Oracle数据库编程

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;