oracle存储过程动态sql语句实例讲解

注意事项：

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