一、子程序

　　子程序是已命名的PL/SQL块，它们存储在数据库中，可以Wie它们指定参数，可以从任何数据库客户端和应用程序中调用它们。子程序包括存储过程和函数。

　　子程序包括：

　　1、声明部分：声明部分包括类型、游标、常量、变量、异常和嵌套子程序的声明。这些项都是局部的，在退出后就不复存在。

　　2、可执行部分：可执行部分包括赋值、控制执行过程以及操纵ORacle数据的语句。

　　3、异常处理部分：　　异常处理部分包括异常处理程序，负责处理执行存储过程中出现的异常。

　　子程序的有点：

　　1、模块化：通过子程序，可以将程序分解为可管理的、明确的逻辑模块。

　　2、可重用性：子程序在创建并执行后，就可以再任意数目的应用程序中使用。

　　3、可维护性：子程序可以简化维护操作，因为如果一个子程序受到影响，则只需修改该子程序的定义。

　　4、安全性：用户可以设置权限，使得访问数据的唯一方式就是通过用户提供的存储过程和函数。不仅可以让数据更安全，而且可以保证它的正确性。

二、存储过程

　　存储过程是执行某些操作的子程序，是执行特定任务的模块。从根本上讲，存储过程就是明明的PLSQL块，它可以被赋予参数，存储在数据库中，然后由一个应用程序或其他PLSQL程序调用。

　　1、创建存储过程:

　　语法：

　　CREATE [OR REPLACE] PROCEDURE procedure_name

　　[(paraameter_list)]

　　{IS/AS}

　　[local_declarations]

　　BEGIN

　　　　executable_statements;

　　　　[EXCEPTION]

　　　　[exception_handlers]

　　END [procedure_name]

　　说明：

　　procedure_name：为存储过程的名字；

　　paraameter_list：参数列表，参数中可以使用in和out表示输入和输出参数。可选

　　AS表示其他变量声明，IS表示显示游标声明

　　local_declarations：局部声明，可选

　　executable_statements：可执行语句

　　exception_handlers：异常处理语句，可选

　　OR REPLACE：可选。如果不包含，创建存储过程如果存在会报错，包含存在会替换。

　　例：添加员工信息　　

--添加员工信息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：存储过程的参数列表。c

　　调用存储过程有两种方式，命令行方式和PL/SQL方式。

　　1）命令行方式：打开命令行直接输入存储过程名称进行调用。　　

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;

　　参数的传递方式：

　　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);

　　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不可用。

　　带OUT参数的存储过程：

　　例：根据empno查询员工信息返回。　　

CREATE OR REPLACE PROCEDURE QUERY_EMP(  V_EMPNO IN EMP.EMPNO%TYPE,       --输入参数  V_ENAME OUT EMP.ENAME%TYPE,      --输出参数  V_SAL   OUT EMP.SAL%TYPE) IS     --输出参数BEGIN  SELECT ename,sal INTO v_ename,v_sal FROM emp  WHERE empno = v_empno;  dbms_output.put_line(‘数据已查到‘);END QUERY_EMP;

　　调用带输出参数的存储过程，首先定义两个变量保存输出参数返回的内容。　　

DECLARE   v_name emp.ename%TYPE;  v_sal emp.sal%TYPE;BEGIN  query_emp(7788,v_name,v_sal);  dbms_output.put_line(‘name:‘||v_name||‘  sal:‘||v_sal);END;

　　带IN OUT参数的存储过程：

　　数据交换;

CREATE OR REPLACE PROCEDURE swap(  v_num1 IN OUT NUMBER,  v_num2 IN OUT NUMBER)AS  v_temp NUMBER;BEGIN  v_temp := v_num1;  v_num1 := v_num2;  v_num2 := v_temp;END;

--调用DECLARE  v_n1 NUMBER := 10;  v_n2 NUMBER := 20;BEGIN  swap(v_n1,v_n2);  dbms_output.put_line(v_n1||‘    ‘||v_n2);END;

　　 4、存储过程的访问权限

　　存储过程创建后，只有创建该存储过程的用户和管理员才能有权执行。其他用户如果要调用该存储过程，需要得到存储过程的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   

　　5、删除存储过程　　

DROP procedure swap;

Oracle基础 存储过程