Oracle存储过程学习笔记

存储过程动态调用存储过程

stmt:='call '||spname||' (:param1,:param2)';
execute immediate stmt using param1,param2

如果参数中有in, out 参数，则要写成

execute immediate stmt using in out param1,param2;

如果已知过程名，刚直接写成  package_name.sp_name  即可，不用加call。

存储过程动态调用SQL

 sql_text:='select * from '||p_table_name||' where rowid='''||p_rowid||''' and app_cd='''||p_appCode||'''';
  open p_CUR for sql_text;

 execute immediate 'delete from '||p_table_name||' where '||p_delete_condition;
  execute immediate 'insert into '||p_table_name||'('||p_columns||') values('||p_values||')';
  commit;

字符串连接符为||，转义符为单引号，如果要插入单引号则需要再加一个单引号转义。

将动态SQL中select出的内容存入变量

execute immediate  'select upper(status)  from '|| p_TABLE|| ' where rowid ='''||p_rowid||'''' into v_status ;

存储过程包里常量定义方法

C_BASKET_CREATION   constant varchar2(30) := 'BASKET_CREATION';

函数头定义

function CALC_BUSINESS_DATE(
  p_fund in varchar2,
  p_trade_date in date
)
return date;

写函数体的话分号去掉，后加as,begin,end类似过程编写。

存储过程抛出异常

begin
        ...
exception 
    when NO_DATA_FOUND then
    RAISE_APPLICATION_ERROR(-20820,'Calculate Business Date failed!');
end;

存储过程tips

过程中的临时变量，如果用来存放动态SQL的文本varchar2，则后面要加长度。

临时变量的类型最好写成 table_name.column_name%TYPE 形式，防止表列更改引起的类型更改

游标类型可写成SYS_REFCURSOR，为Oracle内置。

取sequence next value 方法：

select UTL_PID_SEQ.nextval into v_pid from dual;

使用insert语句插入字符串时，若串中有‘&’符号，需要写成‘abc’||chr(38)||'def'形式才能插入，如果是在程序中使用PreparedStatement语句插入，则无此问题。