Oracle 存储过程的基本语法

存储 存储软件
在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"no data found"异常。可以在该语法之前,先利用select count(*) from 查看数据库中是否存在该记录,如果存在,再利用select...into...

1.基本结构

  1. CREATE OR REPLACE PROCEDURE 存储过程名字 
  2.    参数1 IN NUMBER, 
  3.    参数2 IN NUMBER 
  4. IS 
  5. 变量1 INTEGER :=0; 
  6. 变量2 DATE
  7. BEGIN 
  8.  
  9. END 存储过程名字 

2.SELECT INTO STATEMENT

将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

例子:

  1. BEGIN 
  2.  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; 
  3.  EXCEPTION 
  4.  WHEN NO_DATA_FOUND THEN 
  5.      xxxx; 
  6.  END
  7.  ... 

3.IF 判断

  1. IF V_TEST=1 THEN 
  2.    BEGIN  
  3.       do something 
  4.    END
  5.  END IF; 

4.while 循环

  1. WHILE V_TEST=1 LOOP 
  2.  BEGIN 
  3. XXXX 
  4.  END
  5.  END LOOP; 

5.变量赋值

  1. V_TEST := 123; 

6.用for in 使用cursor

  1. ... 
  2.  IS 
  3.  CURSOR cur IS SELECT * FROM xxx; 
  4.  BEGIN 
  5. FOR cur_result in cur LOOP 
  6.  BEGIN 
  7.   V_SUM :=cur_result.列名1+cur_result.列名2 
  8.  END
  9. END LOOP; 
  10.  END

7.带参数的cursor

  1. CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; 
  2.  OPEN C_USER(变量值); 
  3.  LOOP 
  4. FETCH C_USER INTO V_NAME; 
  5. EXIT FETCH C_USER%NOTFOUND; 
  6.    do something 
  7.  END LOOP; 
  8.  CLOSE C_USER; 

8.用pl/sql developer debug

连接数据库后建立一个Test WINDOW

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

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

1.在oracle中,数据表别名不能加as,如:

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

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

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

  1. select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid; 
  2. -- 有into,正确编译 
  3.  
  4. select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid; 
  5. -- 没有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.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错

  1. select keynode into kn from APPFOUNDATION  
  2.       where appid=aid and foundationid=fid;-- 正确运行 
  3.  
  4.  
  5. select af.keynode into kn from APPFOUNDATION af  
  6.       where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错 

提示:ORA-01422:exact fetch returns more than requested number of rows[[245922]]

 

5.在存储过程中,关于出现null的问题

假设有一个表A,定义如下:

  1. create table A( 
  2. id varchar2(50) primary key not null
  3. vcount number(8) not null
  4. bid varchar2(50) not null -- 外键  
  5. ); 

如果在存储过程中,使用如下语句:

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

如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,如:fcount number(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里***先判断一下:

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

这样就一切ok了。

6.Hibernate调用oracle存储过程

  1. this.pnumberManager.getHibernateTemplate().execute
  2.                new HibernateCallback() { 
  3.                    public Object doInHibernate(Session session) 
  4.                            throws HibernateException, SQLException { 
  5.                        CallableStatement cs = session 
  6.                                .connection() 
  7.                                .prepareCall("{call modifyapppnumber_remain(?)}"); 
  8.                        cs.setString(1, foundationid); 
  9.                        cs.execute(); 
  10.                        return null
  11.                    } 
  12.                }); 

 

责任编辑:武晓燕 来源: 博客园
相关推荐

2018-08-28 11:40:47

存储过程语法

2011-05-19 14:29:50

Oracle存储语法

2010-04-16 09:03:28

Oracle 存储过程

2010-10-29 16:33:45

ORACLE存储过程

2010-04-06 10:32:12

Oracle递归查询

2010-04-16 10:11:20

Oracle存储过程

2010-04-07 13:12:25

Oracle存储过程

2010-06-01 15:09:55

MySQL 存储过程

2010-04-07 12:08:28

Oracle存储过程

2010-04-15 17:31:10

Oracle存储过程

2010-04-08 16:41:29

Oracle存储过程

2010-11-16 14:30:32

Oracle存储过程

2010-10-29 16:06:55

Oracle存储过程

2010-04-08 18:10:37

Oracle存储过程

2010-04-07 14:01:40

Oracle存储过程

2017-09-01 21:25:45

MySQL存储过程

2010-11-19 11:32:25

Oracle存储过程

2011-04-13 09:45:15

Oracle存储

2011-04-15 10:56:22

2009-03-25 10:48:08

存储银行Oracle
点赞
收藏

51CTO技术栈公众号