Home arrow Oracle arrow Database Interaction with PL/SQL, Named Notations, Storing Procedures and Functions

Database Interaction with PL/SQL, Named Notations, Storing Procedures and Functions

This is part 16 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we worked with PL/SQL TABLE types in between sub-programs. In this article, we will look into Named Notation, default values of parameters, stored procedures, stored functions and finally introduce the concepts of package and package body.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, Named Notations, Storing Procedures and Functions
  2. What are Parameter Default values?
  3. What are STORED PROCEDURES?
  4. What are STORED FUNCTIONS?
  5. PACKAGE and PACKAGE BODY
By: Jagadish Chatarji
Rating: starstarstarstarstar / 25
September 06, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Please note that all the examples in this series have been tested only with Oracle 10g.  I didn’t really test them with all the previous versions of Oracle.  I suggest you refer to the documentation of the respective version you are using, if any of the programs failed to execute.

What is Named Notation?

When we pass parameter values to a PL/SQL program, we are passing parameter values using positional notation. That means the first value goes to the first parameter, second to the second parameter and so on. What If I want the first value to go to the second parameter, the second value to the third parameter, the third value to the first parameter, and so on? This is where the concept of named notation comes in.

Using named notation, you can pass the values of parameters in any order you want regardless of the position.  The following is a sample program:

declare
 procedure dispEmp(p_sal emp.sal%type, p_deptno emp.deptno%type) is
 begin
  for r_emp in (select ename from emp where sal > p_sal and deptno = p_deptno)
  loop
   dbms_output.put_line(r_emp.ename);
  end loop;
 end;
BEGIN
 dispEmp(1000,10);
 dbms_output.put_line('-------------');
 dispEmp(p_sal => 1000, p_deptno => 10);
 dbms_output.put_line('--------------');
 dispEmp(p_deptno => 10, p_sal => 1000);
END;

I hope the procedure ‘dispEmp’ is quite understandable. The only issue on which we need to concentrate is the statements in the main program. The first statement is as follows:

dispEmp(1000,10);

The above statement is straightforward. The 1000 gets into ‘p_sal’ and the 10 gets into ‘p_deptno’.  This is positional notation. Based on the position, the respective parameter name gets chosen.  The next statement is as follows:

dispEmp(p_sal => 1000, p_deptno => 10);

The above statement explicitly says that 1000 has to be assigned to ‘p_sal’ and 10 has to be assigned to ‘p_deptno’.  This is named notation.  In the named notation, we need to specify parameter names (even though it is not that necessary in the above statement).  The next statement is as follows:

dispEmp(p_deptno => 10, p_sal => 1000);

The above statement explicitly says that 10 has to be assigned to ‘p_deptno’ and 1000 has to be assigned to ‘p_sal’. This is also named notation. But it will not give any error and in all three cases, the result would be the same. You can observe that I changed the order according to my requirement.



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