Oracle Package (程序包) 完整指南：規范與主體、游標、內置包實戰 (DBMS_JOB/UTL_FILE/DBMS_RANDOM)

Oracle Package (程序包) 完整指南：規范與主體、游標、內置包實戰

Package 結構

• Specification (包頭/規范)：公開介面 (變量、游標、過程/函數簽名)
• Body (包體/主體)：實現細節、私有變量/子程序

優點：模組化、資訊隱藏、效能優化 (整體載入共享池)、功能擴展

基礎範例：建立包頭/包體

CREATE OR REPLACE PACKAGE pack1 IS
  aa NUMBER := 1;                              -- 公有變量
  PROCEDURE update_student (p_row IN student%ROWTYPE);
  PROCEDURE insert_student (p_row IN student%ROWTYPE);
END pack1;

CREATE OR REPLACE PACKAGE BODY pack1 IS
  bb NUMBER := 2;                              -- 私有變量
  PROCEDURE insert_student (p_row IN student%ROWTYPE) IS
  BEGIN
    INSERT INTO student(id,name,age) VALUES (p_row.id,p_row.name,p_row.age);
    DBMS_OUTPUT.PUT_LINE('bb='||bb||' aa='||aa);
  END;
  PROCEDURE update_student (p_row IN student%ROWTYPE) IS
  BEGIN
    UPDATE student SET name='趙北' WHERE id=p_row.id;
  END;
END pack1;

調用驗證

SET SERVEROUTPUT ON;
EXEC DBMS_OUTPUT.PUT_LINE(pack1.aa);  -- 1 (公有可見)
EXEC DBMS_OUTPUT.PUT_LINE(pack1.bb);  -- ORA-06550: 必須聲明 'BB' (私有不可見)

DECLARE
  r_student student%ROWTYPE;
BEGIN
  r_student.id:=5; r_student.name:='張飛'; r_student.age:=60;
  pack1.insert_student(r_student);  -- bb=2 aa=1
  r_student.name:='關羽';
  pack1.update_student(r_student);  -- 更新 id=5 名稱
END;

包中的游標

顯式游標 (需 RETURN 子句)

CREATE OR REPLACE PACKAGE pack2 IS
  CURSOR student_cursor RETURN student%ROWTYPE;
  PROCEDURE student_pro;
END pack2;

CREATE OR REPLACE PACKAGE BODY pack2 IS
  CURSOR student_cursor RETURN student%ROWTYPE IS SELECT * FROM student;
  PROCEDURE student_pro IS
    r_student student%ROWTYPE;
  BEGIN
    OPEN student_cursor;
    FETCH student_cursor INTO r_student;
    WHILE student_cursor%FOUND LOOP
      DBMS_OUTPUT.PUT_LINE('學號='||r_student.id||' 姓名='||r_student.name);
      FETCH student_cursor INTO r_student;
    END LOOP;
    CLOSE student_cursor;
  END;
END pack2;

REF CURSOR (弱類型/強類型)

CREATE OR REPLACE PACKAGE pack3 IS
  TYPE ref_cursor IS REF CURSOR;  -- 弱類型
  PROCEDURE ref_student_pro;
END pack3;

CREATE OR REPLACE PACKAGE BODY pack3 IS
  PROCEDURE ref_student_pro IS
    r_student student%ROWTYPE;
    c_ref SYS_REFCURSOR;
  BEGIN
    OPEN c_ref FOR SELECT * FROM student;
    FETCH c_ref INTO r_student;
    WHILE c_ref%FOUND LOOP
      DBMS_OUTPUT.PUT_LINE('學號='||r_student.id||' 姓名='||r_student.name);
      FETCH c_ref INTO r_student;
    END LOOP;
    CLOSE c_ref;
  END;
END pack3;

常用內置包實戰

DBMS_JOB (10g 前排程核心)

CREATE TABLE test_job (run_date DATE);
CREATE OR REPLACE PROCEDURE pro_test IS BEGIN INSERT INTO test_job VALUES (SYSDATE); END;

VARIABLE job1 NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:job1, 'pro_test;', SYSDATE, 'SYSDATE + 1/1440'); -- 每分鐘
END;
/
BEGIN DBMS_JOB.RUN(:job1); END;  -- 立即執行
BEGIN DBMS_JOB.REMOVE(:job1); END; -- 刪除作業

UTL_FILE (文件讀寫)

CREATE DIRECTORY dir_utl_file AS '/u01/app/oracle/test_utl_file';
GRANT READ, WRITE ON DIRECTORY dir_utl_file TO hr;

CREATE OR REPLACE PROCEDURE pro_utl_file (p_dir IN VARCHAR2, p_file IN VARCHAR2) IS
  fh UTL_FILE.FILE_TYPE;
  line VARCHAR2(2000);
BEGIN
  fh := UTL_FILE.FOPEN(p_dir, p_file, 'R', 2000);
  LOOP
    UTL_FILE.GET_LINE(fh, line);
    DBMS_OUTPUT.PUT_LINE(line);
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE(fh);
END;

DBMS_RANDOM (隨機數/日期)

-- 某年內隨機日期
SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(TO_DATE('01/01/03','MM/DD/YY'),'J'), TO_CHAR(TO_DATE('01/01/03','MM/DD/YY'),'J')+364)),'J') FROM DUAL;