Home arrow Oracle arrow Page 3 - Database Interaction with PL/SQL: Sub-programs in Depth

Procedures with parameters - Oracle

This is part 13 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we concentrated on procedures and discussed some issues of variable scoping with respect to sub-programs. In this article we proceed with some tips on using procedures together with the introduction to functions.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Sub-programs in Depth
  2. Sub-programs interacting with an Oracle database
  3. Procedures with parameters
  4. Using %ROWTYPE in parameter declarations
  5. Introduction to FUNCTION
By: Jagadish Chatarji
Rating: starstarstarstarstar / 12
August 16, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: