首页 > 代码库 > 存储过程
存储过程
一、子程序
子程序是已命名的PL/SQL块，它们存储在数据库中，可以Wie它们指定参数，可以从任何数据库客户端和应用程序中调用它们。子程序包括存储过程和函数。
子程序包括：
1、声明部分：声明部分包括类型、游标、常量、变量、异常和嵌套子程序的声明。这些项都是局部的，在退出后就不复存在。
2、可执行部分：可执行部分包括赋值、控制执行过程以及操纵ORacle数据的语句。
3、异常处理部分： 异常处理部分包括异常处理程序，负责处理执行存储过程中出现的异常。
子程序的优点：
1、模块化：通过子程序，可以将程序分解为可管理的、明确的逻辑模块。
2、可重用性：子程序在创建并执行后，就可以再任意数目的应用程序中使用。
3、可维护性：子程序可以简化维护操作，因为如果一个子程序受到影响，则只需修改该子程序的定义。
4、安全性：用户可以设置权限，使得访问数据的唯一方式就是通过用户提供的存储过程和函数。不仅可以让数据更安全，而且可以保证它的正确性。
二、存储过程
存储过程是执行某些操作的子程序，是执行特定任务的模块。从根本上讲，存储过程就是明明的PLSQL块，它可以被赋予参数，存储在数据库中，然后由一个应用程序或其他PLSQL程序调用。
1、创建存储过程:
定义过程语法：
CREATE [OR REPLACE] PROCEDURE 过程名称([参数名称[参数模式] NOCOPY 数据类型 [参数名称 [参数模式] NOCOPY 数据类型,...]])
[AUTHID [DEFINER | CURRENT_USER]]
AS || IS
[PRAGMA AUTONOMOUS_TRANSACTION;]
声明部分; --变量的声明等
BEGIN
程序部分;
EXCEPTION
导常处理;
END;
----------------------------------------------
参数模式：表示过程的数据的接收操作，一般分为IN,OUT,IN OUT 3类
OR REPLACE：表示创建或者替换过程，如果过程存在则替换，如果不存在就创建一个新的
AUTHID：子句定义了一个过程的所有者权限，DEFINER（默认）表示定义者权限执行，或者用CURRENT_USER覆盖程序的默认行为，变为使用者权限
PRAGMA AUTONOMOUS_TRANSACTION:表示过程启动一个自治事务，自治事务可以让主事挂起，在过程中执行完SQL后，由用户处理提交或者回滚自治事务，
然后恢复主事务
AS 表示其他变量声明，IS 表示显示游标声明
---------------------示例-------------------------
例：添加员工信息
--添加员工信息create or replace procedure add_emp(eno number,ename varchar2,job varchar2,mgr NUMBER,salary number,hiredate DATE,com NUMBER,dno number) isBEGIN dbms_output.put_line(‘添加员工信息‘); INSERT INTO emp VALUES(eno,ename,job,mgr,hiredate,salary,com,dno); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(‘添加员工失败‘);end add_emp;
注意：
存储过程中数据类型只声明类型，不指定长度。
AS后的变量声明以;结束。
----------------------------------------------------------
2、调用存储过程：
语法：
exec[ute] procedure_name (parameters_list);
说明：
execute：执行命令，可以缩写为exec。
procedure_name：存储过程的名称。
parameters_list：存储过程的参数列表。
调用存储过程有两种方式，命令行方式和PL/SQL方式。
1）命令行方式：打开命令行直接输入存储过程名称进行调用。
SQL〉SET serveroutput ON --设置sql命令窗口，表示显示输出的内容
SQL〉EXEC ADD_EMP(8888,‘zhangsan‘,‘clerk‘,7902,2000,SYSDATE,500,30);
2）PLSQL方式：必须在pl/sql块中调用存储过程，不使用EXEC关键字，直接存储过程名称即可。
BEGIN --PL/SQL方式，不需要使用exec add_emp(8989,‘LISI‘,‘clerk‘,7902,1000,SYSDATE,NULL,30);END;
3、存储过程的参数模式：
存储过程参数有三种模式：IN、OUT和IN OUT，即输入、输出、输入/输出。
定义存储过程参数语法：
parameter_name [IN|OUT|IN OUT] dateType [{:= | default} expression]
注意：
1）参数IN模式是默认模式。如果未指定参数模式，则默认为IN。对于OUT和IN OUT参数，必须明确指定。
2）可以再参数列表中为IN参数指定默认值，OUT和IN OUT不可用。
3）OUT模式时，传入的参数数是无用的，传入的内容不会传递 到过程中去
4）INOUT模式，所过程可以接收到传递的变量内容，同时过程对变量做了修改也可以运回给实参
4 参数的传递方式：
1）按位置传递：
按照参数的书序，依次写入参数内容。调用的参数顺序和定义的参数顺序必须一一对应。
EXEC ADD_EMP(8888,‘zhangsan‘,‘clerk‘,7902,2000,SYSDATE,500,30);
2）按名称传递：
按名称调用时按名称对应，名称对应的关系最重要，次序不重要。
EXEC add_emp(ENO => 8989,ENAME => ‘LISI‘,JOB => ‘clerk‘,MGR => 7902,SALARY => 1000,HIREDATE => SYSDATE,COM => NULL,DNO => 30);
3）混合方式传递：
同时使用位置传递和参数传递，采用这种方式必须将位置参数放在名称参数的前面，只要第一个采用了名称传递法，湖面所有的参数必须使用名称传递法。
EXEC add_emp(8989,‘LISI‘,JOB => ‘clerk‘,MGR => 7902,SALARY => 1000,HIREDATE => SYSDATE,COM => NULL,DNO => 30);
-----------------------------------------------参数模式示例-----------------------------------------------------------------
例一、定义过程使用IN默认可以不写
CREATE OR REPLACE PROCEDURE in_proc( p_a IN VARCHAR2, --明确定义IN参数模式 p_b IN VARCHAR2 --默认的参数模式为in) ASBEGIN dbms_output.put_line(‘执行in_proc()过程: p_a=‘||p_a); dbms_output.put_line(‘执行in_proc()过程: p_b=‘||p_b);END;执行DECLARE v_a VARCHAR2(50):=‘Java开发实战经典‘; v_b VARCHAR2(50):=‘Oracle开发实战经典‘;BEGIN in_proc(v_a,v_b);END;结果:执行in_proc()过程: p_a=Java开发实战经典执行in_proc()过程: p_b=Oracle开发实战经典
示例二、定义过程使用default定义参数默认值
CREATE OR REPLACE PROCEDURE in_proc( p_a IN VARCHAR2 DEFAULT ‘好好学习JAVA‘, --明确定义IN参数模式 p_b IN VARCHAR2 DEFAULT ‘努力看Oracle‘ --默认的参数模式为in) ASBEGIN dbms_output.put_line(‘执行in_proc()过程: p_a=‘||p_a); dbms_output.put_line(‘执行in_proc()过程: p_b=‘||p_b);END;DECLARE v_a VARCHAR2(50):=‘Java开发实战经典‘; BEGIN in_proc(v_a);END;结果:执行in_proc()过程: p_a=Java开发实战经典执行in_proc()过程: p_b=努力看Oracle使用了第二个参数没有写，使用了默认值，如果有传递参数则使用传递的参数
OUT模式
示例一、定义过程使用OUT
CREATE OR REPLACE PROCEDURE out_proc( p_a OUT VARCHAR2, --明确定义out参数模式 p_b OUT VARCHAR2) --明确定义out参数模式ASBEGIN dbms_output.put_line(‘执行out_proc()过程: p_a=‘||p_a); dbms_output.put_line(‘执行out_proc()过程: p_b=‘||p_b); p_a :=‘Java开发实战经典‘; --将此值返回给实参 p_b :=‘Oracle开发实战经典‘;END;执行DECLARE v_a VARCHAR2(50):=‘好好学习‘; v_b VARCHAR2(50):=‘天天向上‘;BEGIN out_proc(v_a,v_b); dbms_output.put_line(‘调用out_proc()过程: v_a=‘||v_a); dbms_output.put_line(‘调用out_proc()过程: v_b=‘||v_b); END;结果:执行out_proc()过程: p_a=执行out_proc()过程: p_b=调用out_proc()过程: v_a=Java开发实战经典调用out_proc()过程: v_b=Oracle开发实战经典
inout模式
示例一、定义过程使用INOUT
CREATE OR REPLACE PROCEDURE inout_proc( p_a IN OUT VARCHAR2, --明确定义out参数模式 p_b IN OUT VARCHAR2) --明确定义out参数模式ASBEGIN dbms_output.put_line(‘执行inout_proc()过程: p_a=‘||p_a); dbms_output.put_line(‘执行inout_proc()过程: p_b=‘||p_b); p_a :=‘Java开发实战经典‘; --将此值返回给实参 p_b :=‘Oracle开发实战经典‘;END;执行DECLARE v_a VARCHAR2(50):=‘好好学习‘; v_b VARCHAR2(50):=‘天天向上‘;BEGIN inout_proc(v_a,v_b); dbms_output.put_line(‘调用inout_proc()过程: v_a=‘||v_a); dbms_output.put_line(‘调用inout_proc()过程: v_b=‘||v_b); END;结果:执行inout_proc()过程: p_a=好好学习执行inout_proc()过程: p_b=天天向上调用inout_proc()过程: v_a=Java开发实战经典调用inout_proc()过程: v_b=Oracle开发实战经典
调用inout_proc过程时，将2个变量v_a,v_b传入到了过程中，由于是INOUT模式，所过程可以接收到传递的变量内容，同时过程对变量做了修改也可以运回给实参
5、存储过程的访问权限
存储过程创建后，只有创建该存储过程的用户和管理员才能有权执行。其他用户如果要调用该存储过程，需要得到存储过程的EXECUTE权限。
例：
--将swap的执行权限授予user1GRANT EXECUTE ON swap TO user1; --将swap的执行权限授予user1，并且user1可以对其他用户进行授权。GRANT EXECUTE ON swap TO user1 WITH GRANT OPTION; --撤销user1用户的执行swap权限。REVOKE EXECUTE ON swap FROM user1
6、删除存储过程
DROP procedure swap;
三、总结：
优点：
1、存储过程增强了PL-SQL的功能和灵活性。
2、存储过程保证了数据的完整性和安全性。
3、提高了sql语句的性能。
4、降低了网络的通讯量。
缺点：
1、移植问题，不同的数据库存储过程语法不同。
2、重新编译问题。更改有依赖的存储过程也会重新编译。
3、需求变更的问题。
存储过程