数据库技术:oracle存储过程动态sql语句实例讲解

注意事项：

在字符串中 ‘’ 代表一个‘ 。例：’select ” ” ,a from table’ 中

需要了解更多数据库技术:oracle存储过程动态sql语句实例讲解，都可以关注数据库技术分享栏目—编程笔记

  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);  -- **在字符串中 ‘’代表一个‘ 。例：&＃39;select &＃39;&＃39; &＃39;&＃39; ,a from table’ 中 &＃39;&＃39;中 的&＃39;&＃39; &＃39;&＃39;代表一个空格，即&＃39;&＃39;代表一个&＃39;**  begin  sql_string:=    &＃39; select   &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  n."clinic_patient_id",  n."patient_id",  n."treatment_card_no",  &＃39;&＃39; &＃39;&＃39;,  n."operate_time",  c."patient_name",  c."patient_sex",  p."birthday",  n."diagnosis",  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  n."treatment_office_id",  n."doctor_id",  to_char(n."operate_time",&＃39;&＃39;yyyy-mm-dd&＃39;&＃39;) operate_time,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  n."exec_dept_id",  n."system_type",  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  n."status",  f."balance_operator",  to_char(f."balance_time",&＃39;&＃39;yyyy-mm-dd&＃39;&＃39;) balance_time,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  p."address",  p."nationality",  p."idcard_no",  p."mobile",  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  n."item_no",  &＃39;&＃39; &＃39;&＃39;,  n."item_name",  &＃39;&＃39; &＃39;&＃39;,  n."unit_price",  n."unit_price",  n."item_quantity",  n."total_price",  n."id",  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;,  &＃39;&＃39; &＃39;&＃39;   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"&＃39;;    if card_type is null and is_vaild is null then  sqlr_result:=sql_string ||&＃39;  where n."treatment_card_no" = &＃39;&＃39;&＃39;||card_no||&＃39;&＃39;&＃39;   and n."system_type" = 9  and (to_char(n."operate_time", &＃39;&＃39;yyyy-mm-dd&＃39;&＃39;) between &＃39;&＃39;&＃39;||start_date||&＃39;&＃39;&＃39;and &＃39;&＃39;&＃39;||end_date||&＃39;&＃39;&＃39;)&＃39;;    elsif card_type is not null and is_vaild is null then   sqlr_result:= sql_string ||&＃39;  where n."treatment_card_no" = &＃39;&＃39;&＃39;||card_no||&＃39;&＃39;&＃39;   and n."system_type" = 9  and (to_char(n."operate_time", &＃39;&＃39;yyyy-mm-dd&＃39;&＃39;) between &＃39;&＃39;&＃39;||start_date||&＃39;&＃39;&＃39;and &＃39;&＃39;&＃39;||end_date||&＃39;&＃39;&＃39;)  and  p."id_categ_code" = &＃39;||card_type||&＃39;&＃39;; ***--而在这里&＃39;&＃39;代表一个空格***      ----------      ----------      elsif card_type is null and is_vaild is not null then  sqlr_result:= sql_string ||&＃39;  where n."treatment_card_no" = &＃39;&＃39;&＃39;||card_no||&＃39;&＃39;&＃39;   and n."system_type" = 9  and (to_char(n."operate_time", &＃39;&＃39;yyyy-mm-dd&＃39;&＃39;) between &＃39;&＃39;&＃39;||start_date||&＃39;&＃39;&＃39;and &＃39;&＃39;&＃39;||end_date||&＃39;&＃39;&＃39;)  and p."is_valid"=&＃39;||is_vaild||&＃39;&＃39;;  elsif card_type is not null and is_vaild is not null then  sqlr_result:= sql_string ||&＃39;  where n."treatment_card_no" = &＃39;&＃39;&＃39;||card_no||&＃39;&＃39;&＃39;  and n."system_type" = 9   and (to_char(n."operate_time", &＃39;&＃39;yyyy-mm-dd&＃39;&＃39;) between &＃39;&＃39;&＃39;||start_date||&＃39;&＃39;&＃39; and &＃39;&＃39;&＃39;||end_date||&＃39;&＃39;&＃39;)  and p."is_valid"=&＃39;||is_vaild||&＃39;   and p."id_categ_code" =&＃39;||card_type||&＃39;&＃39;;  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(&＃39;no date found&＃39;);--抛出no_data_found异常打印dbms  end;  end;