【Oracle】存储过程

存储过程的定义

在PL/SQL程序中，除匿名块以外，还有一类被命名的PL/SQL程序块，称为存储子程序。存储子程序以编译的形式存储在数据库服务器中，可以在应用程序中进行多次调用，是PL/SQL程序模块化的一种体现。PL/SQL存储子程序包括存储过程和（存储）函数两种。存储过程用于执行特定的操作，不需要返回值；函数用于返回特定的数据。在调用时，存储过程可以作为一个独立的表达式被调用，而函数只能作为表达式的一个组成部分被调用。

存储过程的优点

• 存储过程和函数只在创建时需要编译，以后每次执行都不需要重新编译，而一般的SQL语句每次执行一次就会编译一次，因此使用存储过程和函数可提高数据库执行的速度。
• 通常，复杂的业务逻辑需要多条SQL语句，这些语句要分别从客户机发送到服务器，当客户机和服务器之间的操作很多时，将产生大量的网络传输。如果将这些操作放在一个函数或存储过程中，客户机和服务器之间的网络传输将会大大减少，可以降低网络的负载。
• 安全性高，存储过程和函数可以屏蔽对底层数据库对象的直接访问，无须拥有访问底层数据库对象的显示权限。
  存储过程和函数创建一次可以多次使用，可以减少开发人员的工作量。

存储过程的缺点

• 不可移植性：每种数据库内部编程语法都大不相同，当系统需要兼容多种数据库时，最好不要用存储过程和函数。
• 业务逻辑多次存在：采用存储过程和函数就意味着系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试的成本。
• 可扩展性低：如果存储过程和函数中存在复杂运算，则会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统的扩展性的问题。
• 引用对象的结构变更对高并发数据库性能影响较大。为了提升性能，数据库会将存储过程和函数代码编译成中间运行代码（类似于Java的class文件），所以其更像静态语言，当存储过程和函数（如表和视图等）结构改变后，存储过程或函数需要重新编译才能生效，对于一些高并发应用场景，在线变更结构的瞬间同事编译存储过程和函数，可能会导致数据库压力瞬间上升而引起故障。

存储过程的创建和调用

语法：

CREATE [OR REPLACE] PROCEDURE 过程名称(
  参数名称 [参数模式] 数据类型 [DEFALT|:=value],...)
)]
AS|IS
  声明部分；
EXCEPTION
  异常处理；
END;

• CREATE OR REPLACE表示创建或替换存储过程，如果存储过程存在则替换它，否则就创建一个新存储过程、
• 参数模式表示存储过程的数据接收操作，包括IN、OUT、IN OUT三种，默认为IN。

存储过程相关权限：(未指定用户授权)

权限名称	作用
CREATE ANY PROCEDURE	为任意用户创建存储过程的权限
CREATE PROCEDURE	为用户创建存储过程的权限
ALTER PROCEDURE	修改拥有的存储过程的权限
EXECUTE PROCEDURE	执行用户存储过程的权限
DROP ANY PROCEDURE	删除任意存储过程的权限

授权并创建存储过程：

--创建用户
create user test identified by test123;
--授权
grant create procedure to test;
--连接
conn test/test123;

--创建存储过程
create or replace procedure proc_demo
is
begin
 dbms_output.put_line('欢迎你'||user);
 dbms_output.put_line('现在的时间是'||to_char(sysdate,'YYYY-mm-DD hh24:MM:ss'));
end;

调用存储过程： EXECUTE|EXEC|CALL 存储过程名称（实参...）

在PL/SQL程序块中调用存储过程：

--创建
CREATE OR REPLACE PROCEDURE proc_test(p_str1 VARCHAR2,p_str2 VARCHAR2)
AS
BEGIN
 DBMS_OUTPUT.PUT_LINE(p_str1||p_str2);
END;

--调用
declare
 v_var1 varchar2(20):='hello';
 v_var2 varchar2(20):='procedure!';
begin
 proc_test(v_var1,v_var2);
end;
--调用完成

参数模式

存储过程定义的参数有三种模式，分别是IN、OUT、IN OUT。

• IN：默认的参数模式，表示数值传递，可以是常量或表达式，在子程序中所做的修改不会影响原始参数值。
• OUT：不带任何数值到子程序中，出事值为NULL，只能是变量，不能是常量或表达式，子程序可以通过此变量将数值返回给调用处。
• IN OUT：将值传递到子程序中，同时将子程序中对变量的修改返回到调用处，参数只能是变量，不能是常量或表达式。

在存储过程中声明形参时，不能定义形参的长度或精度，他们是作为参数传递机制的一部分传递的，是由实参决定的。

IN参数模式

CREATE OR REPLACE PROCEDURE proc _ in ( p _ empno IN NUMBER )
 AS 
 V _ ename scott , emp . ename TYPE ;
 V _ sal scott . emp . sal 왕 TYPE ;
 BEGIN 
 SELECT ename , sal INTO v _ ename , V _ sal FROM scott . emp 
  WHERE empno = p _ empno ;
 DBMS _ OUTPUT . PUT _ LINE (＇雇员的姓名是：' V _ enamel '工资是：' v _ sal );
 EXCEPTION 
 WHEN NO _ DATA _ FOUND THEN 
 DBMS _ OUTPUT . PUT _ LINE (＇雇员编号未找到）;
 END;

输出：雇员的姓名是：张三 工资是：2500

使用IN参数模式传参时，也可以使用DEFAULT关键字为参数设置默认值，这样在调用时不传递此参数也不会出错。但需要注意的是，有默认值的参数应该放在参数列表的最后。

参数默认值

create or replace procedure proc_user_insert(
  v_user number;
  V _ dname VARCHAR2 DEFAULT ' TEMP ', v _ loc VARCHAR2 DEFAULT ' TEMP ')
AS 
 BEGIN 
  INSERT INTO scott . dept VALUES ( v _ deptno , V _ dname , V _ loc ); COMMIT ;
EXCEPTION 
  WHEN OTHERS THEN 
   DBMS _ OUTPUT . PUT _ LINE (＇添加失败！原因为 IsQLERRM ); 
  ROLLBACK ;
END;
)

OUT参数模式

CREATE OR REPLACE PROCEDURE proc _ out ( P _ deptno NUMBER ,
 P _ num OUT NUMBER , p _ avgsal oU Т NUMBER )
 AS 
 BEGIN 
 SELECТ COUNT (*) num , ROUND ( AVG ( sal ),2) avgsal INTO p _ num , P _ avgsal FROM scott . emp WHERE deptno = p _ deptno ;
 EXCEPTION 
 WHEN NO _ DATA _ FOUND THEN 
 RAISE _ APPLICATION _ ERROR (-20000,＇该部门编号不存在＇);
 END ;

--定义一个 PL / sQL 块调用存储过程
 DECLARE 
 V_num NUMBER ; V_avgsal NUMBER ;
 BEGIN 
 proc _ out (10, v _ num , V _ avgsal );
 DBMS_OUTPUT.put_line ('10号部门的总人数为 v _num1'，平均工资为 lv_avgsal);
 END ;

此时参数只是作为一个形参标记使用，其内容不会传递到存储过程中，在过程中对参数值的修改将返回给相应的实参。

IN OUT参数模式

CREATE OR REPLACE PROCEDURE proc _ dept _ dname _ exist (
 P _ io _ value IN OUT VARCHAR2)
 IS 
 V _ count NUMBER ;
 BEGIN 
 SELECТ COUNT (*) INTO v_count FROM scott.dept WHERE dname= P_io_value; 
 IF ( v_count>0) THEN 
 P_io_value:='已存在＇;
 ELLSE 
 P_io_value:='不存在＇;
 END IF ;
 END ;
 --存储过程已创建

--定义一个 PL / sQL 块调用存储过程
 DECLARE 
 v io value VARCHAR2(20):=' ACCOUNTING '; BEGIN 
 proc 
_ dept 
_ dname _ exist ( v _ io _ value );
 DBMS _ OUTPUT . PUT _ LINE ('部门名称
 END;

IN OUT参数模式相当于IN和OUT两种模式的集合，可以将变量的内容传递到过程中，也可以将过程中对变量的修改返回到原始变量。

存储过程的管理

1. 修改存储过程
   修改存储过程可以先删除该存储过程，然后再重建，这样需要为新创建的存储过程重新进行权限分配，因此通常采用CREATE OR REPLACE PROCEDURE方式重新创建并覆盖原有的存储过程，这样会保留原有的权限分配。
2. 查询存储过程及其代码
   可以通过数据字典查看存储过程及其源代码：

• user_procedure:查看当前用户所有的存储过程、函数信息。
• user_source：查看当前用户所有对象的源代码。
• user_errors：查看当前所有用户的存储过程或函数的错误信息。

查看当前用户的子程序信息：

select object_name,object_type from user_procedure;

查看存储过程定义的内容：

select name,text from user_source where type='PROCEDURE';

3. 重新编译存储过程
   在编写存储过程时，往往需要用到某些数据库对象，这种存储过程和数据库对象存在的依赖关系，可以通过数据字典USER_DEPENDENCIES进行查询。

查询存储过程和数据库对象之间的依赖关系：

select name,type,referenced_name from user_dependencies
where referenced_name='EMP' or referenced_name='DEPT';

4. 删除存储过程

DROP PROCEDURE proc_test;