oracle存储过程问题解答

Question

8、请阅读下面的存储过程：
/* Formatted on 2011/05/21 11:12 (Formatter Plus v4.8.7) */
DECLARE
out_msg VARCHAR2 (1000);
vcount INTEGER;
BEGIN
out_msg := '';

DECLARE
CURSOR cur_cust
IS
SELECT *
FROM base_customers;

   cur_row   cur_cust%ROWTYPE;

BEGIN
OPEN cur_cust;
LOOP
FETCH cur_cust
INTO cur_row;
EXIT WHEN cur_cust%NOTFOUND;
SELECT COUNT ()
INTO vcount
FROM rec_main_consume
WHERE customerid = cur_row.customerid;
insert into temp1(customerid,operno) values (cur_row.customerid,vcount);
if (SQL%ROWCOUNT>1)
then8、请阅读下面的存储过程：
/ Formatted on 2011/05/21 11:12 (Formatter Plus v4.8.7) */
DECLARE
out_msg VARCHAR2 (1000);
vcount INTEGER;
BEGIN
out_msg := '';

DECLARE
CURSOR cur_cust
IS
SELECT *
FROM base_customers;

   cur_row   cur_cust%ROWTYPE;

BEGIN
OPEN cur_cust;
LOOP
FETCH cur_cust
INTO cur_row;
EXIT WHEN cur_cust%NOTFOUND;
SELECT COUNT (*)
INTO vcount
FROM rec_main_consume
WHERE customerid = cur_row.customerid;
insert into temp1(customerid,operno) values (cur_row.customerid,vcount);
if (SQL%ROWCOUNT>1)
then
dbms_output.put_line(to_Char(SQL%ROWCOUNT));
end if;
END LOOP;
END;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
END;
认真阅读之后，请回答下面的问题。
1)请用自己的语言对此存储过程进行功能说明。
2)上述过程中 “ DECLARE
CURSOR cur_cust”
语句是什么用途以及含义？
3)上述过程运行结果什么？

Answer 1

DECLARE
CURSOR cur_cust 就是声明一个名为cur_cust的游标，存储过程大致意思就是查询base_customers表所有信息（
CURSOR cur_cust
IS
SELECT *
FROM base_customers），然后根据base_customers表的customerid字段查询rec_main_consume表（SELECT COUNT (*)
INTO vcount
FROM rec_main_consume
WHERE customerid = cur_row.customerid;），然后把customerid和根据 customerid查询rec_main_consume表的条数存入temp1表（
insert into temp1(customerid,operno) values (cur_row.customerid,vcount)），如果插入有数据，最后再把插入的条数打印出来也就是dbms_output.put_line(to_Char(SQL%ROWCOUNT))