存储函数

创建存储函数

语法：

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 标记。参数之间用逗号隔开。

IN 参数：标记表示传递给函数的值在该函数执行中不改变；

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选项，这样就不必授权给需要调用的此过程的所有用户了。

【Oracle】PL/SQL——存储函数和过程

存储函数

创建存储函数

函数 调用的传参格式有三种

猜你在找的Oracle相关文章