oracle存储过程动态sql语句实例讲解
程序员文章站
2022-11-18 13:10:24
注意事项:
在字符串中 ‘’ 代表一个‘ 。例:’select ” ” ,a from table’ 中
cr...
注意事项:
在字符串中 ‘’ 代表一个‘ 。例:’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;
下一篇: 怎样做自己的二级域名(之二)