3 ways to declare and assign an Oracle stored procedure variable

3 ways to declare and assign an Oracle stored procedure variable creationtime--2018 August 31 16:00 author:marydon1.3 Ways to declare variables

Differentiate by the way data types are declared

Way one: declaring data types directly

Format: Variable name data type (size)

V_start_date VARCHAR2 (+); V_num number;

Description

　　When set to string type, you need to specify the size, otherwise error;

　　The declaration of a variable must precede the "BEGIN" keyword.

--Error usage beginv_sql varchar2: = ' SELECT orgseq from Base_org_info where Orgcode=to_char (410621101233) ';

Way two: Use%type statement

Format: variable name table name. Field name%type

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

V_orgseq Base_org_info.orgseq%type;

Method Three: Use%rowtype declaration

Format: Variable Name table name%rowtype

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

--virtual_card table whole row data V_row_virtual_card Virtual_card%rowtype;

Summary:

　　In a stored procedure, when declaring a variable, you do not need to use the keyword "DECLARE";　

Variable is case-insensitive;

Variables must be declared before they are used.

2.3 Ways to assign values to variables

Method One: Direct assignment, using ": ="

Conditions of use: applies to the first 2 ways of declaring a variable.

V_orgid: =  ' 110 ';

Description

　　After the variable is not directly SQL statement, SQL does not execute, you can refer to mode three.

--Error usage V_orgseq: = Select Orgseq to v_orgseq from base_org_info;--correct usage v_orgseq: = ' SELECT orgseq into V_orgseq from Base_ Org_info ';

　　The Declaration and assignment of a variable can be done in a piece.

--Correct usage v_sql varchar2 (+): = ' SELECT orgseq from Base_org_info where Orgcode=to_char (410621101233) '; BEGIN/* Specific business */end;

Mode two: Select table field into variable from table

Transform one: Query a specified field of a specified table

Conditions of Use: the first 2 ways to declare a variable can be used

--based on the medical institution ID query the corresponding parent institution sequence SELECT orgseq into V_orgseq from base_org_info WHERE ORGID =       (select Parentorgid from base_org _info WHERE ORGID = v_orgid);

Variant two: Querying all fields of a specified table

Conditions of Use: applies only to the 3rd way of declaring a variable

--Assigns the Virtual_card table data of id=5120 to the variable V_row_virtual_cardselect * into V_row_virtual_card from Virtual_card T where t.id = 5120;

Description

　　Query results can only return one record;

　　The table field for the query must be all of the fields of the tables.

Examples of errors:

--error Example one: query for all table records SELECT * Into V_row_virtual_card from Virtual_card t;--Error Example two: Query is multiple fields of the table select T.id,t.name into V_row_ Virtual_card from Virtual_card T;

Mode three: Execute immediate SQL statement string into variable

declare/* stored procedure, do not need to declare *  /V_sql varchar2 ();  V_orgseq varchar2 (+); begin  V_sql: = ' SELECT orgseq from Base_org_info where Orgcode=to_char (410621101233) ';  --v_orgseq Assignment  execute immediate v_sql into V_orgseq;  --Printing results  dbms_output.put_line (V_ORGSEQ); END;

3. Invocation of variables

In general, variables have only these 3 usage scenarios: assignment, logical judgment, arithmetic operations;

Description: cannot be used as a query column

--Error calling select V_orgseq from dual;

How are variables declared using%rowtype called?

Use the Variable name. table field to take the data represented by the specified column (any column) of the specified row (return row) in the table.

--v_row_virtual_card Assignment SELECT * to V_row_virtual_card from Virtual_card T where t.id = 5120;--call Dbms_output.put_line (V_ Row_virtual_card.id);

Related recommendations:

• Oracle stored procedure calls dynamic SQL

3 ways to declare and assign an Oracle stored procedure variable