Oracle DBA FAQ - Creating Your Own PL/SQL Procedures and Functions

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

How To Call a Stored Function?

A stored function can be called as part of expression in any PL/SQL statement. One simplest way to call a stored function is to a dummy SELECT statement as shown in the following tutorial script using SQL*Plus:

SQL> CREATE OR REPLACE FUNCTION GET_SITE
  2    RETURN VARCHAR2 AS
  3  BEGIN
  4    RETURN 'FYICentere.com';
  5  END;
  6  /
Function created.

SQL> SELECT get_site() FROM dual;

GET_SITE()
---------------------------------
FYICentere.com

DUAL is not a real table or view. It is commonly used to with SELECT statement to evaluate expressions.

How To Drop a Stored Function?

If there is an existing stored function and you don't want it any more, you can remove it from the database by using the DROP FUNCTION statement as shown in the following script example:

SQL> CREATE OR REPLACE FUNCTION GET_SITE
  2    RETURN VARCHAR2 AS
  3  BEGIN
  4    RETURN 'FYICentere.com';
  5  END;
  6  /
Function created.

SQL> DROP FUNCTION GET_SITE;
Function dropped.

How To Call a Stored Function with Parameters?

You can define a function that takes parameters, provide values to those parameters when calling the function. Here is a good example of a function with a parameter:

SQL> CREATE OR REPLACE FUNCTION GET_DOUBLE(X NUMBER)
  2    RETURN NUMBER AS
  3  BEGIN
  4    RETURN X * 2;
  5  END;
  6  /
Function created.

SQL> SELECT GET_DOUBLE(36) FROM DUAL;
GET_DOUBLE(36)
--------------
            72

How To Define a Sub Procedure?

A sub procedure is a named procedure defined and used inside another procedure or function. You need to define a sub procedure in the declaration part of the enclosing procedure or function. Sub procedure definition starts with the PROCEDURE key word. Here is a sample script showing you how to define and use a sub procedure:

SQL> CREATE OR REPLACE PROCEDURE HR.DBA_WEEK AS          
  2    PROCEDURE DBA_TASK (day VARCHAR2) AS              
  3    BEGIN
  4      IF day = 'MONDAY' THEN           
  5        DBMS_OUTPUT.PUT_LINE('Checking log files.');  
  6      ELSIF day = 'FRIDAY' THEN        
  7        DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');     
  8      ELSE              
  9        DBMS_OUTPUT.PUT_LINE('Reading some papers.'); 
 10      END IF;           
 11    END;
 12  BEGIN 
 13    DBA_TASK('MONDAY');
 14    DBA_TASK('TUESDAY');              
 15  END;  
 16  /
 
SQL> EXECUTE DBA_WEEK;
Checking log files.
Reading some papers.

(Continued on next part...)

Part: 1 2 3 4 5 6 7

More...