Oracle 存储过程入门

发布时间：2020-12-12 15:39:38 所属栏目：百科来源：网络整理

导读：创建测试表： createtable AAA_TABLE ( BALANCENUMBER, ACCOUNT_IDVARCHAR2(20) ) 创建测试存储过程： CREATEORREPLACEPROCEDUREProc_APPP(acc_noINString,amountINNUMBER)AS BEGIN UPDATEAAA_TABLESETbalance=balance+amount WHEREaccount_id=acc_no; END;

创建测试表： 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没问题。
2、本地动态SQL执行DML语句。需求：将用户输入的值插入到上例中建好的AAA_t表中。 createorreplaceprocedureproc_insert( idinnumber, nameinvarchar2 )as str_sqlvarchar2(500); begin str_sql:='insertintoAAA_Tvalues(:1,:2)';--动态执行插入操作 executeimmediatestr_sqlusingid,name; exception whenothers thennull; end;
然后编译，如果发现编译错误，那么点击存储过程查看语句错误。若成功，可直接测试中输入参数；
也可在命令行执行：
SQL>executeproc_insert(1,'Jom'); PL/SQLproceduresuccessfullycompleted SQL>commit;
查看结果：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!