动态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来读写外部文件,其一般处理过程为:
![](https://i-blog.csdnimg.cn/blog_migrate/666df4a1f5d24774b06a51ae21ded51e.png)
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;