PL/SQL程序设计（九）

第九章  ORACLE提供的常用包

内容：

l  使用DBMS_SQL和EXECUTE IMMEDIATE 写动态的SQL 语句；

l  描述ORACLE 服务器提供的常用包的使用和应用，包括：

n  DBMS_DDL;

n  DBMS_JOB

n  DBMS_OUTPUT

n  UTL_FILE

n  UTL_HTTP 和 UTL_TCP

 

§9.1  使用DBMS_SQL软件包

DBMS_SQL软件包用于在存储过程中学动态的SQL语句，并且分析DDL语句。包括以下存储过程和函数：

 

函数或过程	描述
OPEN_CURSOR	打开游标并且赋予游标ID号。
PARSE	分析DDL或DML 语句，即检查语句的语法，并且与打开的游标进行关联。 ( 当分析的时候，DDL被立即执行)
BIND_VARIABLE	绑定给定的值和变量，变量在给定游标的分析语句中有它的名字确定。
EXECUTE	执行SQL 语句并且返回处理记录的个数。
FETCH_ROWS	返回指定游标的记录。（为了多行纪录，再LOOP语句中调用）
CLOSE_CURSOR	关闭指定的游标。

 

例1：创建存储过程，用于删除指定表的所有纪录。

 

CREATE OR REPLACE PROCEDURE delete_all_rows

( p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)

IS

Cursor_name INTEGER;

BEGIN

-- Use OPEN_CURSOR to establish an area in the memory

-- to process a SQL statement;

       Cursor_name := DBMS_SQL.OPEN_CURSOR;

-- Use PARSE to establish the validity of the SQL statement;

DBMS_SQL.PARSE

(cursor_name, ‘ DELETE FROM ‘||p_tab_name, DBMS_SQL.NATIVE);

       --Use the EXECUTE function to run the SQL statement.

--This function returns the number of row processed.

p_rows_del := DBMS_SQL.EXECUTE(cursor_name);

--Use CLOSE_CURSOR to close the cursor.

DBMS_SQL.CLOSE_CURSOR(cursor_name);

END;

 

VARIABLE deleted NUMBER

 

EXECUTE delete_all_rows(‘emp’, :deleted)

 

PRINT deleted

 

DROP PROCEDURE delete_all_rows;

 

 

§9.2  使用EXECUTE IMMEDIATE 语句

使用EXECUTE IMMEDIATE 语句执行本地SQL具有更好的性能。

 

语法格式：

       EXECUTE IMMEDIATE dynamic_string

              [ INTO { define_variable [, define_variable] … | record } ]

              USING [ IN | OUT | IN OUT ] bind_argument

                     [, [ IN | OUT | IN OUT ] bind_argument ] … ] ;

其中：

       INTO用于单行记录查询，并且将返回的列值指定到变量或记录；

       USING用于存放所有的绑定参数，缺省的参数模式为IN。

 

例2：创建存储过程，用于删除指定表的所有纪录，并且返回表中记录的个数。

 

CREATE OR REPLACE PROCEDURE del_rows

       (p_table_name IN VARCHAR2, p_rows_del OUT NUMBER)

IS

BEGIN

       EXECUTE IMMEDIATE ‘delete from ‘||p_table_name;

       P_rows_del := SQL%ROWCOUNT;

END;

 

VARIABLE deleted NUMBER

 

EXECUTE del_rows ( ‘emp’, :deleted)

 

PRINT deleted

 

DROP PROCEDURE del_rows;

 

 

§9.3  使用DBMS_DDL软件包

DBMS_DDL 软件包提供从存储过程中访问某些SQL DDL 语句的方法。包括一些存储过程和函数：

 

存储过程1：

PROCEDURE ALTER_COMPILE (

type VARCHAR2,

schema VARCHAR2,

name VARCHAR2);

其中：

       type 必须为如下之一："PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE BODY" or "TRIGGER".

       Schema: 模式名，大下写敏感；

       Name: 对象名称, 大下写敏感。

 

相当于SQL语句：

 ALTER PROCEDURE | FUNCTION | PACKAGE

[.] COMPILE [BODY]".

 

 

存储过程2：

       PROCEDURE ANALYZE_OBJECT(

type VARCHAR2,

schema VARCHAR2,

name VARCHAR2,

method VARCHAR2,

estimate_rows number default null,

estimate_percent number default null,

method_opt varchar2 default null,

                 partname varchar2 default null);

 

其中：

type：One of 'TABLE', 'CLUSTER' or 'INDEX'. 

Schema：schema of object to analyze.  NULL means current schema. Case sensitive.

Name：name of object to analyze.  Case sensitive.

Method：One of 'ESTIMATE', 'COMPUTE' or 'DELETE'. If 'ESTIMATE' then either estimate_rows or estimate_percent must be non-zero.

estimate_rows：Number of rows to estimate

estimate_percent：Percentage of rows to estimate.  If estimate_rows is specified than ignore this parameter.

method_opt：method options of the following format

  --      [ FOR TABLE ]

  --      [ FOR ALL [INDEXED] COLUMNS] [SIZE n]

  --      [ FOR ALL INDEXES ]

partname：specific partition to be analyzed.

 

相当于SQL语句：

ANALYZE TABLE | CLUSTER | INDEX [.]

[] STATISTICS [SAMPLE [ROWS|PERCENT]]

 

 

§9.4  使用DBMS_JOB软件包编制工作调度

软件包DBMS_JOB用于编制运行的PL/SQL程序计划。包括：

l  提交作业

l  执行作业

l  修改作业的执行参数

l  删除作业

l  暂停作业

 

 

l  提交作业DBMS_JOB.SUBMIT

使用DBMS_JOB.SUBMIT放置被执行的作业在作业队列中。

 

  PROCEDURE submit   ( job OUT BINARY_INTEGER,

                        what IN VARCHAR2,

                        next_date IN DATE DEFAULT sysdate,

                        interval IN VARCHAR2 DEFAULT 'null',

                        no_parse IN BOOLEAN DEFAULT FALSE,

                     instance IN BINARY_INTEGER DEFAULT 0,

                     force IN BOOLEAN DEFAULT FALSE );

 

  -- Submit a new job.  Chooses JOB from the sequence sys.jobseq.

  -- instance and force are added for job queue affinity

  -- If FORCE is TRUE, then any positive integer is acceptable as the job instance。

  -- If FORCE is FALSE, then the specified instance must be running;

  -- otherwise the routine raises an exception.

  -- If INTERVAL evaluates to null and a job completes successfully, then

  -- the job is automatically deleted from the queue.

 

例1：

       VARIABLE jobno NUMBER

      

       BEGIN

              DBMS_JOB.SUBMIT(

                     Job => :jobno,

                     What => ‘INSERT INTO SCOTT.DEPT VALUES (99, ‘’education’’, ‘‘beijing’’);’,

                     Next_date => SYSDATE,

                     Interval => ‘TRUNC(SYSDATE+1)’

              );

              COMMIT;

       END;

 

       PRINT jobno

 

      COLlog_user FORMAT A12

      COLnext_sec FORMAT A12

      COLwhat FORMAT A60

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

 

 

l  修改作业特性

 

  PROCEDURE change ( job IN BINARY_INTEGER,

                       what IN VARCHAR2,

                       next_date IN DATE,

                       interval IN VARCHAR2,

                                      instance IN BINARY_INTEGER DEFAULT NULL,

                                      force IN BOOLEAN DEFAULT FALSE);

  -- Change any of the the user-settable fields in a job

  -- Parameter instance and force are added for job queue affinity

  -- If what, next_date,or interval is null, leave that value as-is.

  -- instance defaults to NULL indicates instance affinity is not changed.

  -- If FORCE is FALSE, the specified instance (to which the instance number

  -- change) must be running. Otherwise the routine raises an exception.

  -- If FORCE is TRUE, any positive integer is acceptable as the job instance.

  --   execute dbms_job.change( 14144, null, null, 'sysdate+3');

 

  PROCEDURE what (job IN BINARY_INTEGER,

                    what IN VARCHAR2 );

  -- Change what an existing job does, and replace its environment

 

  PROCEDURE next_date ( job IN BINARY_INTEGER,

                        next_date IN DATE);

  -- Change when an existing job will next execute

 

  PROCEDURE instance (job IN BINARY_INTEGER,

                       instance IN BINARY_INTEGER,

                                 force IN BOOLEAN DEFAULT FALSE);

  -- Change job instance affinity. FORCE parameter works same as in SUBMIT

 

  PROCEDURE interval (job IN BINARY_INTEGER,

                       interval IN VARCHAR2 );

  -- Change how often a job executes

 

 

l  运行、删除和终止作业

 

  PROCEDURE run (job IN BINARY_INTEGER,

                               force IN BOOLEAN DEFAULT FALSE);

  -- Run job JOB now.  Run it even if it is broken.

  -- Running the job will recompute next_date, see view user_jobs.

  -- Warning: this will reinitialize the current session's packages

  -- FORCE is added for job queue affinity

  -- If FORCE is TRUE, instance affinity is irrelevant for running jobs in

  -- the foreground process. If FORCE is FALSE, the job can be run in the

  -- foreground only in the specified instance. dbms_job.run will raise an

  -- exception if FORCE is FALSE and the connected instance is the wrong one.

 

例2：

DELETE FROM dept WHERE deptno=99;

COMMIT;

 

EXECUTE DBMS_JOB.RUN(:jobno)

 

  PROCEDURE broken (job IN BINARY_INTEGER,

                      broken IN BOOLEAN,

                      next_date IN DATE DEFAULT SYSDATE );

  -- Set the broken flag.  Broken jobs are never run.

 

  PROCEDURE remove ( job IN BINARY_INTEGER );

  -- Remove an existing job from the job queue.

  -- This currently does not stop a running job.

 

例3：

       EXECUTE DBMS_JOB.REMOVE(:jobno)

 

§9.5  使用DBMS_OUTPUT软件包

使用DBMS_OUTPUT可以从PL/SQL块中输出信息。

 

 

Function or Procedure	Description
PUT	Appends text from the procedure to the current line of line output buffer.
NEW_LINE	Places an end_of_line marker in the output buffer.
PUT_LINE	Combines the action of PUT and NEW_LINE.
GET_LINE	Retrieves the current line from the output buffer into the procedure.
GET_LINES	Retrieves an array of lines from the output buffer into the procedure.
ENABLE/DISABLE	Enable or disable calls to the DBMS_OUTPUT procedures.

 

 

§9.6  使用UTL_FILE软件包

UTL_FILE软件包提供了在PL/SQL块文本文件I/O能力，类似操作系统I/O。