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

What are Parameter Default values? - Oracle

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

This is almost similar to the DEFAULT clause you use with column definition. If you don’t provide any value to the column, the DEFAULT value is chosen to replace null automatically. In the same manner, we can design our parameters to the sub-programs in a very flexible manner. We can provide DEFAULT values to the parameters. That means the parameter values are not compulsory. But if we send parameter values, parameters get bound to those values. Let us consider the following program.

declare
 procedure dispEmp(p_sal emp.sal%type := 0, p_deptno emp.deptno%type := null) is
 begin
  if p_deptno is null then
   for r_emp in (select ename from emp where sal > p_sal)
   loop
    dbms_output.put_line(r_emp.ename);
   end loop;
  else
   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 if;
 end;
BEGIN
 dispEmp(1000,10);
    dbms_output.put_line('-------------');
 dispEmp(2000);
    dbms_output.put_line('-------------');
 dispEmp(p_deptno => 20);
END;

Even though the above program is bit lengthy, it is actually very easy to understand.  If you observe the declaration of procedure, it is something like the following:

procedure dispEmp(p_sal emp.sal%type := 0, p_deptno emp.deptno%type := null) is

The above declaration says that ‘p_sal’ should be considered 0 when no value is provided. Similarly ‘p_deptno’ should be considered null when no value is provided for it.

dispEmp(1000,10);

The above statement states that ‘p_sal’ should be treated as 1000 and ‘p_deptno’ should be treated as 10.

dispEmp(2000);

The above statement states that ‘p_sal’ should be treated as 2000 and ‘p_deptno’ should be treated as null.

dispEmp(p_deptno => 20);

The above statement states that ‘p_sal’ should be treated as 0 and ‘p_deptno’ should be treated as 20. If you observe the above statement carefully, without the named notation, it is impossible to send the value only to ‘p_deptno’.



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