Oracle cursor usage Daquan 3

Record variable

Define a record variable using the type command and % rowtype. For more information about % rowstype, see related materials. Record variables are used to extract data rows from the cursor. When multiple columns are selected for the cursor, it is much easier to use records than to declare a variable for each column. When % rowtype is used in the table and the value retrieved from the cursor is put into the record, if you want to select all columns in the table, therefore, using * in a select clause is much safer than listing all columns.

Example:

Set serveriutput on

Declare

R_emp EMP % rowtype;

Cursor c_emp is select * from EMP;

Begin

Open c_emp;

Loop

Fetch c_emp into r_emp;

Exit when c_emp % notfound;

Dbms_out.put.put_line ('salary of employee '| r_emp.ename |' is '| r_emp.salary );

End loop;

Close c_emp;

End;

% Rowtype can also be defined by the cursor name. In this way, the cursor must be declared first:

Set serveriutput on

Declare

Cursor c_emp is select ename, salary from EMP;

R_emp c_emp % rowtype;

Begin

Open c_emp;

Loop

Fetch c_emp into r_emp;

Exit when c_emp % notfound;

Dbms_out.put.put_line ('salary of employee '| r_emp.ename |' is '| r_emp.salary );

End loop;

Close c_emp;

End;

　　Parameter-based cursor

Similar to stored procedures and functions, you can pass parameters to the cursor and use them in the query. This is useful for processing the case where a cursor is opened under certain conditions. Its syntax is as follows:

Cursor cursor_name [(parameter [, parameter],...)] is select_statement;

Syntax for defining parameters:

Parameter_name [in] data_type [{: = | default} value]

Unlike stored procedures, a cursor can only accept passed values, but cannot return values. The parameter only defines the data type and has no size.

In addition, you can set a default value for the parameter. When no parameter value is passed to the cursor, the default value is used. The parameter defined in the cursor is just a placeholder. It is not necessarily reliable to reference it elsewhere.

Assign a value to the parameter when you open the cursor. The syntax is as follows:

Open cursor_name [value [, value]...];

The parameter value can be text or variable.

Example:

Decalre

Cursor c_dept is select * from Dept order by deptno;

Cursor c_emp (p_dept varachar2) is

Select ename, salary

From EMP

Where deptno = p_dept

Order by ename

R_dept dept % rowtype;

V_ename EMP. ename % type;

V_salary EMP. Salary % type;

V_tot_salary EMP. Salary % type;

Begin

Open c_dept;

Loop

Fetch c_dept into r_dept;

Exit when c_dept % notfound;

Dbms_output.put_line ('department: '| r_dept.deptno |'-'| r_dept.dname );

V_tot_salary: = 0;

Open c_emp (r_dept.deptno );

Loop

Fetch c_emp into v_ename, v_salary;

Exit when c_emp % notfound;

Dbms_output.put_line ('name: '| v_ename | 'salary:' | v_salary );

V_tot_salary: = v_tot_salary + v_salary;

End loop;

Close c_emp;

Dbms_output.put_line ('toltal salary for dept: '| v_tot_salary );

End loop;

Close c_dept;

End;

　　Cursor For Loop

Most of the time we design a program, we follow the steps below:

1. Open the cursor

2. Start Loop

3. values from the cursor

4. Check that the row is returned.

5. Processing

6. close the loop

7. Close the cursor

You can simply call this type of code a cursor for a loop. However, there is another type of loop that is different from this type. This is the for loop. The cursor used for the for loop is declared as normal, it does not need to explicitly open, close, retrieve data, test data, define data storage variables, and so on. The syntax of the cursor for loop is as follows:

For record_name in

(Corsor_name [(parameter [, parameter]...)]

| (Query_difinition)

Loop

Statements

End loop;

The example above is rewritten using a For Loop:

Decalre

Cursor c_dept is select deptno, dname from Dept order by deptno;

Cursor c_emp (p_dept varachar2) is

Select ename, salary

From EMP

Where deptno = p_dept

Order by ename

V_tot_salary EMP. Salary % type;

Begin

For r_dept in c_dept Loop

Dbms_output.put_line ('department: '| r_dept.deptno |'-'| r_dept.dname );

V_tot_salary: = 0;

For r_emp in c_emp (r_dept.deptno) loop

Dbms_output.put_line ('name: '| v_ename | 'salary:' | v_salary );

V_tot_salary: = v_tot_salary + v_salary;

End loop;

Dbms_output.put_line ('toltal salary for dept: '| v_tot_salary );

End loop;

End;