---------- android培训 、java培训、期待与您交流! ----------
期望目标:
a,掌握pl/sql的高级用法(能编写分页过程模块,下订单过程模块...)
b,会处理oracle常见的例外
c,会编写oracle各种触发器
d,理解视图的概念并能灵活使用视图
1,pl/sql的进阶
·控制结构
1)使用各种if语句
a,if——then
案例:编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000,就给
该雇员工资增加10%。
create or replace procedure xp_pro6(xpName varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=xpName;
if v_sal<2000
then update emp set sal=sal+sal*10% where ename=xpName;
end if;
end;
b,if——then——else ,二重条件分支
案例:编写一个过程,可以输入一个雇员名,如果该雇员的补助不是0,就在原
来的基础上增加100;如果补助为0就把补助设为200。
create or replace procedure xp_pro7(xpName varchar2) is
v_comm emp.comm%type;
begin
select nvl(comm,0) into v_comm from emp where ename=xpName;
if v_comm=0 --<>为不等于
then update emp set comm=200 where ename=xpName;
else update emp set comm=comm+100 where ename=xpName;
end if;
end;
c,if——then——elsif——else,多重条件分支
案例:编写一个过程,可以输入一个雇员编号,如果该雇员的职位是PRESIDENT就给他的工资增加1000,如果该雇员的职位是MANAGER就给他的工资增加500,其他职位的雇员工资增加200。
2)使用循环语句loop
a,,loop是pl/sql中最简单的循环语句,这种循环语句以loop开头,以end loop
结尾,这种循环至少会被执行一次。
案例:现有一张表users,表结构如: 用户id 用户名
请编写一个过程,可输入用户名,并循环添加10个用户到users表中,用户
编号从1开始增加。
create or replace procedure xp_pro6(xpName varchar2) is
v_num number:=1;
begin
loop
insert into users values(v_num,xpName);
exit when v_num=10; --判断是否要退出循环
v_num:=v_num+1;
end loop;
end;
b,while循环
基本循环至少要执行循环体一次,而对于while循环来说,只有条件为true时,
才会执行循环体语句,while循环以while..loop开始,以end loop结束。
案例:现有一张表users,表结构如: 用户id 用户名
请编写一个过程,可输入用户名,并循环添加10个用户到users表中,用户
编号从11开始增加。
c,for循环
基本for循环的基本结构如下:
begin
for i in reverse 1..10 loop
insert into users values(i,’小强’);
end loop;
end;
我们可以看到控制变量i,在隐含中就在不停的增加。
3)使用控制语句--goto和null;
a,顺序控制语句goto
goto语句用于跳转到特定标号去执行语句。注意由于使用goto语句会增加程序的复杂性,并使得应用程序可读性变差,所以在做一般应用开发时,建议不要使用goto语句。
基本语法如 goto lable,其中lable是已经定义好的标号名。
declare
i int:=1;
begin
loop
dbms_output.put_line('输出i='||i);
if i=10
then goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;
b,顺序控制语句 null
null语句不会执行任何操作,并且会直接将控制传递到下一条语句,使用null语句的主要好处是可以提高pl/sql的可读性。
declare
v_sal emp.ename%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp
where empno=&no;
if v_sal<3000
then update emp set comm=comm+sal*0.1
where ename=v_ename;
else null;
end if;
end;
2,编写分页过程
分页是任何一个网站(bbs、网上商城、blog)都会使用到的技术,因此学习pl/sql编程
开发就一定要掌握该技术。
·无返回值的存储过程
古人云:欲速则不达,为了让大家伙比较容易接受分页过程编写,应该从简单到复
杂,循序渐进。首先是要掌握最简单的存储过程,无返回值的存储过程:
案例:现有一张表book,表结构如:书号 书名 出版社
编写一个过程,可以向book表添加书,要求通过java程序调用该过程。
·有返回值的存储过程
案例:编写一个过程,可以输入雇员的编号,返回该雇员的姓名。
扩展:编写一个过程,可以输入雇员的编号,返回该雇员的姓名、工资、和岗位。
·有返回值的存储过程(列表[结果集])
案例:编写一个过程,输入部门号,返回该部门所有雇员信息。对该题分析如下:
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用package了,所以要分两部分:
·编写分页过程
有上面的基础,相信大家可以完成分页存储过程了。
要求,请大家编写一个存储过程,要求可以输入表名、每页显示记录数、当前页。
返回总记录数,总页数,和返回的结果集。
·例外处理
1)例外的分类
oracle将例外分为预定义例外,非预定义例外和自定义例外3种。
a,预定义例外用于处理常见的oracle错误
b,非预定义例外用于处理预定义例外不能处理的例外,
c,自定义例外用于处理与oracle
错误无关的其它情况。
2)例外传递
如果不处理例外我们看看出现什么情况呢:
案例,编写一个过程,可接收雇员编号,并显示该雇员的姓名。
问题是,如果输入的雇员编号不存在,怎样去处理呢?
·预定义例外
1)预定义例外case_not_found
在开发pl/sql块中编写case语句时,如果在when子句中没有包含必须的条件分
支,就会触发case_not_found的例外:
2)预定义例外 cursor_slready_open
当重新打开已经打开的游标时,会隐含的触发例外。
3)预定义例外 dup_val_on_index
在唯一索引所对应的列上插入重复的值时,会隐含的触发例外
dup_val_on_index例外。
plsql_test5_例外处理.txt
4)预定义例外invaild_cursor
当试图在不合法的游标上执行操作时,会触发该例外。
例如:试图从没有打开的游标提取数据,或是关闭没有打开的游标,则会触发
该例外:
5)预定义例外 invalid_number
当输入的数据有误时,会触发该例外
比如:数字100写成loo就会触发该例外:
6)预定义例外 no_data_found
下面是一个pl/sql块,当执行select into 没有返回行,就会触发该例外:
7)预定义例外 too_many_rows
当执行select into 语句时,如果返回超过了一行,则会触发该例外:
8)预定义例外 zero_divide
当执行2/0语句时,则会触发该例外。
例如:
declare
v_sum number;
begin
v_sum:=2/0;
exception
when zero_divide
then dbms_output.put_line('0不能作为除数');
end;
9)预定义例外 value_error
当在执行赋值操作时,如果变量的长度不足以容纳实际数据,则会触发该例外
value_error,比如:
declare
v_ename varchar2(5);
begin
select ename into v_ename from emp where empno=&no1;
dbms_output.put_line(v_ename);
exception
when value_error
then dbms_output.put_line(‘变量尺寸不足’);
end;
·其它预定义例外
a,login_denide 当用户非法登陆时,会触发该例外。
b,not_logged_on 如果用户没有登陆就执行dml操作,就会触发该例外。
c,storage_error 如果超出了内存空间或是内存被损坏,就会触发该例外。
d,timeout_on_resource 如果oracle在等待资源时,出现了超时就触发该例外。
·非预定义例外
非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle错误,而当使用pl/sql开发应用程序时,可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时,违反了约束规定等待。在这样的情况下,也可以处理oracle的各种例外。但非预定义例外用的不多。
·处理自定义例外
预定义例外和自定义例外都是与oracle错误相关的,并且出现的oracle错误会隐含的触发相应的例外;而自定义例外与oracle的错误没有任何关联,它是由开发人员为特定情况所定义的例外。
?编写一个pl/sql块,接收一个雇员的编号,并给该雇员工资增加1000元, 如果该雇员不存在,请提示。
3,视图
视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。
1)视图与表的区别 (也是视图的好处)
a,表需要占用磁盘空间,视图不需要。
b,视图不能添加索引。
c,使用视图可以简化复杂查询。比如:学生选课系统
d,视图有利于提高完全性。比如:不同用户查看不同视图。
2)创建视图
create view 视图名 as seelct 语句 with read only;
-- with read only表示只读
3)创建或修改视图
create or replace view 视图名 as seelect 语句;
4)删除视图
drop view 视图名;