what is cursor procedure
Answer / manoj kaushik
/* Same as previous example, this time using a
** cursor. Each update commits as it is made.
*/
create procedure increase_price_cursor
as
declare @price money
/* declare a cursor for the select from titles */
declare curs cursor for
select price
from titles
for update of price
/* open the cursor */
open curs
/* fetch the first row */
fetch curs into @price
/* now loop, processing all the rows
** @@sqlstatus = 0 means successful fetch
** @@sqlstatus = 1 means error on previous fetch
** @@sqlstatus = 2 means end of result set reached
*/
while (@@sqlstatus != 2)
begin
/* check for errors */
if (@@sqlstatus = 1)
begin
print "Error in increase_price"
return
end
/* next adjust the price according to the
** criteria
*/
if @price > $60
select @price = @price * 1.05
else
if @price > $30 and @price <= $60
select @price = @price * 1.10
else
if @price <= $30
select @price = @price * 1.20
/* now, update the row */
update titles
set price = @price
where current of curs
/* fetch the next row */
fetch curs into @price
end
/* close the cursor and return */
close curs
return
Is This Answer Correct ? | 0 Yes | 0 No |
What are different clauses used in sql?
Why does %isopen return false for an implicit cursor?
Explain the purpose of %type and %rowtype data types with the example?
How do I get sql certification?
How do you get column names only for a table (sql server)?
can a stored procedure call itself or recursive stored procedure? : Sql dba
Can we use two order by clause in query?
What is trigger in pl sql with examples?
How is indexing done in search engines?
What is the purpose of primary key?
what is the difference between where clause and having clause? : Sql dba
Explain raise_application_error.