Oracle 基础笔记 5

Oracle基础笔记5（PL/SQL 3）

数据包

定义：定义包的规范

create [or replace] package <数据包名>

as

               --公共类型和对象声明

               --子程序说明

end;

定义包的主体

create [or replace] package body <数据包名> as

              -- 公共类型和对象声明

              --子程序主体

begin

              --初始化语句

end;

使用：

eg:

--创建数据包规范：
create or replace packagepack_1as
      nnumber;
      procedurep_1;
      functionf_1return number;
end;

--创建数据包主体：
create or replace package bodypack_1as
       procedurep_1is
                r emp%rowtype;
       begin
                 select*intorfromempwhereempno =7788;
                 dbms_output.put_line(r.empno ||' '||r.ename||' '||r.sal );
        end;
        
        functionf_1return number is
                 r emp%rowtype;
         begin
                  select*intorfromempwhereempno =7788;
                 returnr.sal;
          end;
end;

--使用包:
declare
         nnumber;
 begin
         n:=&请输入员工号;
          pack_1.n:=n;
          pack_1.p_1;
          n:=pack_1.f_1;
          dbms_output.put_line('薪水为 '||n);
end;
        
在包中使用ref游标

eg:

 

--创建数据包规范：
create or replace package pack_2 as
       type c_type is ref cursor;  --建立一个ref游标类型
       procedure p_1(c1 in out c_type); --过程的参数为ref游标类型
end;

--创建数据包主体:
create or replace package body pack_2 as
       procedure p_1(c1 in out c_type) is
       begin
                 open c1 for select * from emp;
        end;
 end;
 
 --使用包
 var c_1 ref cursor;
 set autoprint on;
 execute pack_2.p_1(:c_1);

 

删除包：

drop package <包名>;

触发器

创建触发器：

create [or replace] trigger <触发器名>

before|alter

insert|delete|update [of <列名>] on <表名>

[for each row]

when (<条件>)

   <pl/sql块>

“for each row”指定触发器每行触发一次。

“of <列名>”不写表示整个表的所有列。

特殊变量:

        :new --为一个引用最新的列值；

        :old --为一个引用以前的列值；

这些变量只有使用了关键字“for each row”时才存在。且update语句两个都有，而insert只有:new,delete只有:old;

使用raise_application_error

语法：raise_application_erro(错误号(-20000到-20999),消息[,{true|false}]);

抛出用户自定义错误.

如果参数为’true’,则错误放在先前的堆栈上。

instead of 触发器

   instead of 触发器主要针对视图(view) 将触发的dml语句替换成为触发器中的执行语句，而不执行dml语句。

禁用某个触发器

     alter trigger <触发器名> disable

重新启用触发器

     alter trigger <触发器名> enable

禁用所有触发器

      alter trigger <触发器名> disable all triggers

启用所有触发器

     alter trigger <触发器名> enable all triggers

删除触发器

     drop trigger <触发器名>

自定义对象

create [or replace] type <对象名> as object

(

属性1 类型

属性2 类型

.

.

方法1的规范 (member procedure <过程名>)

方法2的规范 (member function <函数名> return 类型)

.

prama restric_references (<方法名>,wnds/rnds/wnds/rnps);

)

wnds --不能写入数据库状态;

rnds --不能读出数据库状态;

wnps --不能写入包状态;

rnds --不能读出包状态；

创建对象主体

create [or repalce] type body <对象名> as

方法1的规范 (member procedure <过程名> is <PL/SQL块>)

方法2规范 (member function <函数名> return 类型 is <PL/SQL块>)

end;

使用map方法或order方法

用于对自定义类型排序。每个类型只有一个map或order方法。

格式: map member function <函数名> return 类型

order member function <函数名> return number

创建对象表

create table <表名> of <对象类型>

eg:

  --1 创建name类型
  create or replace type name_type as object
  (
         f_name varchar2(20,
         l_name varchar2(20),
         map member function name_map return varchar2
  );
  create or replace type body name_type as
         map member function name_map return varchar2 is ---对f_name和l_name排序
         begin
             return f_name||l_name;
          end;
end;
  --2 创建address类型
  create or replace type address_type as object
  (
         city varchar2(20),
         streat varchar2(20),
         zip number,
         order member function address_order(other address_type) return number;
  );
  create or replace type body address_type as
  order member function address_order(other address_type) return number is --对zip进行排序
        begin
               return self.zip-other.zip;
               end;
  end;
  
 --创建stu对象
 create or replace type stu_type as object
 (
        stu_id number(5),
        stu_name name_type,
        stu_addr address_type,
        age number(3),
        birth date,
        map member function sut_map return number,
        member procedure update age
 );
 create or replace type body stu_type as
        map member function stu_map return number --对stu_id进行排序
        begin
                   return stu_id;
         end;
         member procedure update_age is --求年龄用现在时间-birth
         begin
             update student set age = to_char(sysdate,'yyyy') - to_char(birth,'yyyy') where stu_id = self.stu_id;
         end;
  end;
  --4 创建对象表
  create table student of stu_type(primary key(stu_id));
  --5向对象中表插值
  insert into student values(1,name_type('关','羽'),address_type('武汉','成都路',430000),null,sysdate-365*20)
  --6使用对象的方法
  declare
         aa stu_type;
  begin
         --value()将对象表的每一行转成行对象括号中为表的别名
         select value(s) into aa from student s where stu_id =1;
         aa.update_age();
  end;
  --7 查看类型的值
  select stu_id,s.stu_name.f_name,s.stu_name.l_name from student s;
  -- 8 

  --ref()求出行对像的oid,括号中必须为表的别名; deref()将oid变成行对象；
   
       select ref(s) from student s; 

其他

在PL/SQL中使用DDL

将sql语句赋给一个varchar2变量，再用execute immediate这个varchar2变量即可；

eg:

declare 
     str varchar2(200);
begin
     str := 'create table test(id number,name varchar2(20))'
     execute immediate str;
     --但是要对这个表插入也必须使用execute immediate 字符变量
     str: = 'insert into test values(2,''c'')';
     execute immediate str;
end;

判断表是否存在

eg:

  declare 
       n tab.tname%type;
  begin
       select tname into n from table where tname = '&请输入表名'
       dbms_output.put_line('此表已存在');
   exception
       when no_data_found then
       dbms_output.put_line('还没有此表');\
  end;

查看已有的过程：

eg:

select object_name,object_type,status from user_objects
where object_type ='PROCEDURE'

object_type必须为大写的procedure