Chapter 10Creating Functions

Objectives

After completing this lesson,you should be able to to do the following:

Differentate between a procedure and a function
Describe the uses of functions
Create stored functions
Invoke a function
Remove a function

Lesson Agenda

Working with functions:

-Defferentiating between a procedure and a function.
-Describing the uses of functions
-Creating,invoking,and removing stored functions

Overview of Stored Functions

A function:

Is a named PL/SQL block that returns a value.
Can be stored in the database as a schema object for repeated execution
Is called as part of an expression or is used to provide a parameter value for another subprogram
Can be grouped into PL/SQL packages

Creating Functions

The PL/SQL block must have at least one RETURN statement.

CREATE [OR PREPLACE] FUNCTION function_name
[
        (
                parameter1 [mode1] datatype1,
                ...
        )
]
RETURN datatype
IS|AS
[
        local_variable_declarations;
        ...
]
BEGIN
        --actions;
        RETURN expression;
END [function_name];

Creating a Function

Demo 01:Creating

CREATE OR REPLACE FUNCTION check_sal
        RETURN Boolean
IS
        v_dept_id       employees.department_id%TYPE;
        v_empno         employees.employee_Id%TYPE;
        v_sal           employees.salary%TYPE;
        v_avg_sal       employees.salary%TYPE;
BEGIN
        v_empno :=205;

        SELECT salary,department_id INTO v_sal,v_dept_id FROM employees WHERE employee_id = v_empno;

        SELECT AVG(salary) INTO v_avg_sal FROM employees WHERE department_id = v_dept_id;

        IF v_sal > v_avg_sal THEN
                RETURN TRUE;
        ELSE
                RETURN FALSE;
        END IF;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                RETURN NULL;
END;

Invoking a Function

Demo 01:Invoke

DECLARE
        ret Boolean;
BEGIN
        ret := check_sal;

        IF(ret IS NULL) THEN
                DBMS_OUTPUT.PUT_LINE('The function returned NULL due to exception');
        ELSIF(ret) THEN
                DBMS_OUTPUT.PUT_LINE('Salary > average');
        ELSE
                DBMS_OUTPUT.PUT_LINE('Salary < average');
        END IF;
END;
/

Demo 01:Results

SQL> @10_1_call.sql
Salary > average

PL/SQL procedure successfully completed.

The Difference Between Procedures and Functions

Procedures	Functions
Execute as a PL/SQL statement	Invoke as part of an expression
Do not contain RETURN clause in the header	Must contain a RETURN clause in the header
Can pass values (if any) using output parameters	Must return a single value
Can contain a RETURN statement without a value	Must contain at least one RETURN statement

注意：在procedure里面包含return表示立即结束这个Procedure；

Creating and Running Functions:Overview

排错：

　　如果是SQL PLUS就可以用SHOW ERRORS命令查看错误.也可以使用USER/ALL/DBA_ERRORS views.

Creating and Invoking a Stored Function Using the CREATE FUNCTION Statement:Example

Demo 02:Creating

CREATE OR REPLACE FUNCTION get_sal
(
        p_id employees.employee_id%TYPE
)
RETURN NUMBER
IS
        v_sal employees.salary%TYPE :=0;
BEGIN
        SELECT salary INTO v_sal FROM employees WHERE employee_id = p_id;

        RETURN v_sal;
END get_sal;
/

Demo 02:Running

--Invoke the function as an expression or as a parameter value.
DECLARE
        v_employee_id employees.employee_id%TYPE := 100;
BEGIN
        DBMS_OUTPUT.PUT_LINE(get_sal(v_employee_id));
END;
/

Demo 02:Result

SQL> @10_2_call.sql
24000

PL/SQL procedure successfully completed.

Using Different Methods for Executing Functions

Advantages of User-Defined Functions in SQL Statements

Can extend SQL where activities are too complex,too awkward,or unavailable with SQL
Can increase efficiency when used in the WHERE clause to filter data,as opposed to filtering the data in the application
Can manipulate data values

NOTE:Whenenver the SQL runtime engine calls a PL/SQL function,it must "switch" to the PL/SQL runtime engine.The overhead of this context switch can be substantial if the function is called many times.

Using a Function in a SQL Expression:Example

View Code

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)
        RETURN NUMBER
IS
BEGIN
        RETURN (p_value * 0.08);
END tax;
/

SELECT employee_id,last_name,salary,tax(salary)
FROM employees
WHERE department_id = 100;

SQL> @10_3.sql

Function created.


EMPLOYEE_ID LAST_NAME                     SALARY TAX(SALARY)
----------- ------------------------- ---------- -----------
        108 Greenberg                      12008      960.64
        109 Faviet                          9000         720
        110 Chen                            8200         656
        111 Sciarra                         7700         616
        112 Urman                           7800         624
        113 Popp                            6900         552

6 rows selected.

Calling User-Defined Functions in SQL Statements

User-defined functions act like built-in single-row functions and can be used in:

The SELECT list or clause of a query
Conditional expressions of the WHERE and HAVING clause
The CONNECT BY、START WITH、ORDER BY and GROUP BY clauses of a query
The VALUES clause of the INSERT statement
The SET clause of the UPDATE statement

Rrestrictions When Calling Functions from SQL Expressions

User-defined functions that are callable from SQL expressions must:
- -Be strored in the database
- -Accept only IN parameters with valid SQL data types,not PL/SQL-specific types
- -Rreturn valid SQL data types,not PL/SQL-specific types
When calling functions in SQL statements:
- You must own the function or have the EXECUTE privilege
- You may need to enable the PARALLEL_ENABLE keyword to allow a parallel execution of the SQL statement(高级知识部分；SQL执行的可以并发操作;)

Controlling Side Effects When Calling Functions from SQL Expressions

Functions called from:

A SELECT statement cannot contain DML statements
An UPDATE or DELETE statement on a table T cannot query or contain DML on the same table T.
SQL statements cannot end transactions(that is,cannot execute COMMIT or ROLLBACK operations)

Note:Calls to subprograms that break these restrictions are alose not allowed in the function.

一条SQL语句对表T进行修改操作，在SQL语句中调用了一个函数F，此时函数F的操作就不能对表T进行查询、修改、删除、增加的操作.否则就会报错.

PL/SQL Functions that SQL Statements can Invoke

To be invocable from SQL statements, a stored function (and any subprograms that is invokes) must obey these purity rules, which are meant to control side effects:

When invoked from a SELECT statement or a parallelized INSERT,UPDATE,or DELETE statement,the subprogram cannot modify any database tables.
When invoked from an INSERT、UPDATE or DELETE statement,the subprogram cannot query or modify any database tables modyfied by the statement.
When invoked from a SELECT,INSERT,UPDATE or DELETE statement,the subprogram cannot execute any of the following SQL statements:
- -Transaction control statement,such as COMMIT
- -Session control statements,such as SET ROLE
- -System control statements,such as ALTER SYSTEM
- -Database definition language(DDL) statements,such as CREATE,which are committed automatically.
If any SQL statement in the execution part of function violates a rule,then a run-time error occurs when that statement is parsed.

Restrictions on Calling Functions from SQL:Example

Demo 01 解析：

当UPDATE语句对表emp进行DML操作时，调用的函数dml_call_sql同时也对表emp进行了INSERT操作，(violates a rule)

解决办法:将函数dml_call_sql中的INSERT Clause进行删除.

CREATE OR REPLACE FUNCTION dml_call_sql
(
        p_sal NUMBER
)
RETURN NUMBER
IS
BEGIN
        INSERT INTO emp(employee_id,last_name,email,hire_date,job_id,salary)
        VALUES(1,'Frost','jfrost@company.com',SYSDATE,'SA_MAN',p_sal);

        RETURN (p_sal + 100);
END;
/

UPDATE emp SET salary = dml_call_sql(2000) WHERE employee_id = 170;

SQL> @10_4.sql

Function created.

UPDATE emp SET salary = dml_call_sql(2000) WHERE employee_id = 170
                        *
ERROR at line 1:
ORA-04091: table HR.EMP is mutating, trigger/function may not see it
ORA-06512: at "HR.DML_CALL_SQL", line 8

Demo 02 解析:

当UDPATE语句对表emp进行DML操作时，调用的函数dml_call_sql同时对表emp进行了SELECT操作你,(violates a rule)

解决办法：将函数中的SELECT Clause进行删除或者查询除了emp表之外的表(示例，见Demo 03)

CREATE OR REPLACE FUNCTION dml_call_sql
(
        p_sal NUMBER
)
RETURN NUMBER
IS
        v_name emp.last_name%TYPE;
BEGIN
        SELECT last_name INTO v_name FROM emp WHERE employee_id = 170;

        RETURN (p_sal + 100);
END;
/

UPDATE emp SET salary = dml_call_sql(2000) WHERE employee_id = 170;
SQL> @10_4_1.sql

Function created.

UPDATE emp SET salary = dml_call_sql(2000) WHERE employee_id = 170
                        *
ERROR at line 1:
ORA-04091: table HR.EMP is mutating, trigger/function may not see it
ORA-06512: at "HR.DML_CALL_SQL", line 9

Demo 03:

解析：UDPATE 语句对表emp进行操作，调用的函数dml_call_sql中对表dept进行的查询操作，是正常的，不影响emp表的操作的；所以不会有错误发生.

CREATE OR REPLACE FUNCTION dml_call_sql
(
        p_sal NUMBER
)
RETURN NUMBER
IS
        v_name dept.department_name%TYPE;
BEGIN
        SELECT department_name INTO v_name FROM dept WHERE department_id = 270;

        RETURN (p_sal + 100);
END;
/

UPDATE emp SET salary = dml_call_sql(2000) WHERE employee_id = 170;
SQL> @10_4_2.sql

Function created.


1 row updated.

SQL>

Named and Mixed Notation from SQL

PL/SQL allows arguments in a subroutine call to be specified using postional,named,or mixed notation.
Prior to Oracle Database 11g,only the postional notation is supported in calls from SQL.
Starting in Oracle Database 11g,named and mixed notation can be used for specifying in calls to PL/SQL subroutines from SQL statements.
For long parameter lists,with most having default values,you can omit values from the optional parameters.
You can avoid duplicating the default value of the optional parameter at eache call site.

Named and Mixed Notation from SQL:Example

View Code

CREATE OR REPLACE FUNCTION f
(
        p_param_1 IN NUMBER DEFAULT 1,
        p_param_5 IN NUMBER DEFAULT 5
)
RETURN NUMBER
IS
        v_var NUMBER;
BEGIN
        v_var := p_param_1 + (p_param_5 * 2);

        RETURN v_var;
END f;
/

SELECT f(p_param_5 => 10) FROM DUAL;
SQL> @10_5.sql

Function created.


F(P_PARAM_5=>10)
----------------
              21

Removing Functions:Using the DROP SQL Statement

Using the DROP statement

DROP FUNCTION f;

Viewing Functions Using Data Dictionary Views

SQL> desc USER_SOURCE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)
SQL> set pagesize 20
SQL> SELECT TEXT
  2  FROM USER_SOURCE
  3  WHERE TYPE='FUNCTION' AND NAME = 'F'
  4  ORDER BY LINE;

TEXT
--------------------------------------------------------------------------------
FUNCTION f
(
        p_param_1 IN NUMBER DEFAULT 1,
        p_param_5 IN NUMBER DEFAULT 5
)
RETURN NUMBER
IS
        v_var NUMBER;
BEGIN
        v_var := p_param_1 + (p_param_5 * 2);

        RETURN v_var;
END f;

13 rows selected.

Quiz

A PL/SQL stored function:

1、Can be invoked as part of an expression

2、Must contain a RETURN clause in the header

3、Must return a single value

4、Must contain at least one RETURN statement

5、Does not contain a RETURN clause in the header.

Summary

In this lesson,you should have learned how to:

Differentiate between a procedure and a function.
Describe the uses fo functions.
Create stored functions
Invoke a function
Remove a function.