- 浏览: 40584 次
- 性别:
- 来自: 大连
文章分类
最新评论
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
execp_updateEmpSal('SCOTT'); |
二重条件分支If…then….else
编写一个过程,可以输入一个雇员名,如果该雇员的补助不是0就在原来的基础上增加100;如果补助为0就把补助设为200。
createorreplaceprocedurep_updateEmpSalAndComm |
多重条件分支If…then…elsif…else
编写一个过程,可以输入一个雇员的编号,如果该雇员的职位是president就给他的工资增加1000,如果该雇员的职位是MANAGER就给他的工资增加500,其它职位的雇员工资增加200.
createorreplaceprocedurep_updateSalBaseTitle |
循环语句
loop
是pl/sql中最简单的循环语句,这种循环语句以loop开头,以end loop结尾,这种循环至少会被执行一次。
案例:现有一张表users,表结构如下
用户id 用户名
1 zhangsan
请编写一个过程,可输入用户名,并循环添加10个用户到users表中,用户编号从1开始增加。
createorreplaceprocedurep_addUsers |
while
基本循环至少要执行循环体一次,而对于while循环来说,只有条件为true时,才会执行循环体语句,while循环以while…loop开始,以end loop结束。
案例:现有一张表users,表结构如下
用户id 用户名
1 zhangsan
请编写一个过程,可输入用户名,并循环添加10个用到users表中,用户编号从11开始增加。
createorreplaceprocedurep_addUsers2 |
For
for循环的基本结构如下:
createorreplaceprocedurep_addUsers3 |
我们可以看到控制变量I, 在隐含中就在不停的增加。
顺序控制语句
Goto语句
Goto语句用于跳转到特定标号去执行语句。注意由于使用goto语句会增加程序的复杂性,并使得应用程序可读性变差,所以在做一般应用开发时,建议不要使用goto语句。
基本语法如下goto lable是已经定义好的标号名。
declare |
null
null语句不会执行任何操作,并且直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。
declare |
编写分页过程
分页是任何一个网站(bbs,网上商城,blog)都会使用到的技术,因此学习pl/sql编程开发就一定要掌握该技术。
无返回值的存储过程
古人云:欲速则不达。为了让大家比较容易接受分页过程编写,我们还是从简单到复杂,循序渐进的给大家讲解。首先是掌握最简单的过程,无返回值的存储过程:
案例:现有一张表,表结构如下:
书号 |
书名 |
出版社 |
|
|
|
请编写一个过程,可以向book表添加书,要求通过java程序调用该过程。
createtablebook(
createorreplaceprocedurep_addBook |
在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 |
在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 |
在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 |
2)建立存储过程
createorreplaceprocedurep_getEmpsByDeptno |
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 |
可以当成一个模板来使用。
分页的存储过程
createorreplacepackagepagingPackageas |
分页的算法请参考第三天的内容。
在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 |
处理预定义例外
预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时,则会隐含的触发一个内部例外。Pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用 的例外。
Case_not_found
在开发pl/sql块中编写case语句时,如果when子句中没有包含必须的条件分支,就会触发case_not_found的例外:
createorreplaceprocedurep_proc6(vnonumber) |
Cursor_already_open
当重新打开已经打开的游标时,会隐含的触发例外cursor_already_open
DECLARE |
Dup_val_on_index
在唯一索引所对应的列上插入重复的值时,会隐含的触发例外dup_val_on_index例外
BEGIN |
Invalid_cursor
当试图在不合法的游标上执行操作时,会触发该例外
例如:试图从没有打开的游标提取数据,或是关闭没有打开的游标。则会触发访例外
DECLARE |
Invalid_number
当输入的数据有误时,会触发该例外
比如:数字100写成了1oo就会触发该例外。
BEGIN |
No_data_found
下面是一个pl/sql块,当执行select into没有返回行,就会触发该例外。
DECLARE |
Too_many_rows
当执行select into语句时,如果返回超过了一行,就会该例外。
DECLARE |
Zero_divide
当执行2/0语句时,则会触发该例外。
DECLARE |
Value_error
当执行赋值操作时,如果变量的长度不足以容纳实际数据,则会触发该例外value_error,
比如:
DECLARE |
其它预定义例外:
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 |
Oracle视图
视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。
假设以下情境:
1.如果要显示各个雇员的名字他所有部门的名称,必须用两张表。
2.假设管理员创建了一个用户,xiaohong,现在就希望xiaohong只可以查询sal<1000的哪些雇员。
createviewmyview1asselect*fromempwheresal < 1000; |
这时我们就可以用视图来解决。
视图与表的区别:
1.表需要占用磁盘空间,视图不需要。
2.视图不能添加索引。
3.使用视图可以简化复杂查询。比如:学生选课系统。
4.视图利于提高安全性。
比如:不同用户查看不同视图。
创建视图
createviewmyviewas |
创建或修改视图
createorreplaceviewmyviewas |
删除视图
dropviewmyview; |
相关推荐
韩顺平老师 oracle 教程笔记 1.Oracle 认证,与其它数据库比较,安装 2.Oracle 的基本使用--基本命令 ...19.pl/sql 的进阶--控制结构(分支,循环,控制) 20.PL/SQL 分页 21.例外处理 22.oracle 的视图
(7)PL/SQL进阶分页过程 62 (8)PL/SQL进阶例外 62 (9)视图 62 22.数据库管理+表的逻辑备份与恢复 63 23.数据字典和动态性能视图 67 24.oracle的卸载 69 25.尚学堂SQL简单讲解 70 Oracle常用函数 73 (1)trunc(for date...
1.掌握oracle表的管理(创建/维护) ...16.掌握pl/sql的高级用法(能编写分页过程模块,下订单过程模块..,) 17.会处理oracle常见的例外 18.会编写oracle各种触发器 19.理解视图的概念并能灵活使用视图
-- 首先,以超级管理员的身份登录oracle sqlplus sys/bjsxt as sysdba --然后,解除对scott用户的锁 alter user scott account unlock; --那么这个用户名就能使用了。 --(默认全局数据库名orcl) 1、...
日期类型 date 7字节 用于存储表中的日期和时间数据,取值范围是公元前4712年1月1日至公元9999年12月31日,7个字节分别表示世纪、年、月、日、时、分和秒 二进制数据类型 row 1~2000字节 可变长二进制数据,在具体...
一、SQL&PL SQL 1.怎么样大批量的更新数据而不影响正常业务 2.怎么对IN子查询使用绑定变量 3.并发容易出现的问题与并发控制 4.怎么使用object与record类型返回表类型数据 5.怎么样在业务繁忙时期正确的创建表约束...
第7章 系统优化方法 85 §6.1 何时优化效率最高 85 §6.1.1 系统设计阶段和开发阶段的优化 85 §6.1.2 改善产品系统的优化 85 §6.2 优化的优先步骤 86 §6.2.1 步骤1:优化商业规则 86 §6.2.2 步骤2:优化数据设计...
sql> create user juncky identified by oracle default tablespace users sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default]; ...
Oracle中PL/SQL单行函数和组函数详解 mssql+oracle Oracle编程的编码规范及命名规则 Oracle数据库字典介绍 0RACLE的字段类型 事务 CMT DEMO(容器管理事务演示) 事务隔离性的一些基础知识 在组件之间实现事务和异步...
35、MySQL、SqlServer、oracle写出字符存储、字符串转时间 52 36、update语句可以修改结果集中的数据吗? 53 37、oracle如何设置主键自动增长? 53 38、表连接、子查询的区别是什么?它们可以相互转化吗?你倾向于用...
第7章 字符的处理与正则表达式 102 7.1 字符类型的特殊性 102 7.2 字符的显示与格式化 102 7.2.1 字符的显示 102 7.2.2 字符的格式化 103 7.3 常见的操作 104 7.3.1 字符串重复操作——str_repeat 104 7.3.2 字符串...