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