Oracle存储过程

原创
2014/05/14 18:33
阅读数 57
 
先来一个简单的例子,有个感性认识.
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存储过程的调用方式介绍完毕。

本文出自 “wIsper 把技术做成艺术” 博客,请务必保留此出处http://lichen.blog.51cto.com/697816/169020

展开阅读全文
打赏
0
2 收藏
分享
加载中
更多评论
打赏
0 评论
2 收藏
0
分享
返回顶部
顶部