Pl sql
Download
1 / 68

第一章 PL/SQL 介绍 - PowerPoint PPT Presentation


  • 62 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' 第一章 PL/SQL 介绍' - renjiro-wada


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Pl sql
第一章 PL/SQL介绍

  • 1.1 什么是PL/SQL

    结构化查询语言(Structured Query Language,简称SQL)是用来访问关系型数据库一种通用语言,它属于第四代语言(4GL),其执行特点是非过程化,即不用指明执行的具体方法和途径,而是简单的调用相应语句来直接取得结果即可。

    例:delete from students where major=‘Nutrition’;

    然而,对于有些复杂的业务流程又要求相应的程序来描述,那么4GL就有些无能为力了。

    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’;


  • 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;


Oracle数据库服务器

Oracle数据库服务器

SQL

SQL

SQL

  • PL/SQL与网络传输

SQL

SQL

SQL

客户应用

客户应用

使用SQL

使用PL/SQL

在执行期间,将所有的SQL语句传递给SQL语句执行器组件执行。相对于逐条发送一组SQL语句,PL/SQL的打包传输,减少网络流量。


  • 1.2 PL/SQL 功能特性

    • 1.2.1 PL/SQL的程序结构

      PL/SQL程序都是以块(block)为基本单位。

      DECLARE

      变量、常量、游标、自定义异常

      BEGIN

      SQL语句

      PL/SQL控制语句

      EXCEPTION

      错误发生时执行的动作

      END;

      整个PL/SQL块分三部分:声明部分(用declare开头)、执行部分(以begin开头)和异常处理部分(以exception开头)。其中执行部分是必须的,其他两个部分可选。


/*声明部分,以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 ;


  • 1.2.2变量声明与赋值

    在大多数PL/SQL块中,都需要使用变量来包含在程序逻辑中使用的值。无论需要什么类型的变量,能够在BEGIN部分中使用它之前,都必须在块的DECLARE段中声明这些变量。

    声明变量必须提供变量名称和数据类型。

    变量名称遵循以下约定:

    (1)以一个字母字符开头

    (2)最多包含30个字符

    (3)可以包含大写和小写字母、数字以及_、$、#及其它特殊字符。


1)PL/SQL的基本数据类型

BINARY_INTEGER

NUMBER[(precision,scale)]

CHAR[(maximum_length)]

LONG

LONG RAW

VARCHAR2(maximum_length)

DATE

BOOLEAN


声明变量的语法:

变量名 类型〔constant〕[not null] [:=value];

注意:在声明部分,每一行只能有一个变量声明,如以下的声明是错误的:

Declare

v_firstname , v_lastname varchar2(20);

正确的声明为:

Declare

v_firstname varchar2(20);

v_last_name varchar2(20);


变量的初始化:

Declare

v_description varchar2(50);

v_numberseats number:=45;

v_counter binary_integer:=0;

注意:后面跟一个值的:=用来向声明语句中的变量来指定初始值。也可以使用default替代:=符号。

Declare

v_numberseat number default 45;


  • 另外还可以在变量中加入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是在数据类型之后列出的。


使用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;


另外还可以在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;


PL/SQL的复合数据类型

标量变量非常有用,但很多时候,需要处理逻辑的数据组,于是PL/SQL提供了复合的数据类型,允许创建一个变量,可以将不同数据类型的多个值存储为一个单独的单元。

把复合数据类型分为两种:

(1)复合数据类型:主要是记录数据类型和记录表类型

(2)集合数据类型:主要是按索引组织表类型、嵌套表类型和数组类型。

这两者之间的区别是,复合变量包含多种数据类型,而集合则包含相同数据类型的多个值。


  • 记录数据类型:

  • 记录数据类型和表的行结构非常相似,一行数据通常包括许多不同的字段。就一个记录变量而言,必须使用TYPE语句来创建自己的数据类型。

  • TYPE语句的语法格式如下:

  • Type record_type is record(

  • field1 type1 [not null]:=expr1;

  • field2 type2 [not null] :=expr2;

    • )


  • 例:

    • 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’;

  • 记录赋值

    • 如果要将一个记录赋值给另一个记录,则这两个记录要类型完全相同。


    • 如:

    • 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));


    • 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);


    • 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;


    • 使用%ROWTYPE属性

      • 定义记录类型为 表名%rowtype,则返回和数据表一样类型的记录行。例:

        • Declare

        • v_roomrecord rooms%rowtype;

        • 则v_roomrecord将与表rooms有相同的结构。如果表的定义改变了,则记录的类型也随之改变。


    记录表类型

    记录表是另外一种复合数据类型。为什么需要记录表呢?

    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子句中的变量不能处理多行数据。


    需要有一个可以存储多个行以及多个字段的变量(记录表类型)需要有一个可以存储多个行以及多个字段的变量(记录表类型)

    例:

    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;


    集合数据类型需要有一个可以存储多个行以及多个字段的变量(记录表类型)

    “集合”是一个已排序的元素组,它允许将相同数据类型的多个值作为一个单独的单元来处理。“集合”有三种:按索引组织的表;数组、嵌套表。

    (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;


    注意:需要有一个可以存储多个行以及多个字段的变量(记录表类型)

    (1)按索引组织的表并不是数据库中实际存在的表。它们是一些变量,用来存储和操作PL/SQL程序中的数据。因此,不能在这些表上执行SQL命令。

    (2)按索引组织的表具有“表属性”。“表属性”是可以和表变量一起使用的函数,它允许更好地处理表的值。


    例:需要有一个可以存储多个行以及多个字段的变量(记录表类型)

    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;


    需要有一个可以存储多个行以及多个字段的变量(记录表类型)2)嵌套表

    嵌套表是表中之表。一个嵌套表是某些行的集合,它在主表中表示为其中的一列。对主表中的每一条记录,嵌套表可以包含多个行。

    例:假设有一个关于动物饲养员的表,希望其中具有他们饲养的动物的信息。用一个嵌套表,就可以在同一个表中存储饲养员和其饲养的全部动物的信息。

    步骤1:创建类型animal_ty:此类型中,对于每个动物都包含有一个记录,记载了其品种、名称和出生日期信息。

    CREATE TYPE animal_ty AS OBJECT (

    breed varchar2(25),

    name varchar2(25),

    birthdate date);


    步骤需要有一个可以存储多个行以及多个字段的变量(记录表类型)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;


    步骤需要有一个可以存储多个行以及多个字段的变量(记录表类型)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’;


    需要有一个可以存储多个行以及多个字段的变量(记录表类型)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:创建表


    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')));


    步骤需要有一个可以存储多个行以及多个字段的变量(记录表类型)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;


    绑定变量(主机变量)需要有一个可以存储多个行以及多个字段的变量(记录表类型)

    变量名称前加上冒号告诉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;


    • 变量的作用域和可见性需要有一个可以存储多个行以及多个字段的变量(记录表类型)

      • 变量的作用域是可以访问该变量的程序部分。PL/SQL变量的作用域为从声明部分开始到块的结束。

        • 当一个变量超出了作用域,PL/SQL将释放变量的空间。

        • Declare

        • v_number(3,2);

        • Begin

        • declare

        • v_character varchar2(10);

        • begin

        • end;

        • End;


    • 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可见


    • <<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可见


    • PL/SQL如果一个变量在作用域内,但不可见,可以使用标签加以引用变量。表达式

      • 赋值

        • 语法: 变量:=表达式;

        • 在一个特定的PL/SQL语句中仅能够有一个赋值。下面的例子是错误的。

          • V_val1:=v_val2:=v_val3:=0;

      • 表达式

        • 表达式作为一个句子是无效的,他必须是一条语句的一部分,是右值。

      • 字符表达式 : ||运算符


    • 运算符的优先级如果一个变量在作用域内,但不可见,可以使用标签加以引用变量。


    • 布尔表达式 :如果一个变量在作用域内,但不可见,可以使用标签加以引用变量。

      • NULL为布尔表达式添加了复杂性。

      • True and null 结果为null

      • 仅当操作数是null时,is null才返回true。

      • 不能用关系运算符对null值进行检测。

      • Like ,between and ,in 返回的值为boolean类型。

    • 1.算术形式的布尔表达式

      • Number1>number2

    • 2.字符形式的布尔表达式

      • String1>string2

    • 3.日期形式的布尔表达式

      • Date1=’01-jan-91’;

      • Date2=’31-dec-90’;

      • Data1>date2


    • 4.一些注意事项如果一个变量在作用域内,但不可见,可以使用标签加以引用变量。

      • (1)不要在实数之间比较相等,因为实数作为近似值存储。如,下例表达式可能不为true

      • Declare

      • number1:=1;

      • Begin

      • if number1=1.0 then

      • dbms_output.put_line(‘dskjhds’);

      • End if;

      • End;

      • (2)比较时用圆括号较好

      • (3)当使用CLOB的值作为比较运算符的操作数时,或者用在LIKE和BETWEEN子句中,结果集会产生一个临时的LOBS,要确定临时存储空间够用


    • 1.2.3如果一个变量在作用域内,但不可见,可以使用标签加以引用变量。pl/sql的程序控制结构

      PL/SQL程序段中有三种程序结构:条件结构、循环结构和顺序结构。

      (1)条件结构 :

      if condition then statement1 else statement2end if ;

      (2) 循环结构 :

      与其他语言不太一样,在PL/SQL程序中有三种循环结构:


    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;


    例: 如果一个变量在作用域内,但不可见,可以使用标签加以引用变量。for循环

    Begin

    for v_loopcounter in 1..50 loop

    insert into temp_table(num_col) values (v_loopcounter);

    end loop;

    End;

    (3)顺序结构

    实际就是goto的运用,不过从程序控制的角度来看,尽量少用goto可以使得程序结构更加的清晰。


    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, …


    1.2.4 如果一个变量在作用域内,但不可见,可以使用标签加以引用变量。 游标

    游标在PL/SQL的编程中非常的重要。其定义为:用游标来指代一个DMLSQL操作返回的结果集。即当一个对数据库的查询操作返回一组结果集时,用游标来标注这组结果集,以后通过对游标的操作来获取结果集中的数据信息。

    定义游标的语法结构如下:

    cursor cursor_name is SQL语句;

    例:cursor c_emp is select * from employee where emp_id=3;


    • 当需要操作该结果集时,必须完成三步:打开游标、使用当需要操作该结果集时,必须完成三步:打开游标、使用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;


    • 1.2.5 当需要操作该结果集时,必须完成三步:打开游标、使用过程和函数

  • PL/SQL中的过程和函数与其他语言的过程和函数的概念一样,都是为了执行一定的任务而组合在一起的语句。过程无返回值,函数有返回值。其语法结构为:

  • 过程:

  • Create or replace procedure procname(参数列表) as

  • PL/SQL语句块

  • 函数:

  • Create or replace function funcname(参数列表) return 返回值as

  • PL/SQL语句块


  • 例:当需要操作该结果集时,必须完成三步:打开游标、使用假设有一张表t1,有f1和f2两个字段,f1为number类型,f2为varchar2类型,然后往t1里写两条记录,内容自定。

    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;


    • 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;


    • 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;


    • 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;


    • 例: 当需要操作该结果集时,必须完成三步:打开游标、使用droptable.sql

    • Create or replace procedure droptable(p_table in varchar2) as

    • v_sqlstring varchar2(100);

    • v_cursor binary_integer;

    • V_returncode binary_integer;

    • Begin

    • v_sqlstring:=‘drop table ‘ || p_table;

    • --open the cursor.

    • V_cursor:=dbms_sql.open_cursor;

    • --parse and execute the statement.

    • Dbms_sql.parse(v_cursor,v_sqlstring,dbms_sql.native);

    • V_returncode:=DBMS_SQL.EXECUTE(v_cursor);

    • --close the cursor.

    • Dbms_sql.close_cursor(v_cursor);

    • End droptable;


    • 1.2.8 当需要操作该结果集时,必须完成三步:打开游标、使用对象类型

      • 对象类型由属性和方法组成,并可存储在数据库表中。

      • 例:ch12/objtypes.sql

      • Create or replace type student as object(

      • id number(5),

      • first_name varchar2(20),

      • last_name varchar2(20),

      • major varchar2(30),

      • current_credits number(3),

      • --return the first and last names

      • Member function formattedname

      • return varchar2,

      • Pragma restrict_references(formattedname,

      • RNDS,WNDS,RNPS,WNPS),


    • --updates the major to the specified value in p_newmajor当需要操作该结果集时,必须完成三步:打开游标、使用

    • MEMBER PROCEDURE changemajor(p_newmajor in varchar2),

    • PRAGMA RESTRICT_REFERENCES(changemajor,RNDS,WNDS,RNPS,WNPS),

    • --updates the current_credits by adding the number of

    • --credits in p_completedclass to the current value.

    • MEMBER PROCEDURE updatecredits(p_completedclass in class),

    • PRAGMA RESTRICT_REFERENCES(updatecredits,

    • RNDS,WNDS,RNPS,WNPS),


    • --order function used to sort students.当需要操作该结果集时,必须完成三步:打开游标、使用

    • Order member function comparestudent(p_student in student)

    • return number

    • );

    • Create or replace type body student as

    • member function formattedname

    • return varchar2 is

    • begin

    • return first_name || ‘ ‘ ||last_name;

    • end formattedname;


    • Member procedure changemajor(p_newmajor in varchar2) is当需要操作该结果集时,必须完成三步:打开游标、使用

    • begin

    • major:=p_newmajor;

    • End changemajor;

    • member procedure updatecredits(p_completedclass in class )

    • is

    • begin

    • current_credits:=current_credits+

    • p_completedclass.num_credits;

    • End updatecredits;


    • Order member function comparestudent(p_student in student)当需要操作该结果集时,必须完成三步:打开游标、使用

    • return number is

    • Begin

    • --first compare by last name

    • if p_student.last_name=self.last_name then

    • if p_student.first_name<self.first_name then

    • return 1;

    • elseif p_student.first_name>self.first_name then

    • return –1;

    • else

    • return 0;

    • end if;

    • else



    • PL/SQL当需要操作该结果集时,必须完成三步:打开游标、使用风格指南

      • 注释风格

        • 在每个块和过程的开始放注释,解释该块的功能,特别是列出过程的输入、输出参数和引用的数据库

        • 在每个变量的旁边添加单行注释,说明该变量的作用。

        • 在块的每个主要部分之前添加注释。最好说明算法的目的和结果。而不是对该代码进行过多细节的说明。

      • 变量名风格:变量名应该告示变量的用途。最大长度20个字符。例:

        • v_variablename program variable

        • E_exceptionname user_defined exception

        • T_typename user_defindex type

        • P_parametename parameter or procedure or function

        • C_constantvalue constant constrained with the constant clauses


    • 大写风格当需要操作该结果集时,必须完成三步:打开游标、使用

      • 保留字使用大写字母

      • 内置函数使用大写字母

      • 预定义类型使用大写字母(NUMBER(7,2))

      • SQL关键字使用大写字母(INSERT)

      • 数据库对象使用小写

      • 变量名使用大小写混合,名字中每个单词开头大写

        • (t_StudentRecordType)

    • 缩进风格

      • 在每个块以内缩进两个空格。在declare…end之间进行缩进,if-then-else内也进行缩进,在多行上连续的SQL语句上也进行缩进。如:

      • Select id,first_name,last_name

      • into v_StudentID,v_FirstName,v_LastName

      • from students

      • where id=1002;


    • 1.6 当需要操作该结果集时,必须完成三步:打开游标、使用本书案例使用的通用数据库表

      • 序列

        • Student_sequence:生成students主键的唯一值

          • Create sequence student_sequence

          • start with 10000

          • increment by 1;

        • Room_sequence

          • Create sequence room_sequence

          • start with 20000

          • increment by 1;


    • 当需要操作该结果集时,必须完成三步:打开游标、使用

      • Students:入学新生的信息

        • Create table students (

        • id number(5) primary key,

        • first_name varchar2(20),

        • last_name varchar2(20),

        • major varchar2(30),

        • current_credits number(3)

        • );

        • Insert into students(id,first_name,last_name,major,current_credits)

        • Values (student_sequence.nextval,’Margaret’,’Mason’,’History’,4);


    • Major_stats当需要操作该结果集时,必须完成三步:打开游标、使用:不同专业的统计信息

      • Create table major_stats(

      • major varchar2(30),

      • total_credits number,

      • total_students number);

      • Insert into major_stats (major,total_credits,total_students)

      • values (‘computer science’,22,3);


    • Rooms:当需要操作该结果集时,必须完成三步:打开游标、使用可用的教室信息

      • Create table rooms(

      • room_id number(5) primary key,

      • building varchar2(15),

      • room_number number(4),

      • number_seats number(4),

      • Description varch2(50)

      • );

      • Insert into rooms (room_id,building,room_number,number_seats,

      • description)

      • Values (room_sequence.nextval,’Building 7’,201,1000,’large lecture hall’);


    • Classes:当需要操作该结果集时,必须完成三步:打开游标、使用学生可以选择的课程

      • Crete table classes(

      • department char(3),

      • course number(3),

      • description varchar2(2000),

      • max_students number(3),

      • current_students number(3),

      • Num_credits number(1),

      • Room_id number(5),

      • Constraint classes_department_course

      • primary key (department,course),

      • Constraint classes_room_id

      • foreign key(room_id) references rooms (room_id)

      • );

      • Insert into classes(department,course,description,max_students,

      • current_students,num_credits,room_id)

      • Values (‘His’,101,’History 101’,20,11,4,20000);


    • Registered_students:当需要操作该结果集时,必须完成三步:打开游标、使用学生目前参加的课程信息

      • Create rtable registered_students(

      • student_id number(5) not null,

      • department char(3) not null,

      • course number(3) not null,

      • grade char(1),

      • constraint rs_grade

      • check (grade in (‘A’,’B’,’C’,’D’,’E’)),

      • Constraint rs_student_id

      • foreign key(student_id) references students (id),

      • Constraint rs_department_course

      • foreign key(department,course) references classes(department,course));

      • Insert into registered_students (student_id,department,course,

      • Grade)

      • Values (10000,’CS’,102,’A’);


    • Rs_audit:当需要操作该结果集时,必须完成三步:打开游标、使用记录对表registered_students所作的修改

      • Create table rs_audit (

      • change_type char(1) not null,

      • chaged_by varchar2(8) not null,

      • timestamp DATE not null,

      • Old_student_id number(5),

      • old_department char(3),

      • old_course number(3),

      • old_grade char(1),

      • new_student_id number(5),

      • new_department char(3),

      • new_course number(3),

      • new_grade char(1)

      • );


    • Log_table:当需要操作该结果集时,必须完成三步:打开游标、使用记录oracle数据库发生的错误信息

      • Create table log_table(

      • code number,

      • Message varchar2(200),

      • info varchar2(100)

      • );

    • Temp_table:存放临时数据

      • Create table temp_table(

      • num_col number,

      • char_col varchar2(60)

      • );

    • Connect_audit:记录与数据库的连接和断开信息

      • Create table connect_audit (

      • user_name varchar2(30).,

      • operation varchar2(30),

      • timestamp DATE);


    • Debug_table:当需要操作该结果集时,必须完成三步:打开游标、使用保存PL/SQL的调试信息

      • Create table debug_table(

      • linecount number,

      • debug_str varchar2(100)

      • );

    • Source:第三章中调试程序案例使用的

      • Create table source(

      • key number(5),

      • value varchar2(50));

    • Destination:第三章中调试程序案例使用的

      • Create table destination(

      • key number(5),

      • value varchar2(50));


    ad