/************************************************I With a cursor
When I was four, I was going to find a job. Squeezing in a crowded talent market, I am very enthusiastic about a company that recruited VB programmers. A uncle does not ask, do you have a project? "No, but my VB grades are good, I hope you can give me a chance, I want to try it ..." Do you have done a database? "Well, I have done." Know what is a cursor? "..." We need experienced programmers ... Just this, the world will only believe in power. At that time, it would be too little, and it was normal to get a chance. Therefore, you have to learn there is no end, let yourself be strong. Weak countries have no diplomacy. /************************************************use Explicit cursor
Define Cursor Declare Cursor Cursor_Name Is Select_Statement;
Open the cursor openopen cursor_name
Extract Fetchfetch Cursor_name Into Variable1, Variable2, ...
Close the cursor closeclose cursor_name;
Explicit Cursor Properties% ISOpen judges whether to open True% Found Whether to extract data from the result set to TRUE% NOTFOUND Nothing from the results set to the data without extracting True% ROWCOUNT Return to the actual line of the current behavior has been extracted number
Parameter cursor
When you use different parameter values to open the cursor multiple times multiple times, you can generate different result sets of cursor cursor_name (parameter_name datattype) is select_statement;
Using a cursor update / delete data CURSOR CURSOR_NAME (Parameter_name DataType) Is Select_Statement for Update [of column_reference] [no color];
For update is used to add sharing locks on the result set data to prevent other users from performing DML operations again to determine that those tables are not allowed to lock upait, if other sessions are already locked on the action line, The current session display error prompt
Update table_name set column = .. where current of cursor_name; delete table_name where current of cursor_name
Cursor cycle
Oracle will implicitly open the cursor, extract the cursor and close the cursor for record_name in cursor_name loop statement1; statement2; ... End loop;
If you do not need to use any bike properties when cycles, you can directly use the sub-query for record_name in (SELECT_STATEMENT) loop statement1; statement2; ... End loop;
Use a cursor variable
Define REF CURSOR Type and Cursor Variable Type Ref_Type_Name Is Ref Cursor [Return Return_TYPE]; CURSOR_VARIABLE REF_TYPE_NAME
Open the cursor Open cursor_variable for select_statement;
Extract Cursor Data Fetch Cursor_Variable Into Variable1, Variable2, ...
Close Cursor CloseClose Cursor_variable
