ORACLE EXECUTE IMMEDIATE 用法
EXECUTE IMMEDIATE 代替了以前Oracle8i中DBMS_SQL package包.
它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。
-- 使用技巧
1. EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交
如果通过EXECUTE IMMEDIATE处理DML命令，
那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分.
如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据
2. 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.
3. 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号.
下面的例子展示了所有用到Execute immediate的可能方面.希望能给你带来方便.
stmt2_:= 'BEGIN :bt:=Shop_Ord_Code_Api.Decode(:bom_type_db); END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt2_ USING OUT bom_type_, IN lead_time_code_db_;
stmt_ :=
'BEGIN
Manuf_Structure_Int_Api.Get_Revision_Alternative(:revision_no,
:cost_alternative_no,
:contract,
:part_no,
:bom_type,
:effective_date,
:serial_no,
:serial_no_end);
END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt_ USING IN OUT revision_no_, IN OUT cost_alternative_no_,
IN contract_, IN part_no_, IN bom_type_,
IN TRUNC(effective_date_), IN serial_no_,
IN serial_no_;
整体示例如下:
PROCEDURE Calculate_Cost__ (
contract_ IN VARCHAR2,
part_no_ IN VARCHAR2,
cost_set_ IN NUMBER,
alternative_no_ IN VARCHAR2,
routing_alternative_no_ IN VARCHAR2,
effective_date_ IN DATE,
stop_roll_ IN VARCHAR2,
plannable_ IN VARCHAR2,
serial_no_ IN NUMBER,
all_levels_ IN BOOLEAN )
IS
cost_template_id_ PART_COST_TAB.cost_template_id%TYPE;
revision_no_ VARCHAR2(2):=NULL;
cost_alternative_no_ VARCHAR2(20);
routing_revision_ VARCHAR2(2):=NULL;
cost_routing_alternative_no_ VARCHAR2(20);
include_plannable_ VARCHAR2(20);
stop_roll_allowed_ VARCHAR2(20);
calculation_date_ DATE;
bom_type_ VARCHAR2(200);
lead_time_code_db_ VARCHAR2(20);
is_manufactured_ NUMBER;
default_alternative_ VARCHAR2(20):= '*';
stmt_ VARCHAR2(2000);
stmt2_ VARCHAR2(2000);
stmt3_ VARCHAR2(2000);
stmt4_ VARCHAR2(2000);
stmt5_ VARCHAR2(2000);
stmt6_ VARCHAR2(2000);
one_part_ VARCHAR2(20);
part_cost_group_id_ PART_COST_TAB.part_cost_group_id%TYPE;
std_order_size_ NUMBER;
part_planning_rec_ Inventory_Part_Planning_API.Public_Rec;
max_level_ PLS_INTEGER := 0;
use_inv_scrap_for_manuf_ BOOLEAN := FALSE;
use_inv_scrap_for_pur_ BOOLEAN := FALSE;
BEGIN
General_SYS.Init_Method(lu_name_, 'PART_COST_API', 'Calculate_Cost__');
IF (cost_set_ = 1) THEN
Error_SYS.Record_General(lu_name_,
'COSTSETONEERROR: Not allowed to calculate on cost set number 1');
END IF;
IF ( Cost_Calculation_API.Is_Calc_All_Parts_Running(contract_,cost_set_ ) = 1 ) THEN
Error_SYS.Record_General(lu_name_,
'ALLPARTSJOBRUNNING: Cannot calculate costs in cost set :P1 because there is a background job calculating costs in this cost set.',cost_set_);
END IF;
IF (plannable_ = 'TRUE') THEN
include_plannable_ := 'TRUE';
ELSIF (include_plannable_ = 'FALSE') THEN
include_plannable_ := ' ';
ELSE
Error_SYS.Record_General(lu_name_,
'INPARAMPROB: The parameter plannable must have the value TRUE or FALSE');
END IF;
IF stop_roll_ = Stop_Cost_Roll_API.Get_Client_Value(1) THEN
stop_roll_allowed_ := 'FALSE';
ELSE
stop_roll_allowed_ := 'TRUE';
END IF;
part_planning_rec_ := Inventory_Part_Planning_API.Get(contract_,part_no_);
IF (part_planning_rec_.mrp_order_code IN ('K','O','T')) THEN
Error_SYS.Record_General(lu_name_,
'CALCPHANTOMS: Cannot calculate costs for phantom parts, part :P1',part_no_);
END IF;
calculation_date_ := Site_API.Get_Site_Date(contract_);
IF (Inventory_Part_API.Get_Type_Code_Db(contract_,part_no_) IN ('1', '2')) THEN
lead_time_code_db_ := 'M';
is_manufactured_ := 1;
ELSE
lead_time_code_db_ := 'P';
is_manufactured_ := 0;
END IF;
-- get alternative,revision
IF (serial_no_ IS NOT NULL) THEN
stmt2_:= 'BEGIN :bt:=Shop_Ord_Code_Api.Decode(:bom_type_db); END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt2_ USING OUT bom_type_, IN lead_time_code_db_;
stmt_ :=
'BEGIN
Manuf_Structure_Int_Api.Get_Revision_Alternative(:revision_no,
:cost_alternative_no,
:contract,
:part_no,
:bom_type,
:effective_date,
:serial_no,
:serial_no_end);
END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt_ USING IN OUT revision_no_, IN OUT cost_alternative_no_,
IN contract_, IN part_no_, IN bom_type_,
IN TRUNC(effective_date_), IN serial_no_,
IN serial_no_;
stmt3_ :=
'BEGIN
Routing_Int_Api.Get_Revision_Alternative (:routing_revision,
:cost_routing_alternative_no,
:contract,
:part_no,
:bom_type,
:effective_date,
:serial_no,
:serial_no_end);
END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt3_ USING IN OUT routing_revision_,
IN OUT cost_routing_alternative_no_,
IN contract_, IN part_no_, IN bom_type_,
IN TRUNC(effective_date_),
IN serial_no_, IN serial_no_;
IF (cost_routing_alternative_no_ IS NULL) THEN
cost_routing_alternative_no_:= default_alternative_;
END IF;
IF (cost_alternative_no_ IS NULL) THEN
cost_alternative_no_:= default_alternative_;
END IF;
ELSE
cost_routing_alternative_no_ := routing_alternative_no_;
cost_alternative_no_ := alternative_no_;
IF (NOT(cost_alternative_no_ = default_alternative_)) THEN
stmt2_:= 'BEGIN :bt:=Shop_Ord_Code_Api.Decode(:bom_type_db); END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt2_ USING OUT bom_type_, IN lead_time_code_db_;
stmt4_:= 'BEGIN :revision_no := Part_Revision_API.Get_Revision_By_Date(:contract,:part_no,:effective_date); END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt4_ USING OUT revision_no_, IN contract_, IN part_no_,
IN TRUNC(effective_date_);
stmt5_:=
'BEGIN
MANUF_STRUCTURE_INT_API.Manuf_Struct_Alternate_Exist(:contract,:part_no,:revision_no,:bom_type,:alternative_no);
END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt5_ USING IN contract_, IN part_no_, IN revision_no_,
IN bom_type_, IN cost_alternative_no_;
END IF;
IF (NOT(cost_routing_alternative_no_ = default_alternative_)) THEN
IF (cost_alternative_no_ = default_alternative_) THEN
stmt2_:= 'BEGIN :bt:=Shop_Ord_Code_Api.Decode(:bom_type_db); END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt2_ USING OUT bom_type_, IN lead_time_code_db_;
END IF;
stmt4_:=
'BEGIN
:routing_revision := Routing_Head_API.Get_Revision_By_Date (:contract,:part_no,:bom_type,:effective_date);
END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt4_ USING OUT routing_revision_, IN contract_,
IN part_no_, IN bom_type_, IN TRUNC(effective_date_);
stmt6_:=
'BEGIN
ROUTING_INT_API.Rout_Alternate_Exist(:contract,:part_no,:routing_revision,:bom_type,:alternative_no);
END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt6_ USING IN contract_, IN part_no_, IN routing_revision_,
IN bom_type_, IN cost_routing_alternative_no_;
END IF;
END IF;
IF (Check_Exist___(contract_, part_no_, cost_set_,cost_alternative_no_, cost_routing_alternative_no_)) THEN
cost_template_id_ := Get_Cost_Template_Id(contract_, part_no_, cost_set_,cost_alternative_no_, cost_routing_alternative_no_);
IF (serial_no_ IS NOT NULL) THEN
UPDATE PART_COST_TAB
SET effective_serial_no = serial_no_
WHERE contract = contract_
AND part_no = part_no_
AND cost_set = cost_set_
AND alternative_no = cost_alternative_no_
AND routing_alternative_no = cost_routing_alternative_no_;
END IF;
ELSE
part_cost_group_id_ := Inventory_Part_API.Get_Part_Cost_Group_Id(contract_,part_no_);
cost_template_id_ := Create_New_Part__(contract_, part_no_, cost_set_, cost_alternative_no_,
cost_routing_alternative_no_,
calculation_date_,serial_no_,TRUE,part_cost_group_id_);
END IF;
IF (all_levels_) THEN
-- Remove cost structure from PartCostBucket.
Part_Cost_Bucket_API.Remove_Cost_Structure(contract_, part_no_, cost_set_, cost_alternative_no_,
cost_routing_alternative_no_);
ELSE
Part_Cost_Bucket_API.Remove_Cost_Level(contract_, part_no_, cost_set_, cost_alternative_no_,
cost_routing_alternative_no_);
END IF;
IF (part_planning_rec_.std_order_size = 0) THEN
std_order_size_ := 1;
ELSE
std_order_size_ := part_planning_rec_.std_order_size;
END IF;
-- To speed up material oh calculation
DELETE FROM material_oh_bucket_tmp;
INSERT INTO material_oh_bucket_tmp
SELECT tcb.contract, tcb.cost_template_id, tcb.cost_bucket_id, tcb.fixed_variable_cost,
tcb.direct_indirect_cost, tcb.sunk_relevant_cost, tcb.bucket_frequency, cb.cost_bucket_type,
cb.cost_activity_id
FROM cost_bucket_tab cb, template_cost_bucket_tab tcb
WHERE tcb.contract = cb.contract
AND tcb.cost_bucket_id = cb.cost_bucket_id
AND tcb.contract = contract_
AND cb.cost_bucket_public_type = 'MATOH';
-- Init the top part with a dummy bucket_id 'SYS'.
Part_Cost_Bucket_API.Init_Top_Level_Part (
contract_ => contract_,
part_no_ => part_no_,
cost_set_ => cost_set_,
alternative_no_ => cost_alternative_no_,
routing_alternative_no_ => cost_routing_alternative_no_,
bucket_level_ => 0,
bucket_seq_ => 0,
top_level_part_no_ => part_no_,
top_alternative_no_ => cost_alternative_no_,
top_routing_no_ => cost_routing_alternative_no_,
cost_bucket_id_ => 'SYS',
calculation_date_ => calculation_date_,
method_ => 'ONE_PART');
one_part_ := 'ONE_PART';
IF Cost_Parameter_API.Get_Parameter_Value1(contract_, 'USE_INV_SCRAP_FOR_MANUF') = 'Y' THEN
use_inv_scrap_for_manuf_ := TRUE;
END IF;
IF Cost_Parameter_API.Get_Parameter_Value1(contract_, 'USE_INV_SCRAP_FOR_PUR') = 'Y' THEN
use_inv_scrap_for_pur_ := TRUE;
END IF;
IF (stop_roll_allowed_ = 'FALSE' OR
Part_Cost_API.Check_If_Stop_Cost_Roll(contract_, part_no_, cost_set_,
alternative_no_,routing_alternative_no_) = 0) THEN
IF (all_levels_) THEN
-- Calculate level cost for all components, and roll up cost from bottom to top
-- This step will create a unique tree in PartCostBucket.
IF (Cost_Calculation_API.mfgstd_is_installed_) THEN
-- begin: henry, 120310
DECLARE
use_inv_scrap_for_manuf_ BOOLEAN := FALSE;
use_inv_scrap_for_pur_ BOOLEAN := FALSE;
BEGIN
IF Cost_Parameter_API.Get_Parameter_Value1(contract_, 'USE_INV_SCRAP_FOR_MANUF') = 'Y' THEN
use_inv_scrap_for_manuf_ := TRUE;
END IF;
IF Cost_Parameter_API.Get_Parameter_Value1(contract_, 'USE_INV_SCRAP_FOR_PUR') = 'Y' THEN
use_inv_scrap_for_pur_ := TRUE;
END IF;
Costing_Support_Int_API.Calc_Top_Down_Level_Cost(
max_level_, contract_, part_no_, cost_set_, cost_alternative_no_,
TRUNC(effective_date_), plannable_, stop_roll_allowed_,
one_part_, calculation_date_,
cost_routing_alternative_no_, revision_no_,
use_inv_scrap_for_manuf_,
use_inv_scrap_for_pur_);
END;
stmt_ :=
'DECLARE
use_inv_scrap_for_manuf_ BOOLEAN := FALSE;
use_inv_scrap_for_pur_ BOOLEAN := FALSE;
BEGIN
IF Cost_Parameter_API.Get_Parameter_Value1(:contract, ''USE_INV_SCRAP_FOR_MANUF'') = ''Y'' THEN
use_inv_scrap_for_manuf_ := TRUE;
END IF;
IF Cost_Parameter_API.Get_Parameter_Value1(:contract, ''USE_INV_SCRAP_FOR_PUR'') = ''Y'' THEN
use_inv_scrap_for_pur_ := TRUE;
END IF;
Costing_Support_Int_API.Calc_Top_Down_Level_Cost(
:max_level, :contract, :part_no, :cost_set, :alternative_no,
:effective_date, :plannable, :stop_roll_allowed,
:one_part, :calculation_date,
:routing_alternative_no, :revision_no,
use_inv_scrap_for_manuf_,
use_inv_scrap_for_pur_);
END;';
-- ifs_assert_safe pemase 20051213
EXECUTE IMMEDIATE stmt_ USING IN contract_,
OUT max_level_,
IN part_no_, IN cost_set_,
IN cost_alternative_no_, IN TRUNC(effective_date_),
IN plannable_, IN stop_roll_allowed_,
IN one_part_, IN calculation_date_,
IN cost_routing_alternative_no_, IN revision_no_;
-- end: 120316
END IF;
ELSE
-- One level, attach structure
Part_Cost_Bucket_API.Create_Structure(contract_, part_no_, cost_set_,
cost_alternative_no_, cost_routing_alternative_no_,
stop_roll_allowed_, TRUNC(effective_date_), serial_no_,
calculation_date_);
END IF;
END IF;
-- Finally, calculate level cost for this part
Cost_Calculation_API.Calculate_Level_Cost(
contract_ => contract_,
part_no_ => part_no_,
cost_set_ => cost_set_,
top_alternative_no_ => cost_alternative_no_,
top_level_part_no_ => part_no_,
parent_part_no_ => part_no_,
effective_date_ => TRUNC(effective_date_),
cost_template_id_ => cost_template_id_,
bucket_level_ => 0,
bucket_seq_ => 0,
parent_bucket_seq_ => 0,
qty_per_assembly_ => 1,
cumm_qty_per_assembly_ => 1,
split_qty_per_assembly_ => 1,
cost_qty_per_assembly_ => 1,
include_plannable_ => include_plannable_,
method_ => 'ONE_PART',
calculation_date_ => calculation_date_,
top_routing_no_ => cost_routing_alternative_no_,
effective_serial_no_ => serial_no_,
line_item_no_ => NULL,
mrp_order_code_ => part_planning_rec_.mrp_order_code,
std_order_size_ => std_order_size_,
is_manufactured_ => is_manufactured_,
split_manuf_acquired_ => part_planning_rec_.split_manuf_acquired,
percent_manufactured_ => part_planning_rec_.percent_manufactured,
percent_acquired_ => part_planning_rec_.percent_acquired,
use_cost_distrib_for_byprod_ => FALSE,
component_to_by_product_fac_ => 0,
use_inv_scrap_for_manuf_ => use_inv_scrap_for_manuf_,
use_inv_scrap_for_pur_ => use_inv_scrap_for_pur_);
DELETE FROM material_oh_bucket_tmp;
-- Do a final calculation of Buckets of Buckets.
Part_Cost_Bucket_API.Calc_Bucket_Of_Buckets(contract_, part_no_, cost_set_, cost_alternative_no_,
cost_routing_alternative_no_,all_levels_);
-- Clean up in CVA.
CVA_Child_Part_API.Adjust_After_Roll_Up__(contract_, part_no_, cost_set_, alternative_no_,
routing_alternative_no_);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END Calculate_Cost__;