PL/SQL笔记2

存储代码
(1) 从数据字典收集存储代码信息
select object_type, object_name, status
from user_objects
where object_type in
    ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE_BODY')
order by object_type;   

OBJECT_TYPE         OBJECT_NAME                                                                      STATUS
------------------- -------------------------------------------------------------------------------- -------
FUNCTION            NEW_INSTRUCTOR_ID                                                                VALID
FUNCTION            ID_IS_GOOD                                                                       VALID
FUNCTION            SHOW_DESCRIPTION                                                                 VALID
PACKAGE             INSTRUCTOR_ADM                                                                   VALID
PACKAGE             MANAGE_STUDENTS                                                                  VALID
PROCEDURE           DISCOUNT                                                                         VALID
PROCEDURE           P_ADD_PARTITION                                                                  VALID
PROCEDURE           FIND_NAME

create or replace function scode_at_line
    (i_name_in in varchar2,
     i_line_in in integer := 1,
     i_type_in in varchar2 := null)
return varchar
is
    cursor scode_cur is
      select text
        from user_source
       where name = upper(i_name_in)
         and (type = upper(i_type_in)
         or i_type_in is null)
         and line = i_line_in;
    scode_rec scode_cur%ROWTYPE;
begin
    open scode_cur;
    fetch scode_cur into scode_rec;
    if scode_cur%NOTFOUND
    then
        close scode_cur;
        return null;
    else
        close scode_cur;
        return scode_rec.text;
    end if;
end;   

注：show errors可以查看编译的错误信息

SQL> desc user_errors;
Name           Type           Nullable Default Comments                                                                                                                         
-------------- -------------- -------- ------- ---------------------------------------------------------------------------------------------------------------------------------
NAME           VARCHAR2(30)                    Name of the object                                                                                                               
TYPE           VARCHAR2(12)   Y                Type: "TYPE", "TYPE BODY", "VIEW", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY", "TRIGGER",
"JAVA SOURCE" or "JAVA CLASS"
SEQUENCE       NUMBER                          Sequence number used for ordering purposes                                                                                       
LINE           NUMBER                          Line number at which this error occurs                                                                                           
POSITION       NUMBER                          Position in the line at which this error occurs                                                                                  
TEXT           VARCHAR2(4000)                  Text of the error                                                                                                                
ATTRIBUTE      VARCHAR2(9)    Y                                                                                                                                                 
MESSAGE_NUMBER NUMBER         Y

user_errors视图会存储用户的存储对象的当前错误。该文本文件包含错误的文本，使用这个函数，有助于
决定编译错误的细节错误信息
查看指定函数或存储过程的编译错误：
select line || '/' || position "LINE/COL",text "ERROR" from user_errors
where name = 'function_name or procedure_name'

user_dependencies视图有利于分析表变更，或者其他存储过程变革所带来的影响

SQL> desc user_dependencies;
Name                 Type          Nullable Default Comments                                                  
-------------------- ------------- -------- ------- ----------------------------------------------------------
NAME                 VARCHAR2(30)                   Name of the object                                        
TYPE                 VARCHAR2(18)  Y                Type of the object                                        
REFERENCED_OWNER     VARCHAR2(30)  Y                Owner of referenced object (remote owner if remote object)
REFERENCED_NAME      VARCHAR2(64)  Y                Name of referenced object                                 
REFERENCED_TYPE      VARCHAR2(18)  Y                Type of referenced object                                 
REFERENCED_LINK_NAME VARCHAR2(128) Y                Name of dblink if this is a remote object                 
SCHEMAID             NUMBER        Y                                                                          
DEPENDENCY_TYPE      VARCHAR2(4)   Y

DEPTREE
Oracle提供了一个名为DEPTREE的实用程序，可以显示指定对象，以及依赖于这个对象的其他对象
这个程序包含三部分：
script: $ORACLE_HOME/rdbms/admin/utldtree.sql
procedure: deptree_fill(type,schema,object_name)
view: ideptree

步骤一：执行脚本
sqlplus jiang/huawei@noas < $ORACLE_HOME/rdbms/admin/utldtree.sql

步骤二：通过执行存储过程，填充的表
SQL> exec deptree_fill('TABLE',USER,'COURSE')

步骤三：在视图ideptree中查看deptree信息
SQL> select * from ideptree;
 
DEPENDENCIES
--------------------------------------------------------------------------------
   PROCEDURE JIANG.DISCOUNT
   FUNCTION JIANG.SHOW_DESCRIPTION
TABLE JIANG.COURSE

Oracle中对象类型
对象类型通常包括两部分：属性和方法
对象类型的创建过程包含两部分：对象类型规范和对象类型体
对象类型规范包含属性声明和作用于该对象的方法

创建对象类型规范
create or replace type zipcode_obj_type as object
(zip varchar2(5),
 city varchar2(25),
 state varchar2(2),
 created_by varchar2(30),
 created_date date,
 modified_by varchar2(30),
 modified_date date
 )
 /
 
使用：
declare
    v_zip_obj zipcode_obj_type;
begin    
    select zipcode_obj_type(zip,city,state,null,null,null,null)
        into v_zip_obj
        from zipcode
      where zip = '06883';
    dbms_output.put_line('Zip: ' ||  v_zip_obj.zip);
    dbms_output.put_line('City: ' ||  v_zip_obj.city);
    dbms_output.put_line('State: ' ||  v_zip_obj.state);
end;
/

引用未初始化对象实例的单个属性会导致错误
注：只有当被引用的对象类型属性是非数值，或者没有限制精度的数值类型，就会产生这个错误
oracle@NOAS252:~> oerr ora 06530
06530, 00000, "Reference to uninitialized composite"
// *Cause:  An object, LOB, or other composite was referenced as a
//          left hand side without having been initialized.
// *Action: Initialize the composite with an appropriate constructor
//          or whole-object assignment.
//

对象类型和集合类型相互嵌套的情况：
declare
    type v_zip_type is table of zipcode_obj_type
        index by binary_integer;
    v_zip_tab v_zip_type;
begin
    select zipcode_obj_type(zip,city,state,null,null,null,null)
      bulk collect into v_zip_tab
      from zipcode
      where rownum < 5;
     
    for i in 1.. v_zip_tab.count
    loop
        dbms_output.put_line('Zip: '||v_zip_tab(i).zip);
        dbms_output.put_line('City: '||v_zip_tab(i).city);
        dbms_output.put_line('State: '||v_zip_tab(i).state);
        dbms_output.put_line('------------------------');
    end loop;   
end;
   
   
   

Oracle提供的包
1. 使用Oracle提供的包来配置PL/SQL，访问文件和调度作业
(1) 使用DBMS_HPROF配置PL/SQL
为了使用DBMS_HPROF，DBA必须把该包的执行权限赋予合适的用户，并且在服务器上提供一个用于写入信息的目录。一般
会使用一个文本文件
conn sys/oracle@ora11g
grant execute on dbms_hprof to public;
create or replace directory profiler_dir as '/home/oracle/temp';
grant read,write on directory profiler_dir to public;

为分析执行结果，用户需要安装相关的数据库表
/opt/oracle/product/11gR2/db/rdbms/admin/dbmshptab.sql
oracle@NOAS252:/opt/oracle/product/11gR2/db/rdbms/admin> sqlplusjiang/huawei@noas < ./dbmshptab.sql

drop table dbmshp_runs                     cascade constraints;
drop table dbmshp_function_info            cascade constraints;
drop table dbmshp_parent_child_info        cascade constraints;

drop sequence dbmshp_runnumber;

create table dbmshp_runs
(
  runid               number primary key,  -- unique run identifier,
  run_timestamp       timestamp,
  total_elapsed_time  integer,
  run_comment         varchar2(2047)    -- user provided comment for this run
);

comment on table dbmshp_runs is
        'Run-specific information for the hierarchical profiler';

create table dbmshp_function_info
(
  runid                  number references dbmshp_runs on delete cascade,
  symbolid               number,               -- unique internally generated
                                               -- symbol id for a run
  owner                  varchar2(32),         -- user who started run
  module                 varchar2(32),         -- module name
  type                   varchar2(32),         -- module type
  function               varchar2(4000),       -- function name
  line#                  number,               -- line number where function
                                               -- defined in the module.
  hash                   raw(32) DEFAULT NULL, -- hash code of the method.
  -- name space/language info (such as PL/SQL, SQL)
  namespace              varchar2(32) DEFAULT NULL,
  -- total elapsed time in this symbol (including descendats)
  subtree_elapsed_time   integer DEFAULT NULL,
  -- self elapsed time in this symbol (not including descendants)
  function_elapsed_time  integer DEFAULT NULL,
  -- number of total calls to this symbol
  calls                  integer DEFAULT NULL,
  -- 
  primary key (runid, symbolid)
);

comment on table dbmshp_function_info is
        'Information about each function in a run';

create table dbmshp_parent_child_info
(
  runid                  number,       -- unique (generated) run identifier
  parentsymid            number,       -- unique parent symbol id for a run
  childsymid             number,       -- unique child symbol id for a run
  -- total elapsed time in this symbol (including descendats)
  subtree_elapsed_time   integer DEFAULT NULL,
  -- self elapsed time in this symbol (not including descendants)
  function_elapsed_time  integer DEFAULT NULL,
  -- number of calls from the parent
  calls                  integer DEFAULT NULL,
  --
  foreign key (runid, childsymid)
    references dbmshp_function_info(runid, symbolid) on delete cascade,
  foreign key (runid, parentsymid)
    references dbmshp_function_info(runid, symbolid) on delete cascade
);

comment on table dbmshp_parent_child_info is
        'Parent-child information from a profiler runs';

create sequence dbmshp_runnumber start with 1 nocache;

 

下面的过程不会处理任何值，仅仅相互调用，用于显示使用Profiler简单范例：
步骤一：
create or replace procedure count_student
(p_zip in number)
as
    v_count number;
begin
    select count(*)
    into v_count
    from student
    where zip = p_zip;
end;
/

create or replace procedure count_instructor
(p_zip in number)
as
    v_count number;
begin
    select count(*)
    into v_count
    from instructor
    where zip = p_zip;
end;
/

create or replace procedure loop_zipcode
as
begin
    for r in (select * from zipcode)
    loop
        count_student(r.zip);
        count_instructor(r.zip);
    end loop;
end;
/

步骤二：使用START_PROFILING过程，启动Hierarchical Profiler;
begin
    DBMS_HPROF.start_profiling (
      localtion => 'PROFILER_DIR',
      filename => 'profiler.txt');
     
    loop_zipcode;
    DBMS_HPROF.stop_profiling;
end;
   
   
   
   
  
      

      
分区表(list方式)
create table FCT_AREA_RES_ANA
(
   DIM1GRAN_ID          number(12),
   DIM1GRAN_NAME        varchar2(90),
   DIM1TYPE_ID          number(12),
   DIM2GRAN_ID          number(12),
   DIM2TYPE_ID          number(12),
   FILIALE_ID           number(12),
   FILIALE_NAME         varchar2(90),
   OFFICE_ID            number(12),
   OFFICE_NAME          varchar2(90),
   CELL_HOME_COUNTY_ID  number(12),
   CELL_HOME_COUNTY_NAME varchar2(90),
   AREA_TYPE_ID         number(12),
   AREA_TYPE_NAME       varchar2(90),
   CELL_NUM             number(16),
   CELL_NUM_SAME        number(16),
   CARR_WAVE_CNT        number(16),
   CARR_WAVE_CNT_SAME   number(16),
   TCH_CNT              number(16),
   TCH_CNT_SAME         number(16),
   PDCH_OCCU_CNT        number(16),
   PDCH_OCCU_CNT_SAME   number(16),
   TCH_AVG_TRAF         number(22,4),
   TCH_AVG_TRAF_SAME    number(22,4),
   PDCH_AVG_FLUX        number(22,4),
   PDCH_AVG_FLUX_SAME   number(22,4)
)
partition by list (dim2gran_id)
(
partition pd20120701 values (20120701) tablespace tbs_ni_iass,
partition pd20120702 values (20120702) tablespace tbs_ni_iass,
partition pd20120703 values (20120703) tablespace tbs_ni_iass,
partition pd20120704 values (20120704) tablespace tbs_ni_iass,
partition pd20120705 values (20120705) tablespace tbs_ni_iass,
partition pd20120706 values (20120706) tablespace tbs_ni_iass,
......
partition pdother values (default) tablespace tbs_ni_iass
);

comment on table FCT_AREA_RES_ANA is
'区域资源情况分析';
comment on column FCT_AREA_RES_ANA.DIM1GRAN_ID is
'空间ID';

先按照range分区再按照list分区

create table FCT_FOURNET_TMN_FLUX_CKM
(
  prov_id                     NUMBER(12),
  prov_name                   VARCHAR2(32),
  city_id                     NUMBER(12),
  city_cd                     VARCHAR2(10),
  city_name                   VARCHAR2(32),
  county_id                   NUMBER(12),
  county_name                 VARCHAR2(64),
  year_id                     NUMBER(12),
  month_id                    NUMBER(12),
  day_id                      NUMBER(12)
)
partition by range (MONTH_ID)
subpartition by list (CITY_CD)
(
  partition PM7 values less than (201208)
    tablespace TBS_NI_IASS
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PM7CZ values ('CZ') tablespace TBS_NI_IASS,
    subpartition PM7DG values ('DG') tablespace TBS_NI_IASS,
    subpartition PM7FS values ('FS') tablespace TBS_NI_IASS,
    subpartition PM7OTHER values (default) tablespace TBS_NI_IASS
  ),
  partition PM8 values less than (201209)
    tablespace TBS_NI_IASS
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PM8CZ values ('CZ') tablespace TBS_NI_IASS,
    subpartition PM8DG values ('DG') tablespace TBS_NI_IASS,
    subpartition PM8FS values ('FS') tablespace TBS_NI_IASS,
    subpartition PM8OTHER values (default) tablespace TBS_NI_IASS
  ),
  partition PM9 values less than (201210)
    tablespace TBS_NI_IASS
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition PM9CZ values ('CZ') tablespace TBS_NI_IASS,
    subpartition PM9DG values ('DG') tablespace TBS_NI_IASS,
    subpartition PM9FS values ('FS') tablespace TBS_NI_IASS,
    subpartition PM9GZ values ('GZ') tablespace TBS_NI_IASS,
    subpartition PM9OTHER values (default) tablespace TBS_NI_IASS
  )
);

range分区表
create table FCT_FOURNET_2GCELL_EQUP_ALL_M
(
  prov_id                  NUMBER(12),
  prov_name                VARCHAR2(90),
  city_id                  NUMBER(12),
  city_name                VARCHAR2(90),
  county_id                NUMBER(12),
  county_name              VARCHAR2(90),
  spec_area_type_id        NUMBER(12),
  spec_area_type_name      VARCHAR2(90),
  cell_id                  NUMBER(12),
  cell_name                VARCHAR2(90),
  year_id                  NUMBER(12),
  month_id                 NUMBER(12),
  wlan_tmn_rate            NUMBER(6,2)
)
partition by range (MONTH_ID)
(
  partition PM07 values less than (201208)
    tablespace TBS_NI_IASS,
  partition PM08 values less than (201209)
    tablespace TBS_NI_IASS,
  partition PM09 values less than (201210)
    tablespace TBS_NI_IASS,
  partition PM10 values less than (201211)
    tablespace TBS_NI_IASS
);

SQL> select decode(2,1,0,2,4) from dual;
DECODE(2,1,0,2,4)
-----------------
                4
               
SQL> select decode(1,1,9,2,4) from dual;
 
DECODE(1,1,9,2,4)
-----------------
                9              

控制结构：
if 条件1
then

elsif 条件2

else

end if;

loop

end loop;

for counter in [reverse] 开始值..结束值
loop

end loop;

while "条件"
loop

end loop;

退出循环：
无条件退出：
loop
...
exit;
end loop;

有条件退出：
loop
...
exit when y > 3;
end loop;

循环继续：
无条件继续：
loop
...
continue;
end loop;

有条件继续：
loop
...
continue when y > 3;
end loop;

GOTO语句：
declare
...
begin
    <<1b2>>
    ...
    if V1 > 20
    then
        goto <<1b2>>;
    end if;
    ...
end;

   
异常处理：
declare
...
begin
...
exception
    when 异常1 then
    ...;
    when 异常2 then
    ...;
    when others then
    ...;
end;   
       
捕获异常：
declare
...
begin
...
exception
    when 异常1 then
    ...;
    when 异常2 then
    ...;
    when others then
        dbms_output.put_line('Error code:' || SQLCODE || '  ' || SQLERRM);
end;

自定义异常：
1. 格式
"异常的名字" exception;
2. 给异常定义了错误编号
pragma exception_init(exception_name, -Oracle_error_number);
其中，exception_name是异常的名称
Oracle_error_number是错误的编号 范围-20999~-20000
3. 异常触发的方式
(1) 由Oracle自动触发异常
(2) 使用RAISE语句手工触发
(3) 调用存储过程RAISE_APPLICATION_ERROR手工触发

4.实例
使用RAISE触发
declare
    myecp exception; --定义异常
begin
    ...
    raise myecp; --触发异常 raise也可以触发预定义异常
    ...
exception
    when myecp then --对异常进行处理
    dbms_output.put_line('myecp error...');
end;

使用raise_application_error触发
declare
    myecp2 exception;
    pragma exception_init(myecp2, -20009);
begin
    ...
    raise_application_init(-20009, 'data is out of list');
    ...
exception
    when myecp2 then
    dbms_output.put_line('Error code:' || SQLCODE || '  ' || SQLERRM);
end;   

 

游标变量：
游标变量其实就是指针，只是这个指针可以指向不同的查询工作区。
而普通游标(显示游标)总是指向相同的查询工作区。
游标变量称为动态游标，显示游标称为动态游标
1. 定义游标变量
两个过程：
(1) 定义CURSOR类型的指针
TYPE ref_type_name is ref cursor[return return_type]
如果有返回类型，我们称之为强 REF CURSOR;
如果没有返回类型，我们称之为弱 REF CURSOR;
实例1：定义强 REF CURSOR
type strong_type is ref cursor return employees%ROWTYPE;

实例2：定义弱 REF CURSOR
type weak_type is ref cursor;

(2) 声明游标变量
strong_type v2;

2. 游标变量的使用
create or replace procedure p_cursor(choice int)
as
  type newcursor_type is ref cursor; --定义游标变量之前，要定义一种新数据类型
  v_cursor newcursor_type; --根据新数据类型，定义游标变量
  v_rec employee%ROWTYPE; --定义行类型
begin
  if choice = 1
  then
    open v_cursor
    for
      select * from employee;
  elsif choice = 2
  then
    open v_cursor
    for
      select * from grade;
  end if;
 
  loop 
    fetch v_cursor into v_rec;
    exit when v_cursor%NOTFOUND;
    dbms_output.put_line(v_rec.NAME);
   
  end loop;
 
  close v_cursor;
end p_cursor;

 

触发器:
使用PL/SQL，java和C语言编写的程序单元
能够点燃触发器的事件有：DML操作(insert,update,delete)，DDL操作，系统事件(数据库启动和关闭)，用户事件(用户登录).
1. 触发器组成
(1) 触发事件或语句
(2) 触发限制
(3) 触发动作

create trigger tri_ftab
before insert or update of name,address on ftab  --触发事件
for each row

when (new.address <> 'China') --触发限制
begin
...   ----触发动作

end;