oracle存储过程--数据结构(上)

柔光的暖阳◎ 2022-01-10 03:21 294阅读 0赞

这篇主要想说的是plsql语言的自定义数据类型。

和编程语言java类比，我们刚入门的时候，最初学习的就是基础数据类型，

然后学习一个main函数，编写代码熟悉java关键字和逻辑控制语句，然后

就是java中常用集合的使用。这些会了以后，就是编写最简单的类：

java bean或pojo类。

类比java的入门，在学习plsql语言时，可以类比记忆，更易理解。

存储过程中也是plsql语句的集合，在匿名代码块，方法，触发器中都是plsql语句。

所以他们之间区别只在于用途。比如匿名代码块（declare begin end;）的用途与

存储过程相比就是简单的执行一次，类似java中的main函数或者测试代码。

存储过程是oracle的对象，这种被封装的对象程序可以被多次调用。类似java有具体功能的类。方法的用途在于公用性强，有返回结果，可以嵌在sql中。。。

知道了这些，在下面做实验就不必纠结于同样的代码在匿名代码块和存储过程中的区别。

有个注意的地方，代码块在test window中不能设断点，只能单步调试。

说道自定义类型，其实和java中java bean一个道理。定义使用type关键字。

关于plsql中容器类型，目前我只知道

record，table,array,varray,subtype, object。

表中的字段或者行也可以

被定义成type。例如：变量名 Table\_xxx.column\_xxx%type。

## Table\_xxx.column\_xxx%type和Table\_xxx%rowtype ##

-- Created on 2013/7/11 by lsy 
    -- rowtype测试1
    declare
      v_description lsy_codebook.description%type;
      v_myrow_one lsy_codebook%rowtype;
    begin
      select * into v_myrow_one from lsy_codebook where id = 500;
      select description into v_description from lsy_codebook where id = 500;
      dbms_output.put_line('v_myrow_one.id:' || v_myrow_one.id || ',v_myrow_one.codecode:' ||
                           v_myrow_one.codecode || ',v_myrow_one.codename:' || v_myrow_one.codename);
      dbms_output.put_line('v_description:' || v_description);                   
    end;
    
    /*打印
    v_myrow_one.id:500,v_myrow_one.codecode:qw,v_myrow_one.codename:全网
    v_description: changed
    
    */

## 记录类型record ##

-- Created on 2013/7/11 by lsy 
    -- record测试1
    declare
      type type_myrecord is record(
        r_codecode lsy_codebook.codecode%type  not null  :='i am default value',
        r_codename lsy_codebook.codename%type,
        r_result varchar2(32) default null,
        r_flag number default 0
        );
    
      v_one type_myrecord;
    begin
      v_one.r_codename := 'ww';
      --v_one.r_result:='success!';
      v_one.r_flag:=1;
      dbms_output.put_line('v_one.r_codecode:' ||
                           v_one.r_codecode ||
                           ',v_one.r_codename:' ||
                           v_one.r_codename||
                           ',v_one.r_result:'||v_one.r_result||
                           ',v_one.r_flag:'||v_one.r_flag);
    end;
    
    /*打印
    v_one.r_codecode:i am default value,v_one.r_codename:ww,v_one.r_result:,v_one.r_flag:1
    
    */

从上可以看出record很像java bean。

其中的每个字段都是这个record的一个属性。赋值和取值都是通过.属性实现。

这种数据类型的定义如果在存储过程中只用一次，这种声明方式是可以的。

但是为了共享和复用，还是建议把它声明到package中。

下面给个例子:

在package中声明一个record，写一个存储过程它的返回类型是这个record类型。

在其他地方调用存储过程，用这种record类型变量接收返回值，并把信息展现。

包

create or replace package lsy_package_two is
    
      --类型
      type type_myrecord1 is record(
        r_codecode lsy_codebook.codecode%type not null := 'i am default value',
        r_codename lsy_codebook.codename%type,
        r_result   varchar2(32) default null,
        r_flag     number default 0);
    
    end lsy_package_two;

存储过程

create or replace procedure pro_eleven(res out lsy_package_two.type_myrecord1) is
    
      v_one lsy_package.type_myrecord1;
    begin
      select codecode, codename, 'success', 1
        into res
        from lsy_codebook
       where id = 500;
      dbms_output.put_line('处理结果信息如下：');
      dbms_output.put_line('res.r_codecode:' || res.r_codecode ||
                           ',res.r_codename' || res.r_codename ||
                           ',res.r_result' || res.r_result || ',res.r_flag' ||
                           res.r_flag);
    
    end pro_eleven;

匿名代码块测试：

-- Created on 2013/7/11 by lsy 
    -- record测试2.在package定义，返回该类型的record
    declare
    
      v_result lsy_package_two.type_myrecord1;
    begin
      pro_eleven(v_result);
      dbms_output.put_line('调用存储过程pro_eleven的返回结果信息record：');
      dbms_output.put_line('v_result.r_codecode:' || v_result.r_codecode ||
                           ',v_result.r_codename:' || v_result.r_codename ||
                           ',v_result.r_result:' || v_result.r_result ||
                           ',v_result.r_flag:' || v_result.r_flag);
    end;
    
    /*打印
    处理结果信息如下：
    res.r_codecode:qw,res.r_codename全网,res.r_resultsuccess,res.r_flag1
    调用存储过程pro_eleven的返回结果信息record：
    v_result.r_codecode:qw,v_result.r_codename:全网,v_result.r_result:success,v_result.r_flag:1
    
    */

从上面可以看出这样使用自定义数据类型record才更像编程。

## 数组类型array 和 varray ##

关于数组，我从网上查了很多文章，基本上都是抄自同一篇文章。上来就说plsql里面没有数组，然后就开始说plsql表table怎么使用。

但是我确实查到文章说array和varray的使用。这个不是数组是什么？！

我也是一个新手，初学plsql语言，不保证说的都是对的。但是我尽量用实验数据说话，用实例说明和介绍知识。从此也看出人们的学习态度是怎样的。

大牛总是自己写文章，写的多了自然就更牛。不求甚解的人只能做半吊子程序员。

不多说了，说这么多也是因为网上的资料不太好找才去百度上看别人的文章，官网上找了很长

时间也找不到类似于教程或者手册的文档，有什么白皮书我就不想看，太深了。所以干脆看

别人博客，自己写实例来学习。

定义 TYPE varray\_name IS VARRAY(size) OF element\_type \[NOT NULL\];

数组array的遍历

create or replace procedure pro_twelve is
      type type_arr is array(3) of varchar2(100);
      v_arr_one type_arr := type_arr('a', 'b', 'c');
    begin
      /*这样循环遍历是错误的，报v_arr_one不是游标
      for v_temp in v_arr_one loop
        dbms_output.put_line(v_temp);
      end loop;*/
      --正确的遍历
      v_arr_one(3):='e';
      for i in 1 .. v_arr_one.count loop
        dbms_output.put_line(i||':'||v_arr_one(i));
      end loop;
    
    end pro_twelve;
    /*测试打印
    1:a
    2:b
    3:e
    */

关于array 和 varray的区别，没找到相关文章，自己通过实验也没发现有什么区别，

我认为两者的使用是没有区别的。

Array例子

create or replace procedure pro_twelve_2 is
      type type_arr is array(3) of varchar2(100);
      v_arr_one type_arr:= type_arr('a');
    begin
      /*array的变量必须有初始值，像v_arr_one type_arr;这样
      的声明就会报错：引用未初始化的收集。*/
      
      /*即使在初始化时已经赋值，它的长度不是你声明时的长度，而是
      你初始化赋值时的长度。例如，上面的长度是1，而不是3.这样赋值
      v_arr_one(2):='e';就会报错：下标超出数量。如果想要使用未占用
      的空间，就必须使用array的扩展extend。注意，即使扩展了，array
      还是定长的，不能超过声明时的长度*/
      
      v_arr_one.extend;
      v_arr_one(2):='e';
      --每一次在未使用空间添加元素，必须调用一下扩展。否则还是会报错
      v_arr_one.extend;
      v_arr_one(3):='ff';
      for i in 1 .. v_arr_one.count loop
        dbms_output.put_line(i||':'||v_arr_one(i));
      end loop;
    
    end pro_twelve_2;
    /*测试打印
    1:a
    2:e
    3:ff
    
    */

Varray例子

create or replace procedure pro_twelve_3 is
      type type_arr is varray(3) of varchar2(100);
      v_arr_one type_arr:= type_arr('a');
    begin
      /* varray的变量和定长数组array一样必须有初始值，像v_arr_one type_arr;这样
      的声明就会报错：引用未初始化的收集。*/
      
      /*即使在初始化时已经赋值，它的长度不是你声明时的长度，而是
      你初始化赋值时的长度。例如，上面的长度是1，而不是3.这样赋值
      v_arr_one(2):='e';就会报错：下标超出数量。如果想要使用未占用
      的空间，就必须使用varray的扩展extend。注意，即使扩展了，varray
      还是定长的，不能超过声明时的长度*/
      
      v_arr_one.extend;
      v_arr_one(2):='e';
      --每一次在未使用空间添加元素，必须调用一下扩展。否则还是会报错
      v_arr_one.extend;
      v_arr_one(3):='f';
      for i in 1 .. v_arr_one.count loop
        dbms_output.put_line(i||':'||v_arr_one(i));
      end loop;
    
    end pro_twelve_3;
    /*测试打印
    1:a
    2:e
    3:f
    
    */

从上可以看出，数组array和 varray在声明时必须指定长度，而且必须有初始值。

它的实际长度是初始赋值时的长度，每次想要使用未占用的空间必须扩展extend。

这样的数据结构更像一个矩阵，而不是我们通常意义上的数组。不知道甲骨文的那帮人

为什么定义出这样的数据结构，总感觉用处不大。怪不得好多人一说oracle的数组，

马上就开始说plsql表table。plsql表table更像我们理解上的数组结构。

数组的集合函数：

create or replace procedure pro_twelve_4 is
      type type_arr is varray(4) of varchar2(100);
      v_arr_one type_arr := type_arr('a', 'b');
    begin
      /* 相关集合函数*/
      --扩展extend的使用，后面可以带参数表示扩展几位，这个数字不能超过
      --未占用的数字大小，比如声明定长4，初始化用了2位，那最多扩展2位
      dbms_output.put_line('数组定长:' || v_arr_one.limit);
      dbms_output.put_line('数组实际长度:' || v_arr_one.count);
      dbms_output.put_line('数组首位元素下标:' || v_arr_one.first);
      dbms_output.put_line('数组末位元素下标:' || v_arr_one.last);
      for i in 1 .. v_arr_one.count + 1 loop
        if (v_arr_one.exists(i)) then
          dbms_output.put_line(i || ':' || v_arr_one(i));
        else
          dbms_output.put_line('第' || i || '位不存在');
        end if;
      end loop;
      dbms_output.put_line('---------------------');
      
      
      v_arr_one.extend(2);--一旦扩展，数组的实际长度就是扩展后的长度，默认元素null
      
      dbms_output.put_line('数组定长:' || v_arr_one.limit);
      dbms_output.put_line('数组实际长度:' || v_arr_one.count);
      dbms_output.put_line('数组首位元素下标:' || v_arr_one.first);
      dbms_output.put_line('数组末位元素下标:' || v_arr_one.last);
      
      for i in 1 .. v_arr_one.count + 1 loop
        if (v_arr_one.exists(i)) then
          dbms_output.put_line(i || ':' || v_arr_one(i));
        else
          dbms_output.put_line('第' || i || '位不存在');
        end if;
      end loop;
      dbms_output.put_line('---------------------');
    
      v_arr_one(2) := 'e';
      v_arr_one(3) := 'f';
      v_arr_one(4) := 'g';
    
      dbms_output.put_line('数组定长:' || v_arr_one.limit);
      dbms_output.put_line('数组实际长度:' || v_arr_one.count);
      dbms_output.put_line('数组首位元素下标:' || v_arr_one.first);
      dbms_output.put_line('数组末位元素下标:' || v_arr_one.last);
      for i in 1 .. v_arr_one.count + 1  loop
        if (v_arr_one.exists(i)) then
          dbms_output.put_line(i || ':' || v_arr_one(i));
        else
          dbms_output.put_line('第' || i || '位不存在');
        end if;
      end loop;
      dbms_output.put_line('---------------------');
      
      /*.count和.last都是数组实际长度，我认为没什么区别*/
      dbms_output.put_line('------还可以像这样遍历---------------');
      for i in v_arr_one.first .. v_arr_one.last + 1 loop
        if (v_arr_one.exists(i)) then
          dbms_output.put_line(i || ':' || v_arr_one(i));
        else
          dbms_output.put_line('第' || i || '位不存在');
        end if;
      end loop;
      dbms_output.put_line('---------------------');
      
    
    end pro_twelve_4;
    /*测试打印
    数组定长:4
    数组实际长度:2
    数组首位元素下标:1
    数组末位元素下标:2
    1:a
    2:b
    第3位不存在
    ---------------------
    数组定长:4
    数组实际长度:4
    数组首位元素下标:1
    数组末位元素下标:4
    1:a
    2:b
    3:
    4:
    第5位不存在
    ---------------------
    数组定长:4
    数组实际长度:4
    数组首位元素下标:1
    数组末位元素下标:4
    1:a
    2:e
    3:f
    4:g
    第5位不存在
    ---------------------
    ------还可以像这样遍历---------------
    1:a
    2:e
    3:f
    4:g
    第5位不存在
    ---------------------
    
      
      */

## Plsql表table ##

看看table的操作;

create or replace procedure pro_thirteen is
      type type_table is table of varchar2(32) index by BINARY_INTEGER;
      v_one type_table; --table可以不初始化赋值，实际上根本不能初始化赋值！
    begin
      -- v_two:=type_table('aa','bb');--报错，table不能这样赋值,网上有这样写的，但是实际通不过！
      -- 正确赋值方式
      v_one(1) := 'aa';
      v_one(2) := 'bb';
      dbms_output.put_line('v_one限定大小:' || v_one.limit);
      --限定大小总是null
      dbms_output.put_line('v_one实际大小:' || v_one.count);
      dbms_output.put_line('v_one首位元素下标:' || v_one.first);
      dbms_output.put_line('v_one末位元素下标:' || v_one.last);
      -- table这样变长的类型limit没有意义
      
      --遍历方式。注意不少省略exists的判断，否则一旦不存在该元素就直接报错了
      for i in 1 .. v_one.count + 1 loop
        if v_one.exists(i) then
          dbms_output.put_line(i || ':' || v_one(i));
        else
          dbms_output.put_line('第' || i || '位元素不存在');
        end if;
      end loop;
      dbms_output.put_line('---------v_one遍历完毕-----------------');
    end pro_thirteen;
    /*打印：
    v_one限定大小:
    v_one实际大小:2
    v_one首位元素下标:1
    v_one末位元素下标:2
    1:aa
    2:bb
    第3位元素不存在
    ---------v_one遍历完毕-----------------*/

索引下标使用varchar2类型，这样的结构有点像map了。

这种情况好处是显而易见的。

create or replace procedure pro_thirteen2 is
      type type_table is table of varchar2(32) index by varchar2(32);
      -- 索引下标使用varchar2类型，这样的结构有点像map了
      v_one type_table; 
      
      v_temp_key varchar2(32);
    begin
      v_one('key1') := 'value_aa';
      v_one('key2') := 'value_bb';
      dbms_output.put_line('v_one实际大小:' || v_one.count);
      dbms_output.put_line('v_one首位元素下标:' || v_one.first);
      dbms_output.put_line('v_one末位元素下标:' || v_one.last);
      
      --遍历方式。注意不少省略exists的判断，否则一旦不存在该元素就直接报错了
      for i in 1 .. v_one.count + 1 loop
         /*以字符串类型的下标的table，我们就认为和map数据结构是一样的，
         取值方式也必须通过key值来获取*/
         v_temp_key:='key'||i;
        if v_one.exists(v_temp_key) then
          dbms_output.put_line(v_temp_key || ':' || v_one(v_temp_key));
        else
          dbms_output.put_line('key值是' || v_temp_key || '的元素不存在');
        end if;
      end loop;
      dbms_output.put_line('---------v_one遍历完毕-----------------');
    end pro_thirteen2;
    
    /*打印：
    v_one实际大小:2
    v_one首位元素下标:key1
    v_one末位元素下标:key2
    key1:value_aa
    key2:value_bb
    key值是key3的元素不存在
    ---------v_one遍历完毕-----------------
    
    
    */

下面写一个功能，涉及到2个存储过程和一个包。

一个存储过程处理数据并把结果作为一个集合返回，另一个存储过程调用这个

存储过程，并接受处理结果，进行遍历。看看例子;

包：

create or replace package lsy_package_three is
    
      --类型
      type type_record is record(
        r_code lsy_codebook.codecode%type,
        r_name lsy_codebook.codename%type
      );
      type type_table is table of type_record index by BINARY_INTEGER;
    
    end lsy_package_three;

create or replace procedure pro_thirteen3(resultList out lsy_package_three.type_table) is
      /*  把这样的类型声明在包里或者声明成单个对象，以便于公用
      type type_record is record(
          r_code lsy_codebook.codecode%type,
          r_name lsy_codebook.codename%type
        );
        type type_table is table of type_record index by BINARY_INTEGER;*/
      -- table只是个容器类型，它里面可以装各种类型的数据，常用的就是record
      v_result lsy_package_three.type_table;
      v_record lsy_package_three.type_record;
      v_temp_i number := 1;
      cursor mycur is
        select codecode, codename from lsy_codebook;
    begin
      open mycur;
      loop
        exit when mycur%notfound;
        fetch mycur
          into v_record.r_code, v_record.r_name;
        v_result(v_temp_i) := v_record;
        v_temp_i := v_temp_i + 1;
      end loop;
      resultList:=v_result;--返回处理结果
      if mycur%isopen then
        close mycur;
      end if;
    
      dbms_output.put_line('v_result结果集大小:' || v_result.count);
    end pro_thirteen3;
    
      /*打印：
      v_result结果集大小:8
      
      */

create or replace procedure pro_thirteen4 is
      v_result lsy_package_three.type_table; 
      v_record lsy_package_three.type_record;
      v_temp_i number:=1;
    begin
      pro_thirteen3(v_result);--调用pro_thirteen3获取处理结果集,然后遍历
      dbms_output.put_line('获取处理结果集大小:' || v_result.count);
      
      for i in 1 .. v_result.count loop
        if v_result.exists(i) then
          begin
            v_record:=v_result(i);
            dbms_output.put_line(i||':'||'v_record.r_code:'||v_record.r_code||',v_record.r_name:'||v_record.r_name);
          end;
        else
          dbms_output.put_line('第' || i || '位元素不存在');
        end if;
      end loop;
      dbms_output.put_line('---------v_result遍历完毕-----------------');
    end pro_thirteen4;
    
    /*打印：
    v_result结果集大小:8
    获取处理结果集大小:8
    1:v_record.r_code:qw,v_record.r_name:全网
    2:v_record.r_code:gd,v_record.r_name:广东
    3:v_record.r_code:gx,v_record.r_name:广西
    4:v_record.r_code:yn,v_record.r_name:云南
    5:v_record.r_code:gz,v_record.r_name:贵州
    6:v_record.r_code:hn,v_record.r_name:海南
    7:v_record.r_code:hn,v_record.r_name:海南
    8:v_record.r_code:hn,v_record.r_name:海南
    ---------v_result遍历完毕-----------------
    
    
    */

关于plsql的自定义数据类型，集合等数据结构本来想在本篇大概了解一下，发现内容远比我想象的多。

下篇继续说数据类型。作为一个初学者，错误在所难免，本文只是我的学习记录罢了，只对自己负责。