oracle pl/sql 学习笔记（1）

declare
/* 定义一个Sql语句中使用的变量*/
v_Sex number;
v_AuthorCode char(6);
v_Arcount number;
begin
 /* 查询表中的数据*/
 select author_code, sex
  into v_AuthorCode, v_Sex
  from auths
 where name ='王大林';

/* 检查该作家是否为女作家，如果是女作家，那么查询其发表文章的数量*/
if v_Sex =0 then
  select count(article_code)
     into v_arcount
     from article;
   where author_code = 'A00001';
 end if
end;

declare
   v_Sex number;
   v_AuthorCode char(6);
   v_Arcount  number;
begin
   select author_code , sex
          into v_AuthorCode, v_Sex
   from auths
   where name = '王大林';
  
   if v_Sex = 0 then
      select count(article_code)
      into v_Arcount
      from article
      where author_code = 'A00001';
      -- ????? where author_code = :v_AuthorCode;
   end if;
end;

create table auths
( author_code  varchar2(10),
  sex          integer
  );
 
  alter table auths add (name varchar2(20));
 
  select * from auths;
 
  insert into auths values( 'A00001','0','王大林');
 
  select * from auths;
 
 
  create table article
  (article_code  integer,
   author_code   varchar2(10)
   )

insert into article values( 0001, 'A00001');
insert into article values( 0002, 'A00001');
insert into article values( 0003, 'A00001');
insert into article values( 0004, 'A00001');
select * from article;
  

块机构，变量与常量，循环结构以及游标是PL/SQL最重要的特性，

块(block)是pl/sql程序的基本执行单元。所有pl/sql程序都是由块组成的。
每个块完成程序中的部分工作，这样就可以将程序分成多个块，一个标准的块结构如下：

declare
  /* 块的定义部分，这里可以定义pl/sql变量、自定义类型、游标和局部子程序。
  这部分是可选部分*/
begin
  /* 块的执行部分， 这里放置一些可执行的sql 或pl/sql语句
     这是块中最重要的部分，并且也是块中必要的部分
     这里必须至少包含一个可执行语句
   */
exception
  /*
     异常处理部分，这里存放对错误进行的语句。这部分可选
   */
end;
其中，异常处理部分是用来报告和处理程序运行时遇到的错误的。oracle的这种将错误处理代码从程序主体中
分离出俩的结构，使的程序的结构更加清晰。

变量和常量：
pl/sql和数据库之间是通过变量进行数据交换的。变量是一个存储空间，它是在程序中取出数据或传入数据给程序

declare
  v_Name varchar2(10); ---字符类型
     v_CurrentDate DATE;  ---日期类型
 v_Salary;  NUMBER(8,2)-- 数值类型
 v_LoopCounter BINARY_INTEGER;  --整数类型
        v_CurrentRegistered BOOLEAN ;  --boolean类型

      以上变量对应的类型是oracle预定义的类型，pl/sql也支持用户自定义类型（例如：pl/sql 表和记录）。
      自定义类型是指用户定义程序中要进行处理的数据的结构，如下面例子：
     
delcare
      TYPE  Au_Record is  RECORD( --创建一个记录类型
 Author_code char(6),
 Name varchar2(10),
 sex NUMBER(1)
      );
      v_Author  Au_REcord; --声明一个Au_Record记录类型的变量

 
     常量的定义与变量的定义类似，只是在定义是必须增加一个关键字 constant,并且同时给它一个值，这以后就不能再给常量进行赋值
      了like:

      DECLARE
     sex_male   CONSTANT INT:=1;
     sex_female CONSTANT INT:=0;
     循环结构：
 pl/sql支持很多类型的循环结构。一个循环结构用来重复
     
      建表：
  
  create table Table_A(
       num_col number,
       char_col varchar2(60)
       );
      
      
declare
       v_LoopCounter binary_integer:=1;
begin
     loop
         insert into Table_A(Num_Col)
                values(v_LoopCounter);
                v_LoopCounter:=v_LoopCounter +1;
         exit when v_LoopCounter >100;
      end Loop;
end;

也可以使用其它类型的循环结构，例如for结构，下面的例子是用for循环实现与上例相同的功能：

declare
  v_LoopCounter binary_integer:=1;
begin
  for v_LoopCounter IN 300..350 Loop
      insert into table_a(num_col)
             values(v_loopCounter);
  end loop;
end;

游标：
游标用来查询数据库中的数据（例如select 语句返回的纪录），并对查询的结果进行处理。
游标分为显示游标和隐式游标两种，通过游标，可以对查询结果中的数据一条一条进行处理。例如：

declare
 v_Name varchar2(10);
 v_Salary number(8,2);
 -- define cursor
 cursor c_Auths is
 select name, salary from auths;
 
 begin
 open c_Auths; -- open cursor
 
  loop
  -- checkout a record
  fetch c_Auths into v_Name, v_Salary;
  -- when all the record checkout exit loop
  exit when c_Auths%notfound;
  /* transaction to record */
  end loop;
 
  -- end transaction
  close c_Auths;
  end ;

pl/sql的运行：
pl/sql程序是通过一个引擎来执行的。这个引擎安装在oracle的数据库服务器或一些客户端的应用开发工具（for example
Oracle forums or Oracle Reports)
PL/SQL程序可以在：
oracle 服务器
oracle 的一些应用开发工具
这两个环境是独立的，在任意一个环境中，pl/sql引擎都可以接收有小的pl/sql块或子程序，
这个引擎执行pl/sql程序中的过程性的语句，而将Sql语句送给服务器端的sql语句执行器运行，

pl/sql的组成元素

块结构：
class A：

declare
   --define part
begin
 -- transaction part
exception
 -- exception part
end;

or 只有可执行部分的块时，结构：
class B:

begin
 --transaction part
end;

or  块有定义部分和可执行部分（无异常）
class C:

declare
 --define part
begin
 --transaction part
end;

or 块中只有可执行部分和异常处理部分（无定义部分）

class D:
begin
 --defined part
exception
 --exception part
end;

块分类：
无名块：动态构造并只能执行一次
命名块：前面加了符号的无名块
子程序：包括存储在数据库中的存储过程，函数和包等。这些块一旦被定义后，便可随时调用。
触发器：他是存储在数据库中的块。这些块移动被构造后，就可以多次执行。当触发它的事件发生时
 调用该触发器。触发事件是对表中的数据的操作，如；insert, delete,update.

无名块的例子：
set serveroutput on size 10000 -- 设定存储缓存区的大小
declare
 /* 声明在块中要用到的变量 */
 v_TypeCode1               varchar2(6):='CC';
 v_TypeCode2               varchar2(6):='DD';
 v_TypeRemark1               varchar2(15):='Computer';
 v_TypeRemark2               varchar2(15):='C++ Language';
 v_OutPut                    varchar2(15);
 
begin
/*利用变量的值像表中插入两行*/
insert into type
   values(v_TypeCode1, v_TypeRemark1);

insert into type
   values(v_TypeCode2, v_TypeRemark2);
  
/* 查询刚插入表中的两行，并用dbms_output报输出结果*/

select typeremark
  into v_Output
From type
where typecode=v_TypeCode1;
Dbms_Output.put_line(v_OutPut);
/* 将结果显示在屏幕上*/

select typeremark
  into v_Output
From type
where typecode=v_TypeCode1;
Dbms_Output.put_line(v_OutPut);
/* 将结果显示在屏幕上*/

end;

一个命名块的例子；
<>
declare
 /* 声明在块中要用到的变量 */
 v_TypeCode1               varchar2(6):='CC';
 v_TypeCode2               varchar2(6):='DD';
 v_TypeRemark1               varchar2(15):='Computer';
 v_TypeRemark2               varchar2(15):='C++ Language';
 v_OutPut                    varchar2(15);
 
begin
 
/* 查询刚插入表中的两行，并用dbms_output报输出结果*/

select typeremark
  into v_Output
From type
where typecode=v_TypeCode1;
Dbms_Output.put_line(v_OutPut);
/* 将结果显示在屏幕上*/

select typeremark
  into v_Output
From type
where typecode=v_TypeCode2;
Dbms_Output.put_line(v_OutPut);
/* 将结果显示在屏幕上*/

end inserttype;

在无名块declare关键字前面加一个标号后，就变成了有名块，在end关键字后面也可以加标号名，表示
该命名块的结束。
块前的标号必须用符号<>括起来，块末的标号可有可无，也无须用符号"<>括起来

定义子程序的例子
--创建存储过程inserttype
create or replace procedure inserttype as

 /* 声明在块中要用到的变量 */
 v_TypeCode1               varchar2(6):='CC';
 v_TypeCode2               varchar2(6):='DD';
 v_TypeRemark1               varchar2(15):='Computer';
 v_TypeRemark2               varchar2(15):='C++ Language';
 v_OutPut                    varchar2(15);
 
begin
 
/* 查询刚插入表中的两行，并用dbms_output报输出结果*/

select typeremark
  into v_Output
From type
where typecode=v_TypeCode1;
Dbms_Output.put_line(v_OutPut);
/* 将结果显示在屏幕上*/

select typeremark
  into v_Output
From type
where typecode=v_TypeCode2;
Dbms_Output.put_line(v_OutPut);
/* 将结果显示在屏幕上*/

end inserttype;

注意，在END关键字后面必须加存储过程名。

一个触发器的例子

create or replace trigger salary_trigger
--当向Auths表insert a row or update a row 激活触发器
before insert or update of salary
       on auths
       for each row
begin
--如果插入值或修改值大于1000时，报告应插入的工资值
--并产生异常，退出该触发器
  if :new.salary >10000 then
      raise_application_error(-200060,'插入的工资应小于10000！');
  end if;
end  salary_trigger;

变量名：

declare
"author's age" number;
begin
"author's age":=21;
dbms_output.put_line("author's age");
end;

标识符
。标识符必须以字母开头
。标识符可以由一个或多个字母、数字（0-9）或特殊（$,#,_)组成
。标识符长度不超过30个字符
。标识符名内不能有空格

如果希望标识符能够区分大小写或包含其它的字符，则可以使用带双引号的标识符。
但标识符的长度不能是30个字符（不包括双引号）

rowid类：
每个数据库表都有一个rowid伪列，用于存储行标识符（rowid)。行标识符是一个固定场地的
16进制字符串。用来表示一条记录的存储地址。在行标识符中包含如下的信息：
。数据对象号
。数据文件（第一个文件为1）
。数据文件中的数据块
。数据块中的记录（第一条记录为0）；

使用行标识符可以快速访问特定的记录。通常，行标识符唯一确定一条记录。但是如果不同表中的
记录存储在相同的簇中，则这个记录具有相同的行标识符。

数据类型之间的转换
pl/sql能够处理标量数据类型中不同类型之间的转换。两种方式：
强制类型转换
自动类型转换
强制类型转换：
    强制类型转换是指通过使用函数来完成不同类型之间的转换。
 to_char
 to_date
 to_number
 rawtohex
 hextoraw
 chartorowid
 rowidtochar

alter table auths drop column brithdate;  --
alter table auths add   birthdate date;   --注意有什么不同
update auths set birthdate=to_date('19990101','yyyymmdd');

declare
v_birthdate date;
v_show varchar2(20);
begin
select birthdate
into v_birthdate
from  auths
where name ='yaoyu4';
v_show:=to_char(v_birthdate);
dbms_output.put_line('yaoyu birthday '|| v_show);
end;

复合类型
复合类型是指具有内部成员的类型，该成员可以被单独操作。通常，该类型的变量有一个或多个标量类型组成。
在pl/sql中有两种复合类型：
--记录
--表
与预定义的标量类型不同，在使用复合类型之前用户必须先定义该复合类型。定义记录类型的语法如下：
type recore_type is record(
 field1 type1[not null][:=expr1],
 field2 type2[not null][:=expr2],
 ...
 fieldn typen[not null][:=exprn];
其中record_type表示记录烈性的名字，field表示记录里域的名字，type表示记录里域的类型，expr表示域赋的初始值。

在定义记录类型时，对域的声明要注意一下几点：
。域的声明于记录外的变量声明类似，可以有not null约束和初始值。
。如果一个域没有赋初始值，系统自动将其赋值为null.
。在赋值时，“：=”可以用关键词default来代替。

当定义一个记录类型后，就可以声明记录类型的变量了。

declare
       type t_Article is Record(
            ArticleCode varchar2(10),
            --下面的声明中，用关键字default代替“：=”
            secrate_level  char(1) default '1',
            --下面的变量有not null约束。所以必须符值
            Title varchar2(120) not null:='not know');
            --声明t_ArticleRec记录类型的变量v_Article1和v_article2
            v_Article1 t_Article;
            v_Article2 t_Article;

begin
            v_Article1.ArticleCode:='AS6';
            v_Article2.Title:='C++Programing';
end;

也可以用select语句来为记录赋值，这种方法是从数据库中检索出数据，然后将他们
存入记录中。在这里要注意，记录中域的类型一定要和Select列表中的域类型类型一致：

declare
--定义一个记录，他的域和auths表中的一些域类型相同。
type t_AuthRec is record(
     AuthorCode   auths.author_code%type,
     name         auths.name%type,
     birthdate    auths.birthdate%type,
     EntryDateTime auths.entry_data_time%type);
    
     --声明一个记录类型变量来接收数据
     v_Author t_AuthRec;
begin
     --检索作家代码是A00009的作家信息，并将其存入v_Author记录变量中
     select author_code, name, birthdate, entry_data_time
     into v_Author
     From auths
     where author_code='A00005';
end;

在pl/sql中经常会遇到这样的声明--声明一个与数据库中各类别类型相同的纪录。
为了简化这种记录的声明，pl/sql提供%rowtype类似于%type,%rowtype返回一个记录类型，这个记录类型中的
域类型与定义该记录的表中各列的类型相同，
使用%rowtype声明的记录不包括数据库中列的not null约束，但包括varchar2列和char列的长度，number 列的精度和标度。当
表定义发生改变时，%rowtype声明的记录也发生相应的改变。

表：
定义一个表类型的语法如下所示：
type tabletype is table of type index by binary_integer

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/59111/viewspace-793902/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/59111/viewspace-793902/