PL/SQL:程序語言：SQL、程序語言的三大結構：循環、判斷、順序

語言塊：Block

存儲過程:Procedure

函數:Function

執行任務：Jobs

觸發器：Trigger

 

塊：

DECLARE
--定義變量
v_a VARCHAR2(10) := 'AAA';
BEGIN
  NULL;
  DBMS_OUTPUT.PUT_LINE(v_a);
END;

 

存儲過程：

CREATE OR REPLACE PROCEDURE t1(v_i VARCHAR2) IS
--DECLARE
--定義變量
--v_a VARCHAR2(10) := 'AAA';
BEGIN
  NULL;
  DBMS_OUTPUT.PUT_LINE(v_i);
END t1;

--帶游標的存儲過程
CREATE OR REPLACE PROCEDURE p_t2 IS
  CURSOR c_cur IS(
    SELECT *
      FROM TEST_TEM_COURSE_DIS t
    --GROUP BY t.inpatient_no
    );
BEGIN
  FOR v_content IN c_cur LOOP
    DBMS_OUTPUT.put_line(v_content.patient_id);
  END LOOP;
END p_t2;

 

存儲過程應用實例：

--單行多病程 轉 多行單病程
create or replace procedure p_course_dis is
  v_col_record_time    varchar2(4000) := '';--列名
  v_col_record_content varchar2(4000) := '';
  v_col_record_doctor  varchar2(4000) := '';
  v_record_time        varchar2(4000) := '';--列值
  v_record_content     varchar2(4000) := '';
  v_reccord_doctor     varchar2(4000) := '';
  cursor v_rows is select reference_id from MULTICOL_COURSE_DIS;--主鍵游標
  v_sql VARCHAR2(4000) := '';--sql語句
begin
  for v_row in v_rows loop--遍歷表
    for v_i in 1 .. 3 loop--循環列
      v_col_record_time    := 'record_time' || v_i;--列名賦值
      v_col_record_content := 'record_content' || v_i;
      v_col_record_doctor  := 'record_doctor' || v_i;
      v_sql := 'select ' || v_col_record_time || ',' 
                         || v_col_record_content || ',' 
                         || v_col_record_doctor ||
              ' from MULTICOL_COURSE_DIS
                where reference_id = ''' || v_row.reference_id || '''';
      EXECUTE IMMEDIATE v_sql into v_record_time, 
                                   v_record_content, 
                                   v_reccord_doctor;--執行sql
      DBMS_OUTPUT.put_line(v_record_time || ' ' 
                        || v_record_content || ' ' 
                        || v_reccord_doctor);--輸出樣例
    end loop;
  end loop;
end p_course_dis;