PL/SQL存储函数&存储过程(存储过程和存储函数的区别、函数(创建/修改/删除函数、函数参数、无参函数、带输入/输出参数函数、函数相关)、过程(创建/修改/删除存储过程)

---

PL/SQL 存储函数 & 存储过程

ORACLE提供可以把PL/SQL程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。

Oracle中AS/IS的区别

• 在Oracle的存储过程和函数中，其实IS和AS是同义词，没有什么区别。
• 在自定义类型（TPYE）和包（PACKAGE）时，使用IS和AS也并没有什么区别。
• 但是在创建视图（VIEW）时，只能使用AS而不能使用IS。
• 在声明游标（CURSOR）时，只能使用IS而不能使用AS。

存储过程和存储函数的区别

• 存储过程和函数的唯一区别在于函数可以有一个返回值，而存储过程没有返回值。
• 调用/执行时
  • 存储函数：有返回值，创建完成后，通过 SELECT function() FROM dual; 或者 在PL/SQL块中 执行
  • 存储过程：由于没有返回值，创建完成后，不能使用SELECT 语句，通过 EXECUTE命令 或者 在PL/SQL块中 执行

AUTHID

在创建函数/存储过程时，可使用 AUTHID CURRENT_USER 或 AUTHID DEFINER 选项以表明在执行该函数/过程时Oracle使用的权限。

1、如果使用 AUTHID CURRENT_USER 选项创建一个函数/过程，则 Oracle 用调用该函数/过程的用户权限执行该函数/过程。为了成功执行该函数/过程，调用者必须具有访问该函数/存储过程体中引用的所有数据库对象所必须的权限。

2、如果用默认的 AUTHID DEFINER 选项创建函数/过程，则 Oracle 使用函数/过程所有者的特权执行该函数/过程，为了成功执行该函数/过程，函数/过程的所有者必须具有访问该函数/存储过程体中引用的所有数据库对象所必须的权限 。想要简化应用程序用户的特权管理，在创建函数/存储过程时，一般选择 AUTHID DEFINER选项。这样就不必授权给需要调用的此过程的所有用户了。

函数

创建/修改函数

CREATE [OR REPLACE] FUNCTION 函数名 [(参数名 [IN | OUT | IN OUT] 数据类型, …)]
[AUTHID DEFINER | CURRENT_USER]
RETURN 返回值类型
{IS | AS}
	[变量或常量声明]
BEGIN
	<可执行部分> 
	RETURN 返回值;
[EXCEPTION
		<异常处理...>;
		RETURN <当出现异常时的返回值、变量或常量>;]
END [函数名];

函数参数

函数的参数有3种类型：

• IN 参数类型：表示输入给函数的参数，该参数只能用于传值，不能被赋值。
  • 不写默认为 IN
• OUT 参数类型：表示参数在函数中被赋值，能够传给函数调用程序，该参数只能用于赋值，不能用于传值。
  • 因为函数只能有一个返回值，PL/SQL 程序可以通过 OUT 型的参数实现有多个返回值
• IN OUT 参数类型：表示参数既能够传值，也能够被赋值。

无参函数

1、定义无参函数

CREATE OR REPLACE FUNCTION fn_PrintToday
RETURN DATE
IS
BEGIN
    RETURN sysdate;
END;

2、调用无参函数

--调用方式1
SELECT fn_PrintToday() FROM DUAL;

--调用方式2
BEGIN
    dbms_output.put_line(fn_PrintToday);
END;

有参函数

带输入参数函数

1、定义带输入参数函数

CREATE OR REPLACE FUNCTION fn_SearchEmpByEmpno (p_empno VARCHAR2)
RETURN VARCHAR2         --返回值类型 
IS
	v_ename emp.ename%TYPE;
BEGIN
	SELECT ename INTO v_ename FROM emp WHERE empno = p_empno;
	RETURN v_ename;
EXCEPTION
	WHEN no_data_found THEN 
		raise_application_error(-20001,'您输入的empno无效!');
END;

2、调用带输入参数函数

--调用方式1
SELECT fn_SearchEmpByEmpno(p_empno=>'1001') FROM DUAL;

--调用方式2
BEGIN
    dbms_output.put_line(fn_SearchEmpByEmpno(p_empno=>'1001'));
END;

带输出参数函数

1、定义带输出参数函数

CREATE OR REPLACE FUNCTION fn_CountEmp(p_ename IN VARCHAR2, out_value OUT INT)
RETURN VARCHAR2
IS
BEGIN
	SELECT COUNT(1) INTO out_value FROM emp WHERE ename = p_ename;
    RETURN p_ename;
END;

2、调用带输出参数函数

DECLARE
    ename emp.ename%TYPE;
    result INT;
BEGIN
    ename := fn_CountEmp(p_ename=>'James',out_value=>result);
    dbms_output.put_line(ename || '名字的有' || to_char(result) || '个人');
END;

删除函数

DROP FUNCTION  [schema.]函数名

函数相关

• 查看函数：SELECT * FROM USER_SOURCE WHERE USER_SOURCE.NAME=’<函数名>’;
• 编译函数：ALTER FUNCTION 函数名 COMPILE;

过程

过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。
函数参数相关的知识，存储过程也都一样，所以就不多写了。

创建/修改过程

CREATE [OR REPLACE] PROCEDURE 过程名 [(参数名 [IN | OUT | IN OUT] 数据类型, …)]
[AUTHID DEFINER | CURRENT_USER]
{IS | AS}
	[变量或常量声明]
BEGIN
	<可执行部分> 
[EXCEPTION
		<异常处理...>;
END [函数名];

删除存储过程

DROP PROCEDURE [schema.]过程名

存储过程实例

对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间, 为其加薪 %5
[95 , 98) %3
[98, ?) %1
得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).

CREATE or REPLACE PROCEDURE add_sal_procedure(dept_id NUMBER, temp OUT NUMBER)
IS
	CURSOR sal_cursor 
	IS 
	  SELECT employee_id id, hire_date hd, salary sal 
	  FROM employees 
	  WHERE department_id = dept_id;
	  
	a NUMBER(4, 2) := 0;
BEGIN
	temp := 0;       
	FOR c in sal_cursor LOOP
		a := 0;    
		IF c.hd < to_date('1995-1-1', 'yyyy-mm-dd') THEN
			a := 0.05;
		ELSIF c.hd < to_date('1998-1-1', 'yyyy-mm-dd') THEN
			a := 0.03;
		ELSE
			a := 0.01;
		END IF;
		temp := temp + c.sal * a;
		UPDATE employees SET salary = salary * (1 + a) WHERE employee_id = c.id;
	END LOOP; 
	dbms_output.put_line(temp);         
end;

执行过程

DECLARE
	v_temp number(10) := 0;
BEGIN
	add_sal_procedure(80, v_temp);
END;