How to write a PL/SQL procedure that mimics a function

Function or Procedure?

Somebody asked for a simple comparison between a PL/SQL pass-by-value function and pass-by-reference procedure, where the procedure uses only an OUT mode parameter to return the result. This provides examples of both, but please note that a pass-by-value function can be used in SQL or PL/SQL context while a pass-by-reference procedure can only be used in another anonymous of named block PL/SQL program.

The function and procedure let you calculate the value of a number raised to a power of an exponent. The third parameter lets you convert the exponent value to an inverse value, like 2 to ¹/₂.

CREATE OR REPLACE FUNCTION find_root_function
( pv_number   BINARY_DOUBLE
, pv_power    BINARY_DOUBLE
, pv_inverse  BINARY_INTEGER DEFAULT 0 ) RETURN BINARY_DOUBLE IS
 
  -- Declare local variable for return value.
  lv_result   BINARY_DOUBLE;
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    lv_result := POWER(pv_number,pv_power);
  ELSE
    lv_result := POWER(pv_number,(1 / pv_power));
  END IF;
 
  RETURN lv_result;
END find_root_function;
/

You can test it with these to queries against the dual table:

SELECT TO_CHAR(find_root_function(4,3),'99,999.90') FROM dual;
SELECT TO_CHAR(find_root_function(125,3,1),'99,999.90') FROM dual;

The procedure does the same thing as the function. The difference is that the fourth parameter to the procedure returns the value rather than a formal return type like a function.

CREATE OR REPLACE PROCEDURE find_root_procedure
( pv_number   IN     BINARY_DOUBLE
, pv_power    IN     BINARY_DOUBLE
, pv_inverse  IN     BINARY_INTEGER DEFAULT 0
, pv_return      OUT BINARY_DOUBLE ) IS
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    pv_return := POWER(pv_number,pv_power);
  ELSE
    dbms_output.put_line('here');
    pv_return := POWER(pv_number,(1 / pv_power));
  END IF;
 
END find_root_procedure;
/

You can test it inside an anonymous block PL/SQL program, like this:

DECLARE
 
  -- Declare input variables.
  lv_input   BINARY_DOUBLE;
  lv_power   BINARY_DOUBLE;
  lv_inverse BINARY_INTEGER;
  lv_output  BINARY_DOUBLE;
 
BEGIN
 
  -- Assign input values to variables.
  lv_input := '&1';
  lv_power := '&2';
  lv_inverse := '&3';
 
  -- Test raising to a power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
  -- Test raising to an inverse power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
END;
/

You can test it inside an anonymous block PL/SQL program, like the following example. For reference, the difference between PL/SQL and the SQL*Plus environment is large. The EXECUTE call is correct in SQL*Plus but would be incorrect inside a PL/SQL block for a Native Dynamic SQL (NDS) call. Inside a PL/SQL block you would use EXECUTE IMMEDIATE because it dispatches a call from the current running scope to a nested scope operation (see comment below).

-- SQL*Plus Test.
VARIABLE sv_input BINARY_DOUBLE
VARIABLE sv_power BINARY_DOUBLE
VARIABLE sv_inverse BINARY_DOUBLE
VARIABLE sv_output  BINARY_DOUBLE
 
-- Verify the null value of the session variable.
SELECT :sv_output AS ":sv_output" FROM dual;
 
BEGIN
 
  -- Prompt for local assignments and initialize output variable.
  :sv_input   := '&1';
  :sv_power   := '&2';
  :sv_inverse := '&3';
  :sv_output  := 0;
 
END;
/
 
-- Run the procedure in the SQL*Plus scope.
EXECUTE find_root_procedure(:sv_input, :sv_power, :sv_inverse, :sv_output);
 
-- Query the new value of the session variable.
SELECT TO_CHAR(:sv_output,'99,999.90') AS ":output" FROM dual;

As usual, I hope this helps folks beyond the one who asked. Comments are always welcome.

Written by maclochlainn

January 31st, 2012 at 5:00 pm

Posted in Oracle,Oracle 11g,Oracle XE,pl/sql,sql

7 Responses to 'Function or Procedure?'

Subscribe to comments with RSS or TrackBack to 'Function or Procedure?'.

Thanks! This was very helpful in helping me understand how to use OUT-only mode variables.

Michael

1 Feb 12 at 8:35 am
You can use PL/SQL functions in SQL as long as they don’t update the database.

I turn that the rules around so that I only make a PL/SQL function if it can be called from SQL. If it updates the database then I always make it a procedure.

That way if someone sees a function, they know it can be called from SQL without delving into the code or physically testing it.

Gary

3 Feb 12 at 2:11 am

I understand the philosophy, and if it works for you that’s great. Alternatively, you could put all your functions in a code schema and only grant EXECUTE privileges and create synonyms for the SQL callable functions. This type of architecture masks the PL/SQL-only scope functions from your web developers. It also allows you to write an autonomous function that can insert, update, or delete rows by returning a 1 for true or 0 for false in an autonomous function, or a string through the DECODE function or CASE operator.

Here’s a working example of the required code components. The first PL/SQL and SQL statements conditionally drop and create the table and sequence, the second creates the an autonomous function that inserts data, the third is a function that calls the autonomous function and that’s callable from SQL, and last the query that demonstrates it works.

This creates the sample table and sequence.

-- This conditionally drops the table and sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('ORACLE10G_FORWARD_ID','ORACLE10G_FORWARD')) LOOP
    IF object_type = 'TABLE' THEN
      EXECUTE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINT';
    ELSE
      EXECUTE 'DROP SEQUENCE '||i.object_type;
    END IF;
  END LOOP;
END;
/
 
-- Create table.
CREATE TABLE oracle10g_forward
( oracle10g_forward_id NUMBER CONSTRAINT pk_oracle10g_forward PRIMARY KEY
, oracle10g_forward    VARCHAR2(20));
 
-- Create sequence.
CREATE SEQUENCE oracle10g_forward_seq;

This is the PL/SQL-only scope autonomous function that inserts a row:

CREATE OR REPLACE FUNCTION from_plsql 
( pv_input VARCHAR2 ) RETURN NUMBER IS
  -- Use the SQL NUMBER type for true (1) or false (0).
  lv_retval NUMBER := 0;
  -- Use this to hold sequence for 10g compatibility.
  lv_sequence NUMBER;
  -- Set function as autonomous.
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- Use backward compatible 10g syntax for sequence and insert row.
  SELECT oracle10g_forward_seq.NEXTVAL INTO lv_sequence FROM dual;
  INSERT INTO oracle10g_forward VALUES (lv_sequence, pv_input);
 
  -- Check whether the INSERT statement succeeded and commit it.
  IF SQL%ROWCOUNT > 0 THEN
    COMMIT;
    lv_retval := 1;
  END IF;
 
  -- This returns 1 when the INSERT statement succeeds.  
  RETURN lv_retval;
EXCEPTION
  WHEN OTHERS THEN
    -- This returns 0 when the INSERT statement fails.
    RETURN lv_retval;
END from_plsql;
/

This is the SQL-only function that can be called from a query:

CREATE OR REPLACE FUNCTION from_sql 
( pv_input VARCHAR2 ) RETURN NUMBER IS
  -- Use the SQL NUMBER type for true (1) or false (0).
  lv_retval NUMBER := 0;
BEGIN
  -- Does the function confirm insert.
  IF from_plsql(pv_input) = 1 THEN
    lv_retval := 1;
  END IF;
  RETURN lv_retval;
END from_sql;
/

This tests the viability of the solution from a query. Voilà a query that performs DML statements.

1 2	SELECT DECODE(from_sql('Yes, you can!'),1,'Inserted','Failed') AS RESULT FROM dual;

maclochlainn

4 Feb 12 at 12:47 am

hi

in your program you are using execute command . i think you should use execute immediate instead of only execute.

thanks

jawad

15 Sep 12 at 3:18 am
The difference between PL/SQL and the SQL*Plus environment is large. The EXECUTE call is correct in SQL*Plus but would be incorrect inside a PL/SQL block. Inside PL/SQL you would use an EXECUTE IMMEDIATE when making a Native Dynamic SQL (NDS) call.

You use EXECUTE IMMEDIATE inside PL/SQL blocks to dispatch the call to the SQL Engine. If your immediate call runs function or pass-by-reference procedure, that’s an inline interruption, and your calling program waits on completion of the called program unit and a reply from any function. Whether a function is autonomous or not doesn’t matter because a function call must return a value. That means dispatched function calls may be run inside (when the function is defined as non-autonomous) of the calling program’s scope and outside (when the function is defined as autonomous); and this becomes very important when designing database triggers.

If your immediate call runs an autonomously defined pass-by-value procedure, the call out doesn’t await a response or completion of the called procedure. It simply dispatches the call and forgets about whether or not it succeeds.

maclochlainn

15 Sep 12 at 12:16 pm
I know mysql commands usage, so i know i want to execute PL/SQL programs under MYSQL, so any one can guide me on that with user- friendly steps.
You can also send easy way of learning plsql with good examples.

Kiran

13 May 13 at 1:30 am
Do you mean you want to write MySQL stored programs? If so I’ve got a lot of examples on the blog,or you can get my comparative Oracle Database 11g and MySQL Developer Handbook. Chapter 14 teaches you how to write MySQL stored programs and Chapter 15 how to write triggers.

maclochlainn

17 May 13 at 4:20 pm

MacLochlainns Weblog

Function or Procedure?

7 Responses to 'Function or Procedure?'

Leave a Reply

Recent Posts

Things Written About

Pages

Blogroll

Archives