1、静态sql和动态sql
Oracle编译PL/sql程序块分为两个种（其它sql代码块包括存储过程也是如此）：
其一为前期联编（early binding），即sql语句在程序编译期间就已经确定，大多数的编译情况属于这种类型；另外一种是后期联编（late binding），即sql语句只有在运行阶段才能建立，例如当查询条件为用户输入时，那么Oracle的sql引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给sql引擎进行处理。通常，静态sql采用前一种编译方式，而动态sql采用后一种编译方式。
动态sql的两种写法：
（1） Excute immediate 动态sql语句 using 绑定参数列表 returning into 输出参数列表;对这一语句作如下说明：
（2）v_sqlvarchar2(4000); v_month_name varchar2(10); …… v_sql:=’……’; execute immediatev_sql;
v_month_name varchar2(10);
v_sql varchar2(4000);
v_sql:=’insert into tablename(c1,c2,……,cn) select '||v_month_name||',short_name,……from tablename2’;
executeimmediate v_sql;
2、使用动态sql的业务场景
需求是根据传入的日期，取出年份v_year和月份v_month值，然后根据业务规则来查出对应的承租率返回，然后组成数据录入到一个新的报表表里面去；
因为年份字段有一个，可以用where字段直接判断，但是月份数据有12个字段，而且每次传入的日期获取的月份数值不一定一致，所以按照比较笨的办法是需要if else 连续判断12次才能囊括所有的月份字段的，这样存储过程就会非常冗余笨重而且不易阅读。
所以需要找一个新的办法来处理，避免做连续12个if else的判断，这里可以采用动态sql，也就是自动根据传入日期来组织去查表中的哪个月份字段和年份字段。
3、存储过程使用动态sql示例
createorreplaceprocedure BIS_PROJECT_BUDGET(P_DATE inDATE) is v_date varchar2(10); v_year varchar2(10); v_month varchar2(10); v_month_name varchar2(10); v_sql varchar2(4000); begin
--取当日的上个月的最后一天 每个月首日统计上个月 select to_char((last_day(add_months(P_DATE,-1))),'yyyy-mm-dd') last_day, to_char((last_day(add_months(P_DATE,'yyyy') years,'mm') months into v_date,v_year,v_month from dual;
select case when v_month='01'then 'bt.JAN_KPI' when v_month='02'then 'bt.FEB_KPI' when v_month='03'then 'bt.MAR_KPI' when v_month='04'then 'bt.APR_KPI' when v_month='05'then 'bt.MAY_KPI' when v_month='06'then 'bt.JUN_KPI' when v_month='07'then 'bt.JUL_KPI' when v_month='08'then 'bt.AUG_KPI' when v_month='09'then 'bt.SEP_KPI' when v_month='10'then 'bt.OCT_KPI' when v_month='11'then 'bt.NOV_KPI' else'DEC_KPI'endinto v_month_name from dual;
delete BIS_SECOND_ZS_PRO where PRO_MONTH=to_number(v_month) and PRO_YEAR = v_year; commit;
v_sql:= ' insert into BIS_SECOND_ZS_PRO bp( BIS_SECOND_ZS_PRO_ID, bis_project_id, PRO_YEAR, PRO_MONTH, EMPTY_PRE, BUDGETYEAR_MONTH, CREATOR, CREATED_DATE, RECORD_VERSION ) select sys_guid(), m.bis_project_id, '||v_year||' as years, to_number('||v_month||') as months, m.budget_month, y.budget_year, ''system'', sysdate, dbms_random.value(0,100000) from ( select r.bis_project_id, nvl(decode(r.total_square,round(('||v_month_name||' - r.open_square/r.total_square)*r.total_square,2)),0) budget_month from (select pt.bis_project_id, pt.short_name, pt.total_square, nvl(sum(af.open_square_total),0) open_square from (select p.bis_project_id, p.short_name, nvl(sum(vf.rent_square_total),0) total_square from bis_project p left join vw_bis_mall_floor_summary vf on p.bis_project_id = vf.bis_project_id where p.is_business_project = ''1'' and p.oper_status = ''2'' group by p.bis_project_id,p.short_name) pt, rep_agg_floor_month af where 1 = 1 and pt.bis_project_id = af.bis_project_id and af.store_type = ''1'' and af.agg_year = '||v_year||' and af.agg_month = to_number('||v_month||') group by pt.bis_project_id,pt.short_name,pt.total_square) r, bis_project_target bt where 1=1 and r.bis_project_id = bt.bis_project_id(+) ) m, ( select r.bis_project_id,0)+r.off_square budget_year from (select pt.bis_project_id, af.agg_month, af.agg_year,0) open_square, nvl(sum(af.off_square_total),0) off_square from (select p.bis_project_id,rep_agg_floor_month af where 1 = 1 and pt.bis_project_id = af.bis_project_id and af.store_type = ''1'' and af.agg_year = '||v_year||' and af.agg_month = ''1'' group by pt.bis_project_id,pt.total_square,af.agg_month,af.agg_year) r,bis_project_target bt where r.bis_project_id = bt.bis_project_id(+)) y where m.bis_project_id = y.bis_project_id '; executeimmediate v_sql;
-- v_sql:='select 1 as a from dual;';
commit; EXCEPTION -- 异常处理部份 WHENOTHERSTHEN dbms_output.put_line('insertBisSecondZsPro 错误：' || sqlERRM); null; end BIS_PROJECT_BUDGET; |
