How Oracle Writes Packages

One: How to use a For loop
Two: How to use the stitching statement EXECUTE IMMEDIATE v_sql into v_work_orderid;
Three: How to define record types as variables for storing and querying

CREATE OR REPLACE Package pkg_generatereport_biz are/*=============================================== * Program NAME:  Pkg_css_customer_delete_sync_biz * * DESCRIPTION:?????????????????? * * History: * 1.00 2016-07-28 Chenli Creation * ==============================================*/PROCEDURE m Ain (P_inv_orgid decimal); END pkg_generatereport_biz;/create OR REPLACE Package BODY pkg_generatereport_biz is/* ===============================  ============== * function/procedure * name:main * DESCRIPTION:?????????????? * ARGUMENT: * RETURN: * * History: * 1.00 2016-07-28 Chenli Creation * =============================== ==============*/PROCEDURE Main (p_inv_orgid decimal) is V_sql VARCHAR2 (4000); --Get the related work order V_sql_update_offline VARCHAR2 (4000); --Update the end of the line V_sys_roles_config_row Sys_roles_config%rowtype; --System configuration item V_oqc_insp_sample_rpt_row Oqc_insp_sample_rpt%rowtype; --Sampling report V_oqc_insp_saMple_rpt_sn_row Oqc_insp_sample_rpt_sn%rowtype; V_work_orderid VARCHAR2 (5000); --related work order Str_production_date_from varchar2 (50); --Production start time str_production_date_to varchar2 (50); --Production end time begin for V_RLT in (SELECT * from Oqc_inspection                             _sample s where s.state = ' A ' and s.status = ' tested ' and s.is_v2 = ' 1 ' and s.org_id = P_inv_orgid ORDER BY s.i Nspection_time desc) where rownum <=) loop Str_production_date_from: = To_char (V_rlt.prod      Uction_start_time, ' Yyyy-mm-dd hh24:mi:ss '); Str_production_date_to: = To_char (V_rlt.production_end_time, ' Yyyy-mm-dd hh24:m          I:ss ');    SELECT * to V_sys_roles_config_row from sys_roles_config C   WHERE c.org_id = v_rlt.org_id and c.state = ' A ';      V_work_orderid: = ";      V_sql: = ";                        V_sql: = V_sql | |                        ' SELECT wm_concat (DISTINCT (O.mo_name)) from Oqc_mes_inf_ul_qms_offline O WHERE o.org_id = ' | |                        P_inv_orgid | |                        ' and o.datetime_offline>= to_date (' ' | |                        Str_production_date_from | |                        ' ', ' yyyy-mm-dd hh24:mi:ss ') and O.datetime_offline<=to_date ("| |                        str_production_date_to | |          ' ', ' yyyy-mm-dd hh24:mi:ss ') '; IF V_sys_roles_config_row. SYS_PARMS1 = ' Same production ticket ' then v_sql: = V_sql | | ' and o.mo_name= ' | | V_rlt. work_order_id | |            ‘‘‘‘; Elsif V_sys_roles_config_row. SYS_PARMS2 = ' same material code ' then v_sql: = V_sql | | ' and o.product_code= ' | | V_rlt. prod_id | |            ‘‘‘‘; Elsif V_sys_roles_config_row. SYS_PARMS3 = ' Same product model ' then V_SQL: = V_sql | | ' and o.product_type= ' | | V_rlt. prod_model_id | |            ‘‘‘‘; Elsif V_sys_roles_config_row. SYS_PARMS4 = ' co-production line ' then v_sql: = V_sql | | ' and o.line_desc= ' | | V_rlt. Production_line | |            ‘‘‘‘;          END IF;          EXECUTE IMMEDIATE v_sql into V_work_orderid; V_oqc_insp_sample_rpt_row.      user_created: = v_rlt.user_created; V_oqc_insp_sample_rpt_row.      datetime_created: = sysdate; V_oqc_insp_sample_rpt_row.      Inspection_no: = V_rlt.inspection_no;      v_oqc_insp_sample_rpt_row.org_id: = v_rlt.org_id; V_oqc_insp_sample_rpt_row.      Inspection_result: = V_rlt.inspection_result; V_oqc_insp_sample_rpt_row.      Workorderno: = V_work_orderid; V_oqc_insp_sample_rpt_row.      ref_sample_id: = v_rlt.id; V_oqc_insp_sample_rpt_row.      Inspection_type: = ' sampling '; V_oqc_insp_sample_rpt_row.      Id: = Sys_guid (); V_oqc_insp_sample_rpt_row.          state: = ' A '; If V_rlt.inspection_result = ' hopLattice ' then v_oqc_insp_sample_rpt_row.is_update_mes: = ' Y ';      elsif v_rlt.inspection_result = ' unqualified ' then v_oqc_insp_sample_rpt_row.is_update_mes: = ' N ';      END IF; --1, increase the sampling report table INSERT into Oqc_insp_sample_rpt VALUES v_oqc_insp_sample_rpt_row;                       --Inserting sampling report--2, inserting sampling barcode for V_title in (SELECT * from Oqc_base_standrad_title t where t.baseamine_id = V_rlt. Id and T.is_insepection_result = ' 1 ' and t.state = ' A ') loop v_oqc_ Insp_sample_rpt_sn_row.        Id: = Sys_guid (); V_oqc_insp_sample_rpt_sn_row.        Sn: = V_title.barcode; V_oqc_insp_sample_rpt_sn_row.        state: = ' A '; V_oqc_insp_sample_rpt_sn_row.        datetime_created: = sysdate; V_oqc_insp_sample_rpt_sn_row. rpt_id: = V_oqc_insp_sample_rpt_row.        Id; V_oqc_insp_sample_rpt_sn_row. user_created: = V_rlt.        user_created; INSERT into Oqc_insp_sample_RPT_SN VALUES V_oqc_insp_sample_rpt_sn_row;      -Insert sampling report end loop;      --3, update the offline completion table v_sql_update_offline: = ";                                If V_rlt.inspection_result = ' qualified ' then v_sql_update_offline: = V_sql_update_offline | |                                ' Update oqc_mes_inf_ul_qms_offline O set inspection_result= ' | | V_rlt.                                Inspection_result | |                                "', o.batch_inspection_result= ' | | V_rlt.                                Inspection_result | | "', o.batch_no= ' | | V_rlt.                                Inspection_no | |                                "', o.batch_datetime_created=sysdate where org_id= ' | |      v_rlt.org_id;                                elsif v_rlt.inspection_result = ' unqualified ' then v_sql_update_offline: = V_sql_update_offline | |                                ' Update Oqc_mes_inf_ul_qms_offline O set o.batch_datetime_created=sysdate where o.org_id= ' | |      v_rlt.org_id; End If                              V_sql_update_offline: = V_sql_update_offline | |                              ' and o.datetime_offline>= to_date (' ' | |                              Str_production_date_from | |                              ' ', ' yyyy-mm-dd hh24:mi:ss ') and O.datetime_offline<=to_date ("| |                              str_production_date_to | |          ' ', ' yyyy-mm-dd hh24:mi:ss ') '; IF V_sys_roles_config_row. SYS_PARMS1 = ' Same production ticket ' then v_sql_update_offline: = V_sql_update_offline | |                                ' and o.mo_name= ' | | V_rlt. work_order_id | |            ‘‘‘‘; Elsif V_sys_roles_config_row.                                SYS_PARMS2 = ' same material code ' then v_sql_update_offline: = V_sql_update_offline | | ' and o.product_code= ' | | V_rlt. prod_id | |            ‘‘‘‘; Elsif V_sys_roles_config_row.                                SYS_PARMS3 = ' Same product model ' then v_sql_update_offline: = V_sql_update_offline | | ' And o.product_Type= ' | | V_rlt. prod_model_id | |            ‘‘‘‘; Elsif V_sys_roles_config_row.                                SYS_PARMS4 = ' co-production line ' then v_sql_update_offline: = V_sql_update_offline | |                                ' and o.line_desc= ' | | V_rlt. Production_line | |            ‘‘‘‘;      End If;          EXECUTE IMMEDIATE V_sql_update_offline;                    --4, updated sampling table update Oqc_inspection_sample s set s.is_inspector = ' 1 ', s.status = ' generated report ', s.datetime_report_generated = sysdate, s.batch_no = v_      Rlt.inspection_no where s.id = v_rlt.id and s.org_id = v_rlt.org_id; Commit    --Final submission of end loop; End END pkg_generatereport_biz;/

　　

How Oracle Writes Packages