注意事项:
在字符串中 ‘’ 代表一个‘ 。例:’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;