PLSQL语法以及程序编写介绍ppt课件.pptx

PL SQL语法以及程序编写介绍 一 PL SQL介绍PL SQL是一种高级数据库程序设计语言 该语言专门用于在各种环境下对Oracle数据库进行访问 PL SQL语言实现了将过程结构与OracleSQL的无缝集成 从而为用户提供了一种功能强大的结构化程序设计语言 1 二 引入PL SQL的原因PL SQL语言可以将第四代语言的强大功能和灵活性与第三代语言的过程结构的优势融为一体 2 三 PL SQL内容PL SQL是在SQL语言中扩充了面向过程语言中使用的程序结构 如 变量和类型 即可以予定义也可以由用户定义 控制语句 如IF THEN ELSE 和循环过程和函数对象类型和方法 PL SQL8 0版本以上 3 四 变量的声明在PL SQL中声明变量与其他语言不太一样 它采用从右往左的方式声明 C语言声明变量方式 intv id PL SQL声明变量方式 v idnumber 五 变量的赋值不能用 应该用 即形式为v id 5 4 六 数据类型PL SQL主要用于数据库编程 所以其所有数据类型跟oracle数据库里的字段类型是一一对应的 常用的数据类型大体上有数字型 布尔型 字符型和日期型 数字型用来存储整型或者浮点型数值 范围为 E 130 10E125 其声明方式为 number precision scale 其中 precision scale 是可选的 precision表示所有数字的个数 scale表示小数点右边数字的个数 5 实际数据定义在oralce中的数据 7456123 89NUMBER7456123 89NUMBER 9 7456123 89NUMBER 9 2 7456123 89NUMBER 9 1 7456123 89NUMBER 6 7456123 89NUMBER 7 2 7456123 89NUMBER 7 2 7456123 89 7456124 7456123 89 7456123 9 溢出 7456100 溢出 6 布尔型布尔型BOOLEAN 用于逻辑判断 只有TRUE FALSE两种值 字符型主要说明字符类型的存储格式 主要包括char varchar2和long等几种类型 CHAR 用于描述定长的字符型数据 长度 2000字节 如果实际值不够定义的长度 系统将以空格填充 7 v charchar 10 abcde Length v char 10 VARCHAR2 用于描述变长的字符型数据 长度 4000字节 它的声明方式如下VARCHAR2 L L为字符串长度 没有缺省值 v charvarchar2 10 abcde Length v char 5 8 NCHAR 来存储Unicode字符集的定长字符型数据 长度 1000字节 它的声明方式与CHAR相同 NVARCHAR2 用来存储Unicode字符集的变长字符型数据 长度 1000字节 它的声明方式与VARCHAR2相同 LONG 用来存储最大长度为2GB的变长字符数据 9 日期型DATE 用于存储日期与时间信息 虽然日期信息也可以用字符型变量或者数字型变量来表示 但是DATE型变量有它特有的属性 每一个DATE型的数据都存储了以下信息 年 月 日 小时 分钟以秒 TO DATE函数TO DATE函数可以将一个字符型变量按照程序员的要求转换成日期型变量 10 假设有一DATE类型变量v date 现在用to date函数将v date赋值为2006年4月6日 V date to date 2006 04 06 yyyy mm dd V date to date 2006 04 0610 10 10 yyyy mm ddhh24 mi ss V date to date 20060406 yyyymmdd V date to date 2006 04 06 yyyy mm dd V date to date 20060406101010 yyyymmddhh24miss V date to date 2006 04 0610 10 10 yyyy mm ddhh24 mi ss 11 七 条件逻辑在PL SQL中 可以使用命令if else和elsif来控制可执行命令段的命令流 下面列出了可用的条件逻辑命令的格式 循环条件除外 格式1 ifthenelsifthenelseendif 12 格式2 ifthenifthenendif elseendif 总的说来除了格式上与C略有区别 其它都是一样的 13 例 判断通过映射关系 将邮编信息填入到i addr zipcode字段中去ifi area id 1theni addr zipcode 325200 elsifi area id 2theni addr zipcode 325400 elsifi area id 3theni addr zipcode 325800 elsifi area id 4theni addr zipcode 325100 elsifi area id 5theni addr zipcode 325600 elsifi area id 6theni addr zipcode 325700 elsifi area id 7theni addr zipcode 325300 elsifi area id 8theni addr zipcode 325500 elsei addr zipcode 325000 endif 14 八 循环在一个单独的PL SQL代码块中可以使用循环处理多个记录 PL SQL支持三种类型的循环 简单循环 一个重复运动的循环 直到碰到循环中的exit或者exitwhen语句时才结束循环 WHILE循环 一个重复运行的循环 直到满足条件才退出 FOR循环 一个重复运行指定遍数的循环 15 例 一个简单循环举例 i variable 10 i sum 0 loopi sum i sum i variable i variable i variable 1 exitwheni variable 0 endloop 16 例 用while循环来完成上例 i variable 10 i sum 0 whilei variable 0loopi sum i sum i variable i variable i variable 1 endloop 17 例 用for循环来完成简单循环 i variable 10 i sum 0 fori variablein0 10loopi sum i sum i variable i variable i variable 1 endloop 18 九 游标当查询返回结果超过一行时 就需要一个显式游标 也就是我们常说的游标 常用的游标要在程序中使用游标 大致需要以下几个步骤 1 定义游标2 打开游标3 从游标中提取数据4 关闭游标 19 例 i cust namevarchar2 20 i cust idnumber 13 cursorcur cust infoisselectname cust idfromcustwherests A abegin opencur cust info bloopfetchcur cust infointoi cust name i cust id cexitwhencur cust info notfound endloop closecur cust info d end 20 带参数的游标与存储过程和函数相似 可以将参数传递给游标并在查询中使用 这对于处理在某种条件下打开游标的情况非常有用 21 例 i cust namevarchar2 20 i cust idnumber 13 i stsvarchar2 10 A cursorcur cust info p stsvarchar2 isselectname cust idfromcustwherests p sts abegin opencur cust info i sts bloopfetchcur cust infointoi cust name i cust id cexitwhencur cust info notfound endloop closecur cust info d end 22 REFCURSOR i cust namevarchar2 20 i cust idnumber 13 typecur refisrefcursor cur cust infocur ref abegin opencur cust infoforselectname cust idfromcustwherests A bloopfetchcur cust infointoi cust name i cust id cexitwhencur cust info notfound endloop closecur cust info d end 23 十 PL SQL程序编写块的介绍PL SQL程序基本结构是块 BLOCK 所有的PL SQL程序都是由块组成的 这些块之间还可以相互嵌套 通常 程序中的每一块都实现一个逻辑操作 从而把不同的任务进行分割 由不同的块来实现 24 块的结构DECLARE Declarativesection PL SQLvariables types cursors andlocalsubprogramsgohere 声明部分BEGIN Executablesection proceduralandSQLstatementsgohere Thisisthemainsectionoftheblockandtheonlyonethatisrequired 执行部分EXCEPTION Exception handlingsection error handlingstatementsgohere 出错部分END 25 Declare 声明部分l servidnumber 12 begin 执行部分forrecin select fromtemp ctzj sts cmpwhereserv id 97isnull loopbeginselectserv idfromserv acc nbrwherenxx nbr rec nxx nbrandline nbr rec line nbrandsts A whererownum 1 exceptionwhenno data foundthennull end beginupdatetemp ctzj sts cmpsetserv id 97 to char l servid wherenxx nbr rec nxx nbrandline nbr rec line nbr exceptionwhenothersthendbms output put line i servid end i servid null endloop exception 出错部分whenothersthenrollback dbms output put line SQLERRM end 26 过程与函数PL SQL的过程和函数的运行方式非常类似于其他第3代程序设计语言使用的过程和函数 它们之间具有许多共同的特征属性 总体来说 过程和函数统称为子程序 27 创建过程语句的语法如下所示 CREATE ORREPLACE PROCEDUREprocedure name argument IN OUT INOUT datatype argument IN OUT INOUT datatype IS AS procedure body 28 IN OUT INOUT参数 模式说明 IN OUT INOUT 当过程被调用时 实参的值将传入该过程 在该过程内部 形参类似PL SQL使用的常数 即该值具有只读属性不能对其修改 当该过程结束时 控制将返回到调用环境 这时 对应的实参没有改变 当过程被调用时 实参具有的任何值将被忽略不计 在该过程内部 形参的作用类似没有初始化的PL SQL变量 其值为空 NULL 该变量具有读写属性 当该过程结束时 控制将返回调用环境 形参的内容将赋予对应的实参 该模式是模式IN和OUT的组合 当调用过程时 实参的值将被传递到该过程中 在该过程内部 形参相当于初始化的变量 并具有读写属性 当该过程结束时 控制将返回到调用环境中 形参的内容将赋予实参 29 过程体过程体是一种带有声明部分 可执行语句部分和异常部分的PL SQL块 该声明部分是位于关键字IS或AS和关键字BEGIN之间的语句 可执行部分 该部分是必须要有的 是位于关键字BEGIN和EXCEPTION之间的语句 最后 异常部分位于关键字EXCEPTION和关键字END之间的语句 30 综上所述 过程的结构应具有下面所示的特征 CREATEORREPLACEPROCEDUREprocedure name parameter list AS Declarativesectionishere 声明部分BEGIN Executablesectionishere 执行部分EXCEPTION Exceptionsectionishere 异常部分END procedure name 31 函数函数的创建与过程的创建十分相似 具体语法如下 CREATE ORREPLACE FUNCTIONfunction name argument IN OUT INOUT datatype argument IN OUT INOUT datatype RETURNdatatype IS AS FUCTIONBODY 32 包包的说明 也叫做包头 包含了有关包内容的信息 然而 该部分中不包括包的代码部分 CREATE ORREPLACE PACKAGEpackage name IS AS type definition 结构定义procedure specification 过程声明function specification 函数声明variable declaration 变量声明exception declaration 出错声明cursor declaration 游标声明pragma declarationEND package name 通俗地讲 包头只是起到声明作用 其他具体的实现都是在包体中完成的 33 包体介绍包头和包体中的过程和函数的说明必须一致 其中包括子程序名和其参数名 以及参数的模式 34 包头CREATEORREPLACEPACKAGEpkg valIS Author MRC Created 2005 4 814 07 30 Purpose VALIDATETHEVALUESOFEACHENTITYINCLUDECUST ACCT SERV PROD ORDER 修改 王兴明2005 10 20 客户信息校验PROCEDUREprc val cust src i src filesetVARCHAR2 i sib process statusVARCHAR2 i batch noNUMBER 账户信息校验PROCEDUREprc val acct src i src filesetVARCHAR2 i sib process statusVARCHAR2 i batch noNUMBER 用户信息校验PROCEDUREprc val serv src i src filesetVARCHAR2 i sib process statusVARCHAR2 i batch noNUMBER 子产品信息校验PROCEDUREprc val prod src i src filesetVARCHAR2 i sib process statusVARCHAR2 i batch noNUMBER 优惠信息校验PROCEDUREprc val disc src i src filesetVARCHAR2 i sib process statusVARCHAR2 i batch noNUMBER END 35 CREATEORREPLACEPACKAGEBODYpkg valASPROCEDUREprc val cust src i src filesetvarchar2 i sib process statusvarchar2 i batch nonumber ASi process nameVARCHAR2 50 i error infoVARCHAR2 2000 错误详细描述 BEGIN END PROCEDUREprc val acct src i src filesetvarchar2 i sib process statusvarchar2i batch nonumber AS END 36 包的作用域包头中声明的任何对象都是在其作用域中 并且可在其外部使用包名作为前缀对其进行引用 beginarg1datatype xxx arg2datatype xxx package name procedure name arg1 arg2 end 37 十一 常用工具与调试方式 SQLNavigatorPL SQLDeveloper SQLNavigator调试SQLNavigator的调试很简单 大致分为以下几步 1 进入Debug模式 2 设置调试参数 3 正式调试 4 正常结束调试或者中断调试 38 Serveroutput按钮 可以显示调试过程中oracle执行的情况 Startdebugger按钮 点击按钮进入调试模式 调试必须点这个按钮 进入DEBUG模式 Stepover按钮 点击进入Console界面 调试控制台 39 设置调试参数需要完成两项工作 1 设置IN参数 2 设置提交方式 40 正式调试 直接运行 跑到断点处停下来 按钮F4 单步调试 遇到调用其他存储过程或者函数时跳过 按钮F8 单步调试 遇到调用其他存储过程或者函数时进入该存储过程或者函数中 按钮F7 当调试时进入了调用存储过程或者函数后 该按钮可以帮助跳出该调用存储过程或者函数 回到调试的上层函数中 按钮Shift F7 41 断点设置1 点击需要设置断点的代码行 点击右键 出现菜单 选择togglebreakpoint选项 该行应该成红色 此时断点即设置成功 2 点击需要设置断点的代码行 按按钮F5也能设置断点 设置断点之后 按F4按钮 程序可以连续运行 直到碰到设置的断点为止 当然 设置的断点需要在程序运行能够执行道的代码段上 否则程序将直接执行下去 42 察看变量1 将鼠标移到变量上方 一秒之后SQLNavigator就会显示出变量的值 移开鼠标 变量值消失 2 将鼠标移到变量上方 点击右键出现菜单 点击Addwatch选项 弹出对话框 输入变量名