数据库培训-PLSQL.ppt

数据库培训PL SQL基础 电信网通BU2007年05月 2 培训内容 PL SQL程序结构基本语法要素流程控制事务处理游标异常处理存储过程和函数程序包触发器 3 PL SQL程序结构 PL SQL 过程化SQL语言PL SQL块语法 DECLARE declarationstatementsBEGIN executablestatements EXCEPTION exceptionstatementsEND 4 基本语法要素 常量变量符合数据类型变量表达式函数ODS变量命名规范 5 常量 语句格式 常量名CONSTANT类型标识符 notnull 值 例 avc acct idCONSTANTVARCHAR2 5 NOTNULL AP001 6 变量 语句格式变量名类型标识符 notnull 值 例 avc serv idVARCHAR2 5 NOTNULL SV001 基本数据类型numberintchar varchar varchar2 longdateboolean turefalsenull 7 复合数据类型变量 使用type 定义变量avc nodept deptno type 定义记录类型变量使用rowtype 定义变量一维表类型变量多维表类型变量 8 表达式 算术表达式逻辑表达式字符表达式关系表达式 9 函数 数字函数字符函数转换函数日期函数常规函数 10 ODS变量命名规范 createorreplaceprocedurep tf acct income mon 统计分析 收入情况 月 模块名称 p tf acct income mon生成周期 按月执行数据来源 FAS TF ACCT ITEM数据目标 统计分析 收入情况 月 TF ACCT INCOME MON avc cycleidvarchar2 数据周期avc lastcycleidvarchar2 上一次成功数据周期an returnoutnumber 返回值 0 表示成功 1 表示失败 an syserroutnumber 系统错误号an recordoutnumber 总记录数avc syserrtextoutvarchar2 系统错误文本 isn pointvarchar2 10 vc sqlvarchar2 4000 begin endp tf acct income mon 11 流程控制 条件控制循环控制 12 条件控制 If条件1then语句段1 Elsif条件2thenif 条件4 then语句段2 endif Else语句段3 Endif 13 循环控制 Loop循环While循环For循环 14 Loop循环 Loop循环语句1If条件语句thenexit else语句2endif Endloop 15 Loop循环 Loop循环语句1语句2exitwhen条件语句 Endloop 16 For循环 for循环变量in reverse 循环上届 下届循环loop循环处理语句 Endloop 17 事务处理 Commit命令用setautoon off 来打开 关闭自动提交Rollback命令Savepoint命令 18 游标 游标的作用隐式游标显示游标游标属性引用游标 动态游标 19 游标的作用 从数据库中提取出数据 以临时表的形式放在内存中 初始指向首记录 利用fetch移动指针 对游标中的数据进行处理 然后写到结果表中 20 显示游标 select语句上使用显式游标 明确能访问结果集FOR循环游标 常用的一种游标 转换函数fetch游标参数游标 21 FOR循环游标 定义游标定义游标变量使用for循环来使用这个游标前向游标只能往一个方向走效率很高declare 类型定义cursorccisselectempno ename job salfromempwherejob MANAGER 定义一个游标变量ccreccc rowtype begin for循环forccrecinccloopdbms output put line ccrec empno ccrec ename ccrec job ccrec sal endloop end 22 fetch游标 使用的时候必须要明确的打开和关闭declare 类型定义cursorccisselectempno ename job salfromempwherejob MANAGER 定义一个游标变量ccreccc rowtype begin 打开游标opencc loop循环loop 提取一行数据到ccrec中fetchccintoccrec 判断是否提取到值 没取到值就退出 取到值cc notfound是false 取不到值cc notfound是trueexitwhencc notfound dbms output put line ccrec empno ccrec ename ccrec job ccrec sal endloop 关闭closecc end 23 游标属性 游标的属性4种 notfoundfetch是否提到数据没有true提到false foundfetch是否提到数据有true没提到false rowcount已经取出的记录的条数 isopen布尔值游标是否打开declare 类型定义cursorccisselectempno ename job salfromempwherejob MANAGER 定义一个游标变量ccreccc rowtype begin 打开游标opencc loop循环loop 提取一行数据到ccrec中fetchccintoccrec 判断是否提取到值 没取到值就退出 取到值cc notfound是false 取不到值cc notfound是trueexitwhen cc notfoundorcc rowcount 3 dbms output put line cc rowcount ccrec empno ccrec ename ccrec job ccrec sal endloop 关闭closecc end 24 参数游标 按部门编号的顺序输出部门经理的名字declare 部门cursorc1isselectdeptnofromdept 参数游标c2 定义参数的时候 只能指定类型 不能指定长度 参数只能出现在select语句 号的右侧cursorc2 nonumber pjobvarchar2 isselectemp fromempwheredeptno noandjob pjob c1recc1 rowtype c2recc2 rowtype 定义变量的时候要指定长度v jobvarchar2 20 begin 部门forc1recinc1loop 参数在游标中使用forc2recinc2 c1rec deptno MANAGER loopdbms output put line c1rec deptno c2rec ename endloop endloop end 25 综合例子 题目求购买的商品包括了顾客 Dennis 所购买商品的顾客 姓名 createtablepurcase productidnumber customeridnumber createtablecustomer customeridnumber namevarchar 30 思路 Dennis A B 别的顾客 A B C A C B C C 26 declare Dennis所购买的商品cursorcur dennisisselectproductidfrompurcasewherecustomerid selectcustomeridfromcustomerwherename Dennis 除Dennis以外的每个顾客cursorcur custisselectcustomeridfromcustomerwherename Dennis 每个顾客购买的商品cursorcur prod idvarchar2 isselectproductidfrompurcasewherecustomerid id jnumber inumber rec denniscur dennis rowtype rec custcur cust rowtype rec prodcur prod rowtype avc namevarchar2 10 begin 顾客循环forrec custincur custloopi 0 j 0 forrec dennisincur dennisloopi i 1 每个顾客买的东西forrec prodincur prod rec cust customerid loopif rec prod productid rec dennis productid thenj j 1 endif endloop endloop if i j thenselectnameintoavc namefromcustomerwherecustomerid rec cust customerid DBMS output put line avc name endif endloop end 27 隐式游标 隐式游标也叫sql游标 是用来处理所有sql语句的环境区域指针 不能通过专门的语句来打开 PL SQL隐式的打开sql游标处理完后自动关闭 单条sql语句所产生的结果集合用关键字SQL表示隐式游标4个属性 rowcount影响的记录的行数整数 found影响到了记录true notfound没有影响到记录true isopen是否打开布尔值永远是false多条sql语句隐式游标SQL永远指的是最后一条sql语句的结果主要使用在update和delete语句上 28 游标变量 动态游标 select语句是动态的declare 定义一个类型 refcursor 弱类型typecurisrefcursor 定义一个refcursor类型的变量curacur c1recemp rowtype c2recdept rowtype beginDBMS output put line 输出员工 opencuraforselect fromemp loopfetchcuraintoc1rec exitwhencura notfound DBMS output put line c1rec ename endloop DBMS output put line 输出部门 opencuraforselect fromdept loopfetchcuraintoc2rec exitwhencura notfound DBMS output put line c2rec dname endloop closecura end 29 异常处理 系统预定义的异常自定义异常声明 异常名EXCEPION 产生异常 raise语句异常处理 30 存储过程和函数 没有名字的PL SQL块 匿名 有名字的PL SQL块 子程序 存储过程和函数 存储过程createorreplaceprocedurep1asbeginexceptionend createorreplaceprocedurep jdashellovarchar2 20 beginselect HelloWorld intohellofromdual dbms output put line hello end 执行存储过程的方法executep jd SQL PLUS中SQL beginp jd end 31 带参数的存储过程 输入参数in 不写in的参数都是输入参数 根据部门编号查员工姓名createorreplaceprocedurep getemp nonumber ascursorc1isselect fromempwheredeptno no c1recc1 rowtype begin no 20 输入参数是不能赋值的forc1recinc1loopdbms output put line c1rec ename endloop end 32 带参数的存储过程 输出参数out 根据部门编号查出部门的平均工资 返回平均工资的值 in输入 在procedure中是不能赋值的 out输出 在procedure中是能赋值的 定义参数是不能指定长度的 定义变量是必须指定长度的createorreplaceprocedurep getavgsal nonumber avgsaloutnumber no输入参数 avgsal输出参数asaavarchar2 10 变量beginselectavg sal intoavgsalfromempwheredeptno no end 调用它只能使用PL SQL块declareavnumber beginp getavgsal 10 av dbms output put line 平均工资 round av 2 end 33 带参数的存储过程 一个参数同时可以输入 也可以输出 输入输出参数createorreplaceprocedurep getavgsal ninoutnumber asbeginselectavg sal intonfromempwheredeptno n end declareavnumber beginav 10 p getavgsal av dbms output put line 平均工资 round av 2 end 34 带多个参数的存储过程 带多个参数的存储过程createorreplaceprocedurep getM nonumber pjobvarchar2 as 参数游标c2 定义参数的时候 只能指定类型 不能指定长度 参数只能出现在select语句 号的右侧cursorc2 no1number pjob1varchar2 isselect fromempwheredeptno no1andjob pjob1 c2recc2 rowtype 定义变量的时候要指定长度v jobvarchar2 20 begin 参数在游标中使用forc2recinc2 no pjob loopdbms output put line c2rec deptno c2rec ename endloop end 调用方法 executep getm 10 MANAGER 按位置 no 10 pjob MANAGER executep getm pjob MANAGER no 10 按参数的名字来传值 35 函数 必须要有返回值只能返回一个值 36 函数例子 根据部门编号查出部门的平均工资 返回平均工资的值 利用函数 createorreplacefunctionf getavgsal nonumber returnnumberasavgsalnumber 7 2 beginselectavg sal intoavgsalfromempwheredeptno no 返回值returnavgsal end 37 一个函数返回2个值 带输出参数 每个部门的平均工资和工资总额 一个函数返回2个值createorreplacefunctionf getavgsal nonumber sumsaloutnumber returnnumberasavgsalnumber 7 2 begin 平均工资selectavg sal intoavgsalfromempwheredeptno no 工资总额selectsum sal intosumsalfromempwheredeptno no 返回值returnavgsal end 38 函数的调用方法 块调用declareaanumber beginaa f getavgsal 10 dbms output put line to char aa end SQL语句来调用 DML selectf getavgsal 10 fromdual selectdeptno f getavgsal deptno fromdept 含有修改语句 select语句是无法调用createorreplacefunctionf1returnnumberasupdateempsetcomm 1000wherejob CLERK returnsql rowcount end select语句是无法调用它的 因为其中含有修改语句 39 程序包 PACKAGE用途 模块化 公司的员工的管理1 增加一个员工2 员工离职包中的变量是全局变量返回结果集合 40 PACKAGE的例子 createorreplacepackagetest pack 包头asproceduregetname enonumber enmoutvarchar2 functionf get name enonumber returnvarchar2 end createorreplacepackagebodytest pack 包体asproceduregetname enonumber enmoutvarchar2 asbeginselectenameintoenmfromempwhereempno eno end functionf get name enonumber returnvarchar2asnvarchar2 20 beginselectenameintonfromempwhereempno eno returnn