官术网_书友最值得收藏!

Functions

Similar to a stored procedure, a function is a named derivative of a PL/SQL block that is physically stored within the Oracle database schema.

The key features of stored functions are as follows:

  • A function can accept parameters in all three modes (IN, OUT, and IN OUT) and mandatorily returns a value.
  • Functions can be called in SQL statements (SELECT and DMLs). Such functions must accept only IN parameters of valid SQL types. Alternatively, a function can also be invoked from SELECT statements if the function body obeys the database purity rules.
  • If the function is called from an SQL statement, its return type should be a valid SQL data type. If the function is invoked from PL/SQL, the return type should be a valid PL/SQL type.

    Note

    Starting from Oracle Database 12c, PL/SQL—only data types can cross the PL/SQL to SQL interface. A PL/SQL anonymous block can invoke a PL/SQL subprogram with parameters of BOOLEAN or a packaged collection type.

The syntax for a function is as follows:

CREATE [OR REPLACE] FUNCTION [Function Name] [Parameter List]
RETURN [Data type]
[AUTHID DEFINER | CURRENT_USER]
[DETERMINISTIC | PARALLEL_ENABLED | PIPELINED]
[RESULT_CACHE [RELIES_ON (table name)]]
IS
  [Declaration Statements]
BEGIN
 [Executable Statements]
  RETURN [Value]
EXCEPTION
 [Exception handlers]
END [Function Name];

Let us create a standalone function, F_GET_DOUBLE, which accepts a numeric parameter and returns its double:

/*Create the function F_GET_DOUBLE*/
CREATE OR REPLACE FUNCTION F_GET_DOUBLE (P_NUM NUMBER)
RETURN NUMBER   /*Specify the return data type*/
IS

/*Declare the local variable*/
   L_NUM NUMBER;
BEGIN

/*Calculate the double of the given number*/
   L_NUM := P_NUM * 2;

/*Return the calculated value*/
   RETURN L_NUM;
END;
/

Function created.

Functions – execution methods

Functions can either be called from a SQL*Plus environment or invoked from a PL/SQL program as a procedural statement.

The function F_GET_DOUBLE can be executed in the SQL* Plus command prompt as follows. As the function returns an output, you must declare a session variable and capture the function result in the variable.

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Declare a session variable M_NUM to hold the function output*/
VARIABLE M_NUM NUMBER;

/*Function is executed and output is assigned to the session variable*/
EXECUTE :M_NUM := F_GET_DOUBLE(10);

PL/SQL procedure successfully completed.

/*Print the session variable M_NUM*/
PRINT M_NUM

M_NUM
----------
20

The F_GET_DOUBLE function can be called from an anonymous block or a standalone subprogram.

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

DECLARE
      M_NUM NUMBER;
BEGIN
   M_NUM := F_GET_DOUBLE(10);
   DBMS_OUTPUT.PUT_LINE('Doubled the input value as : '||M_NUM);
END;
    /
Doubled the input value as : 20

PL/SQL procedure successfully completed.

Restrictions on calling functions from SQL expressions

Unlike procedures, a stored function can be called from a SELECT statement, provided it does not violate the database purity levels. The rules are as follows:

  • A function called from a SELECT statement cannot contain DML statements
  • A function called from an UPDATE or DELETE statement on a table cannot query (SELECT) or perform transactions (DMLs) on the same table
  • A function called from an SQL expression cannot contain TCL (COMMIT or ROLLBACK) commands or DDL (CREATE or ALTER) commands

The F_GET_DOUBLE function can easily be embedded within a SELECT statement as it respects all the preceding rules:

/*Invoke the function F_GET_DOUBLE from SELECT statement*/
SQL> SELECT F_GET_DOUBLE(10) FROM DUAL;

F_GET_DOUBLE(10)
----------------
              20

Note

In the Oracle Database, DUAL is a table owned by the SYS user, which has a single row and a single column, DUMMY, of VARCHAR2 (1) type. It was first designed by Charles Weiss while working with internal views to duplicate a row. The DUAL table is created by default during the creation of the data dictionary with a single row whose value is X. All database users, other than SYS, use its public synonym to select the value of pseudo columns such as USER, SYSDATE, NEXTVAL, or CURRVAL. Oracle 10g considerably improved the performance implications of the DUAL table through a fast dual-access mechanism.

主站蜘蛛池模板: 神农架林区| 那曲县| 本溪| 新乡市| 鄂托克旗| 曲周县| 循化| 如东县| 漠河县| 旬阳县| 措美县| 绵阳市| 孝感市| 伊春市| 青川县| 墨江| 吴川市| 津南区| 恩施市| 石门县| 伊春市| 渑池县| 紫云| 盐边县| 高邑县| 南澳县| 八宿县| 井陉县| 二连浩特市| 长葛市| 弋阳县| 桂平市| 香港 | 都匀市| 台东市| 昆山市| 岱山县| 广水市| 德保县| 拉萨市| 兴义市|