Oracle sql 中的“%Type”是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3790658/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What does "%Type" mean in Oracle sql?
提问by Matt M
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.
如果这是模糊的，我道歉。一如既往，感谢您的帮助。
回答by OMG Ponies
Oracle (and PostgreSQL) have:
Oracle（和PostgreSQL）有：
- %TYPE
- %ROWTYPE
- ％类型
- %行类型
%TYPE
％类型
%TYPEis 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;
/
回答by Roshan jha
%Typeis 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.
所以不需要为第二个变量 ie 声明数据类型L_num_Test。
Comment if anyone needs further clarification regarding this topic.
如果有人需要对此主题进行进一步说明，请发表评论。
回答by zloctb
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;
/
