Home  >  Article  >  Database  >  What are the methods of assigning values ​​to variables in Oracle stored procedures?

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

青灯夜游
青灯夜游Original
2022-01-25 16:57:0820999browse

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.

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

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn