玩转oracle学习第七天

痛定思痛。 2022-03-30 11:19 256阅读 0赞

1.上节回想  
2.PL/SQL的进阶  
3.oracle的视图  
4.oracle的触发器  
目标：  
1.掌握PL/SQL的高级使用方法（能编写分页过程模块，下订单过程模块。。

。

）  
2.会处理oracle常见的例外  
3.会编写oracle各种触发器  
4.理解视图的概念并能灵活使用视图

不论什么计算机语言都有各种控制语句，在PL/SQL中也存在这种控制结构

在本部分学习完成后，希望大家达到：  
1）使用各种if  
2）使用循环结构  
3）使用控制语句 goto 和 null;

条件分支语句：  
if then end if

if then else end if

if then elsif else end if

简单的条件推断：  
案例：编写一个过程，能够输入一个雇员名，假设该雇员的工资低于  
2000。就给该雇员工资添加10%

create or replace procedure sp\_pro6(spName varchar2) is  
\--定义  
v\_sal emp.sal%type;  
begin  
\--运行  
select sal into v\_sal from emp where ename=spName;  
\--推断  
if v\_sal < 2000 then  
update emp set sal=sal+sal\*10 where ename = spName;  
end if;  
end;  
/

调用：  
exec sp\_pro6('scott');

二重条件分支：if - then - else  
编写一个过程，能够输入一个雇员名，假设该雇员的补助不是0，则工资加入100  
create or replace procedure sp\_pro6(spName varchar2) is  
\--定义  
v\_comm emp.comm%type;  
begin  
\--运行  
select comm into v\_comm from emp where ename=spName;  
\--推断  
if v\_comm <> 0 then  
update emp set comm=comm+100 where ename = spName;  
else  
update emp set comm=comm+200 where ename = spName;  
end if;  
end;  
/

\--编写过程。给不同职位的员工加入不同的工资

create or replace procedure sp\_pro6(spNo number) is  
v\_job emp.job%type;  
begin  
select job into v\_job from emp where empno=spNo;  
if v\_job = 'PRESIDENT' then  
update ...  
elsif  
update ...  
else  
update ...  
end if;  
end;

循环语句 -loop  
PL/SQL中的循环语句，最简单的循环语句是loop语句

\--编写过程，能够输入username，并循环加入10个用户到user表中。  
用户编号从1開始添加

create or replace procedure sp\_pro6(spName varchar2) is  
v\_num number := 1;  
begin  
loop  
insert into users values(v\_num,spName);  
\--推断是否要退出循环  
exit then v\_num=10; --等于10就要退出循环  
\--自增  
v\_num := v\_num+1;  
end loop;  
end;

exec sp\_pro6('你好')；

循环语句：while循环  
\--编写过程，可输入username，并循环加入10个用户到users表中。  
用户编号从11開始添加  
while v\_num <= 20 loop  
insert into user values(v\_num,spName);  
v\_num:=v\_num+1;  
end loop;  
end;

循环语句：for循环  
基本for循环的基本结构例如以下：  
begin  
for i in reverse 1 .. 10 loop  
insert into user values(i,'世阳')  
end loop;  
end;

goto语句和null语句  
goto end\_loop;

<<end\_loop>>

goto案例：  
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;  
/

if  
...  
else  
null;--表示什么都不做  
  
分页过程:  
无返回值的存储过程  
create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));

\--编写过程：  
\--in代表这是一个输入參数。默觉得in  
\--out:表示一个输出參数  
create or replace sp\_pro7(spBookId in number。spbookName in varchar2,sppublishHouse in varchar2) is

begin  
insert into book values(spBookId,spbookName,sppublishHouse);  
end;

编写java程序调用无返回值的过程  
//1.载入驱动  
Class.forName("oracle.jdbc.driver.OracleDriver");  
Connection ct=DriverManager.getConnection("jdbc:oracle:hin@127.0.0.1:1521","scott","tiger");

//2.创建CallableStatement  
CallableStatement cs = ct.prepareCall("\{call sp\_pro7(?,?

,?)\}");

//给？赋值  
cs.setInt(1,10);  
cs.setString(2,"笑傲江湖");  
cs.setString(3,"人民出版社");

//运行  
cs.execute();

编写有返回值的存储过程（非列表）  
create or replace procedure sp\_pro8  
(spno in number,spName out varchar2。spSal out number,spJob out varchar2)is  
begin  
select ename, sal, job into spName, spSal, spJob from emp where empno=spno;  
end;

java怎样获取有返回的存储过程的数据

//  
callableStatement cs = ct.prepareCall("\{call sp\_pro8(?,?

,?,?)\}");

//给第一个？赋值  
cs.setInt(1,7788);  
//给第二个？赋值  
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);  
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);  
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);  
//运行  
cs.execute();

//区返回值要注意？的顺序  
string name=cs.getString(2);  
double sal =cs.getDouble(3);  
string job=cs.getString(4);

编写一个过程，输入部门号，返回该部门全部雇员信息。此时用一般的參数是不能够的，须要使用package了，所以要分为两部分  
(1)创建一个包。例如以下：  
\--返回结果集的过程  
\--创建一个包，包中定义了一个游标，类型test\_cursor  
create or replace package testpackage as  
type test\_cursor is ref cursor;  
end testpackage;

（2）创建存储过程  
create or replace procedure sp\_pro9(spNo in number,p\_cursor out testpackage.test\_cursor) is  
begin  
open p\_cursor for select \* from emp where deptno = spNO;  
end;

java程序调用：  
//创建CallableStatement  
CallableStatement cs=ct.prepareCall("\{call sp\_pro9(?

)\}");

//给？赋值  
cs.SetInt(1,10);  
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

//运行  
cs.execute();

ResultSet rs = (ResultSet)cs.getObject(2);  
while(rs.next())  
\{  
System.out.println(rs.getInt(1)+" "+ra.getString(2));  
\}

编写分页过程  
输入表名。每页显示记录数。当前页。返回总记录数，总页数  
\--oracle的分页：  
select t1.\*,rownum rn from(select \* from emp) t1 where rownum<=10;

\--在分页时，大家能够把以下的sql语句当做一个模板使用  
select \* from (select t1.\*,rownum rn from(select \* from emp order by sal) t1 where rownum<=10) where rn>=6;

\--开发一个包  
create or replace package testpackage as  
type test\_cursor is ref cursor;  
end testpackage;

\--開始编写分页的过程  
create or replace procedure fenye(tableName in varchar2,  
pagesize in number,--一页显示多少条记录  
pageNow in number,--第几页  
myrows out number, --总记录数  
myPageCount out number,--总页数  
p\_cursor out testpackage.test\_cursor) is  
\--定义部分  
\--定义sql语句 字符串  
v\_sql varchar2(1024);  
\--定义两个整数  
v\_begin number := (pageNow-1)\*pagesize+1;  
v\_end number := pageNow\*pagesize;  
begin  
\--运行部分  
v\_sql := 'select \* from (select t1.\*,rownum rn from(select \* from '||tbaleName ||') t1 where rownum<='||v\_end ||') where rn>=' ||v\_begin;  
\--把游标和sql关联  
open p\_cursor for v\_sql;  
\--计算myrows和myPageCount  
\--组织一个sql  
v\_sql := 'select count(\*) from ' || tableName;  
\--运行sql。并把返回的值，赋给myrows  
execute immediate v\_sql into myrows;  
\--计算myPageCount  
if mod(myrows,Pagesize)=0 then  
myPageCount:=myrows/Pagesize;  
else  
myPageCount:=myrows/Pagesize+1  
end if;  
\--关闭游标  
\--close p\_cursor;  
end;  
/

java程序来验证分页过程显示的正确性  
//測试分页

//载入驱动  
Class.forName("oracle.jdbc.driver.OracleDriver");  
Connection ct=DriverManager.getConnection("...");

CallableStatement cs=ct.prepareCall("\{call fenye(?

,?,?,?

,?)\}");

cs.setString(1,"emp");  
cs.setInt(2,5);  
cs.setInt(3,1);

cs.registerOutParameter(4,orace.jdbc.OracleTypes.INTEGER);

cs.registerOutParameter(5,oracle.jdbc.OrcleTYpes.INTEGER);

cs.registerOutParameter(5,oracle.jdbc.OrcleTYpes.CURSOR);

cs.execute();

//获取总记录数/这里要注意。getInt(4),当中4。是由该參数的位置决定的

int rowNum = cs.getInt(4);  
int pageCount = cs.getINt(5);  
ResultSet rs = (ResultSet)cs.getObject(6);

while(rs.next())  
\{  
...  
\}

\--新的需求。依照薪水由低到高进行排序

PL/SQL的进阶 --例外处理  
例外的分类  
例外传递  
\--例外案例  
写一个块：  
declare  
\--定义  
v\_ename emp.ename%type;  
begin  
\--  
select ename into v\_name from emp where empno=&gno;  
dbms.output.put\_line(v\_ename);  
exception  
when no\_data\_found then  
dbms.output.put\_line('编号没有');  
end;

处理提前定义例外：  
PL/SQL提供了20过个提前定义的例外：

case\_no\_found

case when ... when ... end case

dup\_val\_on\_index  
在试图在不合法的游标上运行操作时，会触发该例外  
比如：试图从没有打开的游标提取数据，或是关闭没有打开的游标，则会  
触发该例外

invalid\_number  
当输入的数据有误时，会触发该例外

比方：

too\_many\_rows  
当运行select into语句的时候，假设返回超过了一行，则会触发该异常  
zero\_divide  
value\_error  
当运行赋值操作时，假设变量的长度不足以容纳实际数据  
处理自己定义例外  
提前定义例外和自己定义例外都是与oracle错误相关的。而且

\--自己定义例外  
create or replace procedure ex\_test(spNo number)  
is  
\--定义一个例外  
myex exception;  
begin  
\--更新用户sal  
update emp set sal=sal+1000 where empno=spNo;  
\--sql%notfound这里表示没有update  
\--raise myex;触发myex  
if sql%notfound then  
raise myex;  
end if;  
exception  
when myex then  
dbms\_output.put\_line('没有更新不论什么用户');  
end;  
/

exec ex\_test(56);  
oracle视图  
介绍：  
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包括一系列带有名称的列  
和行的数据。

可是，视图并不在数据库中以存储数据值集形式存在  
  
比如两张表 emp表和dept表  
1.假设要显示各个雇员的名字和他所在部门的名称，必须用两张表？  
2.假定管理员创建一个用户，如今仅仅希望该用户查询sal<1000的那些雇员？  
  
视图和表的差别：  
1.表须要占用磁盘空间，视图不须要  
2.视图没有索引，表有索引，所以视图查询较表速度慢  
3.使用视图能够简化复杂查询  
4.使用视图有利于提高安全性  
创建视图：  
\--把emp表的 sal<1000的雇员 映射到该视图（view）  
create view myView as select \* from emp where sal<1000;  
\--视图一旦创建成功，就能够当成一个普通表来使用  
\--为简化操作，用一个视图解决 显示雇员编号，姓名和部门名称。而且为可读视图  
create view myView1 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno with read only;  
  
注意：视图和视图之间能够做复杂联合查询  
  
改动视图：  
  
删除视图：