第一章 PL/SQL 介绍

1. 第一章 PL/SQL介绍 • 1.1　什么是PL/SQL 结构化查询语言(Structured Query Language，简称SQL)是用来访问关系型数据库一种通用语言，它属于第四代语言（4GL），其执行特点是非过程化，即不用指明执行的具体方法和途径，而是简单的调用相应语句来直接取得结果即可。 例：delete from students where major=‘Nutrition’; 然而，对于有些复杂的业务流程又要求相应的程序来描述，那么4GL就有些无能为力了。 PL/SQL通过增加了用在其它过程性语言中的结构来对SQL进行了扩展，使得它不仅仅是一个数据库查询语言，而且也是一个编程语言。

2. PL/SQL代表面向过程化的语言与SQL语言的结合，在编写PL/SQL代码时可以在SQL语句中使用：PL/SQL代表面向过程化的语言与SQL语言的结合，在编写PL/SQL代码时可以在SQL语句中使用： • (1)变量和类型 • (2)控制语句 • (3)过程和函数 • (4)对象类型和方法 • PL/SQL语言实现了将过程结构与Oracle SQL的无缝集成 • 例：修改一个学生的专业，如果没有该学生，就插入一个新的记录 • --3gl_4gl.sql • DECLARE • v_newmajor VARCHAR2(10):=‘History’; • v_firstname VARCHAR2(10):=‘Scott’; • v_lastname VARCHAR2(10):=‘Urman’;

3. BEGIN • update students • set major=v_newmajor • where first_name=v_last_name and last_name=v_last_name; • If SQL%notfound then • insert into students(ID,FIRST_NAME,LAST_NAME,MAJOR) VALUES (student_sequence.NEXTVAL,v_firstname, • v_lastname,v_newmajor); • End if; • End;

4. Oracle数据库服务器 Oracle数据库服务器 SQL SQL SQL • PL/SQL与网络传输 SQL SQL SQL 客户应用 客户应用 使用SQL 使用PL/SQL 在执行期间，将所有的SQL语句传递给SQL语句执行器组件执行。相对于逐条发送一组SQL语句，PL/SQL的打包传输，减少网络流量。

5. 1.2 PL/SQL　功能特性 • 1.2.1 PL/SQL的程序结构 PL/SQL程序都是以块（block）为基本单位。 DECLARE 变量、常量、游标、自定义异常 BEGIN SQL语句 PL/SQL控制语句 EXCEPTION 错误发生时执行的动作 END； 整个PL/SQL块分三部分：声明部分（用declare开头）、执行部分（以begin开头）和异常处理部分（以exception开头）。其中执行部分是必须的，其他两个部分可选。

6. /*声明部分，以declare开头*/declare v_id integer;v_name varchar(20);cursor c_emp is select * from employee where emp_id=3; /*执行部分，以begin开头*/begin open c_emp;--打开游标loopfetch c_emp into v_id,v_name;--从游标取数据exit when c_emp%notfound ;end loop ;close c_emp;--关闭游标dbms_output.PUT_LINE(v_name);/*异常处理部分，以exception开始*/exceptionwhen no_data_found then dbms_output.PUT_LINE('没有数据');end ;

7. 1.2.2变量声明与赋值 在大多数PL/SQL块中，都需要使用变量来包含在程序逻辑中使用的值。无论需要什么类型的变量，能够在BEGIN部分中使用它之前，都必须在块的DECLARE段中声明这些变量。 声明变量必须提供变量名称和数据类型。 变量名称遵循以下约定： （1）以一个字母字符开头 （2）最多包含30个字符 （3）可以包含大写和小写字母、数字以及_、$、#及其它特殊字符。

8. （1）PL/SQL的基本数据类型 BINARY_INTEGER NUMBER[(precision,scale)] CHAR[(maximum_length)] LONG LONG RAW VARCHAR2(maximum_length) DATE BOOLEAN

9. 声明变量的语法： 变量名 类型〔constant〕[not null] [:=value]; 注意：在声明部分，每一行只能有一个变量声明，如以下的声明是错误的： Declare v_firstname , v_lastname varchar2(20); 正确的声明为： Declare v_firstname varchar2(20); v_last_name varchar2(20);

10. 变量的初始化： Declare v_description varchar2(50); v_numberseats number:=45; v_counter binary_integer:=0; 注意：后面跟一个值的：=用来向声明语句中的变量来指定初始值。也可以使用default替代：=符号。 Declare v_numberseat number default 45;

11. 另外还可以在变量中加入NOT NULL和CONSTANT。 • declare • C_minimunstudentid constant number(5):=10000; • 如果变量在声明时使用了constant,则该变量应被初始化，且以后不能改变它的值。 • 如果在声明时指明not null，那么应该给该变量赋初值，下面声明是错误的： • Declare • v_tempvar number not null; • 正确的声明为： • Declare • v_tempvar number not null:=1; • 注意：CONSTANT关键字是在变量类型之前列出的，而NOT NULL是在数据类型之后列出的。

12. 使用DBMS_OUTPUT检查变量值： （1）打开SQL*PLUS并登录 （2）set serveroutput on （3）declare lv_basket_date date:=sysdate; begin dbms_output.put_line(lv_basket_date ); end;

13. 另外还可以在PL/SQL块中包括SQL： declare lv_qty_num number(3); begin select count(*) into lv_qty_num from emp; dbms_output.put_line(lv_qty_num ); End;

14. PL/SQL的复合数据类型 标量变量非常有用，但很多时候，需要处理逻辑的数据组，于是PL/SQL提供了复合的数据类型，允许创建一个变量，可以将不同数据类型的多个值存储为一个单独的单元。 把复合数据类型分为两种： （1）复合数据类型：主要是记录数据类型和记录表类型 （2）集合数据类型：主要是按索引组织表类型、嵌套表类型和数组类型。 这两者之间的区别是，复合变量包含多种数据类型，而集合则包含相同数据类型的多个值。

15. 记录数据类型： • 记录数据类型和表的行结构非常相似，一行数据通常包括许多不同的字段。就一个记录变量而言，必须使用TYPE语句来创建自己的数据类型。 • TYPE语句的语法格式如下： • Type record_type is record( • field1 type1 [not null]:=expr1; • field2 type2 [not null] :=expr2; • … • )

16. 例： • Type t_StudentRecord is RECORD( • StudentID NUMBER(5), • FirstName VARCHAR2(20), • LastName VARCHAR2(20)); • V_StudentInfo t_StudentRecord; • 当引用记录中的一个字段时，用点标记，其语法为： • Record_name.filed_name • 例： • v_StudentInfo.FistName:=‘john’; • 记录赋值 • 如果要将一个记录赋值给另一个记录，则这两个记录要类型完全相同。

17. 如： • declare • v_sample1 t_StudentRecord; • v_sample2 t_StudentRecord; • Begin • v_sample1:=v_sample2; • .. • End; • 甚至尽管两个类型包含相同的字段定义，但是这两个类型完全不同， 这样类型的记录，彼此之间也不能赋值。下例是非法的 • Declare • TYPE t_RecType1 is RECORD( • Field1 number, • Field2 VARCHAR(5)); • TYPE t_RecType2 is RECORD( • Field1 number, • Field2 VARCHAR2(5));

18. v_Rec1 t_RecType1; • v_Rec2 t_RecType2 ; • Begin • v_Rec1:=v_Rec2; --非法 • v_Rec1.Field1:=v_Rec2.Field1; --合法 • v_Rec1.Field2:=v_Rec2.Field2; --合法 • End; • 也可以使用select语句对记录进行赋值，记录中的字段应该与查询选择结果列表的字段相匹配 • 例： • Declare • TYPE t_StudentRec is RECORD( • FirstName students.first_name%type, • LastName students.last_name%type, • Major students.major%type);

19. v_Student t_StudentRec ; • Begin • SELECT first_name,last_name,major into v_Student • FROM students • WHERE id=10000; • End; • (select语句中的列顺序与在记录变量中的字段顺序一致） • %TYPE和％ROWTYPE • 使用%TYPE属性 • %type用于变量类型的声明中。也可以用于保证定义的变量的数据类型和长度与数据库表中的列保持一致。 • 例： • Declare • v_vxm treader.vxm%type;

20. 使用%ROWTYPE属性 • 定义记录类型为 表名％rowtype，则返回和数据表一样类型的记录行。例： • Declare • v_roomrecord rooms%rowtype; • 则v_roomrecord将与表rooms有相同的结构。如果表的定义改变了，则记录的类型也随之改变。

21. 记录表类型 记录表是另外一种复合数据类型。为什么需要记录表呢？ Declare iv_basket_num number(3); iv_created_date date; Begin select idbasket,dtcreated into iv_basket_num, iv_created_date from bb_basket; Dbms_output.put_line(iv_basket_num||’*’|| iv_created_date ); End; 如果查询语句返回值有多个，则会出现错误“exact fetch returns more than requested numbers of rows”。因为into子句中的变量不能处理多行数据。

22. 需要有一个可以存储多个行以及多个字段的变量（记录表类型）需要有一个可以存储多个行以及多个字段的变量（记录表类型） 例： Declare type type_basketitems is table of bb_basketitem%rowtype index by binary_integer; Tbl_basketitems type_basketitems; Begin Tbl_basketitems(1).idproduct:=1; Tbl_basketitems(1).price:=10.8; Tbl_basketitems(1).quantity:=2; Tbl_basketitems(1).option1:=2; End;

23. 集合数据类型 “集合”是一个已排序的元素组，它允许将相同数据类型的多个值作为一个单独的单元来处理。“集合”有三种：按索引组织的表；数组、嵌套表。 （1）按索引组织的表 它是一种可以处理多行数据但是只能处理一个字段的变量。除了只包含单独的一列数据之外，按索引组织的表在本质上与记录表是相同的。 声明按索引组织的表的数据类型与声明记录表数据类型非常相似。首先创建一种数据类型（包含一列的表结构和一个引用行的索引），其次，使用这种数据类型声明一个表。如： Declare type type_roast is table of number index by binary_integer; tbl_roast type_roast ; Begin tbl_roast(1):=6.22; /*因为按索引组织的表变量中只有一个单独的列是可用的，所以在将一个值*/ tbl_roast(2):=6.13; /*输入到这种变量中时，只需要指出行号即可*/ tbl_roast(3):=6.27; End;

24. 注意： （1）按索引组织的表并不是数据库中实际存在的表。它们是一些变量，用来存储和操作PL/SQL程序中的数据。因此，不能在这些表上执行SQL命令。 （2）按索引组织的表具有“表属性”。“表属性”是可以和表变量一起使用的函数，它允许更好地处理表的值。

25. 例： Declare type type_roast is table of number index by binary_integer; tbl_roast type_roast ; Lv_avg_num number; Begin tbl_roast(1):=6.22; tbl_roast(2):=6.13; tbl_roast(3):=6.27; Lv_avg_num:= tbl_roast . count; Dbms_output.put_line(Lv_avg_num); End;

26. （2）嵌套表 嵌套表是表中之表。一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。 例：假设有一个关于动物饲养员的表，希望其中具有他们饲养的动物的信息。用一个嵌套表，就可以在同一个表中存储饲养员和其饲养的全部动物的信息。 步骤1：创建类型animal_ty：此类型中，对于每个动物都包含有一个记录，记载了其品种、名称和出生日期信息。 CREATE TYPE animal_ty AS OBJECT ( breed varchar2(25), name varchar2(25), birthdate date);

27. 步骤2：创建animals_nt：此类型将用作一个嵌套表的基础类型。 CREATE TYPE animals_nt as table of animal_ty; 步骤3：创建表breeder：饲养员的信息表 create table breeder (breedername varchar2(25), animals animals_nt) nested table animals store as animals_nt_tab;

28. 步骤4：向嵌套表中插入记录 insert into breeder values('mary',animals_nt(animal_ty('dog','butch','970304'),animal_ty('dog','rover','970405'),animal_ty('dog','julio','970506'))); insert into breeder values('jane',animals_nt(animal_ty('cat','an','970304'), animal_ty('cat','jame','970405'), animal_ty('cat','killer','970808'))); 步骤5：查询嵌套表 select name,birthdate from table(select animals from breeder); select name,birthdate from table(select animals from breeder where breedername=’mary’) where name=’dog’;

29. （3）可变数组 可变数组与嵌套表相似，也是一种集合。一个可变数组是对象的一个集合，其中每个对象都具有相同的数据类型。可变数组的大小由创建时决定。在表中建立可变数组后，可变数组在主表中作为一个列对待。从概念上讲，可变数组是一个限制了行集合的嵌套表。 例： 步骤1：创建类型comm_info CREATE TYPE comm_info AS OBJECT ( /*此类型为通讯方式的集合*/ no number(3), /*通讯类型号*/ comm_type varchar2(20), /*通讯类型*/ comm_no varchar2(30)); /*号码*/ 步骤2：创建可变数组comm_info_list CREATE TYPE comm_info_list AS VARRAY(50) OF comm_info; 步骤3：创建表

30. create table user_info (user_id number(6), /*用户ID号*/ user_name varchar2(20), /*用户名称*/ user_comm comm_info_list); /*与用户联系的通讯方式*/ 步骤4：向可变数组插入记录 insert into user_info values(1,'mary',comm_info_list(comm_info(1,'手机','13651401919'), comm_info(2,‘呼机’,‘1281234567’))); insert into user_info values(2,'carl',comm_info_list(comm_info(1,'手机','13901018888'), comm_info(2,'呼机','1281234567')));

31. 步骤5：查询可变数组 select user_comm from user_info where user_id=1; select comm_type,comm_no from table(select user_comm from user_info where user_id=1) where no=1;

32. 绑定变量（主机变量） 变量名称前加上冒号告诉PL/SQL这是一个主机变量，如： where idshopper=:g_shopper and orderplaced=0; 使用variable命令来创建主机变量。注意， variable命令创建的主机变量是SQL*PLUS变量，不是PL/SQL变量。主机变量的类型是number,char和varchar2，没有date和boolean类型。 （1）在SQL*PLUS中输入 variable g_shopper number （注意末尾没有;号） （2） begin : g_shopper :=25; End;

33. 变量的作用域和可见性 • 变量的作用域是可以访问该变量的程序部分。PL/SQL变量的作用域为从声明部分开始到块的结束。 • 当一个变量超出了作用域，PL/SQL将释放变量的空间。 • 例 • Declare • v_number(3,2); • Begin • declare • v_character varchar2(10); • begin • … • end; • End;

34. 变量的可见性是可以访问变量而不必保证该变量的引用是否有效的程序部分。可见性始终在作用域内。如果一个变量超出了作用域范围，就不可见了。例：变量的可见性是可以访问变量而不必保证该变量的引用是否有效的程序部分。可见性始终在作用域内。如果一个变量超出了作用域范围，就不可见了。例： • Declare • v_availableflag boolean; • v_ssn number(9); • Begin • (1) • declare • v_ssn char(11); • v_startdate date; • begin • (2) • end; • (3) • End; V_availableflag 和 number(9)的v_ssn可见 V_availableflag和char(11)的v_ssn，v_startdate可见 V_availableflag 和number(9)的v_ssn可见

35. 如果一个变量在作用域内，但不可见，可以使用标签加以引用变量。如果一个变量在作用域内，但不可见，可以使用标签加以引用变量。 • <<outer>> • Declare • v_availableflag boolean; • v_ssn number(9); • Begin • (1) • declare • v_ssn char(11); • v_startdate date; • begin • (2) • end; • (3) • End; V_availableflag 和 number(9)的v_ssn可见 V_availableflag和char(11)的v_ssn，v_startdate可见，通过outer.v_ssn引用number(9)的v_ssn V_availableflag 和 number(9)的v_ssn可见

36. PL/SQL表达式 • 赋值 • 语法： 变量:=表达式； • 在一个特定的PL/SQL语句中仅能够有一个赋值。下面的例子是错误的。 • V_val1:=v_val2:=v_val3:=0; • 表达式 • 表达式作为一个句子是无效的，他必须是一条语句的一部分，是右值。 • 字符表达式 : ||运算符

37. 运算符的优先级

38. 布尔表达式 ： • NULL为布尔表达式添加了复杂性。 • True and null 结果为null • 仅当操作数是null时，is null才返回true。 • 不能用关系运算符对null值进行检测。 • Like ，between and ，in 返回的值为boolean类型。 • １．算术形式的布尔表达式 • Number1>number2 • ２．字符形式的布尔表达式 • String1>string2 • ３．日期形式的布尔表达式 • Date1=’01-jan-91’; • Date2=’31-dec-90’; • Data1>date2

39. ４．一些注意事项 • （１）不要在实数之间比较相等，因为实数作为近似值存储。如，下例表达式可能不为true • Declare • number1:=1; • Begin • if number1=1.0 then • dbms_output.put_line(‘dskjhds’); • End if; • End; • （２）比较时用圆括号较好 • （３）当使用CLOB的值作为比较运算符的操作数时，或者用在LIKE和BETWEEN子句中，结果集会产生一个临时的LOBS，要确定临时存储空间够用

40. 1.2.3pl/sql的程序控制结构 PL/SQL程序段中有三种程序结构：条件结构、循环结构和顺序结构。 （1）条件结构 ： if condition then statement1 else statement2end if ; （2） 循环结构 ： 与其他语言不太一样，在PL/SQL程序中有三种循环结构：

41. a: loop … end loop; b. while condition loop … end loop; c. for variable in low_bound . . upper_bound loop … end loop; 其中的“…”代表循环体。 例：loop循环(simpleloop.sql) Declare v_loopcounter binary_integer:=1; Begin loop insert into temp_table (num_col) values (v_loopcounter); v_loopcounter:=v_loopcounter+1; exit when v_loopcounter>50; end loop; End;

42. 例： for循环 Begin for v_loopcounter in 1..50 loop insert into temp_table(num_col) values (v_loopcounter); end loop; End; （3）顺序结构 实际就是goto的运用，不过从程序控制的角度来看，尽量少用goto可以使得程序结构更加的清晰。

43. 4、SQL基本命令 PL/SQL使用的数据库操作语言还是基于SQL的，所以熟悉SQL是进行PL/SQL编程的基础。下表为SQL语言的分类。 类别SQL语句 数据定义语言(DDL)Create ,Drop,Grant,Revoke, …数据操纵语言(DML)Update,Insert,Delete, … 数据控制语言(DCL)Commit,Rollback,Savapoint, …其他Alter System,Connect,Allocate, …

44. 1.2.4 　游标 游标在PL/SQL的编程中非常的重要。其定义为：用游标来指代一个DMLSQL操作返回的结果集。即当一个对数据库的查询操作返回一组结果集时，用游标来标注这组结果集，以后通过对游标的操作来获取结果集中的数据信息。 定义游标的语法结构如下： cursor cursor_name is SQL语句; 例：cursor c_emp is select * from employee where emp_id=3;

45. 当需要操作该结果集时，必须完成三步：打开游标、使用fetch语句将游标里的数据取出、关闭游标。例：当需要操作该结果集时，必须完成三步：打开游标、使用fetch语句将游标里的数据取出、关闭游标。例： • Declare • v_first_name varchar2(20); • V_last_name varchar2(20); • Cursor c_student is select first_name, last_name from students; • Begin • open c_students; • loop • fetch c_students into v_firstname, v_last_name; • exit when c_students%notfound; • end loop; • close c_students; • End;

46. 1.2.5 过程和函数 • PL/SQL中的过程和函数与其他语言的过程和函数的概念一样，都是为了执行一定的任务而组合在一起的语句。过程无返回值，函数有返回值。其语法结构为： • 过程： • Create or replace procedure procname(参数列表) as • PL/SQL语句块 • 函数： • Create or replace function funcname(参数列表) return 返回值as • PL/SQL语句块

47. 例：假设有一张表t1，有f1和f2两个字段，f1为number类型，f２为varchar2类型，然后往t１里写两条记录，内容自定。例：假设有一张表t1，有f1和f2两个字段，f1为number类型，f２为varchar2类型，然后往t１里写两条记录，内容自定。 Create or replace procedure test_procedure as V_f11 number :=1; /*声明变量并赋初值*/V_f12 number :=2;V_f21 varchar2(20) :=’first’;V_f22 varchar2(20) :=’second’;BeginInsert into t1 values (V_f11, V_f21);Insert into t1 values (V_f12, V_f22);End test_procedure; /*test_procedure可以省略*/ 至此，test_procedure存储过程已经完成，编译后存储在数据库中，然后可以在其他PL/SQL块或者过程中调用了。 Sql>begin 2 test_procedure; 3 end;

48. 1.2.6 包 • PL/SQL的过程和函数可以和变量与类型共同组成包。PL/SQL的包由两部分组成，即说明部分和包体。 • 例：roompkg.sql • Create or replace package roomspkg as • procedure newroom(p_building rooms.building%type, • p_roomnum rooms.room_number%type, • p_description rooms.description%type); • Procedure deleteroom(p_roomid in rooms.room_id%tye); • End roomspkg;

49. Create or replace package body roompkg as • procedure newroom(p_building rooms.building%type, • p_roomnum rooms.room_number%type, • p_description rooms.description%type) is • begin • insert into rooms (room_id,building,room_number,number_seats,description) • values (room_sequence.nextval,p_building,p_roomnum,p_numseats,p_description); • End newroom; • Procedure deleteroom(p_roomid in rooms.room_id%tye) is • begin • delete from rooms where room_id=p_roomid; • end deleteroom; • End roompkg;

50. 1.2.7　动态SQL • 借助于动态SQL，可以在运行期间构造并执行SQL语句。使用动态SQL语句有两种方法，一种是使用DBMS_SQL包，另一种是使用Oracle8i支持的本地动态SQL。 • 例：printstudents.sql • Create or replace procedure droptable(p_table in varchar2) as • v_sqlstring varchar2(100); • Begin • v_sqlstring:=‘drop table ‘ || p_table; • execute immediate v_sqlstring; • End droptable;