需求背景:对有些数据库操作较多的功能算法需求,或使用应用程序代码多次连接操作数据,会造成较大性能损耗,或者系统动态参数化如要查询的表名是存在数据库里的,为节减网络连接开销,建议使用存储过程实现数据库操作需求的算法。动态sql ,意思是sql语句并不是事先明确的,而是可根据参数变化的,通常是我们在存储过程中拼接生成sql字符串,并执行这个字符串返回结果。
1.创建存储过程语法:
CREATE OR REPLACE PROCEDURE 存储过程名称( 参数列表)
Is
变量定义;
Begin
逻辑实现;
End 存储过程名称;
例子:
CREATE OR REPLACE PROCEDURE GZGPORTAL.POSITION_JOB_PAY(
orgUnitLongNumber varchar2,
isSub char,
beginYearMonth varchar2,
endYearMonth varchar2,
isPosition char,
returnSet OUT sys_refcursor //返回值 ,游标类型,可用于返回结果集
)
IS
horTableName varchar2(30);
hisTableName varchar2(30);
insertSQL VARCHAR2(2000);
Begin
。。。。。
End POSITION_JOB_PAY ;
2.存储过程动态sql
sqlstr := 'delete t_temp_orgSecheme' ;
EXECUTE IMMEDIATE (sqlstr);
Commit;
3.执行动态sql 返回结果集
returnSet OUT sys_refcursor ; 定义返回值变量,游标类型
open returnSet for
select * from table;
return returnSet;