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.

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.

{mospagebreak title=What are Parameter Default values?}

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’.

{mospagebreak title=What are STORED PROCEDURES?}

The procedures which get stored within the database can be called STORED PROCEDURES. In the past few articles, we worked with several procedures within the PL/SQL program. The PL/SQL program is automatically lost from memory once you close your SQL*Plus or iSQL*Plus (unless you save it in an SQL file). 

Now we shall see how to store procedures in the database to make them STORED PROCEDURES. Let us consider the following program:

create or replace procedure dispEmp as
  cursor c_emp is
   select ename, sal from emp;
 begin
  for r_emp in c_emp
  loop
   dbms_output.put_line(r_emp.ename || ‘,’ || r_emp.sal); 
  end loop;
 end;

Make sure that you are not actually executing the procedure in the above program. You are just asking the Oracle database to accept and store this new procedure ‘dispEmp’ into its database. That is why you will get a message along the lines of ‘Procedure successfully Created’. 

The above created a procedure successfully inside the database. Now, how do we execute it? There exists a separate command ‘execute’ exclusively dedicated to executing stored procedures. The command to execute the above stored procedure would be the following:

Execute dispEmp;

This causes the stored procedure ‘dispEmp’ to be executed successfully.  Now, how do we execute the above stored procedure from another PL/SQL? The following shows how to do it.

BEGIN
 dispEmp;
END;

That’s it. Very simple. There exist no declarations within the above PL/SQL program. The only issue is that we need to call an existing stored procedure. We need not use the command ‘Execute’ here, as it is an SQL command.

Now wherever you carry your database, the STORED PROCEDURES also come along with the database!

{mospagebreak title=What are STORED FUNCTIONS?}

I hope you can understand the STORED FUNCTIONS by now. The FUNCTIONS which get stored inside the database are called STORED FUNCTIONS. The concept is very similar to that of STORED PROCEDURES. Let us see an example for stored functions:

create or replace function getDname(p_empno emp.empno%type) return varchar2 is
  v_dname  dept.dname%type;
 begin 
  select dname into v_dname
  from dept
  where deptno = (select deptno from emp
    where empno = p_empno);
  
  return v_dname;
 end;

The above function receives an ‘empno’ as a parameter and returns the employee’s  department name.  Now, how do we execute the stored function?  You cannot execute a function through the ‘execute’ statement of SQL. Instead you can use it directly with the SELECT statement as follows:

SELECT getDname(7369) FROM dummy;

The above SQL statement should return you ‘RESEARCH’ as the result. What is ‘dummy’?  It is a default table which gets installed automatically when you install Oracle (unless anyone deletes it). The primary use of ‘dummy’ is to provide calculations within the SELECT statement.  As the scope of this article is limited to PL/SQL, I will not go in depth about the ‘dummy’ table.

Now, can we use the above function without using a dummy table?  The answer to this question is yes. You can use it just like a pre-defined function (like TO_UPPER, TO_DATE and so on) within any SQL statement. The following example demonstrates this.

select empno,ename, getDname(empno) as deptname from emp;

The output of the above query would be all employee numbers, names and department names from the table ‘emp’ (without using any join). That is the beauty of stored function. You can use stored functions to hide any huge complex calculations and just use it on the fly using any SQL command (of course generally DML commands).

{mospagebreak title=PACKAGE and PACKAGE BODY}

A package is a single unit containing several stored sub-programs. Even a package itself gets stored inside the database (along with all of its sub-programs). Basically any package in Oracle has two parts, namely the package specification and the package body.

The package specification contains the definition or specification of all the elements in the package that may be referenced outside of the package. These are called the public elements of the package. Like the module, the package specification contains all the code that is needed for a developer to understand how to call the objects in the package. A developer should never have to examine the code behind the specification (which is the body) in order to understand how to use and benefit from the package. The package specification does not contain any executable statements or exception handlers. A specification only specifies, or declares, those objects in the package that are public – that is, visible outside of the package and callable by other programs.

The body of the package contains all the code behind the package specification: the implementation of the modules, cursors, and other objects.   The body may also contain elements that do not appear in the specification. These are called private elements of the package. A private element cannot be referenced outside of the package, since it does not appear in the specification.

The body of the package resembles a standalone module’s declaration section. It contains both declarations of variables and the definitions of all package modules. The package body may also contain an execution section, which is called the initialization section because it is only run once, to initialize the package.

Google+ Comments

Google+ Comments