Answer Posted / 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 |
Post New Answer View All Answers
What is pls integer?
how to check server status with 'mysqladmin'? : Sql dba
What is the importance of sqlcode and sqlerrm?
how to use myisamchk to check or repair myisam tables? : Sql dba
How do I copy a table in sql?
What is sorting in sql?
What is rename command in sql?
What are properties of the transaction?
what are the advantages and disadvantages of views in a database? : Sql dba
What packages(if any) has oracle provided for use by developers?
How can we optimize a sql query?
Write the command to remove all players named sachin from the players table.
What is trigger point?
What are different categories of sql commands?
Does truncate release storage space?
