 |
| Declaring PL/SQL Variables |
01. What is variable?
In Oracle/PLSQL, a variable allows a programmer to store data temporarily during the execution of code. Variable declarations allocate storage for a value, specify its data type, and specify a name that you can reference.
02. Write down the use of Variables.
Variables can be used for:
a. Temporary storage of data
b. Manipulation of stored values
c. Reusability
03. Write down the naming rules for variable.
Naming rules:
a. Must start with a letter.
b. Can include letters or numbers.
c. Can include special characters (such as $, _, and #).
d. Must limit the length to 30 characters.
e. Must not be reserved words.
04. How to handle the variable in PL/SQL?
Variables are:
a. Declared and initialized in the declarative section
b. Used and assigned new values in the executable section
c. Passed as parameters to PL/SQL subprograms
d. Used to hold the output of a PL/SQL subprogram
05. What are the types of variables?
Variables:
a. PL/SQL variables:
i. Scalar.
ii. Composite.
iii. Reference.
iv. Large objects (LOB).
b. Non-PL/SQL variables: Bind variables
06. Write down the Guidelines for Declaring and Initializing PL/SQL Variables.
Guidelines:
a. Follow naming conventions.
b. Use meaningful names for variables.
c. Initialize variables designated as NOT NULL and CONSTANT.
d. Initialize variables with the assignment operator (:=) or the DEFAULT keyword:
i. Myname VARCHAR2(20):='John';
ii. Myname VARCHAR2(20) DEFAULT 'John';
e. Declare one identifier per line for better readability and code maintenance.
07. What is scalar data type variable?
A scalar data type holds a single value and has no internal components. Scalar data types can be classified into four categories: number, character, date, and Boolean.
08. What is %TYPE attribute?
The %TYPE attributes:
a. Is used to declare a variable according to:
i. A database column definition
ii. Another declared variable
b. Is prefixed with:
i. The database table and column
ii. The name of the declared variable
09. Write down the Advantages of the %TYPE Attribute.
Advantages of the %TYPE Attribute:
a. You can avoid errors caused by data type mismatch or wrong precision.
b. You can avoid hard coding the data type of a variable.
c. You need not change the variable declaration if the column definition changes.
10. What is LOB Data Type Variables?
Large objects (LOBs) are meant to store a large amount of data. A database column can be of the LOB category. With the LOB category of data types (BLOB, CLOB, BFILE and NCLOB)
11. What is a composite data type variable?
A composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite.
12. What is bind variables?
Bind variables are:
a. Created in the environment.
b. Also called host variables.
c. Created with the VARIABLE keyword.
d. Used in SQL statements and PL/SQL blocks.
e. Accessed even after the PL/SQL block is executed.
f. Referenced with a preceding colon.
0 Comments