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操作数据--复合数据类型--varraydeclaretype strings is varray(5) of varchar2(10);--定义一个数组数组元素的长度为5个，每个是10个可变长度v_str strings:=strings('aa','bb','cc','dd','ee');--创建一个数组对象的引用 从一开始的begindbms_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中的可变数组declaretype strings is table of varchar2(10) index by binary_integer;--定义一个可变数组，数组中每个元素的可变长度为10--数组角标没有下限v_str strings;--定义一个可变数组对象的应用beginv_str(1):='aa';v_str(999):='bb';dbms_output.put_line(v_str(1)||v_str(999));end;--recorddeclare--可以定义多个类型type strings is record(v_name tb_student.lastname%type,id number,r_tb_student tb_student%rowtype);v_str strings;beginselect 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;

--流程控制--分支语句declarev_str varchar2(10):='b';beginif(v_str='a') thendbms_output.put_line('if');elsif(v_str='b') thendbms_output.put_line('elsif');elsedbms_output.put_line('else');end if;end;declare i constant number:=4;beginif(i=5) thennull; --如果什么都不做的话，那么补null来保证语句的完整性elsif(i=4) thendbms_output.put_line(i);elsedbms_output.put_line('else');end if;end;--验证如果不给字符变量赋值时默认是空字符串还是为空declarei varchar2(10);beginif(i='') thendbms_output.put_line('空字符串');elsif(i is null) thendbms_output.put_line('null');elsif(i='a') thendbms_output.put_line('a');elsedbms_output.put_line('b');end if;end;declarei number;beginif(i='') thendbms_output.put_line('空字符串');elsif(i is null) thendbms_output.put_line('null');elsif(i='a') thendbms_output.put_line('a');elsedbms_output.put_line('b');end if;end;--循环控制--简单循环loopdeclare i number:=0;begin loopi:=i+1;dbms_output.put_line('i='||i);if(i=30) thenexit;end if;end loop;end;declare i number:=0;begin loopi:=i+1;dbms_output.put_line('i='||i);exit when (i=30); end loop;end;--嵌套循环declarei number:=0;b number:=0;beginloopi:=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;declarei number:=0;b number:=0;beginloopi:=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;declarei number:=0;b number:=0;begin<<outer>>loopi:=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 loopdbms_output.put_line('i'||i);end loop;end;declare a constant number:=1;b constant number:=10;begin for i in a ..b loopdbms_output.put_line('i'||i);end loop;end;declare a constant number:=1;b constant number:=10;begin for i in reverse a ..b loopdbms_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) loopi:=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 游标名(参数 参数类型）

--异常处理declarer_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);exceptionwhen no_data_found thendbms_output.put_line('没有数据');when others thendbms_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;declarer_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);exceptionwhen no_date_found thendbms_output.put_line('没有数据');when others thendbms_output.put_line('others');--一定要以这个为结束；end; declarer_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);exceptionwhen others thenv_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;declarer_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);exceptionwhen others thenv_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 isselect * 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 isselect * from tb_clazz;v_tb_clazz tb_clazz%rowtype;begin--打开游标open c_tb_clazz;--提取数据loopfetch 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;--提取每个班级的信息，包括班级里面学生信息declarecursor c_tb_clazz isselect * from tb_clazz;cursor c_tb_student(v_class_id number) isselect * 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;--提取数据loopfetch 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);loopfetch 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;begininsert 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;begininsert into tb_student(id,lastname,age,sex,email,address,class_id,course_id,great)values(tb_student_seq.nextval,'付鹏鹏',21,'男','123@','南昌',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@','邯郸',2,2,98);commit;end;

存储过程和函数

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

主要有4类

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

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

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

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

参数模式

inoutinout

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

子程序不能改变参数值

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 firstProisbegin--向tb_student中插入一条数据insert into tb_student(id,lastname,age,sex,email,address,class_id,course_id,great)values(tb_student_seq.nextval,'爸爸',46,'男','123888@','南昌',1,2,98);commit;end firstPro;--存储过程执行只是编译只有调用时才是执行了 他作为ｐｌ/sql存储单元，作为数据库对象保存在数据库中--调用存储过程declare beginfirstPro;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) isbegininsert 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;--调用有参存储过程declarebeginparaPro('fwppr',24,'男','123343dddd@','北京',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)iscursor c_tb_student isselect * 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) thennull;elseopen c_tb_student;end if;loopfetch 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) thencommit;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 asselect * from tb_student where id=-1;--创建存储每天主键最大id表create table tb_max(id number);--创建调用备份存储过程的存储过程create or replace procedure invokeBackShujuisv_max number;beginselect 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;begininvokeBackShuju;end;declarev_aa number:=2000;beginwhile (v_aa>=0) loopinsert 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||'','南昌',1,2,98);v_aa:=v_aa-1;end loop;commit;end;--创建任务调度器declarejobno number;begindbms_job.submit(jobno,what=>'invokeBackShuju;',--为存储过程的名称Interval=>'trunc(sysdate,''mi'')+1/(24*60)');commit;end;--实验游标和%rowtype的功能区别--%rowtype最多只能提取一条数据--游标可以提取多条数据--下面语句错误declarer_tb_student tb_student%rowtype;beginselect * into r_tb_student from tb_student;end;--函数和包--无参函数create or replace function firstfun return varchar2isbeginreturn '许洁是傻逼';end firstfun;--调用函数declarebegindbms_output.put_line(firstfun());end;--有参函数create or replace function parafun(v_para number)return number isbeginreturn v_para*12;end parafun;--调用有参函数declare v_sal emp.sal%type;beginselect 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 isfunction secondfun(v_max number) return number isbeginreturn v_max*12; end;procedure secondPro isbegininsert into tb_clazz(id,code) values(tb_clazz_seq.nextval,'ddddd');commit;end;end firstpage;--调用包declare v_sal emp.sal%type;beginselect 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 firstTriggerafter delete /* or update*/on tb_clazzreferencing new as mynew old as myoldfor each row--行级触发declarev_str tb_clazz%rowtype;beginv_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 databasedeclare begininsert 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 deleteTriafter create on databasedeclaretablename varchar2(20);begintablename:=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;

如果觉得《oracle学习总结2(pl/sql 游标　异常的处理 存储过程和函数 包 触发器)》对你有帮助，请点赞、收藏，并留下你的观点哦！