oracle存储过程----变量的介绍及使用(PL/SQL)

喜欢ヅ旅行 2023-10-17 23:26 95阅读 0赞

上一篇 [oracle存储过程—-入门存储过程][oracle_-]

##### oracle存储过程—-变量的介绍及使用 #####

学习一门语言，既然学会了`hello word` 的输出，那就要关注下基础，首先要熟悉`存储过程` 中的变量类型。  
  关于`存储过程变量有哪些` ，这样的问题，我在百度上搜索，却发现很多没用的，最近在同事那里，忽然看到一本`《oracle从入门到精通》`，搜了一下，发现了`存储过程` 的东西，却是`PL/SQL` 中的基础知识 ，好尴尬啊。  
![这里写图片描述][70]  
  通过学习，了解到了变量的一些知识。  
变量的分类如下：

存储过程中的变量：
    一、标量类型 （有%TYPE）
    二、复合类型
         （1）记录类型 (有%ROWTYPE）
         （2）索引表类型（关联数组）
         （3）varry变长数组

##### 一、标量类型 #####

标量类型，一种是常用的有`NUMBER`、`CHAR` 、`VARCHAR2` 、`VARCHAR`、`NCHAR` 、`NVARCHAR2` 、`LONG` 、`DATE` 、`TIMESTAMP` ,基本上`oracle` 数据库认识的类型都可以直接用了。  
  还有一些我觉得不常用的，`PLS_INTEGER` 、`BINARY_INTEGER` 、`SIMPLE_INTEGER` 、`布尔类型（可以用来存储逻辑的值，不能用作定义表中的数据类型）`。  
  **另外还有一个最常用的** `%TYPE` 。  
  因为上一篇已经简单用过第一种，所以这里介绍一下`%TYPE` 。  
  `%TYPE` 这种方式定义的变量类型的方式和其他的有所不同，它利用已经存在的数据类型来定义新数据的数据类型。例如，当定义多个变量或常量时，只要前面使用过的数据类型，后边的变量就可以利用`%TYPE` 引用，最常见的就是把表中的字段类型作为变量或常量的数据类型。使用此种方式的好处有以下几点：

1.  利用`%TYPE` 方式定义的变量类型和被引用的常量数据类型保持一致，如果以前是`VARCHAR` 类型，现在要改成`NUMBER` 类型，那就只需要直接改数据表中的类型即可，对已写好的`存储过程` 没有影响。
2.  还有个好处就是，`存储过程` 基本都会有表操作，这就避免了在数据传入的时候，引发数据溢出或不符的情况。比如数据库里是`VARCHAR(1)` 这样的，但是实际传入的可能是`张三` 这样的字符串，肯定会导致在执行到这行数据的时候引发异常。如果能用`%TYPE`,那在调用`存储过程` 的时候，就能抛出错误。

如下图所示：  
  `sex LY_DS.LY_NB%TYPE` ，表示:  
  **`定义一个变量`sex`，它的类型与表`LY\_DS`中的字段`LY\_NB\` 类型一致。**

create or replace procedure test_select_procedure
    (sex  LY_DS.LY_NB%TYPE,countNum out number)
    AS
    BEGIN
    select count(*) into countNum from ly_ds where LY_NB=sex;
    dbms_output.put_line(countNum);
    END;

##### 二、复合类型 #####

###### 1、记录类型 ######

如果在存储过程中查询结果中，是多列的情况，我们可能需要查询到结果后，对其做`引用名称.成员名称` 这样取值。上一篇中说到过，可以使用`into`，即如下写法：

create or replace procedure test_select2_procedure
    (sex  varchar)
    AS
    countNum number(10); --别忘了写上具体的长度,并且以分号结束
    maxId number(10); --别忘了写上具体的长度,并且以分号结束
    BEGIN
    select count(*),max(id) into countNum,maxId from ly_ds where LY_NB=sex;
    dbms_output.put_line(countNum);
    dbms_output.put_line(maxId);
    END;

上边的是一种写法，就是`into` 后边跟上定义好的多个`标量类型` 。但是如果字段很多呢？  
  显而易见，再用这个`into` 就不合适了，`记录类型` 就是解决这个的。

###### （1）定义`type` 的声明写法 ######

定义`type` 的写法为 `type type_name is record(col_name col_type);` 这样，括号里可以写多个，中间用 `,` 分割，但最后一个不能有 `,` ，具体如下：

set serveroutput on;
    DECLARE
    TYPE new_type IS RECORD   --以TYPE type_name IS RECORD开始
    (
       v_ly_nl LY_DS.LY_NL%TYPE,
       v_ly_mc varchar(100) --最后不要加,   
    );  --最后以 ;结束
    v_obj new_type; --将新对象定义为刚才声明的类型
    BEGIN
     --into赋值给v_obj，会按照定义的type顺序赋值
    select ly_nl,ly_mc into v_obj from ly_ds where id='2'; 
    dbms_output.put_line('第一个变量：'||v_obj.v_ly_nl);
    dbms_output.put_line('第二个变量：'||v_obj.v_ly_mc);
    END;

输出结果如下：

匿名块已完成
    第一个变量：22
    第二个变量：王五

###### （2）通过`%ROWTYPE` 的声明写法 ######

`%ROWTYPE` 该类型是提取行记录时常用的存储数据的方式。这种声明方式，可以直接引用表中的行作为变量类型，它同`%TYPE` 在优点上类似，避免因表中字段的数据类型改变，而导致`PL/SQL` 块出错的问题。  
  下边是`%ROWTYPE` 的用法例子，挺简单的：

set serveroutput on;
    DECLARE
    v_obj ly_ds%rowtype; --ROWTYPE不区分大小写
    BEGIN
     --into赋值给v_obj，会按照定义的type顺序赋值
    select * into v_obj from ly_ds where id='2'; 
    dbms_output.put_line('第一个变量：'||v_obj.ly_nl);
    dbms_output.put_line('第二个变量：'||v_obj.ly_mc);
    END;

注意一个地方，**就是上边的`into` 前边，只能为 `*` ，如果要写为具体的列，那就要写全**。

###### 2、索引表类型（关联数组） ######

该类型与数组相似，利用键值查找对应的数据，但是这里的键值同我们真正的数组下标不同，这种索引表的下标，还可以为字符串，真正的数组下标都是数字。索引表中的数据可以是上边介绍过的标量类型，也可以是记录类型。当在赋值的过程中，对已存在的索引表下标重复赋值，则会替换以前的数据，这个很好理解。

以下是我学习的时候，记录的这部分内容，都写在了下边：

set serveroutput on;
    DECLARE
      /**
       *声明一个存储ly_ds整行数据的索引表，下标为数字，
       *即 binary_integer
       */
      type index_row_type is table of ly_ds%rowtype
      index by binary_integer;
    
      /**
       *声明一个存储字符串数据的索引表，下标也为数字，
       *即 pls_integer
       */
      type index_val_type is table of varchar2(10)
      index by pls_integer;
    
      /**
       *声明一个存储字符串数据的索引表，下标为字符串，
       *即 varchar(100)、varchar(10),必须给固定大小
       */
      type index_str_val_type is table of varchar2(100)
      index by varchar(10);
       /**
        *定义一个下标为数字，存ly_ds一行的变量
        */
      v_row index_row_type; 
      /**
       *定义一个下标为数字，存字符串的变量
       */
      v_val index_val_type; 
      /**
       *定义一个下标为字符串，存字符串的变量
       */
      v_str_val index_str_val_type; 
    BEGIN
     /**
      *为下标为数字的 字符串索引表下标1赋值
      */
      v_val(1) :='正数'; 
      /**
      *为下标为数字的 字符串索引表下标-1赋值
      */
      v_val(-1) :='负数'; 
      dbms_output.put_line('v_val中下标1的值：'||v_val(1));
      dbms_output.put_line('v_val中下标-1的值：'||v_val(-1));
    
      /**
      *将改行数据赋值给行变量的下标1上
      */
      select * into v_row(1) from ly_ds where id='2'; 
      dbms_output.put_line('v_row(1)中ly_mc的值：'||v_row(1).ly_mc);
      dbms_output.put_line('v_row(1)中ly_nl的值：'||v_row(1).ly_nl);
    
       /**
        *为下标为字符串的 字符串索引表的下标one赋值
        */
      v_str_val('one') :='java天下第一';  
       /**
        *为下标为字符串的 字符串索引表的下标test赋值
        */
      v_str_val('test') :='java天下无敌'; 
       /**
        *为下标为字符串的 字符串索引表的下标test1赋值
        */
      v_str_val('test1') :='java太可怕了'; 
      dbms_output.put_line('v_str_val中下标one的值：'||v_str_val('one'));
      dbms_output.put_line('v_str_val中下标test的值：'||v_str_val('test'));
      dbms_output.put_line('v_str_val中第一个值的下标：'||v_str_val.first);
      dbms_output.put_line('v_str_val中第一个下标对应的值：'||v_str_val(v_str_val.first));
      dbms_output.put_line('v_str_val中最后一个下标：'||v_str_val.last);
    END;

运行后会有下边的结果：

匿名块已完成
    v_val中下标1的值：正数
    v_val中下标-1的值：负数
    v_row(1)中ly_mc的值：王五
    v_row(1)中ly_nl的值：22
    v_str_val中下标one的值：java天下第一
    v_str_val中下标test的值：java天下无敌
    v_str_val中第一个值的下标：one
    v_str_val中第一个下标对应的值：java天下第一
    v_str_val中最后一个下标：test1

上边有个特别注意的问题，就是，定义下标和存储的值的时候，一定要给出大小，比如字符串，不能写成`varchar` ,必须要写成`varchar(100)` 这样有固定大小的类型。  
  上边我用了三种，一种是`pls_integer` 、`binary_integer` ，一种是`ly_ds%rowtype` ，这两种都有固定的大小，第一种是数字类型，它们有固定的大小，可以百度一下。**如果定义了标量类型那种，必须要要给出大小**，这个需要注意下。

###### 3、varry变长数组 ######

`varry` 数组，是另一种存储有序元素的集合。集合下标从1开始，比较适合较少的数据使用。具体如下：  
  它有一个注意的地方，就是数组在定义一个变量时候，一定要`初始化` ，并且，在`使用前` 一定要先`确定容量` 。对于，在初始化时，已赋值的，可以不用定义存储的大小了。这点与`java` 中的数组是一样的。

set serveroutput on;
    DECLARE
      /**
       *声明一个最多容纳100个数的varry数组，注意，它的下标是从1开始的。
       *即 binary_integer
       */
      type array_type is varray(100) of varchar(100);
      /**
       *分别定义一个直接赋值的和两个未赋值的数组。
       *注意：一定要初始化，但可以不赋值。对于没有赋值的这种数组，在用之前
       *也一定要先确定容量。
       */
      v_val_array array_type := array_type('one','two');
      v_val_array2 array_type := array_type();
      v_val_array3 array_type := array_type();
    BEGIN
       /**
        *获取第一个varry数组中的值
        *varry的下标从1开始
        */
        dbms_output.put_line('v_val_array中下标1的值：'||v_val_array(1));
        dbms_output.put_line('v_val_array中下标2的值：'||v_val_array(2));
    
    
       /**
        *获取第二个varry数组中的值
        *因为第二个varry没有初始化长度，所以通过extend方法，
        *为该数组加一个空位
        */
        v_val_array2.extend;
        v_val_array2(1) :='aaa'; 
        v_val_array2.extend;
        v_val_array2(2) :='bbb'; 
        v_val_array2.extend;
        v_val_array2(3) :='ccc'; 
        dbms_output.put_line('v_val_array2中下标1的值：'||v_val_array2(1));
        dbms_output.put_line('v_val_array2中下标2的值：'||v_val_array2(2));
        dbms_output.put_line('v_val_array2中下标3的值：'||v_val_array2(3));
    
         /**
        *获取第三个varry数组中的值
        *因为第三个varry没有初始化长度，所以通过extend方法
        *初始化空位
        */
    
      /**
        *获取第二个varry数组中的值
        *因为第二个varry没有初始化长度，所以通过extend方法，
        *为该数组初始化长度
        */
        v_val_array3.extend(v_val_array2.count());
        v_val_array3(1) :='ddd'; 
        v_val_array3(2) :='eee'; 
        v_val_array3(3) :='fff'; 
        dbms_output.put_line('v_val_array3中下标1的值：'||v_val_array3(1));
        dbms_output.put_line('v_val_array3中下标2的值：'||v_val_array3(2));
        dbms_output.put_line('v_val_array3中下标3的值：'||v_val_array3(3));
    END;

执行后的结果为：

匿名块已完成
    v_val_array中下标1的值：one
    v_val_array中下标2的值：two
    v_val_array2中下标1的值：aaa
    v_val_array2中下标2的值：bbb
    v_val_array2中下标3的值：ccc
    v_val_array3中下标1的值：ddd
    v_val_array3中下标2的值：eee
    v_val_array3中下标3的值：fff

上边分别是三种情况，其中有`extend` ，可能新手会感到迷惑。  
  因为`v_val_array2`和`v_val_array3` 开始的时候，只是定义了变量，所以，在使用前，需要给出大小，`extent` 的用法，即是`对数组的长度加1个空位` 的意思。  
  如果想直接为`v_val_array3`给出固定大小，可以直接`v_val_array3.extend(3)` ，这样就会给`v_val_array3` 一个默认的存储大小为`3` ，也可以通过已有数组的大小，即是上边的`v_val_array3.extend(v_val_array2.count())`。

虽然上边都是`PL/SQL` 那边的知识，但是它们在`存储过程` 中，都是通用的。变量这地方，浪费了挺长的时间，但是基础有利用后边的学习，我觉得浪费了这两天应该会对自己有用处的。  
  下一篇 [oracle存储过程—-存储过程执行简单的增删改查sql][oracle_-_sql]

[oracle_-]: https://blog.csdn.net/wohaqiyi/article/details/81501769
[70]: https://img-blog.csdn.net/20180815085358957?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvaGFxaXlp/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[oracle_-_sql]: https://blog.csdn.net/wohaqiyi/article/details/81529997