Explain the oracle Stored Procedure dynamic SQL statement instance and explain the oracle instance

Explain the oracle Stored Procedure dynamic SQL statement instance and explain the oracle instance

Note:

In the string''Represents'. For example, 'select ", a from table'

Create or replace procedure partition (card_type in VARCHAR2, card_no in VARCHAR2, start_date in VARCHAR2, end_date in VARCHAR2, interval in VARCHAR2, timed out VARCHAR2, pat_type out VARCHAR2, pat_no VARCHAR2, pat_id out VARCHAR2, pat_cardno out VARCHAR2, inp_id out VARCHAR2, inp_date out VARCHAR2, pat_name out VARCHAR2, pat_sex out VARCHAR2, pat_birth out VARCHAR2, pat_diag out VARCHAR2, charge _ Typeno out VARCHAR2, timed out VARCHAR2, timed out VARCHAR2, req_comm out VARCHAR2, timed out VARCHAR2, timed out VARCHAR2, req_dt out VARCHAR2, emer_flag out VARCHAR2, timed out VARCHAR2, optional m_dept out VARCHAR2, req_groupna out VARCHAR2, specimen_name out VARCHAR2, sample_detail out VARCHAR2, timed out VARCHAR2, sample_items out VARCHAR2, charge_flag out VARCHAR2, charge_user Out VARCHAR2, timed out VARCHAR2, secrecy out VARCHAR2, other_stat out VARCHAR2, timed out VARCHAR2, timed out VARCHAR2, timed out VARCHAR2, pat_address out VARCHAR2, pat_nation out VARCHAR2, timed varout char2, pat_phone out VARCHAR2, pat_height out VARCHAR2, weight out VARCHAR2, his_itemcode out VARCHAR2, req_itemcode out VARCHAR2, req_itemname out VARCHAR2, combitemna out VARCHAR2, Base_price out VARCHAR2, item_price out VARCHAR2, qty out VARCHAR2, amount out VARCHAR2, his_recordid out VARCHAR2, out VARCHAR2, out VARCHAR2, out VARCHAR2) ASsql_string VARCHAR2 (2000 ); sqlr_result VARCHAR2 (2000); -- ** in the string, ''represents '. For example, 'select', ''' in ''of a from table' represents a space, that is,'' represents a '** BEGINsql_string: = 'select', ''', n. "clinic_patient_id", n. "patient_id", n. "treatment_card_no", ''', n. "operate_time", c. "patient_name", c. "patient_sex", p. "birthday", n. "diagnosis", ''', n. "treatment_office_id", n. "doctor_id", to_char (n. "operate_time", ''yyyy-MM-DD '') operate_time, ''', ''', n. "exec_dept_id", n. "system_type", ''', n. "status", f. "balance_operator", to_char (f. "balance_time", ''yyyy-MM-DD '') balance_time ,'''','''','''','''','''', p. "address", p. "nationality", p. "idcard_no", p. "mobile", ''', ''', n. "item_no", ''', n. "item_name", ''', n. "unit_price", n. "unit_price", n. "item_quantity", n. "total_price", n. "id", ''' FROM "his_clinic_doctor_rx_others" n left join "his_clinic_doctor_rx_info" c ON n. "prescription_no" = c. "prescription_no" left join "his_clinic_patient_info" p ON p. "patient_id" = n. "patient_id" left join "his_clinic_charge_info" f ON n. "patient_id" = f. "patient_id" '; IF card_type is null and is_vaild is NULL THENsqlr_result: = SQL _string | 'where n. "treatment_card_no" = ''' | card_no | ''' AND n. "system_type" = 9AND (TO_CHAR (n. "operate_time", ''yyyy-MM-DD '') BETWEEN ''' | start_date | ''' AND ''' | end_date | ''')'; ELSIF card_type is not null and is_vaild is null then sqlr_result: = SQL _string | 'where n. "treatment_card_no" = ''' | card_no | ''' AND n. "system_type" = 9AND (TO_CHAR (n. "operate_time", ''yyyy-MM-DD '') between''' | start_date | ''' AND ''' | end_date | ''') AND p. "id_categ_code" = '| card_type | ''; *** -- here,'' represents a space. *** -------------------- ELSIF card_type is null and is_vaild is not null THENsqlr_result: = SQL _string | 'where n. "treatment_card_no" = ''' | card_no | ''' AND n. "system_type" = 9AND (TO_CHAR (n. "operate_time", ''yyyy-MM-DD '') between''' | start_date | ''' AND ''' | end_date | ''') AND p. "is_valid" = '| is_vaild | ''; ELSIF card_type is not null and is_vaild is not null THENsqlr_result: = SQL _string | 'where n. "treatment_card_no" = ''' | card_no | ''' AND n. "system_type" = 9 AND (to_char (n. "operate_time", ''yyyy-MM-DD '') between''' | start_date | ''' AND ''' | end_date | ''') AND p. "is_valid" = '| is_vaild |' AND p. "id_categ_code" = '| card_type | ''; end IF; BEGINdbms_output.put_line (sqlr_result); -- output the SQL statement EXECUTE IMMEDIATE sqlr_result INTO hospitalcode, -- execute the SQL statement in sqlr_result and give the value "into" to the following field "pat_type", "pat_no", "pat_id", "pat_cardno", "inp_id", "inp_date", "pat_name", "signature, req_comm, comment, comment, req_dt, comment m_dept, comment, specimen_name, sample_detail, comment, charge_user, charge_dt, secrecy, other_stat, comment, pat_address, pat_nation, identifier, pat_phone, pat_height, weight, his_itemcode, req_itemcode, req_itemname, combitemna, base_price, item_price, qty, amount, his_recordid, identifier; EXCEPTION when NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('no date found'); -- throw NO_DATA_FOUND EXCEPTION to print dbmsEND; END;