Full cursor details

Cursor is also called cursor. Oracle uses two types of cursors: explicit cursors and implicit cursors. Regardless of how many records the statement returns, PL/SQL implicitly declares a cursor for each UPDATE, DELETE, and INSERT SQL command used.

When you enter text in a text box or a program, there is always a cursor that shows where you are going to enter the text. Most cursors now are small vertical lines that flash regularly. Under DOS, some cursors are short horizontal lines that flash regularly. The current Windows cursor is an image and can be dynamic. can also be static and look different in different situations. Basic introduction Chinese name: Cursor Foreign name: cursor Alias: Cursor Category: Displayed cursor and implicit cursor Category: Computer usage: Enter text computer terminology, cursors in the database, basic methods of using cursors, declare cursors, open cursors, Retrieve data from the cursor, close the cursor, Computer Terminology When you enter text in a text box or some kind of program, there is always a cursor to show the position where you are going to enter the text. Most cursors now are small vertical lines that flash regularly. Under DOS, some cursors are short horizontal lines that flash regularly. The current Windows cursor is an image and can be dynamic. can also be static and look different in different situations. Cursors in the database (To manage the processing of a SQL statement, a cursor must be implicitly defined for it.) Users declare and use explicit cursors to process multiple records returned by a SELECT statement. An explicit definition cursor is a structure that enables the user to specify an area of ??memory for a specific statement for later use. When a PL/SQL cursor query returns multiple rows of data, these groups of records are called active sets. Oracle stores this active set in a named cursor in a display definition that you create. Oracle cursors are a mechanism for easily processing multiple rows of data. Without cursors, Oracle developers must individually and explicitly retrieve and manage each record selected by a cursor query. Another feature of a cursor is that it contains a pointer that keeps track of the currently accessed record, allowing your program to process multiple records at once. The basic method of using a cursor is to declare a cursor. The syntax for declaring a cursor is as follows: DECLARE cursor_name Is SELECT statement where cursor_name is the name you specify for the cursor; SELECT statement is a query that returns records to the cursor active set. Declaring a cursor accomplishes two purposes: naming the cursor and associating a query with the cursor. It is worth noting that the cursor must be declared in the declaration part of the PL/SQL block; the name assigned to the cursor is an undeclared identifier, not a PL/SQL variable, and the cursor name cannot be assigned a value or used in in the expression. PL/SQL blocks use this name to refer to cursor queries. Example: DECLARE CURSOR c1 Is SELECT VIEW_NAME FROM ALL_VIEWS WHERE ROWNUMlt; = 10; In addition, the cursor parameters can be declared in the cursor definition statement, for example: CURSOR c1 (view _nbr number) Is SELECT VIEW_NAME FROM ALL_VIEWS WHERE ROWNUMlt; = view _nbr; Cursor parameters are only visible to the corresponding cursor, and the parameters of the cursor cannot be referenced outside the cursor scope. If you attempt to do this, Oracle will return an error stating that the variable is not defined.

Opening a Cursor The syntax for opening a cursor is: OPEN cursor_name; where cursor_name is the name of the cursor you defined previously. Opening a cursor activates the query and identifies the active set, but no records are actually retrieved until the cursor retrieval command is executed. The OPEN command also initializes the cursor pointer to point to the first record in the active set. After the cursor is opened, all data retrieved into the active set is static until it is closed. In other words, the cursor ignores all SQL DML commands (INSERT, UPDATE, DELETE and SELECT) executed on the data after the cursor is opened. . So only open it when needed, to refresh the active set just close and reopen the cursor. Retrieving data from a cursor The FETCH command retrieves records from the active set one record at a time. The FETCH command is usually used in conjunction with some kind of iterative processing, in which the cursor advances to the next record in the active set each time the FETCH command is executed. Syntax for the FETCH command: FETCH cursor_name INTO record_list; where cursor_name is the name of the previously defined cursor; record_list is a variable list that accepts columns from the active set. The FETCH command places the results of the active set into these variables. After executing the FETCH command, the results in the active set are fetched back into PL/SQL variables for use in the PL/SQL block. Each time a record is retrieved, the cursor pointer moves to the next record in the active set. Example: FETCH C1 INTO VNAME; WHILE C1FOUND LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1ROWCOUNT)||' '||VNAME); END LOOP; Closing the Cursor The CLOSE statement closes a previously open cursor, leaving the active set undefined. Oracle implicitly closes the cursor when the user's program or session ends. After the cursor is closed, no operations can be performed on it, otherwise an exception will be thrown. The syntax of the CLOSE statement is: CLOSE cursor_name; where cursor_name is the name of the previously opened cursor. The complete program code is as follows: DECLARE CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS WHERE ROWNUMlt;=10 ORDER BY VIEW_NAME; VNAME VARCHAR2(40); BEGIN OPEN C1; FETCH C1 INTO VNAME; WHILE C1FOUND LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1ROWCOUNT)| |''||VNAME); END LOOP; END; ... CLOSE C1;