oracle的储存过程学习

一、储存过程适用范围：将数据表中表A的数据组装写入导入表B中，运行速度很快。

二、储存过程包含体、头和方法

1.头的基本语法

-------头部申明
create or replace package PKG_DATA is PRAGMA SERIALLY_REUSABLE;
--------声明一种类型为T_CURSOR,可输出为一个对象
TYPE T_CURSOR IS REF CURSOR;
------输出的类型为T_CURSOR类型,输入类型为P_DATE
PROCEDURE GET_DATA(P_DATE IN DATE,P_PROCESS_CODE OUT VARCHAR2,p_cursor OUT T_CURSOR);
------头部结束
end PKG_DATA;

// todo
方法头的声明

2.体的基本语法

create or replace package body PKG_SEC_DATA is 
 PRAGMA SERIALLY_REUSABLE;
 PROCEDURE GET_DATA (p_cursor OUT T_CURSOR)  is 
 v_limit number;
 BEGIN
select nvl(to_number(C.config_value),1000) 
into vlimit
from SEC_INFO c
where c.config_type = 'sec_gen'
and c.config_id = 'sec_single_num' 
for update;

update sec_list
set flag = ‘98’
where falg = '00'
and date_updated > sysdate -3
and rownum <= v_limit;

open p_cursor for 
select t.id reportId,
t.policy_no reportNo,
t.PRT_DATR bizDateStr
from SEC_LIST
WHERE t.flag = '98'
and date_updated >sysdate - 3;

update REPORT_LIST t
set t.falg= '99'
where t.falg= '98'
and DATE_UPDATED >sysdate - 3;

commit;
EXCEPTION
where OTHERS THEN 
ROLLBACK;------事务回滚
RAISE；-----将异常抛出去
	end GET_DATA ;
end PKG_SEC_DATA;

3.方法的基本语法

关键字function
①头的申明

function add (V_INPUT in varchar2) return NUMBER;

②体的语法

Function FUNC_TIMES (V_INPUT in varchar2) RETURN NUMBER is 
v_due_times NUMBER := 0;
cursor c_over_times(P_ACCT_NO VARCHAR2,P_DATE DATE) IS 
select count(distinct log.plan_id) over_due_times from cfs_plan_log log where log.acct_no = P_ACCT_NO AND log.change_type = '00' AND log.product_mode = 'PL_WS';
begin 
<<v_overdue_time_analysis>>
FOR v_overdue_times in c_over_times(P_ACCT_NO,P_DATE) loop
v_due_times := nvl(v_overdue_times.over_due_times,0);
end loop v_overdue_time_analysis;
return v_due_times;
end FUNC_TIMES;

4.游标的使用与变量的使用

① 储存过程从begin开始，游标用到时才触发

--方法开始，声明变量
v_potout_amt  NUMBER (12,2);


CURSOR v_putout_info(p_acct_no VARCHAR2,p_date DATE) IS

---客户名字、年龄、放款金额
SELECT  custName,age,putoutAmt from cust_info;
--执行从begin开始

begin
--游标v_putout_info 

<<v_putput_amt_analysis>>
FOR v_putout_info in c_putout_amt (p_acct_no,p_date) loop
v_potout_amt := nvl (c_putout_amt.putoutAmt,0);---变量

END loop v_putout_amt_analysis;

② 批量插入的写法

inset into SIGN_CHANGE
(created_by,
code,
FIR_BUSS_NUM)
(select 'ZHANGWEI',
'202'
t.policy_no) 
from 
(select t.policy_no from PHDRP_SEC_LB
where t.finish_date is null
)

5.存储过程定义数据类型

declare
type IdList is table of SEC_LIST_FD.ID_SEC_LIST_FD%type;
v_id Idlist;
cursor mycursor is select h.ID_SEC_LIST_FD from 
SEC_LIST_FD where h.flag = '00'

三、储存过程的调用

1.sql层

xml配置文件

<parameterMap id = "reportDataParamMap" class = "Map" >
	<parameter property = "result" javaType = "java.sql.ResultSet" jdbcType = "ORACLECURSOR" mode = "OUT"/>
	<parameter property = "P_DATE" javaType = "java.lang.String" jdbcType = "VARCHAR" mode = "IN"/>
	<parameter property = "P_PROCESS_CODE" javaType = "java.lang.String" jdbcType = "VARCHAR" mode = "OUT"/>
	<parameter property = "P_PROCESS_MSG" javaType = "java.lang.String" jdbcType = "VARCHAR" mode = "OUT"/>
<parameterMap/>

<proceduce id = "runSignDate" parameterMap = "reportDataParamMap">---parameterMap对应xnl文件配置
{
call cfodsode.PKG_SEC_SIGCHAN_LIST_CX.CX_OTHER_SIGCHAN_DATA(?,?,?，?)
}

</proceduce>

2.dao层

 public void runCXDAta(String reportDate){
        Map<String,Object> param = new HashMap<>();
        param.put("P_DATESTR",reportDate);
        param.put("P_PROCESS_CODE","");
        param.put("P_PROCESS_MSG","");
        param.put("result","");
        getSqlMaopClientTemplate().update("runSignDate",param);
        log.info("储存过程返回值P_PROCESS_CODE为"+param.get("P_PROCESS_CODE"));
        log.info("储存过程返回值P_PROCESS_MSG为"+param.get("P_PROCESS_MSG"));
    }

四、从储存过程中返回一个对象

1.PKG的写法

1.头部的声明

-------头部申明
create or replace package PKG_DATA is PRAGMA SERIALLY_REUSABLE;
--------声明一种类型为T_CURSOR,可输出为一个对象
TYPE T_CURSOR IS REF CURSOR;
------输出的类型为T_CURSOR类型,输入类型为P_DATE
PROCEDURE GET_DATA(P_DATE IN DATE,P_PROCESS_CODE OUT VARCHAR2,p_cursor OUT T_CURSOR);
------头部结束
end PKG_DATA;

2.体部的写法，p_cursor是名称，输出，类型为T_CURSOR.

create or replace package body PKG_SEC_DATA is 
 PRAGMA SERIALLY_REUSABLE;
 PROCEDURE GET_DATA (p_cursor OUT T_CURSOR)  is 
 v_limit number;
 BEGIN
select nvl(to_number(C.config_value),1000) 
into vlimit
from SEC_INFO c
where c.config_type = 'sec_gen'
and c.config_id = 'sec_single_num' 
for update;

update sec_list
set flag = ‘98’
where falg = '00'
and date_updated > sysdate -3
and rownum <= v_limit;

open p_cursor for 
select t.id reportId,
t.policy_no reportNo,
t.PRT_DATR bizDateStr
from SEC_LIST
WHERE t.flag = '98'
and date_updated >sysdate - 3;

update REPORT_LIST t
set t.falg= '99'
where t.falg= '98'
and DATE_UPDATED >sysdate - 3;

commit;
EXCEPTION
where OTHERS THEN 
ROLLBACK;------事务回滚
RAISE；-----将异常抛出去
	end GET_DATA ;
end PKG_SEC_DATA;

2.java调用PKG的代码

xml配置

<parameterMap id = "secReportDataParamMap" class="map">
	<parameter property = "result" javaType = "java.sql.ResultSet" jdbcType = "ORACLECURSOR" mode = "OUT" resultMap = "secReportDataMap">
</parameterMap>
<resultMap id = "secReportDataMap" class= "com.icore.secondary.reportBuild.dto.SecProductBaseInfoDto">
	<result property = "reporNo" column = "reportNo">
	<result property = "scence" column = "scence">
</resultMap>

<proceduce id = "getDtoByOracle" parameterMap = "secReportDataParamMap">---parameterMap对应xnl文件配置
{
call cfodsode.PKG_DATA .GET_DATA(?)
}

dao层

 public void runCXDAta(String reportDate){
        Map<String,Object> param = new HashMap<>();     
        List result = getSqlMaopClientTemplate().update("getDtoByOracle",param);
        if (param.containKey("result")){
        	result = (List(SecProductBaseInfoDto))param.get("result");
        }
		param.get("result");
    }

五、oracle中实现遍历

create or replace package body PKG_SEC_DATA is 
 PRAGMA SERIALLY_REUSABLE;
 PROCEDURE GET_DATA (p_cursor OUT T_CURSOR)  is 
 v_limit number;  ---查询条数上限
 result_num number;
 execute_sql varchar(5000) :='';
 type num_list is varray（3） of varchar(3);
 body_arry num_list := num_list('','_FD','_TJ')
 --语法结构   变量名   变量类型  赋值符号
 BEGIN
select nvl(to_number(C.config_value),1000) 
into vlimit
from SEC_INFO c
where c.config_type = 'sec_gen'
and c.config_id = 'sec_single_num' 
for update;

for i in 1 .. body_arry.count loop
---拼接SQL语句
execute_sql := 'update sec_list' || body_arrry(i) ||' t set t.flag = 98 where t.flag = 00 and trunc(t.date_updated) > trunc(sysdate) - 3 and rownum <=' || v_limit;

execute immediate execute_sql;---执行SQL语句
result_num = sql% rowcount; ---判断SQL是否有更新到

if result_num <= 0 then continue;
end if;
if body_arry(i) is null then 
open p_cursor for select t.policy_no reportNo
from sec_list t where t.falg = '98';
else if '_FD' = body_arry(i) then 
open p_cursor for select t.policy_no reportNo
from sec_list_FD t where t.falg = '98';
else if '_TJ' = body_arry(i) then 
open p_cursor for select t.policy_no reportNo
from sec_list_TJ t where t.falg = '98';
end if;
execute_sql :='update sec_list' || body_arry(i) || 't set t.flag= 99 where t.flag = 98';
execute immediate execute_sql;
exit;---跳出本次循环

end loop;
commit;
 
EXCEPTION
where OTHERS THEN 
ROLLBACK;------事务回滚
RAISE；-----将异常抛出去
	end GET_DATA ;
end PKG_SEC_DATA;

六、oracle中实现SQL语句传参

for i in 1..24 loop 
  select add_months(P_E_DATE,i-24) into V_HIS_E_DATE from dual;
  v_sql = 'merge into zw_stat_mas l 
  using (select cp.acct_no,nvl(:1-min(cp.payment_date),0) odays from zw_plan
   where cp.product_mode = ''PL_WS'' and cp.plan_status in (''1'',''2'') and 
   ((cp.grace_for_date < trunc(cp.date_settle) 
   and trunc(cp.payment_date,''MM'') < trunc(cp.date_settle,''MM'') 
   and cp.date_settle > :3 ) or cp.date_settle is
    null) group by cp.acct_no) p
    on (l.acct_no = p.acct_no) 
    when matched then 
    update set l.overduedays_mon_'||i |
' = odays';
execute immediate v_sql using V_HIS_E_DATE ,V_HIS_E_DATE ,V_HIS_E_DATE ;
commit;

七、异常处理

begin 
select config_value into v_bank_code from 
zw_info phd where phd.config_type = 'xa_zed' 

exception 
when others then v_bank_code := 'N10155840H0001'