The PL / SQL cursor provides the program to select multi-line data from the database, and then processed separately for each row of data, which provides Oracle to indicate and control each phase of the SQL processing. I will think you have a certain understanding of PL / SQL. Through this article, you will learn:
Create cursor processing definition and use cursor properties
First, what is the cursor Oracle uses two cursors: explicit cursor and implicit cursor. Regardless of the statement returns how many records, PL / SQL is implicitly declared a cursor for each Update, DELETE, and INSERT used. (To manage the processing of SQL statements, you must implicit a cursor to it.) The user declares and uses the display cursor to process the SELECT statement returned multiple records. A definition of the cursor is a structure that allows the user to specify a memory area for a particular statement for later use. Second, the role of the cursor When the PL / SQL cursor query returns multi-line data, these record groups are called active sets. Oracle stores this active set in the named cursor you created. The Oracle cursor is a mechanism for easy processing of multi-line data, no cursor, Oracle developers must separately retrieve and manage each record selected by the cursor query. Another function of the cursor, which contains a pointer to track the currently accessible record, which allows your program to handle multiple records at a time. Third, the basic method steps to display the cursor are as follows:
Declare the cursor Open the cursor from the cursor Remove the data to close the cursor
1. The grammatical syntax of the cursor declaration cursor is as follows:
Declare cursor_nameisselect statement
Among them, Cursor_name is the name you specified to the cursor; select Statement is a query that returns a record to the cursor activity set. The declaration cursor completed the following two purposes: named the cursor; associate a query with the cursor. It is worth noting that the cursor must be declared at the declaration of the PL / SQL block; the name specified for the cursor is an undeclared identifier, not a PL / SQL variable, cannot assign the cursor name, and cannot use it Expression. The PL / SQL block uses this name to reference the cursor query.
Example: DECLARECURSOR c1IsSELECT VIEW_NAME FROM ALL_VIEWSWHERE ROWNUM <= 10; also can declare the parameters of the cursor in the cursor definition statement, for example: CURSOR c1 (view _nbr number) IsSELECT VIEW_NAME FROM ALL_VIEWSWHERE ROWNUM <= view _nbr;
The cursor parameters are only visible to the corresponding cursor, and the parameters of the cursor cannot be referenced outside the cursor range. If you try to do this, Oracle will return an error and point out that the variable is not defined. 2. The grammar that opens the cursor to open the cursor as follows:
Open cursor_name;
Where cursor_name is the name of the cursor you have previously defined. Top the cursor will activate the query and identify the active set, but do not really retrieve the record before performing the light scaled back command. The Open command also initializes the cursor pointer to point to the first record of the active set. After the cursor is opened until it is closed, all the data retrieved to the active set is static. In other words, the cursor ignores all SQL DML commands executed after the cursor is open (INSERT, UPDATE, DELETE, and SELECT) . Therefore, only when it is needed, you want to refresh the active set, just turn off and reopen the cursor. 3. Remove the data fetch command from the cursor to retrieve the record in the activity set in a record. The FETCH commands and certain iteration processes are usually used. In the iterative processing, the fetch command enters the next record of the active set before the cursor is executed. Fetch command syntax: fetch cursor_name inTo record_list;
Among them, cursor_name is the name of the cursor defined by the previously defined; RECORD_LIST is a list of variables that accept columns in the active set. The fetch command places the result of the active set to these variables. After executing the fetch command, the results in the active set are retrieved into the PL / SQL variable to use in the PL / SQL block. Each time a record, the pointer to the cursor moves to the next record of the active set.
Example: Fetch C1 INTO VNAME; While C1% Found loopdbms_output.put_line (to_CHAR (C1% Rowcount) || '' || vname); end loop; where the attribute '% FOUND' makes the Fetch arrive at the end of the activity set Will not cause an exception. Other attributes and meaning are shown below: Attribute content% Found Boolean properties, when the record is successfully returned, the value is True% notfound Boolean properties, and its value is always the value of the% FOUND attribute.% Isopen Boolean Attribute, when the cursor is turned back while turning back True% ROWCOUNT digital properties, return to the number of records read from the cursor
Attribute content% FOUND Boolean properties, when the record is successfully returned, the value is True% notfound Boolean properties, and its value is always the value of the value of the% FOUND attribute, the% isopen Boolean property, when the cursor is turned back True% ROWCOUNT digital properties, returns the number of records that have been read from the cursor
4. Turn off the cursor Close statement to turn off the previously opened cursor, so that the active set is uncertain. Oracle implicitly turns off the cursor when the user's program or session ends. After the cursor is turned off, you cannot perform any operations, otherwise an exception will be triggered.
The syntax of the Close statement is: Close Cursor_Name; where Cursor_Name is the name of the previously opened cursor. The complete code is as follows: DECLARECURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWSWHERE ROWNUM <= 10ORDER BY VIEW_NAME; VNAME VARCHAR2 (40); BEGINOPEN C1; FETCH C1 INTO VNAME; WHILE C1% FOUND LOOPDBMS_OUTPUT.PUT_LINE (TO_CHAR (C1% ROWCOUNT) || '' || vname); end loop; end; ... Close C1;
