第3章 PLSQL编程(1).ppt

1 解释数据库的四个基本概念 数据 数据库 数据库管理系统 数据库系统 p1DATA 数据就是事物的符号记录 是能够被计算机识别 存储和处理的信息 DB 长期存储在计算机内 有组织的 可共享的数据集合 DBMS 位于用户与操作系统之间的一层数据管理软件 DBS 指引入数据库后的计算机系统 它一般由操作系统 数据库管理系统 数据库 应用程序 数据库管理员 DatabaseAdministrator 简称DBA 和用户构成 3 简述数据库系统的三级模式结构与二级映像 p7 1 简述启动Oracle9i数据库的一般步骤 p322 简述启动Oracle9i模式中三个选项的区别 p323 简述关闭Oracle9i模式中四个选项的区别 p33 4 如何安装Oracle9i系统软件 如何正确地卸载Oracle9i软件 1 安装Oracle9iDatabase服务器端 2 安装客户端Oracle9iClient 3 再安装Oracle9iManagementandIntegration 卸载时 1 先关闭所有Oracle服务和程序 2 运行OracleUniversalInstaller 选择卸装产品 选中已安装的产品 单击 删除 3 然后修改注册表 删除所有与oracle有关的注册表选项 4 最后删除硬盘上的所有相关的包括文件在内的安装目录 重新启动系统 大型数据库概论IntroductionofLargeDatabase第3章PLSQL编程 1 学习目标1 掌握PL SQL语句块的使用 2 掌握PL SQL中变量的使用 3 掌握PL SQL中常用数据类型的使用 4 掌握PL SQL中常用数据类型的使用 5 掌握PL SQL中主要控制结构的使用 第5章PL SQL编程 PL SQL是Oracle9i在标准SQL基础上扩展的一种过程化数据库编程语言 既具有标准SQL语言的简洁性 又具有过程语言的灵活性 体现了Oracle9i数据库的特点 PL SQL是经过编译以后执行的 所以执行速度快于SQL语句 并减少了服务器和客户机之间的网络传输 提高了数据库系统的效率 PL SQL基础 语法规则是构成任何程序设计语言的基石 PL SQL也不例外 本节主要讨论PL SQL语句块 变量 常用数据类型 表达式和运算符 PL SQL语句块 PL SQL是一种结构化的语言 其程序结构的基本单位是 块 Block 组成程序的块可以顺序出现 也可以相互嵌套 每个块执行程序的一个独立功能 PL SQL语句块分匿名 anonymous 块和命名 named 块两种 匿名块是动态生成的 它只能执行一次 而命名块是具有名字的语句块 如存储在数据库内部的过程 函数 包和触发器等 可以执行多次 PL SQL语句块一般包含三个部分 声明部分 执行部分和异常处理部分 其中 执行部分是必需的 其它两个部分是可选的 定义PL SQL语句块的语法如下 DECLARE声明部分 主要声明变量 常量 自定义类型和游标等 BEGIN执行部分 包含各种合法的PL SQL语句 EXCEPTION异常处理部分 当程序出现错误时执行该部分语句 END 程序块结束 上面语法中各参数描述如下 n声明部分 声明变量 常量 游标和自定义类型 n执行部分 必须部分 描述了语句块所要完成的处理工作 可以使用SQL语句和过程性语句 n异常处理部分 对错误进行处理 如果没有发生错误 该部分中的代码将不会被执行 例3 1定义一个PL SQL语句块 setserveroutputon declarel textvarchar2 100 beginl text Hello World dbms output put line l text exceptionwhenothersthendbms output put line 引发了一个异常 raise end PL SQL变量是可以存储数据的内存单元 其内容随着程序的运行可以发生变化 PL SQL借助于变量可以与数据库进行通信 来自数据库中的信息可以赋给变量 而变量中的内容也可以被插入到数据库中 变量在PL SQL语句块的声明部分被声明 每一个变量都有一个特定的类型 该类型描述了可以在该变量中存储的数据类型 下一小节将讨论数据类型 PL SQL变量 变量的命名及声明 n必须以字母开头 其后可跟随一个或多个字母 数字 货币符号 下划线和 字符 n变量的最大长度是30个字符 n变量名中不能有空格 PL SQL语句块的声明部分声明变量 或常量 的语法如下 变量名 CONSTANT 数据类型 DEFAULT值 NOTNULL 值 注意 每一行只能声明一个变量 声明一个常量要加上关键字CONSTANT 而且必须初始化 初始化后常量值就不能更改了 例如 下面都是合法的变量声明 DECLARESnoVARCHAR2 6 SageNUMBER 2 NOTNull 18 Date Of TodayDATENOTNull sysdate SclassCHAR 20 NOTNull 计算机科学与技术41 3 1 3PL SQL常用数据类型 除了支持SQL标准的数据类型外 Oracle9i还为PL SQL提供了一些特殊的数据类型 其中常用的数据类型有以下几种 1 字符类型字符类型用来存储字符数据 常用的字符类型有 CHAR VARCHAR2 NCHAR NVARCHAR2 LONG LONGRAW和RAW等 CHAR L CHAR BYTE 存储定长字符串 其中可选项L 缺省值为1 是变量的长度 CHAR和BYTE分别用来指定L是以字符还是字节为单位 缺省为CHAR 一个字符可以包括一个或多个字节 这取决于系统的字符集设置 CHAR变量的最大长度是32767字节 CHAR数据库表列的最大长度是2000字节 VARCHAR2 L CHAR BYTE 存储变长字符串 VARCHAR2变量的最大长度是32767字节 VARCHAR2数据库表列的最大长度是4000字节 VARCHAR2还有两个子类型 STRING VARCHAR 它们有着与VARCHAR2相同的范围 使用它们主要是为了与ANSI ISO类型相兼容 建议使用VARCHAR2 NCHAR L 和NVARCHAR2 L 存储定长和变长国际字符集数据 取值范围分别与CHAR和VARCHAR2相同 NCHAR和NVARCHAR2中的L始终是按字符指定的 LONG变量存储变长字符串 这点与LONG数据库表列不同 LONG变量的最大长度是32760字节 比VARCHAR2变量少了7个字节 LONG数据库表列的最大长度是231字节 LONGRAW的精度与LONG相同 但LONGRAW存储的是二进制数据或字节字符串 RAW的精度与CHAR相同 但RAW存储的是二进制数据或字节字符串 LONG LONGRAW和RAW L 2 数值类型 用来存储整数 实数和浮点数 常用的数值类型有NUMBER PLS INTEGER和BINARY INTEGER NUMBER P S 存储整数或浮点数 其中P是精度 指数值中所有数字的个数 S是刻度 指小数点右边数字的个数 S取负数表示由小数点开始向左计算数字的个数 P和S都是可选的 但是如果指定了刻度 则必须指定精度 表5 1给出了不同的精度和刻度组合及其含义 四舍五入 NUMBER有许多子类型 DEC DECIMAL DOUBLEPRECISION FLOAT INTEGER INT NUMERIC REAL SMALLINT等 PLS INTEGER 存储有符号整数 精度范围是 231 231 但数据库表列不能存储PLS INTEGER数据 与NUMBER相比 PLS INTEGER占用较少的存储空间 并且可以直接进行算术运算 而NUMBER必须先转变成二进制才能进行算术运算 PLS INTEGER进行运算发生溢出时 会触发异常 BINARY INTEGER 存储类型与精度与PLS INTEGER相似 但操作比PLS INTEGER要慢 且运算发生溢出时 如果指派给一个NUMBER变量就不会触发异常 3 日期 时间类型日期 时间类型用来存储日期 时间和时间间隔 常用的日期 时间类型有DATE TIMESTAMP和INTERVAL DATE存储日期和时间数据 包括世纪 年 月 日 小时 分和秒 但不存储秒的小数部分 缺省格式为DD MON YY DATE数据占用7个字节 每个部分占一个字节 函数SYSDATE返回系统当前日期和时间 有效的日期和时间范围是公元前4712年1月1日 公元9999年12月31日 TIMESTAMP P 存储年 月 日 小时 分和秒 但还可存储秒的小数部分 其中P是秒小数部分的精度 范围是0 9 缺省值是6 TIMESTAMP P WITHTIMEZONETIMESTAMP类型的扩展 包含时区偏移 当地时间和格林尼治时间的差异 例如 以下脚本可以用来显示系统当前日期 时间及时区偏移 DECLAREV TIMETIMESTAMP 3 WITHTIMEZONE BEGINSELECTSYSDATEINTOV TIMEFROMDUAL DBMS OUTPUT PUT LINE V TIME END INTERVALDAY P1 TOSECOND P2 存储天数 小时 分钟和秒之间的时间间隔 其中P1 P2分别指定天和秒的数字位数 范围为0 9 缺省值分别是2和6 4 Boolean类型 存储TRUE FALSE和NULL declarev intevalINTERVALDAY 7 TOSECOND 5 beginv inteval INTERVAL 45 12 10 222 DAYTOSECOND dbms output put line v inteval end declarev intevalINTERVALYEAR 3 TOMONTH beginv inteval INTERVAL 123 2 YEARTOMONTH dbms output put line v inteval end 存储最大尺寸不超过4GB的无结构数据块 如文本 图像 声音和视频等 常用的LOB类型有BFILE BLOB CLOB和NCLOB BFILE 在数据库外的操作系统文件中存储大型的二进制文件 每个BFILE变量存储着一个文件定位器 包含一个路径别名来指定一个完整路径 用来指向服务器上的大型二进制文件 BLOB 在数据库内存储大型的二进制对象 每个BLOB对象存储一个定位器 指向大型的二进制对象 CLOB 在数据库内存储大型的字符型数据 每个CLOB对象存储一个定位器 指向大型的字符型数据 NCLOB 在数据库内存储大型的NCHAR类型数据 每个NCLOB对象存储一个定位器 指向大型的NCHAR类型数据 5LOB类型 每个PL SQL基类型 如上述数据类型 指定了一组值和一组适应该类型操作的约定 子类型不是一个新类型 而是基类型的候选名称 有着与基类型同样的操作约定 自定义子类型的语法 SUBTYPE子类型名IS基类型 NOTNULL 例如 以下脚本定义了一个子类型MYTYPE和MYTYPE类型的变量MYTEXT DECLARESUBTYPEMYTYPEISVARCHAR2 6 MYTEXTMYTYPE 6自定义子类型 7 记录类型 记录是PL SQL中的一个复合类型 前面所讲述的都是标量类型 标量类型的内部没有可以单独操纵的元素 它与数据库表列的数据类型一般是一致的 而复合类型的内部含有可以单独操纵的元素 记录类似于数据库表列的集合 表的 ROWTYPE属性实质上就是一种记录类型 记录类型 PL SQL记录类型类似于C语言中的结构体 声明记录类型的语法如下 DECLARETYPE记录名ISRECORD 字段名1 NOTNULL 值 字段名n NOTNULL 值 TYPE PL SQL变量可用来处理数据库表中的数据 该变量必须拥有与数据库表列相同的类型 例如 要定义与system student表中Sname列 类型为VARCHAR2 6 相同类型的变量studentname可以声明如下 DECLAREstudentnameVARCHAR2 6 但若Sname列的类型发生改变 则studentname变量必须重新定义 这是十分耗时和容易出错的 解决的办法是使用数据库表列的 TYPE属性 它对应着数据库表列的数据类型 如 DECLAREstudentnamesystem student Sname TYPE 表示变量studentname的数据类型是基于表system student中的Sname列的数据类型 如果Sname列的定义改变了 则studentname的数据类型也随之改变 ROWTYPE实际应用中 经常要将PL SQL中的一个记录类型声明为对应于一个数据库表的数据行 如果该表有若干列 则声明记录时就需要若干行 这样的记录声明非常烦琐 为此 PL SQL提供了 ROWTYPE运算符 例如 要定义一个与表system student数据行相同的记录变量V student 可以声明如下 DECLAREV studentsystem student ROWTYPE 表示记录变量V student是基于表system student数据行的数据类型 若表定义改变 则V student也随之改变 用来给变量赋值及对操作数处理 1 赋值运算符 作用是将赋值运算符右边表达式的值赋给其左边的变量 2 算术运算符 用于进行加 减 乘 除 乘方等算术运算 包括加 减 乘 除 乘方 3 1 4PL SQL运算符 3 关系运算符 用于将一个表达式与另一个表达式进行比较 包括等于 不等于或 大于 小于 小于等于 介于BETWEEN AND 测试IN 模糊匹配LIKE 是否非空ISNULL 4 逻辑运算符 用于对两个布尔表达式进行逻辑运算 包括逻辑与AND 逻辑或OR 逻辑非NOT 5 字符串连接运算符 作用是将两个字符串连接起来 3 1 5PL SQL表达式 由操作数和运算符构成 操作数可以是一个变量 常量或函数 组成表达式的操作数和运算符一起决定了该表达式的类型 字符 数值 日期等 表达式可出现在赋值运算符的右边或者作为一个PL SQL语句的一部分 PL SQL的控制结构包括顺序 选择 NULL和循环等四种结构 除了顺序结构外 PL SQL主要通过选择 NULL和循环结构来控制和改变程序执行的逻辑顺序 从而实现复杂的运算或控制功能 此外 PL SQL还提供GOTO转移语句 3 2PL SQL控制结构 3 2 1顺序结构 按照语句出现的先后顺序执行 这与任何过程化的语言相同 3 2 2选择结构 PL SQL中常用的选择结构有以下两种形式 1 IF语句语法 IF条件1THEN语句1 ELSIF条件2THEN语句2 ELSE语句n 1 ENDIF 2 CASE语句语法 CASE条件选择器WHEN值1THEN语句1 WHEN值nTHEN语句n ELSE语句n 1 ENDCASE 例3 2根据城市的名称查找在该城市的代理人的名称 DECLARECITYVARCHAR2 6 南昌 BEGINCASECITYWHEN 扬州 THENDBMS OUTPUT PUT LINE 老朱 WHEN 徐州 THENDBMS OUTPUT PUT LINE 老纪 WHEN 唐山 THENDBMS OUTPUT PUT LINE 老单 WHEN 南昌 THENDBMS OUTPUT PUT LINE 小蔡 ENDCASE END 3 2 3NULL结构 也称空结构 显式指明不进行任何操作 如 SETSERVEROUTPUTON DECLAREV MarksNumber V PassNumber 0 BEGINIfV Marks 60THENNULL ELSEV Pass V Pass 1 ENDIF dbms output put line v pass END 3 2 4循环结构 PL SQL中常用的循环结构有以下三种形式 1 LOOP EXIT EXITWHEN ENDLOOP语法 LOOP循环体ENDLOOP 这种循环结构简称LOOP循环 终止条件通过在循环体中加入EXIT或EXITWHEN来实现 例3 3使用LOOP循环输出1 10的数值 SETSERVEROUTPUTON DECLARENUMBINARY INTEGER 1 BEGINLOOPDBMS OUTPUT PUT LINE NUM NUM NUM 1 IFNUM 10THENEXIT ENDIF ENDLOOP END 本程序使用EXIT来终止循环 也可以使用EXITWHEN来终止循环 程序如下 SETSERVEROUTPUTON DECLARENUMBINARY INTEGER 1 BEGINLOOPDBMS OUTPUT PUT LINE NUM NUM NUM 1 EXITWHENNUM 10 ENDLOOP END 2 WHILE LOOP ENDLOOP语法 WHILE条件LOOP循环体ENDLOOP 这种循环结构简称为WHILE循环 表示当条件成立时执行循环体 条件不成立时退出循环 例3 4使用WHILE循环输出1 10的数值 SETSERVEROUTPUTON DECLARENUMBINARY INTEGER 1 B