创建测试表:
create table AAA_TABLE
(
BALANCE NUMBER,
ACCOUNT_ID VARCHAR2(20)
)
创建测试存储过程:
CREATE OR REPLACE PROCEDURE Proc_APPP(acc_no IN String,amount IN NUMBER) AS
BEGIN
UPDATE AAA_TABLE SET balance = balance + amount
WHERE account_id = acc_no;
END;
关于Oracle存储过程的若干问题备忘
1.在Oracle中,数据表别名不能加as。
如:
select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误
也许,是怕和Oracle中的存储过程中的关键字as冲突的问题吧
2. 在存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录,利用游标的话就另当别论了。
select af.keynode into kn from APPFOUNDATION af
where af.appid=aid and af.foundationid=fid; -- 有into,正确编译
select af.keynode
from APPFOUNDATION af
where af.appid=aid and af.foundationid=fid;-- 没有into,编译报错,
提示:Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement
3. 在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"no data found"异常。
可以在该语法之前,先利用select count(*) from 查看数据库中是否存在该记录,如果存在,再利用select...into...
4. 在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid; -- 正确运行
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;
-- 运行阶段报错,提示: ORA-01422:exact fetch returns more than requested number of rows
5. 在存储过程中,关于出现null的问题
假设有一个表A,定义如下: create table A(
id varchar2(50) primary key not null, vcount number(8) not null,
bid varchar2(50) not null -- 外键
);
如果在存储过程中,使用如下语句:
select sum(vcount) into fcount from A where bid='xxxxxx'; 如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,如:fcount number(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下: if fcount is null then fcount:=0; end if;
这样就一切ok了
6.Hibernate调用Oracle存储过程
this.pnumberManager.getHibernateTemplate().execute(
new HibernateCallback() ...{
public Object doInHibernate(Session session) throws HibernateException, SQLException ...{
CallableStatement cs = session .connection().prepareCall("{callmodifyapppnumber_remain(?)}");
cs.setString(1, foundationid);
cs.execute();
return null;
}
});
本地动态sql
本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。
1、 本地动态SQL执行DDL语句:
需求:根据用户输入的表名及字段名等参数动态建表。
create or replace procedure proc_test(
table_name in varchar2,
field1 in varchar2,
datatype1 in varchar2,
field2 in varchar2,
datatype2 in varchar2
)as
str_sql varchar2(500);
begin
str_sql:='create table '||table_name||'('||field1||' '||datatype1||','||field2||' '||datatype2||')';
execute immediate str_sql;
exception
when others then
null;
end;
然后编译,如果发现编译错误,那么点击存储过程查看语句错误。
若成功,可直接测试中输入参数;
也可在命令行执行:
SQL> execute proc_test('AAA_t','id','number(8) not null','name','varchar2(100)');
PL/SQL procedure successfully completed
SQL>
建表成功,证明存储过程动态执行ddl没问题。
2、 本地动态SQL执行DML语句。
需求:将用户输入的值插入到上例中建好的AAA_t表中。
create or replace procedure proc_insert(
id in number,
name in varchar2
)as
str_sql varchar2(500);
begin
str_sql:='insert into AAA_T values(:1,:2)';--动态执行插入操作
execute immediate str_sql using id,name;
exception
when others
then null;
end;
然后编译,如果发现编译错误,那么点击存储过程查看语句错误。
若成功,可直接测试中输入参数;
也可在命令行执行:
SQL> execute proc_insert(1,'Jom');
PL/SQL procedure successfully completed
SQL> commit;
查看结果: