PLSQL开发(精).ppt

1、2020/7/28,*,第八章,PL/Sql开发,2020/7/28,*,本章要点,PL/SQL基本概念 PL/SQL的变量 PL/SQL的基本语法 Oracle各种程序单元 使用游标取出多条数据 异常处理,2020/7/28,*,PL/SQL概述,什么是PL/SQL PL/SQL也是一种程序语言,被称作支持SQL的程序语言(Program Language),是Oracle数据库对SQL语句的扩展，在普通的SQL语言中增加了编程语言的特点 数据操作和查询语句被包含在PL/SQL代码的过程性单元中，经过逻辑判断、循环等操作完成复杂的功能或者计算,2020/7/28,*,PL/SQL的优点,使用

2、PL/SQL可以编写具有很多高级功能的程序,虽 然这些功能可以通过多个SQL语句来完成同样的功 能,但是PL/SQL具有如下的优点: 使一组语句功能形成模块化程序开发 使用过程性语言控制程序结构 可以对程序中的错误进行处理 具有较好的可移植性 集成在数据库中,调用更快 减少了网络的交互,有助于提高程序性能,2020/7/28,*,PL/SQL提供的新特性,PL/SQL提供了一些新的特性,可以进行复杂的信息处理 软件包 触发器 存储过程 函数 异常处理 PL/SQL可以使用所有的SQL数据操作,游标控制和事务控制命令,以及所有的SQL函数和运算符.PL/SQL完全支持SQL数据类型,2020/7

3、/28,*,SQL,SQL*PLUS,PL/SQL之间的关系,可以把oracle数据库看作餐馆的厨房,而SQL*PLUS是将菜单(脚 本,命令或程序)送进厨房(即数据库)的服务员.在厨房中有两 个厨师,SQL 和PL/SQL.作为服务员的SQL*PLUS知道它可以 处理那些命令,那些命令要交给厨师处理. 在SQL*PLUS提示符下输入的执行命令或程序就象顾客点的 菜.对于每个顾客点的菜,厨师都知道如何进行处理,就像在厨 师心中的菜谱一样,PL/SQL也存储常用命令的食谱(这些元素 称为触发器,存储函数,存储过程,软件包). 有些大菜需要多个厨师共同处理,大多数的程序都是结合了 SQL和PL/S

4、QL,并在他们之间来回的传递信息,从而处理脚本或程 序.顾客点的菜作好之后,再由作为服务员的SQL*PLUS将执行的结 果显示给用户,2020/7/28,*,PL/SQL提高了性能,Database,Database,SQL,SQL,SQL,SQL,SQL IF.THEN SQL ELSE SQL END IF; SQL,应用程序,应用程序,2020/7/28,*,PL/SQL块的基本结构,PL/SQL中起作用的部分都是由基本块组成的.基本块有四个 组成部分 声明部分:DECLARE 可选部分 变量、常量、游标、用户定义异常声明 执行体开始部分:BEGIN 必要部分 SQL语句 PL/SQL语

5、句 异常处理部分:EXCEPTION 可选部分 程序出现异常时，捕捉异常并处理异常 执行体结束:END; 必要部分,2020/7/28,*,PL/SQL块例子,DECLARE v_dept_id employees.department_id%TYPE; BEGIN SELECT department_id INTO v_dept_id FROM employees WHERE employee_id = 100; DELETE departments WHERE department_id = v_dept_id; COMMIT; EXCEPTION WHEN OTHERS THEN ROL

6、LBACK; INSERT INTO exception_table (message) VALUES (Some error occurred in the database.); COMMIT; END;,2020/7/28,*,PL/SQL环境,PL/SQL Block,PL/SQL 引擎,oracle 数据库,PL/SQL Block,Procedural Statement Executor,non-SQL,SQL,SQL Statement Executor,SQL 引擎,在数据库执行PL/SQL程序的时候,PL/SQL语句和SQL 语句被分别解析和执行的。PL/SQL块被数据库内

7、 部的PL/SQL引擎提取，将SQL语句取出送给Oralce的 SQL引擎。两种语句分别在两种引擎中分析处理，在 数据库内部完成数据交互，处理的过程,2020/7/28,*,在PL/SQL中处理变量,在声明部分声明和初始化变量 在执行部分为变量赋新值，或在表达式中使用变量 在异常处理部分也可以使用变量 通过参数把值传递到PL/SQL 块中 通过输出变量或者参数将值传出PL/SQL块,2020/7/28,*,声明变量和常量：语法,定义的标示符名称应遵循命名规则 在声明常量和变量的时候可以为其设置初始化值,也可以设置NOT NULL 可以使用赋值运算符(:=)或者DEFAULT保留字来初始化标识符

8、 在声明时，每行只能声明一个标识符,identifier CONSTANT datatype NOT NULL := | DEFAULT expr; 例如: v_total_salNUMBER(9,2) := 0; c_tax_rateCONSTANT NUMBER(3,2) := 8.25; v_genderCHAR(1); v_validBOOLEAN NOT NULL := TRUE;,2020/7/28,*,PL/Sql中的变量类型,简单变量 复合（组合）变量 外部变量,2020/7/28,*,简单变量,简单变量不包括任何组件，只能保存一个值 基本类型包括三大类：字符,数字,日期 BI

9、NARY_INTEGER 整形数字 NUMBER (precision, scale) 数字类型 CHAR (maximum_length) 定长字符类型 VARCHAR2(maximum_length) 变长字符类型 DATE 日期类型 LONG 长字符类型 LONG RAW 长二进制类型 CLOB / BLOB / BFILE 大对象类型(字符大对象,二 进制大对象,操作系统文件大对象) BOOLEAN 布尔类型,有效值为 TRUE,FALSE,NULL,2020/7/28,*,简单变量的声明,v_genderCHAR(1); v_countBINARY_INTEGER := 0; v_t

10、otal_salNUMBER(9,2) := 0; v_order_dateDATE := SYSDATE + 7; c_tax_rateCONSTANT NUMBER(3,2) := 8.25; v_validBOOLEAN NOT NULL := TRUE;,2020/7/28,*,复合数据类型,复合变量也叫做组合变量.在复合变量中包含多个内部的组件,每个组件都可以单独存放值.一个复合变量可以存放多个值 与简单变量类型不同,复合变量类型不是数据库中已经存在的数据类型,所以复合变量在声明类型之前,首先要创建使用到的复合类型,然后将变量声明为复合变量 复合数据类型： PL/SQL TABLES

11、 表类型 PL/SQL RECORDS 记录类型 复合类型被创建后,可以被使用多次定义多个变量,2020/7/28,*,复合数据类型- TABLE,表类型类似于其他编程语言中的数组类型 由两个组件组成： 数据类型为BINARY_INTEGER(整形数字)的主键 数据类型为一个确定的简单类型的列 Table类型没有长度限制，可以动态增长.表类型中的第二部分类似与数组中的值,这个部分必须是一个已经确定的简单类型,不能是其他的复合类型 表类型的结构很像数组.第一部分使一个按1递增的整形数字,起到数字索引的作用,第二部分使一种确定的简单类型,用来存放每个索引号对应的具体的数值,2020/7/28,*,

12、PL/SQL TABLE 结构,主键列 . 1Jones 2Smith 3Maduro . BINARY_INTEGER标量,2020/7/28,*,声明一个PL/SQL TABLE,TYPE type_name IS TABLE OF scalar_datatype NOT NULL INDEX BY BINARY_INTEGER; identifiertype_name;,. TYPE name_table_type IS TABLE OF VARCHAR2(16) INDEX BY BINARY_INTEGER; v1_namename_table_type; v2_namename_t

13、able_type; .,语法：,例子：,2020/7/28,*,PL/SQL RECORDS,复合类型中的RECODES类型是由多个组件组成的一种类型.包含一个或几个组件，每个组件称为一个域(FIELD)，域的数据类型可以是简单变量类型、另一个RECORD类型或PL/SQL的TABLE类型 在使用RECORD变量时把多个域的集合作为一个逻辑单元使用,对记录类型变量赋值或引用,都需要使用“记录变量名.域名”的方式来实现 主要用于从表中取出查询到的行数据,2020/7/28,*,PL/SQL RECORD结构,记录类型可以包含一个或多个域,每个域相当于记录类型变量的一个属性.在使用记录变量类型时

14、,实际上是对记录类型变量的属性进行操作.每个域都可以是不同的数据类型,存放不同类型的数据,Field1 (数据类型)Field2 (数据类型)Field3 (数据类型),2020/7/28,*,声明PL/SQL RECORD,语法： TYPE type_name IS RECORD (field_name1 field_type NOT NULL :=|DEFAULT expr, field_name2 field_type NOT NULL :=|DEFAULT expr,.); identifiertype_name; 例子： . TYPE emp_record_type IS RECOR

15、D (last_nameVARCHAR2(25), first_nameVARCHAR2(25), salNUMBER(8) ); emp_recordemp_record_type; .,2020/7/28,*,%TYPE与%ROWTYPE,除了象前面那样直接为变量声明一个确定的简单类型或者已经创建好的复合类型外,PL/SQL也支持另外的两种声明变量类型的方法,通红%TYPE和%ROWTYPE属性来声明变量类型,2020/7/28,*,%TYPE 属性,通过%TYPE属性声明一个变量，变量将遵循下面的类型声明： 一个已经声明过的变量类型 一个数据库中的表的字段定义 通过%TYPE类型声明新变

16、量的类型,实际上就是将参照的变量或表中的字段类型作为新变量的类型,新变量的类型与它所参照的类型完全相同,并且保持同步 可以作为%TYPE的前缀的可以是 数据库表和列 前面声明的变量名称 PL/SQL在运行程序时确定变量的数据类型和大小,2020/7/28,*,%TYPE属性：例子,使用%TYPE 属性的好处： 可能不知道数据库中字段的数据类型 数据库中字段的数据类型可以在运行时已被改变 和前面声明过的变量的类型保持一致,. v_last_names_emp.last_name%TYPE; v_first_name s_emp.first_name%TYPE; v_balanceNUMBER(7

17、,2); v_minimum_balancev_balance%TYPE := 10; .,2020/7/28,*,%ROWTYPE属性,与%TYPE作用类似，用于定义不确定的类型 变量类型将定义为由数据库的表的字段集合构成的RECORD类型 %ROWTYPE的前缀是数据库的表名，或者另一个已经定义好的RECORD变量 RECORD中的域，与表的字段的名称,个数,数据类型,以及长度完全相同,可以用来存放从表中取出的一条记录的所有的字段值(select * from 表),2020/7/28,*,%ROWTYPE 属性：优点,数据库中表字段的数据类型和数目可能不知道 数据库中表字段的个数和数据类

18、型会在运行中改变,. dept_records_dept%ROWTYPE; emp_records_emp%ROWTYPE; .,2020/7/28,*,PL/SQL 块语法规则,语句可以写在多行,就象SQL语句一样 各个关键字,字段名称等,通过空格分隔 每条语句必须通过分号结束,包括PL/SQL结束部分的END关键字后面也需要加分号 标识符的规定： 最多可以包含30个字符 不能包含保留字，若有使用双引号括起来 必须以字母字符开始 不能与数据库的表或者列名称相同,2020/7/28,*,PL/SQL 语法规则,在PL/SQL程序中出现的字符和日期必须用单引号括起来 数字可以是简单值或科学计数法

19、表示 V_SAL NUMBER(4) := 2000; V_YEAR NUMBER(4) := V_SAL * 12; 在PL/SQL中也要养成添加注释的习惯,注释可以是 /* 和*/之间的多行注释 单行注释，以 - 开始,2020/7/28,*,赋值语句,使用“:=”作为赋值符号，而不是“=” 语法: identifier := expr; plsql_table_name (primary_key_value) := expr; plsql_record_name.field_name := expr;,2020/7/28,*,变量赋值,设置保存薪水的变量 v_sal:= 5000; v_

20、married := true; 在PL/SQL TABLE的第一个值中保存名字“Smith /* 前面定义好的table型的变量 TYPE name_table_type IS TABLE OF VARCHAR2(16) INDEX BY BINARY_INTEGER; v1_namename_table_type; v2_namename_table_type; */ v1_name(1) := Smith; 把一个员工的基本信息保存到PL/SQL RECORD中 /*前面定义好的RECORD型的变量 TYPE emp_record_type IS RECORD (last_nameVAR

21、CHAR2(25), first_nameVARCHAR2(25), salNUMBER(8); emp_record emp_record_type; */ emp_record.last_name:= Smith; emp_record.first_name:= zhang; emp_record.sal := 5000;,2020/7/28,*,嵌套块和变量作用域,PL/SQL程序也叫做PL/SQL程序块,在程序块中可以嵌套另一个程序块,外部的程序块叫做“父块”或“外部块”,嵌套的块叫“子块”或“嵌套块” 只要是允许执行语句的地方，就可以使用嵌套语句 嵌套块也被当作一个语句 异常部分也可

22、以包含嵌套块，嵌套块也可以包括异常部分 对象的作用域指的是可以应用对象的程序范围 标识符可见的范围： 子块可以向上查看父块的标识符 父块不能向下查看到子块的标识符,2020/7/28,*,嵌套块和变量作用域,DECLARE x BINARY_INTEGER; BEGIN . DECLARE y NUMBER; BEGIN . END; . END;,2020/7/28,*,嵌套块中的变量赋值,set serveroutput on;declare v_weight number(3) := 100;begin -嵌套块- declare v_weight number(3) := 1; beg

23、in v_weight := v_weight +1; dbms_output.put_line(在嵌套块中v_weight的值是: | v_weight); end; - v_weight := v_weight +1; dbms_output.put_line(在嵌套块外v_weight的值是: | v_weight);end;/,2020/7/28,*,PL/SQL语句中的操作符,逻辑操作符 数学操作符 连接操作符 指数操作符,2020/7/28,*,PL/SQL中的操作符,设置Boolean 标志的值 v_equal:= (v_1 = v_2); 为循环增加计数器的值 v_count:

24、= v_count + 1;,2020/7/28,*,set serveroutput on;declare v_1 number(3) := 100; v_2 number(3) := 100; v_valid boolean ;begin v_valid := (v_1=v_2); if(v_valid) then dbms_output.put_line(真); else dbms_output.put_line(假); end if; end;/,2020/7/28,*,PL/SQL中的函数,可用： 数字函数 字符函数 类型转换函数 日期函数 不可用： 组函数,2020/7/28,*,

25、PL/SQL中的函数,计算姓名字符数： set serveroutput on;declare v_name varchar2(20) := aaa; v_addr varchar2(20) := 北京; v_count number(3); begin v_count := length(v_addr); dbms_output.put_line(v_count);end;/,2020/7/28,*,数据类型转换,把数据转换为可操作的数据类型 转换函数： TO_CHAR TO_DATE TO_NUMBER,2020/7/28,*,数据类型转换,set serveroutput on;decl

26、are v_1 varchar2(20) ;begin -v_1 := USER|: |TO_CHAR(SYSDATE); v_1 := USER|: |SYSDATE; dbms_output.put_line(v_1);end;/,2020/7/28,*,PL/SQL中的SQL命令,在PL/SQL程序块中可以使用各种SQL命令，但是使用的方法根据命令不同也各不相同 使用SELECT 命令，可以从数据库中取出单行数据,语法略有变化 使用DML命令，修改数据库中的行,没有变化 使用COMMIT 或ROLLBACK 命令控制事务,没有变化 通过EXECUTE IMMEDIATE，执行DDL和DC

27、L语句,2020/7/28,*,查询数据,在PL/SQL中通过SELECT从数据库中检索数据： SELECTselect_list INTO ariable_name | record_name FROMtable WHEREcondition; 必须使用INTO子句,用于将查询出的数据传递给变量 查询必须并且只能返回一行 可以使用完整的SELECT 语法,2020/7/28,*,查询数据,取出某个员工的雇佣时间和所属部门,drop table employees;create table employees( empl_id number(5) primary key, hire_date

28、date, department_id number(5);insert into employees values(1000,1-2月-1999,2000);insert into employees values(1001,2-2月-1999,2000);insert into employees values(1002,3-2月-1999,2000);insert into employees values(1003,4-2月-1999,2000);commit;set serveroutput on;declare v_hire_date employees.hire_date%typ

29、e; v_department_id employees.department_id%type;begin select hire_date,department_id into v_hire_date,v_department_id from employees where empl_id =1000; dbms_output.put_line (v_hire_date |- | v_department_id );end;/,2020/7/28,*,查询数据,返回员工的平均薪水,drop table employees;create table employees( empl_id num

30、ber(5) primary key, hire_date date, salary number (8,2), department_id number(5);insert into employees values(1000,1-2月-1999,2000,2000);insert into employees values(1001,2-2月-1999,5000,2000);insert into employees values(1002,3-2月-1999,8900,2000);insert into employees values(1003,4-2月-1999,6700,2000);commit;set serveroutput on;declare v_avg_salary employees.salary%type;begin select avg(salary) into v_avg_salary from employees; dbms_output.put_line (平均工资是 |: | v_avg_salary );end;/,2020/7/28,*,查询异常,PL/SQL 中的SE