Database Interaction with PL/SQL: Sub-programs in Depth - Procedures with parameters
(Page 3 of 5 )
Parameters (or arguments) are very common in almost any type of programming language. If you know SQL (obviously), you will see several SQL functions like SUM, MAX, TO_UPPER and so on. All of them accept parameters (or arguments). The values you send to those functions are nothing but the parameters.
The value you send as a parameter to a function should match the data type it can accept. For example, the SUM function accepts only column as a parameter. More specificly, it accepts only a numeric column. So every parameter has to be defined with its own data type and width.
Coming to our scenario, even a sub-program can be declared and defined with parameters as well. But, we need to care about the data type, width and other stuff. The declaration of a parameter will be very similar to that of a variable declaration. And these are nearly similar to ‘cursors with parameters’ discussed in part 11 of this series. Consider the following example:
declare
procedure dispEmp(p_deptno dept.deptno%type) as
cursor c_emp is
select ename, sal from emp
where deptno = p_deptno;
begin
for r_emp in c_emp
loop
dbms_output.put_line(r_emp.ename || ',' || r_emp.sal);
end loop;
end;
BEGIN
dbms_output.put_line('-----------------');
dbms_output.put_line('EMPLOYEES');
dbms_output.put_line('-----------------');
dispEmp(10);
END;
For the sake of clarity, let us rewrite the above with only the important statements:
declare
procedure dispEmp(p_deptno dept.deptno%type) as
.
.
.
begin
.
.
end;
BEGIN
.
.
.
dispEmp(10);
END;
I hope I need not explain any more. A value ‘10’ is being passed from th main program to the sub-program using the statement ‘dispEmp(10)’. This value is caught in ‘p_deptno’ and finally used in the SELECT statement of the CURSOR declaration.
Next: Using %ROWTYPE in parameter declarations >>
More Oracle Articles
More By Jagadish Chatarji
/ 10 
