oralce实战第七天--pl/sql的进阶、分页存储过程、例外和视图

内容介绍

1.pl/sql的进阶

2.oracle的视图

3.oracle的触发器

期望目标

1.掌握pl/sql的高级用法（能编写分页过程模块，下订单过程模块…）。

2.会处理oracle常见的例外。

3.会编写oracle各种触发器。

4.理解视图的概念并能灵活使用视图。

pl/sql的进阶

控制结构

在任何计算机语言（c,java,pascal）都有各种控制语句（条件语句，循环语句，顺序控制结构…）在pl/sql中也存在这样的控制结构。

条件分支语句

Plsql中提供了三种条件分支语句

If…then

If…then….else

If…then…elsif…else

简单的条件判断If…then

编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给雇员工资增加10%。

createorreplaceprocedurep_updateEmpSal (v_enamevarchar2) is v_sal emp.sal%type; begin selectsalintov_sal fromemp whereename= v_ename; ifv_sal < 2000then updateemp setsal = sal * 1.1 whereename = v_ename; endif; end; execp_updateEmpSal('SCOTT');

二重条件分支If…then….else

编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100；如果补助为0就把补助设为200。

createorreplaceprocedurep_updateEmpSalAndComm (v_enamevarchar2) is v_comm emp.comm%type; begin selectcommintov_comm fromemp whereename= v_ename; ifv_comm <> 0then updateemp setcomm = comm + 100 whereename = v_ename; else updateemp setcomm = comm + 200 whereename = v_ename; endif; end;

多重条件分支If…then…elsif…else

编写一个过程，可以输入一个雇员的编号，如果该雇员的职位是president就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其它职位的雇员工资增加200.

createorreplaceprocedurep_updateSalBaseTitle (v_empnonumber) is v_job emp.job%type; begin selectjobintov_job fromemp whereempno = v_empno; ifv_job = 'PRESIDENT'then updateempsetsal=sal + 1000whereempno = v_empno; elsifv_job = 'MANAGER'then updateempsetsal=sal + 500whereempno = v_empno; else updateempsetsal=sal + 200whereempno = v_empno; endif; end;

循环语句

loop

是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。

案例：现有一张表users，表结构如下

用户id 用户名

1 zhangsan

请编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加。

createorreplaceprocedurep_addUsers (v_namevarchar2) is v_numnumber:= 1; begin loop insertintousersvalues(v_num,v_name); exitwhenv_num = 10; v_num := v_num + 1; endloop; end;

while

基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while…loop开始，以end loop结束。

案例：现有一张表users,表结构如下

用户id 用户名

1 zhangsan

请编写一个过程，可输入用户名，并循环添加10个用到users表中，用户编号从11开始增加。

createorreplaceprocedurep_addUsers2 (v_namevarchar2) is v_numnumber:= 11; begin whilev_num <= 20 loop insertintousersvalues(v_num,v_name); v_num := v_num + 1; endloop; end;

For

for循环的基本结构如下：

createorreplaceprocedurep_addUsers3 (v_namevarchar2) is begin foriinreverse1..10loop insertintousersvalues(i,v_name); endloop; end;

我们可以看到控制变量I, 在隐含中就在不停的增加。

顺序控制语句

Goto语句

Goto语句用于跳转到特定标号去执行语句。注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议不要使用goto语句。

基本语法如下goto lable是已经定义好的标号名。

declare iint:= 1; begin loop dbms_output.put_line('i = ' || i); ifi=10then gotoend_loop; endif; i := i + 1; endloop; <<end_loop>> dbms_output.put_line('loop end'); end;

null

null语句不会执行任何操作，并且直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。

declare v_sal emp.sal%type; v_ename emp.ename%type; begin selectename,salintov_ename,v_sal fromemp whereempno = &no; ifv_sal < 3000then updateemp setcomm = sal * 0.1 whereename = v_ename; else null; endif; end;

编写分页过程

分页是任何一个网站（bbs,网上商城，blog）都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。

无返回值的存储过程

古人云：欲速则不达。为了让大家比较容易接受分页过程编写，我们还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的过程，无返回值的存储过程：

案例：现有一张表，表结构如下：

书号	书名	出版社

请编写一个过程，可以向book表添加书，要求通过java程序调用该过程。

createtablebook( bookIdnumber, bookNamevarchar2(50), pulishHousevarchar2(50) ); createorreplaceprocedurep_addBook ( vBookIdinnumber, vBookNameinvarchar2, vPublishHouseinvarchar2 ) is begin insertintobook values ( vBookId, vBookName, vPublishHouse ); end;

在java中调用：

packagecom.anllin.jdbc.oracle; importjava.sql.*; publicclassNoReturnValProc { publicstaticvoidmain(String[] args) { Connection conn =null; CallableStatement stmt =null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager .getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myorcl", "scott", "tiger"); stmt = conn.prepareCall("{call p_addBook(?,?,?)}"); stmt.setInt(1, 1); stmt.setString(2, "水是最好的药"); stmt.setString(3, "人民出版社"); stmt.execute(); } catch(Exception e) { thrownewRuntimeException(e); } finally { try { if(null!= stmt) stmt.close(); if(null!= conn) conn.close(); } catch(Exception ex) { thrownewRuntimeException(ex); } } } }

有返回值的存储过程（非列表）

再看如何处理有返回值的存储过程：

案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。

createorreplaceproceduresp_getEnameByEmpno (spnoinnumber,spNameoutvarchar2) is begin selectenameintospName fromemp whereempno = spno; end;

在java中调用

packagecom.anllin.jdbc.oracle; importjava.sql.*; publicclassHaveReturnValProc { publicstaticvoidmain(String[] args) { Connection conn =null; CallableStatement stmt =null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myorcl","scott","tiger"); stmt = conn.prepareCall("{call sp_getEnameByEmpno(?,?)}"); stmt.setInt(1,7788); stmt.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); stmt.execute(); String name = stmt.getString(2); System.out.println("7788的名字是："+ name); } catch(Exception e) { thrownewRuntimeException(e); } finally { try { if(null!= stmt) stmt.close(); if(null!= conn) conn.close(); } catch(Exception e) { thrownewRuntimeException(e); } } } }

案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名，工资和岗位。

createorreplaceproceduresp_getEmpMsgByEmpno ( vNoinnumber, vNameoutvarchar2, vSaloutnumber, vJoboutvarchar2 ) is begin selectename,sal,jobintovName,vSal,vJob fromemp whereempno = spno; end;

在java中调用

packagecom.anllin.jdbc.oracle; importjava.sql.CallableStatement; importjava.sql.Connection; importjava.sql.DriverManager; publicclassHaveReturnValProc2 { publicstaticvoidmain(String[] args) { Connection conn =null; CallableStatement stmt =null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager .getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myorcl", "scott", "tiger"); stmt = conn.prepareCall("{call sp_getEmpMsgByEmpno(?,?,?,?)}"); stmt.setInt(1, 7788); stmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); stmt.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE); stmt.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR); stmt.execute(); String name = stmt.getString(2); doublesal = stmt.getDouble(3); String job = stmt.getString(4); System.out.println("7788的名字是：" + name + ",工资是：" + sal + ",职位是：" + job); } catch(Exception e) { thrownewRuntimeException(e); } finally { try { if(null!= stmt) stmt.close(); if(null!= conn) conn.close(); } catch(Exception e) { thrownewRuntimeException(e); } } } }

有返回值的存储过程（列表[结果集]）

案例：编写一个过程，输入部门号，返回该部门所有雇员信息。

对该题分析如下：

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用package了，所以要分两部分：

1）建一个包，如下：

createorreplacepackagetestpackageas typetest_cursorisrefcursor; endtestpackage;

2）建立存储过程

createorreplaceprocedurep_getEmpsByDeptno ( vNOinnumber, p_cursorouttestpackage.test_cursor ) is begin openp_cursorfor select*fromemp wheredeptno = vNO; end;

3）在java中调用

packagecom.anllin.jdbc.oracle; importjava.sql.*; publicclassHaveReturnValProc3 { publicstaticvoidmain(String[] args) { Connection conn =null; CallableStatement stmt =null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn =DriverManager .getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myorcl", "scott", "tiger"); stmt = conn.prepareCall("{call sp_getEmpsByDeptno(?,?)}"); stmt.setInt(1, 20); stmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); stmt.execute(); ResultSet rs = (ResultSet) stmt.getObject(2); while(rs.next()) { System.out.println(rs.getInt(1) + " " + rs.getString(2)); } } catch(Exception e) { thrownewRuntimeException(e); } finally { try { if(null!= stmt) stmt.close(); if(null!= conn) conn.close(); } catch(Exception e) { thrownewRuntimeException(e); } } } }

编写存储过程

有了上面的基础，相信大家可以完成分页存储过程了。

要求，请编写一个存储过程，要求可以输入表名，每页显示记录数，当前页。返回总记录数，总页数和返回的结果集。

分页的写法：

select*from ( selectt.*,rownumrn from(select*fromemp) t whererownum<= 10 ) wherern >= 6;

可以当成一个模板来使用。

分页的存储过程

createorreplacepackagepagingPackageas typepaging_cursorisrefcursor; endpagingPackage; createorreplaceprocedurepagingProcedure ( tableNameinvarchar2, pageSizeinnumber, pageNowinnumber, rowsCountoutnumber, pageCountoutnumber, p_cursoroutpagingPackage.paging_cursor ) is v_sqlvarchar2(1000); v_beginnumber:= (pageNow - 1) *pageSize+ 1; v_endnumber:= pageNow *pageSize; begin v_sql := 'select * from ( select t.*,rownum rn from (select * from '||tableName||') t where rownum <= '|| v_end ||') where rn >= '|| v_begin; openp_cursorforv_sql; v_sql := 'select count(*) from '||tableName; executeimmediatev_sqlintorowsCount; ifmod(rowsCount,pageSize) = 0then pageCount := rowsCount/pageSize; else pageCount := rowsCount/pageSize+ 1; endif; closep_cursor; end;

分页的算法请参考第三天的内容。

在java中调用

packagecom.anllin.jdbc.oracle; importjava.sql.*; importoracle.jdbc.*; publicclassPaging { publicstaticvoidmain(String[] args) { Connection conn =null; CallableStatement stmt =null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager .getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger"); stmt = conn.prepareCall("{call pagingProcedure(?,?,?,?,?,?)}"); stmt.setString(1, "emp"); stmt.setInt(2, 5); stmt.setInt(3, 1); stmt.registerOutParameter(4, OracleTypes.INTEGER); stmt.registerOutParameter(5, OracleTypes.INTEGER); stmt.registerOutParameter(6, OracleTypes.CURSOR); stmt.execute(); introwCount = stmt.getInt(4); intpageCount = stmt.getInt(5); ResultSet rs = (ResultSet) stmt.getObject(6); System.out.println("总记录数为：" + rowCount); System.out.println("总页数为：" + pageCount); while(rs.next()) { System.out.println("员工编号：" + rs.getInt(1) + "　姓名：" + rs.getString(2) + " 工资：" + rs.getFloat(6)); } } catch(Exception e) { thrownewRuntimeException(e); } finally { try { if(null!= stmt) stmt.close(); if(null!= conn) conn.close(); } catch(Exception ex) { thrownewRuntimeException(ex); } } } }

例外处理

例外的分类

Oracle将例外分为预定义例外，非预定义例外和自定义例外三种。

预定义例外用于处理常见的oracle错误。

非预定义例外用于处理预定义例外不能处理的例外。

自定义例外用于处理与oracle错误无关的其它情况。

例外处理

例外传递

如果不处理例外我们看看会出现什么情况：

案例：编写一个过程，可接收雇员的编号，并显示该雇员的姓名。

问题是，如果输入的雇员的编号不存在，怎样去处理呢?

declare v_ename emp.ename%type; begin selectenameintov_enamefromempwhereempno=&no; dbms_output.put_line('emp name : '|| v_ename); exception whenno_data_foundthen dbms_output.put_line('empno not exist.'); end;

处理预定义例外

预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时，则会隐含的触发一个内部例外。Pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用 的例外。

Case_not_found

在开发pl/sql块中编写case语句时，如果when子句中没有包含必须的条件分支，就会触发case_not_found的例外：

createorreplaceprocedurep_proc6(vnonumber) is v_sal emp.sal%type; begin selectsalintov_sal fromemp whereempno= vno; case whenv_sal < 1000then updateemp setsal = sal + 100 whereempno = vno; whenv_sal < 2000then updateemp setsal = sal + 200 whereempno = vno; endcase; exception whencase_not_foundthen dbms_output.put_line('case statement no match condition for '||v_sal); end;

Cursor_already_open

当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open

DECLARE CURSORemp_cursorIS SELECTename, sal FROM emp; emp_record emp_cursor%ROWTYPE; BEGIN OPENemp_cursor; FORemp_recordINemp_cursor LOOPdbms_output.put_line(emp_record.ename); ENDLOOP; EXCEPTION WHENcursor_already_openTHEN dbms_output.put_line('cursor already open'); END;

Dup_val_on_index

在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup_val_on_index例外

BEGIN INSERTinfodept VALUES (10, 'PublicRelations', 'beijing'); EXCEPTION WHENdup_val_on_indexTHEN dbms_output.put_line('the column of deptno can not appear repeat value'); END;

Invalid_cursor

当试图在不合法的游标上执行操作时，会触发该例外

例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发访例外

DECLARE CURSORemp_cursorIS SELECTename, sal FROM emp; emp_record emp_cursor%ROWTYPE; BEGIN --OPEN emp_cursor; FETCHemp_cursor INTOemp_record; dbms_output.put_line(emp_record.ename); CLOSEemp_cursor; EXCEPTION WHENinvalid_cursorTHEN dbms_output.put_line('please check cursor is already open?'); END;

Invalid_number

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

比如：数字100写成了1oo就会触发该例外。

BEGIN UPDATEempSETsal = sal + '1oo'; EXCEPTION WHENinvalid_numberTHEN dbms_output.put_line('input number is invalid.'); END;

No_data_found

下面是一个pl/sql块，当执行select into没有返回行，就会触发该例外。

DECLARE v_sal emp.sal%TYPE; BEGIN SELECTsalINTOv_salFROMempWHEREename = '&name'; EXCEPTION WHENno_data_foundTHEN dbms_output.put_line('the emp no exist.'); END;

Too_many_rows

当执行select into语句时，如果返回超过了一行，就会该例外。

DECLARE v_ename emp.ename%TYPE; BEGIN SELECTenameINTOv_enameFROMemp; EXCEPTION WHENtoo_many_rowsTHEN dbms_output.put_line('return too many rows.'); END;

Zero_divide

当执行2/0语句时，则会触发该例外。

DECLARE v_numNUMBER; BEGIN v_num := 2 / 0; EXCEPTION WHENzero_divideTHEN dbms_output.put_line('can not divide zero'); END;

Value_error

当执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error,

比如：

DECLARE v_enameVARCHAR2(5); BEGIN SELECTenameINTOv_enameFROMempWHEREempno = &no; EXCEPTION WHENvalue_errorTHEN dbms_output.put_line('variable is not enough size to store value'); END;

其它预定义例外：

1）login_denide

当用户非法登陆时，会触发该例外。

1)Not_logged_on

如果用户没有登陆就执行dml操作，就会触发该例外。

2)Storage_error

如果超出了内存空间或是内存被损坏，就会触发该例外。

3)Timeout_on_resource

如果oracle在等待资源时，出现了超时就会触发该例外。

非预定义例外

非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle错误，而当使用pl/sql开发应用程序时，可能会遇到其他的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等，在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外的不多，这里就不举例了。

处理自定义例外

预定义例外和自定义例外都是oracle错误相关的，并且出现的oracle错误会隐含的触发相应的例外；而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外。

请编写一个pl/sql块，接收一个雇员的编号，并给雇员工资增加1000元，如果该雇员不存在，请提示。

CREATEORREPLACEPROCEDUREex_test(vnoNUMBER)IS --definition exception myexEXCEPTION; BEGIN UPDATEempSETsal = sal + 100WHEREempno = vno; --sql%notfound -- denotation not update IFSQL%NOTFOUND THEN RAISEmyex; ENDIF; EXCEPTION WHENmyexTHEN dbms_output.put_line('no emp to update'); END;

Oracle视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

假设以下情境：

1.如果要显示各个雇员的名字他所有部门的名称，必须用两张表。

2.假设管理员创建了一个用户，xiaohong，现在就希望xiaohong只可以查询sal<1000的哪些雇员。

createviewmyview1asselect*fromempwheresal < 1000;

这时我们就可以用视图来解决。

视图与表的区别：

1.表需要占用磁盘空间，视图不需要。

2.视图不能添加索引。

3.使用视图可以简化复杂查询。比如：学生选课系统。

4.视图利于提高安全性。

比如：不同用户查看不同视图。

创建视图

createviewmyviewas selectemp.ename,emp.job,dept.deptnofromemp,dept whereemp.deptno = dept.deptno withreadonly;

创建或修改视图

createorreplaceviewmyviewas selectemp.ename,emp.job,dept.deptnofromemp,dept whereemp.deptno = dept.deptno withreadonly;

删除视图

dropviewmyview;