Hi Oracle [2]

Source: Internet
Author: User

/*************************************** *********
Cursor and I

When I was a senior, I was about to start looking for a job. In the crowded talent market, I approached a company that recruited VB programmers with enthusiasm. An uncle asked me, have you done a project? "No, but my VB score is good. I hope you can give me a chance. I want to try it ..." Have you ever performed databases? "Well, I did it ." Do you know what a cursor is? "……" We need experienced programmers ......
In this case, the world will only believe in powerful people. At that time there will be too few, and it will be normal if there is no chance. Therefore, you have to learn and make yourself powerful. Weak countries have no diplomacy.

/*************************************** *********
Use an explicit cursor

Define the cursor declare
Cursor cursor_name is select_statement;

Open cursor
Open cursor_name

Extract cursor fetch
Fetch cursor_name into variable1, variable2 ,...;

Close cursor close
Close cursor_name;

Explicit cursor attribute
% Isopen determine whether open is true
% Found: whether to extract data from the result set to true
% Notfound: whether the data is not extracted to true from the result set
% Rowcount returns the actual number of rows that have been extracted to the current row.

Parameter cursor

Different result sets can be generated when the cursor is opened multiple times with different parameter values.
Cursor cursor_name (parameter_name datatype) is select_statement;

Update/delete data with a cursor
Cursor cursor_name (parameter_name datatype) is select_statement for update [of column_reference] [Nowait];

For update is used to add a row share lock to the result set data to prevent other users from performing the DML operation again.
Of determine which tables need to be locked
Nowait specifies that the lock is not waiting for execution. If other sessions have been locked on the target row, an error message is displayed for the current session.

Update table_name set column = .. where current of cursor_name;
Delete table_name where current of cursor_name;

Cursor Loop

Oracle implicitly opens the cursor, extracts the cursor, and closes the cursor.
For record_name in cursor_name Loop
Statement1;
Statement2;
...
End loop;

If you do not need to use any cursor attribute during the loop, you can directly use the subquery
For record_name in (select_statement) loop
Statement1;
Statement2;
...
End loop;

Use 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 cursor
Open cursor_variable for select_statement;

Extract cursor data
Fetch cursor_variable into variable1, variable2 ,...;

Close cursor close
Close cursor_variable

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.