“％Type”在Oracle sql中意味着什么？

I'm getting my first experience with Oracle and TOAD (I know SSMS). I came across this "%Type" next to an input parameter in an update procedure and I have no idea what it is or what it means. I found links on Google related to "%Rowtype". Is the same thing or something entirely different?

我第一次体验Oracle和TOAD（我知道SSMS）。我在更新过程中的输入参数旁边遇到了这个“％Type”，我不知道它是什么或它意味着什么。我在Google上找到了与“％Rowtype”相关的链接。是同一件事还是完全不同的事情？

If this is vague, I apologize. As always, thanks for the help.

如果这很模糊，我道歉。一如既往，感谢您的帮助。

3 个解决方案

#1

Oracle (and PostgreSQL) have:

Oracle（和PostgreSQL）有：

• %TYPE
• ％类型
• %ROWTYPE
• ％ROWTYPE

%TYPE

%TYPE is used to declare variables with relation to the data type of a column in an existing table:

％TYPE用于声明与现有表中列的数据类型相关的变量：

DECLARE v_id ORDERS.ORDER_ID%TYPE

The benefit here is that if the data type changes, the variable data type stays in sync.

这样做的好处是，如果数据类型发生变化，则变量数据类型保持同步。

Reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i6080

参考：http：//download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i6080

%ROWTYPE

This is used in cursors to declare a single variable to contain a single record from the resultset of a cursor or table without needing to specify individual variables (and their data types). Ex:

这在游标中用于声明单个变量以包含来自游标或表的结果集的单个记录，而无需指定单个变量（及其数据类型）。例如：

DECLARE
  CURSOR c1 IS
     SELECT last_name, salary, hire_date, job_id 
       FROM employees 
      WHERE employee_id = 120;

  -- declare record variable that represents a row fetched from the employees table
  employee_rec c1%ROWTYPE; 

BEGIN
 -- open the explicit cursor and use it to fetch data into employee_rec
 OPEN c1;
 FETCH c1 INTO employee_rec;
 DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
END;
/

#2

%Type is used for inheriting the same data type used by the previous declared variable.

％Type用于继承先前声明的变量使用的相同数据类型。

The syntax is :

语法是：

 Declare
            L_num Number(5,2) not null default 3.21;
            L_num_Test L_num%Type:=1.123;

So there is no need to declare the data type for the second variable i.e L_num_Test.

因此，不需要声明第二个变量的数据类型，即L_num_Test。

Comment if anyone needs further clarification regarding this topic.

评论是否有人需要进一步澄清此主题。

#3

some example

一些例子

set serveroutput on
DECLARE
  var1  table_a.id%TYPE;
  var2  table_a.value%TYPE;
  var3_row table_a%ROWTYPE;
BEGIN
  SELECT id,value
    INTO var1, var2
    FROM table_a
  WHERE id= 8 AND ROWNUM<=1; 

  SELECT id+100,value INTO var3_row from table_A where rownum=1;

  INSERT INTO table_a VALUES var3_row;

  dbms_output.put_line('this is a test');
  dbms_output.put_line(var1);
  dbms_output.put_line(var2);
  NULL;  -- do something useful here
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;  -- do something appropriate here
  WHEN TOO_MANY_ROWS THEN
    NULL;  -- do something appropriate here
END;
/