Home arrow Oracle arrow Page 2 - Database Interaction with PL/SQL: Introduction to Sub-programs

Coding our first sub-program - Oracle

This is part 12 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I looked at several examples of explicit cursors. I also introduced the concept of cursors with parameters. In this article we will look into sub-programs. Mainly we will concentrate on procedures and discuss some issues of variable scoping with respect to sub-programs.

  1. Database Interaction with PL/SQL: Introduction to Sub-programs
  2. Coding our first sub-program
  3. Variables and scope in sub-programs
  4. Variable scope bit in depth
  5. More sub-programs
By: Jagadish Chatarji
Rating: starstarstarstarstar / 18
August 09, 2005

print this article



The previous section dealt with only theory. Now let us implement the above theory. Before implementing the sub-program, we need to know how to implement it. Every sub program (procedure or function) has to specify itself, whether it is procedure or function, followed by its own name. First of all consider the following very simple program:


            procedure displayMsg as


                        dbms_output.put_line('Displaying the message from displayMsg');



            dbms_output.put_line('msg before calling sub-program');


            dbms_output.put_line('msg after calling sub-program');


From the above program, we can see that the sub-program is a procedure and it is named as “displayMsg”. It has its own set of statements (in this case only one DBMS_OUTPUT statement) enclosed within “begin” and “end”.

In the above program, there exists a set of statements between capital BEGIN and capital END. It is just for clarity. Those are the statements of the main program. The program starts its execution as usual at the “declare” statement. And from there it jumps to “BEGIN” (capital) and continues its execution from there onwards. Try to observe that the procedure will not be executed automatically (like anonymous or nested blocks). 

Within the statements of the main program (between capital BEGIN and capital END), the first statement displays some message. After that it calls the sub-program directly with its name (in this case it is “displayMsg”). So, now the flow of execution suddenly jumps to the ‘begin' statement of the sub-program.  It executes all the statements within that ‘begin’ and ‘end’ (in this case it is only one DBMS_OUTPUT statement). 

Once it finishes the execution of the procedure, the control returns back to the next statement of main program i.e., the next statement involved with calling the procedure, which is the final DBMS_OUTPUT statement.

I hope the explanation is very clear in all the aspects.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: