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