四、存储过程创建语法

1. 定义

所谓存储过程( )是一组用于完成特定数据库功能的SQL语句，在数据库系统中被编译并存储。 使用时，用户通过指定存储过程名称并给出相应的存储过程参数来调用并执行所定义的存储过程，从而完成一个或一系列数据库操作。

2.存储过程创建

存储过程由三部分组成：过程声明、执行过程部分和存储过程异常。

2.1 无参数存储过程语法

create or replace procedure NoParPro  
 as  //声明  
 ;  
 begin // 执行  
 ;  
 exception//存储过程异常  
 ;  
 end;  

2.2 带参数的存储过程实例

create or replace procedure queryempname(sfindno emp.empno%type)   
as  
   sName emp.ename%type;  
   sjob emp.job%type;  
begin  
       ....  
exception  
       ....  
end; 

2.3 带参数的存储过程包括赋值方法

create or replace procedure runbyparmeters    
    (isal in emp.sal%type,   
     sname out varchar,  
     sjob in out varchar)  
 as   
    icount number;  
 begin  
      select count(*) into icount from emp where sal>isal and job=sjob;  
      if icount=1 then  
        ....  
      else  
       ....  
     end if;  
exception  
     when too_many_rows then  
     DBMS_OUTPUT.PUT_LINE('返回值多于1行');  
     when others then  
     DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');  
end;  

其中参数IN代表输入参数，为参数的默认模式。

OUT代表返回值参数，类型可以使用任何合法类型。

OUT模式定义的参数只能在过程体内赋值，这意味着该参数可以将值传递回调用过程

IN OUT 表示参数可以向进程传递一个值，或者将某个值传递出去。

2.4 在存储过程中使用游标定义

as //定义(游标一个可以遍历的结果集)   
CURSOR cur_1 IS   
  SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,  
         SUM(usd_amt)/10000 usd_amt_sn   
  FROM BGD_AREA_CM_M_base_T   
  WHERE ym >= vs_ym_sn_beg   
       AND ym <= vs_ym_sn_end   
  GROUP BY area_code,CMCODE;   
      
begin //执行（常用For语句遍历游标）       
FOR rec IN cur_1 LOOP   
  UPDATE xxxxxxxxxxx_T   
   SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn   
   WHERE area_code = rec.area_code   
   AND CMCODE = rec.CMCODE   
   AND ym = is_ym;   
END LOOP;  

2.5 光标定义

--显示cursor的处理
declare  
---声明cursor,创建和命名一个sql工作区
cursor cursor_name is  
    select real_name from account_hcz;
    v_realname varchar2(20);
begin 
    open cursor_name;---打开cursor,执行sql语句产生的结果集
    fetch cursor_name into v_realname;--提取cursor,提取结果集中的记录
    dbms_output.put_line(v_realname);
    close cursor_name;--关闭cursor
end;

3、调用存储过程 3.1 过程调用方法1

declare  
      realsal emp.sal%type;  
      realname varchar(40);  
      realjob varchar(40);  
begin   //过程调用开始  
      realsal:=1100;  
      realname:='';  
      realjob:='CLERK';  
      runbyparmeters(realsal,realname,realjob);－－必须按顺序  
      DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);  
END;  //过程调用结束 

3.2 过程调用方法2

declare  
     realsal emp.sal%type;  
     realname varchar(40);  
     realjob varchar(40);  
begin    //过程调用开始  
     realsal:=1100;  
     realname:='';  
     realjob:='CLERK';  
     －－指定值对应变量顺序可变  
     runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);           
    DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);  
END;  //过程调用结束

3.3 过程调用方法3（SQL命令行方式）

SQL>exec  proc_emp('参数1','参数2');//无返回值过程调用  

SQL>var vsal number  
SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用  

或者：

call proc_emp ('参数1',:vsal);// 有返回值过程调用 

四、存储过程创建语法

create [or replace] procedure 存储过程名（param1 in type，param2 out type）
as
变量1 类型（值范围）;
变量2 类型（值范围）;
Begin
    Select count(*) into 变量1 from 表A where列名=param1；
    If (判断条件) then
       Select 列名 into 变量2 from 表A where列名=param1；
       Dbms_output.Put_line(‘打印信息’);
    Elsif (判断条件) then
       Dbms_output.Put_line(‘打印信息’);
    Else
       Raise 异常名（NO_DATA_FOUND）;
    End if;
Exception
    When others then
       Rollback;
End;

五、注意事项 存储过程参数没有取值范围，in表示输入，out表示输出； 变量有一个值范围，后面跟一个分号； 在判断语句操作记录之前最好先使用count(*)函数判断该值是否存在； 使用...into...给变量赋值； 在代码中抛出异常并使用raise+异常名称； 5.1 命名异常

命名系统异常原因

6. 基本语法 6.1 基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
    --执行体
END 存储过程名字;

6.2 进入

将查询结果存储到变量中。 多个列可以同时存储在多个变量中。 必须有记录，否则会抛出异常（没有记录则抛出）

例子：

  BEGIN
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;

6.3 IF判断

 IF V_TEST = 1 THEN
    BEGIN 
       do something
    END;
  END IF;

6.4 while循环

  WHILE V_TEST=1 LOOP
  BEGIN
    XXXX
  END;
  END LOOP;

6.5 变量赋值

 V_TEST := 123;

6.6 使用 for in

  IS
  CURSOR cur IS SELECT * FROM xxx;
  BEGIN
 FOR cur_result in cur LOOP
  BEGIN
   V_SUM :=cur_result.列名1+cur_result.列名2
  END;
 END LOOP;
  END;

6.7 带参数

  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
  OPEN C_USER(变量值);
FETCH C_USER INTO V_NAME;
  EXIT WHEN FETCH C_USER%NOTFOUND;
CLOSE C_USER;

6.8 使用pl/sql调试

连接数据库后创建一个Test，在窗口中输入调用SP的代码，F9开始调试，CTRL+N单步调试

7.关于存储过程的一些问题的说明

中，不能添加数据表别名，如：

select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误

在存储过程中，当使用某个字段时，必须在其后面加上into。 如果整个记录都使用游标，那就另当别论了。

select af.keynode into kn from APPFOUNDATION af 
   where af.appid=aid and af.foundationid=fid;-- 有into，正确编译
select af.keynode from APPFOUNDATION af 
 where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement

使用...into...语法时，首先要保证数据库中存在该记录，否则会报“no data found”异常。

在该语法之前，使用 count(*) from 检查数据库中是否存在该记录，如果存在，则使用 ...into...

存储过程中别名不能与字段名相同，否则虽然编译可以通过，但运行时会报错

 --正确
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;
--错误
select af.keynode into kn from APPFOUNDATION af 
 where af.appid=appid and af.foundationid=foundationid;
-- 运行阶段报错，提示ORA-01422:exact fetch returns more than requested number of rows

存储过程中，关于null的问题

假设有一个表A，定义如下：

create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外键 
);

如果在存储过程中，请使用以下语句：

select sum(vcount) into fcount from A where bid='xxxxxx';

如果A表中没有bid=""记录，那么= null（即使定义时设置了默认值，如：（8）：= 0仍然无效，仍然会变成null），这样就有可能有问题，所以最好先在这里判断：

if fcount is null then
    fcount:=0;
end if;

调用存储过程

this.pnumberManager.getHibernateTemplate().execute(
   new HibernateCallback() {
       public Object doInHibernate(Session session)
               throws HibernateException, SQLException {
           CallableStatement cs = session
                   .connection()
                   .prepareCall("{call modifyapppnumber_remain(?)}");
           cs.setString(1, foundationid);
           cs.execute();
           return null;
       }
   });