Oracle Procedure 存储过程

Oracle Procedure




一、块
块是pl/sql组成的基本单元，本篇讲述存储过程，鉴于2者有很高的相似性，且是其中一种块，所以在此做一个简单的块讲述


1、块的结构
declare
  定义部分(可选)
begin
  可执行部分(必选)
exception
  异常处理部分(可选)
end;


2、块的分类
(1)、无名块(也叫匿名块)：动态构造并只能执行一次，常用来测试或执行存储过程。
(2)、命名块：加了用<<>>括起了带标号的无名块。
(3)、子程序：包括存储过程【本篇要讲述的内容】、函数和包等。这些块一旦被定义便会存储在数据库中，可随时调用。
(4)、触发器：根据触发的事件调用。


3、举例
(1)、无名块
declare
  v_name varchar2(8):='CC';
  v_name2 constant varchar2(8):='CC';  --常量，constant关键字用于指定常量
  v_name3 varchar2(8) default 'CC';  --和:=等价
  v_name4 varchar2(8) not null :='CC';  --为not null的必须被赋值
  v_name4 varchar2(8);  --缺省情况下，变量被初始化为null
  v_name5 boolean not null default false;
begin
  sp_flux_user_list();
end;


(2)、命名块
说明：主要用于不同作用域同名变量间的引用
<<insertype1>>
declare
  v_name number:=1;
begin
  insert into t_type values(v_name);
  <<insertype2>>
  declare
    v_name number:=2;
  begin
    dbms_output.put_line(v_name);
  end;
  <<insertype3>>
  declare
    v_name number:=2;
  begin
    dbms_output.put_line(v_name);
  end;
  <<insertype4>>
  declare
    v_salary number:=2;
  begin
    dbms_output.put_line(v_salary);
  end;
end;


如上面的例子，现在引入2个概念
变量的作用域：指变量在程序中的有效范围。对于一个plsql变量，它的作用域是从该变量被声明开始到变量所在块结束。
变量的可视域：指变量前不加以限定就能直接访该变量的那一段程序。
上面例子中，insertype1块的变量v_name在到所在块结束前都是其作用域，并且在insertype4块内可视，但在insertype2和insertype3内不可视，因为insertype2和insertype3内有同名变量。
当在块中声明一个变量时，如果在子块中也声明了同名的变量，则它们的作用域没变。但在子块中，只有子块中声明的变量时可视的，这时要引用父块中的变量名时，必须加以限定。
这时要在insertype2内引用insertype1的变量就需要这样写“insertype1.v_name:=v_name”，但insertype2与insertype3的变量无法互相引用，因为作用域互不相同。


(3)、存储过程
create or replace procedure sp_flux_user_list
is
  v_em emp.empno%TYPE; --表示以emp表中的empno字段的类型定义变量
  v_de dept%rowtype; --表示v_de是一行数据
begin
  dbms_session.set_nls('nls_date_format','"yyyy-mm-dd hh24:mi:ss"');
  dbms_output.put_line(sysdate);
end sp_flux_user_list;


(4)、触发器
create or replace trigger tri_tse
before insert or update or delete on emp
begin
  if to_char(sysdate,'DY','nls_date_language=american') in ('SAT','SUN') then
    raise_application_error(-20001,'不能在休息日改变雇员信息');
  end if;
end;


4、注意
(1)、定义部分的标识符必须以字母开头；一个或多个字母、数字、或特殊符号组成；长度不超过30；不能有空格；非保留字
plsql不区分大小写，如要区分大小写或空格或保留字等，则使用""，例如"aAt code" number;
(2)、plsql将长度为0的字符串当做null来处理。
(3)、在PLSQL里，select、DML语句可以直接写，而DDL、DCL语句不能直接写。




二、存储过程概述，存储过程大体分为这么几个部分: 
  1.创建语句：create or replace procedure 存储过程名 
    如果没有or replace语句，则仅仅是新建一个存储过程。如果系统存在该存储过程，则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。 
  2.存储过程名定义：包括存储过程名和参数列表。参数名和参数类型。参数名不能重复， 参数传递方式:IN, OUT, IN OUT 
    IN 表示输入参数，按值传递方式，并且它不允许在存储过程中被重新赋值(in相当于JAVA的final)。如果存储过程的参数没有指定存参数传递类型，默认为IN。
    OUT 表示输出参数，可以理解为按引用传递方式，可以作为存储过程的输出结果，供外部调用者使用。需要注意，当一个参数被指定为OUT类型时，就算在调用存储过程之前对该参数进行了赋值，在存储过程中该参数的值仍然是null。 
    IN OUT 即可作输入参数，也可作输出参数，属于真正的按引用传递参数。
    参数的数据类型只需要指明类型名即可，不能指定宽度，参数的宽度由外部调用者决定。过程可以有参数，也可以没有参数。
  3.变量声明块：紧跟着的as (is)关键字，可以理解为pl/sql的declare关键字，用于声明变量。 
    变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。且最大长度为30. 
    变量名[CONSTANT] 类型标识符[NOT NULL][:=值|DEFAULT 值]；
  4.过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。 
  5.异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选 
  6.结束块：由end关键字结果。




三、存储过程参数的传递方式
1.无参过程
create or replace procedure pro_time
is
  v_em emp.empno%TYPE; --字段变量，表示以emp表中的empno字段的类型定义变量
  v_de dept%rowtype; --记录变量，表示v_de是一行数据
begin
  dbms_session.set_nls('nls_date_format','"yyyy-mm-dd hh24:mi:ss"');
  dbms_output.put_line(sysdate);
  dbms_output.put_line(v_de.id);--输出记录变量的某个字段
end pro_time;


2.带输入输出参数的过程
create or replace procedure pro_emp(
  i_eno emp.empno%type, --默认in
  i_sal in emp.sal%type,
  o_name out varchar2,
  num1 in out number) is
begin
  update emp set sal=i_sal where empno=i_eno return ename into o_name;
  num1:=i_sal*10;
  commit;
end pro_emp;


调用
declare
  v_eno number:=1111;
  v_salary:=2500;
  vn emp.ename%type;
  n1 number:=30;
begin
  pro_sal(v_eno,v_salary,vn,n1);
  dbms_output.put_line('姓名:' || vn || '升至10倍后的工资:' || n1);
end;


说明：
我们无法在存储过程的定义中指定存储参数的宽度，也就导致了我们无法在存储过程中控制传入变量的宽度。这个宽度是完全由外部传入时决定的。
对于IN参数，其宽度是由存储过程外部决定。 
对于OUT 和IN OUT 参数，其宽度是由存储过程内部决定。 
比较明智的方法就是参数的数据类型使用%type，这样双方就达成了一致。但注意，使用%type为参数定义类型，该参数具有定义在形参上而不是通过实参传递的数据长度。




四、存储过程参数的默认值
说明：
可以通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。 
需要注意的是，默认值仅仅支持IN传输类型的参数，OUT 和 IN OUT不能指定默认值。。


例一：
create or replace procedure pro_test(i1 in number,
                                     i2 in date default sysdate) is
begin
  dbms_output.put_line(to_char(add_months(i2, i1),'yyyy-mm-dd'));
end pro_test;
调用
exec pro_test(1) --输出 2012-09-17(今天是2012-08-17)
exec pro_test(1,to_date('2012-01-01','yyyy-mm-dd')) --输出 2012-02-01


例二：当默认值不在最后一位怎么调用
create or replace procedure pro_test(i2 in date default sysdate,
                                     i1 in number) is
begin
  dbms_output.put_line(to_char(add_months(i2, i1),'yyyy-mm-dd'));
end pro_test;
调用
exec pro_test(i1=>1) --输出 2012-09-17(今天是2012-08-17)




五、存储过程参数的传参形式
由上面默认值不在第一位的情况可见，传参的形式有2种
第一种是位置表示法，按照参数顺序传参，例如exec pro_test(1,to_date('2012-01-01','yyyy-mm-dd'))，这种形式不需要知道过程的形参名称，但必须按照参数顺序传参。
第二种是名称表示法，指定参数名称传参，例如exec pro_test(i1=>1)，这种形式不需要按照参数的顺序传参，但必须知道过程形参的名称，且如果使用缺省值的参数不是存储过程最后一个参数，则只能用名称表示法。
另外，位置表示法和名称表示法在一些调用中也可以混合使用。但是，当在调用存储过程中出现了第一个名称表示法的参数时，后面的参数也必须使用名称表示法传值。


例一：
declare
  v_bir varchar2(10):='1999';
  v_sex varchar2(10):='1';
begin
  pro_tb_flux_user_list(v_code,v_name,p_bir=>v_bir,p_sex =>v_sex);
end;




六、存储过程的结构
1.存储过程的结构：
CREATE OR REPLACE PROCEDURE myproc AS --这里写as和is是等价
/*声明变量部分*/
  v_name varchar2(10);  --变量
  c_male constant int :=1;  --常量
  type au_record is record(name varchar2(10),sex number);
  v_Author au_record;  --记录类型
Begin
/*执行部分，必须且最重要的部分，至少包含一条可执行语句*/
Exception
/*异常处理*/
End myproc;




七、存储过程的选择语句


1、IF语句的三种形式：
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSIF-ELSE-END IF


IF…ELSIF…ELSE语句
例子：
CREATE OR REPLACE PROCEDURE myproc AS
  cou NUMBER;
BEGIN 
  cou:= 1;
  IF cou>10 THEN DBMS_OUTPUT.put_line('cou = '||cou);
  ELSIF cou<5 THEN DBMS_OUTPUT.put_line('值小于5');
  ELSE DBMS_OUTPUT.put_line('条件不满足');
  END IF;
END myproc;


2、case语句的三种形式


基本CASE 结构
CASE 选择变量名
WHEN 表达式1 THEN 语句序列1
WHEN 表达式2 THEN 语句序列2
WHEN 表达式n THEN 语句序列n
ELSE 语句序列n+l
END CASE;


表达式CASE 结构
变量:=CASE 选择变量名
WHEN 表达式1 THEN 值1
WHEN 表达式2 THEN 值2
WHEN 表达式n THEN 值n
ELSE 值n+l
END CASE；


搜索CASE 结构
CASE 
WHEN 条件表达式1 THEN 语句序列1
WHEN 条件表达式2 THEN 语句序列2
WHEN 条件表达式n THEN 语句序列n
ELSE 语句序列n+1
END CASE;




八、存储过程的循环语句


1、Loop循环(do...while)
说明：此循环是先执行一次之后再进行判断
格式：
LOOP 
循环的语句;
EXIT WHEN 终止条件,true则终止;
循环条件必须更改;
END LOOP;
--如果没有WHEN 条件，遇到EXIT 语句则无条件退出循环。


例子(循环输出1~10)：
DECLARE
  cou NUMBER;
BEGIN -- 必须给一个初始值
  cou := 1;
  LOOP 
    DBMS_OUTPUT.put_line('cou = '||cou);
    cou := cou + 1;
    EXIT WHEN cou>10;
  END LOOP;
END;


2、While循环
说明：此循环是先判断，之后如果条件满足则执行
格式：
while(判断循环的条件,true则执行) loop 
  循环的语句;
  循环条件的改变;
End loop;


例子：
DECLARE cou NUMBER;
BEGIN -- 必须给一个初始值
  cou := 1;
  WHILE(cou<10) LOOP 
    DBMS_OUTPUT.put_line('cou = '||cou);
    cou := cou + 1;
  END LOOP;
END;


3、For循环
说明：循环变量被隐式定义为integer类型的局部变量，缺省情况下，循环变量从初始值以1的增量递增到结束值，如果使用关键字reverse，则循环从结束值以1的减量递减到初始值。
格式：
FOR 控制变量 in [REVERSE] 下限..上限LOOP
语句1；
语句2；
……
END LOOP；


循环控制变量是隐含定义的，不需要要声明。
下限和上限用于指明循环次数。正常情况下循环控制变量的取值由下限到上限递增，
REVERSE 关键字表示循环控制变量的取值由上限到下限递减。


例子：
declare
  v_l binary_integer:=1;
begin
  for v_loop in 1..10 loop
    dbms_output.put_line(v_loop);
--exit when (v_loop=8),加上这句则当v_loop=8时退出;exit则直接退出。
  end loop;
end;


declare
  v_l binary_integer:=1;
begin
  for v_loop in reverse 1..10 loop
    dbms_output.put_line(v_loop);
  end loop;
end;




九、存储过程的终止循环跳转语句（EXIT和EXIT WHEN）


1、关于EXIT和EXIT WHEN的区别：
EXIT：无条件终止语句，当遇到一个exit语句，则立即终止当前的循环。
EXIT WHEN：有条件终止语句，当遇到exit when语句时，首先检测when子句中的条件，为true则终止。


2、主要作用
提前退出当前循环
提前退出嵌套循环


带标号的loop循环语句，主要用于供内层的exit语句终止外层的循环
例如：


declare
  v_l binary_integer:=1;
begin
<<outer>>
  for i in 1..100 loop
    <<inter>>
    for v_loop in 1..10 loop
      dbms_output.put_line(v_loop);
      exit outer when outer.i*v_loop>=20;  --终止外层循环
      exit when v_loop=5;  --终止本层(内层)循环，等同于exit inter when v_loop=5
    end loop inter;
  end loop outer;
end;




十、存储过程的无条件跳转语句（GOTO语句）与NULL语句


1.GOTO语句概述
说明：GOTO为非结构化语句，跳转到某个标号，标号必须唯一，且标号后必须是一个可执行的语句或PLSQL块
限制：不能跳转到IF语句、loop语句、或子块中；不能从子程序中挑出；不能从异常处理跳转到可执行部分。


2.NULL语句概述
NULL语句不会执行任何操作，相当于一个占位符，并且会直接连接控制传递到下一条语句，使用NULL语句的好处是可以提高PL/SQL程序的可读性，使语句变得有意思合法。


例一：
DECLARE 
  eno emp.empno%TYPE; 
  sal emp.sal%TYPE; 
BEGIN 
  eno := &en;
  SELECT sal INTO sal FROM emp WHERE empno=eno;
  IF sal>3500 THEN goto po1;
  ELSIF sal>2000 THEN goto po2;
  ELSE goto po3;END IF;
<<po1>> DBMS_OUTPUT.put_line('高工资。。。'); 
<<po2>> DBMS_OUTPUT.put_line('中等工资。。'); 
<<po3>> DBMS_OUTPUT.put_line('底工资。。。');  --标号<<po3>>后面必须要跟着可执行的语句或PLSQL块
END ;


例二：如果跳转的后面没有什么需要执行的语句了怎么办？用null语句。
DECLARE 
  eno emp.empno%TYPE;
  sal emp.sal%TYPE; 
BEGIN 
  eno := &en; 
  SELECT sal INTO sal FROM emp WHERE empno=eno ; 
  IF sal>3500 THEN goto po1 ; 
  ELSIF sal>2000 THEN goto po2 ; 
  ELSE goto po3 ; END IF ; 
<<po1>> DBMS_OUTPUT.put_line('高工资。。。') ; 
<<po2>> DBMS_OUTPUT.put_line('中等工资。。') ; 
<<po3>> null;  --没有null则语法错误，null显式指定一个什么也不做的操作，相当于一个占位符，使语句变得有意义合法。
END ;




十一、存储过程细节
1、删除存储过程
drop procedure procedure_name;


2、有关存错过程中使用到的集合数据类型(PL/SQL表、索引表、嵌套表、可变数组)、游标（普通游标、参数游标、游标变量、循环选择）、异常处理等在本博客的其他文章做专题详述。