Interested to Buy Any Domain ? << Click Here >> for more details...
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
Can a table contain multiple foreign key’s?
What is pl sql variable?
when MSQL8.0 is in market
Can we perform dml on view?
What is dense_rank in sql?
Which data dictionary views have the information on the triggers that are available in the database?
What is rank () in sql?
how to create a new table by selecting rows from another table in mysql? : Sql dba
Is there a pl/sql pragma similar to deterministic, but for the scope of one single sql select?
How to write a single statement that concatenates the words ?hello? And ?world? And assign it in a variable named greeting?
Can we commit inside a trigger?
What if we write return in procedure?
What are the topics in pl sql?
How does postgresql compare to oracle/db2/ms sql server/informix?
How can you fetch first 5 characters of the string?
