oracle高级PLSQL编程

《oracle高级PLSQL编程》由会员分享，可在线阅读，更多相关《oracle高级PLSQL编程（59页珍藏版）》请在金锄头文库上搜索。

1、Oracle10g 应用技术,目标,PL/SQL介绍 PL/SQL语言 PL/SQL的特点 PL/SQL变量、常量和数据类型 PL/SQL基本程序结构和语句 总结,PL/SQL介绍,SQL(Structure Query Language)的含义是结构化查询语句，最早由Boyce和Chambedin在1974年提出，称为SEQUEL语言。1976年，IBM公司的San Jose研究所在研制关系数据库管理系统System R时修改为SEQUEL2，即目前的SQL语言。1976年，SQL开始在商品化关系数据库管理系统中应用。1982年美国国家标准化组织ANSI确认SQL为数据库系统的工业标准。目前

2、，许多关系型数据库供应商都在自己的数据库中支持SQL语言，如：Access、Sybase、SQL Server、Infomix、DB2等。,PL/SQL语言,PL/SQL语言由以下几部分组成： (1) 数据定义语言(DDL) 用于执行数据库的任务，对数据库以及数据库中的各种对象进行创建、删除、修改等操作。基本的DDL命令及功能如表所示 。,(2) 数据操纵语言 用于操纵数据库中各种对象、检索和修改数据。需要注意的是，使用DML语句对某一种数据对象进行操作时，要求必须拥有该对象的相应操作权限或拥有相应的系统权限。DML包括的主要语句及功能如表所示。,(3) 数据控制语言 用于安全管理、确定哪些用

3、户可以查看或修改数据库中的数据，DCL包括的主要语句及功能如表所示。,用PL/SQL编制的程序代码如下所示 DECLARE e_ename varchar2(8):=jerry; e_job varchar2(10):=player; e_empno number(10):=1000; /*定义变量类型*/ BEGIN UPDATE emp SET ename=e_ename WHERE empno=e_empno; /*更新员工表*/ IF SQL%NOTFOUND THEN /*检查记录是否存在，如果不存在就插入记录*/ INSERT INTO emp(empno,ename,job) V

4、ALUES(e_empno,e_ename,e_job); commit; END IF; END; 使用PL/SQL的主要有以下好处： (1) 有利于客户/服务器环境应用的运行。 (2) 适合于客户环境。,PL/SQL的特点,Oracle对PL/SQL进行了扩展，在许多方面增强了PL/SQL的功能，主 要体现在以下方面： (1)SQL和PL/SQL编译器集成PL/SQL现在支持SQL所有范围的语法， 如INSERT、UPDATE、DELETE等。 (2) 支持CASE语句和表达式。 (3) 继承和动态方法释放。 (4) 类型进化。 (5) 新的日期/时间类型。 (6) PL/SQL代码的本地

5、编译。 (7) 改善了全球和国家语言支持。 (8) 函数和游标表达式。 (9) 多层集合。 对批操作的增强。,使用PL/SQL，用户可以使用SQL语句操作Oracle数据和用于处理数 据的流控语句，而且可以声明变量和常量、定义过程和函数、跟踪 运行错误。 PL/SQL将SQL的数据操纵功能同过程语言的数据处理功能结合在一 起。此外，PL/SQL还具有以下特性： 1. 数据抽象 数据抽象可以从数据结构中提取必要的属性，忽略不必要的细节。 一旦设计了数据结构，就可以忽略它的细节，从而设计操纵数据结 构的算法。 2. 信息隐藏 使用信息隐藏，用户只能看到算法和数据结构设计的给定层次上的 信息。信息隐

6、藏使高层设计决策与底层设计细节相分离。,和所有其他程序设计语言一样，PL/SQL也有一个字符 集。用户能从键盘上输入的字符都是PL/SQL的字符。此 外，在某些场合，还有使用某些字符的规则。 合法字符 在使用PL/SQL进行程序设计时，可以使用的有效字符包 括以下3类： 所有的大写和小写英文字母； 数字09； 符号() + * / = ! ；：. % , # ? 。 PL/SQL标识符的最大长度为30个字符，不区分大小写。 但是适当地使用大小写可以提高程序的可读性。,运算符,Oracle提供了三类运算符：算术运算符、关系运算符和逻 辑运算符。 1. 算术运算符 算术运算符执行算术运算。算术运算

7、符有： +（加）、-（减）、*（乘）、/（除）*(指数)和（连 接）。 其中(加)和(减)运算符也可用于对DATE(日期)数据类 型的值进行运算。 【例5.1】求员工的工龄。 SELECT (SYSDATE - hiredate) AS 工龄 FROM emp; 其中：SYSDATE是当前系统时间，hiredate是入职日期。,2. 关系运算符 关系运算符 (又称比较运算符)有下面几种： (1) (等于)、或! = (不等于)、(大于)、=(大于等于)、=(小于 等于)； (2) BETWEENAND(检索两值之间的内容)； (3) IN(检索匹配列表中的值)； (4) LIKE(检索匹配字符

8、样式的数据)； (5) IS NULL(检索空数据)。 关系运算符用于测试两个表达式值满足的关系，其运算结果为逻辑 值TRUE、FALSE 及 UNKNOWN。,3. 逻辑运算符 逻辑运算符用于对某个条件进行测试，运 算结果为 TRUE 或 FALSE。Oracle提供的 逻辑运算符有： (1) AND(两个表达式为真则结果为真) (2) OR(只要有一个为真则结果为真) (3) NOT(取相反的逻辑值),其他符号,PL/SQL为支持编程，还使用其他一些符号。表列 出了部分符号，它们是最常用的，也是使用 PL/SQL的所有用户都必须了解的。,PL/SQL变量、常量和数据类型,变量 变量就是指可

9、以由程序读取或赋值的存储单元。变量用 于临时存放数据，变量中的数据随着程序的运行而变化。 1. 变量的声明 数据在数据库与PL/SQL程序之间是通过变量进行传递 的。变量通常是在PL/SQL块的声明部分定义的。每个变 量都有一个特定的类型，变量的类型定义了变量可以存 放的信息类别。PL/SQL变量可以与数据库列具有同样的 类型。 变量名必须是一个合法的标识符，变量命名规则如下：,(1) 变量必须以字母(AZ)开头 (2) 其后跟可选的一个或多个字母、数字(09)或特殊字符$、# 或_ (3) 变量长度不超过30个字符 (4) 变量名中不能有空格,1. 变量的声明 表给出了几个变量名实例并评价了

10、其合法性。 在使用变量前，首先要声明变量。变量定义的基本格式 为：(长度):=； 例如：定义一个长度为10的变量count，其初始值为1， 是varchar2类型。 count varchar2(10) := 1;,2. 变量的属性 变量有名字及其数据类型两个属性。变量名用于标识该变量，变量 的数据类型确定了该变量存放值的格式及允许的运算。%用来表示属 性提示符。 (1) %TYPE %TYPE属性提供了变量和数据库列的数据类型。在声明一个包含数 据库值的变量时非常有用。例如，在EMP表中包含”ename”列，为了声明一个变量”e_ename”与”ename”列具有相同的数据类型，可使用点和%

11、TYPE属性，格式如下： e_ename emp.ename %TYPE; 使用%TYPE声明具有以下两个优点： 不必知道ename列的确切的数据类型； 如果改变了ename列的数据库定义，e_ename的数据类型在运行时会自动进行修改。,2. 变量的作用域 变量的作用域是指可以访问该变量的程序部分。对于PL/SQL变量来说，其作用域就是从变量的声明到语句块的结束。当变量超出了作用域时，PL/SQL解析程序就会自动释放该变量的存储空间。,常用数据类型,1. VARCHAR类型 语法格式： var_field VARCHAR(n); 其中长度值n是本变量的最大长度且必须是正整数，例如： var_

12、field VARCHAR(11); 在定义变量时，可以同时对其进行初始化，例如： var_field VARCHAR(11):=Hello world; 2. NUMBER类型 NUMBER数据类型可用来表示所有的数值类型。 语法格式： num_field NUMBER(precision,scale); 其中precision表示总的位数；scale表示小数的位数，scale缺省表示小 数位为0。如果实际数据超出设定精度则出现错误。例如： num_field NUMBER(10,2); num_field是一个整数部分最多使8位，小数部分最多是2的变量。,3. DATE 用来存放日期时间类

13、型数据，用7个字节分别描述年、月、日、时、 分、秒。 语法格式： date_field DATE; 日期缺省格式为DD-MON-YY，分别对应日、月、年，例如22-9月- 2011。注意，月份的表达要用英文单词的缩写格式。 4. BOOLEAN 逻辑型(布尔型)变量的值只有true(真)或false(假)。逻辑型变量一般用 于判断状态，然后根据其值是“真”或是“假”来决定程序执行分支。关系表达式的值就是一个逻辑值。,数据类型转换,PL/SQL可以进行数据类型之间的转化。常见的数据类型之间的转化函数如下： (1) TO_CHAR：将NUMBER和DATE类型转换成VARCHAR2类型。 (2)

14、TO_DATE：将CHAR转换成DATE类型。 (3) TO_NUMBER：将CHAR转换成NUMBER类型。 此外，PL/SQL还会自动地转换各种类型，如下例所示： DECLARE e_sal varchar2(6); BEGIN SELECT MAX(sal) INTO e_sal FROM emp; END;,PL/SQL基本程序结构和语句,PL/SQL的基本逻辑结构包括顺序结构、条件结构和循环结构。除了顺序执行的语句外，PL/SQL主要通过条件语句和循环语句来控制程序执行的逻辑顺序，这就是所谓的控制结构。控制结构是所有程序设计语言的核心。检测不同条件并加以处理是程序控制的主要部分。,条

15、件结构 1. IF逻辑结构 IF逻辑结构有3种表达式。 (1) IF-THEN 语法格式： IF Boolean_expression THEN /*条件表达式*/ Run_expression /*条件表达式为真时执行*/ END IF; 这个结构用于测试一个简单条件。如果Boolean_expression条件表达 式为true，则执行Run_expression操作。IF-THEN语句可以用流程图 表示。,基本流程图,例:查询工资1000的员工人数。 DECLARE v_num number(3); BEGIN SELECT COUNT(*) INTO v_num FROM emp WH

16、ERE sal1000; IF v_num0 THEN dbms_output.put_line(薪水大于1000的人数为： | TO_CHAR(v_num); END IF; END; IF-THEN语句可以嵌套使用,例:判断员工工资1000的人数是否超过6人。 DECLARE v_num number(3); BEGIN SELECT COUNT(*) INTO v_num FROM emp WHERE sal1000 ; IF v_num0 THEN IF v_num6 THEN dbms_output.put_line(工资大于1000的人数超过了6人); END IF; END IF; END;,(2) IF-THEN-ELSE 语法格式： IF Boolean_expression THEN /*条件表达式*/ Run_expression /*条件表达式为真时执行*/ EL