欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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;