PL_SQL模块学习之十二、存储过程

存储过程

定义

 存储过程：是用户定义的一系列SQL语句的集合，涉及特定表或其他对象的任务，用户可以调用存储过程；
 而函数：通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

创建执行

 1.存储过程用于执行特定的操作，可以接受输入参数、输出参数、返回单个或多个结果集。
 2.在创建存储过程时，可指定输入/输出参数：输入参数可将数据传递到执行部分；输出参数可将结果传递到应用环境；

优点

 1）存储过程增强了SQL语言的功能和灵活性。存储过程可以流控制语句编写，完成复杂的判断和运算.
 2）存储过程保证数据的安全性。通过存储过程可以使没有权限的用户在权限控制下间接获取数据库中的数据，从而保证数据的安全.
 3）通过存储过程可以使相关的动作关联在一起发生，从而维护数据库的完整性。
 4）在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已编译好的过程可极大的改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。
 5）可以降低网路的通信量，因为不需要通过网络来传送很多SQL语句到数据库服务器。
 6）把体现企业规则的运算程序放入数据库服务器中，以便于集中控制。当企业规则发生变化时，数据库改变存储过程即可，无需改变任何应用程序。

1.1 存储结构

结构：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_list)]
{IS|AS}
[local_declarations]#声明区
BEGIN
executable_statements#子程序区
[EXCEPTION exception_handlers]#异常处理区
END [procedure_name];

1.1.1 声明区

位于PROCEDURE和BEGIN之间，定义变量

CREATE OR REPLACE PROCEDURE PROTEST
IS

--声明变量
	xxx number;
	yyy varchar2(20) :='oracle';
	
--声明REF变量
	type empcursor is ref cursor;
	
--异常对象
	read_disk_refused exception;
	
--内嵌函数或其他存储过程
	FUNCTION foo RETURN BOOLEAN IS
	BEGIN
	RETURN (XXX>1000)
	END IF;
	BEGIN
	......
	END;

1.1.2 子程序区

位于BEGIN和END之间，至少存在一条PL/SQL语句，可以为NULL；

1.1.3 异常处理区

即处理子程序在执行中发生的异常

CREATE OR REPLACE PROCEDURE PRO(employee_age IN number)
IS 
	age NUMBER;
	mydate DATE;
	dateexp EXCEPTION;
	BEGIN	
		mydate := 'TIME_STRING';
		age := employee_age;
		IF age >150
		THEN
			raise dateexp;#当age>150时就抛出异常
				EXCEPTION#在异常处理区EXCEPTION后进行处理
					WHEN dateexp THEN  #WHEN子句对应了异常类型
						handling dateexp;
					WHEN OTHERS THEN
						handling other exps;
	END;

1.2 示例

创建一个存储过程，用于更新SCOTT用户的EMP表中的员工薪水，低于平均工资的员工在原有基础上统一增加15%.

SQL> CREATE OR REPLACE PROCEDURE InCreSal
  2  IS
  3  avg_sal NUMBER;
  4  CURSOR var_cursor
  5  IS
  6  SELECT * FROM emp;
  7  BEGIN
  8  SELECT avg(sal) INTO avg_sal FROM emp_test;
  9  FOR var_cur IN var_cursor
 10  loop
 11  UPDATE emp_test SET sal = sal*1.15
 12  WHERE sal < avg_sal;
 13  dbms_output.put_line(var_cur.ename || 'updated');
 14  END LOOP;
 #此处若想更新到库，需添加commit;语句
 15  END;
 16  /

过程已创建。

1.3 信息和定义查询

通过数据字典user_objects查询过程名，过程状态等

SQL> select object_id,object_type,status,created from user_objects
  2  where object_name='INCRESAL';

 OBJECT_ID OBJECT_TYPE         STATUS          CREATED
---------- ------------------- --------------- --------------
     73526 PROCEDURE           VALID           12-3月 -20

通过Oracle提供的DBMS_METADATA包的过程get_dll获得该对象的创建过程

SQL> set pagesize 1000;
SQL> set long 1000;
SQL> select dbms_metadata.get_dDl('PROCEDURE','INCRESAL')FROM DUAL;
#使用DBMS_METADATA获得过程InCreSal定义
DBMS_METADATA.GET_DDL('PROCEDURE','INCRESAL')
--------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "SCOTT"."INCRESAL"
IS
avg_sal NUMBER;
CURSOR var_cursor
IS
SELECT * FROM emp;
BEGIN
SELECT avg(sal) INTO avg_sal FROM emp_test;
FOR var_cur IN var_cursor
loop
UPDATE emp_test SET sal = sal*1.15
WHERE sal < avg_sal;
dbms_output.put_line(var_cur.ename || 'updated');
END LOOP;
END;

通过数据字典user_source获得对象定义

SQL> desc user_source;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 NAME                                                                       VARCHAR2(30)
 TYPE              #包括表，触发器，存储过程等                                                         VARCHAR2(12)
 LINE                                                                       NUMBER
 TEXT              #存储对象的大文本定义                                                       VARCHAR2(4000)

SQL> select text from user_source where name='INCRESAL';

TEXT
------------------------------------------------------------------------------------------------------------------------
PROCEDURE InCreSal
IS
avg_sal NUMBER;
CURSOR var_cursor
IS
SELECT * FROM emp;
BEGIN
SELECT avg(sal) INTO avg_sal FROM emp_test;
FOR var_cur IN var_cursor
loop
UPDATE emp_test SET sal = sal*1.15
WHERE sal < avg_sal;
dbms_output.put_line(var_cur.ename || 'updated');
END LOOP;
END;

已选择15行。

当删除表格emp_test使得过程无效时，如何恢复有效

SQL> drop table emp_test;

表已删除。

SQL> select object_id,object_type,status,created from user_objects
  2  where object_name='INCRESAL';

 OBJECT_ID OBJECT_TYPE         STATUS          CREATED
---------- ------------------- --------------- --------------
     73526 PROCEDURE           INVALID         12-3月 -20

SQL> create table emp_test as select * from emp;

表已创建。
#将表格恢复后一九无效
SQL> select object_id,object_type,status,created from user_objects
  2  where object_name='INCRESAL';

 OBJECT_ID OBJECT_TYPE         STATUS          CREATED
---------- ------------------- --------------- --------------
     73526 PROCEDURE           INVALID         12-3月 -20
#需要将过程重新编译
SQL> alter procedure InCreSal compile;

过程已更改。

SQL> select object_id,object_type,status,created from  user_objects
  2  where object_name='INCRESAL';

 OBJECT_ID OBJECT_TYPE         STATUS          CREATED
---------- ------------------- --------------- --------------
     73526 PROCEDURE           VALID           12-3月 -20

1.4 IN 和OUT 参数

使用IN和OUT参数完成数据库服务端与用户之间交互。

• IN ：给过程传值，可以是常量、字面值或表达式，该参数只读
• OUT：给过程返回值，必须是变量，不可使用默认值，该参数只写
• INOUT：给程序传值，同时是过程返回值，该参数只能是变量

示例：
创建过程search_name，定义输入参数为ID，经过计算返回员工名和相应工资。

SQL> list
  1  CREATE OR REPLACE PROCEDURE search_name
  2  (id IN NUMBER,name OUT varchar2,sal OUT number)
  3  AS
  4  BEGIN
  5  SELECT ename,sal INTO name,sal
  6  FROM emp
  7  WHERE empno=id;
  8  EXCEPTION
  9  WHEN OTHERS
 10  THEN
 11  dbms_output.put_line('Error!');
 12* end search_name;
SQL> /

过程已创建。

调用该过程时，需要定义实参代替输入值，通过定义和输出对应的变量来获得输出结果。

SQL> list
  1  declare
  2  name varchar2(20); #定义两个输出变量
  3  salary number(8,2);
  4  begin
  5     search_name(7844,name,salary);
  6      dbms_output.put_line('id 7844 ' || name ||' salary is '||salary);
  7* end;
SQL> /
id 7844 TURNER salary is 1500

PL/SQL 过程已成功完成。

1.5 存储过程权限

• CREATE PROCEDURE
• CREATE ALL PROCEDURE

通过user_sys_privs查看SCOTT用户的系统权限

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          UNLIMITED TABLESPACE                     NO
SCOTT                          CREATE TRIGGER                           NO
SCOTT                          CREATE ANY TRIGGER                       NO

在dba即sys用户通过grant create procedure to scott;授权后，SCOTT用户获得CREATE PROCEDURE权限

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          UNLIMITED TABLESPACE                     NO
SCOTT                          CREATE PROCEDURE                         NO
SCOTT                          CREATE TRIGGER                           NO
SCOTT                          CREATE ANY TRIGGER                       NO