CREATE GLOBAL TEMPORARY TABLE BOSP_TEMP_YearDesposite ( company_name VARCHAR2(300), subbid_name VARCHAR2(30), amount number,maxformula number) ON COMMIT PRESERVE ROWS --PRESERVE会话相关的临时表,DELETE事务相关的临时表 CREATE OR REPLACE PACKAGE YearDesposite AS TYPE Result_CURSOR IS REF CURSOR; END YearDesposite; --定义一个游标变量 create or replace procedure calculateYearDesposite (v_year in varchar2, isNewMaterial in varchar2, p_course out YearDesposite.Result_CURSOR) authid current_user --role在存储过程中不可见,这里authid current_user指使用调用者的身份去执行存储过程 --不然就会在create时出现insufficient privileges的异常 as v_old_material varchar2(100); v_new_material varchar2(100); v_company_name varchar2(300); v_subbid_name varchar2(30); n_amount number; c_original_course YearDesposite.Result_CURSOR; c_formula_course YearDesposite.Result_CURSOR; --TYPE c_original IS REF CURSOR; --c_original_course c_original; --c_formula_course c_original; v_sql_string varchar2(2000) :=''; v_sql_formula varchar2(2000) :=''; n_start_value number; n_end_value number; n_percentage number; v_include_mode varchar2(10); b_hasAssignment boolean :=false; n_maxformula number :=0; sqlcount number :=0; begin v_sql_string :=v_sql_string||'select company_name, subbid_name, max(amount) amount,old_materialcode_id,new_materialcode_id'; v_sql_string :=v_sql_string||' from (select company_name, subbid_name, subbid_code, sum(amount) amount,old_materialcode_id,new_materialcode_id'; v_sql_string :=v_sql_string||' from (select b.company_name, a.amount, c.subbid_name, c.subbid_code,c.old_materialcode_id,c.new_materialcode_id'; v_sql_string :=v_sql_string||' from bosp_zb_packageinfo a'; v_sql_string :=v_sql_string||' inner join bosp_global_supplier b on a.supplier_id = b.id'; v_sql_string :=v_sql_string||' inner join bosp_zb_subbidinfo c on a.parent_id = c.id'; v_sql_string :=v_sql_string||' inner join bosp_zb_projectinfo d on c.project_id = d.id'; v_sql_string :=v_sql_string||' where d.bid_year =:year'; v_sql_string :=v_sql_string||' union all'; v_sql_string :=v_sql_string||' select b.company_name, a.amount, c.subbid_name, c.subbid_code,c.old_materialcode_id,c.new_materialcode_id'; v_sql_string :=v_sql_string||' from bosp_zb_packageinfo a'; v_sql_string :=v_sql_string||' inner join bosp_global_supplier b on a.supplier_id = b.id'; v_sql_string :=v_sql_string||' inner join bosp_zb_packageinfo e on a.parent_id = e.id'; v_sql_string :=v_sql_string||' inner join bosp_zb_subbidinfo c on e.parent_id = c.id'; v_sql_string :=v_sql_string||' inner join bosp_zb_projectinfo d on c.project_id = d.id'; v_sql_string :=v_sql_string||' where d.bid_year =:year'; v_sql_string :=v_sql_string||' )'; v_sql_string :=v_sql_string||' group by company_name, subbid_name, subbid_code,old_materialcode_id,new_materialcode_id)'; v_sql_string :=v_sql_string||' group by company_name, subbid_name,old_materialcode_id,new_materialcode_id'; if isNewMaterial = '1' then v_sql_formula :=v_sql_formula||' select c.start_value, c.end_value, c.percentage, c.include_mode'; v_sql_formula :=v_sql_formula||' from bosp_zb_productlink a'; v_sql_formula :=v_sql_formula||' inner join bosp_zb_formula b on a.formula_id = b.id'; v_sql_formula :=v_sql_formula||' inner join bosp_zb_formulaarithmetic c on a.formula_id = c.formula_id'; v_sql_formula :=v_sql_formula||' where a.newproduct_id =:newproduct_id'; v_sql_formula :=v_sql_formula||' and b.formula_type ='||chr(39)||'11'||chr(39);--单引号的解决办法 v_sql_formula :=v_sql_formula||' and b.create_date= (select max(b.create_date)'; v_sql_formula :=v_sql_formula||' from bosp_zb_productlink a'; v_sql_formula :=v_sql_formula||' inner join bosp_zb_formula b on a.formula_id = b.id'; v_sql_formula :=v_sql_formula||' where a.newproduct_id =:newproduct_id'; v_sql_formula :=v_sql_formula||' and b.formula_type ='||chr(39)||'11'||chr(39)||' )'; else v_sql_formula :=v_sql_formula||' select c.start_value, c.end_value, c.percentage, c.include_mode'; v_sql_formula :=v_sql_formula||' from bosp_zb_productlink a'; v_sql_formula :=v_sql_formula||' inner join bosp_zb_formula b on a.formula_id = b.id'; v_sql_formula :=v_sql_formula||' inner join bosp_zb_formulaarithmetic c on a.formula_id = c.formula_id'; v_sql_formula :=v_sql_formula||' where a.oldproduct_id =:oldproduct_id '; v_sql_formula :=v_sql_formula||' and b.formula_type ='||chr(39)||'11'||chr(39); v_sql_formula :=v_sql_formula||' and b.create_date= (select max(b.create_date)'; v_sql_formula :=v_sql_formula||' from bosp_zb_productlink a'; v_sql_formula :=v_sql_formula||' inner join bosp_zb_formula b on a.formula_id = b.id'; v_sql_formula :=v_sql_formula||' where a.oldproduct_id =:oldproduct_id'; v_sql_formula :=v_sql_formula||' and b.formula_type ='||chr(39)||'11'||chr(39)||' )'; end if; --execute immediate 'CREATE OR REPLACE GLOBAL TEMPORARY TABLE BOSP_TEMP_YearDesposite ( company_name VARCHAR2(300), subbid_name VARCHAR2(30), amount number,maxformula number) ON COMMIT delete ROWS'; --这里把执行语句提出来了,DDL语句是比较占用资源的,而且用了后必须删掉,不然第二次执行存储过程就会出错 open c_original_course for v_sql_string using v_year,v_year; loop fetch c_original_course into v_company_name,v_subbid_name,n_amount,v_old_material,v_new_material; exit when c_original_course%NOTFOUND; if isNewMaterial = '1' then open c_formula_course for v_sql_formula using v_new_material,v_new_material; loop fetch c_formula_course into n_start_value,n_end_value,n_percentage,v_include_mode; exit when c_formula_course%NOTFOUND; if n_maxformula <n_percentage then n_maxformula :=n_percentage;end if; if v_include_mode = '01' then if n_amount >=n_start_value and n_amount < n_end_value then if not b_hasAssignment then n_amount :=n_percentage;b_hasAssignment :=true; end if; end if; end if; if v_include_mode = '02' then if n_amount >n_start_value and n_amount <= n_end_value then if not b_hasAssignment then n_amount :=n_percentage;b_hasAssignment :=true; end if; end if; end if; if v_include_mode = '03' then if n_amount >=n_start_value and n_amount <= n_end_value then if not b_hasAssignment then n_amount :=n_percentage;b_hasAssignment :=true; end if; end if; end if; if v_include_mode = '04' then if n_amount >n_start_value and n_amount < n_end_value then if not b_hasAssignment then n_amount :=n_percentage;b_hasAssignment :=true; end if; end if; end if; end loop; close c_formula_course; else open c_formula_course for v_sql_formula using v_old_material,v_old_material; loop fetch c_formula_course into n_start_value,n_end_value,n_percentage,v_include_mode; exit when c_formula_course%NOTFOUND; if n_maxformula <n_percentage then n_maxformula :=n_percentage;end if; if v_include_mode = '01' then if n_amount >=n_start_value and n_amount < n_end_value then if not b_hasAssignment then n_amount :=n_percentage;b_hasAssignment :=true; end if; end if; end if; if v_include_mode = '02' then if n_amount >n_start_value and n_amount <= n_end_value then if not b_hasAssignment then n_amount :=n_percentage;b_hasAssignment :=true; end if; end if; end if; if v_include_mode = '03' then if n_amount >=n_start_value and n_amount <= n_end_value then if not b_hasAssignment then n_amount :=n_percentage;b_hasAssignment :=true; end if; end if; end if; if v_include_mode = '04' then if n_amount >n_start_value and n_amount < n_end_value then if not b_hasAssignment then n_amount :=n_percentage;b_hasAssignment :=true; end if; end if; end if; end loop; close c_formula_course; end if; if n_maxformula <>0 then execute immediate 'insert into BOSP_TEMP_YearDesposite values (:1,:2,:3,:4)' using v_company_name,v_subbid_name,n_amount,n_maxformula /**returning MAXFORMULA into sqlcount*/; end if; --以上部分是逻辑处理过程,不知道为什么测试时BOSP_TEMP_YearDesposite表中始终没有数据 b_hasAssignment :=false; n_maxformula :=0; end loop; close c_original_course; open p_course for 'select * from BOSP_TEMP_YearDesposite'; end calculateYearDesposite;