How to query and assign values ​​to variables in Oracle

Oracle method of querying and assigning values ​​to variables: 1. Use the "variable name data type (size)" statement to define the variable; 2. Use the "select table field name into variable name from table name WHERE clause" statement Assign the query results to variables.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

How to query and assign values ​​to variables in oracle

First we need to define (declare) a variable

Format:

变量名  数据类型(大小)

Example:

V_TEST varchar2(20);
V_NUM  number;

Then use sselect table field name into variable name from table name WHERE clause statement query And assign the value to the variable

Method 1:

①Query all fields of the specified table

②The first two methods applicable to declaring variables can be used

--根据代发工资个人id查询对应的父机构（代发单位）序列
SELECT REVC_SIGN INTO V_LOGO FROM DFGZ_PKG
 WHERE REVCID=(SELECT PARENTID FROM DFGZ_PKG WHERE REVEID = V_REVCID);

Method 2:

①Query all fields of the specified table

②Applicable to the third way of declaring variables

--将id=2203的DFGZ_PKGMX表数据赋值给变量V_COLUMN_SID;
SELECT * INTO  V_COLUMN_SID FROM DFGZ_PKGMX T WHERE t.id=2203

Note:

①The query result can only return one record

②The table fields queried must be all fields of the table

--错误示例一：查询的是所有记录
select * into V_COLUMN_SID  from DFGZ_PKGMX T;
--错误示例二：查询的是该表的多个字段
select  t.id,t.name into V_COLUMN_SID  from dfgz_pkgmx t;

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to query and assign values ​​to variables in Oracle. For more information, please follow other related articles on the PHP Chinese website!