Oracle pl sql 设计.ppt

1、1,PLSQL基础,2,本章目标,学员应掌握pl/sql块的结构 变量定义、分界符 流程控制语句 游标定义及其使用 复合数据类型record类型 游标数据类型,3,1.1PL/SQL概述,pl/sql（procedure language/structure query language）是一种应用程序开发语言，它包括大量的过程语句和随sql命令一起的命令。此语言消除了数据库技术和过程编程语言之间的障碍。基本上，作为一个应用程序开发工具pl/sql使用完善的oracle rdbms工具并扩展了标准sql数据库语言。 pl/sql允许使用包括insert、update、delete和select

2、在内的所有sql数据操作语句，它也可以使用诸如commit、rollback和savepoint等事务处理语句。pl/sql不仅允许您使用sql语句操作数据，也允许您使用诸如循环和条件分支这些在sql中无法进行的流程控制语句来处理数据。,4,sql与pl/sql区别,Sql是当今最为强大的数据检索机制之一。它被认为是当今最先进的数据库技术。但sql并不能完成一个过程语言所能完成的所有任务。pl/sql的出现将sql的强大功能和过程语言的灵活性结合起来。换句话讲，pl/sql是sql的扩展。,5,pl/sql程序运行环境,Sql*plus：最基本的环境。以declare或begin关键字开始的，

3、sql*plus就会识别为pl/sql程序，是以“/”结束并执行。若是sql则是以“；”结束并执行。,6,pl/sql程序块,一组标准的pl/sql代码段，oracle的程序都是程序块为基本单位，可相互嵌套。,7,pl/sql块的分类,匿名块：没有标识名字，只执行一次，不能存储在db中。 带名块：该块指定了一个名称，也是动态产生只能执行一次，不能存储在db中。 子程序：是指可以存储在数据库中的过程、函数、包等程序块。可以多次执行，也可以在外部的程序中显示调用。 触发器：是一种存储在db中特殊的带名块。当某些事件执行时，就激发它，可多次执行。,8,程序块的结构,头部（head）：定义块的类型，可

4、以是函数、过程、包等。 声明部分（declare）：定义变量、游标等。 执行部分（execute）：以begin开始，end结束的一条或多条语句组成，完成特定操作。 异常处理部分（exception）：主要处理执行中出现的多种错误。,9,程序块结构,10,注释,单行注释：“-” 以双连号开始，一行开始至末尾。 多行注释：/*.*/可以跨越多个，不能嵌套。,11,分界符,定义：由单字符或复合字符组成，用来将标识符分隔开来的字符。 单字符分界符：,12,分界符,复合字符分界符：,13,变量的命名规则,以字母（A-Z，a-z）、汉字开头。 后面可跟一个或多个字母，数字，汉字或特殊字符（$,#或_等）

5、。 最多30个字符。 不能有空格。 不能与关键字同名。,14,变量的声明,规定：在pl/sql中使用任一个变量必须事先声明，声明关键字为declare，行尾使用分号结束。 格式： 变量名 constant 数据类型 not null:=值/default 值 说明： - constant：表示定义的为常量，且必须初始化。 - not null：表示定义变量时，必须初始化。,15,赋值,利用赋值操作符 := 。 利用select或fetch把数据库中的相应值赋到变量中。Fetch主要用在游标中。后面讲解。 格式： select 列名清单 into 变量清单 from 表名 where 条件表达式

6、；,16,1.2控制结构,控制结构分类：顺序结构、选择结构、循环结构。 顺序结构：指执行过程按所写程序的顺序执行的结构。 显示执行结果：set serveroutput on,17,选择结构,格式一： if 条件表达式 then 语句； end if； 格式二： if 条件表达式 then 语句1； else 语句2； end if；,18,选择结构,格式三： if 条件表达式 then 语句1； elsif 条件 then 语句2； elsif 条件 then 语句3； else 语句； end if；,19,多分支结构,格式： case when 条件表达式 then 语句序列1； whe

7、n 条件表达式 then 语句序列n； else 语句序列n+1； end case；,20,循环结构,定义：是指按照指定的逻辑条件对一组命令执行一定次数的结构。 类型分类： loop-exit-end loop-exit when-end while-loop-end for-in-loop-end,21,loop-exit-end,执行过程：一个简单是通过其中的选择结构控制的。如果没有选择控制，则是死循环，该循环从loop开始，到end loop结束，其循环循环。 格式： loop 语句序列； if 退出循环表达式 then exit； end if； end loop；,22,loop-

8、exit when-end,格式： loop 语句序列； exit when 条件表达式； end loop；,23,while-loop-end,格式： while 条件表达式 loop 语句序列； end loop；,24,for-in-loop-end,执行过程：循环变量是一个隐式声明的变量，循环变量在上界和下界之间变化。 格式： for 循环变量 in reverse 下界.上界 loop 语句序列； end loop；,25,部分语句及包说明,Dbms_output包将字符输出到缓冲区中。,26,goto语句,功能：无条件转到某个标签。 执行情况：执行时，“goto”语句将更改pl/

9、sql程序块的控制流程。 实现方法：定义标签，使用goto语句将控制转到标签。 使用标签：如果对象在外部程序块中已经声明，而同样的对象在内部程序块中再次声明，则该内部程序块不能引用外部程序块中的对象。除非外部程序块已经用标签名标注，可以在标有标签名的程序块中引用对象。,27,游标概述,定义：实质上是根据相应条件从数据库表中挑选出来的一组记录（查询所返回的行集可以是零行，一行或多行组成，作为一个临时表放置在内存之中）,28,游标类型,PL/SQL 使用两类游标 隐式游标 由 Oracle 自动定义并打开，用于处理每个 SQL语句 最近打开的环境区域被称为“SQL%”游标 显式游标 如果查询返回多

10、行，则可定义显式游标来对当前正在处理的行进行跟踪,29,游标功能,游标名是一个未声明的标识符，仅用于引用查询 不能对游标赋值，也不能在表达式中使用游标名 显式游标可以有参数 可以将实参的不同值传递到游标，按需要采用或替换默认值,30,操作游标,声明游标 打开游标 提取游标 关闭游标 注意：隐式游标不用显示使用这些操作。,31,声明游标,定义： cursor 游标名 is select 语句； 示例 cursor emp_cur is select empno, ename, job, sal from emp； 注意：游标名不是pl/sql变量，它仅是用于引用某个查询的名称。pl/sql中不允

11、许提前引用，只有声明了游标才能引用它。,32,打开游标,在查询返回任何行之前必须打开游标 打开游标将执行查询并识别活动集合 open 游标名； 示例： open emp_cur;,33,提取游标,功能：打开游标后，可以将游标中的记录或数据赋给指定的变量和记录，然后使用它。 格式： fetch 游标名 into 变量1变量n； 或者 fetch 游标名 into 记录类型变量； 示例 fetch emp_cur into rec;,34,关闭游标,游标用完后应关闭，关闭后，与游标相关的资源就释放回系统（再对其操作系统就认为是非法的），可以通过open命令重新初始化游标，也可以用游标for循环来隐

12、式打开游标。 格式：close 游标名；,35,显式游标的属性,每个游标有四个属性可以用于访问游标的环境区域 %NOTFOUND %FOUND %ROWCOUNT %ISOPEN 要使用这些属性，只要简单地将它们添加到游标名后即可,36,显示游标属性,%notfound：fetch提取成功为假，没有返回行为真。 %found： fetch提取成功为真，没有返回行为假。 %rowcount：用来返回游标的行数。 %isopen：判断当前的游标所处的状态。 打开则为真，否则为假。,37,游标 FOR 循环,功能：利用隐式声明其循环变量为与游标一样类型的记录类型变量。自动打开游标，重复从活动集获取各

13、个行的值并传送到记录类型变量中，然后当处理完所有行或退出循环时，自动关闭游标。 格式： for 记录类型变量 in 游标名；,38,隐式游标属性,隐式游标：在程序块中执行insert、update、delete、select语句时，pl/sql自动给予一隐式游标协助操作。隐式游标为sql%。 隐式游标属性：可用属性访问游标的环境区域。自动打开前，全部都是null。,39,隐式游标属性,Sql%notfound：insert、update、delete语句没有影响到任何行或单行的select语句没有返回任何行时此属性为真，否则为假。 Sql%found：如果insert、update、delet

14、e语句影响到一行或多行，或单行select语句返回一行或多行，此属性为真，否则为假。 Sql%rowcount：返回受到insert、update、delete作用的行数或由单行的select返回的行数。 Sql%isopen：由于oracle执行完与隐式游标相关的sql语句后，总是自动关闭它，且sql游标为隐式游标，所以sql%isopen总是假。,40,参数化的游标,参数性质：仅可用作游标的select语句的输入参数，它不能用于游标返回数据。 引用游标时注意事项：当游标声明中既作为列名，又作为游标参数的标识符时，则该标识符应引用列名；如果要引用参数而不是列名，则标识符前必须加上游标名。 游

15、标参数范围：对游标是局部的，也就是仅可以在游标声明的查询中引用游标参数。 参数的提供：可以在open语句或游标for循环中。,41,复合数据类型,pl/sql的复合数据类型有记录、表、数组，这些结构是标量数据类型的组合，可扩宽应用范围。 常用复合数据类型属性： -%type属性 -%rowtype属性,42,复合数据类型,%type：在很多情况下，pl/sql变量被用来处理存储在数据库表中的数据。在这种情况下，变量应该拥有与表字段相同的类型、长度。当源数据的类型改变时，自动适应，从而保证一致，利于编程。 %rowtype：如果想变量具有与表或视图中的行相同的结构，或从定义的游标中获取的行相同的

16、结构，使用%rowtype属性就能达到此目的。并且与表或视图中的列具有相同的列名和数据类型。,43,记录数据类型,格式： type 记录类型 is record （字段1 类型， 字段n 类型）；,44,记录数据类型,使用方法：先使用记录类型，再声明记录类型变量，然后向记录变量中存储值。 使用记录类型： 记录变量.字段名；,45,游标类型-游标变量,游标类型分类： - 强型游标类型：在设置游标类型时，设置return字句，限制此类游标仅能返回return类型中指定的数据类型。 - 弱型游标类型：在声明游标类型时，没指定返回值类型，根据实际的返回值自适应。灵活。,46,声明游标变量,声明步骤： - 声明游标变量类型 - 声明游标变量 - 打开游标变量 - 提取游标变量 - 关闭游标变量,47,声明游标变量类型,格式： type 游标变量类型名 is ref cursor return 返回类型；,48,声明游标变量,格式： 游标变量名 游标变量类型名；,49,打开游标变量,目的：定义游标