Variable usage in PLSQL

Variables and constants can be defined when compiling PL/SQL programs. PL/SQL programs include:
(1). scalar type (scalar)
(2) composite type (composite)
(3). Reference)
(4). lob (large object)
1. Scalar-Common Type
When compiling PL/SQL blocks, if you want to use variables, you must define the variables in the definition section. The syntax for defining variables and constants in PL/SQL is as follows:
Identifier [constant] datatype [not null] [: = | default expr]
Identifier: Name
Constant: Specifies a constant. You need to specify its initial value, and its value cannot be changed.
Datatype: Data Type
Not NULL: the specified variable value cannot be null.
: = Specify the initial value for the variable or constant
Default is used to specify the initial value.
Expr: the PL/SQL expression that specifies the initial value. It can be a text value, other variables, and functions.
Scalar definition case
(1) define a variable-length string
V_ename varchar2 (10 );
(2) define a decimal number in the range of-9999.99 ~ 9999.99
V_sal number (6, 2 );
(3) define a decimal number and assign a value of 5.4: = to PL/SQL.
V_sal2 number (6, 2): = 5.4;
(4) define a date type data
V_hiredate date;
(5) define a Boolean variable. The value cannot be blank or the initial value is false.
V_valid Boolean not null default false;
2. Scalar-use a scalar
After defining variables, you can use these variables. Note that PL/SQL blocks assign values to variables different from other programming languages. You must add a colon (: =) before the equal sign)
The following example shows how to enter an employee number and display employee name, salary, and personal income tax (with a tax rate of 0.03. Describes how to write variables.

Declare <br/> c_tax_rate number (3, 2): = 0.03; <br/> -- User Name <br/> v_ename varchar2 (5); <br/> v_sal number (7, 2 ); <br/> v_tax_sal number (7, 2); <br/> begin <br/> -- run <br/> select ename, Sal into v_ename, v_sal from EMP where empno = & No; <br/> -- calculate the Income Tax <br/> v_tax_sal: = v_sal * c_tax_rate; <br/> -- output <br/> dbms_output.put_line ('name: '| v_ename | 'salary:' | v_sal | 'tax: '| v_tax_sal ); <br/> end; <br/>

Scalar -- use the % Type
There is a problem with the preceding PL/SQL block:
If the employee name exceeds 5 characters, an error occurs. To reduce the PL/SQL program maintenance workload, you can use the % Type attribute to define the variable, in this way, it determines the type and length of the variables you define according to the database column.
Let's take a look at how to use this:
Identifier name table name. Column name % type;
For example, the v_ename in the above example is defined as: v_ename EMP. ename % type;
3. Composite variable-Introduction
Variable used to store multiple values. It mainly includes the following types:
PL/SQL records
PL/SQL table
Nested table
Varray
(1). Composite Type -- PL/SQL record
Similar to the struct in advanced languages, note that when referencing PL/SQL record Members, you must add the record variable as the prefix (record variable. Record member) as follows:
Declare <br/> -- defines a PL/SQL record type, such as emp_record_type, which contains three data names, salary, and title. To put it bluntly, a type can store three data items, mainly for better management <br/> type emp_record_type is record (<br/> name EMP. ename % type, <br/> salary EMP. sal % type, <br/> title EMP. job % type); <br/> -- defines a sp_record variable, whose type is emp_record_type <br/> sp_record emp_record_type; <br/> begin <br/> select ename, Sal, job into sp_record from EMP where empno = 7788; <br/> dbms_output.put_line ('employee name: '| sp_record.name ); <br/> end; <br/>

(2). Composite Type-PL/SQL table
It is equivalent to an array in advanced languages. However, in advanced languages, the subscript of arrays cannot be negative, while PL/SQL can be negative, and there is no limit on the subscript of table elements. Example:
Declare <br/> -- defines a PL/SQL table Type sp_table_type, which is used to store EMP. ename % Type <br/> -- index by binary_integer indicates that the subscript is an integer <br/> type sp_table_type is table of EMP. ename % Type <br/> index by binary_integer; <br/> -- defines an sp_table variable. The type of this variable is sp_table_type <br/> sp_table sp_table_type; <br/> begin <br/> select ename into sp_table (-1) from EMP where empno = 7788; <br/> dbms_output.put_line ('employee Name: '| sp_table (-1); <br/> end; <br/> 
Note:
Sp_table_type is a PL/SQL table type.
EMP. ename % Type specifies the table element type and length.
Sp_table is a PL/SQL table variable.
Sp_table (0) indicates an element whose subscript is 0.
Note: If you change select ename into sp_table (-1) from EMP where empno = 7788; To select ename into sp_table (-1) from EMP; an error occurs during running, the error is as follows:
ORA-01422: the number of rows actually returned exceeds the number of requested rows
Solution: Use the reference variable (not mentioned here)
(3). Composite variable-nested table)
(4). Composite variable -- variable-length array (varray)
(5). Reference variable -- Introduction
A reference variable is a variable used to store a value pointer. By using reference variables, applications can share the same object to reduce the occupied space. When compiling a PL/SQL program, you can use two types of reference variable: cursor variable (ref cursor) and object type variable (ref obj_type.
(6). Reference variable -- ref cursor variable
When using a cursor, you do not need to specify the corresponding SELECT statement when defining the cursor, but you need to specify the SELECT statement when using the cursor (open, such a cursor is combined with a select statement. Example:
1. Use PL/SQL to write a block. You can enter the Department number and display the names and salaries of all employees in the department.
2. On the basis of 1, if an employee's salary is less than 200 yuan, 100 yuan will be added.

Declare <br/> -- defines the cursor sp_emp_cursor <br/> type sp_emp_cursor is ref cursor; <br/> -- defines a cursor variable <br/> test_cursor sp_emp_cursor; <br/> -- Define the variable <br/> v_ename EMP. ename % type; <br/> v_sal EMP. sal % type; <br/> begin <br/> -- execute <br/> -- combine test_cursor with a select statement <br/> open test_cursor for select ename, sal from EMP where deptno = & No; <br/> -- loop removal <br/> loop <br/> fetch test_cursor into v_ename, v_sal; <br/> -- determine whether test_cursor is null <br/> exit when test_cursor % notfound; <br/> dbms_output.put_line ('name: '| v_ename |' salary: '| v_sal); <br/> end loop; <br/> end; <br/>