PL/SQL环境

PL/SQL环境

SQL*PLUS命令
-----------------------------------------------------------------------------
SQL*Plus command      Description
-----------------------------------------------------------------------------
SAVE filename         以sql扩展名将sql*plus缓存中的数据保存为一个文件
@filename             发送指定文件到oracle编译/执行环境，不需要输入.sql扩展名
SET SERVEROUTPUT ON   设置DBMS_OUTPUT输出到屏幕
/                     将SQL*PLUS缓存的数据发送的oracle编译/执行环境
List(l)               显示缓存区的命令
SHOW ERRORS           显示缓存区的错误信息
SET FEEDBACK OFF      将SQL*PLUS输出信息推迟到执行完毕输出。
Execute               调用执行存储过程
------------------------------------------------------------------------------
默认缓存是20,000字符大小，可以使用DBMS_OUTPUT.Enable（）来增加缓存，最大为1,000,000字符大小。
SQL> execute dbms_output.enable(1000000);

PL/SQL procedure successfully completed.

使用DBMS_OUT包作为标准输出。它有几个为不同数据类型：DATE、NUMBER、和VARCHAR重载的过程。包中PUT_LINE过程的定义是：
procedure put_line (arg VARCHAR2);
procedure put_line (arg NUMBER);
procedure put_line (arg DATE);
如果你使用DBMS_OUTPUT必须在每一个对话执行SQL*PLUS命令一次：
SET SERVEROUTPUT ON
DBMS_OUTPUT缓存输出到一个特定会话DBMS_OUTPUT缓存。每调用PUT_LINE过程，不会立即把文本输出到屏幕上，它仍存放在缓存中。SET SERVEROUTPUT ON命令指示SQL*PLUS会话把缓存文本在程序执行完毕输出到屏幕上。

SET FEEDBACK OFF 推迟到命令执行完毕输出。
SQL> set feedback off
SQL> @hello
No errors.
SQL> execute hello
Hello

SET FEEDBACK ON   执行过程中时实时输出。
SQL> set feedback on
SQL> execute hello
hello

PL/SQL 过程已成功完成。
二、引用Oracle包
(一)、环境创建
数据库管理员编译许多PL/SQL包放于Oracle sys用户下。这是创建数据库的一部分。数据库创建过程也生成公共的同义词和对这些包的公共的授权。当然，一些包是用于oracle内部使用，有严格的访问权限限制。
这些包提供了强壮的应用编程接口(API)。可以使用Oracle API去开发使用java类的方法的PL/SQL存储过程。写操作主机文件、通过SMTP服务发送email、以及许多其他功能。
DBMS_OUTPUT也是API的组件，在数据库创建期间，使DBMS_OUTPUT对普通用户都可用。
1.GRANT EXECUTE ON DBMS_OUTPUT TO PUBLIC;
2.CREATE PUBLIC SYNONYM DBMS_OUTPUT FOR SYS.DBMS_OUTPUT;
第一条语句意味着Scott和未来所有Oracle帐户都可以使用DBMS_OUTPUT包来编写PL/SQL。关键字PUBLIC给所有用户权限。
第二条语句意味着Scott可以用类似下面语句编写PL/SQL：
    DBMS_OUTPUT.PUT_LINE('Hello');
不使用第二条语句，Scott用户将不得不用下面的方式：
    SYS.DBMS_OUTPUT.PUT_LINE('Hello');
       
（二）、API介绍
API多大？它包括成百上千个包，许多包以DBMS开头，一些工具包以UTL开头，比如UTL_SMTP是一个SMTP服务的PL/SQL API
从DBMS和UTL开始预览API中的所有包，查询数据字典视图ALL_OBJECTS。
下面的语句生成一个spool文件，列出了所有DBMS和 UTL包的名称，这段脚本以TERM OFF方式运行，即关闭终端输出，直接输出到spool文件中，ALL_OBJECTS.LST
-- Filename ALL_OBJECTS.SQL
set pagesize 0
set term off
set feedback off
spool all_objects
SELECT  object_name
FROM   all_objects
WHERE  owner='SYS' AND
       object_type='PACKAGE'
AND    (object_name like 'DBMS%' OR object_name like 'UTL%');
set feedback on
set term on
spool off

ALL_OBJECTS.LST文件中有数百个包，
SQL> @ALL_OBJECTS
This shows a few of the hundreds of packages.
DBMS_ALERT
DBMS_APPLICATION_INFO
DBMS_AQ
DBMS_AQADM
DBMS_AQADM_SYS
DBMS_AQADM_SYSCALLS
DBMS_AQIN
DBMS_AQJMS
DBMS_AQ_EXP_HISTORY_TABLES
……

怎么使用API呢？有三种方法学会Oracle包的API：
·使用SQL*PLUS的Describe（desc）命令，这仅是一个接口定义。
·可以从数据字典分析出包的源码。这常常包含示例和使用API的详细描述。
·可以访问ORACLE WEB SITE，technet.oracle.com，查看包说明文档
三、USER_OBJECTS
USER_OBJECTS视图提供了当前用户创建的对象上的状态信息。包括表、序列、视图、存储过程、数据库链接、以及其他数据库对象。

User_objects   提供当前用户创建的所有对象的信息
All_objects    提供当前用户创建的所有对象加上有权限访问的所有对象信息
DBA_OBJECTS    提供数据库中的所有对象信息。需要有Oracle DBA权限或SELECT_CATALOG_ROLE才能DBA视图。

DBA有高权限。SELECT_CATALOG_ROLE 能查询数据字典视图，应用开发员应当被授予该角色。

SQL> desc user_objects
 Name                           Null?    Type
 ------------------------------ -------- ---------------
 OBJECT_NAME                             VARCHAR2(128)
 OBJECT_TYPE                             VARCHAR2(18)
 CREATED                                 DATE
 LAST_DDL_TIME                           DATE
 STATUS                                  VARCHAR2(7)
 And other columns

OBJECT_NAME   对象名
OBJECT_TYPE   对象类型 FUNCTION, PROCEDURE, PACKAGE, or PACKAGE BODY.
CREATED       对象创建时间
LAST_DDL_TIME 最近编译时间
STATUS        对象状态 VALID or INVALID.
 
四、User_dependencies
视图User_dependecies显示了所有对象的依赖关系。包括过程、函数、包、包体等，查询这个视图来看所有PL/SQL过程准备删除的表上的独立性。
SQL> desc user_dependencies
 Name                           Null?    Type
 ------------------------------ -------- ---------------
 NAME                           NOT NULL VARCHAR2(30)
 TYPE                                    VARCHAR2(12)
 REFERENCED_OWNER                        VARCHAR2(30)
 REFERENCED_NAME                         VARCHAR2(64)
 REFERENCED_TYPE                         VARCHAR2(12)
 REFERENCED_LINK_NAME                    VARCHAR2(128)
 SCHEMAID                                NUMBER
 DEPENDENCY_TYPE                         VARCHAR2(4)

如果编写了一个过程：hello，它在students表上插入一行。这个视图中将有一行name=HELLO 和REFERENCED_NAME=STUDENTS.
NAME      对象名
TYPE      对象类型 Procedure, function, package, package body
REFERENCED_OWNER 拥有被引用的对象所属模式名或用户名
REFERENCED_NAME  被引用的对象名
REFERENCED_LINK_NAME 数据库链接名 table_name@link_name
SCHEMAID  模式ID
DEPENDENCY_TYPE  HARD/REF（如果通过一个REF类型对象的依赖）

五、User_source
    过程、函数、包、包体编译后的源码可以从User_source视图中访问。

    ·User_source   当前用户下编译的所有PL/SQL源码
    ·All_source    当前用户及当前用户有权限访问的所有PL/SQL源码。
    ·DBA_SOURCE    数据库中所有的源码。
SQL> desc user_source
 Name                           Null?    Type
 ------------------------------ -------- ---------------
 NAME                                    VARCHAR2(30)
 TYPE                                    VARCHAR2(12)
 LINE                                    NUMBER
 TEXT                                    VARCHAR2(4000)
六、USER_ERRORS

错误信息
SQL> desc user_errors
 Name                           Null?    Type
 ------------------------------ -------- ----------------
 NAME                           NOT NULL VARCHAR2(30)
 TYPE                                    VARCHAR2(12)
 SEQUENCE                       NOT NULL NUMBER
 LINE                           NOT NULL NUMBER
 POSITION                       NOT NULL NUMBER
 TEXT                           NOT NULL VARCHAR2(4000)