oracle 存储过程的基本语法 及注意事项

oracle 存储过程的基本语法

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，如：

selecta.appnamefromappinfoa;--正确
selecta.appnamefromappinfoasa;--错误

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

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

selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--有into，正确编译
selectaf.keynodefromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--没有into，编译报错，提示：Compilation
Error:PLS-00428:anINTOclauseisexpectedinthisSELECTstatement

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

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

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

selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;--正确运行
selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid=foundationid;--运行阶段报错，提示
ORA-01422:exactfetchreturnsmorethanrequestednumberofrows

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

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

createtableA(
idvarchar2(50)primarykeynotnull,
vcountnumber(8)notnull,
bidvarchar2(50)notnull--外键
);

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

selectsum(vcount)intofcountfromAwherebid='xxxxxx';

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

iffcountisnullthen
fcount:=0;
endif;

这样就一切ok了。

6.Hibernate调用oracle存储过程

this.pnumberManager.getHibernateTemplate().execute(
newHibernateCallback(){
publicObjectdoInHibernate(Sessionsession)
throwsHibernateException,SQLException{
CallableStatementcs=session
.connection()
.prepareCall("{callmodifyapppnumber_remain(?)}");
cs.setString(1,foundationid);
cs.execute();
returnnull;
}
});

在大型数据库系统中，有两个很重要作用的功能，那就是存储过程和触发器。在数据库系统中无论是存储过程还是触发器，都是通过SQL 语句和控制流程语句的集合来完成的。相对来说，数据库系统中的触发器也是一种存储过程。存储过程在数据库中运算时自动生成各种执行方式，因此，大大提高了对其运行时的执行速度。在大型数据库系统如Oracle、SQL Server中都不仅提供了用户自定义存储过程的功能，同时也提供了许多可作为工具进行调用的系统自带存储过程。
所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL 语句集，该SQL语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。
由于J2EE体系一般建立大型的企业级应用系统，而一般都配备大型数据库系统如Oracle或者SQL Server，在本文《JAVA与Oracle存储过程》中将介绍JAVA跟Oracle存储过程之间的相互应用跟相互间的各种调用。
一、JAVA调用Oracle存储过程
JAVA跟Oracle之间最常用的是JAVA调用Oracle的存储过程，以下简要说明下JAVA如何对Oracle存储过程进行调用。
Ⅰ、不带输出参数情况
过程名称为pro1，参数个数1个，数据类型为整形数据

importjava.sql.*;
publicclassProcedureNoArgs
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载Oracle驱动
DriverManager.registerDriver(neworacle.jdbc.driver.OracleDriver());
//获得Oracle数据库连接
Connectionconn=DriverManager.getConnection("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd");

//创建存储过程的对象
CallableStatementc=conn.divpareCall("{callpro1(?)}");

//给Oracle存储过程的参数设置值，将第一个参数的值设置成188
c.setInt(1,188);

//执行Oracle存储过程
c.execute();
conn.close();
}
}

Ⅱ、带输出参数的情况
过程名称为pro2，参数个数2个，数据类型为整形数据，返回值为整形类型

importjava.sql.*;
publicclassProcedureWithArgs
{
publicstaticvoidmain(Stringargs[])throwsException
{
//加载Oracle驱动
DriverManager.registerDriver(neworacle.jdbc.driver.OracleDriver());
//获得Oracle数据库连接
Connectionconn=DriverManager.getConnection("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd");

//创建Oracle存储过程的对象，调用存储过程
CallableStatementc=conn.divpareCall("{callpro2(?,?)}");

//给Oracle存储过程的参数设置值，将第一个参数的值设置成188
c.setInt(1,188);
//注册存储过程的第二个参数
c.registerOutParameter(2,java.sql.Types.INTEGER);//执行Oracle存储过程
c.execute();
//得到存储过程的输出参数值并打印出来
System.out.println (c.getInt(2));
conn.close();
}
}

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

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

1createorreplaceprocedureNoParPro
2as;
3begin
4;
5exception //存储过程异常
6;
7end;
8

二、带参存储过程实例

1createorreplaceprocedurequeryempname(sfindnoemp.empno%type)as
2sNameemp.ename%type;
3sjobemp.job%type;
4begin
5 ....
7exception
....
14end;
15

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

1createorreplaceprocedurerunbyparmeters(isalinemp.sal%type,
snameoutvarchar,sjobinoutvarchar)
2asicountnumber;
3begin
4selectcount(*)intoicountfromempwheresal>isalandjob=sjob;
5ificount=1then
6 ....
9else
10 ....
12endif;
13exception
14whentoo_many_rowsthen
15DBMS_OUTPUT.PUT_LINE('返回值多于1行');
16whenothersthen
17DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
18end;
19

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

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

过程调用方式二

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