Basic Statements

During PL/SQL programming, you may define some variables, assign values to variables, and call other stored procedures. This chapter describes basic PL/SQL statements, including variable definition statements, value assignment statements, call statements, and return statements.

NOTE: You are advised not to call the SQL statements containing passwords in the stored procedures because authorized users may view the stored procedure file in the database and password information is leaked. If a stored procedure contains other sensitive information, permission to access this procedure must be configured, preventing information leakage.

Variable Definition Statements

This section describes the declaration of variables in the PL/SQL and the scope of this variable in codes.

Variable Declaration

For details about the variable declaration syntax, see Figure 1.

Figure 1 declare_variable::=

The above syntax diagram is explained as follows:

• variable_name indicates the name of a variable.
• type indicates the type of a variable.
• value indicates the initial value of the variable. (If the initial value is not given, NULL is taken as the initial value.) value can also be an expression.

Examples

MogDB=# DECLARE
    emp_id  INTEGER := 7788; -- Define a variable and assign a value to it.
BEGIN
    emp_id := 5*7784; -- Assign a value to the variable.
END;
/

In addition to the declaration of basic variable types, %TYPE and %ROWTYPE can be used to declare variables related to table columns or table structures.

%TYPE Attribute

%TYPE declares a variable to be of the same data type as a previously declared variable (for example, a column in a table). For example, if you want to define a my_name variable whose data type is the same as the data type of the firstname column in the employee table, you can define the variable as follows:

my_name employee.firstname%TYPE

In this way, you can declare my_name without the need of knowing the data type of firstname in employee, and the data type of my_name can be automatically updated when the data type of firstname changes.

TYPE employee_record is record (id INTEGER, firstname VARCHAR2(20));
my_employee employee_record;
my_id my_employee.id%TYPE;
my_id_copy my_id%TYPE;

%ROWTYPE Attribute

%ROWTYPE declares data types of a set of data. It stores a row of table data or results fetched from a cursor. For example, if you want to define a set of data with the same column names and column data types as the employee table, you can define the data as follows:

my_employee employee%ROWTYPE

The attribute can also be used on the cursor. The column names and column data types of this set of data are the same as those of the employee table. For the cursor in a package, %ROWTYPE can be omitted. %TYPE can also reference the type of a column in the cursor. You can define the data as follows:

cursor cur is select * from employee;
my_employee cur%ROWTYPE
my_name cur.firstname%TYPE
my_employee2 cur -- For the cursor defined in a package, %ROWTYPE can be omitted.

NOTICE:

• %TYPE cannot reference the type of a composite variable or a record variable, a column type of the record type, a column type of a variable of the cross-package composite type, or a column type of a cursor variable of the cross-package type.
• %ROWTYPE cannot reference the type of a composite variable or a record variable and the type of a cross-package cursor.

Scope of a Variable

The scope of a variable indicates the accessibility and availability of the variable in code block. In other words, a variable takes effect only within its scope.

• To define a function scope, a variable must declare and create a BEGIN-END block in the declaration section. The necessity of such declaration is also determined by block structure, which requires that a variable has different scopes and lifetime during a process.
• A variable can be defined multiple times in different scopes, and inner definition can cover outer one.
• A variable defined in an outer block can also be used in a nested block. However, the outer block cannot access variables in the nested block.

Assignment Statements

Syntax

Figure 2 shows the syntax diagram for assigning a value to a variable.

Figure 2 assignment_value::=

The above syntax diagram is explained as follows:

• variable_name indicates the name of a variable.
• value can be a value or an expression. The type of value must be compatible with the type of variable_name.

Example

MogDB=# DECLARE
    emp_id  INTEGER := 7788; --Assignment
BEGIN
    emp_id := 5; --Assignment
    emp_id := 5*7784;
END;
/

Nested Value Assignment

Figure 3 shows the syntax diagram for assigning a nested value to a variable.

Figure 3 nested_assignment_value::=

The syntax in Figure 3 is described as follows:

• variable_name: variable name
• col_name: column name
• subscript: subscript, which is used for an array variable. The value can be a value or an expression and must be of the int type.
• value: value or expression. The type of value must be compatible with the type of variable_name.

Example

MogDB=# CREATE TYPE o1 as (a int, b int);
MogDB=# DECLARE
    TYPE r1 is VARRAY(10) of o1;
    emp_id  r1;
BEGIN
    emp_id(1).a := 5;-- Assign a value.
    emp_id(1).b := 5*7784;
END;
/

NOTICE:

• In INTO mode, values can be assigned only to the columns at the first layer. Two-dimensional or above arrays are not supported.
• When a nested column value is referenced, if an array subscript exists, only one parenthesis can exist in the first three layers of columns. You are advised to use square brackets to reference the subscript.

INTO/BULK COLLECT INTO

INTO and BULK COLLECT INTO store values returned by statements in a stored procedure to variables. BULK COLLECT INTO allows some or all returned values to be temporarily stored in an array.

Example

MogDB=# DECLARE
    my_id integer;
BEGIN
    select id into my_id from customers limit 1; -- Assign a value.
END;
/

MogDB=# DECLARE
    type id_list is varray(6) of customers.id%type;
    id_arr id_list;
BEGIN
    select id bulk collect into id_arr from customers order by id DESC limit 20; -- Assign values in batches.
END;
/

NOTICE: BULK COLLECT INTO can only assign values to arrays in batches. Use LIMIT properly to prevent performance deterioration caused by excessive operations on data.

Call Statement

Syntax

Figure 4 shows the syntax diagram for calling a clause.

Figure 4 call_clause::=

The above syntax diagram is explained as follows:

• procedure_name specifies the name of a stored procedure.
• parameter specifies the parameters for the stored procedure. You can set no parameter or multiple parameters.

Example

-- Create the stored procedure proc_staffs:
MogDB=# CREATE OR REPLACE PROCEDURE proc_staffs
(
section     NUMBER(6),
salary_sum out NUMBER(8,2),
staffs_count out INTEGER
)
IS
BEGIN
SELECT sum(salary), count(*) INTO salary_sum, staffs_count FROM hr.staffs where section_id = section;
END;
/

-- Invoke a stored procedure proc_return:
MogDB=# CALL proc_staffs(2,8,6);

-- Delete a stored procedure:
MogDB=# DROP PROCEDURE proc_staffs;