PLSQL内置工具包

动态SQL：不是在Designer Time写的SQL, 而是可以在运行时临时拼接起来的SQL语句

例如：

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;

动态SQL可以使用Oracle 内置包 DBMS_SQL 来执行，也可以使用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;

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

动态SQL缺点： 调试及阅读理解困难

 

程序中执行DDL：

如果想在程序中执行DDL,可使用Oracle 内置包：DBMS_DDL

比如在程序中执行数据收集命令：

DBMS_DDL.ANALYZE_OBJECT('TABLE','A_USER','JOBS','COMPUTE'【收集指令】)

 

Oracle数据库JOB：

定义JOB 可以定期执行某个程序，

应用场景：比如每隔一周对某些表进行数据收集，以确保CBO正确，又比如在消息处理机制中，每隔5分钟对 消息队列进行扫描处理等。

Oracle提供内置包 DBMS_JOB，可完成JOB的定义、提交、更改、停止、移除。

例如：

DECLARE jobno NUMBER;

BEGIN

DBMS_JOB.SUBMIT ( job => jobno ,

what => 'OVER_PACK.ADD_DEPT(''EDUCATION'',2710);',——what值为执行语句，语句中带引号的再加个单引号以视区分

next_date => TRUNC(SYSDATE + 1),

interval => 'TRUNC(SYSDATE + 1)' ); ————间隔1天更新一次

dbms_output.put_line(„job_no =„ || jobno )

COMMIT;

END;

更改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;

 

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;