Oracle数据库的基本操作（四）

一、PL/SQL程序结构

• PL/SQL:Procedural Language/SQL
• 是一种块结构的语言；

1.PL/SQL块

• 每个块分为三个部分：声明部分、执行部分、异常处理部分；

• 语法：

  [declare]
   声明语句    - -定义常量、变量、游标等；
  begin
   执行语句    - -SQL语句、流程控制语句；嵌套PL/SQL块；
  [exception]
   异常处理语句
  end;
  

• 声明部分和异常处理部分是可选的；执行部分是必选的；

• PL/SQL块中的每条语句都必须以分号结束；

• PL/SQL语句块分为：匿名块和命名块；

1.1只包含执行部分的PL/SQL块

1.2包含声明部分、执行部分和异常处理部分的PL/SQL块

declare
 vSname varchar2(20);
begin
 select Sname into vSname from Student where Sno = '&vSno';   --&vSno为替换变量
 dbms_output.put_line('姓名：'|| vSname);
exception
 when no_data_found then
 dbms_output.put_line('您输入的学号不存在');
end;

1.3命名PL/SQL块

<<outer>>
declare
  vSno char(7);
  vGrade number(3,1);
begin
  <<inner>>
  begin
    select Sno into vSno from Student where Sname = '张三';
  end; - -<<inner>>
  select AVG(Grade) into vGrade from SC where Sno = vSno;
  dbms_output.put_line(vSno || '的平均成绩是：'|| vGrade);
end; - -<<outer>>

二、PL/SQL数据类型

• 标量类型：char、varchar2、number、date、boolean…
• 复合类型：record、varry、table
• 参照类型：ref cursor…

三、常量

• 使用constant关键字声明常量；

• 只能在declare部分赋值；

• 可以使用“:=”赋值，也可以使用default关键字赋值；

• 声明常量pi并赋值

  declare
    pi constant number(6,5) := 3.14159;
  begin
    dbms_output.put_line(pi);
  end;
  

四、变量

• 可在declare部分或begin… end部分赋值；

• 变量的作用域是从变量的声明到语句块的结束；

• SQL Server变量的作用域

  declare @Sname varchar(20)
  select @Sname = Sname from Student where Sno = '2017001'
  - -go
  select @Sname
  

1.标量类型的变量

（1）声明变量

• 变量名 数据类型 [not null] [:= | default 值]

（2）变量赋值

• 方法一：直接赋值，即 变量名 := 值

• 方法二：select列名into变量名from表名where条件
  ·该select语句必须返回一条数据；
  ·当select into语句没有返回任何数据时，会触发no_data_found异常；
  ·当select into语句返回多条数据时，会触发too_many_rows异常。

• Oracle

  declare
    vSno char(7) := '2017001';
    vSname Student.Sname % type;
  begin
    select Sname into vSname from Student where Sno = vSno;
    dbms_output.put_line('学号2017001的姓名是:' || vSname);
  end;
  

2.复合变量

• 复合变量是指用于存放多个数值的变量；

（1）VARRAY类型

• VARRAY：variable array即可变数组；
• Oracle中，数组的索引从1开始；

2.1 数组的用法

declare
  type ArraySname is varray(5) of varchar2(20);
  vSname ArraySname;
  total number;
begin
  vSname := ArraySname('张三','李四','王五','赵六','孙七');
  total := vSname.count;  --求数组的长度
  dbms_output.put_line('该数组共有' || total || '个元素');
  for i in 1 .. total loop
    dbms_output.put_line(vSname(i));  --数组下标从1开始
  end loop;
end;

（2）记录类型

• 用于处理单行多列数据；
• 需先定义记录类型和记录变量；
• 采用“记录变量，记录成员”的形式来引用记录成员；

2.2 使用rowtype%定义记录类型

• 用法：变量名 数据表%rowtype

• 代码及运行结果

  declare 
    vSname Student.Sname % Type;
    vStudent Student % rowtype;
  begin
    select * into vStudent from Student where Sno = '2017001';
    vSname := vStudent.Sname;
    dbms_output.put_line('学号2017001的姓名是：' || vSname);
  end;
  

2.3 使用type…record自定义记录类型

• 代码及运行结果

  declare
    type stuRecord is record(
      vSname Student.Sname % type,
      vSsex Student.Ssex % type
    );
    vstuRecord stuRecord;
  begin
    select Sname,Ssex into vstuRecord from Student where Sno = '2017001';
    dbms_output.put_line('学号是2017001的姓名是：' || vstuRecord.vSname);
    dbms_output.put_line('学号是2017001的性别是：' || vstuRecord.vSsex);
  end;
  

（3）记录表类型

• 记录类型的变量只可以存储查询语句返回的一行记录；
• 记录表类型的变量可以存储查询语句返回的多行记录；

2.3

declare
  --定义两个表类型：TableType1和TableType2,相当于一维数组
  type TableType1 is table of char(6) index by binary_integer;
  type TableType2 is table of Student.Sage % type index by binary_integer;
  --定义两种表类型变量：table1和table2
  table1 TableType1;
  table2 TableType2;
begin
  table1(1) := '张三';
  table1(2) := '李四';
  table2(1) := 20;
  table2(2) := 23;
  dbms_output.put_line(table1(1) || '年龄：' || table2(1));
  dbms_output.put_line(table1(2) || '年龄：' || table2(2));
end;

2.4

declare
  --多维表类型，相当于对维数组
  type TableStudent is table of Student % rowtype index by binary_integer;
  vStudent TableStudent;  --vStudent是一个多维表类型变量

  --一维表类型，相当于一维数组
  type TableVARCHAR2 is table of varchar2(10) index by binary_integer;
  vtable TableVARCHAR2;  --vtable是一个一维表类型变量
begin
  select * into vStudent(1) from Student where Sno = '2017001';
  dbms_output.put_line(vStudent(1).Sname);

  --在一维数组变量vtable插入三个数据
  vtable(1) := '张三';
  vtable(2) := '李四';
  vtable(3) := '王五';

  --使用表变量的属性
  dbms_output.put_line('总记录数：' || vtable.count);
  dbms_output.put_line('第一条记录：' || vtable.first);
  dbms_output.put_line('最后一条记录：' || vtable.last);
  dbms_output.put_line('第二条的前一条记录：' || vtable.prior(2));
  dbms_output.put_line('第二条的后一条记录：' || vtable.next(2));

  --使用delete方法删除表变量vtable中索引值为1的记录
  vtable.delete(1);
  dbms_output.put_line('删除一条记录后的总记录数：' || vtable.count);
end;