创建测试表：
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(
newHibernateCallback()...{
publicObjectdoInHibernate(Sessionsession)throwsHibernateException,sqlException...{
CallableStatementcs=session.connection().prepareCall("{callmodifyapppnumber_remain(?)}");
cs.setString(1,foundationid);
cs.execute();
returnnull;
}
});
本地动态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;
查看结果：
