oracle存储过程.ppt

Oracle存储过程 什么是存储过程 为啥要用存储过程 存储过程 存储过程可以说是一个记录集 它是由一些T SQL Transact SQL SQL程式设计语言的增强版 T SQL包含了SQL 如select fromTABLE这既是一个T SQL又是SQL语句 例如BEGIN DBMS OUTPUT 1111 END这属于T SQL语句不是SQL语句 语句组成的代码块 这些T SQL语句代码像一个方法一样实现一些功能 如对单表或多表的增删改查 然后再给这个代码块取一个名字 在用到这个功能的时候调用对应名称即可 存储过程优点 1 速度上 由于数据库执行动作时 是先编译在执行 然而存储过程属于已编译过的代码块 所以执行效率比SQL执行效率快 2 当对数据库进行复杂操作时 如对多个表进行UPDATE INSERT QUERY DELETE时 可将这些复杂操作改用存储过程封装起来之后节后数据库提供的事务处理结合一起使用 这些操作 如果用程序来完成 就变成了一条条的SQL语句 可能要多次连接数据库 而换成存储 只需要连接一次数据库就可以了 3 存储过程可以重复使用 可减少数据库开发人员的工作量 如果内容修改后 可直接重新编译即可使用 相对于同功能的后台实现 可以减少服务器重启等 存储过程优点 3 安全性高 可设定只有某此用户才具有对指定存储过程的使用权 4 使用存储过程 可以实现存储过程设计和编码工作分开进行 只要将存储过程名 参数 及返回信息告诉编码人员即可 存储过程的语法结构 结构如下 createorreplaceprocedure存储过程名称 参数1in out 类型 as is 声明语句段 如声明存储过程中所要使用到的局部参数变量 以及游标的声明等等 begin执行语句段 具体的业务逻辑 exception异常处理语句段 end存储过程名称 存储过程的参数 参数说明 存储过程的参数类型 char varchar2 number Integer Date 以及oracle的其他数据类型 存储过程中的变量声明 第一种 直接声明如 字段名类型名 长度 fieldnamevarchar2 32 第二种 根据某张表的表字段类型声明 字段名表名称 字段名 typefieldnamestudent sno type 参数过程的调用执行 PLSQL中调用存储过程的语句命令窗口模式中的调用执行 exec存储过程名称 参数1 2 注 调用的过程中 不论有没有参数 其 可有可无 存储过程中调用另一个存储过程存储过程名称 注 调用的过程中 不论有没有参数 其 是必不可少的 后台方法的调用存储过程无参存储过程调用 callprocedure name 有输入参数的调用 callprocedure name 创建过程实例 创建名为stu proc的过程 create是创建过程的标识符 replace表示若同名过程存在将覆盖原过程 该过程定义了一个变量 其类型和student数据表中的sname字段类型相同 都是字符型 将数据表中的sno字段为1的sname字段内容送入变量中 然后输出结果 表结构 createtablestudent guidvarchar2 32 primarykey snonumber 6 snamevarchar2 32 pnonumber 6 altertablestudentmodifyguiddefaultsys guid 参数过程实例 无参数存储过程 包含两种方式的变量声明 createorreplaceprocedurestu procas is 变量的声明区pnamevarchar2 25 beginselectsnameintopnamefromstudentwheresno 1 dbms output put line pname end 或createorreplaceprocedurestu procaspnamestudent sname type beginselectsnameintop namefromstudentwheresno 1 dbms output put line pname end 仅有输入参数的过程createorreplaceprocedurestu proc1 pnoinstudent sno type pno1invarchar2 aspnamevarchar2 25 beginselectsnameintopnamefromstudentwheresno pno dbms output put line pname pno1 end 存储过程 仅有输出参数的存储过程createorreplaceprocedurestu proc2 pnameoutstudent sname type asbeginselectsnameintopnamefromstudentwheresno 1 dbms output put line pname end 此种存储过程不能直接用call来调用 这种情况的调用将在下面oracle函数调用中说明 参数过程实例 有输入 输出参数的存储过程 createorreplaceprocedurestu proc3 pnoinstudent sno type pnameoutstudent sname type asbeginselectsnameintopnamefromstudentwheresno pno dbms output put line pname end 此种存储过程不能直接用call来调用 这种情况的调用将在下面oracle函数调用中说明 参数过程实例 Oracle函数调用存储过程 我们已经学习了oracle函数 下面就针对参数的4种情况分别举出实例说明函数对存储过程的调用 对无参数过程的调用 函数声明createorreplacefunctionget pnamereturnvarchar2ispnamevarchar2 20 beginstu proc selectsnameintopnamefromstudentwheresno 1 returnpname end 测试窗口中的调用 declarebegindbms output put line 在PL SQL中打印的结果 get pname end 函数调用存储过程实例 对有输入参数过程的调用 createorreplacefunctionget pname1 pnoinnumber returnvarchar2ispnamevarchar2 20 beginstu proc1 pnoinstudent sno type selectsnameintopnamefromstudentwheresno pno returnpname end 调用declarebegindbms output put line 在PL SQL中打印的结果 get pname1 2 end 函数调用存储过程实例 对有输出参数过程的调用 createorreplacefunctionget pname2 pnameoutvarchar2 returnvarchar2isbeginstu proc2 pnameoutstudent sname type returnpname end 调用declarepnamestudent sname type begindbms output put line 在PL SQL中打印的结果 get pname2 pname end 函数调用存储过程实例 对有输入 输出参数过程的调用 createorreplacefunctionget pname3 pnoinnumber pnameoutvarchar2 returnvarchar2isbeginstu proc3 pnoinstudent sno type pnameoutstudent sname type returnpname end 调用declarepnamestudent sname type begindbms output put line 在PL SQL中打印的结果 get pname3 2 pname end 函数调用存储过程实例 JAVA调用数据库存储过程 前面我们已经讲述了有关oracle数据库的存储过程的几种形式 以及oracle函数对存储过程的调用 下面我将根据上面存储过程的实例来举出JAVA对oracle存储过程的调用 无参数过程 DbOperoper DbOper getdbOper Stringsql callstu proc oper executeprocedure sql JAVA调用实例 仅有返回值的过程publicvoidno paramsCall DbOperoper DbOper getdbOper Stringsql callstu proc oper executeprocedure sql JAVA调用实例 既有输入参数又有输出参数的过程DbOperoper DbOper getdbOper Stringsql callstu proc3 Object params 1 222 Stringstr oper executeprocedure inAndOut sql params returnstr JAVA调用实例 游标 什么是游标 从表中检索出结果集 从中每次指向一条记录进行交互的机制游标从概念上讲基于数据库的表返回结果集游标有什么作用 指定结果集中特定行的位置 基于当前的结果集位置检索一行或连续的几行 在结果集的当前位置修改行中的数据 对其他用户所做的数据更改定义不同的敏感性级别 可以以编程的方式访问数据库 游标的声明 FOR循环游标 1 定义游标 2 定义游标变量 3 使用for循环来使用这个游标declare 类型定义cursorc studentisselect fromstudent 定义一个游标变量v cinfoc emp ROWTYPE 该类型为游标c emp中的一行数据类型c rowc student rowtype beginforc rowinc studentloopdbms output put line c row pno c row pname endloop end 游标的声明 Fetch游标 使用的时候必须要明确的打开和关闭declare 类型定义cursorc studentisselect fromstudent 定义一个游标变量c rowc student rowtype beginopenc student loop 提取一行数据到c rowfetchc studentintoc row 判读是否提取到值 没取到值就退出 取到值c job notfound是false 取不到值c job notfound是trueexitwhenc job notfound d