Oracle存储过程

原创作品，允许转载，转载时请务必以超链接形式标明文章  原始出处 、作者信息和本声明。否则将追究法律责任。 http://lichen.blog.51cto.com/697816/169020

 

先来一个简单的例子,有个感性认识.

create  or  replace  procedure test  is 
aa number; 
begin    
select  count(*)  into aa  from t_server_server; 
DBMS_OUTPUT.PUT_LINE(aa); 
insert  into test_test  values(1, 'aaa'); 
commit; 
end test;

主要是查询,打印,插入.

以下是转载

1.基本结构 
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT
  将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
  记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
  例子： 
  BEGIN
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;
  ...

3.IF 判断
  IF V_TEST=1 THEN
    BEGIN 
       do something
    END;
  END IF;

4.while 循环
  WHILE V_TEST=1 LOOP
  BEGIN
 XXXX
  END;
  END LOOP;

5.变量赋值
  V_TEST := 123;

6.用for in 使用cursor
  ...
  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;

7.带参数的cursor
  CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
  OPEN C_USER(变量值);
  LOOP
 FETCH C_USER INTO V_NAME;
 EXIT FETCH C_USER%NOTFOUND;
    do something
  END LOOP;
  CLOSE C_USER;

8.用pl/sql developer debug
  连接数据库后建立一个Test WINDOW
  在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

关于oracle存储过程的若干问题备忘

1.在oracle中，数据表别名不能加as，如：

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

 也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

   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

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。

   可以在该语法之前，先利用 select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

  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

5.在存储过程中，关于出现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="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：

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

这样就一切ok了。

6.Hibernate调用oracle存储过程

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

Oracle存储过程包含三部分： 过程 声明，执行过程部分，存储过程异常。

Oracle存储过程可以有无参数存储过程和带参数存储过程。 
一 、无参程序过程语法

1 create   or   replace  procedure  NoParPro
2 as   ;
3 begin
4  ;
5  exception     //存储过程异常
6      ;
7 end ;
8 

        二、带参存储过程实例

 1 create   or   replace  procedure  queryempname(sfindno emp.empno % type) as
 2        sName emp.ename% type;
 3        sjob emp.job% type;
 4 begin
 5        ....
 7 exception
          ....
14 end ;
15 

    三、 带参数存储过程含赋值方式

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

  四、在Oracle中对存储过程的调用
  过程调用 方式一

 1 declare
 2        realsal emp.sal% type;
 3        realname varchar(40 );
 4        realjob varchar(40 );
 5  begin   //存储过程调用开始
 6        realsal:=1100 ;
 7        realname:='' ;
 8        realjob:='CLERK' ;
 9        runbyparmeters(realsal,realname,realjob);     －－ 必须按顺序
10        DBMS_OUTPUT.PUT_LINE(REALNAME||'   '|| REALJOB);
11  END ;  //过程调用结束
12 

  过程调用方式二

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

 至此，有关ORACLE的基本存储过程以及对Oracle存储过程的调用方式介绍完毕。

本文出自 “李晨专栏” 博客，请务必保留此出处http://lichen.blog.51cto.com/697816/169020