关于Oracle 的定时执行作业问题(动态PLSQL)
oracle数据库定时任务dbms_job的用法详解
oracle数据库定时任务dbms_job的⽤法详解⼀、dbms_job涉及到的知识点1、创建job：variable jobno number;dbms_job.submit(:jobno, —-job号'your_procedure;',—-执⾏的存储过程, ';'不能省略next_date, —-下次执⾏时间'interval' —-每次间隔时间，interval以天为单位);–系统会⾃动分配⼀个任务号jobno。
2、删除job: dbms_job.remove(jobno);3、修改要执⾏的操作: job:dbms_job.what(jobno, what);4、修改下次执⾏时间：dbms_job.next_date(jobno, next_date);5、修改间隔时间：dbms_job.interval(jobno, interval);6、启动job: dbms_job.run(jobno);7、停⽌job: dbms.broken(jobno, broken, nextdate); –broken为boolean值⼆、初始化相关参数job_queue_processes1、job_queue_process表⽰oracle能够并发的job的数量，当job_queue_process值为0时表⽰全部停⽌oracle的job。
2、查看job_queue_processes参数⽅法⼀：show parameter job_queue_process;⽅法⼆：select * from v$parameter where name='job_queue_processes';3、修改job_queue_processes参数alter system set job_queue_processes = 10;三、user_jobs表结构字段（列）类型描述job number 任务的唯⼀标⽰号log_user varchar2(30) 提交任务的⽤户priv_user varchar2(30) 赋予任务权限的⽤户schema_user varchar2(30) 对任务作语法分析的⽤户模式last_date date 最后⼀次成功运⾏任务的时间last_sec varchar2(8) 如hh24:mm:ss格式的last_date⽇期的⼩时，分钟和秒this_date date 正在运⾏任务的开始时间，如果没有运⾏任务则为nullthis_sec varchar2(8) 如hh24:mm:ss格式的this_date⽇期的⼩时，分钟和秒next_date date 下⼀次定时运⾏任务的时间以下使⽤⼀个案例来演⽰dbms_job的使⽤⼀、在plsql中创建表：create table t(id varchar2(30),name varchar2(30));⼆、在plsql中创建存储过程：create or replace procedure proce_t isbegininsert into t(id, name) values('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));commit;end proce_t;/三、创建job任务(1分钟执⾏⼀次)：在sql>后执⾏：variable jobno number;begindbms_job.submit(:jobno,'proce_t;', sysdate, 'sysdate+1/24/60');commit;end;/提交后提⽰：pl/sql procedure successfully completedjobno---------25四、跟踪任务的情况(查看任务队列)：sql> select job, next_date, next_sec, failures, broken from user_jobs;job next_date next_sec failures broken---------- ----------- ---------------- ---------- ------25 2012/9/14 1 10:59:46 0 n说明任务已创建成功。
OracleJob的使用（定时执行）
OracleJob的使⽤（定时执⾏）oracle中的job能为你做的就是在你规定的时间格式⾥执⾏存储过程，定时执⾏⼀个任务。
下⾯是⼀个⼩案例，定时每15分钟向⼀张表插⼊⼀条数据⼀1.创建⼀张测试表-- Create tablecreate table A8(a1 VARCHAR2(500))tablespace DSP_DATApctfree 10initrans 1maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited);2.创建存储过程实现向测试表插⼊数据create or replace procedure proc_add_test asbegininsert into a8 values (to_char(sysdate, 'yyyy-mm-dd hh:mi'));/*向测试表插⼊数据*/commit;end;3.创建job定时任务实现⾃动调⽤存储过程(当前时间 17:03)declarejob number;BEGINDBMS_JOB.SUBMIT(JOB => job, /*⾃动⽣成JOB_ID*/WHAT =>'proc_add_test;', /*需要执⾏的存储过程名称或SQL语句*/NEXT_DATE => sysdate+3/(24*60), /*初次执⾏时间-下⼀个3分钟*/INTERVAL =>'trunc(sysdate,''mi'')+1/(24*60)'/*每隔1分钟执⾏⼀次*/);commit;end;4.也就是应该从17:06开始每隔1分钟执⾏⼀次存储过程下⾯是截⽌17:12分的测试表的数据⼆1.可以通过查询系统表查看该job信息select*from user_jobs；2.⼿动sql调⽤job (直接调⽤job可以忽略开始时间)beginDBMS_JOB.RUN(40); /*40 job的id*/end;3.删除任务begin/*删除⾃动执⾏的job*/dbms_job.remove(40);end;4.停⽌jobdbms.broken(job，broken，nextdate);dbms_job.broken(v_job,true,next_date); /*停⽌⼀个job,⾥⾯参数true也可是false，next_date（某⼀时刻停⽌）也可是sysdate（⽴刻停⽌）。
Oracle过程中执行动态SQL或DDL语句
如果你用的是Oracle8i 及以上的版本，那简单，在过程中用 execute immediate sql_str 就行， sql_str 是一个拼凑的 SQL 语句，但这个动态语句中带参数，或 Select 的结果要 into 到变量中时就要稍加留心一下了。
而在 8i 以前的版本（谁还用这么古老的玩艺，总有些不得已的地方，老系统考虑升级成本遗留下来的，应用软件所伴随着的等），都没法用 execute immediate，就得使用 DBMS_SQL 包来实现了何谓动态 SQL 和 DDL 语句呢？通常在过程中要操作的表名、字段名都必须是明确的，否则编译过程时就要报错，但如果这两者也用变量名来表示就是动态的。
DDL 就是数据库对象定义的操作，如 CREATE TABLE/VIEW/INDEX/SYN/CLUSTER……，及这些对象的删除、修改操作等等。
比如在Oracle中有执行下面过程块的意图时，就要使用到 execute immediate 或是DBMS_SQL 包了。
当然下面的语句块是通不过的。
declarecol_name varchar2(30) := 'name'; --假定表user存在name字段col_val varchar2(30);beginselect col_name into col_val --按照惯常思维，可能就会这么写from user where age between 18 and 25; --编译时会提示列名不存在的drop table t2; --不能直接执行 DDL 语句，后面查询 t2 编译器就无能为力了end;现在我们提出对上面问题的解，针对第一个 Select 语句来说明，并假设查询中还带有参数。
块中的 DDL 也是类似的解法。
例子因力图涵盖更多内容，所以稍显复杂，如果不需要 into （如 update/delete 语句），或者不带参数，会简单多了，应不难简化。
oracle如何使用作业
oracle如何使用作业Oracle可以使用作业来自动执行重复性任务，如定期备份数据库、生成报表等。
以下是Oracle使用作业的步骤：1. 创建作业：使用CREATE_JOB语句来创建作业。
可以指定作业的名称、调度时间表、执行程序、参数等信息。
2. 创建程序：使用CREATE_PROGRAM语句来创建程序。
程序是作业执行的实际任务，可以是存储过程、PL/SQL块、SQL脚本等。
3. 创建调度时间表：使用DBMS_SCHEDULER包中的CREATE_SCHEDULE语句或在Oracle Enterprise Manager工具中创建调度时间表。
调度时间表定义了作业的执行计划，可以指定作业的执行频率和时间范围。
4. 链接作业、程序和时间表：使用DBMS_SCHEDULER包中的CREATE_JOB语句，将作业与程序和时间表进行关联。
5. 启用作业：使用DBMS_SCHEDULER包中的ENABLE函数启用作业。
只有处于启用状态的作业才能被调度执行。
6. 监控作业执行：可以使用DBA_SCHEDULER_JOBS视图来监控作业的执行情况。
可以查看作业的状态、执行次数、最后执行时间等信息。
7. 管理作业：使用DBMS_SCHEDULER包中的ALTER_JOB函数可以修改作业的属性，如修改作业的执行时间、禁用作业等。
使用DROP_JOB函数可以删除作业。
8. 取消作业：使用DBMS_SCHEDULER包中的DISABLE函数可以取消作业的调度。
需要注意的是，Oracle的作业管理功能在某些版本中可能会有所差异，具体使用方法可以参考相关版本的文档。
oracle定时任务（dbms_job）的时间设置参考
oracle定时任务（dbms_job）的时间设置参考最后那⼀项可以参考如下：每天午夜12点 'TRUNC(SYSDATE + 1)'每天早上8点30分 'TRUNC(SYSDATE + 1) + (8*60+30)/(24*60)'每星期⼆中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'每个⽉第⼀天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'每个季度最后⼀天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'每星期六和⽇早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) +(6*60+10)/(24*60)'其中li_jobno是它的ID,可以通过这个ID停掉这个任务，最后想说的是不要执⾏多次，你可以在⾥⾯管理起来，发现已经运⾏了就不SUBMIT select TRUNC(SYSDATE,'MONTH'),last_day(sysdate) from dual--每个⽉的第⼀天和最后⼀天每天运⾏⼀次 'SYSDATE + 1'每⼩时运⾏⼀次 'SYSDATE + 1/24'每10分钟运⾏⼀次 'SYSDATE + 10/（60*24）'每30秒运⾏⼀次 'SYSDATE + 30/(60*24*60)'每隔⼀星期运⾏⼀次 'SYSDATE + 7'不再运⾏该任务并删除它 NULL每年1⽉1号零时 trunc(last_day(to_date(extract(year from sysdate)||'12'||'01','yyyy-mm-dd'))+1-------------------Job的参数：⼀：时间间隔执⾏（每分钟，每天，每周，:每⽉，每季度，每半年，每年）interval是指上⼀次执⾏结束到下⼀次开始执⾏的时间间隔，当interval设置为null时，该job执⾏结束后，就被从队列中删除。
Oracle定时执行计划任务
Oracle定时执行计划任务Oracle在10g版本以前，计划任务用的是DBMS_JOB包，10g版本引入DBMS_SCHEDULER 来替代先前的DBMS_JOB,在功能方面,它比DBMS_JOB提供了更强大的功能和更灵活的机制管理，但DBMS_JOB包的使用相对比较简单，也基本能够满足定时执行计划任务的需求，故接下来就先看看DBMS_JOB包的使用方法。
1.DBMS_JOB我们可以在命令窗口输入show parameter job_queue_processes查看数据库中定时任务的最多并发数，一般设置为10（设置方法：alter system set job_queue_processes=10），如果设为0，那么数据库定时作业是不会运行的。
oracle定时执行job queue 的后台进程是SNP,要启动snp,首先看系统模式是否支持sql> alter system enable restricted session;或sql> alter system disenable restricted session;利用上面的命令更改系统的会话方式为disenable restricted,为snp的启动创建条件.接下来我们尝试实现以下功能：每隔一分钟自动向job_test表中插入当前的系统时间。
1、创测试表create table job_test(updatetime date);2、创建JOBvariable v_job_no number;begindbms_job.submit(:v_job_no, 'insert into job_test values(sysdate);', sysdate, 'sysdate+1/1440');end;/其中最后一个参数'sysdate+1/1440'表示时间间隔为每分钟。
其它常用的时间间隔的设置如下：（1）如果想每天凌晨1点执行，则此参数可设置为'trunc(sysdate)+25/24'；（2）如果想每周一凌晨1点执行，则此参数可设置为'trunc(next_day(sysdate,1))+25/24'；（3）如果想每月1号凌晨1点执行，则此参数可设置为'trunc(last_day(sysdate))+25/24'；（4）如果想每季度执行一次，则此参数可设置为'trunc(add_months(sysdate,3),'Q')+1/24'；（5）如果想每半年执行一次，则此参数可设置为'add_months(trunc(sysdate,'yyyy'),6)+1/24'；（6）如果想每年执行一次，则此参数可设置为'add_months(trunc(sysdate,'yyyy'),12)+1/24'。
Oracle Job定时任务
Oracle Job定时任务oracle job有定时执行的功能，可以在指定的时间点或每天的某个时间点自行执行任务。
一、查询系统中的job，可以查询视图--相关视图select * from dba_jobs;select * from all_jobs;select * from user_jobs;-- 查询字段描述/*字段（列）类型描述JOB NUMBER 任务的唯一标示号LOG_USER VARCHAR2(30) 提交任务的用户PRIV_USER VARCHAR2(30) 赋予任务权限的用户SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式LAST_DATE DATE 最后一次成功运行任务的时间LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时，分钟和秒THIS_DATE DATE 正在运行任务的开始时间，如果没有运行任务则为nullTHIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时，分钟和秒NEXT_DATE DATE 下一次定时运行任务的时间NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时，分钟和秒TOTAL_TIME NUMBER 该任务运行所需要的总时间，单位为秒BROKEN VARCHAR2(1) 标志参数，Y标示任务中断，以后不会运行INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式FAILURES NUMBER 任务运行连续没有成功的次数WHAT VARCHAR2(2000) 执行任务的PL/SQL块CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置MISC_ENV RAW(32) 任务运行的其他一些会话参数*/-- 正在运行jobselect * from dba_jobs_running;其中最重要的字段就是job 这个值就是我们操作job的id号，what 操作存储过程的名称，next_date 执行的时间，interval执行间隔二、执行间隔interval 运行频率描述INTERVAL参数值每天午夜12点TRUNC(SYSDATE + 1)每天早上8点30分TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)每星期二中午12点NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24每个月第一天的午夜12点TRUNC(LAST_DAY(SYSDATE ) + 1)每个季度最后一天的晚上11点TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' )-1/24每星期六和日早上6点10分TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）每秒钟执行次Interval => sysdate + 1/(24 * 60 * 60)如果改成sysdate + 10/(24 * 60 * 60)就是10秒钟执行次每分钟执行Interval => TRUNC(sysdate,'mi') + 1/ (24*60)如果改成TRUNC(sysdate,'mi') + 10/ (24*60) 就是每10分钟执行次每天定时执行例如：每天的凌晨1点执行Interval => TRUNC(sysdate) + 1 +1/ (24)每周定时执行例如：每周一凌晨1点执行Interval => TRUNC(next_day(sysdate,'星期一'))+1/24每月定时执行例如：每月1日凌晨1点执行Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24每季度定时执行例如每季度的第一天凌晨1点执行Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24每半年定时执行例如：每年7月1日和1月1日凌晨1点Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24每年定时执行例如：每年1月1日凌晨1点执行Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24三、创建job方法创建job,基本语法：declarevariable job number;beginsys.dbms_job.submit(job => :job,what => 'prc_name;',next_date => to_date('22-11-2013 09:09:41', 'dd-mm-yyyy hh24:mi:ss'),interval => 'sysdate+1/86400');--每天86400秒钟，即一秒钟运行prc_name过程一次commit;end;使用dbms_job.submit方法过程，这个过程有五个参数：job、what、next_date、interval 与no_parse。
oracle动态sql语句基本语法
oracle动态sql语句基本语法Oracle动态SQL语句是一种在运行时动态生成SQL语句的技术。
它可以根据不同的条件和参数生成不同的SQL语句，从而实现更加灵活和高效的数据操作。
下面是Oracle动态SQL语句的基本语法：1. 使用EXECUTE IMMEDIATE语句执行动态SQL语句：EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE department_id = :dept_id' USING dept_id;2. 使用BIND VARIABLES绑定变量：DECLAREv_dept_id NUMBER := 10;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = :dept_id';EXECUTE IMMEDIATE v_sql USING v_dept_id;END;3. 使用PL/SQL变量拼接SQL语句：DECLAREv_dept_id NUMBER := 10;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = ' || v_dept_id;EXECUTE IMMEDIATE v_sql;END;4. 使用CASE语句生成动态SQL语句：DECLAREv_dept_id NUMBER := 10;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = ';v_sql := v_sql || CASE WHEN v_dept_id IS NULL THEN 'NULL' ELSE TO_CHAR(v_dept_id) END;EXECUTE IMMEDIATE v_sql;END;5. 使用FOR LOOP生成动态SQL语句：DECLAREv_dept_id NUMBER := 10;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id IN (';FOR i IN 1..10 LOOPv_sql := v_sql || i || ',';END LOOP;v_sql := SUBSTR(v_sql, 1, LENGTH(v_sql) - 1) || ')';EXECUTE IMMEDIATE v_sql;END;6. 使用SYS_CONTEXT函数获取当前用户信息：DECLAREv_user VARCHAR2(30) := SYS_CONTEXT('USERENV', 'CURRENT_USER');v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE created_by = ''' || v_user || '''';EXECUTE IMMEDIATE v_sql;END;7. 使用DBMS_SQL包执行动态SQL语句：DECLAREv_cursor INTEGER;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = :dept_id';v_cursor := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);DBMS_SQL.EXECUTE(v_cursor);DBMS_SQL.CLOSE_CURSOR(v_cursor);END;8. 使用DBMS_SQL.RETURN_RESULT函数返回结果集：DECLAREv_cursor INTEGER;v_sql VARCHAR2(100);BEGINv_sql := 'SELECT * FROM employees WHERE department_id = :dept_id';v_cursor := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);DBMS_SQL.EXECUTE(v_cursor);DBMS_SQL.RETURN_RESULT(v_cursor);DBMS_SQL.CLOSE_CURSOR(v_cursor);END;9. 使用DBMS_SQL.DESCRIBE_COLUMNS函数获取结果集元数据：DECLAREv_cursor INTEGER;v_sql VARCHAR2(100);v_col_cnt INTEGER;v_col_desc DBMS_SQL.DESC_TAB;BEGINv_sql := 'SELECT * FROM employees WHERE department_id = :dept_id';v_cursor := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);DBMS_SQL.EXECUTE(v_cursor);v_col_cnt := DBMS_SQL.FETCH_ROWS(v_cursor);DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_cnt, v_col_desc); DBMS_SQL.CLOSE_CURSOR(v_cursor);END;10. 使用DBMS_SQL.COLUMN_VALUE函数获取结果集列值：DECLAREv_cursor INTEGER;v_sql VARCHAR2(100);v_col_cnt INTEGER;v_col_desc DBMS_SQL.DESC_TAB;v_emp_id NUMBER;v_emp_name VARCHAR2(30);BEGINv_sql := 'SELECT employee_id, first_name FROM employees WHERE department_id = :dept_id';v_cursor := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);DBMS_SQL.EXECUTE(v_cursor);v_col_cnt := DBMS_SQL.FETCH_ROWS(v_cursor);DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_cnt, v_col_desc); LOOPEXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) = 0;DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_emp_id);DBMS_SQL.COLUMN_VALUE(v_cursor, 2, v_emp_name);DBMS_OUTPUT.PUT_LINE(v_emp_id || ' ' || v_emp_name);END LOOP;DBMS_SQL.CLOSE_CURSOR(v_cursor);END;以上是Oracle动态SQL语句的基本语法，可以根据实际需求进行灵活应用。
Oracle定时执行存储过程
Oracle定时执行存储过程Oracle定时执行存储过程是一种自动化任务调度的方式，可以使存储过程定期地在指定的时间点执行。
在很多情况下，定时执行存储过程可以提高工作的效率，并减少手动操作的出错可能性。
下面是关于Oracle 定时执行存储过程的一些要点。
1. 创建定时任务：在Oracle中，可以使用DBMS_SCHEDULER包来创建和管理定时任务。
首先，需要创建一个作业，指明需要定时执行的存储过程。
可以使用DBMS_SCHEDULER.CREATE_JOB存储过程来创建作业。
2.指定作业的各种属性：创建作业后，需要指定作业的各种属性，如作业名称、作业类型、所属用户、执行模式等。
可以使用DBMS_SCHEDULER.SET_ATTRIBUTE存储过程来设置各种属性。
3.定义作业的调度计划：要让作业定期执行，需要定义作业的调度计划。
调度计划可以是基于时间的，也可以是基于事件的。
可以使用DBMS_SCHEDULER.SET_ATTRIBUTE存储过程来设置作业的调度计划。
4.启用作业：创建和配置完作业后，需要启用作业，使其可以按计划执行。
可以使用DBMS_SCHEDULER.ENABLE存储过程来启用作业。
5.监控和管理作业：一旦作业启用，它将按计划自动执行。
可以使用DBMS_SCHEDULER.SET_ATTRIBUTE存储过程来设置作业的调度计划。
以及其他相关的存储过程来监控和管理作业，如DBMS_SCHEDULER.DISABLE （禁用作业）、DBMS_SCHEDULER.RUN_JOB（手动运行作业）等。
6.定时任务的日志记录和错误处理：在定时执行存储过程时，常常需要记录执行的日志和处理错误。
可以在存储过程中使用DBMS_OUTPUT.PUT_LINE存储过程来写入日志信息，以及使用异常处理机制来捕获和处理错误。
7.定时任务的权限和安全性：在配置定时任务时，需要确保用户具有足够的权限来执行相关的操作。
PLSQL--Oracle定时任务--常规时间设定
PLSQL--Oracle定时任务--常规时间设定间隔/interval是指上⼀次执⾏结束到下⼀次开始执⾏的时间间隔，当interval设置为null时，该job执⾏结束后，就被从队列中删除。
假如我们需要该job周期性地执⾏，则要⽤‘sysdate＋m’表⽰。
（1）每分钟执⾏Interval => TRUNC(sysdate,‘mi’) + 1/ (24*60)每⼩时执⾏Interval => TRUNC(sysdate,‘hh’) + 1/ (24)（2）每天定时执⾏例如：每天的凌晨1点执⾏Interval => TRUNC(sysdate+ 1) +1/ (24)（3）每周定时执⾏例如：每周⼀凌晨1点执⾏Interval => TRUNC(next_day(sysdate,‘星期⼀’))+1/24（4）每⽉定时执⾏例如：每⽉1⽇凌晨1点执⾏Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24（5）每季度定时执⾏例如：每季度的第⼀天凌晨1点执⾏Interval => TRUNC(ADD_MONTHS(SYSDATE,3),‘Q’) + 1/24（6）每半年定时执⾏例如：每年7⽉1⽇和1⽉1⽇凌晨1点Interval => ADD_MONTHS(trunc(sysdate,‘yyyy’),6)+1/24（7）每年定时执⾏例如：每年1⽉1⽇凌晨1点执⾏Interval =>ADD_MONTHS(trunc(sysdate,‘yyyy’),12)+1/24job的运⾏频率设置1.每天固定时间运⾏，⽐如早上8:10分钟：Trunc(Sysdate+1) + (8*60+10)/24*602.Toad中提供的：每天：trunc(sysdate+1)每周：trunc(sysdate+7)每⽉：trunc(sysdate+30)每个星期⽇：next_day(trunc(sysdate),'星期⽇')每天6点：trunc(sysdate+1)+6/24半个⼩时：sysdate+30/(24*60)3.每个⼩时的第15分钟运⾏，⽐如：8:15，9:15，10:15…：trunc(sysdate,'hh')+(60+15)/(24*60)。
ORACLE执行计划和SQL调优
ORACLE执行计划和SQL调优
Oracle执行计划是一种察看并分析查询处理过程的工具，即可以通
过执行计划了解Oracle数据库在执行SQL查询时的行为，以及查询性能
的一般情况。
Oracle的执行计划分析待查询的SQL语句及其执行路径，
可以在查询性能不理想的情况下，做出相应的调整，以提高查询速度和运
行效果。
针对Oracle数据库执行计划的调优，通常采用五种方法：
（1）使用创建索引的方法.需要分析SQL语句，把经常出现的列和表
给创建索引，以提高查询的速度。
（2）使用查看表空间的方法，如果表空间太小，则把表空间扩展，
以提高SQL语句的执行效率。
（3）调整Oracle的配置参数，把一些参数调大，以提高执行计划的
效率。
（4）优化查询语句，尽量减少不必要的查询，减少查询时间的消耗，提高查询速度。
（5）尽可能采用通过内存进行SQL查询，而不是使用磁盘I/O，以
便提高查询性能。
总之，Oracle数据库的执行计划调优是一个非常重要的任务，可以
通过上述几种方法，以改善查询性能，降低查询延迟，提高数据库的性能。
dbms_job定时规则
dbms_job定时规则
DBMS_JOB 是一个Oracle数据库中用于调度和管理定时任务的包。
它允许用户在数据库中创建、修改、引发和删除定时任务。
DBMS_JOB的定时规则由以下部分组成：
1. Job编号：每个定时任务在数据库中都有一个唯一的编号。
2. Job程序：指定要执行的具体任务，可以是一个过程、函数或者PL/SQL块。
3. 开始日期和时间：指定任务第一次执行的日期和时间。
4. 重复间隔：指定任务之间的间隔时间，可以是分钟、小时、天、星期、月份等。
5. 重复次数：指定任务重复执行的次数，可以是无限次数或者有限次数。
6. 优先级：指定任务的执行优先级，可以是1（最高优先级）到5（最低优先级）。
7. 失败重试：指定任务失败后是否要自动重试，以及重试的时间间隔。
8. 挂起：指定任务是否被挂起，如果挂起，任务将不会执行。
使用DBMS_JOB，可以通过调用存储过程dbms_job.submit来创建一个定时任务，通过调用dbms_job.run来手动触发一个任务的执行，通过调用dbms_job.remove来删除一个任务。
ORACLE动态执行SQL语句
3. 返回结果集
str_column:='sal';--查询的列 str_Total:=0;--总和
str_sql:=' select '|| 'str_column' ||' from emp ' ; execute immediate str_sql into str_Total;
2. 返回单条结果
declare str varchar2(500); c_1 varchar2(10); r_1 test%rowtype; begin c_1:='测试人员'; str:='select * from test where name=:c WHERE ROWNUM=1'; execute immediate str into r_1 using c_1; DBMS_OUTPUT.PUT_LINE(R_||R_1.ADDRESS); end ;
网络错误503请刷新页面重试持续报错请尝试更换浏览器或网络环境
ORACLE动态执行 SQL语句
Oracle 动态SQL有两种写法：用 DBMS_SQL 或 execute immediate，建议使用后者。试验步骤如下：
Oracle 动态SQL有两种写法：用 DBMS_SQL 或 execute immediate，建议使用egin EXECUTE IMMEDIATE 'drop table temp_1'; EXECUTE IMMEDIATE 'create table temp_1(name varchar2(8))'; end;
/*** DML ***/ declare v_1 varchar2(8); v_2 varchar2(10); str varchar2(50); begin v_1:='测试人员'; v_2:='北京'; str := 'INSERT INTO test (name ,address) VALUES (:1, :2)'; EXECUTE IMMEDIATE str USING v_1, v_2; commit; end;
ORACLE动态执行SQL语句
ORACLE动态执行SQL语句
在ORACLE中，可以使用EXECUTEIMMEDIATE语句来动态执行SQL语句。
这个语句可以接受一个字符串作为参数，然后将该字符串作为SQL语句进
行执行。
下面是一个示例：
```
DECLARE
sql_stmt VARCHAR2(100);
BEGIN
sql_stmt := 'INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, ''John'', ''Doe'')';
EXECUTE IMMEDIATE sql_stmt;
sql_stmt := 'SELECT * FROM employees WHERE employee_id = 1';
EXECUTE IMMEDIATE sql_stmt;
END;
```
在上面的示例中，我们首先构造了一个INSERT语句，然后通过EXECUTE IMMEDIATE语句执行该语句，将一个员工数据插入到employees
表中。
然后，我们又构造了一个SELECT语句，并通过EXECUTE IMMEDIATE语句执行该语句，返回了符合条件的员工数据。
需要注意的是，在构造SQL语句时需要注意使用逃逸字符来处理字符串中的引号。
另外，动态执行SQL语句存在一定的安全风险，应该谨慎使用，并对用户输入进行适当的验证和过滤。
Oracle中动态SQL详解（EXECUTEIMMEDIATE）
Oracle中动态SQL详解（EXECUTEIMMEDIATE）Oracle中动态SQL详解(EXECUTE IMMEDIATE)2017年05⽉02⽇ 18:35:48 阅读数：744 标签：更多个⼈分类：Oracle中动态SQL详解1．静态SQLSQL与动态SQL Oracle编译PL/SQL程序块分为两个种：其⼀为前期联编（early binding），即SQL语句在程序编译期间就已经确定，⼤多数的编译情况属于这种类型；另外⼀种是后期联编（late binding），即SQL语句只有在运⾏阶段才能建⽴，例如当查询条件为⽤户输⼊时，那么Oracle的SQL引擎就⽆法在编译期对该程序语句进⾏确定，只能在⽤户输⼊⼀定的查询条件后才能提交给SQL引擎进⾏处理。
通常，静态SQL采⽤前⼀种编译⽅式，⽽动态SQL采⽤后⼀种编译⽅式。
本⽂主要就动态SQL的开发进⾏讨论，并在最后给出⼀些实际开发的技巧。
2．动态SQL程序开发 理解了动态SQL编译的原理，也就掌握了其基本的开发思想。
动态SQL既然是⼀种”不确定”的SQL，那其执⾏就有其相应的特点。
Oracle中提供了Execute immediate语句来执⾏动态SQL，语法如下：Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;对这⼀语句作如下说明： 1)动态SQL是指DDL和不确定的DML（即带参数的DML） 2)绑定参数列表为输⼊参数列表，即其类型为in类型，在运⾏时刻与动态SQL语句中的参数（实际上占位符，可以理解为函数⾥⾯的形式参数）进⾏绑定。
3)输出参数列表为动态SQL语句执⾏后返回的参数列表。
4)由于动态SQL是在运⾏时刻进⾏确定的，所以相对于静态⽽⾔，其更多的会损失⼀些系统性能来换取其灵活性。
为了更好的说明其开发的过程，下⾯列举⼀个实例： 设数据库的emp表，其数据为如下：ID NAME SALARY100Jacky5600101Rose3000102John4500要求： 1．创建该表并输⼊相应的数据。
Oracle存储过程定时任务
DBMS_JOB系统包是Oracle“任务队列”子系统的API编程接口。
DBMS_JOB包对于任务队列提供了下面这些功能：提交并且执行一个任务、改变任务的执行参数以及删除或者临时挂起任务等。
DBMS_JOB包是由ORACLE_HOME目录下的rdbms/admin子目录下的DBMSJOB.SQL和PRVTJOB.PLB 这两个脚本文件创建的。
这两个文件被CATPROC.SQL脚本文件调用，而CATPROC.SQL 这个文件一般是在数据库创建后立即执行的。
脚本为DBMS_JOB包创建了一个公共同义词，并给该包授予了公共的可执行权限，所以所有的Oracle用户均可以使用这个包。
下面几个数据字典视图是关于任务队列信息的，主要有DBA_JOBS, USER_JOBS和DBA_JOBS_RUNNING。
这些字典视图是由名为CATJOBQ.SQL的脚本文件创建的。
该脚本文件和创建DBMS_JOB包的脚本文件一样在ORACLE_HOME目录的rdbms/admin子目录中，同样也是由脚本文件CATPROC.SQL调用。
最后，要使任务队列能正常运行，还必须启动它自己专有的后台过程。
启动后台过程是通过在初始化文件init*.ora(实例不同，初始化文件名也略有不同)中设置初始化参数来进行的。
下面就是该参数：JOB_QUEUE_PROCESSES = n其中，n可以是0到36之间的任何一个数。
除了该参数以外，还有几个关于任务队列的初始化参数，本文后面将会对其进行详细讨论。
DBMS_JOB包中包含有许多过程，见表1所示。
表1 DBMS_JOB包三、DBMS_JOB包参数DBMS_JOB包中所有的过程都有一组相同的公共参数，用于定义任务，任务的运行时间以及任务定时运行的时间间隔。
这些公共任务定义参数见表2所示。
表2 DBMS_JOB过程的公共参数下面我们来详细讨论这些参数的意义及用法。
1、job参数job是一个整数，用来唯一地标示一个任务。
使用Oracle的DBMS_SQL包执行动态SQL语句
ÔÚijЩ³¡ºÏÏ£¬´æ´¢¹ý³Ì»ò´¥·¢Æ÷ÀïµÄSQLÓï¾äÐèÒª¶¯Ì¬Éú³É¡£OracleµÄDBMS_SQL°ü¿ÉÒÔÓÃÀ´Ö´Ðж¯Ì¬SQLÓï¾ä¡£±¾ÎÄͨ¹ýÒ»¸ö¼òµ¥µÄÀý×ÓÀ´Õ¹Ê¾ÈçºÎÀûÓÃDBMS_SQL°üÖ´Ðж¯Ì¬SQLÓï¾ä£ºDECLAREv_cursor NUMBER;v_stat NUMBER;v_row NUMBER;v_id NUMBER;v_no VARCHAR(100);v_date DATE;v_sql VARCHAR(200);s_id NUMBER;s_date DATE;BEGINs_id := 3000;s_date := SYSDATE;v_sql := 'SELECT id,qan_no,sample_date FROM "tblno" WHERE id > :sid and sample_date < :sdate';v_cursor := dbms_sql.open_cursor; --´ò¿ªÓαꣻdbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --½âÎö¶¯Ì¬SQLÓï¾ä£»dbms_sql.bind_variable(v_cursor, ':sid', s_id); --°ó¶¨ÊäÈë²ÎÊý£»dbms_sql.bind_variable(v_cursor, ':sdate', s_date);dbms_sql.define_column(v_cursor, 1, v_id); --¶¨ÒåÁÐdbms_sql.define_column(v_cursor, 2, v_no, 100);dbms_sql.define_column(v_cursor, 3, v_date);v_stat := dbms_sql.execute(v_cursor); --Ö´Ðж¯Ì¬SQLÓï¾ä¡£LOOPEXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0; --fetch_rowsÔÚ½á¹û¼¯ÖÐÒƶ¯Óα꣬Èç¹ûδµÖ´ïĩ⣬·µ»Ø1¡£dbms_sql.column_value(v_cursor, 1, v_id); --½«µ±Ç°ÐеIJéѯ½á¹ûдÈëÉÏÃ涨ÒåµÄÁÐÖС£dbms_sql.column_value(v_cursor, 2, v_no);dbms_sql.column_value(v_cursor, 3, v_date);dbms_output.put_line(v_id || ';' || v_no || ';' || v_date);END LOOP;dbms_sql.close_cursor(v_cursor); --¹Ø±ÕÓαꡣEND;½á¹û£º3095;S051013XW00010;15-10ÔÂ-053112;A051013XW00027;10-10ÔÂ-053113;A051013XW00028;13-10ÔÂ-053116;S051013XW00031;13-10ÔÂ-05¸½£ºDBMS_SQLµÄÎĵµ(À´Ô´£º)Oracle DBMS_SQLVersion 10.2GeneralNote: DMBS_SQL is the traditional form of dynamic SQL in Oracle.For most purposes native dynamic sql (NDS) will suffice but there are some things the DBMS_SQL package does that can not be done any other way. This page emphasizes those areas where there is no substitute.PurposeSource {ORACLE_HOME}/rdbms/admin/dbmssql.sqlConstants Name Data Type Valuev6 INTEGER 0native INTEGER 1v7 INTEGER 2Defined Data Types General TypesTYPE desc_rec IS RECORD (col_type binary_integer := 0,col_max_len binary_integer := 0,col_name varchar2(32) := '',col_name_len binary_integer := 0,col_schema_name varchar2(32) := '',col_schema_name_len binary_integer := 0,col_precision binary_integer := 0,col_scale binary_integer := 0,col_charsetid binary_integer := 0,col_charsetform binary_integer := 0,col_null_ok boolean := TRUE);TYPE desc_rec2 IS RECORD (col_type binary_integer := 0,col_max_len binary_integer := 0,col_name varchar2(32767) := '',col_name_len binary_integer := 0,col_schema_name varchar2(32) := '',col_schema_name_len binary_integer := 0,col_precision binary_integer := 0,col_scale binary_integer := 0,col_charsetid binary_integer := 0,col_charsetform binary_integer := 0,col_null_ok boolean := TRUE);TYPE desc_tab IS TABLE OF desc_recINDEX BY binary_integer;TYPE desc_tab2 IS TABLE OF desc_rec2INDEX BY binary_integer;TYPE varchar2a IS TABLE OF VARCHAR2(32767)INDEX BY binary_integer;TYPE varchar2s IS TABLE OF VARCHAR2(256)INDEX BY binary_integer;Bulk SQL TypesTYPE Bfile_Table IS TABLE OF bfileINDEX BY binary_integer;TYPE Binary_Double_Table IS TABLE OF binary_double INDEX BY binary_integer;TYPE Binary_Float_Table IS TABLE OF binary_float INDEX BY binary_integer;TYPE Blob_Table IS TABLE OF blobINDEX BY binary_integer;TYPE Clob_Table IS TABLE OF clobINDEX BY binary_integer;TYPE Date_Table IS TABLE OF dateINDEX BY binary_integer;TYPE interval_day_to_second_Table IS TABLE OFdsinterval_unconstrained INDEX BY binary_integer;TYPE interval_year_to_MONTH_Table IS TABLE OFyminterval_unconstrainedINDEX BY binary_integer;TYPE Number_Table IS TABLE OF NUMBERINDEX BY binary_integer;TYPE time_Table IS TABLE OF time_unconstrainedINDEX BY binary_integer;TYPE time_with_time_zone_Table IS TABLE OF TIME_TZ_UNCONSTRAINED INDEX BY binary_integer;TYPE timestamp_Table IS TABLE OF timestamp_unconstrainedINDEX BY binary_integer;TYPE timestamp_with_ltz_table IS TABLE OFTIMESTAMP_LTZ_UNCONSTRAINEDINDEX BY binary_integer;TYPE Urowid_Table IS TABLE OF urowidINDEX BY binary_integer;TYPE timestamp_with_time_zone_table IS TABLE OFTIMESTAMP_TZ_UNCONSTRAINEDINDEX BY binary_integer;TYPE Varchar2_Table IS TABLE OF VARCHAR2(2000)INDEX BY binary_integer;Dependencies SELECT nameFROM dba_dependenciesWHERE referenced_name = 'DBMS_SQL'UNIONSELECT referenced_nameFROM dba_dependenciesWHERE name = 'DBMS_SQL';Exceptions Error Code ReasonORA-06562 Inconsistent types: Raised by procedure "column_value" or"variable_value" if the type of the given out argument where to put the requested value is different from the type of the valueBIND_ARRAYBinds a given value to a given collection dbms_sql.BIND_ARRAY(c IN INTEGER,name IN VARCHAR2,<table_variable> IN <datatype>[,index1 IN INTEGER,index2 IN INTEGER)]);DECLAREstmt VARCHAR2(200);dept_no_array dbms_sql.number_table;c NUMBER;dummy NUMBER;BEGINdept_no_array(1) := 10; dept_no_array(2) := 20;dept_no_array(3) := 30; dept_no_array(4) := 40;dept_no_array(5) := 30; dept_no_array(6) := 40;stmt := 'delete from emp where deptno = :dept_array';c := dbms_sql.open_cursor;dbms_sql.parse(c, stmt, dbms_sql.NATIVE);dbms_sql.bind_array(c, ':dept_array', dept_no_array, 1, 4);dummy := dbms_sql.execute(c);dbms_sql.close_cursor(c);EXCEPTIONSWHEN OTHERS THENIF dbms_sql.is_open(c) THENdbms_sql.close_cursor(c);END IF;RAISEEND;/BIND_VARIABLEBinds a given value to a given variable dbms_sql.bind_variable (c IN INTEGER,name IN VARCHAR2,value IN <datatype>)CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AScursor_name INTEGER;rows_processed INTEGER;BEGINcursor_name := dbms_sql.open_cursor;dbms_sql.parse(cursor_name, 'DELETE FROM emp WHERE sal > :x',dbms_sql.NATIVE);dbms_sql.bind_variable(cursor_name, ':x', salary);rows_processed := dbms_sql.execute(cursor_name);dbms_sql.close_cursor(cursor_name);EXCEPTIONWHEN OTHERS THENdbms_sql.close_cursor(cursor_name);END;/BIND_VARIABLE_CHARBinds a given value to a given variable dbms_sql.bind_variabl_char (c IN INTEGER,name IN VARCHAR2,value IN CHAR CHARACTER SET ANY_CS [,out_value_size IN INTEGER]);See bind_variable demoBIND_VARIABLE_RAWBinds a given value to a given variable dbms_sql.bind_variable_raw (c IN INTEGER,name IN VARCHAR2,value IN RAW [,out_value_size IN INTEGER]);See bind_variable demoBIND_VARIABLE_ROWIDBinds a given value to a given variable dbms_sql.bind_variable_rowid ( c IN INTEGER,name IN VARCHAR2,value IN ROWID);See bind_variable demoCLOSE_CURSORCloses cursor and free memory dbms_sql.close_cursor(c IN OUT INTEGER); See is_open demoCOLUMN_VALUEReturns value of the cursor element for a given position in a cursor dbms_sql.column_value (c IN INTEGER,position IN INTEGER,value OUT <datatype>[,column_error OUT NUMBER][,actual_length OUT INTEGER]);See final demoCOLUMN_VALUE_CHARReturns value of the cursor element for a given position in a cursor dbms_sql.column_value_char (c IN INTEGER,position IN INTEGER,value OUT CHAR CHARACTER SET ANY_CS[,column_error OUT NUMBER][,actual_length OUT INTEGER]);See column_value in final demoCOLUMN_VALUE_LONGReturns a selected part of a LONG column, that has been defined using DEFINE_COLUMN_LONG dbms_sql.column_value_long (c IN INTEGER,position IN INTEGER,length IN INTEGER,offset IN INTEGER,value OUT VARCHAR2,value_length OUT INTEGER);See column_value in final demoCOLUMN_VALUE_RAWReturns value of the cursor element for a given position in a cursor dbms_sql.column_value_raw (c IN INTEGER,position IN INTEGER,value OUT RAW[,column_error OUT NUMBER][,actual_length OUT INTEGER]);See column_value in final demoCOLUMN_VALUE_ROWIDUndoc dbms_sql.column_value_rowid (c IN INTEGER,position IN INTEGER,value OUT ROWID[,column_error OUT NUMBER][,actual_length OUT INTEGER]);See column_value in final demoDEFINE_ARRAYDefines a collection to be selected from the given cursor, used only with SELECT statements dbms_sql.define_array (c IN INTEGER,position IN INTEGER,<table_variable> IN <datatype>cnt IN INTEGER,lower_bnd IN INTEGER);DECLAREc number;d number;n_tab dbms_sql.number_table;indx number := -10;BEGINc := dbms_sql.open_cursor;dbms_sql.parse(c,'select n from t order by 1',dbms_sql.NATIVE);dbms_sql.define_array(c, 1, n_tab, 10, indx);d := dbms_sql.execute(c);LOOPd := dbms_sql.fetch_rows(c);dbms_sql.column_value(c, 1, n_tab);exit when d != 10;END LOOP;dbms_sql.close_cursor(c);EXCEPTIONSWHEN OTHERS THENIF dbms_sql.is_open(c) THENdbms_sql.close_cursor(c);END IF;RAISE;END;/DEFINE_COLUMNDefines a column to be selected from the given cursor, used only with SELECT statements dbms_sql.define_column (c IN INTEGER,position IN INTEGER,column IN <datatype>)See final demoDEFINE_COLUMN_CHARUndoc dbms_sql.define_column_char (c IN INTEGER,position IN INTEGER,column IN CHAR CHARACTER SET ANY_CS,column_size IN INTEGER);See define_column in final demoDEFINE_COLUMN_LONGDefines a LONG column to be selected from the given cursor, used only with SELECT statements dbms_sql.define_column_long (c IN INTEGER,position IN INTEGER);See define_column in final demoDEFINE_COLUMN_RAWUndoc dbms_sql.define_column_raw (c IN INTEGER,position IN INTEGER,column IN RAW,column_size IN INTEGER);See define_column in final demoDEFINE_COLUMN_ROWIDUndoc dbms_sql.define_column_rowid (c IN INTEGER,position IN INTEGER,column IN ROWID);See define_column in final demoDESCRIBE_COLUMNSDescribes the columns for a cursor opened and parsed through DBMS_SQL dbms_sql.describe_columns (c IN INTEGER,col_cnt OUT INTEGER,desc_t OUT DESC_TAB);DECLAREc NUMBER;d NUMBER;col_cnt PLS_INTEGER;f BOOLEAN;rec_tab dbms_sql.desc_tab;col_num NUMBER;PROCEDURE print_rec(rec in dbms_sql.desc_rec) ISBEGINdbms_output.new_line;dbms_output.put_line('col_type = '|| rec.col_type);dbms_output.put_line('col_maxlen = '|| rec.col_max_len);dbms_output.put_line('col_name = '|| rec.col_name);dbms_output.put_line('col_name_len = '|| rec.col_name_len);dbms_output.put_line('col_schema_name = '|| rec.col_schema_name);dbms_output.put_line('col_schema_name_len = '|| rec.col_schema_name_len);dbms_output.put_line('col_precision = '|| rec.col_precision);dbms_output.put_line('col_scale = '|| rec.col_scale);dbms_output.put('col_null_ok = ');if (rec.col_null_ok) thendbms_output.put_line('true');elsedbms_output.put_line('false');END IF;END;BEGINc := dbms_sql.open_cursor;dbms_sql.parse(c, 'select * from scott.bonus', dbms_sql.NATIVE);d := dbms_sql.execute(c);dbms_sql.describe_columns(c, col_cnt, rec_tab);/** Following loop could simply be for j in 1..col_cnt loop.* Here we are simply illustrating some of the PL/SQL table* features.*/col_num := rec_tab.first;IF (col_num IS NOT NULL) THENLOOPprint_rec(rec_tab(col_num));col_num := rec_tab.next(col_num);EXIT WHEN (col_num is null);END LOOP;END IF;dbms_sql.close_cursor(c);end;/DESCRIBE_COLUMNS2Describes the specified column, an alternative method dbms_sql.describe_columns2 ( c IN INTEGER,col_cnt OUT INTEGER,desc_tab2 OUT DESC_TAB);Why? ResearchEXECUTEExecute dynamic SQL cursor dbms_sql.execute(c IN INTEGER) RETURN INTEGER; DECLAREsqlstr VARCHAR2(50);tCursor PLS_INTEGER;RetVal NUMBER;BEGINsqlstr := 'DROP SYNONYM my_synonym';tCursor := dbms_sql.open_cursor;dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);RetVal := dbms_sql.execute(tCursor);dbms_sql.close_cursor(tCursor);END;/EXECUTE_AND_FETCHExecutes a given cursor and fetch rows dbms_sql.execute_and_fetch(c IN INTEGER, exact IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;Combine demo w/ last_row_count and last_row_id demosFETCH_ROWSFetches a row from a given cursor dbms_sql.fetch_rows(c IN INTEGER) RETURN INTEGER; See final demoIS_OPENDetermine whether a cursor is open dbms_sql.is_open(c IN INTEGER) RETURN BOOLEAN; set serveroutput onDECLAREtCursor PLS_INTEGER;BEGINtCursor := dbms_sql.open_cursor;IF dbms_sql.is_open(tCursor) THENdbms_output.put_line('1-OPEN');ELSEdbms_output.put_line('1-CLOSED');END IF;dbms_sql.close_cursor(tCursor);IF dbms_sql.is_open(tCursor) THENdbms_output.put_line('2-OPEN');ELSEdbms_output.put_line('2-CLOSED');END IF;END;/LAST_ERROR_POSITIONReturns byte offset in the SQL statement text where the error occurred dbms_st_error_position RETURN INTEGER;LAST_ROW_COUNTReturns cumulative count of the number of rows fetched dbms_st_row_count RETURN INTEGER;LAST_ROW_IDReturns ROWID of last row processed dbms_st_row_id RETURN ROWID;LAST_SQL_FUNCTION_CODEReturns SQL function code for statement dbms_st_sql_function_code RETURN INTEGER;OPEN_CURSOROpen dynamic SQL cursor and return cursor ID number of new cursor dbms_sql.open_cursor RETURN INTEGER;See is_open demoPARSEParse statementOverload 1 dbms_sql.parse(<cursor_variable>, <sql_string>,dbms_sql.NATIVE);CREATE SYNONYM test_syn FOR dual;SELECT *FROM test_syn;SELECT synonym_nameFROM user_synonyms;DECLAREsqlstr VARCHAR2(50);tCursor PLS_INTEGER;BEGINsqlstr := 'DROP SYNONYM test_syn';tCursor := dbms_sql.open_cursor;dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);dbms_sql.close_cursor(tCursor);END;/SELECT synonym_nameFROM user_synonyms;-- with returning clausecreate or replace procedure single_Row_insert(c1 number, c2 number, r out number) isc number;n number;beginc := dbms_sql.open_cursor;dbms_sql.parse(c, 'insert into tab values (:bnd1, :bnd2) ' ||'returning c1*c2 into :bnd3', 2);dbms_sql.bind_variable(c, 'bnd1', c1);dbms_sql.bind_variable(c, 'bnd2', c2);dbms_sql.bind_variable(c, 'bnd3', r);n := dbms_sql.execute(c);dbms_sql.variable_value(c, 'bnd3', r); -- get value of outbind variable dbms_Sql.close_Cursor(c);end;/Parse statementOverload 2 dbms_sql.parse(c IN INTEGER,statement IN VARCHAR2A,lb IN INTEGER,ub IN INTEGER,lfflg IN BOOLEAN,language_flag IN INTEGER);Parse statementOverload 3 dbms_sql.parse(c IN INTEGER,statement IN VARCHAR2S,lb IN INTEGER,ub IN INTEGER,lfflg IN BOOLEAN,language_flag IN INTEGER);VARIABLE_VALUEReturns value of named variable for given cursorOverload 1 dbms_sql.variable_value(c IN INTEGER,name IN VARCHAR2,value OUT <datatype>);Returns value of named variable for given cursorOverload 2 dbms_sql.variable_value(c IN INTEGER,name IN VARCHAR2,<table_varaible> IN <datatype>);VARIABLE_VALUE_CHARUndoc dbms_sql.variable_value_char(c IN INTEGER,name IN VARCHAR2,value OUT CHAR CHARACTER SET ANY_CS);VARIABLE_VALUE_RAWUndoc dbms_sql.variable_value_raw(c IN INTEGER,name IN VARCHAR2,value OUT RAW);VARIABLE_VALUE_ROWIDUndoc dbms_sql.variable_value_rowid(c IN INTEGER,name IN VARCHAR2,value OUT ROWID);DemosDrop Synonym Demo SELECT synonym_nameFROM user_synonyms;CREATE SYNONYM d FOR dept;CREATE SYNONYM e FOR emp;SELECT synonym_nameFROM user_synonyms;CREATE OR REPLACE PROCEDURE dropsyn ISCURSOR syn_cur ISSELECT synonym_nameFROM user_synonyms;RetVal NUMBER;sqlstr VARCHAR2(200);tCursor PLS_INTEGER;BEGINFOR syn_rec IN syn_curLOOPsqlstr := 'DROP SYNONYM ' || syn_rec.synonym_name;tCursor := dbms_sql.open_cursor;dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);RetVal := dbms_sql.execute(tCursor);dbms_sql.close_cursor(tCursor);END LOOP;EXCEPTIONWHEN OTHERS THENRAISE;END dropsyn;/exec dropsyn;SELECT synonym_nameFROM user_synonyms;Executing CLOBS Demo Tables CREATE TABLE workstations ( srvr_id NUMBER(10),ws_id NUMBER(10),location_id NUMBER(10),cust_id VARCHAR2(15),status VARCHAR2(1),latitude FLOAT(20),longitude FLOAT(20),netaddress VARCHAR2(15))TABLESPACE data_sml;CREATE TABLE test (test NUMBER(10));Run Demo CREATE OR REPLACE PROCEDURE execute_plsql_block( plsql_code_block CLOB) ISds_cur PLS_INTEGER := dbms_sql.open_cursor;sql_table dbms_sql.VARCHAR2S;c_buf_len CONSTANT BINARY_INTEGER := 256;v_accum INTEGER := 0;v_beg INTEGER := 1;v_end INTEGER := 256;v_loblen PLS_INTEGER;v_RetVal PLS_INTEGER;----------------------------- local function to the execute_plsql_block procedure FUNCTION next_row (clob_in IN CLOB,len_in IN INTEGER,off_in IN INTEGER) RETURN VARCHAR2 ISBEGINRETURN DBMS_LOB.SUBSTR(clob_in, len_in, off_in); END next_row;---------------------------BEGINv_loblen := DBMS_LOB.GETLENGTH(plsql_code_block);INSERT INTO test VALUES (v_loblen);COMMIT;LOOP-- Set the length to the remaining size-- if there are < c_buf_len characters remaining. IF v_accum + c_buf_len > v_loblen THENv_end := v_loblen - v_accum;END IF;sql_table(NVL(sql_ST, 0) + 1) :=next_row(plsql_code_block, v_end, v_beg);v_beg := v_beg + c_BUF_LEN;v_accum := v_accum + v_end;IF v_accum >= v_loblen THENEXIT;END IF;END LOOP;-- Parse the pl/sql and execute itdbms_sql.parse(ds_cur, sql_table, sql_table.FIRST, sql_ST, FALSE, dbms_sql.NATIVE);v_RetVal := dbms_sql.execute(ds_cur);dbms_sql.close_cursor(ds_cur);END execute_plsql_block;/Executing CLOBS Demo Data DECLAREclob_in CLOB;BEGINclob_in := CAST('BEGININSERT INTO WORKSTATIONS VALUES (1,1,20075,'''',''Y'',32.97948,-117.2569,''''); INSERT INTO WORKSTATIONS VALUES (1,10,20077,'''',''N'',32.97125,-117.2675,''''); INSERT INTO WORKSTATIONS VALUES (1,11,20078,'''',''N'',33.03865,-96.83579,''''); INSERT INTO WORKSTATIONS VALUES (1,12,20079,'''',''Y'',32.97413,-117.2694,''10.128.48.121'');INSERT INTO WORKSTATIONS VALUES (1,2,20081,'''',''N'',32.97948,-117.2569,''''); INSERT INTO WORKSTATIONS VALUES (1,3,20082,'''',''Y'',32.97948,-117.2569,''10.128.0.1'');INSERT INTO WORKSTATIONS VALUES (1,4,20083,''15689'',''N'',32.98195,-117.2636,'''');INSERT INTO WORKSTATIONS VALUES (1,5,20085,'''',''Y'',32.98195,-117.2636,''10.128.16.105'');INSERT INTO WORKSTATIONS VALUES (1,6,20086,'''',''N'',32.97096,-117.2689,''''); INSERT INTO WORKSTATIONS VALUES (1,7,20077,'''',''Y'',32.97125,-117.2675,''10.128.48.105'');INSERT INTO WORKSTATIONS VALUES (1,8,20090,'''',''N'',32.97124,-117.2676,''''); INSERT INTO WORKSTATIONS VALUES (1,9,20092,'''',''N'',32.97023,-117.2688,''''); INSERT INTO WORKSTATIONS VALUES (10,1,20094,'''',''Y'',61.2224,-149.8047,''10.128.112.1'');INSERT INTO WORKSTATIONS VALUES (10,2,20095,'''',''N'',61.2224,-149.8047,''''); INSERT INTO WORKSTATIONS VALUES (10,3,20096,'''',''Y'',61.2224,-149.8047,''10.128.112.113'');INSERT INTO WORKSTATIONS VALUES (10,4,13545,'''',''Y'',61.14104,-149.9519,''10.128.112.121'');INSERT INTO WORKSTATIONS VALUES (10,5,20104,'''',''N'',61.2224,-149.8047,''''); INSERT INTO WORKSTATIONS VALUES (10,6,20106,'''',''Y'',61.21685,-149.8002,''10.128.80.113'');INSERT INTO WORKSTATIONS VALUES (11,1,20110,'''',''N'',61.137,-149.9395,''''); INSERT INTO WORKSTATIONS VALUES (11,10,20113,'''',''N'',61.14104,-149.9519,''''); INSERT INTO WORKSTATIONS VALUES (11,11,20116,'''',''N'',61.14104,-149.9519,''''); INSERT INTO WORKSTATIONS VALUES (11,12,20117,'''',''Y'',61.137,-149.9395,''10.128.32.193'');INSERT INTO WORKSTATIONS VALUES (11,13,20118,'''',''Y'',61.137,-149.9395,''10.128.16.129'');INSERT INTO WORKSTATIONS VALUES (11,14,20119,'''',''Y'',61.137,-149.9395,''''); INSERT INTO WORKSTATIONS VALUES (11,15,20121,'''',''N'',61.13695,-149.9396,''''); INSERT INTO WORKSTATIONS VALUES (11,16,20122,'''',''N'',61.13695,-149.9396,''''); INSERT INTO WORKSTATIONS VALUES (11,17,13545,'''',''Y'',61.14104,-149.9519,'''');INSERT INTO WORKSTATIONS VALUES (11,18,19922,'''',''Y'',61.13549,-149.959,''10.128.48.153'');INSERT INTO WORKSTATIONS VALUES (11,19,19923,'''',''N'',61.13422,-149.962,''''); INSERT INTO WORKSTATIONS VALUES (11,2,19924,'''',''N'',61.137,-149.9395,''''); INSERT INTO WORKSTATIONS VALUES (11,20,19925,'''',''N'',61.146,-149.9799,''''); INSERT INTO WORKSTATIONS VALUES (11,21,19926,'''',''N'',61.146,-149.9799,''''); INSERT INTO WORKSTATIONS VALUES (11,22,19927,'''',''N'',61.13695,-149.9396,''''); INSERT INTO WORKSTATIONS VALUES (11,23,19928,'''',''N'',61.14104,-149.9519,''''); INSERT INTO WORKSTATIONS VALUES (11,24,19930,'''',''N'',61.13422,-149.962,''''); INSERT INTO WORKSTATIONS VALUES (11,25,19931,'''',''N'',61.13678,-149.9644,''''); INSERT INTO WORKSTATIONS VALUES (11,26,20033,'''',''N'',61.14477,-149.9586,''''); INSERT INTO WORKSTATIONS VALUES (11,27,20034,'''',''N'',61.13466,-149.975,''''); INSERT INTO WORKSTATIONS VALUES (11,28,20035,'''',''N'',61.14142,-149.9668,''''); INSERT INTO WORKSTATIONS VALUES (11,29,20036,'''',''N'',61.13695,-149.9396,''''); INSERT INTO WORKSTATIONS VALUES (11,3,20037,'''',''Y'',61.137,-149.9395,''10.128.16.105'');INSERT INTO WORKSTATIONS VALUES (11,30,20038,'''',''N'',61.13695,-149.9396,''''); INSERT INTO WORKSTATIONS VALUES (11,31,20039,'''',''N'',61.13695,-149.9396,''''); INSERT INTO WORKSTATIONS VALUES (11,32,20040,'''',''N'',61.13695,-149.9396,''''); INSERT INTO WORKSTATIONS VALUES (11,33,20042,'''',''N'',61.12887,-149.9578,''''); INSERT INTO WORKSTATIONS VALUES (11,4,20043,'''',''N'',61.137,-149.9395,''''); INSERT INTO WORKSTATIONS VALUES (11,5,20044,'''',''Y'',61.137,-149.9395,''10.128.32.129'');INSERT INTO WORKSTATIONS VALUES (11,6,20045,'''',''N'',61.137,-149.9395,''''); INSERT INTO WORKSTATIONS VALUES (11,7,20046,'''',''N'',61.137,-149.9395,''''); INSERT INTO WORKSTATIONS VALUES (11,8,20047,'''',''N'',61.137,-149.9395,'''');--====INSERT INTO WORKSTATIONS VALUES (11,9,20048,'''',''Y'',61.137,-149.9395,''10.128.32.169'');INSERT INTO WORKSTATIONS VALUES (12,1,20051,''15706'',''Y'',32.75604,-117.1201,'''');INSERT INTO WORKSTATIONS VALUES (12,10,20053,'''',''N'',32.75689,-117.12,''''); INSERT INTO WORKSTATIONS VALUES (12,100,20054,'''',''N'',32.7596,-117.124,''''); INSERT INTO WORKSTATIONS VALUES (12,101,20056,'''',''N'',32.75689,-117.129,''''); INSERT INTO WORKSTATIONS VALUES (12,102,20057,'''',''Y'',32.75677,-117.1241,''10.129.112.25'');INSERT INTO WORKSTATIONS VALUES (12,103,20058,'''',''Y'',32.75662,-117.124,''10.129.112.33'');INSERT INTO WORKSTATIONS VALUES (12,104,20060,'''',''N'',32.7571,-117.1242,''''); INSERT INTO WORKSTATIONS VALUES (12,105,20061,'''',''N'',32.75316,-117.1253,''''); INSERT INTO WORKSTATIONS VALUES (12,106,20063,'''',''N'',32.76154,-117.1251,''''); --====COMMIT;END;' AS CLOB);。
oracle定时任务时间写法
在Oracle 数据库中，可以使用DBMS_SCHEDULER 包来创建和管理定时任务。
定时任务的时间写法遵循标准的cron 表达式语。
cron 表达式由个或六个字段组成，分表示分钟、小时日期、月份和星期几。
以下是cron 表达式的常见写法：
1. 每分钟执行次：`* * * * *`
2. 每小时的第30 分钟执行一次：`30 * * * *`
3. 每天的8 点执行一次`0 8 * * *`
4. 每周一的9 点执行一次：`0 9 * * 1`
5. 每的第一天12 点执行一次：`0 12 1 * *`
6. 每年的1 月1 日的00:00 执行一次：`0 0 1 1 *`
其中，`*` 表示匹配任意值，例如`* * * * *` 表示每分钟都执行；数字表示具体的数值例如`30 * * * *` 表示每小时的第30 分钟执行；多个数值可以用逗号分隔，例如`1,15,30 * * * *` 表示每小时的第1、15 和30 分钟执行。
此外，还可以使用特字符来表示一些特定的时间段例如：
- `/` 表示步，例如`*/5 * * * *` 表示每隔5 分钟执行一次。
- `-` 表示范围，例如`0 9-17 * * *` 表示每天9 点到17 点之每小时执行一。
以上是一些常见的定时任务时间写法，你可以根据具体求来编写适合的cro n 表式。
Oracle执行计划 SQL语句执行效率问题查找与解决方法
Oracle的SQL语句执行效率问题查找与解决方法一、识别占用资源较多的语句的方法（4种方法）1.测试组和最终用户反馈的与反应缓慢有关的问题。
2.利用V_$SQLAREA视图提供了执行的细节。
（执行、读取磁盘和读取缓冲区的次数）•数据列EXECUTIONS：执行次数DISK_READS：读盘次数COMMAND_TYPE：命令类型（3:select,2:insert;6:update;7delete;47:pl/sql程序单元）OPTIMIZER_MODE：优化方式SQL_TEXT：Sql语句SHARABLE_MEM：占用shared pool的内存多少BUFFER_GETS：读取缓冲区的次数•用途1、帮忙找出性能较差的SQL语句2、帮忙找出最高频率的SQL3、帮忙分析是否需要索引或改善联接3.监控当前Oracle的session，如出现时钟的标志，表示此进程中的sql运行时间较长。
4.Trace工具：a)查看数据库服务的初始参数：timed_statistics、user_dump_dest和max_dump_file_sizeb)Step 1: alter session set sql_trace=truec)Step 2: run sql；d)Step 3: alter session set sql_trace=falsee)Step 4:使用“TKPROF”转换跟踪文件f)Parse，解析数量大通常表明需要增加数据库服务器的共享池大小，query或current提取数量大表明如果没有索引，语句可能会运行得更有效，disk提取数量表明索引有可能改进性能，library cache中多于一次的错过表明需要一个更大的共享池大小二、如何管理语句处理和选项•基于成本（Cost Based）和基于规则（Rule Based）两种优化器，简称为CBO 和RBO •Optimizer Mode参数值:Choose：如果存在访问过的任何表的统计数据，则使用基于成本的Optimizer,目标是获得最优的通过量。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
方法：oracle 的动态SQL，一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。
如何把下数的业务用PL/SQL实现?
1.业务操作流水表ODS_PRODUCT_SEQUENCE_DM_434
操作日期：每月1日上午即可
操作方式：按标号顺序在一个ORACLE SQL*PLUS中顺序执行
（1）备份
conn boss/******8@jyfx36;
select count(*) from jlcrm.ods_product_sequence_dm_434
where start_datetime>=to_date('20090201','yyyymmdd')
and start_datetime<to_date('20090301','yyyymmdd');
conn boss/******@sp87;
select count(*) from yxf_oper_200902
where start_datetime>=to_date('20090201','yyyymmdd')
and start_datetime<to_date('20090301','yyyymmdd');
如上述两个执行结果相等，备份成功，否则，重新查找原因。
解决办法:
CREATE OR REPLACE PROCEDURE "SCOTT"."BACKDATA" (
vdate in varchar2
) as
str_sql1 varchar2(500);
str_sql2 varchar2(150);
str_sql3 varchar(350);
str_sql4 varchar2(450);
str_sql5 varchar(450);
cnt int;
tablename varchar(20):='yxf_oper_'||vdate;
begin
select count(*) into cnt from cat where table_name=upper(trim(tablename));
if cnt > 0 then
str_sql2:='drop table ' ||tablename ;
execute immediate str_sql2 ;
end if;
str_sql1:= 'create table ' || tablename || ' as select * from yxf_oper_mode' ;
execute immediate str_sql1 ;
str_sql3:= 'insert into yxf_oper_'|| vdate || '(select * from yxf_oper_'|| vdate || 'tmp where start_datetime>=to_date('||''''|| vdate || '01' ||''''||',' || '''yyyymmdd''' || ')'||' and
start_datetime<=to_date('||''''||vdate ||'31'||'''' ||',' || '''yyyymmdd'''||'))' ;
-- execute immediate str_sql3 ;
--execute immediate 'commit';
str_sql4:='create index inde_yxf_oper_' || vdate|| ' on yxf_oper_' || vdate || ' (phone_no)' ;
-- execute immediate str_sql4 ;
str_sql5:='create index inde_yxf_oper_1'||vdate||' on yxf_oper_'||vdate||' (id_no)';
-- execute immediate str_sql5 ;
DBMS_OUTPUT.PUT_LINE(str_sql1);
-- DBMS_OUTPUT.PUT_LINE(str_sql2);
DBMS_OUTPUT.PUT_LINE(str_sql3);
DBMS_OUTPUT.PUT_LINE(str_sql4);
DBMS_OUTPUT.PUT_LINE(str_sql5);
exception
when others then
DBMS_OUTPUT.PUT_LINE('error');
end ;。