Database Surface Questions

Cursors (cursor)

During database development, when we encounter a single record from one result set. So how do we solve this problem? cursors provide us with a very good solution.

1. Advantages of cursors and cursors

Essentially, a cursor is actually a mechanism that extracts one record at a time from a result set that includes multiple data records.
Cursors are always associated with an SQL selection statement because the cursor consists of a result set (which can be 0, one, or multiple records retrieved by a related selection statement) and a cursor position in the result set that points to a particular record.

When you decide to process a result set, you must declare a cursor that points to the result set.

1. What is a cursor?

Cursors, also known as cursors. Generally speaking, it is based on records.

For example, we look up the number in the phone book, find the file in the student file, and ultimately it comes down to one of the numbers, a file, that's a record.

In real life, when we look for an item in a table, we may sweep through one line by hand to help us find the record we need. For the database, this is the cursor model.

So, you can imagine that tables are tables in a database, and our hands are like cursors.

A cursor is the ' positioning system ' of the data.

2. How do I use Cursors from Oracle?

1). Cursors in Oracle are divided into display cursors and implicit cursors
2). The display cursor is a cursor defined with the cursor...is command, which can handle multiple records returned by the query statement (select);

An implicit cursor is automatically defined by PL/SQL when you perform an insert (insert), delete, modify (update), and a query (SELECT) statement that returns a single record.

3). Explicit cursor operation: Open the cursor, manipulate the cursor, close the cursor, PL/SQL implicitly opens the SQL cursor, processes the SQL statement inside it, and then closes it

I. Using cursors

1. When a query statement select and data manipulation statement DML are executed in a PL/SQL block, Oracle assigns it a context area (the context area), and the cursor refers to the context region pointer

For data manipulation statements and single-line SELECT INTO statements, Oracle assigns them an implied cursor.

Use a display cursor to work with multiple rows of data, or you can use SELECT. BULK COLLECT into statement handles multiple rows of data.

1. Display Cursors

Defining cursors

Cursor cursor_name is select_statement;

2. Open the cursor: Executes the corresponding SELECT statement and temporarily stores the result of the SELECT statement in the result set.

Open cursor_name;

3. Extracting data

When the cursor is opened, the result of the SELECT statement is temporarily stored in the cursor result set and only one row of data can be fetched using the FETCH statement

By using fetch: BULK COLLECT into statement can fetch multiple rows of data at a time

Fetch cursor_name into Variable1,varibale2,...;

fetch cursor_name bulk collect into Collect1,collect2,... [Limit rows];

4. Close the cursor

Close cursor_name;

9.2 Displaying cursor properties

Used to return execution information for a display cursor, including%isopen,%found,%notfound,%rowcount

1.%isopen: Determine if the cursor is open if Cl%isopen then ... else open C1; End If;

2.%found: Checks whether data was extracted from the result set

Loop

Fetch C1 into VAR1,VAR2;

If C2%found then ... else exit;

End Loop;

3.%notfound

Loop

Fetch C1 into VAR1,VAR2;

Exit when C2%notfound;

...

End Loop;

4.%rowcount: Returns the actual number of rows that have been extracted to the current behavior

Loop

Fetch C1 into My_ename,my_deptno;

If C1%rowcount>10 Then

...

End If;

...

End Loop;

9.3 Display Cursor Use example

1. Use fetch in the display cursor: INTO statement: Only one row of data can be processed, unless the loop statement

Declare

Cursor Emp_cursor is a select ename,sal from emp where deptno=10;

V_ename Emp.ename%type;

V_sal Emp.sal%type;

Begin

Open emp_cursor;

Loop

Fetch emp_cursor into v_ename,v_sal;

Exit when Emp_cursor%notfound;

Dbms_output.put_line (v_ename| | ': ' | | V_sal);

End Loop;

Close emp_cursor;

End

2. In the display cursor, use FETCH: Balk COLLECT into statement extracts all data

Declare

Cursor Emp_cursor is a select ename from emp where deptno=10;

Type Ename_table_type is Table of VARCHAR2 (10);

Ename_table Ename_table_type;

Begin

Open emp_cursor;

Fetch Emp_cursor bulk collect into ename_table;

For I in 1..ename_table.count loop

Dbms_output.put_line (Ename_table (i));

End Loop;

Close emp_cursor;

End

3. Use fetch in the display cursor: BULK COLLECT into. The limit statement extracts part of the data

Declare

Type Name_array_type is Varray (5) of VARCHAR2 (10);

Name_array Name_array_type;

Cursor Emp_cursor is a select ename from EMP;

Rows int:=5;

V_count int:=0;

Begin

Open emp_cursor;

Loop

fetch emp_cursor bulk collect into Name_array limit rows;

Dbms_output.pur (' Employee name ');

For I in 1.. (Emp_currsor%rowcount-v_count) loop

Dbms_output.put (Name_array (i) | | ‘);

End Loop;

Dbms_output.new_line;

V_count:=emp_cursor%rowcount;

Exit when Emp_cursor%notfound;

End Loop;

Close emp_cursor;

End

4. Using Cursor properties

Declare

Cursor Emp_cursor is a select ename from emp where deptno=10;

Type Ename_table_type is Table of VARCHAR2 (10);

Ename_table Ename_table_type;

Begin

If not Emp_cursor%isopen then

Open emp_cursor;

End If;

Fetch Emp_cursor bulk collect into ename_table;

Dbms_output.put_line (' Total number of fetched rows: ' | | Emp_cursor%rowcount);

Close emp_cursor;

End

5. Defining record variables based on cursors

Declare

Cursor Emp_cursor is a select ename,sal from EMP;

Emp_record Emp_cursor%rowtype;

Begin

Open emp_cursor;

Loop

Fetch emp_cursor into Emp_record;

Exit when Emp_cursor%notfound;

Dbms_output.put_line (' Employee Name: ' | | emp_record.ename| | ', Employee wages: ' | | Emp_record.sal);

End Loop;

End

9.4 Parameter Cursors

When defining a parameter cursor, the cursor parameter can specify only the data type, not the length.

Cursor cursor_name (parameter_name datatype) is select_statment;

Declare

Cursor Emp_cursor (no number) is a select ename from emp where deptno=no;

V_ename Emp.ename%type;

Begin

Open Emp_cursor (10);

Loop

Fetch emp_cursor into v_ename;

Exit when Emp_cursor%notfound;

Dbms_output.put_line (V_ename);

End Loop;

Close emp_cursor;

End

9.5 Updating or deleting data using cursors

To update or delete data through a cursor, you must have a FOR UPDATE clause when you define the cursor

Cursor cursor_name (parameter_name datetype) is select_statement for update [of Column_reference] [nowait];

The FOR UPDATE clause is used to share locks in the cursor result set data home row, preventing other users from performing DML operations on the corresponding rows

The of clause determines which tables are to be locked, without the by clause, and locks on all referenced tables

The NOWAIT clause is used to specify that no lock waits

The WHERE CURRENT OF clause must be referenced in the DELETE statement after the update

UPDATE table_name set column=. where current of cursor_name;

Delete table_name where CURRENT OF cursor_name;

1. Updating data with Cursors

Declare

Cursor Emp_cursor is a select ename,sal from EMP for update;

V_ename Emp.ename%type;

V_sal Emp.sal%tyep;

Begin

Open emp_cursor;

Loop

Fetch emp_cursor into v_ename,v_oldsal;

Exit when Emp_cursor%notfound;

If v_oldsal<2000 Then

Update EMP Set sal=sal+100 where CURRENT of emp_cursor;

End If;

End Loop;

Close emp_cursor;

End

2. Using cursors to delete data

Declare

Cursor Emp_cursor is a select Ename,sal,deptno from EMP for update;

V_ename Emp.ename%type;

V_oldsal Emp.sal%type;

V_deptno Emp.deptno%type;

Begin

Open emp_cursor;

Loop

Fetch emp_cursor into V_ename,v_oldsal,v_deptno;

Exit when Emp_cursor%notfound;

If V_deptno=30 Then

Delete from the EMP where current of emp_cursor;

End If;

End Loop;

Close emp_cursor;

End

3. Use the of clause to add a row shared lock on a specific table

Declare

Cursor Emp_cursor is a select Ename,sal,dname,emp.deptno from emp,dept where Emp.deptno=dept.deptno

for update of Emp.deptno;

Emp_record Emp_cursor%type;

Begin

Open emp_cursor;

Loop

Fetch emp_cursor into Emp_record;

Exit when Emp_cursor%notfound;

If Emp_record.deptno=30 Then

Update EMP Set sal=sal+100 where CURRENT of emp_cursor;

End If;

Dbms_output.put_line (' Employee Name: ' | | emp_record.ename| | ', wages: ' | | emp_record.sal| | ', department name: ' | | Emp_record.dname);

End Loop;

Close emp_cursor;

End

4. Using the NOWAIT clause

You can avoid waiting for a lock by specifying the NOWAIT statement in the FOR UPDATE clause. If you have been locked by the action line, you are prompted with an error message

Declare

Cursor Emp_cursor is a select ename,sal from EMP for update nowait;

V_ename Emp.ename%type;

V_oldsal Emp.sal%type;

Begin

Open emp_cursor;

Loop

Fetch emp_cursor into v_ename,v_sal;

Exit when Emp_cursor%notfound;

If v_oldsal<2000 Then

Update EMP Set sal=sal+100 where CURRENT of emp_cursor;

End If;

End Loop;

Close emp_cursor;

End

9.6 Cursor FOR loop

When using a For loop, Oracle implicitly opens the cursor, extracts the cursor data, and closes the cursor

For Record_name in Cursor_name Loop

Statement1;

Statement2;

...

End Loop;

Extracts data once per loop, automatically exits the loop and implicitly closes the cursor after all data has been extracted

1. Using the cursor for loop

Declare

Cursor Emp_cursor is a select ename,sal from EMP;

Begin

For Emp_record in Emp_cursor loop

Dbms_output.put_line (' The ' | | emp_curosr%rowcount| | ' Employees: ' | | Emp_record.ename);

End Loop;

End

2. Using subqueries directly in the cursor for loop

Begin

For Emp_record in (select Ename,sal from emp) loop

Dbms_output.put_line (Emp_record.ename);

End Loop;

End

9.7 Using cursor variables

A cursor variable in PL/SQL holds a pointer to a memory address.

1. Steps for using cursor variables

Four stages including defining cursor variables, opening cursors, extracting cursor data, closing cursors, etc.

1.1 Defining REF CURSOR types and cursor variables

Type ref_type_name is REF CURSOR [return return_type];

Cursor_varibale Ref_type_name;

When a return clause is specified, its data type must be a record type and the cursor variable cannot be defined within the package

1.2 Opening cursors

Open cursor_variable for select_statement;

1.3 Extracting cursor data

Fetch cursor_varibale into Variable1,variable2,...;

Fetch Cursor_varibale Bulk collect into Collect1,collect2,... [Limit rows]

1.4 Closing Cursor variables

Close Cursor_varibale;

2. Examples of cursor variable use

2.1 Do not specify a return clause when defining the FEF cursor type

Any SELECT statement can be specified when the cursor is opened

Declare

Type emp_cursor_type is REF CURSOR;

Emp_cursor Emp_cursor_type;

Emp_record Emp%rowtype;

Begin

Open Emp_cursor for SELECT * from EMP where deptno=10;

Loop

Fetch emp_cursor into Emp_record;

Exit when Emp_cursor%notfound;

Dbms_output.put_line (' The ' | | emp_curosr%rowcount| | ' Employees: ' | | Emp_record.ename);

End Loop;

Close emp_cursor;

End

2.2 Specifying a return clause when defining a REF CURSOR type

When you open a cursor, the return result of the SELECT statement must match the record type specified by the return clause.

Declare

Type Emp_record_type is record (name Varchar2 (ten), salary number (6,2));

Type Emp_cursor_type is REF CURSOR return emp_record_type;

Emp_cursor Emp_cursor_type;

Emp_record Emp_record_type;

Begin

Open Emp_cursor for select ename,sal from EMP where deptno=20;

Loop

Fetch emp_cursor into Emp_record;

Exit when Emp_cursor%notfound;

Dbms_output.put_line (' The ' | | emp_curosr%rowcount| | ' Employees: ' | | Emp_record.ename);

End Loop;

Close emp_cursor;

End

9.7 Using the cursor expression

Cursor expressions are used to return nested cursors

A result set can contain not only normal data, but also data that contains nested cursors

Cursor (subquery)

Declare

Type refcursor is REF CURSOR;

Cursor Dept_cursor (no number) is a select A.dname,cursor (select Ename,sal from emp where Deptno=a.deptno)

From dept a where A.deptno=no;

Empcur Refcursor;

V_dname Dept.dname%type;

V_ename Emp.ename%type;

V_sal Emp.sal%type;

Begin

Open Dept_cursor (&no);

Loop

Fetch dept_cursor into v_danme,empcur;

Exit when Dept_cursor%notfound;

Dbms_output.put_line (' Department Name: ' | | V_dname);

Loop

Fetch empcur into v_ename,v_sal;

Exit when Empcur%notfound;

Dbms_output.put_line (' Employee Name: ' | | v_ename| | ', wages: ' | | V_sal);

End Loop;

End Loop;

Close dept_cursor;

End

Database Surface Questions