创建测试表:
createtable AAA_TABLE
(
BALANCENUMBER,
ACCOUNT_IDVARCHAR2(20)
)
创建测试存储过程:
CREATEORREPLACEPROCEDUREProc_APPP(acc_noINString,amountINNUMBER)AS
BEGIN
UPDATEAAA_TABLESETbalance=balance+amount
WHEREaccount_id=acc_no;
END;
关于Oracle存储过程的若干问题备忘
1.在Oracle中,数据表别名不能加as。
如:
selecta.appnamefromappinfoa;--正确
selecta.appnamefromappinfoasa;--错误
也许,是怕和Oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录,利用游标的话就另当别论了。
selectaf.keynodeintoknfromAPPFOUNDATIONaf
whereaf.appid=aidandaf.foundationid=fid;--有into,正确编译
selectaf.keynode
fromAPPFOUNDATIONaf
whereaf.appid=aidandaf.foundationid=fid;--没有into,编译报错,
提示:CompilationError:PLS-00428:anINTOclauseisexpectedinthisSELECTstatement
3.在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"nodatafound"异常。
可以在该语法之前,先利用selectcount(*)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定义时设置了默认值,如:fcountnumber(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:iffcountisnullthenfcount:=0;endif;
这样就一切ok了
6.Hibernate调用Oracle存储过程
this.pnumberManager.getHibernateTemplate().execute(
@H_403_160@newHibernateCallback()...{
publicObjectdoInHibernate(Sessionsession)throwsHibernateException,sqlException...{
CallableStatementcs=session.connection().prepareCall("{callmodifyapppnumber_remain(?)}");
cs.setString(1,foundationid);
cs.execute();
@H_403_160@return@H_403_160@null;
}
});
本地动态sql
本地动态sql是使用EXECUTEIMMEDIATE语句来实现的。
1、本地动态sql执行DDL语句:
需求:根据用户输入的表名及字段名等参数动态建表。
createorreplaceprocedureproc_test(
table_nameinvarchar2,
field1invarchar2,
datatype1invarchar2,
field2invarchar2,
datatype2invarchar2
)as
str_sqlvarchar2(500);
begin
str_sql:='createtable'||table_name||'('||field1||''||datatype1||','||field2||''||datatype2||')';
executeimmediatestr_sql;
exception
whenothersthen
null;
end;
若成功,可直接测试中输入参数;
也可在命令行执行:
sql>executeproc_test('AAA_t','id','number(8)notnull','name','varchar2(100)');
PL/sqlproceduresuccessfullycompleted
sql>
建表成功,证明存储过程动态执行ddl没问题。
createorreplaceprocedureproc_insert(
idinnumber,
nameinvarchar2
)as
str_sqlvarchar2(500);
begin
str_sql:='insertintoAAA_Tvalues(:1,:2)';--动态执行插入操作
executeimmediatestr_sqlusingid,name;
exception
whenothers
thennull;
end;
查看结果: