Chapter -10-Creating 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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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; /
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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; /
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--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; /
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.