oracle学习总结2(pl/sql 游标 异常的处理 存储过程和函数 包 触发器)

pl/sql的学习

给sql添加了逻辑判断与流程控制的功能

语法：

declare

begin

exception

end;

运算符  

  重要的就是赋值运算符了：=        连接  ||      ** 平方

 常用的数据类型

也就是oracle常用的数据类型

constanct常量的声明

引用  constanct nubmer:=1;

pl/sql操作数据

１.不能直接查出来，缺少into子句

　　　需要将查询的数据放到变量当中

２.每次查询只能查询一条数据赋值给变量

　　　实际返回的行数超出请求的行数

复合数据类型

%type

%rowtype

varray

type 类型名  is varray(maxnum_size) of  数据类型

创建数组对象的引用 然后赋值 

v_str  strings:=strings('aa','bb')

table(相当于可变数组)

type 类型名  is table  of 数据类型    index  by binary_integer (数组角标无下限)

创建可变数组对象的引用，然后赋值

record(相当于java中的集合)

type 类型名 is record  (定义不同的类型);

创建集合对象的引用

--pl/sql操作数据
--复合数据类型
--varray
declare
type strings is varray(5) of varchar2(10);--定义一个数组数组元素的长度为5个，每个是10个可变长度
v_str strings:=strings('aa','bb','cc','dd','ee');--创建一个数组对象的引用   从一开始的
begin
  dbms_output.put_line(v_str(1));
  dbms_output.put_line(v_str(2));
  dbms_output.put_line(v_str(3));
  dbms_output.put_line(v_str(4));
  dbms_output.put_line(v_str(5));
  end;
--table  可以理解为java中的可变数组
declare
type strings is table of varchar2(10) index by binary_integer;--定义一个可变数组，数组中每个元素的可变长度为10
--数组角标没有下限
v_str strings;--定义一个可变数组对象的应用
begin
  v_str(1):='aa';
  v_str(999):='bb';
  dbms_output.put_line(v_str(1)||v_str(999));
  end;
--record
declare
--可以定义多个类型
type strings is record(
    v_name tb_student.lastname%type,
    id number,
    r_tb_student tb_student%rowtype
);
v_str strings;
begin
  select id,lastname,sex,age,email,address into v_str.r_tb_student.id,v_str.v_name,v_str.r_tb_student.sex
  ,v_str.r_tb_student.age,v_str.r_tb_student.email,v_str.r_tb_student.address from tb_student where id=2;
  dbms_output.put_line(v_str.v_name);
  end;

流程控制

if

loop循环

for循环

for  变量名   in  1 ..5 loop

end loop;

while循环

while (布尔表达式) loop

end loop;

--流程控制
--分支语句
declare
  v_str varchar2(10):='b';
begin
  if(v_str='a') then
  dbms_output.put_line('if');
  elsif(v_str='b') then
  dbms_output.put_line('elsif');
  else
    dbms_output.put_line('else');
    end if;
  end;
  
declare 
   i constant number:=4;
begin
  if(i=5) then
  null; --如果什么都不做的话，那么补null来保证语句的完整性
  elsif(i=4) then
     dbms_output.put_line(i);
  else
    dbms_output.put_line('else');
    end if;
  end;

--验证如果不给字符变量赋值时默认是空字符串还是为空
declare
  i varchar2(10);
begin
  if(i='') then
  dbms_output.put_line('空字符串');
  elsif(i is null) then
    dbms_output.put_line('null');
  elsif(i='a') then
  dbms_output.put_line('a');
  else
      dbms_output.put_line('b');
    end if;
  end;
  
declare
  i number;
begin
  if(i='') then
  dbms_output.put_line('空字符串');
  elsif(i is null) then
    dbms_output.put_line('null');
  elsif(i='a') then
  dbms_output.put_line('a');
  else
      dbms_output.put_line('b');
    end if;
  end;
  
  
--循环控制
--简单循环loop
declare 
   i number:=0;
begin 
  loop
    i:=i+1;
    dbms_output.put_line('i='||i);
    if(i=30) then
    exit;
    end if;
    end loop;
  end;
declare 
   i number:=0;
begin 
  loop
    i:=i+1;
    dbms_output.put_line('i='||i);
    exit when (i=30); 
    end loop;
  end;
  
--嵌套循环
declare
   i number:=0;
   b number:=0;
begin
   loop
     i:=i+1;
     dbms_output.put_line('i>>>>>>='||i);
     exit when i=5;
     b:=0;
     --嵌套循环
     loop 
       b:=b+1;
       dbms_output.put_line('b='||b);
       exit when b=2;
       end loop;
     end loop;
  end;
declare
   i number:=0;
   b number:=0;
begin
   loop
     i:=i+1;
     dbms_output.put_line('i>>>>>>='||i);
     exit when i=5;
     b:=0;
     --嵌套循环
     loop 
       
       dbms_output.put_line('b='||b);
       exit when b=2;
       b:=b+1;
       end loop;
     end loop;
  end;
declare
   i number:=0;
   b number:=0;
begin
   <<outer>>loop
     i:=i+1;
     dbms_output.put_line('i>>>>>>='||i);
     exit when i=5;
     b:=0;
     --嵌套循环
     <<inner>>loop 
       
       dbms_output.put_line('b='||b);
       exit outer when b=2;
       b:=b+1;
       end loop;
     end loop;
  end;
--for循环
declare 
  a constant number:=4;
  b constant number:=10;
begin 
  for i in a ..b loop
    dbms_output.put_line('i'||i);
    end loop;
  end;
declare 
  a constant number:=1;
  b constant number:=10;
begin 
  for i in a ..b loop
    dbms_output.put_line('i'||i);
    end loop;
  end;
declare 
  a constant number:=1;
  b constant number:=10;
begin 
  for i in reverse a ..b loop
    dbms_output.put_line('i+>>>>>'||i);
    end loop;
  end;
--while循环
declare 
  a constant number:=1;
  b constant number:=10;
  i number:=0;
begin 
  while (b>a) loop
    i:=i+1;
    dbms_output.put_line('i='||i);
    exit when i=5;
    end loop;
  end;

异常处理

异常没有父子继承关系

oracle可根据异常名设置多个异常处理代码    在异常块一次运行过程中，只有一个异常处理器会处理异常

语法：

when   异常名  then

最后一句 条件when other  then

两个常用的异常相关函数

sqlcode返回错误代码        sqlerrm 返回错误代码关联的消息

通过建立错误表来存储错误信息

 

 

游标

什么是游标

游标为内存地址的引用,那段内存存放了多条sql语句执行的结果

 

oracle数据库中执行的每个sql语句都有对应的独立的游标

游标提供了访问select 语句执行结果的途径

   隐式游标

       由开发人员声明和控制    定义一个游标，将tb_clazz中所有的信息提取出来

  显示 游标

cursor  游标名  is   sql语句

   游标流程一般是这样的

定义游标 ——>打开游标——>提取数据 ——>关闭游标

用fetch......into提取数据

 fetch  游标  into 存储的变量的引用

带参数的游标  

cursor   游标名(参数  参数类型）

--异常处理
declare
r_tb_clazz tb_clazz%rowtype;
begin 
  select * into r_tb_clazz from tb_clazz where id=2;
  dbms_output.put_line(r_tb_clazz.code);
  exception
  when no_data_found then
    dbms_output.put_line('没有数据');
    when others then
      dbms_output.put_line('others');
end;
--两个异常函数
--sqlcode   返回错误代码
--sqlerrm    返回错误代码关联的消息
create table tb_error(
id number primary key,
tablename varchar2(20),
sqlcode varchar2(50),
sqlerrm varchar2(200),
currdate date default sysdate
)

create sequence tb_error_seq;
declare
r_tb_clazz tb_clazz%rowtype;
v_sqlcode varchar2(50);
v_sqlerrm varchar2(200);
begin 
  select * into r_tb_clazz from tb_clazz where id=2;
  dbms_output.put_line(r_tb_clazz.code);
  exception
  when no_date_found then
    dbms_output.put_line('没有数据');
    when others then
      dbms_output.put_line('others');--一定要以这个为结束；
end; 


   
declare
r_tb_clazz tb_clazz%rowtype;
v_sqlcode varchar2(50);
v_sqlerrm varchar2(200);
begin 
  select * into r_tb_clazz from tb_clazz where id=2;
  dbms_output.put_line(r_tb_clazz.code);
  exception
  when others then
    v_sqlcode:=sqlcode;
    v_sqlerrm:=sqlerrm;
    insert into tb_error (id,tablename,sqlcode,sqlerrm) values(tb_error_seq.nextval,'a',v_sqlcode,v_sqlerrm);
    commit;
end;  
insert into tb_clazz (id,code) values(2,'aaa');
    commit;
declare
r_tb_clazz tb_clazz%rowtype;
v_sqlcode varchar2(50);
v_sqlerrm varchar2(200);
begin 
  select * into r_tb_clazz from tb_clazz;
  dbms_output.put_line(r_tb_clazz.code);
  exception
  when others then
    v_sqlcode:=sqlcode;
    v_sqlerrm:=sqlerrm;
    insert into tb_error (id,tablename,sqlcode,sqlerrm) values(tb_error_seq.nextval,'a',v_sqlcode,v_sqlerrm);
    commit;
end;      
select * from tb_error;


--游标
--提取tb_clazz表所有的数据
declare
--创建一个游标
   cursor c_tb_clazz is
   select * from tb_clazz;
   v_tb_clazz tb_clazz%rowtype;
begin
  --打开游标
  open c_tb_clazz;
  --提取数据
  fetch c_tb_clazz into v_tb_clazz;
  dbms_output.put_line('id='||v_tb_clazz.id);
  fetch c_tb_clazz into v_tb_clazz;
  dbms_output.put_line('id='||v_tb_clazz.id);
  --关闭游标
  close c_tb_clazz;
  end;
--循环提取
declare
--创建一个游标
   cursor c_tb_clazz is
   select * from tb_clazz;
   v_tb_clazz tb_clazz%rowtype;
begin
  --打开游标
  open c_tb_clazz;
  --提取数据
  loop
  fetch c_tb_clazz into v_tb_clazz;
  exit when c_tb_clazz%notfound;
  dbms_output.put_line('id='||v_tb_clazz.id);
  dbms_output.put_line('code='||v_tb_clazz.code);

  end loop;
  --关闭游标
  close c_tb_clazz;
end;

--提取每个班级的信息，包括班级里面学生信息
declare
 cursor c_tb_clazz is
 select * from tb_clazz;
 cursor c_tb_student(v_class_id number) is
 select * from tb_student where class_id=v_class_id;
 r_tb_clazz tb_clazz%rowtype;
 r_tb_student tb_student%rowtype;
begin
  --打开游标
  open c_tb_clazz;
  --提取数据
  loop
    fetch c_tb_clazz into r_tb_clazz;
    exit when c_tb_clazz%notfound;
     dbms_output.put_line('第'||r_tb_clazz.id||'班');
    dbms_output.put_line('班级名为--'||r_tb_clazz.code);
    --********************************
    open c_tb_student(r_tb_clazz.id);
    loop
      fetch c_tb_student into r_tb_student;
      exit when c_tb_student%notfound;
      dbms_output.put_line('姓名:'||r_tb_student.lastname);
      end loop;
      close c_tb_student;
    end loop;
   
  --关闭游标
  close c_tb_clazz;
  end;
select * from tb_clazz;
truncate table tb_clazz;
truncate table tb_student;
delete from tb_student;
delete from tb_clazz;
create sequence tb_clazz_seq;
begin
insert into tb_clazz(id,code) values(tb_clazz_seq.nextval,'fpp');
insert into tb_clazz(id,code) values(tb_clazz_seq.nextval,'许洁');
commit;
end;
drop sequence tb_student_seq;
create sequence tb_student_seq;
select * from tb_course;
begin
  insert into tb_student(id,lastname,age,sex,email,address,class_id,course_id,great)
  values(tb_student_seq.nextval,'付鹏鹏',21,'男','123@qq.com','南昌',1,2,98);
  insert into tb_student(id,lastname,age,sex,email,address,class_id,course_id,great)
  values(tb_student_seq.nextval,'许洁',20,'女','231@qq.com','邯郸',2,2,98);
  commit;
  end;

　存储过程和函数

pl/sql程序单元    作为数据库对象保存在数据库里

主要有4类

    1.存储过程    没有返回结果  执行特定的操作

    2.函数    有返回值    进行复杂计算

   3.包     相当于java中的类     逻辑上相关的过程和函数组织在一起

   4.触发器     事件触发，执行相应的操作

参数模式

  in                                out                                        inout

传递给子程序                 可以改变                              可以改也可以不改

子程序不能改变参数值

create   or  replace procedure  存储过程名

is

begin

end;

如果f8的话没有执行，而是编译，只有调用了他才会执行

 

 

函数

函数只有in  没有out 和inout

create  or replace function 函数名（参数  参数类型） return   返回值类型

is

begin

end;

包（相当于java中的接口  他还有一个包体相当于实现类     只要在包中定义了的，在包体中都要实现）

create or replace package 包名  

is

begin

end

包体

create or replace package body 包名（要和包名一致否则会出错）

is

begin 

end;

--存储过程(没有返回值的函数)和函数
/****************
   pl/sql程序单元
   是数据库中命名的pl/sql块，作为数据库对象保存在数据库里。
   存储过程：执行特定的操作，没有返回值。
   函数：进行复杂的运算，有返回值。
   包：相当于java中的类，可以在里面定义变量常量方法（存储过程，函数等）
***********************/
create or replace procedure firstPro
is
   
begin
  --向tb_student中插入一条数据
  insert into tb_student(id,lastname,age,sex,email,address,class_id,course_id,great)
  values(tb_student_seq.nextval,'爸爸',46,'男','123888@qq.com','南昌',1,2,98);
  commit;
  end firstPro;
--存储过程执行只是编译只有调用时才是执行了  他作为ｐｌ/sql存储单元，作为数据库对象保存在数据库中
--调用存储过程
declare 
begin
  firstPro;
  end;
select * from tb_student;

--有参数的存储过程
create or replace procedure paraPro
(
  v_lastname varchar2,
  v_age number,
  v_sex varchar2,
  v_email varchar2,
  v_address varchar2,
  v_class_id number,
  v_course_id number,
  v_great number
) 
is
begin
  insert into tb_student(id,lastname,age,sex,email,address,class_id,course_id,great)
  values(tb_student_seq.nextval,v_lastname,v_age,v_sex,v_email,v_address,v_class_id,v_course_id,v_great);
  commit;
  end paraPro;
  
--调用有参存储过程
declare
begin
  paraPro('fwppr',24,'男','123343dddd@qq.com','北京',1,2,97);
  end; 
select * from tb_student;
create sequence seq_tb_student;

--练习
/*******************
  存储过程更过情况下实在数据库方做数据整合等复杂的工作，现在在开发银行系统，数据相当重要   tb_student代替一张重要的
  表把表中的数据备份
  
  备份每天的表的数据
**************************/
/**********
   思路：首先你要知道见几张表才能完成这个备份工作
   1.你要备份得有一张备份表
   2.备份的条件是什么呢？每天的12点进行备份
   那么就是每天存储的主键id最大的时候为临界点
   所以又需要一张表来进行存储每天最大的主键id值并且每天必须更新
   3.存储过程的参数就为每天主键最大的id值
**************/
create or replace procedure backShuju
(v_max number)
is
cursor c_tb_student is
select * from tb_student where id>v_max;
v_back_tb_student tb_student%rowtype;
a number:=0;
v_maxid number;
begin
  --判断游标是否打开
  if(c_tb_student%isopen) then
  null;
  else
    open c_tb_student;
    end if;
 loop
 fetch c_tb_student into v_back_tb_student;
 exit when c_tb_student%notfound;
 insert into tb_student_back(id,lastname,age,sex,email,address,class_id,course_id,great)
 values(v_back_tb_student.id,v_back_tb_student.lastname,v_back_tb_student.age,v_back_tb_student.sex,
 v_back_tb_student.email,v_back_tb_student.address,v_back_tb_student.class_id,v_back_tb_student.course_id,v_back_tb_student.great);
 a:=a+1;
 /***
   1.如果在循环中commit效率低下
   2.如果数据量很大，没有commit就没有提交，放在缓存当中。缓存就是内存当中，在oracle回滚段不足导致瘫痪
 ****/
 if(a>=2000) then
 commit;
 a:=0;
 end if;
 end loop;
 commit;
 select max(id) into v_maxid from tb_student_back;
 update tb_max set id=v_maxid;
 close c_tb_student;
  end backShuju;
  
  
  
--创建数据备份表
 create table tb_student_back as
 select * from tb_student where id=-1;
 --创建存储每天主键最大id表
 create table tb_max(
 id number
 );
 
--创建调用备份存储过程的存储过程
create or replace procedure invokeBackShuju
is
  v_max number;
begin
  select id into v_maxid from tb_max;
  backShuju(v_max);
  end invokeBackShuju;
select * from tb_student_back;
select * from tb_student;
select * from tb_max;
insert into tb_max (id) values(0);
drop table tb_max;
drop table tb_student_back;
begin
  invokeBackShuju;
  end;
declare
v_aa number:=2000;
begin
  while (v_aa>=0) loop
  insert into tb_student(id,lastname,age,sex,email,address,class_id,course_id,great)
  values(tb_student_seq.nextval,'许洁'||v_aa,46,'男','1s8822'||v_aa||'qq.com','南昌',1,2,98);
  v_aa:=v_aa-1;
  end loop;
  commit;
  end;
  
  
--创建任务调度器
declare
jobno number;
begin
  dbms_job.submit(
  jobno,
  what=>'invokeBackShuju;',--为存储过程的名称
  Interval=>'trunc(sysdate,''mi'')+1/(24*60)'
  );
  commit;
  end;
 

--实验游标和%rowtype的功能区别
--%rowtype最多只能提取一条数据
--游标可以提取多条数据
--下面语句错误
declare
r_tb_student tb_student%rowtype;
begin
  select * into r_tb_student from tb_student;
  end;



--函数和包
--无参函数
create or replace function firstfun return varchar2
is
begin
  return '许洁是傻逼';
  end firstfun;
  
--调用函数
declare
begin
  dbms_output.put_line(firstfun());
  end;
--有参函数
create or replace function parafun(v_para number)return number is
begin
  return v_para*12;
  end parafun;
  
  
--调用有参函数
declare 
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=7499;
  dbms_output.put_line(parafun(v_sal));
  end;
  
  
select * from emp;

--包
--相当于java中的接口
--包体相当于接口方法的实现类
create or replace package firstpage is
  --在包中定义一个type类型的变量
  type strings is array;
  --定义一个常量
  i constant number:=1;
  --定义一个过程
  procedure secondPro;
  --定义一个函数
  function secondfun(v_max number) return number;
end firstpage;

create or replace package body firstpage is
  function secondfun(v_max number) return number is
    begin
      return v_max*12; 
      end;
  procedure secondPro is
    begin
      insert into tb_clazz(id,code) values(tb_clazz_seq.nextval,'ddddd');
      commit;
      end;
end firstpage;


--调用包
declare 
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=7499;
  dbms_output.put_line('年薪》》'||firstpage.secondfun(v_sal));
  firstpage.secondPro;
  end;
select * from tb_clazz;

触发器（相当于js当中的事件  只要触发了那个事件，下面的代码就会执行）

触发器中不能提交

因为触发器是跟着数据走的，如果提交了完成触发器的执行之后想要回滚将不可以

什么是触发器？

触发器在数据库里以独立的对象存储，他和存储过程是不一样的  ，存储过程是通过被调用才执行，而触发器使用过某次事件发生了，根据事件有没有发生来决定他是否执行

1.dml触发器      

相当于一个dml语句影响到了多条sql语句，对于数据库的每个数据行，只要它符合触发的条件，那么触发器将被激活一次，这是行级触发器

语句级触发器   

将整个语句操作为触发事件，让符合约束条件，激活一次触发器

create  or replace  tirgger 名

after  delete on  tb_clazz  --在tb_clazz表执行删除操作时，触发这个事件

referenceing  olg as myold new as mynew--说明相关名称      触发器的pl/sql中应用相关名称时必须在她们之前加冒号:;但是在when子句中不能加冒号

for each row

declare

begin

end

 

:new   访问操作完成后的值 

:old    访问操作完成之前的值

2.替代触发器

不能对由两个以上的表建立的视图进行操作。所以给出了替代触发器；专门为进行视图操作的一种处理方法

3.系统触发器

创建这个触发器需要一个权限

administer database trigger

系统触发器时间表  logging_event

event：=sysevent

type:=dictionary_obj_type   对象的类型

name:=dictionary_obj_name  对象的名称

owner:=dictionary_obj_owner  对象的拥有者  也就是哪个用户

create  or replace trigger 触发器名

after  login database

declare

begin

end;

--触发器
--在触发器中不用提交
create or replace trigger firstTrigger
after delete /* or update*/on tb_clazz
referencing new as mynew old as myold
for each row--行级触发
declare
v_str tb_clazz%rowtype;
begin
  v_str.id:=:myold.id;
  v_str.code:=:myold.code;
  dbms_output.put_line(v_str.id);
  dbms_output.put_line(v_str.code);
  insert into tb_clazz_back (id,code) values(v_str.id,v_str.code);
  --触发器中不能提交   
  --触发器是跟着数据走的，在触发器中commit的话触发器中的sqlcommit了，而且他监听的delete也commit了，所以想
  --会滚时就不可以了
  end;
insert into tb_clazz(id,code) values(9,'111');
select * from tb_clazz;
delete from tb_clazz where id=9;
select * from tb_clazz_back;
create table tb_clazz_back as select * from tb_clazz where id=9999999;


   drop table logging_event;

--系统级触发器
--赋予创建任何触发器的权限
grant create any trigger to scott;
grant create trigger to scott;
--授予管理数据库触发器的权限
grant administer database trigger to scott;
  create table scott.logging_event(
   username varchar2(20),
   logintime date
   );
--创建登录记录时候的备份
create or replace trigger XiTongTri 
after logon on database
declare 
begin
   insert into scott.logging_event(username,logintime) values(USER,sysdate);
   commit;
  end;
select * from logging_event;

drop table loggin_event2 purge;
create table loggin_event3(username varchar2(20),tablename varchar2(20));
create or replace trigger deleteTri
after create on database
declare
    tablename varchar2(20);
begin
   tablename:=dictionary_obj_name;
   insert into loggin_event3(username,tablename) values(user,tablename);
   --在触发器中不能commit;
  end;
  create table tb_test_1(a number);
  select * from loggin_event3;