6.9内置PLSQL工具包

Oracle PLSQL

1、动态SQL

不是在Designer Time写的SQL，而是可以在运行时临时拼接起来的SQL语句； 动态SQL可以使用Oracle 内置包 DBMS_SQL 来执行，也可以使用EXECUTE IMMEDIATE 语句来执行

CREATE OR REPLACE PROCEDURE delete_all_rows
 (p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)
IS
 cursor_name INTEGER;
BEGIN
 cursor_name := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(cursor_name, 'DELETE FROM '||p_tab_name,
 DBMS_SQL.NATIVE );
 p_rows_del := DBMS_SQL.EXECUTE (cursor_name);
 DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/

1，EXECUTE IMMEDIATE执行例子

CREATE PROCEDURE del_rows
 (p_table_name IN VARCHAR2,
 p_rows_deld OUT NUMBER)
IS
BEGIN
 EXECUTE IMMEDIATE 'delete from '||p_table_name;
 p_rows_deld := SQL%ROWCOUNT;
END;
/

2，动态SQL实际应用例子

POS基础数据同步并发程序 dpos_base_sync.pck

A.动态SQL的好处

在某些应用环境中可以做到灵活配置，避免因为添加一种业务而更改代码或者新建代码；

B.动态SQL的缺点

调试及阅读理解困难

2、程序中执行DDL

在程序中执行DDL，可使用Oracle 内置包：DBMS_DDL

1，在程序中执行编译命令

DBMS_DDL.ALTER_COMPILE('PROCEDURE','A_USER','QUERY_EMP')

2，在程序中执行数据收集命令

DBMS_DDL.ANALYZE_OBJECT('TABLE','A_USER','JOBS','COMPUTE')

3、Oracle数据库JOB

定义JOB 可以定期执行某个程序
应用场景：比如每隔一周对某些表进行数据收集，以确保CBO正确，又比如在消息处理机制中，每隔5分钟对 消息队列进行扫描处理等。
Oracle提供内置包 DBMS_JOB，可完成JOB的定义、提交、更改、停止、移除。

例子1：提交一个JOB 每隔1天执行一次

DECLARE
 jobno NUMBER;
BEGIN
 DBMS_JOB.SUBMIT (
 job => jobno ,
 what => 'OVER_PACK.ADD_DEPT(''EDUCATION'',2710);',
 next_date => TRUNC(SYSDATE + 1),
 interval => 'TRUNC(SYSDATE + 1)'
 );
dbms_output.put_line(„job_no =„ || jobno )
 COMMIT;
END;

例子2：更改JOB的执行频率为：每4小时执行一次

BEGIN
 DBMS_JOB.CHANGE(1, NULL, TRUNC(SYSDATE+1)+6/24, ‟SYSDATE+4/24');
END;

查找自己提交的JOB号

SELECT job, log_user, next_date, next_sec, broken, what FROM DBA_JOBS;

4、PLSQL中读写外部文件

Oracle 提供内置包UTL_FILE来读写外部文件，其一般处理过程为

UTL_FILE应用举例

CREATE OR REPLACE PROCEDURE sal_status
        (p_filedir IN VARCHAR2, p_filename IN VARCHAR2)
IS
    v_filehandle UTL_FILE.FILE_TYPE;
    CURSOR emp_info IS
         SELECT last_name, salary, department_id
         FROM employees
         ORDER BY department_id;
    v_newdeptno employees.department_id%TYPE;
    v_olddeptno employees.department_id%TYPE := 0;
BEGIN
    v_filehandle := UTL_FILE.FOPEN (p_filedir, p_filename,'w');
    UTL_FILE.PUTF (v_filehandle,'SALARY REPORT: GENERATED ON
     %s\n', SYSDATE);
    UTL_FILE.NEW_LINE (v_filehandle);
    FOR v_emp_rec IN emp_info LOOP
     v_newdeptno := v_emp_rec.department_id;
    ...
    ...
     IF v_newdeptno <> v_olddeptno THEN
     UTL_FILE.PUTF (v_filehandle, 'DEPARTMENT: %s\n',
     v_emp_rec.department_id);
     END IF;
     UTL_FILE.PUTF (v_filehandle,' EMPLOYEE: %s earns: %s\n',
     v_emp_rec.last_name, v_emp_rec.salary);
     v_olddeptno := v_newdeptno;
     END LOOP;
     UTL_FILE.PUT_LINE (v_filehandle, '*** END OF REPORT ***');
     UTL_FILE.FCLOSE (v_filehandle);
EXCEPTION
     WHEN UTL_FILE.INVALID_FILEHANDLE THEN
     RAISE_APPLICATION_ERROR (-20001, 'Invalid File.');
     WHEN UTL_FILE.WRITE_ERROR THEN
     RAISE_APPLICATION_ERROR (-20002, 'Unable to write to file');
END sal_status;
/