做路线图的网站,宝安区在深圳排第几,了解网站的建设心得,网站建设技术方案模板下载目录 异常
自定义异常的格式
raise_application_error
处理异常
预定义异常
SQLcode和SQLerrm
非预定义异常
作业
触发器
触发器基本概念
DML触发器
DML触发器使用
instead of 触发器
管理触发器
作业2
函数、过程和包
函数
过程
参数
1. in 参数
2.out 参…目录 异常
自定义异常的格式
raise_application_error
处理异常
预定义异常
SQLcode和SQLerrm
非预定义异常
作业
触发器
触发器基本概念
DML触发器
DML触发器使用
instead of 触发器
管理触发器
作业2
函数、过程和包
函数
过程
参数
1. in 参数
2.out 参数
3. in out 参数
函数和过程的区别
过程优点
过程缺点
作业3
遗忘点复习
后面预告 异常
在PL/SQL中系统错误都由程序异常统一对待。一个异常一般属于以下情况
●系统错误内存溢出索引重复
●用户动作导致的错误
●应用程序向用户发出的警告
异常分成三大类预定义异常、非预定义异常、自定义异常
处理方法分为直接抛出异常、内部块处理异常、游标处理异常 预定义异常由PL/SQL定义的异常。由于它们已在standard包中预定义了因此这些预定义异常可以直接在程序中使用而不必再定义部分声明。
非预定义异常用于处理预定义异常所不能处理的Oracle错误。
自定义异常用户自定义的异常需要在定义部分声明后才能在可执行部分使用。用户自定义异常对应的错误不一定是Oracle错误例如它可能是一个数据错误。
三种异常中预定义与非预定义异常都与Oracle错误有关并且由Oracle隐含自动抛出而自定义异常与Oracle错误没有任何关联由开发人员为特定情况所定义的异常需要显式抛出(raise)。
PL/SQL使用一个异常处理框架在跟踪并对错误进行响应。异常分为系统异常和程序员自定义异常。系统异常通常都有一个名称。自定义异常则使用exception_init指令给oracle指定名称或使用raise_application_error 给这个错误指定错误编号和描述。 自定义异常的格式
声明异常
声明异常的格式
异常名称 exception;
抛出异常
声明异常后在程序中抛出异常。
RAISE 异常名;
RAISE 包名.异常名称;
RAISE; 第一种是抛出当前块异常第二种是抛出当前包异常这种方式能够实现在包外进行异常的抛出。第三种不需要异常名称用于在异常中抛出异常实现异常传播的目的。
raise_application_error
该过程用于自定义错误信息仅限数据库端子程序使用过程、函数、包、触发器不能在匿名块或客户端子程序中。
格式
raise_application_error(error_number,mesage[,[true|false]]);
其中error_number定义错误号必须是-20000到-20999之间的负整数message指定错误信息不长于2048字节 第三个为可选参数true则错误会被放在先前错误的堆栈中false则替换先前所有错误默认为false。
使用RAISE_APPLICATION_ERROR 抛出异常的好处在于可以给异常加上一段错误消息。 处理异常
当有异常抛出当前代码块会终止执行并跳转到异常处理单元在异常处理单元
exception处理异常。
when 异常名称 [or 异常名称] then 处理语句;
when others then 处理语句; 预定义异常
Oracle根据程序可能报错的情况定义的一类异常常见的Oracle预定义异常如下 SQLcode和SQLerrm
sqlCode是数据库操作的返回码。
sqlerrm是数返回指定错误代码的错误信息。
在一个内在的异常中SQLCODE返回Oracle错误的序号而SQLERRM返回的是相应的错误消息错误消息首先显示的是错误代码。SQLCODE返回的是负数除非Oracle的错误为“ORA-01403NO DATA FOUND”译ORA-01403未找到数据当Oracle错误为“ORA-01403NO DATA FOUND”时其对应的SQLCODE为100。对于用户自定义的异常SQLCODE返回的是1而SQLERRM返回的是User-Defined Exception。
sqlcode和sqlerrm是不能直接在sql语句中使用必须先将其赋给变量后才能在sql语句中使用 --预定义异常
declare
v_empno number;
begin
SELECT EMPNO INTO V_EMPNO from emp ;
exception
when TOO_MANY_ROWS then
dbms_output.put_line(sqlerrm||-sql语句返回结果多余一行||sqlcode);
end; --多个异常
declare
v_empno number;
begin
SELECT EMPNO INTO V_EMPNO from emp where empno2;
exception
when no_data_found then
dbms_output.put_line(sqlerrm ||-啥也没有||sqlcode);
when TOO_MANY_ROWS then
dbms_output.put_line(sqlerrm||-sql语句返回结果多余一行||sqlcode);
when others then
dbms_output.put_line(sqlerrm||---------||sqlcode);
end; 非预定义异常
非预定义异常用于处理与21个预定义异常无关的Oracle错误。 由于预定义异常只是与一部分Oracle错误相连的异常所以如果要处理没有与预定义异常对应的Oracle错误时则需要为这些Oracle错误声明相应的非预定义异常。
注意通过exception_init一个自定义异常只能和一个Oracle错误相连在异常处理语句中 sqlcode和sqlerrm将返回这个Oracle错误的代码和消息文本而不是返回用户自定义消息。 --非预定义异常
declare
errorsql exception ;
pragma exception_init(errorsql,-2291);
begin
update emp set deptno 55 where empno7499;
exception
when errorsql then
dbms_output.put_line(sqlerrm||输入的部门编号有误查无此部门||sqlcode);
end; --自定义异常
declare
v_sal number:输入工资;
lowersal exception ;--声明一个异常名称
begin
if v_sal 2000 then
raise lowersal ;--抛出异常
else update emp set sal v_sal where empno员工编号;
end if;
exception
when lowersal then --处理异常
dbms_output.put_line(你给的工资太低了 社保局要调查你的);
end; 作业
第一题乱写的第二题抄的课件第三题自己认真写的。
--1.练习预定义异常多个异常和其他异常declarev_empno number;beginSELECT EMPNO INTO V_EMPNO from emp ;exceptionwhen others thendbms_output.put_line(杂鱼~代码都不会写的杂鱼~); end;--2.写一个触发员工表主键的非预定义异常declareerrorsql exception ;pragma exception_init(errorsql,-2291);beginupdate emp set deptno 55 where empno7499;exceptionwhen errorsql thendbms_output.put_line(sqlerrm||输入的部门编号有误查无此部门||sqlcode);end;--3.输入员工编号和要修改的工资如果工资低于3000则抛出自定义异常declarev_empno emp.empno%type:输入员工编号;v_sal emp.sal%type:输入要修改的工资;RowSal exception;beginif v_sal3000 thenraise RowSal;elseupdate emp set salv_sal where empnov_empno;end if;exceptionwhen RowSal thendbms_output.put_line(工资太低了员工要活不下去啦); end; 触发器
触发器使用场景
●对表的修改进行验证
●数据库的自我维护
●通过不同方式对数据库活动进行规范
触发器触发场景
●dml语句触发
●ddl语句触发
●数据库事件触发如登录、退出、启动和关闭
●instead of 触发针对视图操作
●挂起触发语句运行中遇到空间问题挂起后触发 触发器基本概念
before触发器在触发场景执行前执行触发器语句
after触发器在触发场景执行后执行触发器语句
语句级别触发器触发场景以语句为单位进行触发一个语句只触发一次
行级别触发器触发场景以记录为单位进行触发每影响一行记录触发一次
伪记录new触发场景触发后记录的值
伪记录old触发场景触发前记录的值
when语句通过判断条件执行触发器 DML触发器
dml触发器格式
create or replace trigger 触发器名称
{before|after} --指明触发器实在语句执行前或之后进行处理
{insert|delete|update|update of 列名表} on 表名--指明触发器是在哪种DML语句中触发
[for each row]--指明语句会在处理每行记录是激活触发器默认是只为整个语句激活一次
[when .....] --一个when语句来指明触发逻辑避免一些错误执行
[declare] 触发器中禁用commit 、rollback。整个when语句需要用括号包裹并且只能使用内置函
数自定义函数无法引用when语句只能用于行级别触发器语句级无法使用when语
句。对于insert 语句不存在 old伪记录对于delete语句不存在new伪记录。
DML触发器使用
DML触发器使用规范
1. 触发器不接受参数。
2. 一个表上最多可有12个触发器但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
3. 在一个表上的触发器越多对在该表上的DML操作的性能影响就越大。--索引--约束--序列--
4. 触发器最大为32KB。若确实需要可以先建立过程然后在触发器中用CALL语句进行调用。
5. 在触发器的执行部分只能用DML语句SELECT、INSERT、UPDATE、DELETE不能使用DDL语句CREATE、ALTER、DROP。--隐式事务
6. 触发器中不能包含事务控制语句(TCL)。因为触发器是触发语句的一部分触发语句被提交、回退时触发器也被提交、回退了。
7. 在触发器主体中调用的任何过程、函数都不能使用事务控制语句。
8. 在触发器主体中不能申明任何clob和blob变量。新值new和旧值old也不能向表中的任何long和blob列。
9. 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。 instead of 触发器
针对视图操作的触发器
instead of insert
instead of update
instead of delete
create or replace trigger test_emp_trigger
instead of insert on view_emp
for each row
begin
insert into emp values(:new.empno,:new.enmame,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);
insert into dept values(:new.deptno,:new.dname,:new.loc);
end;
管理触发器
禁用触发器
alter trigger 触发器名称 disable;
激活触发器
alter trigger 触发器名称 enable;
删除触发器
drop trigger 触发器名称; create or replace trigger dml_emp_trigger
before
update or delete or insert on emp
for each row
begin
if updating then
dbms_output.put_line(更新了一条记录更新内容为 员工编号
||:new.empno||员工姓名||:new.ename||员工职位||:new.job);
elsif deleting then
dbms_output.put_line(删除了一条记录删除内容为 员工编号
||:old.empno||员工姓名||:old.ename||员工职位||:old.job);
elsif inserting then
dbms_output.put_line(插入了一条记录插入内容为 员工编号
||:new.empno||员工姓名||:new.ename||员工职位||:new.job);
end if;
end; select * from emp;
insert into emp values(9899,test,sale,9999,sysdate,3333,444,10);
update emp set sal990 where empno9899;
delete from emp where empno9899; --添加到emplog日志表中
create table emplog
(
id number(8),
time date,
type varchar2(6),
empno number(4)
)
create sequence seq_emplog_1
start with 1000
seq_emplog_1.nextval create or replace trigger dml_emp_trigger
before
update or delete or insert on emp
for each row
begin
if updating then
dbms_output.put_line(更新了一条记录更新内容为 员工编号
||:new.empno||员工姓名||:new.ename||员工职位||:new.job);
insert into emplog values(seq_emplog_1.nextval,sysdate,update,:new.empno);
elsif deleting then
dbms_output.put_line(删除了一条记录删除内容为 员工编号
||:old.empno||员工姓名||:old.ename||员工职位||:old.job);
insert into emplog values(seq_emplog_1.nextval,sysdate,delete,:old.empno);
elsif inserting then
dbms_output.put_line(插入了一条记录插入内容为 员工编号
||:new.empno||员工姓名||:new.ename||员工职位||:new.job);
insert into emplog values(seq_emplog_1.nextval,sysdate,add,:new.empno);
end if;
end; insert into emp values(5555,test,sale,9999,sysdate,3333,444,10);
update emp set sal990 where empno5555;
delete from emp where empno5555;
select * from emplog ; 作业2
第二题乱写的可无视。
--1、创建触发器对emp表进行增删改操作create or replace trigger t_empbeforeinsert or update or delete on empfor each rowbeginif inserting thendbms_output.put_line(有人偷偷插入数据);elsif updating thendbms_output.put_line(有人偷偷修改数据); elsif deleting thendbms_output.put_line(有人偷偷删除数据); end;--2、把上面的记录添加到表emplog中create or replace trigger t_empbeforeinsert or update or delete on emplogfor each rowbeginif inserting thendbms_output.put_line(有人偷偷插入数据);elsif updating thendbms_output.put_line(有人偷偷修改数据); elsif deleting thendbms_output.put_line(有人偷偷删除数据); end;--3、查看oracle自带日志 系统表v$sqlselect * from v$sql; 函数、过程和包
函数
所谓函数是通过 return语句而不是 out 或in out 参数返回数据的模块。和过程的调用不同的是前者可以作为一个可执行语句单独存在而函数只能作为一个可执行语句的一部分如表达式中的元素或声明时赋值。
函数格式
create or replace function 函数名称[参数,参数,参数....]
return 返回数据类型
is --计算两个数的和
create or replace function getSum(n1 in number,n2 in number)
return number
is
he number(4);
begin
he:n1n2;
return he;
end;
select getSum(3,5)from dual;
select getSum(3,5),upper(ttt) from dual; --根据输入日期输出星期几
create or replace function f_day(da in varchar2)
return varchar2
is
d date :to_date(da,yyyymmdd);
begin
return to_char(d,day);
end;
select f_day(20220518) from dual; 过程
所谓过程就是执行一个或多个动作的块。由于在PL/SQL中对于过程的是一个单独的可执
行语句一个PL/SQL代码中可以只有一个过程调用语句。
过程的格式
create or replace procedure 过程名称 (参数,参数,参数)
is
可以有0个或多个返回值通过out参数来返回结果 过程的调用
call 过程名();
或
declare
begin
过程名();
end; 示例
--无输入参数无输出参数
create or replace procedure p1
is
begin
dbms_output.put_line(我的第一个过程);
end;
call p1();
declare
begin
dbms_output.put_line(ttt);
p1();
end; 参数
存储过程作用执行效率和SQL 代码封装
1. in 参数
用于接收参数在子程序内部不能进行修改。默认的参数模式in
-- 声明存储过程
create or replace procedure pro_in(p_num in number)
is
begin
dbms_output.put_line(p_num);
-- p_num:10;-- 添加此行编译报错,in 参数不能赋值
end ; -- 调用
declare
test number:1;
begin
pro_in(test);
dbms_output.put_line(test);
end;
结果: 输出 两个 1 2.out 参数
输出模式的参数用于输出值会忽略传入的值。在子程序内部可以对其进行修改。
调用时 参数需要使用变量.
-- 声明存储过程
create or replace procedure pro_out(p_num out number)
is
begin
dbms_output.put_line(p_num);
p_num:10;
end; -- 调用
declare
test number:1;
begin
pro_out(test);
dbms_output.put_line(test);
end;
结果: 忽略输入的值,输出一个 空,和一个 10 3. in out 参数
能接收传入的实参值在子程序内部可以修改,可以输出
调用时 参数需要使用变量.
-- 声明存储过程
create or replace procedure pro_in_out(p_num in out number)
is
begin
dbms_output.put_line(p_num);
p_num:10;
end ; -- 调用
declare
test number:1;
begin
pro_in_out(test);
dbms_output.put_line(test);
end;
结果: 输出一个 1 和 一个 10 --输入empno返回ename
create or replace procedure getName(eno in number,name1 out varchar2)
is
n number(4);
begin
n:9;
dbms_output.put_line(n);
select ename into name1 from emp where empnoeno;
end;
declare
name1 varchar2(10);
begin
getName(7499,name1);
dbms_output.put_line(name1);
end; --三个输入参数和两个输出参数 返回两个值
create or replace procedure
p2(n1 in number,n2 in number,he out number,ji in out number)
is
begin
dbms_output.put_line(he);
dbms_output.put_line(ji);
he:n1n2;
ji:n1*n2;
dbms_output.put_line(he);
dbms_output.put_line(ji);
end;
declare
he number(4);
ji number(4):0;
begin
p2(3,5,he,ji);
select getSum(3,6) into he from dual;
dbms_output.put_line(he);
dbms_output.put_line(ji);
p1();
end; --改造猴子吃桃代码1
create or replace procedure taozi
is
tao number(4):1;
begin
for i in reverse 1..9
loop
tao:(tao1)*2;
end loop;
dbms_output.put_line(tao);
end;
call taozi(); --改造猴子吃桃代码2
create or replace procedure chitao1(tao in out number)
is
begin
for i in reverse 1..9
loop
tao:(tao1)*2;
end loop;
end;
declare
tao number(4):1;
begin
chitao1(tao);
dbms_output.put_line(tao);
end; 函数和过程的区别
1、返回值的区别
函数有1个返回值,而存储过程是通过参数返回的,可以有多个或者没有
2、调用的区别,函数可以在查询语句中直接调用,而存储过程必须单独调用.
函数一般情况下是用来计算并返回一个计算结果
存储过程 一般是用来完成特定的数据操作比如修改、插入数据库表或执行某些DDL语句等等 过程优点
1、存储过程可以一次编译多次使用。 存储过程只在创建时进行编译之后的使用都不需要重新编译这就提升了 SQL 的执行效率。
2、可以减少开发工作量。将代码封装成模块实际上是编程的核心思想之一这样可以把复杂的问题拆解成不同的模块然后模块之间可以重复使用在减少开发工作量的同时还能保证代码的结构清晰。
3、存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限这样就和视图一样具有较强的安全性。
4、可以减少网络传输量。因为代码封装到存储过程中每次使用只需要调用存储过程即可这样就减少了网络传输量。
5、良好的封装性。在进行相对复杂的数据库操作时原本需要使用一条一条的 SQL 语句可能要连接多次数据库才能完成的操作现在变成了一次存储过程只需要连接一次即可 过程缺点
基于上面这些优点不少大公司都要求大型项目使用存储过程比如微软、IBM 等公司。但是国内的阿里并不推荐开发人员使用存储过程这是为什么呢
存储过程虽然有诸如上面的好处但缺点也是很明显的。
1、可移植性差。存储过程不能跨数据库移植比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程在换成其他数据库时都需要重新编写。
2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试但要收费。
3、存储过程的版本管理很困难。比如数据表索引发生变化了可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理但是存储过程本身没有版本控制版本迭代更新的时候很麻烦。
4、它不适合高并发的场景。高并发的场景需要减少数据库的压力有时数据库会采用分库分表的方式而且对可扩展性要求很高在这种情况下存储过程会变得难以维护增加数据库的压力显然就不适用了。 作业3
--1、函数输入一个日期返回当前日期是星期几create or replace function selectday(daytime in varchar2)return varchar2isdd varchar2(10);begindd:to_char(to_date(daytime,yyyymmdd),day);return dd;end;declaredaytime varchar2(10):输入一个日期;n varchar2(10);beginn :selectday(daytime);dbms_output.put_line(n); end;--2、函数实现输入一个字符串,返回一个倒序的字符串改写上机练习4.5create or replace function reversestr(n in varchar2)return varchar2ism varchar2(100);beginfor i in reverse 1..length(n)loopm:m||substr(n,i,1);end loop;return m;end;--3、写一个过程输入员工编号返回员工的工资和姓名create or replace procedure esn(v_empno in out number)istype names is record(ename emp.ename%type,sal emp.sal%type);v names;beginselect ename,sal into v from emp where empnov_empno;dbms_output.put_line(v.ename|| ||v.sal); end;declarev_empno emp.empno%type:输入员工编号;beginesn(v_empno);end;/*4、写一个过程输入一个部门编号,返回该部门工资最高的员工的员工编号不考虑并列用row_number开窗*/create or replace procedure des(v_deptno in out number)isv_empno emp.empno%type;beginselect empno into v_empno from(select row_number()over(partition by deptno order by sal desc) r,empno,deptno from emp) where r1 and deptnov_deptno;dbms_output.put_line(v_empno); end;declarev_deptno emp.deptno%type:输入部门编号;begindes(v_deptno);end;--5、把上面两个过程配合使用实现输入部门编号显示该部门的最高工资和人员姓名create or replace procedure des_and_esn(v_deptno in out number)isv_empno emp.empno%type;beginselect empno into v_empno from(select row_number()over(partition by deptno order by sal desc) r,empno,deptno from emp) where r1 and deptnov_deptno;esn(v_empno);end;declarev_deptno emp.deptno%type:输入部门编号;begindes_and_esn(v_deptno);end;select empno,ename from emp;--验证用 遗忘点复习
Substr(字符串,n,m) 第n位开始截取第m位结束。
例
Substr(‘aaaabbbccc’,-3,2) from dual 返回cc.
Substr(‘aaaabbbccc’,1,3) from dual 返回3个a. 要多敲代码刷题的同时不要脱离理论的学习。 后面预告
