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存储过程的若干问题备忘
selecta.appname fromappinfo asa; -- 错误
selectaf.keynode fromAPPFOUNDATIONaf whereaf.appid =aid andaf.foundationid =fid; -- 没有into,编译报错,提示:Compilation
Error:PLS - 00428:an INTOclause isexpected inthis SELECTstatement
selectaf.keynode intokn fromAPPFOUNDATIONaf where af.appid = appid and af.foundationid = foundationid; -- 运行阶段报错,提示
ORA - 01422:exact fetch returnsmorethanrequested number ofrows
id varchar2( 50) primary key not null,
vcount number( 8) not null,
bid varchar2( 50) not null -- 外键
);
fcount:=0;
end if;
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个,数据类型为整形数据
public classProcedureNoArgs
{
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个,数据类型为整形数据,返回值为整形类型
public classProcedureWithArgs
{
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存储过程可以有无参数存储过程和带参数存储过程。
一、无参程序过程语法
2 as ;
3 begin
4 ;
5exception //存储过程异常
6 ;
7 end;
8
二、带参存储过程实例
2sNameemp.ename %type;
3sjobemp.job %type;
4 begin
5 ....
7exception
....
14 end;
15
三、 带参数存储过程含赋值方式
snameout varchar,sjob inout varchar)
2 asicount number;
3 begin
4 select count( *) intoicount fromemp wheresal >isal andjob =sjob;
5 ificount = 1 then
6 ....
9 else
10 ....
12 end if;
13exception
14 whentoo_many_rows then
15DBMS_OUTPUT.PUT_LINE( ' 返回值多于1行 ');
16 whenothers then
17DBMS_OUTPUT.PUT_LINE( ' 在RUNBYPARMETERS过程中出错! ');
18 end;
19
四、在Oracle中对存储过程的调用
过程调用方式一
2realsalemp.sal %type;
3realname varchar( 40);
4realjob varchar( 40);
5 begin //存储过程调用开始
6realsal: = 1100;
7realname: = '';
8realjob: = ' CLERK ';
9runbyparmeters(realsal,realname,realjob); --必须按顺序
10DBMS_OUTPUT.PUT_LINE(REALNAME || ' ' ||REALJOB);
11 END; //过程调用结束
12
过程调用方式二
2realsalemp.sal %type;
3realname varchar( 40);
4realjob varchar( 40);
5 begin//过程调用开始
6realsal: = 1100;
7realname: = '';
8realjob: = ' CLERK ';
9runbyparmeters(sname =>realname,isal =>realsal,sjob =>realjob); --指定值对应变量顺序可变
10DBMS_OUTPUT.PUT_LINE(REALNAME || ' ' ||REALJOB);
11 END; //过程调用结束
12