《调用oracle存储过程(详解)精编版》由会员分享，可在线阅读，更多相关《调用oracle存储过程(详解)精编版（35页珍藏版）》请在金锄头文库上搜索。
1、第7讲PL SQL块的结构和实例 PL SQL块的结构和实例介绍 学习目标理解oracle的pl sql概念掌握pl sql编程技术 包括编写过程 函数 触发器 pl sql的介绍pl sql是什么pl sql procedurallanguage sql 是oracle在标准的sql语言上的扩展 pl sql不仅允许嵌入sql语言 还可以定义变量和常量 允许使用条件语句和循环语句 允许使用例外处理各种错误 这样使得它的功能变得更加强大 PL SQL块的结构和实例 学习必要性1 提高应用程序的运行性能2 模块化的设计思想 分页的过程 订单的过程 转账的过程 3 减少网络传输量4 提高安全性 s
2、ql会包括表名 有时还可能有密码 传输的时候会泄露 PL SQL就不会 为什么PL SQL会快呢 不好的地方 移植性不好 换数据库就用不了 PL SQL块的结构和实例 sqlplus开发工具sqlplus是oracle公司提供的一个工具 这个因为我们在以前介绍过的 举一个简单的案例 编写一个存储过程 该过程可以向某表中添加记录 一个简单的存储过程CREATEORREPLACEPROCEDUREproc helloworldISBEGINDBMS OUTPUT put line HelloWorld END PL SQL块的结构和实例 需要在屏幕上显示出 DBMS OUTPUT put line
3、 的输出字符串 需要做一个小小的设置SQL showserveroutputserveroutputOFFSQL setserveroutputonSQL showserveroutputONSIZE10000FORMATWORD WRAPPED1 创建一个简单的表Sql代码1 createtablemytest namevarchar2 30 passwdvarchar2 30 PL SQL块的结构和实例 2 创建过程Sql代码1 createorreplaceproceduresp pro1is2 begin 执行部分3 insertintomytestvalues 杨世顺 m1234 4
4、 end 5 replace 表示如果有sp pro1 就替换如何查看错误信息 showerror 如何调用该过程 1 exec过程名 参数值1 参数值2 SQL执行2 call过程名 参数值1 参数值2 程序调用 PL SQL块的结构和实例 pl sqlndeveloper开发工具pl sqldeveloper是用于开发pl sql块的集成开发环境 ide 它是一个独立的产品 而不是oracle的一个附带品 举一个简单案例 编写一个存储过程 该过程可以删除某表记录 Sql代码1 createorreplaceproceduresp pro2is2 begin 执行部分3 deletefrom
5、mytestwherename 杨世顺 4 end PL SQL块的结构和实例 pl sql基础pl sql介绍开发人员使用pl sql编写应用模块时 不仅需要掌握sql语句的编写方法 还要掌握pl sql语句及语法规则 pl sql编程可以使用变量和逻辑控制语句 从而可以编写非常有用的功能模块 比如 分页存储过程模块 订单处理存储过程模块 转账存储过程模块 而且如果使用pl sql编程 我们可以轻松地完成非常复杂的查询要求 pl sql可以做什么简单分类过程 存储过程 函数块 编程 触发器包 PL SQL块的结构和实例 编写规范编写规范n1 注释单行注释 Sql代码1 select from
6、empwhereempno 7788 取得员工信息多行注释 来划分2 标志符号的命名规范1 当定义变量时 建议用v 作为前缀v sal2 当定义常量时 建议用c 作为前缀c rate3 当定义游标时 建议用 cursor作为后缀emp cursor4 当定义例外时 建议用e 作为前缀e error PL SQL块的结构和实例 pl sql块介绍介绍n块 block 是pl sql的基本程序单元 编写pl sql程序实际上就是编写pl sql块 要完成相对简单的应用功能 可能只需要编写一个pl sql块 但是如果想要实现复杂的功能 可能需要在一个pl sql块中嵌套其它的pl sql块 块n结构
7、示意图pl sql块由三个部分构成 定义部分 执行部分 例外处理部分 如下所示 declare 定义部分 定义常量 变量 游标 例外 复杂数据类型 begin 执行部分 要执行的pl sql语句和sql语句 exception 例外处理部分 处理运行的各种错误 end 定义部分是从declare开始的 该部分是可选的 执行部分是从begin开始的 该部分是必须的 例外处理部分是从exception开始的 该部分是可选的 可以和java编程结构做一个简单的比较 PL SQL块的结构和实例 pl sql块的实例 1 实例1 只包括执行部分的pl sql块nSql代码1 setserveroutpu
8、ton 打开输出选项2 begin3 dbms output put line hello 4 end 相关说明 dbms output是oracle所提供的包 类似java的开发包 该包包含一些过程 put line就是dbms output包的一个过程 PL SQL块的结构和实例 pl sql块的实例 2 实例2 包含定义部分和执行部分的pl sql块nSql代码1 declare2 v enamevarchar2 5 定义字符串变量3 begin4 selectenameintov enamefromempwhereempno 相关说明 表示要接收从控制台输入的变量 PL SQL块的结构
9、和实例 pl sql块的实例 3 实例3 包含定义部分 执行部分和例外处理部分为了避免pl sql程序的运行错误 提高pl sql的健壮性 应该对可能的错误进行处理 这个很有必要 1 比如在实例2中 如果输入了不存在的雇员号 应当做例外处理 2 有时出现异常 希望用另外的逻辑处理我们看看如何完成1的要求 相关说明 oracle事先预定义了一些例外 no data found就是找不到数据的例外 Sql代码1 declare2 定义变量3 v enamevarchar2 5 4 v salnumber 7 2 5 begin6 执行部分7 selectename salintov ename v
10、 salfromempwhereempno 15 PL SQL块的结构和实例 pl sql分类 过程 函数 包 触发器过程过程用于执行特定的操作 当建立过程时 既可以指定输入参数 in 也可以指定输出参数 out 通过在过程中使用输入参数 可以将数据传递到执行部分 通过使用输出参数 可以将执行部分的数据传递到应用环境 在sqlplus中可以使用createprocedure命令来建立过程 PL SQL块的结构和实例 实例如下 1 请考虑编写一个过程 可以输入雇员名 新工资 可修改雇员的工资2 如何调用过程有两种方法 execcall3 如何在java程序中调用一个存储过程问题 如何使用过程返回
11、值 特别说明 对于过程我们会在以后给大家详细具体的介绍 现在请大家先有一个概念 createproceduresp pro3 spNamevarchar2 newSalnumber is 不要写成number 3 2 表明类型就可以了 不需要大小 就好像Java写方法时的参数一样Sql代码1 begin2 执行部分 根据用户名去修改工资3 updateempsetsal newSalwhereename spName 4 end 5 PL SQL块的结构和实例 java程序中调用一个存储过程 演示java程序去调用oracle的存储过程案例5 try 6 1 加载驱动7 Class forNa
12、me oracle jdbc driver OracleDriver 8 2 得到连接9 Connectionct DriverManager getConnection jdbc oracle thin 127 0 0 1 1521 MYORA1 scott m123 11 3 创建CallableStatement12 CallableStatementcs ct prepareCall callsp pr3 13 4 给 赋值14 cs setString 1 SMITH 15 cs setInt 2 10 17 cs execute 18 关闭19 cs close 20 ct clo
13、se 21 catch Exceptione 22 e printStackTrace 23 24 25 PL SQL块的结构和实例 1 使用各种if语句2 使用循环语句3 使用控制语句 goto和null 条件分支语句pl sql中提供了三种条件分支语句if then if then else if then elsif then这里我们可以和java语句进行一个比较简单的条件判断if thenn问题 编写一个过程 可以输入一个雇员名 如果该雇员的工资低于2000 就给该员工工资增加10 Sql代码1 createorreplaceproceduresp pro6 spNamevarchar
14、2 is2 定义3 v salemp sal type 4 begin5 执行6 selectsalintov salfromempwhereename spName 7 判断8 ifv sal 2000then9 updateempsetsal sal sal 10 whereename spName 10 endif 11 end 12 PL SQL块的结构和实例 二重条件分支if then else问题 编写一个过程 可以输入一个雇员名 如果该雇员的补助不是0就在原来的基础上增加100 如果补助为0就把补助设为200 Sql代码1 createorreplaceproceduresp p
15、ro6 spNamevarchar2 is2 定义3 v m type 4 begin5 执行6 selectcommintov commfromempwhereename spName 7 判断8 ifv comm0then9 updateempsetcomm comm 100whereename spName 10 else11 updateempsetcomm comm 200whereename spName 12 endif 13 end 14 PL SQL块的结构和实例 多重条件分支if then elsif thenn问题 编写一个过程 可以输入一个雇员编号 如果该雇员的职位是P
16、RESIDENT就给他的工资增加1000 如果该雇员的职位是MANAGER就给他的工资增加500 其它职位的雇员工资增加200 Sql代码1 createorreplaceproceduresp pro6 spNonumber is2 定义3 v jobemp job type 4 begin5 执行6 selectjobintov jobfromempwhereempno spNo 7 ifv job PRESIDENT then8 updateempsetsal sal 1000whereempno spNo 9 elsifv job MANAGER then10 updateempsetsal sal 500whereempno spNo 11 else12 updateempsetsal sal 200whereempno spNo 13 endif 14 end 15 16 PL SQL块的结构和实例 循环语句 loop是pl sql中最简单的循环语句 这种循环语句以loop开头 以endloop结尾 这种循环至少会被执行一次 案例 现有一张表users 表结构如下 用户id
