PLSQL中的变量详解

一：简介：

PLSQL 是oracle公司开发的一套编程语言，是对SQL的扩充。
在PLSQL程序中，可以定义变量、数据类型、函数、存过，可以使用流控制语句,可以包含SQL语句，可以进行错误处理。
利用PLSQL程序，可以完成对数据库的复杂访问。

PLSQL是一种结构化编程语言，程序的基本单元是块，主要的形式有函数、存过、匿名块。PLSQL块由3个部分组成：

declare
   变量声明部分：可定义变量、类型、游标、子程序、触发器、异常，在本块范围内有效
begin
   可执行部分
exception
   异常处理部分
end;

  其中，变量声明部分、异常处理部分作为可选，可以没有。

begin
   可执行部分
end;  

实例

begin
   dbms_output.put_line('Hello,World!');
end;
--注意，在生产环境的脚本里面，不能用dbms_output.put_line，否则缓冲区满了就报错了

输出：

Hello,World!

二：PLSQL中的变量

1：变量的定义与使用

变量定义

--没有初始化、没有赋值的变量默认null
1： 变量名 类型 [defalut 默认值]
2： 变量名 类型[:= 默认值]

简单科普下Oracle中一些基本的数据类型

integer          相当于number(38)，可以省写为int
binary_integer   整数类型的数据，以2的补码二进制形式表述，循环计数器经常使用这种类型
number(m,n)      可表示整数和小数。m和n分别表示精度和小数位数。m在[1,38],n在[-84,127]
char(n)          固定长度的字符串，最大32767字节 【不常用】
varchar2(n)      可变长度的字符串，最大32767字节。但作为函数、存过的返回值(包括out 变量)，不能超过4000。
long             可变长度的字符串                  【不常用】
raw、long raw    存储二进制数据的可变长度字符串     【不常用】
date             日期类型，精确到秒。当前时间 sysdate
boolean          布尔类型，取值true、false
clob、blob       大对象类型、文本数据，最大4GB        【不常用】

变量赋值

--没有初始化、没有赋值的变量默认null
1：变量 := 值
2：select 查询列 into 变量 from dual；--如果查询结果为空into会报错

实例

set serveroutput on;
declare
   v_id      number(18) := 10;
   v_name    varchar2(255);
   v_isTrue  boolean := true;
   v_isTrue2 boolean;
begin
   dbms_output.put_line('v_id='||v_id);
   dbms_output.put_line('v_name='||v_name);--注意，没有初始化、没有赋值的变量默认null，做运算时要注意
   ---dbms_output.put_line('v_isTrue='||v_isTrue); --boolean不能直接转换为字符
   dbms_output.put_line('v_isTrue='||(case v_isTrue when true then 'true' when false then 'false' else null end));
   dbms_output.put_line('v_isTrue2='||(case v_isTrue2 when true then 'true' when false then 'false' else null end));
end;

输出

v_id=10
v_name=
v_isTrue=true
v_isTrue2=

2：%TYPE和%ROWTYPE定义类型

%type用于获取某个变量或者表中某个列的类型

实例

declare
   v_id      gsc_test.id%type; --gsc_test.id是你数据库中表名和列名
   v_name    varchar2(255);
   v_code    v_name%type :='code';
begin
   v_id := 1;
   v_name := '张三';
   v_code := 'zhangsan'; --覆盖声明里面的值了
   
   dbms_output.put_line('v_id='||v_id);
   dbms_output.put_line('v_name='||v_name);
   dbms_output.put_line('v_code='||v_code);
end;

输出

v_id=1
v_name=张三
v_code=zhangsan

%ROWTYPE来定义一个表示表中一行记录的变量，适用于字段非常多的表。还常常与自定义类型联合使用。

实例

drop table gsc_test;
create table gsc_test
(
   id    number(18),
   name  varchar2(255)
);


declare
   v_testRow  gsc_test%rowtype;
begin
   
   v_testRow.id := 1;
   v_testRow.name := 'A1';
   insert into gsc_test values v_testRow;
   
   v_testRow.id := 2;
   v_testRow.name := 'A2';
   insert into gsc_test values v_testRow;  
   
   v_testRow.id := 3;
   v_testRow.name := 'A3';
   insert into gsc_test values v_testRow;
   commit; 
end;

select * from gsc_test;

输出

ID	NAME
1	A1
2	A2
3	A3

三：在PLSQL中自定义类型

常用的自定义类型，包括记录类型和集合类型，都属于复合类型

1：记录类型 Record

Record类型的定义与Java中定义数据库对象非常相似。
其中，每个字段的定义与变量定义的方法完全一样，可以设置默认值。
定义了类型，就可以该类型的变量了。通过变量.字段对记录的字段进行访问。record只能存储一行数据。

type 类型名 is record
(
    字段1   定义,
    字段2   定义,
    ......
);

实例1

declare
   type StudentType is record
   (
      id      number(18),
      name    varchar2(255)
   );
   v_st1      StudentType;
   v_st2      StudentType;     
begin
   
   --第一种赋值方法,注意不用类型java一样，来个StudentType() 或者new StudentType();
   v_st1.id := 2;
   v_st1.name := '李四';
   
   --第二种赋值方法:通过select into,要恰好返回一行，否则报错。
   select id,name
     into v_st2
     from bm_bandwidth
    where id=2;
    
   dbms_output.put_line('v_st1.id='||v_st1.id);
   dbms_output.put_line('v_st1.name='||v_st1.name);
   dbms_output.put_line('v_st2.id='||v_st2.id);
   dbms_output.put_line('v_st2.name='||v_st2.name);
end;

输出

v_st1.id=2
v_st1.name=李四
v_st2.id=2
v_st2.name=A2

2：集合类型 is table of

我们在实际业务中查询的结果有可能返回的是一个结果集。此时，如果使用基本类型或自定义的记录类型，将会报错。
所以我们需要定义一个变量，是某种类型的集合。类似java中的ArrayList

语法

type 类型名 is table of 类型

集合操作方法：

extend(n)   最近n个空元素，不指定n，表示n=1                   【重点】
count       返归集合元素个数，集合为空返回0                  【重点】
first       返回集合第一个元素的下标，集合为空返回null        【少用】
last        返回集合最后一个元素的下标，集合为空返回null      【少用】
next(n)     返回第n个元素之后紧挨着的元素的下标               【少用】           
prior(n)    返回第n个元素之前紧挨着的元素的下标               【少用】
exists(n)   判断第n个元素是否存在，存在返回true，反之false    【少用】
DELETE(n)   删除元素下标为n的元素，如果n为null,则集合保持不变  【别用】

实例一

declare
   type StudentType is record
   (
      id      number(18),
      name    varchar2(255)
   );
   type StudentTab is table of StudentType;
   v_sts      StudentTab;     
begin
   
   v_sts := StudentTab();--初始化一下，不初始化，没法用
   v_sts.extend(1);--增加一个元素，没前面的初始化，这里就报错了
   --extend后，可以不赋值，集合字段没设置默认值的，都默认null
   v_sts(1).id := 100;
   v_sts(1).name := 'A100';
   
   v_sts.extend(1);--增加一个元素，没前面的初始化，这里就报错了
   v_sts(2).id := 200;
   v_sts(2).name := 'A200';
   
   --最简单的循环，表示i依次取值1、2、3、4、5 ... 直到v_sts.count，集合元素为空，count为0，不进入循环.
   for i in 1..v_sts.count
   loop
      dbms_output.put_line('v_sts('||i||').id='||v_sts(i).id);
      dbms_output.put_line('v_sts('||i||').name='||v_sts(i).name);
   end loop;
end;

输出

v_sts(1).id=100
v_sts(1).name=A100
v_sts(2).id=200
v_sts(2).name=A200

实例二，联合%rowtype、%type使用

declare
   type TestIdTab is table of gsc_test.id%type;
   type TestTab is table of gsc_test%rowtype;
   type VarTab is table of varchar2(255);
   type IntTab is table of integer;
   
   v_testIds1     TestIdTab;
   v_testIds2     TestIdTab;
   v_testrows1   TestTab;
   v_testrows2   TestTab;
   v_strs        VarTab;
   v_ints        IntTab;
   
begin
   
    v_testIds1 := TestIdTab(3,4,5); --初始化方法1，3个元素
    v_strs := VarTab('A3','A5');--初始化方法1，2个元素
    
    v_ints := IntTab();--初始化方法2，只是个空集合
    v_ints.extend(3);--增加3个元素,没上面的初始化，就直接进行操作，那要报错的
    v_ints(1) := 11; --没有上面extend来增加元素，就操作具体元素，那要报错的
    v_ints(2) := 12;
    v_ints(3) := 13;
    
    --有多个字段的集合初始化方法一
    v_testrows1 := TestTab();
    for i in 1..5  --最简单的循环，表示i依次取值1、2、3、4、5
    loop
       v_testrows1.extend;--不输入(n)，表示每次扩展一个元素
       v_testrows1(i).id := i;
       v_testrows1(i).name := 'B'||i;
    end loop;
    
    --有多个字段的集合初始化方法二
    --v_testrows2有没有通过TestTab()初始化都可以的
    --注意对集合赋值，要通过bulk collect 
    select id,name
      bulk collect        
      into v_testrows2
      from temp_zpy_test 
     where id <=3;   --查询不到结果集，则v_testrows2是空集(已初始化过)
     
    -- v_testIds2 也可以类似初始化
    select id
      bulk collect 
      into v_testIds2
      from temp_zpy_test;
      
    --结果输出
    --集合变量.count表示取元素长度，集合无元素返回0，但集合变量必须初始化过，否则报错
    for i in 1..v_testIds1.count
    loop
       dbms_output.put_line('v_testIds1('||i||')='||v_testIds1(i));
    end loop;
    
    for i in 1..v_testIds2.count
    loop
       dbms_output.put_line('v_testIds2('||i||')='||v_testIds2(i));
    end loop;
    
    for i in 1..v_strs.count
    loop
       dbms_output.put_line('v_strs('||i||')='||v_strs(i));
    end loop;
    
    for i in 1..v_ints.count
    loop
       dbms_output.put_line('v_ints('||i||')='||v_ints(i));
    end loop;
    
    for i in 1..v_testrows1.count
    loop
       dbms_output.put_line('v_testrows1('||i||').id='||v_testrows1(i).id);
       dbms_output.put_line('v_testrows1('||i||').name='||v_testrows1(i).name);
    end loop;
    
    for i in 1..v_testrows2.count
    loop
       dbms_output.put_line('v_testrows2('||i||').id='||v_testrows2(i).id);
       dbms_output.put_line('v_testrows2('||i||').name='||v_testrows2(i).name);
    end loop;
    
    --结果可以入库
    for i in 1..v_testrows1.count
    loop
       insert into gsc_test values v_testrows1(i);
    end loop;
end;

输出

v_testIds1(1)=3
v_testIds1(2)=4
v_testIds1(3)=5
v_testIds2(1)=1
v_testIds2(2)=2
v_testIds2(3)=3
v_strs(1)=A3
v_strs(2)=A5
v_ints(1)=11
v_ints(2)=12
v_ints(3)=13
v_testrows1(1).id=1
v_testrows1(1).name=B1
v_testrows1(2).id=2
v_testrows1(2).name=B2
v_testrows1(3).id=3
v_testrows1(3).name=B3
v_testrows1(4).id=4
v_testrows1(4).name=B4
v_testrows1(5).id=5
v_testrows1(5).name=B5
v_testrows2(1).id=1
v_testrows2(1).name=A1
v_testrows2(2).id=2
v_testrows2(2).name=A2
v_testrows2(3).id=3
v_testrows2(3).name=A3

3：集合类型的特殊定义

实例一，无需初始化集合定义集合

type 类型名 is table of 类型 index by binary_integer;
--不需要初始，不能extend。元素实际数量，就是被赋值的元素个数.

declare
   type IntTab is table of integer index by binary_integer;
   v_ints        IntTab;
   v_idx         binary_integer; --特殊的下标定义，类似java中集合的索引
begin

   v_ints(2) := 1000;
   v_ints(8) := 20000;
   v_ints(100) := 30000; 
   v_ints(13) := 40000;--注意下标13在100后面初始化，对结构有影响不？
   
   --由于下标不连续，不好用for循环了
   
   v_idx := v_ints.first;--即使上面没有对v_ints没有赋值，也不会报错，返回null
   dbms_output.put_line('v_ints.count=' || v_ints.count);
   while v_idx is not null --最简单的一个while，满足条件就进入循环体，不满足就结束了
   loop
      dbms_output.put_line('v_ints('||v_idx||')='||v_ints(v_idx));
      v_idx := v_ints.next(v_idx);
   end loop;
   
   --注意观察13、100的位置
   --访问不存在的下标会报错
   --dbms_output.put_line('v_ints(99)='||v_ints(99));
end;

输出

v_ints.count=4
v_ints(2)=1000
v_ints(8)=20000
v_ints(13)=40000
v_ints(100)=30000

实例二：只对部分字段赋值

declare
   type TestTab is table of gsc_test%rowtype index by binary_integer;
   v_testrows    TestTab;   
   v_idx         binary_integer; 
begin

   --只对部分字段赋值看看
   v_testrows(5).id := 100;
   v_testrows(100).name := 100;
   v_testrows(80).id := 1000;
   
   v_idx := v_testrows.first;--即使上面没有对v_testrows没有赋值，也不会报错，返回null
   dbms_output.put_line('v_testrows.count=' || v_testrows.count);
   while v_idx is not null --最简单的一个while，满足条件就进入循环体，不满足就结束了
   loop
      dbms_output.put_line('v_testrows('||v_idx||').id='||v_testrows(v_idx).id);
      dbms_output.put_line('v_testrows('||v_idx||').name='||v_testrows(v_idx).name);
      v_idx := v_testrows.next(v_idx);
   end loop;
end;

输出

v_testrows.count=3
v_testrows(5).id=100
v_testrows(5).name=
v_testrows(80).id=1000
v_testrows(80).name=
v_testrows(100).id=
v_testrows(100).name=100

实例五，字符类型的下标

--下标可以是字符
--可以实现类似key+value的数据缓存，当然，根据key取值时key要存在，否则直接取就要报错了
declare
   type IntTab is table of integer index by varchar2(255);
   v_ints        IntTab;
   v_idx         varchar2(255);
begin

   v_ints('ABCD') := 1000;
   v_ints('ABCD') := 20000;--写个重复的看看，注意观察
   v_ints('CDF') := 30000; 
   v_ints('中国') := 40000;
      
   v_idx := v_ints.first;--即使上面没有对v_ints没有赋值，也不会报错，返回null
   dbms_output.put_line('v_ints.count=' || v_ints.count);
   while v_idx is not null --最简单的一个while，满足条件就进入循环体，不满足就结束了
   loop
      dbms_output.put_line('v_ints('||v_idx||')='||v_ints(v_idx));
      v_idx := v_ints.next(v_idx);
   end loop;
   
   --访问不存在的下标会报错
   --dbms_output.put_line('v_ints(A)='||v_ints('A'));
end;

输出

--多次对同一个下标进行赋值，之前的值会被覆盖
v_ints.count=3
v_ints(ABCD)=20000
v_ints(CDF)=30000
v_ints(中国)=40000