Oracle存储过程

Oracle存储过程语法：

create or replace procedure 存储过程名（param1 in type，param2 out type）

as

变量1 类型（值范围）;
变量2 类型（值范围）;
begin

    select count(*) into 变量1 from 表名 where 列名=param1
    ......
end 存储过程名字



注意事项

1、存储过程参数不带取值范围，in表示传入，out表示输出

2、变量带取值范围，后面接分号

3、在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4、用select 。。。into。。。给变量赋值

5、在代码中抛异常用 raise+异常名



举例：

无参存储过程

create or replace procedure test
is
--设置变量total默认值
total number:=0;
begin  
--给变量total赋值
select count(*) into total from ycxk_apply_card;
--输出总条数total
DBMS_OUTPUT.PUT_LINE(total);
--操作insert、update、delete才需要提交
--commit;
end test;

带参存储过程

create or replace procedure addYcxkAreaCode
(
    province_code in varchar2,
    area_code in varchar2,
    area_name in varchar2
)
is
begin
    insert into ycxk_area_code values (province_code, area_code, area_name);
    commit;
end;

--------------------------------

以下为Oracle存储过程知识点总结

1、用select 。。。into。。。给变量赋值

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录则抛出NO_DATA_FOUND)
例子：
begin
  select col1, col2 into 变量1, 变量2 from test where id=1;
  exception
  when no_data_found then  xxxx;
end;



2、if判断

if test=1 then
    begin

      do something;
    end;
end if;


3、while 循环
while test=1 loop

    begin

      do somathing;

    end;
end loop;


4、变量赋值: test:=1;



5、在oracle中，数据表别名不能加as，如：

select a.appname from appinfo a; -- 正确
select a.appname from appinfo as a; -- 错误
也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧


6、在存储过程中，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


7、在使用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。
可以在该语法之前，先利用select count(*) from查看数据库中是否存在该记录，如果存在，select...into...



8、在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

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


9、在存储过程中，关于出现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了。


10、Hibernate调用oracle存储过程

this.pnumberManager.getHibernateTemplate().execute(new HibernateCallback() {
    public Object doInHibernate(Session session) throws HibernateException, SQLException {
        CallableStatement cs = session.connection()

            .prepareCall("{call addYcxkAreaCode(?)}");
        cs.setString(1, "123");
        cs.execute();
        return null;
    }
});