oracle存储过程动态sql语句实例讲解,oracle实例讲解
注意事项:
在字符串中 ‘’ 代表一个‘ 。例:’select ” ” ,a from table’ 中
CREATE OR REPLACE PROCEDURE sp_lisgetoutpfee ( card_type in VARCHAR2, card_no in VARCHAR2, start_date in VARCHAR2, end_date in VARCHAR2, is_vaild in VARCHAR2, hospitalcode out VARCHAR2, pat_type out VARCHAR2, pat_no out 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, req_wardno out VARCHAR2, req_bedno out VARCHAR2, req_comm out VARCHAR2, req_deptno out VARCHAR2, req_docno out VARCHAR2, req_dt out VARCHAR2, emer_flag out VARCHAR2, original_reqno out VARCHAR2, perform_dept out VARCHAR2, req_groupna out VARCHAR2, specimen_name out VARCHAR2, sample_detail out VARCHAR2, req_reason out VARCHAR2, sample_items out VARCHAR2, charge_flag out VARCHAR2, charge_user out VARCHAR2, charge_dt out VARCHAR2, secrecy out VARCHAR2, other_stat out VARCHAR2, abo_bldtype out VARCHAR2, rh_bldtype out VARCHAR2, pat_diag_icd out VARCHAR2, pat_address out VARCHAR2, pat_nation out VARCHAR2, pat_idcardno out VARCHAR2, 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, his_refcol1 out VARCHAR2, his_refcol2 out VARCHAR2, his_refcol3 out VARCHAR2 )AS sql_string VARCHAR2(2000); sqlr_result VARCHAR2(2000); -- **在字符串中 ‘’代表一个‘ 。例:'select '' '' ,a from table’ 中 ''中 的'' ''代表一个空格,即''代表一个'** BEGIN sql_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 THEN sqlr_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||''')'; 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" = 9 AND (TO_CHAR(n."operate_time", ''YYYY-MM-DD'') BETWEEN '''||start_date||'''AND '''||end_date||''') AND p."id_categ_code" = '||card_type||''; ***--而在这里''代表一个空格*** ---------- ---------- ELSIF card_type is NULL AND is_vaild IS NOT NULL THEN sqlr_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||''; ELSIF card_type is NOT NULL AND is_vaild IS NOT NULL THEN sqlr_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; BEGIN dbms_output.put_line(sqlr_result); -- 输出sqlr_result中的sql的语句 EXECUTE IMMEDIATE sqlr_result INTO hospitalcode, -- 执行sqlr_result 中的sql 语句 并把值into给下面的字段 pat_type, pat_no, pat_id, pat_cardno, inp_id, inp_date, pat_name, pat_sex, pat_birth, pat_diag, charge_typeno, req_wardno, req_bedno, req_comm, req_deptno, req_docno, req_dt, emer_flag, original_reqno, perform_dept, req_groupna, specimen_name, sample_detail, req_reason, sample_items, charge_flag, charge_user, charge_dt, secrecy, other_stat, abo_bldtype, rh_bldtype, pat_diag_icd, pat_address, pat_nation, pat_idcardno, pat_phone, pat_height, weight, his_itemcode, req_itemcode, req_itemname, combitemna, base_price, item_price, qty, amount, his_recordid, his_refcol1, his_refcol2, his_refcol3; EXCEPTION when NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('no date found');--抛出NO_DATA_FOUND异常打印dbms END; END;
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。