Oracle database Cursors case explanation and source code

1. The concept of cursors

A cursor is a data buffer opened by the system for the user, which holds the results of the SQL statement execution. Each cursor area has a name that allows the user to retrieve the record from the cursor one at a-and assign it to the main variable for further processing by the main language. In a database, cursors are a very important concept. Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is essentially a mechanism for extracting one record at a time from a result set that includes multiple data records. Cursors are always associated with an SQL query statement because the cursor consists of a result set (which can be 0, one, or multiple records retrieved by a related selection statement) and a cursor position in the result set that points to a particular record. When you decide to process a result set, you must declare a cursor that points to the result set.

One of the main reasons for using cursors is to convert the set operation to a single record processing mode. When data is retrieved from a database in SQL language, the result is placed in an area of memory, and the result is often a collection of multiple records. The cursor mechanism allows users to access these records row-by-line within SQL Server, displaying and processing these records at the user's own will.

2. Use of Cursors

In general, using cursors follows these general steps:
(1) Declares a cursor. Associate a cursor with the result set of a T-SQL statement.
(2) Open the cursor.
(3) Manipulate data using cursors.
(4) Close the cursor.

3. Classic case

I requirements:
Use cursors to transform data from two tables.
First, insert data that meets the following three criteria into a new table (PRODUCTINFO_TMP):
<1> Price greater than 1000
<2> origin is "China" or "Hangzhou"
<3> Product Type is "home appliance" or "electronic product"
Then, in the new Table (PRODUCTINFO_TMP), do the following two actions:
<1> Price is greater than 2000 cut by 5%
<2> product type number converted to commodity type name

II Analysis:
After obtaining the two tables of the commodity information table and the Commodity type information table containing the data, the following 5 steps are performed in the PL/SQL statement block:
<1> Create a new table (PRODUCTINFO_TMP);
<2> using SQL statements to query the data that meets the requirements;
<3> Insert compliant data into the new table (PRODUCTINFO_TMP);
<4> in the new Table (PRODUCTINFO_TMP), the price is greater than 2000 cut 5%
<5> Converting a product type number to a product type name in a new table (PRODUCTINFO_TMP)

||| Build the original table and insert the data

--Creating the original Table CREATE TABLE CategoryInfo (CID VARCHAR2 (primary) key, CNAME varchar2); Commit;create table ProductInfo (PID VARCHAR2 () primary key, PName VARCHAR2, price number (8,2), quanty number, category VARCHAR2 (01), Desperation VARCHAR2 (+), Origin varchar2 ()); commit;--inserting raw data insert into CategoryInfo (cid,cname) values ', ' food '); insert into CategoryInfo (cid,cname) VALUES (' 02 ', ' Home appliances '), insert into CategoryInfo (cid,cname) VALUES (' 03 ', ' Wash '); Insert into CategoryInfo (cid,cname) VALUES (' 04 ', ' electronic products '), insert into CategoryInfo (cid,cname) VALUES (' 05 ', ' office supplies '); Insert into CategoryInfo (cid,cname) VALUES (' 06 ', ' Toys '), insert into CategoryInfo (cid,cname) VALUES (' 07 ', ' stationery '); commit; Insert into ProductInfo (pid,pname,price,quanty,category,desperation,origin) VALUES (' 001 ', ' paper towel ', 20,10000, ' 03 ', ' Raw wood pulp ', ' Hebei '); insert into ProductInfo (pid,pname,price,quanty,category,desperation,origin) VALUES (' 002 ', ' laptops ', 5000,300, ' 04 ', ' rock solid, Chinese quality ', ' China '); insert into ProductInfo (pid,pname,price,qUanty,category,desperation,origin) VALUES (' 003 ', ' refrigerators ', 7000,900, ' 02 ', ' only once electricity per night ', ' Hangzhou '); insert into ProductInfo (PID, Pname,price,quanty,category,desperation,origin) VALUES (' 004 ', ' u disk ', 50,500, ' 04 ', ' with plug-and-unplug ', ' China '); INSERT into ProductInfo (Pid,pname,price,quanty,category,desperation,origin) VALUES (' 005 ', ' air conditioning ', 4000,100, ' 02 ', ' Make you Four Seasons like Spring ', ' Hangzhou '); commit;--Create a field property with the same empty table as the ProductInfo table create table productinfo_tmp as SELECT * from ProductInfo where 1=0;

|||| Implementing task requirements in a PL/SQL statement block

Declare-define variables, respectively, to store the number and name of the commodity category V_cid Categoryinfo.cid%type;  V_cname Categoryinfo.cname%type;  --defining variables, storing the records of commodity information V_prod Productinfo%rowtype;  --Define temporary variable Tmpnum number (8,0);  ---Define Cursors cursor CUR_PRDT_CATG is a select * from ProductInfo where price>1000 and origin in (' China ', ' Hangzhou ') and category in    (select CID from CategoryInfo where cname in (' Electronic products ', ' appliances '));  Cursor CUR_CATG is a select Cid,cname from CategoryInfo where cname in (' Electronic products ', ' home appliances '); Begin-Put the data into the new table open CUR_PRDT_CATG to meet the requirements;    --Open the cursor Loop fetch CUR_PRDT_CATG into V_prod; If Cur_prdt_catg%found then insert into Productinfo_tmp (Pid,pname,price,quanty,category,desperation,origin) Val    UEs (V_prod.pid,v_prod.pname,v_prod.price,v_prod.quanty,v_prod.category,v_prod.desperation,v_prod.origin); else Dbms_output.put_line (' All eligible data has been fetched, total ' | | cur_prdt_catg%rowcount| | '      Article ');    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 complete, total conversion ' | | tmpnum| | '          Article ');       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 changes, total changes ' | | sql%rowcount| | ' ); commit;end;

Perform:

Code map:

Oracle database Cursors case explanation and source code