[PL/SQL] Procedure vs Function

1. Difference between PROCEDURE and FUNCTIONS

2. IN, OUT, IN OUT parameters

IN : read-only (we can’t change its value)
OUT : write-only (we can’t read its value)
IN OUT : read-write

Demo:

DECLARE
  vcParamIN    VARCHAR2(100) := 'i';
  vcParamOUT   VARCHAR2(100) := 'o';
  vcParamINOUT VARCHAR2(100) := 'io';
BEGIN
  /*
  test.testIOParam( p_param_in    => 'i'  //OK         
                   ,p_param_out   => 'o'  //NOK
                   ,p_param_inout => 'io' //NOK
                );
  */  dbms_output.put_line('-------1-------'); 
  test.testIOParam( p_param_in    => ''
                   ,p_param_out   => vcParamOUT
                   ,p_param_inout => vcParamINOUT
                );  dbms_output.put_line('-------2-------');
  test.testIOParam( p_param_in    => vcParamIN
                   ,p_param_out   => vcParamOUT
                   ,p_param_inout => vcParamINOUT
                );
  dbms_output.put_line('vcParamIN = '    || vcParamIN   || 
                      ',vcParamOUT = '   || vcParamOUT  ||
                      ',vcParamINOUT = ' || vcParamINOUT            
                      ); 
END;
/
-------1-------
> p_param_in =  ,p_param_out =  ,p_param_inout = io
-------2-------
> p_param_in = i ,p_param_out =  ,p_param_inout = io
  l_dummy_in = i ,l_dummy_out =  ,l_dummy_inout = io
< p_param_in = i ,p_param_out = OUT ,p_param_inout = INOUT
vcParamIN = i,vcParamOUT = OUT,vcParamINOUT = INOUT

3. NOCOPY hint for OUT, IN OUT parameters

NOCOPY hint is used to improve performance. Let’s see how it works ? When we should use it and what is side-effect of it ?

First we need to know some notions about “actual” and “formal” parameters.

3.1 Pass by value

NOTE : When there is an exception occurs, the control will go back to the caller immediately. So the value of “actual” won’t be changed.

3.2 Pass by reference using “NOCOPY” hint

NOTE : When there is an exception occurs, since “NOCOPY” shares the same memory segment. So the value of “actual” could be changed in case if the value of parameter is changed before raising exception.

Key Points

Demo

Let’see how much performance are improved when using NOCOPY hint by using dbms_utility.get_time() to capture the processing time.

DECLARE
  list_param reflist_t := reflist_t();
  
  nStartTime NUMBER;
  nEndTime   NUMBER; 
  nLastIndex NUMBER;
BEGIN
  --init "list_param" with 50k items. Each item has length = 4000
  FOR i IN 1..50000 LOOP
    list_param.extend;
    nLastIndex := list_param.last();
    list_param(nLastIndex) := LPAD('BINH', 4000, '#');
  END LOOP;
  
  --Capture processing time of test.testCopyPara()
  nStartTime := dbms_utility.get_time;
  test.testCopyParam(list_param);
  nEndTime := dbms_utility.get_time;  
  dbms_output.put_line('COPY = ' || (nEndTime - nStartTime) );  --Capture processing time of test.testNoCopyParam()
  nStartTime := dbms_utility.get_time;
  test.testNoCopyParam(list_param);
  nEndTime := dbms_utility.get_time;  
  dbms_output.put_line('NOCOPY = ' || (nEndTime - nStartTime) );  
END;
/
COPY = 17
NOCOPY = 0

4. DETERMINISTIC clause

• A PL/SQL function in Oracle can be tagged with a DETERMINISTIC clause, to indicate that the function will always produce the same output for a given input and will have no side effects.
• The DETERMINISTIC clause is a shorthand to document a function’s special properties — that its output depends wholly on its inputs and that it produces no side effects
• Making a function DETERMINISTIC has a performance benefit — if the function is called multiple times within the scope of a single server call (e.g. execution of a PL/SQL block) with the same parameter values, the optimizer can choose to use the previously calculated result instead of invoking the function repeatedly.

Demo

• Here we can see with a DETERMINISTIC clause, the function getJobPriority() is called a few times, not N times for N EMPLOYEE rows.
• This difference in performance with the DETERMINISTIC clause can be significant when dealing with huge volumes of data.

5. Can we use DML and DDL statements inside function?

About DML, directly we can’t do DML inside a func unless we use “PRAGMA autonomous_transaction” with “COMMIT”

About DDL, for any PL/SQL blocks like Procedure, Function, Package, Trigger, … we can’t use DDL command directly as “CREATE TABLE ….”, we have to use dynamic query instead.

And of course, we need to use “PRAGMA autonomous_transaction”, since DDL has “auto-commit” by default, so we don’t need a COMMIT here.

6. Can we use OUT and INOUT parameter in function ? And can function return more than one value ?

Yes and Yes

For the 1st Yes : we can use OUT and IN OUT parameter in function, but if function has OUT parameter then it can’t be called directly from the SELECT statement. We can walkaround by creating a wrapper func in order to call directly from the SELECT statement.

For the 2nd Yes : we can, and there are several ways, here we’ll firgue out 3 ways :

• Using OUT parameters (as the above examples)
• Return collection variable

Demo returns list type

Demo returns table type

Note : we can’t create “AS RECORD”, but “OBJECT’

• Return SYS_REFCURSOR