24. Oracle PL/SQL variable

First, Variable introduction
When you write a PL/SQL program, you can define variables and constants, including the following in the PL/SQL program:
1), scalar type (scalar)
2), composite type (composite) --for manipulating individual records
3), reference type (Reference) --for manipulating multiple records
4), LOB (large object)

Scalars (scalar)--Common types
1), when writing PL/SQL blocks, if you want to use variables, you 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: Specifies that the variable value cannot be null
= Specify an initial value for a variable or constant
Default is used to specify the initial value
Expr: a PL/SQL expression that specifies the initial value, which can be a literal value, other variables, functions, and so on.

2), scalar-defined cases
1. Define a variable-length string
V_ename VARCHAR2 (10);
2. Define a decimal, range -9999.99~9999.99
V_sal number (6,2);
3. Define a decimal and give an assignment number with an initial value of 5.4,:= is PL/SQL
V_sal2 number (6,2): = 5.4;
4. Defining data of a date type
V_hiredate date;
5. Define a Boolean variable that cannot be null and the initial value is False
V_valid Boolean NOT null default false;

Scalars (scalar)--using scalars

After you have defined the variables, you can use them. It should be noted here that the PL/SQL block assigns a variable value to a different programming language, with a colon (: =) before the equal sign.
The following is an example of entering an employee number that shows the employee's name, salary, and personal income tax (tax rate of 0.03). Describe the use of variables and see how to write them.

Set serveroutput on; --Open Output option
DECLARE
  --Tax rate of 0.03
  C_tax_rate Number (3, 2): = 0.03;
  --Employee Name
  V_ename   VARCHAR2 (5);
  --Wages
  V_sal number     (7, 2);
  --Personal income tax
  V_tax_sal Number (7, 2);
BEGIN
  -Execution
  SELECT ename, SAL to V_ename, v_sal from EMP WHERE empno=&empno; --7369
  --Calculation of income tax
  V_tax_sal: = V_sal * c_tax_rate;
  --Output
  Dbms_output. Put_Line (' Employee Name: ' | | V_ename | | ' Wages: ' | | V_sal | | ' Tax: ' | | V_tax_sal);
END;
/

Scalars (scalar)--Using the%type type
There is a problem with the PL/SQL block above: If the employee's name exceeds 5 characters, there will be "Ora-06502:pl/sql: Number or value error: String buffer too small" error, in order to reduce the maintenance work of PL/SQL program, you can use% The Type property defines the variable so that it determines the type and length of the variable you define, according to the database column.
Let's see how this is used: identifier name table name. column name%type;
such as the v_ename of the above example, this definition: V_ename emp.ename%type;

Set serveroutput on; --Open Output option
DECLARE
  --Tax rate of 0.03
  C_tax_rate Number (3, 2): = 0.03;
  --Employee Name
  V_ename   emp.ename%type;--Recommended use of%type type
  --Wages
  V_sal number     (7, 2);
  --Personal income tax
  V_tax_sal Number (7, 2);
BEGIN
  -Execution
  SELECT ename, SAL to V_ename, v_sal from EMP WHERE empno=&empno; --7777
  --Calculation of income tax
  V_tax_sal: = V_sal * c_tax_rate;
  --Output
  Dbms_output. Put_Line (' Employee Name: ' | | V_ename | | ' Wages: ' | | V_sal | | ' Tax: ' | | V_tax_sal);
END;
/

Five, compound variable (composite)--Introduction
A variable used to hold multiple values. These are mainly the following:
1), PL/SQL Records
2), PL/SQL table
3), nested table
4), Varray

Six, compound type--pl/sql record
Similar to structs in high-level languages, it is important to note that when you reference a PL/SQL record member, you must add a record variable as a prefix (record variable. Record member) as follows:

Set serveroutput on; --Open Output option
DECLARE
  --Define a PL/SQL record type Emp_record_type,
  
  TYPE Emp_record_type is RECORD (
    NAME   EMP. Ename%type,
    SALARY EMP. Sal%type,
    TITLE  EMP. Job%type);
  --Defines a Sp_record variable, the type of which is Emp_record_type
  Sp_record Emp_record_type;
BEGIN
  SELECT ename, SAL, JOB into Sp_record from EMP WHERE EMPNO = 7788;
  Dbms_output. Put_Line (' Employee Name: ' | | Sp_record.name | | ' Wages: ' | | Sp_record. SALARY);
END;
/

Seven, compound type--pl/sql table
is equivalent to an array in a high-level language, but it is important to note that the subscript of an array in a high-level language cannot be negative, and PL/SQL can be negative, and there is no limit to the subscript of the table element. Examples are as follows:

Method One (recommended):
Set serveroutput on; --Open Output option
DECLARE
--Defines a PL/SQL table type Sp_table_type, which is used for storing emp.ename%type
--index by VARCHAR2 (20) indicates that the subscript is a string
TYPE Sp_table_type is TABLE of EMP. Ename%type INDEX by VARCHAR2 (20);
--Defines a sp_table variable, the type of which is Sp_table_type
Sp_table Sp_table_type;
BEGIN
SELECT ename, Sal into sp_table (' ename '), sp_table (' Sal ') from EMP WHERE EMPNO = 7788;
Dbms_output. Put_Line (' Employee Name: ' | | Sp_table (' ename ') | | ' Salary: ' | | Sp_table (' Sal '));
END;
/

Method Two:
Set serveroutput on; --Open Output option
DECLARE
--Defines a PL/SQL table type Sp_table_type, which is used for storing emp.ename%type
--index by Binary_integer indicates that the subscript is an integer
TYPE Sp_table_type is TABLE of EMP. Ename%type INDEX by Binary_integer; --Pay attention to Binary_integer if you change to an integer will be error, know friend welcome tell me under
--Defines a sp_table variable, the type of which is Sp_table_type
Sp_table Sp_table_type;
BEGIN
SELECT ename,sal to Sp_table ( -1), sp_table ( -2) from EMP WHERE EMPNO = 7788;
Dbms_output. Put_Line (' Employee Name: ' | | Sp_table (-1) | | ' Salary: ' | | Sp_table (-2));
END;
/

Description
Sp_table_type is a PL/SQL table type
EMP.ENAME%TYPE Specifies the type and length of the elements of the table
Sp_table is a PL/SQL table variable
Sp_table (0) indicates an element with subscript 0
Note: If the select Ename into Sp_table ( -1) from emp where empno = 7788; becomes select ename into sp_table ( -1) from EMP, the runtime will have an error, with the following error: ORA-01422: The number of rows actually returned exceeds the number of rows requested
The workaround is to use the reference variable (not spoken here)

VIII. Compound variables--Nested tables (nested table)
Compound variable-variable-length array (varray)
Omitted

Nine, reference variable--Introduction
A reference variable is a variable used to hold a numeric pointer. By using reference variables, you can make your application share the same objects, thereby reducing the space consumed. When you write a PL/SQL program, you can use both the cursor variable (ref CURSOR) and the object type variable (ref Obj_type) for the reference variable type. It is recommended to use cursor variables.

X. Reference variable--REF CURSOR cursor variable
When using cursors, you do not need to specify the corresponding SELECT statement when you define a cursor, but when using a cursor (open) You need to specify a SELECT statement, which is combined with a SELECT statement. Examples are as follows:
1. Use PL/SQL to write a block that allows you to enter a department number and display all employee names and salary for that department.
2. On the basis of 1, if an employee's salary is less than 200 yuan, add 100 yuan.

SET serveroutput on;

  --Defining cursors
  TYPE sp_emp_cursor is REF cursor;
  --Define a cursor variable
  SP Sp_emp_cursor;
  --Defining variables
  V_ename Emp.ename%type;
  V_sal Emp.sal%type;
BEGIN
  OPEN sp for SELECT e.ename, e.sal from emp e WHERE e.deptno=10;
  --Method One loop loop
  /*
  
  FETCH sp into v_ename, v_sal;
  EXIT when Sp%notfound;
  Dbms_output. Put_Line (' Name: ' | | V_ename | | ' Wages: ' | | V_sal);
  END loop;*/
  --Method Two while loop
  /*
  While 1=1 LOOP
    FETCH sp into v_ename, v_sal;
    EXIT when Sp%notfound;
    Dbms_output. Put_Line (' Name: ' | | V_ename | | ' Wages: ' | | V_sal);
  END loop;*/
  --Method three for loop
  For cur in (SELECT e.ename, e.sal from emp e WHERE e.deptno=10) LOOP
    Dbms_output. Put_Line (' Name: ' | | cur.ename | | ' Wages: ' | | Cur.sal);
  END LOOP;
END;
/

24. Oracle PL/SQL variable