Oracle 存储过程入门

创建测试表：

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;

查看结果：