ORACLE 提供可以把 PL/sql 程序存储在数据库中,并可以在任何地方来运行它。这样就叫存储过程或函数。
过程和函数统称为 PL/sql 子程序,他们是被命名的 PL/sql 块,均存储在数据库中,并通过输入、输出参数或输入/输出参数与其调用者交换信息。
过程和函数的唯一区别是函数总向调用者返回数据,而过程则不返回数据。
存储函数
创建存储函数
语法:
CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ] --注1
RETURN return_type
{ IS | AS }
<类型.变量的说明>
BEGIN
FUNCTION_body
EXCEPTION
其它语句
END;
说明:
1) OR REPLACE 为可选。有了它,可以或者创建一个新函数或者替换相同名字的函数,而不会出现冲突;
2) 函数名后面是一个可选的参数列表,其中包含 IN、OUT 或 IN OUT 标记。参数之间用逗号隔开。
OUT 参数:标记表示一个值在函数中进行计算并通过该参数传递给调用语句;
IN OUT 参数:标记表示传递给函数的值可以变化并传递给调用语句。
若省略标记,则参数隐含为 IN 。
3) 因为函数需要返回一个值,所以 RETURN 包含返回结果的数据类型。
例子:
--创建一个存储函数 --定义一个函数:获取给定部门的工资总和与该部门员工总数 --要求:部门号定义为参数,工资总额定义为返回值 CREATE OR REPLACE FUNCTION GET_SAL_FUN(DEPT_ID NUMBER,TOTAL_COUNT OUT NUMBER) RETURN NUMBER IS --函数使用过程中,需要声明的变量、记录类型、cursor V_SUM_SAL NUMBER(10) := 0; CURSOR SAL_CURSOR IS SELECT E.SALARY FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = DEPT_ID; BEGIN TOTAL_COUNT := 0; --函数的执行体 FOR C IN SAL_CURSOR LOOP V_SUM_SAL := V_SUM_SAL + C.SALARY; TOTAL_COUNT := TOTAL_COUNT + 1; END LOOP; RETURN V_SUM_SAL; END; /运行之后,在旁边的列表中可以找到对应的函数:
、
SET SERVEROUTPUT ON DECLARE V_NUM NUMBER(5) := 0; BEGIN DBMS_OUTPUT.put_line(GET_SAL_FUN(100,V_NUM)); DBMS_OUTPUT.put_line(V_NUM); END; /结果:
其中V_NUM在函数中定义为OUT类型,可以在函数中参与计算后,返回给调用函数的语句。
函数调用的传参格式有三种
第一种:位置表示法
第二种:名称表示法
其中,前面为形参,后面为实参,形参的名称必须与函数定义声明里的参数名称相同,这种方式可以任意排列参数位置。
第三种方式属于第一种和第二种混合使用。
在CREATE OR REPLACE FUNCTION 语句中声明函数参数时,可以使用DEFAULT 关键字为输入参数指定默认值。
存储过程
创建存储过程
存储过程和存储函数差不错,区别在于存储函数有返回值,而存储过程没有返回值。
SET SERVEROUTPUT ON --创建一个存储过程 --定义一个存储过程:获取给定部门的工资总和(通过out参数) --要求:部门号,工资总和 定义为参数 CREATE OR REPLACE PROCEDURE GET_SAL_PRO(DEPT_ID NUMBER,SUM_SAL OUT NUMBER) IS CURSOR SAL_CURSOR IS SELECT E.SALARY FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = DEPT_ID; BEGIN SUM_SAL := 0; --函数的执行体 FOR C IN SAL_CURSOR LOOP SUM_SAL := SUM_SAL + C.SALARY; END LOOP; DBMS_OUTPUT.put_line(SUM_SAL); END; /结果:
例子:
--自定义一个存储过程,对给定部门(作为输入参数)的员工进行加薪操作,若其到公司的时间在 -- (?, 95)期间,加薪 5%;[95,98),加薪3%;[98,?),加薪1%。 -- 并输出为此次加薪公司每月需要额外付出多少成本(定义一个OUT型的输出参数) CREATE OR REPLACE PROCEDURE ADD_SALARY(DEPT_ID NUMBER,ADD_TEMP OUT NUMBER) IS CURSOR SAL_CURSOR IS SELECT E.EMPLOYEE_ID,E.HIRE_DATE,E.SALARY FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = DEPT_ID FOR UPDATE; --加薪比例 R NUMBER(4,2) := 0; --加薪后的工资 V_NEW_SALR NUMBER(10,2) := 0; BEGIN ADD_TEMP :=0; FOR C IN SAL_CURSOR LOOP --每次循环初始化 R := 1; V_NEW_SALR := 0; --打印最初的值 DBMS_OUTPUT.put_line(C.EMPLOYEE_ID || '-入职日期:' || C.HIRE_DATE || ',加薪前的工资:' || C.SALARY); IF C.HIRE_DATE < TO_DATE('1995','yyyy') THEN R := 0.05; ELSIF C.HIRE_DATE >= TO_DATE('1995','yyyy') AND C.HIRE_DATE < TO_DATE('1998','YYYY') THEN R := 0.03; ELSIF C.HIRE_DATE >= TO_DATE('1998','yyyy') THEN R := 0.01; END IF; --计算新增的成本 ADD_TEMP := ADD_TEMP + C.SALARY * R; --更新数据 UPDATE EMPLOYEES E1 SET E1.SALARY = C.SALARY * (1 + R) WHERE E1.EMPLOYEE_ID = C.EMPLOYEE_ID; SELECT E2.SALARY INTO V_NEW_SALR FROM EMPLOYEES E2 WHERE E2.EMPLOYEE_ID = C.EMPLOYEE_ID; --打印改编后的值 DBMS_OUTPUT.put_line('加薪后的工资:' || V_NEW_SALR); END LOOP; END; /存储过程的调用
SET SERVEROUTPUT ON DECLARE ADD_TEMP NUMBER(10,2) := 0; BEGIN ADD_SALARY(100,ADD_TEMP); DBMS_OUTPUT.put_line('新增成本:' || ADD_TEMP); END; /结果:
注1:
AUTHID
在创建存储过程时,可使用 AUTHID CURRENT_USER 或 AUTHID DEFINER 选项,以表明在执行该过程时Oracle 使用的权限。
1) 如果使用 AUTHID CURRENT_USER 选项创建一个过程,则 Oracle 用调用该 过程的用户权限执行该过程。为了成功执行该过程,调用者 必须具有访问该存储过程体中引用的所有数据库对象所必须的权限;
2) 如果用默认的 AUTHID DEFINER 选项创建过程,则 Oracle 使用过程所有者的特权执行该过程。为了成功执行该过程,过程的所有者 必须具有访问该存储过程体中引用的所有数据库对象所必须的 权限。想要简化应用程序用户的特权管理,在创建存储过程时,一般选择 AUTHID DEFINER选项,这样就不必授权给需要调用的此过程的所有用户了。