Table of ContentsPreviousNext

Ispirer             Ispirer

Ispirer SQLWays Database Migration Software

Variable Declaration

This subsection describes declaration of local variables in various databases and their conversion by SQLWays.

TABLE 17. Declaration of Local Variables
var1 datatype [{:= | DEFAULT } exp1] 
The DECLARE statement is used for declare variables in the declarative part of any PL/SQL block or subprogram. Declarations allocate storage space for a value, specify its data type, and name the storage location so that maybe reference it.
The keyword DEFAULT maybe used instead of the assignment operator to initialize variables.
var1 table_name%ROWTYPE 
The %ROWTYPE attribute provides a record type that represents a row in a database table. Fields in a record and corresponding columns in a row have the same names and data types.
The %ROWTYPE attribute may be used in variable declarations as a data type specifier. Variables declared using %ROWTYPE are treated like those declared using a data type name.
table_name - this identifies a database table (or view) that must be accessible when the declaration is elaborated.
The %ROWTYPE attribute lets declare records structured like a row of data in a database table. To reference a field in the record, you use dot notation. For example,you might reference the deptno field as follows:
IF emp_rec.deptno = 20 THEN ... 
The value of an expression may be assign to a specific field, as follows:
emp_rec.sal := average * 1.15; 
In the example below, %ROWTYPE is used to store a row selected from the emp table:
emp_rec emp%ROWTYPE;
In the next example, you select a row from the emp table into a %ROWTYPE record:
emp_rec emp%ROWTYPE; 
SELECT * INTO emp_rec FROM emp WHERE empno = 
IF (emp_rec.deptno = 20) AND (emp_rec.sal > 2000) THEN 
var1 variable_name | column_name %TYPE 
The %TYPE attribute is used for defining data types of variables. Variables, declared with %TYPE attribute get type identical with variable or column, which located before %TYPE.
variable_name - data type of this variable is used for the declared variable var1.
column_name - data type of this column is used for the declared variable var1. column_name is a compound clause that must contain table or view name, where the column is defined.
Examples: In the following example var2 is declared through var1 with data type NUMBER:
var1 number; 
var2 var1%TYPE; 
In the sample below var2 is declared through column col1 of the table tab1:
var2 tab1.col1%TYPE; 
DECLARE var1 [, varN]... datatype 
[DEFAULT exp1]	 
The DECLARE statement is used to declare local variables.
DECLARE may only be used inside a BEGIN ... END compound statement and must be at its start, before any other statements.
The scope of a variable is within the BEGIN ... END block.
Microsoft SQL Server
DECLARE  @var1 [AS] datatype      [ , 
@varN [AS] datatype]  
The DECLARE statement is used to declare variables anywhere within the procedure body, before their usage.
After declaration all variables are initialized as NULL.
DEFINE var1 [, varN] datatype 
The DEFINE statement is used to declare local variables.
DEFINE may only be used inside a stored procedure and must be at the beginning of statements block, before any other statement.
Datatype can be any datatype except for the SERIAL, SERIAL8, BYTE or TEXT.
DEFINE var1 [, varN] LIKE 
The DEFINE statement with the LIKE clause is used to declare local variables as table columns declared.
Column is any column existing in the table or view.
If column has SERIAL or SERIAL8 datatype, it is declared as INT or INT8 variable.

Table of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.