Oracle cursor usage (1)

Query

The SELECT statement is used to query data from the database. When the SELECT statement is used in PL/SQL, it must be used with the into clause. The return value of the query is assigned to the variable in the into clause, variable declaration is in delcare. The select into syntax is as follows:

Select [distict | all] {* | column [, column,...]}

Into (variable [, variable,...] | record)

From {table | (sub-query)} [alias]

Where ............

In PL/SQL, the SELECT statement returns only one row of data. If a row of data is exceeded, an explicit cursor is used (we will discuss the cursor later). The into clause must contain variables with the same number of columns as the select clause. The into clause can also be a record variable.

% Type attribute

In PL/SQL, you can declare variables and constants as built-in or user-defined data types to reference a column name and inherit its data types and sizes. This dynamic value assignment method is very useful. For example, the data type and size of the columns referenced by variables have changed. If % type is used, you do not have to modify it.CodeOtherwise, you must modify the code.

Example:

V_empno Scott. EMP. empno % type;

V_salary EMP. Salary % type;

Not only can the column name use % type, but also variables, cursors, records, or declared constants can use % type. This is useful for defining variables of the same data type.

Delcare

V_a number (5): = 10;

V_ B v_a % Type: = 15;

V_c v_a % type;

Begin

Dbms_output.put_line

('V _ A = '| v_a | 'v _ B =' | V_ B | 'v _ c = '| V_c );

End

SQL>/

V_a = 10 V_ B = 15 V_c =

PL/SQL procedure successfully completed.

SQL>

Other DML statements

The DML statements for other data operations are insert, update, delete, and lock table. the syntax of these statements in PL/SQL is the same as that in SQL. We have discussed the usage of DML statements before. In DML statements, you can use any variable declared in the declare section. If it is a nested block, pay attention to the scope of the variable.

Example:

Create or replace procedure fire_employee (pempno in number)

As

V_ename EMP. ename % type;

Begin

Select ename into v_ename

From EMP

Where empno = p_empno;

Insert into former_emp (empno, ename)

Values (p_empno, v_ename );

Delete from EMP

Where empno = p_empno;

Update former_emp

Set date_deleted = sysdate

Where empno = p_empno;

Exception

When no_data_found then

Dbms_output.put_line ('employee number not found! ');

End