Oracle Database cursor case description and source code

Oracle Database cursor case description and source code

1. cursor Concept

Cursor is a data buffer provided by the system for users to store the execution results of SQL statements. Each cursor area has a name. You can use SQL statements to obtain records from the cursor one by one and assign them to the primary variables for further processing. In databases, cursor is a very important concept. A cursor provides a flexible means to operate the data retrieved from a table. In essence, A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records. A cursor is always associated with an SQL query statement because it is a result set (it can be zero, one, or multiple records retrieved by the relevant selection statement) and the cursor position in the result set pointing to a specific record. When processing a result set, a cursor pointing to the result set must be declared.

A major reason for using cursor is to convert the set operation into a single record processing method. After retrieving data from a database using SQL, the results are stored in a memory area, and the results are often a collection containing multiple records. The cursor mechanism allows you to access these records row by row in SQL server and display and process these records as you wish.

2. cursor usage

Generally, using a cursor follows the following general steps:
(1) Declare the cursor. Associate the cursor with the result set of the T-SQL statement.
(2) Open the cursor.
(3) Use a cursor to operate data.
(4) Close the cursor.

3. Typical Cases

I 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

II analysis:
After obtaining the product information table and product type information table that contain data, perform the following five steps in the PL/SQL statement block:
<1> Create a new table (productinfo_tmp );
<2> use SQL statements to query data that meets requirements;
<3> Insert the data that meets the requirements into the new table (productinfo_tmp );
<4> in the new table (productinfo_tmp), reduce the price above 2000 by 5%.
<5> in the new table (productinfo_tmp), convert the product type number to the product type name.

| Create the original table and insert data

-- Create the original table create table categoryinfo (cid varchar2 (10) primary key, cname varchar2 (20); commit; create table productinfo (pid varchar2 (10) primary key, pname varchar2 (20), price number (8, 2), quanty number (10), category varchar2 (10), desperation varchar2 (1000), origin varchar2 (20); commit; -- insert original data insert into categoryinfo (cid, cname) values ('01', 'food'); insert into categoryinfo (cid, cname) values ('02 ', 'appliance '); insert into categoryinfo (cid, cname) values ('03', 'washing'); insert into categoryinfo (cid, cname) values ('04 ', 'Electronic product'); insert into categoryinfo (cid, cname) values ('05 ', 'Office supplies'); insert into categoryinfo (cid, cname) values ('06 ', 'toy'); insert into categoryinfo (cid, cname) values ('07 ', 'stationery'); commit; insert into productinfo (pid, pname, price, quanty, category, desperation, origin) values ('001', 'paper towels ', 20,10000, '03', 'native woodpulp', 'hebei '); insert into productinfo (pid, pname, price, quanty, category, desperation, origin) values ('002 ', 'laptop', 5000,300, '04 ', 'Rock solid, Chinese quality', 'China '); insert into productinfo (pid, pname, price, quanty, category, desperation, origin) values ('003 ', 'refrigerator', 7000,900, '02 ', 'Only one batch' per night, 'hangzhou'); insert into productinfo (pid, pname, price, quanty, category, desperation, origin) values ('004 ', 'U disk', 50,500, '04 ', 'with insertion and unput', 'China'); insert into productinfo (pid, pname, price, quanty, category, desperation, origin) values ('005 ', 'airconditioner', 4000,100, '02 ', 'Let you have spring seasons, 'hangzhou'); commit; -- create table productinfo_tmp as select * from productinfo where 1 = 0;

| Implement task requirements in PL/SQL statement Blocks

Declare -- defines the variables, respectively storing the commodity class number and name v_cid categoryinfo. cid % type; v_cname categoryinfo. cname % type; -- defines the variable, and stores the item information record v_prod productinfo % rowtype; -- defines the Temporary Variable tmpnum number (8, 0 ); -- Define the cursor cur_prdt_catg is select * from productinfo where price> 1000 and origin in ('China', 'hangzhou ') and category in (select cid from categoryinfo where cname in ('electronics product', 'appliance '); cursor cur_catg is select cid, cname from categoryinfo where cname in ('electronic product', 'appliance '); begin -- put data into the new table open cur_prdt_catg; -- open the cursor Loop fetch cur_prdt_catg into v_prod; if exist % found then insert into productinfo_tmp (pid, pname, price, quanty, category, desperation, origin) values (v_prod.pid, v_prod.pname, v_prod.price, region, v_prod.category, region, v_prod.origin ); else dbms_output.put_line ('all data that meets the condition has been taken out, in total '| cur_prdt_catg % rowcount | 'barri'); exit; end if; end loop; commit; -- convert product type open cur_catg; tmpnum: = 0; loop fetch cur_catg into v_cid, v_cname; if cur_catg % found then update productinfo_tmp set productinfo_tmp.category = v_cname where category = v_cid; if SQL % found then tmpnum: = tmpnum + SQL % rowcount; end if; else dbms_output.put_line ('product type conversion is complete, conversion is complete '| tmpnum | 'string '); exit; end if; end loop; -- product price downgrade update productinfo_tmp set productinfo_tmp.price = productinfo_tmp.price * 0.95 where productinfo_tmp.price> 2000; dbms_output.put_line ('product price changed, change '| SQL % rowcount |' bar '); commit; end;

Run:

Code diagram: