ORACLE中%TYPE和%ROWTYPE的使用

清疚 2021-06-26 16:06 569阅读 0赞

## 1、`%TYPE` ##

为了使一个新定义的变量与另一个已经定义了的变量(通常是表的某一列)的数据类型保持一致,Oracle提供了`%Type`的定义方式,当被参照的那个变量的数据类型发生改变时,那么这个新定义的变量的数据类型也会随之发生改变,这种定义方式类似与其他编程语言中的”`var`“,其类型不由自己决定。而是由与之关联的对象决定。这样就不用修改`Sql`语句了,当不能确切的知道那个变量的类型是,就采用这种方法来定义变量的数据类型。

DECLARE   
      V_ORG_NAME SF_ORG.ORG_NAME%TYPE; --与ORG_NAME类型相同 
      V_PARENT_ID SF_ORG.PARENT_ID%TYPE;--与PARENT_ID类型相同 
    BEGIN SELECT ORG_NAME,PARENT_ID INTO V_ORG_NAME,V_PARENT_ID FROM SF_ORG SO WHERE SO.ORG_ID=&ORG_ID;  
      DBMS_OUTPUT.PUT_LINE('部门名称：' || V_ORG_NAME);  
      DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_PARENT_ID));  
    END;

## 2、`%RowType` ##

如果一个表有较多的列，使用`%ROWTYPE`来定义一个表示表中一行记录的变量，比分别使用`%TYPE`来定义表示表中各个列的变量要简洁得多，并且不容易遗漏、出错。这样会增加程序的可维护性。

为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，`Oracle`提供`%ROWTYPE`定义方式。当表的某些列的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改`PL/SQL`程序了。当不能确切地知道被参照的那个表的结构及其数据类型时，就只能采用这种方法定义变量的数据类型。

一行记录可以保存从一个表或游标中查询到的整个数据行的各列数据。一行记录的各个列与表中一行的各个列有相同的名称和数据类型。

DECLARE   
      V_SF_ORG_REC SF_ORG%ROWTYPE; --与SF_ORG表中的各个列相同 
    BEGIN SELECT * INTO V_SF_ORG_REC FROM SF_ORG SO WHERE SO.ORG_ID=&ORG_ID;  
      DBMS_OUTPUT.PUT_LINE('部门ID：' || TO_CHAR(V_SF_ORG_REC.ORG_ID));  
      DBMS_OUTPUT.PUT_LINE('部门名称：' || V_SF_ORG_REC.ORG_NAME);  
      DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_SF_ORG_REC.PARENT_ID));  
    END;

注:上面的代码在`Sql plus`中运行

--------------------

# `%TYPE` 和 `%ROWTYPE` 的使用 #

`%TYPE` 用在变量的声明里，用于取得表中的字段类型；

`%ROWTYPE` 用于声明基于某个表的行类型；

示例：

`%ROWTYPE` 使用

DECLARE
          CURSOR pdct_cur
          IS SELECT * FROM PRODUCTINFO;
    
          cur_prodcrd productinfo%ROWTYPE;
    
          BEGIN OPEN pdct_cur;
              FETCH pdct_cur INTO cur_prodrcd;
              DBMS_OUTPUT_PUTLINE(cur_prodrcd.productid || '-' || cur_prodrcd.productname);
            CLOSE pdct_cur;
          END;

`%TYPE` 使用

DECLARE
          cur_productid varchar(10);
          cur_prodctname productinfo.productname%TYPE;
    
          CURSOR pdct_cur
          IS SELECT productid,productname FROM PRODUCTINFO;
    
          BEGIN OPEN pdct_cur;
              FETCH pdct_cur INTO cur_productid ,cur_productname;
              DBMS_OUTPUT_PUTLINE(productid || '-' ||productname);
            CLOSE pdct_cur;
          END;

--------------------

# 简述`%TYPE`和`%ROWTYPE`的用法 #

## 基础概念： ##

**`%TYPE`概念：**

为了使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）的数据类型相一致，`Oracle`提供了`%TYPE`定义方式。当被参照的那个变量的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改`PL/SQL`程序了。当不能确切地知道被参照的那个变量的数据类型时，就只能采用这种方法定义变量的数据类型。

使用`%TYPE`特性的优点在于：

*  所引用的数据库列的数据类型可以不必知道。
 *  所引用的数据库列的数据类型可以实时改变。

**`%ROWTYPE`概念：**

为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致，Oracle提供`%ROWTYPE`定义方式。当表的某些列的数据类型改变了之后，这个新定义的变量的数据类型会自动跟随其改变，容易保持一致，也不用修改`PL/SQL`程序了。当不能确切地知道被参照的那个表的结构及其数据类型时，就只能采用这种方法定义变量的数据类型。

一行记录可以保存从一个表或游标中查询到的整个数据行的各列数据。一行记录的各个列与表中一行的各个列有相同的名称和数据类型。

使用`%ROWTYPE`特性的优点在于：

*  所引用的数据库中列的个数和数据类型可以不必知道。
 *  所引用的数据库中列的个数和数据类型可以实时改变。

## 简单实例 ##

--学生结构表
    CREATE TABLE STUDENT ( ID INT NOT NULL, --学生主键ID NAME VARCHAR2(50),--学生名称 SEX INT --学生性别 0='男' 1='女' );
    
    --学生信息
    INSERT INTO STUDENT(ID, NAME, sex) VALUES(1, 'mike',0);
    INSERT INTO STUDENT(ID, NAME, sex) VALUES(2, 'vancy',1);
    
    --%TYPE实例1,简单写法
    DECLARE
      V_NAME STUDENT.NAME%TYPE; --与NAME类型相同
      V_SEX STUDENT.SEX%TYPE; --与SEX类型相同
    BEGIN SELECT t.name,t.sex INTO V_NAME,V_SEX FROM STUDENT t WHERE t.id = &ID;
      DBMS_OUTPUT.put_line('学生姓名:' || V_NAME);
      DBMS_OUTPUT.put_line('学生性别:' || V_SEX);
    END;
    
    --%TYPE实例2,结合type自定义类型使用
    DECLARE
      --定义一个记录类型
      TYPE STUINFO_RECORD IS RECORD(
         V_NAME STUDENT.NAME%TYPE, --与NAME类型相同
         V_SEX STUDENT.SEX%TYPE --与SEX类型相同
      );
      --声明自定义记录类型变量
      V_STUINFO_RECORD STUINFO_RECORD;
    BEGIN SELECT t.name,t.sex INTO V_STUINFO_RECORD FROM STUDENT t WHERE t.id = &ID;
      DBMS_OUTPUT.put_line('学生姓名:' || V_STUINFO_RECORD.V_NAME);
      DBMS_OUTPUT.put_line('学生性别:' || V_STUINFO_RECORD.V_SEX);
    END;
    
    --%ROWTYPE实例
    DECLARE 
      V_STUDENT STUDENT%ROWTYPE; --与STUDENT表中的各个列相同
    BEGIN SELECT * INTO V_STUDENT FROM STUDENT t WHERE t.id=&ID;
      DBMS_OUTPUT.PUT_LINE('学生ID：' || V_STUDENT.ID);
      DBMS_OUTPUT.put_line('学生姓名:' || V_STUDENT.NAME);
      DBMS_OUTPUT.put_line('学生性别:' || V_STUDENT.SEX);
    END;

--------------------

# Oracle中 `%ROWTYPE`： #

`%ROWTYPE`：表示该类型为行数据类型，存储的时候为一行数据，一行有很多列，相当于表中的一行数据，也可以的游标中的一行数据。

用到`%ROWTYPE`作用是：当查询整行的时候(`SELECT * FROM EMP;`)，那么效率比每一个字段对应快。

例如：

v_info emp%ROWTYPE; //该类型为emp表中的一行数据类型

v_cinfo c_emp%ROWTYPE //该类型为游标c_emp中的一行数据类型

下面是一个例子：

Oracle代码

declare  
    r_emp emp%ROWTYPE;   --该类型为emp表中一行的类型  
    cursor c_emp is SELECT * FROM emp ;  
    BEGIN  
      OPEN c_emp;  
      LOOP  
        FETCH c_emp into r_emp;     --游标中查询出来的一行into进定义的变量r_emp中  
        EXIT WHEN c_emp%NOTFOUND;  
        dbms_output.put_line('员工姓名：' || r_emp.ename); 
      END LOOP;  
      CLOSE c_emp;  
    END;