Procedures with parameters - Oracle

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.