Oracle Class6. PL/SQL 简介(数据类型,逻辑比较,控制结构,错误处理)

------------------------2013-5-14------------------------
oracle数据类型
Scalar:
  char(n),nchar(n)
  varchar2(n)
  nvarchar2(n)
  date
  timestamp
  raw
  blob,clob
  nclob,bfile
  long,long raw
  rowid,urowid
Collection:
  varray
  table
Relationship
  ref

PL/SQL 代表Procedural Language/SQL（Procedural Language:过程语言）
它是对SQL的扩展
PL/SQL中存在过程构造和数据库访问

优点：调用外部函数和过程。
支持sql,oop
更高生产率，性能，可移植性。
严密的安全性

PL/SQL引擎
过程语句执行器（过程语句）和SQL语句执行器（SQL语句）。

PL/SQL块的各个组成部分
  声明部分
  可执行部分
  异常处理部分
PL/SQL块的结构
  declare
    declarations  --声明部分
  begin
    executable statements  --可执行部分
  exception
    handlers  --异常处理部分
  end;

变量
临时存储，赋予新值。
可重用性，易于维护。

内置数据类型
标量，容纳单个值（number,character,date,boolean）
 number类型：binary_integer,number,pls_integer
 number子类型：dec,decimal,double precision,float,integer,int,numeric,real,smallint
 character类型：char,varchar2,raw,long和long raw,rowid和urowid
 区域字符类型：nchar,nvarchar2
 datetime和interval类型：date,timestamp,
 timestamp with time zone,timestamp with local time zone,
 interval year to month,interval day to second
 boolean类型：true,false,null,只允许对boolean变量执行逻辑操作。
复合，record,varray,nested table
引用，ref cursor,ref操作符
lob，blob,clob,nclob,bfile

属性的类型：
%type 引用数据库列，变量的类型和数据表中的字段的数据类型一致。
%rowtype 代表表中的行
记录类型变量，引用记录型变量的方法是"记录变量名.基本类型变量名"

布尔表达式类型：
数值型，比较数值
字符型，比较用引号括起来的序列或单个字符
日期型，比较两个日期

控制结构
条件控制，if then,if then else,if then elsif和case
迭代控制，while,for
顺序控制，goto,null

错误处理
预定义，违反oracle规则时，将隐式引发它。
用户定义，由raise语句显示引发，只能在pl/sql块的声明部分中声明

pl/sql支持所有sql数据类型和ansi标准类型。

------------------------2013-5-15------------------------

1.pl/sql程序的结构
2.基本语法要素，包括常量，基本数据类型，复合数据类型，表达式和函数。
3.条件控制和循环控制的流程控制结构
4.事务处理的commit,rollback和savepoint等3个命令
5.定义，打开和提取游标的数据
6.无参数过程和带参数过程的创建，查询，修改和使用方法
7.序列的创建和使用方法
8.定义异常
9.综合实例

pl/sql以解释型方式执行，不能编译成可执行文件，脱离支撑环境执行。

每次只能执行一条语句，语句以英文的分号;为结束标识。
oracle不像vb,vc程序设计语言，侧重后台数据库的管理，提供编程能力较弱。

grant "RESOURCE" to "TEMPUSER";
alter user "TEMPUSER" default role all;

--创建表
create table "TESTTABLE"(
 "RECORDNUMBER" NUMBER(4) NOT NULL,
 "CURRENTDATE" date not null
);

select * from testtable;

--pl/sql添加数据
set serveroutput on   --允许服务器输出
declare
  maxrecords constant int:=100;
  i int:=1;
begin
  for i in 1..maxrecords loop
    insert into TESTTABLE(recordnumber,currentdate)values(i,sysdate);
  end loop;
  dbms_output.put_line('成功录入数据!');
  commit;
end;

成功录入数据!

PL/SQL 过程已成功完成。

sysdate为系统时间函数
dbms_output为系统默认程序包，put_line为包中定义的方法，输出信息。
对数据库数据的更改并没有直接操作数据库，放在工作区的内存里，直到commit语句执行后才发生永久更改。

常量语法格式
常量名 constant 类型标识符 [not null]:=值
括号内的not null为可选参数，若选用，表明该常（变）量不能为空值。

--定义常量
declare
  pi constant number(9) :=3.1415926;
begin
  dbms_output.put_line(pi);
  commit;
end;

3

PL/SQL 过程已成功完成。

关于 varchar2 的最大长度
varchar2有两个最大长度：一个是在字段类型4000；一个是在PL/SQL中变量类型32767。

boolean 布尔型（true,false,null三者取一）

变量语法格式
变量名 类型标识符 [not null]:=值

--定义变量
declare
  age number(3,1) :=26.1;      --number(3) :=26.1; 输出26
begin
  dbms_output.put_line(age);
  commit;
end;
--输出26.1

 

--select * from testtable;

--定义变量，引用列字段类型。
declare
  mydate testtable.currentdate%type:='15-6月 -13';
begin
  dbms_output.put_line(mydate);
  commit;
end;

--记录类型
set serveroutput on;
declare
  type myrecord is record(
    myrecordnumber int,
    mycurrentdate date
  );
  srecord myrecord;
begin
  select * into srecord from testtable where recordnumber = 68;
  dbms_output.put_line(srecord.myrecordnumber);
  dbms_output.put_line(srecord.mycurrentdate);
end;
--在pl/sql程序中，select语句总是和into配合使用，into子句后面就是要被赋值的变量。

--记录类型
set serveroutput on;
declare
  myrecord testtable%rowtype;
begin
  select * into myrecord from testtable where recordnumber = 88;
  dbms_output.put_line(myrecord.recordnumber);
  dbms_output.put_line(myrecord.currentdate);
end;

一维表类型变量，相当于一维数组。
type 表类型 is table of 类型 index by binary_integer;
表变量名 表类型

-- 一维表类型
declare
  type tabletype1 is table of varchar2(4) index by binary_integer;
  type tabletype2 is table of testtable.recordnumber%type index by binary_integer;
  table1 tabletype1;
  table2 tabletype2;
  begin
    table1(1) := '大学';
    table1(2) := '大专';
    table2(1) := 88;
    table2(2) := 55;
  dbms_output.put_line(table1(1) || table2(1));
  dbms_output.put_line(table1(2) || table2(2));
  dbms_output.put_line('总记录数：' || to_char(table1.count));
  dbms_output.put_line('第一条记录' || table1.first);
  dbms_output.put_line('最后条记录' || table1.last);
  dbms_output.put_line('第二条的前一条记录' || table1.prior(2));
  dbms_output.put_line('第一条的后一条记录' || table1.next(1));
  end;

count,delete,first,last,next,exists和prior属性进行操作，使用方法为"表变量名.属性"，返回的是数字。

||是连接字符串的运算符。

多维表类型变量，相当于多维数组。

declare
  type tabletype1 is table of testtable%rowtype index by binary_integer;
  table1 tabletype1;
  begin
  select * into table1(1) from testtable where recordnumber=60;
  dbms_output.put_line(table1(1).recordnumber || table1(1).currentdate);
  end;
--table1(1)如果返回多个记录，也不可以，table1(60)不是代表60个记录。

数值表达式：** 乘方
declare
  result integer;
  begin
    result := 10+3*4-20+5**2;
    dbms_output.put_line('返回结果是：' || to_char(result));
  end;

字符表达式：就是连接运算符 ||
关系表达式：= 等于（不是赋值运算符:=，like类似于，in在...之中，between在...之间）
关系型表达式运算符两边的表达式的数据类型必须一致。

逻辑表达式：not逻辑非,or逻辑或,and逻辑与
运算的优先次序：not,and和or

函数：to_char（字符型）,to_date（日期型）,to_number（数值型）

#条件控制#
if..then..end if条件控制

declare
  number1 integer := 90;
  number2 integer := 60;
begin
  if number1 >= number2 then
    dbms_output.put_line('number1大于等于number2');
  end if;
end;

if..then..else..end if条件控制

declare
  number1 integer := 80;
  number2 integer := 90;
begin
  if number1 >= number2 then
    dbms_output.put_line('number1大于等于number2');
  else
    dbms_output.put_line('number1小于number2');
  end if;
end;

if嵌套条件控制
declare
  number1 integer := 80;
  number2 integer := 90;
begin
  if number1 <= number2 then
    if number1 = number2 then
      dbms_output.put_line('number1等于number2');
    else
      dbms_output.put_line('number1小于number2');
    end if;
  else
    dbms_output.put_line('number1大于number2');
  end if;
end;

#循环控制#
loop .. exit .. end loop循环控制
declare
  number1 integer := 80;
  number2 integer := 90;
  i integer := 0;
begin
  loop
    number1 := number1 + 1;
    if number1 = number2 then
      exit;
    else
      i := i+1;
    end if;
  end loop;
  dbms_output.put_line('共循环次数：' || to_char(i));
end;

loop .. exit .. when .. end loop循环控制
exit when实际上就相当于 if 条件 then
exit
end if;

declare
  number1 integer := 80;
  number2 integer := 90;
  i integer := 0;
begin
  loop
    number1 := number1 + 1;
    i := i+1;
    exit when number1 = number2;
  end loop;
  dbms_output.put_line('共循环次数：' || to_char(i));
end;

while .. loop .. end loop循环控制
declare
  number1 integer := 80;
  number2 integer := 90;
  i integer := 0;
begin
  while number1 < number2 loop
    number1 := number1 + 1;
    i := i+1;
  end loop;
  dbms_output.put_line('共循环次数：' || to_char(i));
end;

for .. in .. loop .. end loop循环控制
declare
  number1 integer := 80;
  number2 integer := 90;
  i integer := 0;
begin
  for i in 1..10 loop
    number1 := number1 + 1;
  end loop;
  dbms_output.put_line('number1的值：' || to_char(number1));
end;

事务处理
commit命令，工作区内的修改内容才写入到数据库上，称为物理写入，这样可以保证在任意的客户机没有物理提交修改以前，别的客户机读取的后台数据库中的数据是完整的，一致的。

打开自动提交事务：set auto on;
取消自动提交事务：set auto off;

rollback命令
--查询--
已选择100行。
--删除--
已删除100行。
--查询--
未选定行
--rollback--
回退已完成。
--查询--
已选择100行。

savepoint命令
创建保存点：savepoint 保存点名；
回滚保存点：rollback to 保存点名；

insert into testtable values('101','16-5月 -13');

savepoint ins1;

--执行删除操作--
select * from testtable;

rollback to ins1;

--查询，回滚到删除之前的状态。--

##游标##
游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回数据表中。

cursor 游标名 is select 语句;

declare
  rn testtable.recordnumber%type;
  cursor mycursor is select * from testtable where recordnumber > rn;  --定义游标
  rs mycursor%rowtype;
begin
  rn := 80;
  open mycursor;  --打开游标 1.将符合条件的记录送入内存，2.将指针指向第一条记录。
  if mycursor%isopen then
    fetch mycursor into rs;  --提取游标
    if mycursor%found then
      dbms_output.put_line(to_char(rs.recordnumber) || rs.currentdate);
    else
      dbms_output.put_line('没有数据!');
    end if;
  else
    dbms_output.put_line('游标没有打开!');
  end if;
  close mycursor;  --关闭游标
end;

--提取游标
fetch 游标名 into 变量名1，变量名2...;
或
fetch 游标名 into 记录型变量名;

游标的属性
1. %isopen 测试游标是否打开。
2. %found 测试前一个fetch语句是否有值，有值将返回true，否则为false。
3. %notfound 是%found属性的反逻辑，常被用于退出循环。
4. %rowcount 返回游标的数据行数，若返回值为0，表明游标已经打开，但没有提取出数据。

declare
  rn testtable.recordnumber%type;
  cursor mycursor is select * from testtable where recordnumber > rn;
  rs mycursor%rowtype;
begin
  rn := 80;
  open mycursor;
  if mycursor%isopen then
    fetch mycursor into rs;  --提取游标
    dbms_output.put_line(to_char(mycursor%rowcount));
    if mycursor%found then
      dbms_output.put_line(to_char(rs.recordnumber) || rs.currentdate);
    else
      dbms_output.put_line('没有数据!');
    end if;
  else
    dbms_output.put_line('游标没有打开!');
  end if;
end;

##过程##
共同的特点是没有名称，只能存储为文件，然后通过执行文件的方式执行，因此称为无名块。
与此对应的是在pl/sql中也引入了高级程序设计的一些概念，其中最重要的就是过程。

过程就是高级程序设计语言中的模块的概念，将一些内部联系的命令组成一个个过程，通过参数在过程之间传递数据是模块化设计思想的重要内容。

create or replace procedure 过程名 as  --as关键字代替了无名块的declare
  声明语句段；
begin
  执行语句段；
exception
  异常处理语句段
end;

create or replace procedure tempprocedure as
  tempdate testtable.currentdate%type;
begin
  select currentdate into tempdate from testtable where recordnumber = 88;
  dbms_output.put_line(to_char(tempdate));
end;
--创建过程是一个数据定义命令。

调用：
begin
tempprocedure;
end;

#参数类型# 调用参数分割用,逗号分割
in参数
out参数
inout参数

create or replace procedure tempprocedure
(
  trn in testtable.recordnumber%type,
  td out testtable.recordnumber%type,
  tn in out testtable.recordnumber%type    --类型为number(4) or varchar2(20) 会有警告?  警告: 创建的过程带有编译错误。
)
as
  tempdate testtable.currentdate%type;
  tempnumber testtable.recordnumber%type;
begin
  select currentdate into tempdate from testtable where recordnumber = trn;
  dbms_output.put_line(to_char(tempdate));
  select recordnumber into tempnumber from testtable where recordnumber = trn;
  dbms_output.put_line(to_char(tempnumber));
  --td := '日期是：' || to_char(tempdate);
 -- tn := '序号是：' || tempnumber;
 td := 1111;
 tn := 2222;
end;

存储过程的调用
declare
  trn testtable.recordnumber%type;
  td  testtable.recordnumber%type;
  tn  testtable.recordnumber%type;
begin
  trn := 10;
  td := '';
  tn := '';
  tempprocedure(trn,td,tn);
  dbms_output.put_line(to_char(trn));
  dbms_output.put_line(to_char(td));
  dbms_output.put_line(to_char(tn));
end;

##序列##
CREATE SEQUENCE "NEWLIFEYHJ"."TESTSEQ" INCREMENT BY 1 START WITH
    1 MAXVALUE 20 MINVALUE 1 NOCYCLE
    CACHE 20 NOORDER

--查询序列值--
select TESTSEQ.nextval from dual;

select TESTSEQ.currval from dual;

insert into testtable values(TESTSEQ.currval,'17-5月 -13');

##异常处理##
系统预定义异常处理

自定义异常处理
1.定义异常
declare
  异常名 exception;
2.触发异常
  raise 异常名;
3.处理异常
  Exception
  when 异常名1 then
    异常处理语句段1;
  when 异常名2 then
    异常处理语句段2;

declare
  tempno integer := 90;
  exc exception;
begin
  tempno :=tempno+1;
  if tempno = 91 then
    raise exc;
  end if;
  exception
  when exc then
    dbms_output.put_line('自定义异常处理中……');
end;

-->p45/56  综合实例

1.功能设计
某高校开发的研究生招生系统，要求设计pl/sql程序对考生的成绩数据进行处理，处理的逻辑是根据每门专业课的最低分数线和总分的最低分数线自动将考生归类为录取考生，调剂考生和落选考生。

为此设计两个数据表，graduate数据表存放考生成绩，resut数据表存放处理结果，pl/sql程序完成的功能就是将graduate数据表的数据逐行扫描，根据分数线进行判断，计算出各科总分，在result数据表中将标志字段自动添加上"录取"或"落选"。

2.数据表设计
--研究生表
create table graduate(
  BH NUMBER(10) NOT NULL,
  XM VARCHAR2(10) NOT NULL,
  LB VARCHAR2(10) NOT NULL,
  YINGYU NUMBER(4,1) NOT NULL,
  ZHENGZHI NUMBER(4,1) NOT NULL,
  ZHUANYE1 NUMBER(4,1) NOT NULL,
  ZHUANYE2 NUMBER(4,1) NOT NULL,
  ZHUANYE3 NUMBER(4,1) NOT NULL
);

--结果表
create table "RESULT"(
  "BH" NUMBER(10) NOT NULL,
  "XM" VARCHAR2(10) NOT NULL,
  "LB" VARCHAR2(10) NOT NULL,
  "YINGYU" NUMBER(4,1) NOT NULL,
  "ZHENGZHI" NUMBER(4,1) NOT NULL,
  "ZHUANYE1" NUMBER(4,1) NOT NULL,
  "ZHUANYE2" NUMBER(4,1) NOT NULL,
  "ZHUANYE3" NUMBER(4,1) NOT NULL,
  "TOTALSCORE" NUMBER(5,1) NOT NULL,
  "FLAG" VARCHAR2(4) NOT NULL
);

3.录入数据
insert into graduate values(2003080520,'张三丰','硕士',55,56,67,78,89);
insert into graduate values(2003060555,'张翠山','硕士',66,78,78,89,92);
insert into graduate values(2003056066,'张无忌','硕士',76,67,89,90,66);
insert into graduate values(2003010989,'赵敏','硕士',45,59,74,66,56);
insert into graduate values(2003050677,'周芷若','硕士',77,67,72,87,66);
insert into graduate values(2003869401,'小昭','硕士',56,67,56,64,34);
insert into graduate values(2003340987,'阿离','硕士',68,93,64,80,56);
insert into graduate values(2003056709,'宋远桥','硕士',90,68,81,61,67);
insert into graduate values(2003100894,'殷素素','硕士',69,73,62,70,75);

###
open graduatecursor;
  if graduatecursor%notfound then
    raise errormessage;
  end if;
  loop
    fetch graduatecursor into graduaterecord;
    graduatetotalscore := graduaterecord.yingyu + graduaterecord.zhengzhi + graduaterecord.zhuanye1 + graduaterecord.zhuanye2 + graduaterecord.zhuanye3;
    if(graduaterecord.yingyu >= tempyingyu and graduaterecord.zhengzhi >= tempzhengzhi and graduaterecord.zhuanye1 >= tempzhuanye1 and graduaterecord.zhuanye2 >= tempzhuanye2 and graduaterecord.zhuanye3 >= tempzhuanye3 and graduatetotalscore >= temptotalscore) then
      graduateflag := '录取';
    else
      graduateflag := '落选';
    end if;
    exit when graduatecursor%notfound;
    insert into result values(graduaterecord.bh,graduaterecord.xm,graduaterecord.lb,graduaterecord.yingyu,graduaterecord.zhengzhi,graduaterecord.zhuanye1,graduaterecord.zhuanye2,graduaterecord.zhuanye3,graduatetotalscore,graduateflag);
  end loop;
  close graduatecursor;
  commit;
  exception
  when errormessage then
  dbms_output.put_line('无法打开数据表');

create or replace procedure graduateprocess
--(
  --tempzhengzhi in graduate.zhengzhi%type,
  --tempyingyu in graduate.yingyu%type,
  --tempzhuanye1 in graduate.zhuanye1%type,
  --tempzhuanye2 in graduate.zhuanye2%type,
  --tempzhuanye3 in graduate.zhuanye3%type,
  --temptotalscore in result.totalscore%type
--)
as
  --graduaterecord graduate%rowtype;
  --graduatetotalscore result.totalscore%type;
  --graduateflag varchar2(4);
  --cursor gradutecursor is select * from graduate;
  --errormessage exception;
begin
 
end;

###

4.程序设计
完整存储过程
/*定义过程参数*/
create or replace procedure graduateprocess
(
  tempzhengzhi in graduate.zhengzhi%type,
  tempyingyu in graduate.yingyu%type,
  tempzhuanye1 in graduate.zhuanye1%type,
  tempzhuanye2 in graduate.zhuanye2%type,
  tempzhuanye3 in graduate.zhuanye3%type,
  temptotalscore in result.totalscore%type
)
as
/*定义graduaterecord为记录型变量，临时存放通过游标从graduate数据表中提取的记录*/
  graduaterecord graduate%rowtype;
/*定义graduatetotalscore为数值型变量，统计总分*/ 
  graduatetotalscore result.totalscore%type;
/*定义graduateflag为字符型变量，根据结果放入"落选"或"录取"，然后写入数据表result*/
  graduateflag varchar2(4);
/*定义游标graduatecursor，存放的是所有的graduate数据表中的记录*/ 
  cursor graduatecursor is select * from graduate;
/*定义异常处理errormessage*/ 
  errormessage exception;
/*开始执行*/ 
begin
  /*打开游标*/
  open graduatecursor;
  /*如果游标没有数据，激活异常处理*/
  if graduatecursor%notfound then
    raise errormessage;
  end if;
  /*游标有数据，指针指向第一条记录，每执行fetch命令，就自动下移，循环执行到记录提取完毕为止*/
  loop
    fetch graduatecursor into graduaterecord;
    /*计算总分*/
    graduatetotalscore := graduaterecord.yingyu + graduaterecord.zhengzhi + graduaterecord.zhuanye1 + graduaterecord.zhuanye2 + graduaterecord.zhuanye3;
    /*判断单科和总分是否满足录取要求，若满足，graduateflag变量值为"录取"，否则为"落选"*/
    if(graduaterecord.yingyu >= tempyingyu and graduaterecord.zhengzhi >= tempzhengzhi and graduaterecord.zhuanye1 >= tempzhuanye1 and graduaterecord.zhuanye2 >= tempzhuanye2 and graduaterecord.zhuanye3 >= tempzhuanye3 and graduatetotalscore >= temptotalscore) then
      graduateflag := '录取';
    else
      graduateflag := '落选';
    end if;
    /*当游标数据提取完毕后，退出循环*/
    exit when graduatecursor%notfound;
    /*向结果数据表result中插入处理后的数据*/
    insert into result values(graduaterecord.bh,graduaterecord.xm,graduaterecord.lb,graduaterecord.yingyu,graduaterecord.zhengzhi,graduaterecord.zhuanye1,graduaterecord.zhuanye2,graduaterecord.zhuanye3,graduatetotalscore,graduateflag);
  end loop;
  /*关闭游标*/
  close graduatecursor;
  /*提交结果*/
  commit;
  /*异常处理，提示错误信息*/
  exception
  when errormessage then
  dbms_output.put_line('无法打开数据表');
  /*程序执行结束*/
end;

5.执行结果，程序调用。
主程序mainprocess
/*定义6个入口变量，分别对应graduate数据表中的专业课和总分分数线*/
declare
  score1 number(4,1);
  score2 number(4,1);
  score3 number(4,1);
  score4 number(4,1);
  score5 number(4,1);
  scoretotal number(5,1);
/*将分数线赋值，在这里修改各值就代表不同的分数线*/
begin
  score1 := 50;
  score2 := 56;
  score3 := 60;
  score4 := 62;
  score5 := 64;
  scoretotal := 325;
/*调用处理过程*/
  graduateprocess(score1,score2,score3,score4,score5,scoretotal);
end;

综合运用pl/sql的设计要素，就可以设计出很多复杂的处理程序，这也是DBA的一项重要任务。