cursor

EXPLICIT CURSORS – modify tables

In the previous article (cursors overview) we have seen simple examples with SQL statements. You can also use cursor to modify database tables. If we want to use explicit cursor to update or delete data, we need to: -declare the cursor with FOR UPDATE clause (to avoid unwanted changes from others). -use clause WHERE CURRENT OF to modify FETCH returned row. FOR UPDATE clause determines a specific row level lock on your data, so you are sure that data don’t change before your delete or update statement. FOR UPDATE syntax is the follow: DECLARE CURSOR cur_name IS SELECT […your cols…] Read More

EXPLICIT CURSORS – modify table

In the previous article (cursors overview) we have seen simple examples with SQL statements. You can also use cursor to modify database tables. If we want to use explicit cursor to update or delete data, we need to: -declare the cursor with FOR UPDATE clause (to avoid unwanted changes from others). -use clause WHERE CURRENT OF to modify FETCH returned row. FOR UPDATE clause determines a specific row level lock on your data, so you are sure that data don’t change before your delete or update statement. FOR UPDATE syntax is the follow: DECLARE CURSOR cur_name IS SELECT […your cols…] Read More

Cursor Overview

Suppose you have a database; you can have access to your data through a query as: SELECT […your cols…] FROM […your tables…] WHERE […your conditions…]; However, from a PL/SQL program, is not sufficient to run query, you need to store the data somewhere to use them. There are two alternatives: 1) if the result of a query is a single line, you can use a variable, with a syntax of this type:   SELECT […your cols…] INTO […your variable…] FROM […your tables…] WHERE […your conditions…]; 2) if the result of the query consists of multiple lines, you need to use Read More