PLSQL存储过程概要

 

1. PL/SQL(Procedural Language/SQL)是在标准SQL的基础上增加了过程化处理的语言，是Oracle对SQL的扩充。   
2.   
3. SQL(Structured Query Language)是一种对关系数据库管理系统(RDBMS)进行操作的标准结构化语言，它用来设置、使用和维护关系数据库。   
4.   
5. PL/SQL正是一种增加了过程化概念的SQL语言，它有以下一些标准SQL所没有的特征：   
6. 变量(包括预先定义的和自定义的)、控制结构(IF-THEN-ELSE)、自定义的存储过程和函数、对象类型  

PL/SQL特性
1、块结构
块是PL/SQL程序的基本执行单元，所有的PL/SQL程序都是由块组成的。每个块完成程序中的部分工作，这样就

可将程序分成多个块。
DECLARE
  /*块定义部分，这儿可定义PL/SQL变量、自定义类型、游标和局部子程序。这部分是块中可选部分。*/
BEGIN
  /*块中执行部分，这里放置一些可执行的SQL或PL/SQL语句。这儿必须至少包含一条可执行语句。*/
EXCEPTION
  /*异常处理部分，这里放置对错误进行处理的语句。这部分是块中可选部分*/
END;
2、变量与常量

3、循环结构
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>10;
  end loop;
end;
___
declare
  v_LoopCounter BINARY_INTEGER:=1;
begin
  for v_LoopCounter in 1..10 loop
    insert into Table_A(num_col) values(v_LoopCounter);
  end loop;
end;

4、游标
游标用来查询数据库中的数据，并对查询的结果进行处理。
  num_col NUMBER,
  char_col VARCHAR2(60)
__
declare
  v_num number;
  v_char varchar2(60);
  --游标定义。
  cursor c_tablea is
    select num_col,char_col from table_a;
begin
  open c_tablea;--打开游标。
  loop
    --检索一条记录
    fetch c_tablea into v_num,v_char;
    --当所有的记录都被检索出后退出循环。
    exit when c_tablea%notfound;
    /*对检索出来的数据进行处理。*/
  end loop;
  --结束处理
  close c_tablea;
end;


块的分类
块可以分为以下四类：

一、无名块：动态构造并只能执行一次。
--set SERVEROUTPUT on size 10000 --设置存储缓存区的大小。
declare
  /*声明在块中要用到的变量。*/
  v_TypeCode1 number:=200;
  v_TypeCode2 number:=201;
  v_TypeRemark1 varchar2(60):='Computer';
  v_TypeRemark2 varchar2(60):='C++ Language';
  v_OutPut varchar2(60);
begin
  /*利用变量的值向表中插入两行。*/
  insert into table_a values(v_TypeCode1,v_TypeRemark1);
  insert into table_a values(v_TypeCode2,v_TypeRemark2);
  /*查询刚插入表中的两行，并使用DBMS_OUTPUT包输出结果。*/
  select char_col into v_Output from table_a where num_col=v_TypeCode1;
  DBMS_OUTPUT.put_line(v_output); --将结果显示在屏幕上。
end inserttype;

二、命名块：前面加了标号的无名块。
declare前加<<inserttype>>

三、子程序：包括存储在数据库中的存储过程、函数和包等。这些块一旦被定义后，便可随时调用。
--创建存储过程inserttype
create or replace procedure inserttype as
  /*声明在块中要用到的变量。*/
  v_TypeCode1 number:=200;
  v_TypeCode2 number:=201;
  v_TypeRemark1 varchar2(60):='Computer';
  v_TypeRemark2 varchar2(60):='C++ Language';
  v_OutPut varchar2(60);
begin
  /*利用变量的值向表中插入两行。*/
  insert into table_a values(v_TypeCode1,v_TypeRemark1);
  insert into table_a values(v_TypeCode2,v_TypeRemark2);
  /*查询刚插入表中的两行，并使用DBMS_OUTPUT包输出结果。*/
  select char_col into v_Output from table_a where num_col=v_TypeCode1;
  DBMS_OUTPUT.put_line(v_output); --将结果显示在屏幕上。
end inserttype;
当将无名块中的关键字declare改变成关键字create or replace procedure时，该无名块就变成了存储过程。

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

四、触发器：它是存储在数据库中的块。这些块一旦被构造后，就可以多次执行。当触发它的事件发生时调用
该触发器。触发事件是指对表中数据的操作，如插入、删除和修改。
--创建触发器salary_trigger
create or replace trigger salary_trigger
  --当向table_a表中插入一行或修改一行后激活触发器。
  before insert or update of num_col on table_a for each row
begin
  --如果插入值或修改值大于10000时，报告应插入值
  --并产生异常，退出该触发器。
  if:new.num_col>10000 then
    raise_application_error(-200060,'插入值应小于10000!');
  end if;
end salary_trigger;


变量名
PL/SQL不区分大小
数据类型
系统提供四种数据类型：标量类型、复合类型、引用类型和LOB类型。
一、标量类型：numeric character raw rowid date boolean trusted
1、numeric类用来存放整数、实数和浮点数。包括binary_integer number pls_integer
  binary_integer类型用于存储带符号的整数，取值范围为-2147483647~2147483647。pls_integer值和

binary_integer值比number值占用较少的存储空间。但是，binary_integer操作较pls_integer操作慢。
  number类型可以存储定点数和浮点数，取值范围为1.0#-130~9.99E125。
定点数number(precision,scale) number(4,2) 12.345 -> 12.35
整型数number(precision) 其中precision为精度。
定义浮点数number这是一个精度为38位的十进制浮点数。
  pls_interger类型用于存储带符号的整数，取值范围与binary_integer的取值范围相同。
2、character类的变量用来存放字符串，类型有：char varchar2 long nchar nvarchar2
  char类型用来存放固定长度的字符数据，数据的内部表示取决于当前数据库的字符集。char(n)类型的变量

用于存储多字节字符，则它的最大长度小于n字符，如果数据不满n个字符，则末尾用空格填充。经测试，如果

是char类型，在执行sql时会自动将空格trim处理。可以说character是char的别名。最大长度为2000个字节。
  varchar2是变长字符类型。最大长度不能大于4000字节。
  long类型用于存储可变长字符数据，最大长度为32760字节。
  nchar
  nvarchar
3、raw类，包括raw、long raw
  raw用来存放固定长度的二进制数据。RAW类型类似于character类型，不同点在于将raw类型从一个系统转换

到另一个系统时，Net8不能进行相应的字符集转换。最大长度2000字节。
  long raw最大长度是32760字节。
4、rowid类，每个数据库都有一个rowid伪列，用于存储行标识符，行标识符是固定长度的十六进制字符串，

用来表示一条记录的存储地址。
5、date类型用于存储长度固定的日期和时间值。日期的缺省值是当月的第一天，时间的缺省值为午夜12:00
PL/SQL中的DATE变量与数据库中DATE类型的列相同。
6、boolean类型用于存储逻辑值：true、false和null。null用来表示一个错误的、不合适的或不确定的值。
boolean类型的变量只能用在逻辑操作中，不能将该类型的值插入到数据库中。DB中不存储这一类型的值。
7、trusted类型的变量被用在Trusted Oracle中存储变长的二进制标签。



数据类型之间的转换
1、强制类型转换，通过使用函数来完成不同类型之间的转换。这些函数也可以是标准SQL中数据类型转换函数
to_char将numeric或date类型值转换成varchar2类型值
to_date将character类型值转换成date类型值
to_number将character类型值转换成number类型值
2、自动类型转换
PL/SQL能够自动将一些类型的数据转换成另外一种类型的数据。
如可将numeric类数据自动转换成字符串并赋给character类的变量。

复合类型：指具有内部成员的类型，该成员可以被单独操作。包括：记录、表。
1、记录
(1)下面首先定义一个关于作家文章信息的记录类型t_ArticleRec，然后声明了两个类型的变量v_Article1和

v_Article2；
declare
  type t_ArticleRec is record(
    ArticleCode varchar2(10),
    --在下面的域声明中，用关键字default代替了":="
    Secrate_level char(1) default '1',
    --下面的变量有not null约束，所以必须赋初值
    title varchar2(120) not null :='not know'
  );
  --两个类型的变量v_Article1和v_Article2
  v_Article1 t_ArticleRec;
  v_Article2 t_ArticleRec;
begin
  v_Article1.Articlecode:='AS6';
  v_Article2.Title:='C++ Programing';
end;
(2)、前面已定义的记录类型为t_ArticleRec的两个变量v_Article1、v_Article2可以互相赋值：
   v_Article1:=v_Article2;
(3)可以用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_date_time%type
  );
  --声明一个记录类型变量来接收数据。
  v_author t_authrec;
begin
  --检索作家代码是A00009的作家信息，并将其存入v_author记录变量中。
  select author_code,name,birthdate,entry_date_time into v_author from auths
    where author_code='A00009';
end;
(4)、PL/SQL中经常会遇到这样的声明－声明一个与数据库表中各列类型相同的记录。为了简化这种记录的声

明,PL/SQL提供了%ROWTYPE操作符，其返回一个记录类型，这个记录类型中域类型与定义该记录的表中各列的

类型相同。下面定义了一个与表article相同的记录：
  declare
    v_article article%rowtype;
但是这里%rowtype声明的记录不包括数据库表中列的not null约束，但包括其它如长度、精度、标度。

2、表
(1)先定义两个表类型t_nametable、t_address，然后分别声明这两种类型的变量：
declare
  --定义表类型t_nametable，其元素类型是auths数据库表中name列的类型。
  type t_nametalbe is table of auths.name%type index by binary_integer;
  --定义表类型t_address
  type t_addresstable is table of auths.address%type index by binary_integer;
  --声明两个表类型变量
  v_name t_nametable;
  v_address t_addresstable;
--为表元素赋值
begin
  v_name(2):='wang';
  v_address(-3):='street 1';
end;
(2)下面定义一个记录表t_authorrecord，表元素类型是与数据库表auths匹配的记录类型，然后检索数据库表

auths中作家代码是"A00009"的作家记录，并将其存放到创建的t_authorrecord中：
declare
  type t_authorrecord is table of auths%rowtype index by binary_integer;
  --每一个元素都是一条记录
  v_auths t_authorrecord;
begin
  --检索作家代码是A00009的作家，并存放到v_auths(00009)中。
  select * into v_auths(00009) from auths where author_code='A00009';
  v_auths(00009).name:='Join';
end
......

引用类型
PL/SQL中的引用类型变量与C中的指针类似，该类型用于存储指向存储空间的指针。包括游标变量(ref
cursor)和对象引用类型(ref object type)

lob类型
lob类型用来存储大对象
用户自定义子类型