What are the methods of assigning values ​​to variables in Oracle stored procedures?

Methods for assigning values ​​to variables: 1. Use ":=" to assign values ​​directly, and the syntax is "variable name:=value;"; 2. Use the "select table field into variable from table" statement; 3. Use " execute immediate sql statement string into variable" statement.

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

Oracle stored procedure variable declaration and assignment methods

1. 3 ways to declare variables

Distinguish according to the declaration method of the data type

Method 1: Directly declare the data type

Format: variable name data type (size)

V_START_DATE VARCHAR2(19);
v_num number;

　Note:

　When set to string type, the size needs to be specified, otherwise an error will be reported;

　The declaration of the variable must be before the "begin" keyword conduct.

--错误用法
BEGIN
v_sql varchar2(100) := 'SELECT ORGSEQ FROM BASE_ORG_INFO where orgcode=to_char(410621101233)';

Method 2: Use %TYPE statement

Format: variable name table name.Field name %TYPE

Meaning: the variable's The data type is consistent with the data type of the specified field in the specified table

V_ORGSEQ BASE_ORG_INFO.ORGSEQ%TYPE;

Method 3: Use %ROWTYPE statement

Format: variable name table name%ROWTYPE

Meaning: the variable's The data type is consistent with the data type of the specified row record (all fields) of the specified table

--VIRTUAL_CARD表整行数据
V_ROW_VIRTUAL_CARD VIRTUAL_CARD%ROWTYPE;　

Summary:

In the stored procedure, when declaring variables, there is no need to use keywords" DECLARE";　

Variables are not case-sensitive;

Variables must be declared before use.

2. 3 ways of variable assignment

Method 1: Direct assignment, use ":="

Usage conditions: Applicable to the first two ways of declaring variables.

V_ORGID :=  '110';　　

Description:

The variable cannot be directly followed by a sql statement. The sql will not be executed. You can refer to method three.

--错误用法
V_ORGSEQ := SELECT ORGSEQ INTO V_ORGSEQ FROM BASE_ORG_INFO;
--正确用法
V_ORGSEQ := 'SELECT ORGSEQ INTO V_ORGSEQ FROM BASE_ORG_INFO';

　The declaration and assignment of variables can be done together.

--正确用法
v_sql varchar2(100) := 'SELECT ORGSEQ FROM BASE_ORG_INFO where orgcode=to_char(410621101233)';
BEGIN
 /* 具体业务 */
END;

Method 2: select table field into variable from table

Variation 1: Query a specified field of the specified table

Use conditions : The first two methods applicable to declaring variables can be used

--根据医疗机构ID查询对应的父机构的机构序列
SELECT ORGSEQ INTO V_ORGSEQ FROM BASE_ORG_INFO
 WHERE ORGID =
       (SELECT PARENTORGID FROM BASE_ORG_INFO WHERE ORGID = V_ORGID);

Variation 2: Query all fields of the specified table

Usage conditions: Only applicable to declaration The third method of variables

--将id=5120的VIRTUAL_CARD表数据赋值给变量V_ROW_VIRTUAL_CARD
SELECT * INTO V_ROW_VIRTUAL_CARD FROM VIRTUAL_CARD T where t.id = 5120;

Description:

The query result can only return one record;

Query table Fields must be all fields of the table.

Error example:

--错误举例一：查询的是所有表记录
SELECT * INTO V_ROW_VIRTUAL_CARD FROM VIRTUAL_CARD T;
--错误举例二：查询的是该表的多个字段
SELECT t.id,t.name INTO V_ROW_VIRTUAL_CARD FROM VIRTUAL_CARD T;　　

Method 3: execute immediate sql statement string into variable

declare/* 存储过程，不需要声明 */
  v_sql varchar2(100);
  V_ORGSEQ varchar2(100);
begin
  v_sql := 'SELECT ORGSEQ FROM BASE_ORG_INFO where orgcode=to_char(410621101233)';
  --V_ORGSEQ赋值
  execute immediate v_sql INTO V_ORGSEQ;
  --打印结果
  DBMS_OUTPUT.put_line(V_ORGSEQ);
END;

3 .Call of variables

Generally speaking, variables only have these three usage scenarios: assignment, logical judgment, arithmetic operations;

Note: cannot be used as Query column

--错误调用
select V_ORGSEQ from dual;

How to call variables declared using %ROWTYPE?

Using the "variable name. table field" method, you can get the data represented by the specified column (any column) of the specified row (return row) in the table.

--V_ROW_VIRTUAL_CARD赋值
SELECT * INTO V_ROW_VIRTUAL_CARD FROM VIRTUAL_CARD T where t.id = 5120;
--调用
DBMS_OUTPUT.put_line(V_ROW_VIRTUAL_CARD.ID);　　

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of What are the methods of assigning values ​​to variables in Oracle stored procedures?. For more information, please follow other related articles on the PHP Chinese website!