PL/SQL高级特性详解-优快云博客

本文链接：https://blog.youkuaiyun.com/zhangbill2000/article/details/1689807

游标cursor,索引index.过程(procedure),函数(function),包(package)和触发器(trigger).Job.Queue.view.sequence.role.tablespace
对于过程和函数的使用原则：如果是返回多个值，一般使用过程。如果返回一个值，一般使用函数
1.pl/sql异常部分:异常类型,用于自定义异常和预定义异常。
对于自定义异常,是在pl/sql语句块的申明部分进行申明的.
DECLARE 
   e_TooManyStudents EXCEPTION;
使用raise 可以显示抛出自定义异常
2.处理异常,语法:
  EXCEPTION
    WHEN exception_name then
    ...
    WHEN OTHERS THEN(类似switch中的default,处理前面未捕捉的异常)
    END;
3.不同的异常之间可以用or
 简单隔开(不管该异常是用户自定义异常还是预定义异常),但一个异常只能被一个分支获取
比如  when ex1 or ex2 then
一般最好在others语句中记录 是什么异常导致的。所以可以用SQLCODE和SQLERRM
其中SQLCODE返回当前错误代码,SQLERRM返回当前错误的消息文本，例如:
WHEN OTHERS THEN
 v_ErrorCode:=SQLCODE;
 v_ErrorText:SUBSTR(SQLERRM,1,200);
 4.异常的传播:
 可执行部分产生的异常：跟java中的类似,现在本地查找,没有的话就往上层抛

 申明部分出现的异常:即使当前语句中有异常处理部分,也不执行,直接跑到外层的异常处理.如下例中如果v_number number(3)处异常.那直接
 打印outer
 begin
 declare 
   v_number number(3):='abc';
   begin
   exception 
    when others then
    dbms_output.put_line("inner");    
   end;   
 exception
    when others then
      dbms_output.put_line("outer");
 end;
 异常部分产生的异常：
可通过raise语句显示产生,也可以通过运行时错误而隐含产生。不论哪种情况,一律立即被传播到外层语句块；
比如下例会执行到2222,但不会执行3333，并提示 用户定义的异常 未得到处理
 declare
  A exception;
  B exception;
  v_age number;
  begin 
   raise A;
   v_age:=10;
 exception
    when A then
      dbms_output.put_line(22222);
      raise B;
    when B then
      dbms_output.put_line(33333);
 end;
 
 5.异常的作用域,不能传到外围
  避免未处理异常
  制定错误位置
  

6.跟pl/sql有关的几个东东:
CREATE OR　REPLACE PROCEDURE procedure_name [parameter_list] AS
/*Declarative section is here*/
BEGIN
/*Executable section is here*/
EXCEPTION
/*EXCEPTION section is here*/
END [procedure_name];
其中parameter_list中的形式为
argument[{IN|OUT|IN OUT}] type

对于in参数，传入，只读
对于out,传入时候参数变为null.最后复制回实参
对于in out.传入实参值，最后复制回实参
例子如下：
create or replace procedure proc1(a in number,b out number,c in out
 number)
is
begin
-- a:=1;　这句不符合．不能赋值
 b:=2;　　
 c:=3;
end proc1;

调用这个过程：
declare 
 c number;
 d number;
 e number;
begin
 c:=10;
 d:=100;
 e:=1000;
 proc1(c,d,e);
 dbms_output.put_line(c);
 dbms_output.put_line(d);
 dbms_output.put_line(e); 
 

 在调用前,c,d,e分别是10,100,1000.调用之后在存储过程中是ｃ是只读，ｄ变为ｎｕｌｌ．ｅ可设置，并且有值
 在回调后，ｃ，ｄ，ｅ分别是10,2,3
 
７．包：
包是pl/sql中允许相关对象共同存储的构造，包有两个部分，说明和包体．可以放　过程，函数，游标，类型和变量，可以作为全局变量引用
调用的时候只需引用　包名．过程(或者函数)
语法：
说明部分：
create or replace package pa as
 Function f(p in char);
end pa;
包体部分：
create or replace pachage body pa as

end pa;

对包可以初始化：语法：(pa是包名)
create or replace package body pa is
...
BEGIN
initialization_code;--此处是初始化代码
END　pa;


８.游标:
处理显式游标：
１)申明游标；
２)为查询打开游标；
３)取得结果放入pl/sql变量中；
４)关闭游标;
其中游标申明是唯一可以出现在模块申明部分的步骤，其他三部分都在执行部分和异常处理部分
典型的例子：
DECLARE
  v_studentID students.id%TYPE;
  v_FirstName students.first_name%TYPE;
  v_LastName  students.last_name%TYPE;
  v_major student.major%type:='computer science';
  CURSOR c_students IS
    select id,first_name,last_name
    from students
    where major=v_major;
BEGIN
  OPEN c_students;
  LOOP
    FETCH c_students INTO v_studentID,v_FirstName,v_LastName;
    EXIT WHEN c_students%NOTFOUND;
    END LOOP;  
  CLOSE c_students;
END;

其中游标属性４种,分别是%FOUND,%NOTFOUND,%ISOPEN,%ROWCOUNT
 以及%BULK_ROWCOUNT
５)参数化游标
CURSOR c_Classes(p_Department classes.department%TYPE,p_Course
 classes.course%TYPE) IS..．
然后在open的时候可以直接把值传给游标：OPEN c_Classes('His',101);
6)处理隐式游标
显式游标被用于处理返回多行数据的SELECT语句．但所有SQL语句都在一个上下文执行,因而要有一个游标指向该上下文区．这个游标
即是SQL游标．与显式游标不同的是,SQL游标不是由程序打开或者关闭的．ｐｌ／ｓｑｌ隐含地打开SQL游标，处理ＳＱＬ语句，然后
关掉游标．
隐式游标被用于处理insert,update,delete和select...into语句．因为sql游标由pl/sql引擎打开和关闭，所以没有相应的
open,fetch和close语句

游标循坏可用loop,while,for等
但是loop.while两种循环需要用open,fetch,close语句来显式处理游标；for可以隐含地处理游标
for语法如下：
　FOR v_student IN c_HistoryData LOOP
  ...
  END LOOP;
可以更简单一些．就是直接把sql放入c_HistoryData.
比如
　FOR v_student IN (select * from students) LOOP
...
END LOOP';

7)select ...for update　[OF column_reference][nowait][wait n]
其中OF column_reference 代表锁住哪行
nowait　碰到资源锁住.open立即返回oracle错误(ora-54)
wait n  n秒内数据行未解锁.返回oracle错误(ora-54)


8)游标变量
游标变量能在运行时与不同的查询语句建立关联．静态游标类似于pl/sql常数，因为他们只与运行时查询关联
游标变量最经常用于向客户端程序返回变量的存储过程中
TYPE t_StudentRef IS REF CURSOR
RETURN student%ROWTYPE;
自定义类型：
TYPE t_NameRecord IS RECORD{
 first_name students.first_name%TYPE;
 last_name students.last_name%TYPE;
};
v_NameRecord t_NameRecord;
TYPE t_NamesRef IS REF CURSOR
RETURN t_NameRecord;

v_NameCV t_NamesRef; 定义cursor变量使用上述类型

为查询打开游标变量：
OPEN cursor_variable FOR select_statement;
举例如下：
DECLARE 
 TYPE t_ClassesRef IS REF CURSOR RETURN classes%ROWTYPE;
 v_ClassesCV t_ClassesRef;
Open v_ClassesCV FOR SELECT  *  FROM classes;
游标是一个强大的功能，可以极大地简化处理过程．因为他们允许在同一个变量中返回不同类型的数据．


９)触发器
触发器必须在数据库中存储为独立的对象,而且对于语句块或包不可以是局部的．
只要触发事件发生，触发器便隐式的执行，而且触发器不接受参数．执行触发器的行为叫　激发触发器。
触发器可以做很多事情：
维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束
通过记录已进行的改变以及是谁进行了该项改变来检查一个表中的信息
当一个表发生改变时，自动向其他程序发送需要采取行动的信号
在一个发布－－预订环境中发布关于各种事件的信息
主要的触发器种类有三种：
DML,instead-of和系统触发器
比如
create table major_stats(
 major varchar2(30),
 total_credits number,
 total_students number);
为了保持major_stats是最新的,我们可以建立students上的触发器．该触发器在每次修改了students之后都会更新major_stats.接下来
显示的UpdateMajorStats触发器会完成这项任务．在对students进行了DML操作后，该触发器就会执行．触发器体查询students.
create or replace trigger UpdateMajorStats
 after insert or delete or update on students
DECLARE
 CURSOR c_Statistics IS
   SELECT major,Count(*) total_students,SUM(current_credits)
 total_credits 
   FROM students
   GROUP BY major;
BEGIN
  DELETE FROM major_stats;
  FOR v_StatsRecord in c_Statistics LOOP
  INSERT INTO major_stats(major,total_credits,total_students)

  values(v_StatsRecord.major,v_StatsRecord.total_credits,v_StatsRecord.total_students);
  END LOOP;
END UpdateMajorStats;

DML触发器激发的顺序：
触发器在ＤＭＬ语句执行时激发，执行ＤＭＬ语句的算法如下：
１)如果存在，执行语句级别之前的触发器
２)对于受语句影响的每一行：
　Ａ．如果存在，执行行级别之前的触发器
　Ｂ．执行语句本身
　ｃ．如果存在，执行行级别之后的触发器
３)如果存在，执行语句级别之后的触发器





instead-of触发器：
只可以定义为视图(关系型或是对象)的触发器．Instead-of触发器执行时，激发他的ＤＭＬ语句不执行．Instead-of触发器必须是行级别的．

系统触发器：
在诸如数据库启动或关闭之类的系统事件发生　以及　诸如创建表之类的ＤＤＬ操作时被激发


10)索引
create sequence trig_seq
 start with 1
 increment by 1;

2.sqlplus操作:
2.1通过isqlplus执行sql
2.2select * from contacts where id=&tt
3.第九章的表空间：
3.1 一般都需要指定用户在什么表空间下，如下是语法
创建表空间： create tablespace [表空间名] datafile '数据文件路径' SIZE 10M
比如：create tablespace tabs datafile 'c:/oracle/products/10.1.0/oradata/test/tabs.dbf' SIZE 10M
     Alter USER omcr DEFAULT TABLESPACE tabs 表示把tabs表空间作为omcr用户的默认表空间
     然后再切换到omcr用户 connect omcr/dt
     但omcr需要被授权，可以用GRANT UNLIMITED TABLESPACE,DBA TO omcr;
     create table t TABLESPACE tabs
4.第十章的约束：
   4.1)除了主键和外键，还有check约束,语法
   alter table emp add constraint ck_emp_sex CHECK(sex='男' or sex='女') 代表sex字段只能是男或者女
   避免在前端应用程序写校验了
   察看约束：
   select constraint name,constraint_type from user_constraint(当前用户的约束) where table name='emp'(此处为表名)
   代表从当前用户中查找emp表的约束，除了user_contraint，还有all_constraint等
4.2)index索引。除了传统索引(普通),还有位图索引，用于字段值比较少,但数据量很大的情况
   比如sex字段其实只有男女有限的值,但数据量很大,就可以用以下的位图索引
   create bitmap index bit_cmp on cmp(sex);
   也可以创建唯一索引
   create unique index myidx on cmp(sex);
5.数据库备份
逻辑备份和恢复:EXP,IMP
物理备份：冷备份(脱机备份)，热备份(联机备份)
冷备份:1)shutdown immediate,然后把oradata下的控制,数据和日志文件copy走
热备份:1)需要把日志改为归档方式,archive mode