3 ways to declare and assign an Oracle stored procedure variable

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.