Oracle Database cursor case and source code (2), oracle cursor

Oracle Database cursor case and source code (2), oracle cursor

For detailed analysis of this case, see the Oracle database cursor case and source code.

Http://blog.csdn.net/sinat_26342009/article/details/45199827

Case requirements:
Use a cursor to convert the data of two tables.
First, insert data that meets the following three conditions into a new table (productinfo_tmp:
<1> price over 1000
<2> origin: "China" or "Hangzhou"
<3> the product type is "Household Appliances" or "Electronic Products"
Then, perform the following two operations in the new table (productinfo_tmp:
<1> the price is reduced by 2000 when the price is greater than 5%.

<2> convert product type numbers to product type names

Because the solution in Oracle Database cursor case and source code uses subqueries and cursors, it will cause greater system overhead and slow query speed, you can change the solution to the following method:

(For table creation statements and data insertion, see Oracle Database cursor case and source code)

Create or replace procedure prcd_1isbegin -- clear the original records in the temporary table delete from productinfo_tmp; -- query the required data, insert new data, and convert the CATEGORY field insert into productinfo_tmp (pid, PNAME, PRICE, QUANTY, CATEGORY, DESPERATION, origin) select pid, PNAME, PRICE, QUANTY, CATEGORY, DESPERATION, origin from productinfo, categoryinfo where productinfo. category = categoryinfo. CID and PRICE> 1000 and origin in ('China', 'hangzhou') and CNAME in ('electronic product', 'appliance '); -- product price downgrade update productinfo_tmp set productinfo_tmp.PRICE = productinfo_tmp.PRICE * 0.95 where productinfo_tmp.PRICE> 2000; commit; end prcd_1;

This not only avoids subqueries, but also eliminates the need to use cursors, which can greatly improve the execution efficiency of stored procedures!