oracle-存储过程培训材料(要好好学习哦)PPT课件

1 Oracle存储过程 2 存储过程的建立存储过程的语法结构存储过程的控制语句存储过程的开发存储过程的运行存储过程的调试Informix与oracle存储过程的差异函数包 存储过程 3 存储过程的编写编写后缀名为sql的文件 一个存储过程一个文件 过程格式 CREATEORREPLACEPROCEDURE存储过程名字 参数1INNUMBER 参数2INNUMBER IS变量1INTEGER 0 变量2DATE BEGINEND存储过程名字 存储过程 4 创建一个简单存储过程的方式1 1 从Window打开SQL Plus并且从SQL Plus登录到你的数据库 打开skeleton sql文件 2 在SQL 命令提示符下输入以下命令 SQL skeleton注释 SQL Plus装载skeleton sql文件的内容到SQL Plus缓冲区 并且执行SQL Plus语句 SQL Plus会通知你存储过程已经被成功地创建 3 写一个存储过程 实例 CREATEORREPLACEPROCEDUREskeletonISBEGINDBMS OUTPUT PUT LINE HelloWorld END 存储过程 5 运行查看信息1 SQL EXECUTEskeleton 注释 SQL Plus输出以下信息确信存储过程成功执即PL SQLproceduresuccessfullycompleted 2 在SQL Plus命令行提示符 键入 SQL SETSERVEROUTPUTON再次敲入SQL EXECUTEskeleton即可 注释 查看存储过程中的打印语句信息 实行以上命令 存储过程 6 删除一个存储过程1 在SQL 命令提示符下输入以下命令 命令 SQL DROPPROCEDURE存储过程名 存储过程 7 存储过程的注释 注释一行 存储过程 8 存储过程入参与返回值createorreplaceprocedurerunbyparmeters isalinemp sal type snameoutvarchar sjobinoutvarchar asicountnumber beginificount 1then else endif end 存储过程 9 存储过程变量定义 常用变量类型realsalemp sal type realnamevarchar2 40 realjobvarchar2 40 Pricenumber 5 2 Product idinterger 注释 同一存储过程中 变量名最好不要重复 存储过程 10 存储过程赋值语句 realjob work Product id 100001 realname Brunhilda Price 3 1415 this day TODAY 存储过程 11 存储过程变量先声明且必须声明才能使用 Beginend块外声明的变量影响全局 Beginend块内声明的变量影响本Beginend 变量声明必须在存储过程开头或者Beginend块的开头部分 存储过程 12 存储过程操作符 合并如 sp str1 ASD ERT 则 sp str1 ASDERT 存储过程 13 存储过程异常控制exceptionwhentoo many rowsthenDBMS OUTPUT PUT LINE 返回值多于1行 whenothersthenDBMS OUTPUT PUT LINE 在RUNBYPARMETERS过程中出错 存储过程 14 存储过程结构块BEGIN第一步处理 END BEGIN第二步处理 END BEGIN第三步处理 END 注释 存储过程BEGIN END之间可以进行嵌套 存储过程 15 存储过程游标1 带参数的游标CURSORC USER C IDNUMBER ISSELECTNAMEFROMUSERWHERETYPEID C IDNUMBER OPENC USER 变量值 LOOPFETCHC USERINTOV NAME EXITWHENC USER NOTFOUND ENDLOOP CLOSEC USER 存储过程 16 2 不带参数的游标CURSORC USERISSELECTNAMEFROMUSERWHERETYPEID C ID OPENC USER LOOPFETCHC USERINTOV NAME EXITWHENC USER NOTFOUND ENDLOOP CLOSEC USER 存储过程 17 存储过程LOOP循环Counter 0 LOOPcounter counter 1 EXITWHENcounter 5 ENDLOOP 存储过程 18 存储过程for循环1 方式一 BEGINFORcur resultincurLOOPBEGINV SUM cur result 列名1 cur result 列名2 END ENDLOOP END 存储过程 19 2 方式二 BEGINFORcur resultREVERSE1 5LOOPBEGINDBMS OUTPUT PUT LINE cur result END ENDLOOP END 存储过程 20 存储过程while循环Counter 0 WHILEcounter 6LOOPcounter counter 1 ENDLOOP 存储过程 21 存储过程if控制IFstr1 str2thenresult 1 ELSIFstr2 str1THENresult 1 ELSEresult 0 ENDIF 存储过程 22 存储过程if条件表达式比较符 ANDORNOT NOT BETWEENAND NOT IN IS NOT NULL NOT LIKE 存储过程 23 type定义方法1 在pl sql中可以将变量和常量声明为内建或用户定义的数据类型 以引用一个列名 同时继承他的数据类型和大小 注释 v anumber 5 10 v bv a type 15 v cv a type 存储过程 24 游标属性1 orcale在对DML操作时会产生隐式游标 2 DML是指 insert update delete select的操作 3 隐式游标只使用sql found sql notfound sql rowcount三个属性 4 sql found sql notfound是布尔值 sql rowcount是整数值 5 sql found为true sql notfound为false 6 sql rowcount是返回当前位置为止游标读取的记录行数 7 在执行任何DML语句之前 sql found sql notfound sql rowcount的值都是null 存储过程 25 存储过程控制语句的跳出exitwheneixt注释 exit语句可立即结束循环exitwhen语句是在指定条件下结束循环 并且可以出现在循环代码中的任何位置 存储过程 26 游标的使用1 loop循环 createorreplaceproceduredept procedure ainvarchar2 v aoutdept rowtype is 声明游标cursorc de ainvarchar2 isselecttidfromdeptwheredname a begin 打开游标 对其中找到的记录进行遍历 存储过程 27 openc de a Loopfetchc deintov a exitwhenc de notfound exitwhen语句一定要紧跟在fetch之后 必避免多余的数据处理 dbms output put line deptno v a deptno dbms output put line dname v a dname dbms output put line loc v a loc endloop closec de end 存储过程 28 游标的使用2 for循环 CURSORcur testISSELECTp number p name p manager p clientFROMprojectWHEREp nameLIKE S BEGININSERTINTOprojectVALUES v number v name v manager v client FORrecINcur testLOOPDBMS OUTPUT put line rec p number DBMS OUTPUT put line rec p name DBMS OUTPUT put line rec p manager DBMS OUTPUT put line rec p client ENDLOOP END 存储过程 29 游标的使用3 while循环 cursorc postype ainvarchar2 isselectbid bidnamefromdeptwheredname a openc postype a beginfetchc postypeintov postype v description whilec postype foundloopfetchc postypeintov postype v description endloop closec postype end 存储过程 30 储存过程嵌套createorreplaceprocedureinnerBlock p1invarchar2 aso1varchar2 10 out1 inner1varchar2 20 Begindbms output put line begininner1 inner1 exceptionwhenothersthennull end end 存储过程 31 储存过程中建表createorreplaceprocedureskeletonasbeginexecuteimmediate createtabletable1 idnumber namevarchar2 20 End 存储过程 32 存储过程的返回值Oracle存储过程的返回值 必须在创建一个存储过程时定义返回值 存储过程 33 存储过程的执行Executexxx Executexxx 值1 值2 Callxxx 值1 值2 值3 Selectxxx 值1 fromdual 注释 Selectxxx 值1 fromdual用法一般只在函数及包代码中用到 存储过程 34 常用存储过程介绍Spcmpdaysum sp statdate 26 Spcmpmonthsum sp statdate Spfnul2zero 存储过程 35 开发一个存储过程1 不带参数的储存过程createorreplaceprocedurerunbyparmetersbeginificount 1then else endif exception 存储过程异常whentoo many rowsthenDBMS OUTPUT PUT LINE 返回值多于1行 whenothersthenDBMS OUTPUT PUT LINE 在RUNBYPARMETERS过程中出错 end 存储过程 36 开发一个存储过程注释 如果没有orreplace语句 则仅仅是新建一个存储过程 如果系统存在该存储过程 则会报错 Createorreplaceprocedure如果系统中没有此存储过程就新建一个 如果系统中有此存储过程则把原来删除掉 重新创建一个存储过程 存储过程名定义 包括存储过程名和参数列表 参数名和参数类型 参数名不能重复 as is 为关键字 可以理解为pl sql的declare关键字 用于声明变量 存储过程 37 开发一个存储过程2 带参数的储存过程createorreplaceprocedurerunbyparmeters isalinemp sal type snameoutvarchar sjobinoutvarchar asicountnumber beginselectcount intoicountfromempwheresal isalandjob sjob ificount 1thenelseendif exception 存储过程异常whentoo many rowsthenDBMS OUTPUT PUT LINE 返回值多于1行 whenothersthenDBMS OUTPUT PUT LINE 在RUNBYPARMETERS过程中出错 end 存储过程 38 开发一个存储过程注释 sal type目的是为了保持与传参过来的数据宽度一致 IN OUT即可作输入参数 也可作输出参数 变量声明块 紧跟着的as is 关键字 用于声明变量 IN按值传递 并且它不允许在存储过程中被重新赋值 如果存储过程的参数没有指定存参数传递类型 默认为IN OUT参数 作为输出参数 需要注意 当一个参数被指定为OUT类型时 就算在调用存储过程之前对该参数进行了赋值 在存储过程中该参数的值仍然是null IN参数的宽度是由外部决定 对于OUT和INOUT参数的宽度是由存储过程内部决定 存储过程 39 开发一个存储过程3 参数的存储过程默认值createorreplaceprocedureprocdefault p1varchar2 p2varchar2default mark asbegindbms output put line p2 end SQL execprocdefault a 或者SQL execprocdefault2 p2 aa 注释 1 可以通过default关键字为存储过程的参数指定默认值 在对存储过程调用时 就可以省略默认值 2 默认值仅仅支持IN传输类型的参数 OUT和INOUT不能指定默认值 存储过程 40 存储过程调用方式1 方式一 Declarerealsalemp sal type realnamevarchar 40 realjobvarchar 40 BEGINrealsal 1100 realname realjob CLERK runbyparmeters realsal realname realjob 必须按顺序DBMS OUTPUT PUT LINE REALNAME REALJOB 输出模式 END 存储过程 41 存储过程调用方式2 方式二 declarerealsalemp sal type realnamevarchar 40 realjobvarchar 40 beginrealsal 1100 realname realjob CLERK runbyparmeters sname realname isal realsal sjob realjob 指定值对应变量顺序可变DBMS OUTPUT PUT LINE REALNAME REALJOB 输出模式 END 存储过程 42 储存过程调试declareparam outvarchar2 28 param inoutvarchar2 28 beginparam inout ff proce test dd param out param inout dbms output put line param out end 注释 存储过程调试主要根据开发者自己的决定 一般情况下通过打印语句来完成 这里就不详细说明 存储过程 43 系统异常错误信息ACCESS INTO NULL试图给为初始化对象的属性赋值CASE NOT FOUNDCASE中若未包含相应的WHEN 并且没有设置COLLECTION IS NULL试图向为初始化的嵌套表和变长数组赋值时 引发异常CURSER ALREADY OPEN试图打开一个已经打开的游标时产生异常DUP VAL ON INDEX唯一索引对应的列上有重复的值INVALID CURSOR在不合法的游标上进行操作INVALID NUMBER内嵌的SQL语句不能将字符转换为数字NO DATA FOUND使用selectinto未返回行 或企图在表中访问为初始化的数据TOO MANY ROWS执行selectinto时 结果集超过一行ZERO DIVIDE试图用0除某个数字SUBSCRIPT BEYOND COUNT元素下标超过嵌套表或VARRAY的最大值SUBSCRIPT OUTSIDE LIMIT试图使用嵌套表或VARRAY时 将下标指定为负数 存储过程 44 系统异常错误信息VALUE ERROR发生算术 转换 截断或大小约束错误 LOGIN DENIEDPL SQL应用程序连接到oracle数据库时 提供了不正确的用户名或密码NOT LOGGED ONPL SQL应用程序在试图连接数据库之前访问数据库中的数据PROGRAM ERRORPL SQL内部问题 可能需要重装数据字典 pl SQL系统包ROWTYPE MISMATCH宿主游标变量与PL SQL游标变量的返回类型不兼容SELF IS NULL使用对象类型时 在null对象上调用对象方法STORAGE ERROR运行PL SQL时 内存用尽或者内存出现问题SYS INVALID ID无效的ROWID字符串TIMEOUT ON RESOURCE当数据库等待某资源时超时 存储过程 45 ORACLE和informix存储过程区别1 建立存储过程的语法Oracle create orreplace procedureprocedue name arg1 in out inout type argn in out inout type is as 变量定义区 beginendprocedure name Informix createprocedureproc name in parameter list returningout para list out result set 存储过程 46 ORACLE和informix存储过程区别2 没有参数也没有返回值Oracle createorreplaceprocedurepNoParamasbegindeletefromt1 commit end endpNoParam Informix 存储过程 47 createprocedurepNoParam Returningintbeginonexceptionrollbackwork endexceptionbeginwork deletefromt1 commitwork return end endprocedure 存储过程 48 ORACLE和informix存储过程区别3 返回记录集Oracle procedurepReturnSet RefCursoroutvarchar2 Refdefineoutvarchar2 aslocalCursorTestRefCursorTyp localnumberTestRefCursorTyp beginselectf1 f2intolocalCursor localnumberfromt1 RefCursor localCursor Refdefine localnumber endpReturnSet 存储过程 49 Informix createprocedurepReturnSet returninginteger defineiinteger definejvarchar 10 beginforeachselectf1 f2intoi jfromt1endforeach returni j End endprocedure 存储过程 50 ORACLE和informix存储过程区别4 错误捕捉Oracle ExceptionwhenothersthenDBMS OUTPUT PUT LINE 在XX过程中出错 Informix ONEXCEPTIONSETsp errsql sp errisam sp errstrIF sp errsql 0 THENRAISEEXCEPTION 746 sp errisam 错误码 sp errsql ENDIF ENDEXCEPTION 存储过程 51 ORACLE和informix存储过程区别5 对游标的处理Oracle createorreplaceprocedurepHasCursorasv f1number 10 0 cursorcurt1isselectf1fromt1forupdate beginopencurt1 loopfetchcurt1Intov f1 exitwhencurt1 notfound endloop Closecurt1 End 存储过程 52 Informix createprocedurepHasCursor definev f1integer beginforeachselectf1intov f1fromt1 注意这里没有分号if v f1 1 thenupdatet1setf2 one wheref3 1001 endif endforeach End EndpHasCursor 存储过程 53 ORACLE和informix存储过程区别6 打印调试信息的处理Oracle DBMS OUTPUT PUT LINE 开始执行存储过程 DBMS OUTPUT PUT LINE v date v date DBMS OUTPUT PUT LINE 存储过程执行完毕 Informix setdebugfileto trace check withappend 说明 withappend 表示以追加模式打开跟踪结果文trace 开始执行存储过程 trace v date v date trace 存储过程执行完毕 traceoff 存储过程 54 ORACLE和informix存储过程区别7 关于参数的说明注释 如果存储过程想返回一个参数 在informix中是通过返回值的形式实现的 而在oracle是通过输出参数或者输入输出参数实现的 Oracle createorreplaceprocedurep1 xoutnumber asbeginx 0 endp1 Informix createprocedurep1 returninginteger return0 存储过程 55 ORACLE和informix存储过程区别8 存储过程中调用另一个存储过程Oracle CallpNoParam CallpNormalParam 1 a v Result Informix spNoParam pNormalParam 1 a returningv Result 存储过程 56 ORACLE和informix存储过程区别9 if使用Oracle IFstr1 str2thenresult 1 ELSIFstr2 str1THENresult 1 ELSEresult 0 ENDIFInformix 存储过程 57 IFstr1 str2thenresult 1 ELIFstr2 str1THENresult 1 ELSEresult 0 ENDIF 存储过程 58 ORACLE和informix存储过程区别10 赋值Oracle v 1 100 Informix letv 1 100 存储过程 59 练习创建一个存储过程 使用游标从dept表中取BID为 010 开头的记录信息 把它往表t01 psn中插入一条记录 并且循环打印插入的数据记录 存储过程 60 1 函数结构CREATEORREPLACEFUNCTION函数名 参数1INNUMBER 参数2INNUMBER RETURN类型IS ASBEGINFUNCTION BODYEND函