在游标的声明中有一点值得注意的是,如同其它变量的声明一样,声明游标的这一段代码行是不执行的,您不能将debug时的断点设在这一代码行上,也不能用IF...END IF语句来声明两个同名的游标,如下列的代码就是错误的。
IF Is_prov="北京"THEN
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province="北京";
ELSE
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province〈〉"北京";
END IF
由于打开游标是对数据库进行一些SQL SELECT的操作,它将耗费一段时间,主要取决于您使用的系统性能和这条语句的复杂程度。如果执行的时间较长,可以考虑将屏幕上显示的鼠标改为hourglass(这句不会)
从语法上讲,上面所述的就是一条合法的取数据的语句,但是一般我们使用游标却还应当包括其它的部分。正如我们前面所谈到的,游标只能一次从后台数据库中取一条记录,而在多数情况下,我们所想要作的是在数据库中从第一条记录开始提取,一直到结束。所以我们一般要将游标提取数据的语句放在一个循环体内,直至将结果集中的全部数据提取后,跳出循环圈。通过检测SQLCA.SQL-CODE的值,可以得知最后一条FETCH语句是否成功。一般,当SQLCODE值为0时表明一切正常,100表示已经取到了结果集的末尾,而其它值均表明操作出了问题,
显示游标
显示游标包括四种属性：
%ISOPEN ：检测游标是否已经打开
%FOUND ：检测游标结果集是否存在数据，存在则返回TRUE
%NOTFOUND ：检测游标结果集是否不存在数据，不存在则返回TRUE
%ROWCOUNT ：返回已提取的实际行数
使用显示游标
定义游标：CURSOR cursor_name IS select_statement；
打开游标：OPEN cursor_name；
提取数据：FETCH cursor_name INTO variable1[,variable2,…];
FETCH INTO每次只能提取一行数据，批量数据需使用循环
使用游标变量接受数据：
SQL> declare
2 cursor emp_cursor is
3 select ename,job,sal from emp where deptno=&dno;
4 vname emp.ename%type;
5 vsal emp.sal%type;
6 vjob emp.job%type;
7 begin Oracle 学习笔记 第 74 页 共 90 页
8 open emp_cursor;
9 loop
10 fetch emp_cursor into vname,vjob,vsal;
11 exit when emp_cursor%notfound;
12 dbms_output.put_line('姓名：'||vname||'，岗位：'||vjob||'，工资：'||vsal);
13 end loop;
14 close emp_cursor;
15 end;
16 /
输入 dno 的值: 30
姓名：ALLEN，岗位：SALESMAN，工资：1600
姓名：WARD，岗位：SALESMAN，工资：1250
姓名：JONES，岗位：MANAGER，工资：3272.5
PL/SQL 过程已成功完成。
使用PL/SQL记录变量接受游标数据：简化单行数据处理
SQL> declare
2 cursor ecur is select ename,sal from emp order by sal desc;
3 erec ecur%rowtype;
4 begin
5 open ecur;
6 loop
7 fetch ecur into erec;
8 exit when ecur%notfound or ecur%rowcount>&n;
9 dbms_output.put_line('姓名：'||erec.ename||'，工资：'||erec.sal);
10 end loop;
11 close ecur;
12 end;
13 /
输入 n 的值: 5
姓名：KING，工资：5000
姓名：FORD，工资：3300
姓名：JONES，工资：3272.5
姓名：BLAKE，工资：2850
姓名：MARY，工资：2000
PL/SQL 过程已成功完成。
使用PL/SQL集合变量接受游标数据：简化多行多列数据处理
SQL> declare
2 cursor ec is select ename,sal from emp where lower(job)=lower('&job');
3 type etype is table of ec%rowtype index by binary_integer;
4 et etype;
5 i int;
6 begin
7 open ec;
8 loop Oracle 学习笔记 第 75 页 共 90 页
9 i:=ec%rowcount+1;
10 fetch ec into et(i);
11 exit when ec%notfound;
12 dbms_output.put_line('姓名：'||et(i).ename||'，工资：'||et(i).sal);
13 end loop;
14 close ec;
15 end;
16 /
输入 job 的值: manager
姓名：JONES，工资：3272.5
姓名：BLAKE，工资：2850
姓名：CLARK，工资：1500
PL/SQL 过程已成功完成。
游标FOR循环
使用游标for循环时，oracle会隐含的打开游标，提取数据并关闭游标
在游标for循环中引用已定义游标：
SQL> declare
2 cursor ec is select ename,hiredate from emp order by hiredate desc;
3 begin
4 for erec in ec loop
5 dbms_output.put_line('姓名：'||erec.ename||'，工作日期：'||erec.hiredate);
6 exit when ec%rowcount=&n;
7 end loop;
8 end;
9 /
输入 n 的值: 3
姓名：MARY，工作日期：
姓名：ADAMS，工作日期：23-5月 -87
姓名：SCOTT，工作日期：01-1月 -84
PL/SQL 过程已成功完成。
在游标for循环中直接引用子查询：
SQL> begin
2 for erec in (select ename,hiredate,rownum from emp order by hiredate) loop
3 dbms_output.put_line('姓名：'||erec.ename||'，工作日期：'||erec.hiredate);
4 exit when erec.rownum=&n;end loop;
5 end;
6 /
输入 n 的值: 2
姓名：ALLEN，工作日期：20-2月 -81
姓名：WARD，工作日期：22-2月 -81
PL/SQL 过程已成功完成。
参数游标：参数只能指定数据类型，不能指定长度，而且必须在where子句中引用参数
SQL> declare Oracle 学习笔记 第 76 页 共 90 页
2 cursor ec(dno number) is select ename,job from emp where deptno=dno;
3 begin
4 for erec in ec(&dno) loop
5 dbms_output.put_line('姓名：'||erec.ename||'，岗位：'||erec.job);
6 end loop;
7 end;
8 /
输入 dno 的值: 30
姓名：ALLEN，岗位：SALESMAN
姓名：WARD，岗位：SALESMAN
姓名：JONES，岗位：MANAGER
PL/SQL 过程已成功完成。
更新游标行
declare
cursor emp_cursor is select ename,sal,deptno from emp for update;
dno int:=&no;
begin
for emp_record in emp_cursor loop
if emp_record.deptno=dno then
dbms_output.put_line(‘姓名：’||emp_record.ename||’，原工资：’||emp_record.sal);
update emp set sal=sal*1.1 where current of emp_cursor;
end if;
end loop;
end;
/
删除游标行
declare
cursor emp_cursor is select ename from emp for update;
name varchar2(10):=lower(‘&name’);
begin
for emp_record in emp_cursor loop
if lower(emp_record.ename)=name then
delete from emp where current of emp_cursor;
else
dbms_output.put_line(‘姓名：’||emp_record.ename);
end if;
end loop;
end;
/
使用for子句在特定表上加共享锁(涉及多张表时的同步问题)
SQL> declare
2 cursor emp_cursor is Oracle 学习笔记第 77 页 共 90 页
select a.dname,b.ename from dept a JOIN emp b ON a.deprno=b.deptno;
3 name varchar2(10):=lower('&name');
4 begin
5 for emp_record in emp_cursor loop
6 if lower(emp_record.dname)=name then
7 dbms_output.put_line(‘姓名：’||emp_record.ename);
8 delete from emp where current of emp_cursor;
9 end if;
10 end loop;
11 end;
12 /
输入 name 的值: sales
PL/SQL过程已完成。
游标变量
游标变量是基于REF CURSOR类型所定义的变量，它实际上是指向内存地址的指针。使用显式游标只能定义静态游标，而通过使用游标变量可以在打开游标时指定其对应的select语句，从而实现动态游标。
使用无返回类型的游标变量
SQL> set serveroutput on
SQL> set verify off
SQL> declare
2 type ref_cursor_type is ref cursor;
3 rc ref_cursor_type;
4 v1 number(6);
5 v2 varchar2(10);
6 begin
7 open rc for
8 select &col1 col1,&col2 col2 from &table where &cond;
9 loop
10 fetch rc into v1,v2;
11 exit when rc%notfound;
12 dbms_output.put_line('col1= '||v1||',col2= '||v2);
13 end loop;
14 close rc;
15 end;
16 /
输入 col1 的值: empno
输入 col2 的值: ename
输入 table 的值: emp
输入 cond 的值: deptno=10
col1= 7782,col2= CLARK
col1= 7839,col2= KING
col1= 7934,col2= MILLER
PL/SQL 过程已成功完成。 Oracle 学习笔记 第 78 页 共 90 页
使用有返回类型的游标变量
SQL> declare
2 type emp_cursor_type is ref cursor return emp%rowtype;
3 ec emp_cursor_type;
4 er emp%rowtype;
5 begin
6 open ec for select * from emp where deptno=&dno;
7 loop
8 fetch ec into er;
9 exit when ec%notfound;
10 dbms_output.put_line('姓名：'||er.ename||'，工资：'||er.sal);
11 end loop;
12 close ec;
13 end;
14 /
输入 dno 的值: 20
姓名：SMITH，工资：800
姓名：JONES，工资：2975
姓名：SCOTT，工资：3000
姓名：ADAMS，工资：1100
姓名：FORD，工资：3000
PL/SQL 过程已成功完成。
使用批量提取
使用fetch…bulk collect提取所有数据;
SQL> declare
2 cursor ec is
3 select * from emp where lower(job)=lower('&job');
4 type etype is table of emp%rowtype;
5 et etype;
6 begin
7 open ec;
8 fetch ec bulk collect into et;
9 close ec;
10 for i in 1..et.count loop
11 dbms_output.put_line('姓名：'||et(i).ename||'，工资：'||et(i).sal);
12 end loop;
13 end;
14 /
输入 job 的值: clerk
姓名：SMITH，工资：800
姓名：ADAMS，工资：1100
姓名：JAMES，工资：950
姓名：MILLER，工资：1300
PL/SQL 过程已成功完成。 Oracle 学习笔记 第 79 页 共 90 页
使用LIMIT子句限制提取行数
SQL> declare
2 cursor ec is select * from emp;
3 type emp_array_type is varray(5) of emp%rowtype;
4 ea emp_array_type;
5 begin
6 open ec;
7 loop
8 fetch ec bulk collect into ea limit &rows;
9 for i in 1..ea.count loop
10 dbms_output.put_line('姓名：'||ea(i).ename||'，工资：'||ea(i).sal);
11 end loop;
12 exit when ec%notfound;
13 end loop;
14 close ec;
15 end;
16 /
输入 rows 的值: 4
姓名：SMITH，工资：800
姓名：ALLEN，工资：1600
姓名：WARD，工资：1250
姓名：JONES，工资：2975
姓名：MARTIN，工资：1250
姓名：BLAKE，工资：2850
姓名：CLARK，工资：2450
姓名：SCOTT，工资：3000
姓名：KING，工资：5000
姓名：TURNER，工资：1500
姓名：ADAMS，工资：1100
姓名：JAMES，工资：950
姓名：FORD，工资：3000
姓名：MILLER，工资：1300
PL/SQL 过程已成功完成。
使用cursor表达式
SQL> declare
2 cursor dept_cursor(no number) is
3 select a.dname,cursor(select * from emp where deptno=a.deptno)
4 from dept a where a.deptno=no;
5 type ref_cursor_type is ref cursor;
6 ec ref_cursor_type;
7 er emp%rowtype;
8 vdname dept.dname%type;
9 begin
10 open dept_cursor(&dno); Oracle 学习笔记第 80 页 共 90 页
11 loop
12 fetch dept_cursor into vdname,ec;
13 exit when dept_cursor%notfound;
14 dbms_output.put_line('部门名：'||vdname);
15 loop
16 fetch ec into er;
17 exit when ec%notfound;
18 dbms_output.put_line('----雇员名：'||er.ename||'，岗位：'||er.job);
19 end loop;
20 end loop;
21 close dept_cursor;
22 end;
23 /
输入 dno 的值: 10
部门名：ACCOUNTING
----雇员名：CLARK，岗位：MANAGER
----雇员名：KING，岗位：PRESIDENT
----雇员名：MILLER，岗位：CLERK
PL/SQL 过程已成功完成。
在存储过程或触发器中使用 Transact-SQL 游标的典型过程为：
- 声明 Transact-SQL 变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从列数据类型隐式转换得到的数据类型。
- 使用 DECLARE CURSOR 语句将 Transact-SQL 游标与 SELECT 语句相关联。另外，DECLARE CURSOR 语句还定义游标的特性，例如游标名称以及游标是只读还是只进。
- 使用 OPEN 语句执行 SELECT 语句并填充游标。
- 使用 FETCH INTO 语句提取单个行，并将每列中的数据移至指定的变量中。然后，其他 Transact-SQL 语句可以引用那些变量来访问提取的数据值。Transact-SQL 游标不支持提取行块。
使用 CLOSE 语句结束游标的使用。关闭游标可以释放某些资源，例如游标结果集及其对当前行的锁定，但如果重新发出一个 OPEN 语句，则该游标结构仍可用于处理。由于游标仍然存在，此时还不能重新使用该游标的名称。DEALLOCATE 语句则完全释放分配给游标的资源，包括游标名称。释放游标后，必须使用 DECLARE 语句来重新生成游标。
-----------------------------------------------------
/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
USE AdventureWorks
GO
DECLARE @MyVariable CURSOR
DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks.Person.Contact
SET @MyVariable = MyCursor
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR
SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks.Person.Contact;
DEALLOCATE MyCursor;
-----------------------------------------------------
USE AdventureWorks
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @LastName varchar(50), @FirstName varchar(50)
DECLARE contact_cursor CURSOR FOR
SELECT LastName, FirstName FROM Person.Contact
WHERE LastName LIKE 'B%'
ORDER BY LastName, FirstName
OPEN contact_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM contact_cursor
INTO @LastName, @FirstName
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
PRINT 'Contact Name: ' + @FirstName + ' ' + @LastName
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM contact_cursor
INTO @LastName, @FirstName
END
CLOSE contact_cursor
DEALLOCATE contact_cursor
GO
一个游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)。游标每个时间点只能指向一行，但是可以根据需要指向结果集中其他的行。
例如：SELECT * FROM employees WHERE sex='M'会返回所有性别为男的雇员，在初始的时候，游标被放置在结果集中第一行的前面。使游标指向第一行，要执行FETCH。当游标指向结果集中一行的时候，可以对这行数据进行加工处理，要想得到下一行数据，要继续执行FETCH。FETCH操作可以重复执行，直到完成结果集中的所有行
在存储过程中使用游标，有如下几个步骤：
声明游标、打开游标、根据需要一次一行，讲游标指向的数据取到本地变量(local variables)中、结束时关闭游标
声明游标：
>>-DECLARE--cursor-name--CURSOR----+------------+--------->
'-WITH HOLD--'
>-----+--------------------------------+--------------------->
| .-TO CALLER--. |
'-WITH RETURN--+------------+--'
'-TO CLIENT--'
>----FOR--+-select-statement-+----------------------------><
'-statement-name---'
WITH RETURN子句用于将游标所定义的结果集传递给另一个存储过程或者应用(an application)
如果select语句中包含CURRENT DATE, CURRENT TIME和CURRENT TIMESTAMP，所有的FETCH语句都会返回相同的日期、时间、时间戳值，因为这些特定寄存器是在打开游标(OPEN CURSOR)的时候进行检查的
FETCH语法：
>>-FETCH--+-------+---cursor-name---------->
'-FROM--'
.-,----------------.
V |
>------INTO-----host-variable---+----------><
FETCH语句使游标指向结果集中的下一行，并且将游标现在的位置赋值给特定的过程变量
例如：一个公司，按照如下规则计算加薪金额：
1.公司中除了总裁(president)外，所有人都会至少增加p_min的薪水
2.任何奖金(bonus)高于$600的员工都会另增加4%
3.员工的佣金(commission)越高，增加越少。佣金(commission)少于$2000的另增加3%，佣金(commission)在$2000到$3000的增加另2%
4.佣金(commission)高于$3000的另增加1%
5.无论每个员工增加多少，增加比例不能高于p_max
CREATE PROCEDURE total_raise ( IN p_min DEC(4,2)
, IN p_max DEC(4,2)
, OUT p_total DEC(9,2) )
LANGUAGE SQL
SPECIFIC total_raise
tr: BEGIN
-- Declare variables
DECLARE v_salary DEC(9,2);
DECLARE v_bonus DEC(9,2);
DECLARE v_comm DEC(9,2);
DECLARE v_raise DEC(4,2);
DECLARE v_job VARCHAR(15) DEFAULT 'PRES';
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
-- Procedure logic
DECLARE c_emp CURSOR FOR
SELECT salary, bonus, comm
FROM employee
WHERE job != v_job; -- (1)这里的SELECT定义了结果集中的行和
列
OPEN c_emp; -- (2)
SET p_total = 0;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; -- (3)得到一行数据，并将其
复制给本地变量
WHILE ( SQLSTATE = '00000' ) DO --SQLSTATE 00000: 操作执行成功，
并且未产生任何类型的警告或异常情
况。通过这个可以检查是否到达最后一行
SET v_raise = p_min;
IF ( v_bonus >= 600 ) THEN
SET v_raise = v_raise + 0.04;
END IF;
IF ( v_comm < 2000 ) THEN
SET v_raise = v_raise + 0.03;
ELSEIF ( v_comm < 3000 ) THEN
SET v_raise = v_raise + 0.02;
ELSE
SET v_raise = v_raise + 0.01;
END IF;
IF ( v_raise > p_max ) THEN
SET v_raise = p_max;
END IF;
SET p_total = p_total + v_salary * v_raise;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; -- (4)在WHILE逻辑中得到
更多的行数据
END WHILE;
CLOSE c_emp; -- (5)
END tr
如果只是想把结果集中的第一个值复制给本地变量，而声明一个游标是不恰当的，因为打开游标会耗费很多资源。所以如下这段代码：
DECLARE c_tmp CURSOR FOR
SELECT c1
FROM t1;
OPEN c_emp;
FETCH FROM c_emp INTO v_c1;
CLOSE c_emp;
应当用有FETCH FIRST 1 ROW ONLY的子句的SQL语句：SELECT c1 INTO v_c1 FROM t1 FETCH FIRST 1 ROW ONLY;
positioned delete：利用游标删除当前行
一个用于删除的游标(a deletable cursor)应该符合以下的要求：
1.每个outer fullselect中的FROM子句只跟一个表有关
2.outer fullselect不包含VALUES, GROUP BY, 或者HAVING子句，并且不包括列函数
3.outer fullselect的select列表中不包含DISTINCT
4.select语句不包含ORDER BY或FOR READ ONLY子句
5.游标是静态定义的，或者明确了FOR UPDATE子句
>>-DELETE FROM-|----table-name---------|--------------->
+-----view-name---------+
>----WHERE CURRENT OF--cursor-name--------------------><
例如：在emp_act表中，如果记录的时间比输入参数p_date早的话，就将该记录删除，并返回删除记录总数
CREATE PROCEDURE cleanup_act ( IN p_date DATE
, OUT p_deleted INT )
LANGUAGE SQL
SPECIFIC cleanup_act
ca: BEGIN
-- Declare variable
DECLARE v_date DATE;
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
-- Procedure logic
DECLARE c_emp CURSOR FOR -- (1)和上面那种read-only cursor语法
类似，只是多了FOR UPDATE
SELECT emendate
FROM emp_act
FOR UPDATE;
OPEN c_emp;
FETCH FROM c_emp INTO v_date; --注意此处，不要落了
SET p_deleted = 0;
WHILE ( SQLSTATE = '00000' ) DO
IF ( v_date < p_date ) THEN
DELETE FROM emp_act
WHERE CURRENT OF c_emp; -- (2)
SET p_deleted = p_deleted + 1;
END IF;
FETCH FROM c_emp INTO v_date;
END WHILE;
CLOSE c_emp;
END ca
直接用DELETE语句删除而不用游标被称作searched delete。像上例这种情况，采用searched delete会比使用positioned delete效率更高。但用positioned delete可以处理更复杂的逻辑
Positioned Update
一个用于更新的游标(A cursor is updatable)应该The cursor is deletable
>>-UPDATE----+-table-name-------------------+-------------->
+-view-name--------------------+
>-----SET--| assignment-clause |--------------------------->
>-----WHERE CURRENT OF--cursor-name-----------------------><
CREATE PROCEDURE upd_raise ( IN p_min DEC(4,2)
, IN p_max DEC(4,2) )
LANGUAGE SQL
SPECIFIC upd_raise
ur: BEGIN
-- Declare variables
DECLARE v_salary DEC(9,2);
DECLARE v_bonus DEC(9,2);
DECLARE v_comm DEC(9,2);
DECLARE v_raise DEC(4,2);
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
-- Procedure logic
DECLARE c_emp CURSOR FOR
SELECT salary, bonus, comm
FROM employee
WHERE job!='PRES'
FOR UPDATE OF salary; -- (1)如果只是更新表中的一部分字段，可以利用
FOR UPDATE OF <column list>提高效
率，让DB2引擎知道只有这些特定列要UPDATE
OPEN c_emp;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;
WHILE ( SQLSTATE = '00000' ) DO
SET v_raise = p_min;
IF ( v_bonus >= 600 ) THEN
SET v_raise = v_raise + 0.04;
END IF;
IF ( v_comm < 2000 ) THEN
SET v_raise = v_raise + 0.03;
ELSEIF ( v_comm < 3000 ) THEN
SET v_raise = v_raise + 0.02;
ELSE
SET v_raise = v_raise + 0.01;
END IF;
IF ( v_raise > p_max ) THEN
SET v_raise = p_max;
END IF;
UPDATE employee
SET salary = v_salary * (1 + v_raise)
WHERE CURRENT OF c_emp;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;
END WHILE;
CLOSE c_emp;
END ur
使用游标时候的COMMIT和ROLLBACK：
数据库程序中很重要的一点就是事务处理(transaction或者the unit of work(UOW))。事务当中的任何一部分失败，整个事物就会失败。利用COMMIT和ROLLBACK进行适当的事务控制对于保证数据完整性来说是至关重要的。
当在使用游标的时候使用COMMIT或者ROLLBACK语句时，游标的行动取决于是否在生命的时候加了WITH HOLD子句。如果一个游标在声明的时候没有指定WITH HOLD，那么它的所有资源（游标，锁，大对象数据类型或者LOB locators）都将在COMMIT或者ROLLBACK之后被释放。因此，如果需要在完成一个事务之后使用游标，就必须重新打开游标，并从第一行开始执行。如果定义了一个游标WITH HOLD
，游标就会在事务之间保存它的位置和锁(lock)。需要明白的是，只有保证游标位置的锁被held了。
锁(lock)是个数据库对象(a database object)，我们用它来控制多个应用访问同一个资源的方式。而一个LOB locator使存储在本地变量中的4字节的值，程序可以用它来查到数据库系统中的LOB对象的值
定义了WITH HOLD的游标在COMMIT之后
1.仍然保证是打开(open)的
2.游标指向下一个满足条件的行之前
3.在COMMIT语句之后只允许FETCH和CLOSE
4.Positioned delete和positioned update只在同一事务中fetch的行上可用
5.所有的LOB locators会被释放
6.除了保存声明为WITH HOLD的游标位置的锁，其他锁都会释放
7.当执行了数据修改语句或者含有WITH HOLD游标的修改语句被commit的时候
所有定义为WITH HOLD的游标在ROLLBACK之后：
1.所有游标会被关闭
2.所有在该事务中的锁会被释放
3.所有的LOB locators会被freed
例如：
CREATE PROCEDURE update_department ( )
LANGUAGE SQL
SPECIFIC upd_dept
ud: BEGIN
-- Declare variable
DECLARE v_deptno CHAR(3);
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
DECLARE c_dept CURSOR WITH HOLD FOR
SELECT deptno
FROM department
FOR UPDATE OF location;
-- Declare condition handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '24504', SQLSTATE '24501'
L1: LOOP -- (1)
LEAVE L1;
END LOOP;
-- Procedure logic
OPEN c_dept; --打开游标，指向第一行前面的位置
FETCH FROM c_dept INTO v_deptno; -- (2)
UPDATE department SET location='FLOOR1' WHERE CURRENT OF c_dept; -- (3)
COMMIT; -- (4)因为该游标声明为WITH
HOLD，此时游标依旧打开，并且
指向第二行前面的位置。此时表
中第一行的锁释放了，且第一行
的值更新为FLOOR1
FETCH FROM c_dept INTO v_deptno; -- (5)得到第二行数据，执行成功
COMMIT; -- (6)COMMIT后游标指向第三行之
前的位置，此时并没有被fetched
UPDATE department SET location='FLOOR2' WHERE CURRENT OF c_dept; -- (7)这行
命令执行失败，因为此时游标没有指向
任何行，此时游标在第二行和第三行之间
FETCH FROM c_dept INTO v_deptno; -- (8)成功
UPDATE department SET location='FLOOR3' WHERE CURRENT OF c_dept; -- (9)成功
COMMIT; -- (10)
FETCH FROM c_dept INTO v_deptno; -- (11)成功
UPDATE department SET location='FLOOR4' WHERE CURRENT OF c_dept; -- (12)成
功，此时第三行和第四行的值都变了
ROLLBACK; -- (13)第四行的值还原。
ROLLBACK之后游标关闭了，
所有的锁也都释放了
FETCH FROM c_dept INTO v_deptno; -- (14)错误
UPDATE department SET location='FLOOR5' WHERE CURRENT OF c_dept; -- (15)
CLOSE c_dept;
RETURN 0;
END ud
上述存储过程执行前：
DEPTNO LOCATION
------ --------
A00 -
B01 -
C01 -
D01 -
D11 -
上述存储过程执行后：
DEPTNO LOCATION
------ --------
A00 FLOOR1
B01 -
C01 FLOOR3
D01 -
D11 -
如果上例中的游标没有声明为WITH HOLD，从(5)到(15)的执行都会失败。因为游标会在COMMIT或ROLLBACK之后隐性关闭
存储过程中的Save Points可用于保存事务回滚的间断点
>>- SAVEPOINT--savepoint-name----+--------+--------------------->
'-UNIQUE-'
>--ON ROLLBACK RETAIN CURSORS--+--------------------------+----><
'-ON ROLLBACK RETAIN LOCKS-'
savepoint-name不能以'SYS'开头，否则会报SQLSTATE 42939的错误。UNIQUE选项表示这个save point name不会在Save Point活动期中被reused。ON ROLLBACK RETAIN CURSORS使游标在rollback发生之后还被保留。附加的ON ROLLBACK RETAIN LOCKS防止在ROLLBACK之后锁丢失
在一个事务中，可以定义多个save points
使用save points的伪代码：
savepoint A;
Do program logic;
savepoint B;
Do more program logic;
savepoint C;
Do even more program logic;
之后就可以用含有SAVE POINT的ROLLBACK：ROLLBACK TO SAVEPOINT savepoint-name
如果ROLLBACK到了最后一个save point，那么这个save point之前的save point都还是活动的(active)，你依旧可以ROLL BACK到更早的save point
例如：
savepoint a;
Do program logic;
savepoint b;
Do more program logic;
savepoint c;
Do even more program logic;
ROLLBACK TO SAVEPOINT c; (1)将事务数据返回到save point c
Do some new logic;
ROLLBACK TO SAVEPOINT a; (2)将事务数据返回到save point a
Do some more logic;
ROLLBACK TO SAVEPOINT b; (3)错误，因为此时save point b已经不存在了
游标除了可以在存储过程中处理数据外，还可以用于返回结果集
比如：
CREATE PROCEDURE read_emp ( )
LANGUAGE SQL
SPECIFIC read_emp
DYNAMIC RESULT SETS 1 --(1)如果想用游标返回结果集到一个应用程序，必须声明DYNAMIC RESULT SETS
re: BEGIN
-- Procedure logic
DECLARE c_emp CURSOR WITH RETURN FOR --(2)
SELECT salary, bonus, comm
FROM employee
WHERE job!='PRES';
OPEN c_emp; --(3)为客户端保持游标打开
END re
此时只返回了所有符合条件的员工的salary, bonus和commission字段。之后，结果集就可以被另外的存储过程或客户端程序调用
在存储过程中我们除了数据操作语言(Data Manipulation Language (DML):SELECT, DELETE和UPDATE)，还可以使用数据定义语言(Data Definition Language (DDL))，比如定义一个表。我们可以在存储过程中定义一个表，然后用游标返回结果集。但是游标声明必须在BEGIN ... END的一开始，但如果这样的话，此时表还没有建立，编译时会报错。但如果先声明表，编译也会报错。这是我们可以用BEGIN ... END可以嵌套这个特性。我们在存储过程末尾嵌套一个BEGIN ... END来声明游标
如：
CREATE PROCEDURE create_and_return ( )
LANGUAGE SQL
SPECIFIC create_and_return
DYNAMIC RESULT SETS 1
cr: BEGIN
-- Procedure logic
CREATE TABLE mytable (sid INT);
INSERT INTO mytable VALUES (1);
INSERT INTO mytable VALUES (2);
BEGIN --(1)
DECLARE c_cur CURSOR WITH RETURN
FOR SELECT *
FROM mytable;
OPEN c_cur; --(2)
END; --(3)OPEN必须在这个嵌套的BEGIN ... END中，因为游
标的定义只在这个BEGIN ... END中有效
END cr
有时我们不只返回一个结果集，若返回多个结果集，要求：
1.CREATE PROCEDURE中的DYNAMIC RESULT SETS子句写明想返回的结果集的数量
2.为每一个结果集声明含有WITH RETURN的游标
3.保证所有游标返回给客户端是打开的
例如：
CREATE PROCEDURE read_emp_multi ( )
LANGUAGE SQL
SPECIFIC read_emp_multi
DYNAMIC RESULT SETS 3 --(1)
re: BEGIN
-- Procedure logic
DECLARE c_salary CURSOR WITH RETURN FOR
SELECT salary
FROM employee;
DECLARE c_bonus CURSOR WITH RETURN FOR
SELECT bonus
FROM employee;
DECLARE c_comm CURSOR WITH RETURN FOR
SELECT comm
FROM employee;
OPEN c_salary;
OPEN c_bonus;
OPEN c_comm;
END re
游标打开的顺序反映了结果集返回给客户端的顺序
当在存储过程中使用游标的时候，会影响其他应用和人们使用这个数据库。锁的类型取决于游标的类型和DB2的隔离级别(isolation level)
锁模式(Lock Modes)：
Table Row Lock Descriptions
|
|
Lock Mode
|
Applicable Object Type
|
Description
|
|
S (Share)
|
Rows, blocks, tables
|
The lock owner and all concurrent applications can read, but not update, the locked data.
|
|
U (Update)
|
Rows, blocks, tables
|
The lock owner can update data. Other UOW can read the data in the locked object, but cannot attempt to update it.
|
|
X (Exclusive)
|
Rows, blocks, tables, bufferpools
|
The lock owner can both read and update data in the locked object. Only uncommitted read applications can access the locked object.
|
不管是sql server 还是 oracle都提供了游标，其一般用途就是从查询结构中遍历数据。游标很好用，许多初学oracle或者sql server的程序员都喜欢用，然而如果滥用游标的话，对程序的性能会造成很大的影响。我认为：除非不得已，不要使用游标。下面是我的一次优化经历。
问题：
在我们的系统中，需要管理产品模型，对PDM了解的人知道，其实产品模型可以当作一个特殊的零部件来处理，所以在数据表的时候产品模型与零部件使用相同的数据表来存放数据，只不过是用一个flag来标示是零部件还是产品模型。产品模型具有版本。现在的要求的是查出最新的所有的零部件信息，查询的时候允许用户自定义查询条件。
用户自定义查询条件是从界面上通过各种选项来构造的，因此传递的是动态SQL语句，为了提高效率数据库上建立了一个存储过程专门来查询产品模型。下面是一个初学oracle不久的编程人员编写的存储过程：
1
PROCEDURE GETPRODMODELBYSQL(p_sql IN VARCHAR2,
2 cur_mi_prodmodel OUT SYS_BASE.BASECURSOR) AS
3 sqlstr varchar2(1000);
4 idstr varchar2(1000);
5
6
7 dis_id number;
8 maxver number;
9 cursor disid_curs is
10 select distinct pt_id
11
from mi_part
12 where pt_flag = 2;
13 BEGIN
14 open disid_curs;
15 loop
16 fetch disid_curs
17 into dis_id;
18 exit when disid_curs% notfound;
19
20 select max(pv_id)
21 into maxver
22 from mi_partver
23 where pv_partid = dis_id;
24
25 if maxver is null then
26 idstr := idstr;
27 else
28 idstr := idstr || maxver || ',';
29 end if;
30 end loop;
31
32 idstr := rtrim(idstr, ',');
33 idstr := ltrim(idstr, ',');
34 if idstr is null then
35 sqlstr := p_sql || ' ORDER BY PV_PRODUCTID,PT_PARTNUMBER';
36 else
37 sqlstr := p_sql || ' and MI_PARTVER.pv_id in (' || idstr || ') ORDER BY PV_PRODUCTID,PT_PARTNUMBER';
38 end if;
39
40 open cur_mi_prodmodel for sqlstr;
41 END GETPRODMODELBYSQL;
这个存储过程可以正常工作，但是其速度执行起来非常慢，如果数据表中有很多数据的话，其速度很可能是直线下降，究其原因是因为使用游标来遍历然后构造查询语句。对这个存储过程进行优化：
1PROCEDURE GETPRODMODELBYSQL(p_sql IN VARCHAR2,
2 cur_mi_prodmodel OUT SYS_BASE.BASECURSOR) AS
3 s varchar2(300);
4 sqlstr varchar2(1000);
5 /**//*idstr varchar2(1000);
6
7
8 dis_id number;
9 maxver number;
10 cursor disid_curs is
11 select distinct pt_id
12 from mi_part
13 where pt_flag = 2; */
14 BEGIN
15 /**//*open disid_curs;
16 loop
17 fetch disid_curs
18 into dis_id;
19 exit when disid_curs% notfound;
20
21 select max(pv_id)
22 into maxver
23 from mi_partver
24 where pv_partid = dis_id;
25
26 if maxver is null then
27 idstr := idstr;
28 else
29 idstr := idstr || maxver || ',';
30 end if;
31 end loop;*/
32 s:=' and MI_PARTVER.pv_id in (select a.pv_id from mi_partver a,mi_part c where a.pv_partid = c.pt_id and c.pt_flag =2 and
33a.pv_id in (select e.pv_id from mi_partver e
34where e.pv_id in (select max(b.pv_id) from mi_partver b where e.pv_partid = b.pv_partid)))';
35 /**//*idstr := rtrim(idstr, ',');
36 idstr := ltrim(idstr, ',');
37 if idstr is null then
38 sqlstr := p_sql || ' ORDER BY PV_PRODUCTID,PT_PARTNUMBER';
39 else
40 sqlstr := p_sql || ' and MI_PARTVER.pv_id in (' || idstr || ') ORDER BY PV_PRODUCTID,PT_PARTNUMBER';
41 end if;*/
42 sqlstr := p_sql || s || 'ORDER BY PV_PRODUCTID,PT_PARTNUMBER';
43 open cur_mi_prodmodel for sqlstr;
44 END GETPRODMODELBYSQL;
这里的有效语句是使用查询语句来构造查询，其结果是速度得到了数量级的提高，而且避免了一个隐患就是缓冲区溢出，因为原来的存储过程中定义了几个varchar2(1000)的变量，实际上这个变量的上界是不可以预期的，而缓冲区溢出也是很多初学oracle的程序员经常忽略甚至想不到的问题。
游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。
1.游标的组成
游标包含两个部分：一个是游标结果集、一个是游标位置。
游标结果集：定义该游标得SELECT语句返回的行的集合。游标位置：指向这个结果集某一行的当前指针。
2.游标的分类
游标共有3类：API服务器游标、Transaction-SQL游标和API客户端游标。
其中前两种游标都是运行在服务器上的，所以又叫做服务器游标。
API服务器游标
API服务器游标主要应用在服务上，当客户端的应用程序调用API游标函数时，服务器会对API函数进行处理。使用API函数和方法可以实现如下功能：
（１）打开一个连接。
（２）设置定义游标特征的特性或属性，API自动将游标影射到每个结果集。
（３）执行一个或多个Transaction-SQL语句。
（４）使用API函数或方法提取结果集中的行。
API服务器游标包含以下四种：静态游标、动态游标、只进游标、键集驱动游标（Primary key）
静态游标的完整结果集将打开游标时建立的结果集存储在临时表中，（静态游标始终是只读的）。静态游标具有以下特点：总是按照打开游标时的原样显示结果集；不反映数据库中作的任何修改，也不反映对结果集行的列值所作的更改；不显示打开游标后在数据库中新插入的行；组成结果集的行被其他用户更新，新的数据值不会显示在静态游标中；但是静态游标会显示打开游标以后从数据库中删除的行。
动态游标与静态游标相反，当滚动游标时动态游标反映结果集中的所有更改。结果集中的行数据值、顺序和成员每次提取时都会改变。
只进游标不支持滚动，它只支持游标从头到尾顺序提取数据行。注意：只进游标也反映对结果集所做的所有更改。
键集驱动游标同时具有静态游标和动态游标的特点。当打开游标时，该游标中的成员以及行的顺序是固定的，键集在游标打开时也会存储到临时工作表中，对非键集列的数据值的更改在用户游标滚动的时候可以看见，在游标打开以后对数据库中插入的行是不可见的，除非关闭重新打开游标。
Transaction-SQL游标
该游标是基于Declare Cursor 语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。
在存储过程或触发器中使用Transaction-SQL游标的过程为：
（１）声明Transaction-SQL变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从数据类型隐式转换得到的数据类型。
（２）使用Declare Cursor语句将Transaction-SQL游标与Select语句相关联。还可以利用Declare Cursor定义游标的只读、只进等特性。
（３）使用Open语句执行Select语句填充游标。
（４）使用Fetch Into语句提取单个行，并将每列中得数据移至指定的变量中。注意：其他Transaction-SQL语句可以引用那些变量来访问提取的数据值。Transaction-SQL游标不支持提取行块。
（５）使用Close语句结束游标的使用。注意：关闭游标以后，该游标还是存在，可以使用Open命令打开继续使用，只有调用Deallocate语句才会完全释放。
客户端游标
该游标将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标操作都在客户端的高速缓存中进行。注意：客户端游标只支持只进和静态游标。不支持其他游标。
3.游标的生命周期
游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。
声明游标是为游标指定获取数据时所使用的Select语句，声明游标并不会检索任何数据，它只是为游标指明了相应的Select 语句。
Declare 游标名称 Cursor 参数
声明游标的参数
（１）Local与Global:Local表示游标的作用于仅仅限于其所在的存储过程、触发器以及批处理中、执行完毕以后游标自动释放。Global表示的是该游标作用域是整个会话层。由连接执行的任何存储过程、批处理等都可以引用该游标名称，仅在断开连接时隐性释放。
（２）Forward_only与Scroll:前者表示为只进游标，后者表示为可以随意定位。默认为前者。
（３）Static、Keyset与Dynamic: 第一个表示定义一个游标，其数据存放到一个临时表内，对游标的所有请求都从临时表中应答，因此，对该游标进行提取操作时返回的数据不反映对基表所作的修改，并且该游标不允许修改。Keyset表示的是，当游标打开时，键集驱动游标中行的身份与顺序是固定的，并把其放到临时表中。Dynamic表示的是滚动游标时，动态游标反映对结果集内所有数据的更改。
（４）Read_only 、Scroll_Locks与Optimistic：第一个表示的是只读游标，第二个表示的是在使用的游标结果集数据上放置锁，当行读取到游标中然后对它们进行修改时，数据库将锁定这些行，以保证数据的一致性。Optimistic的含义是游标将数据读取以后，如果这些数据被更新了，则通过游标定位进行的更新与删除操作将不会成功。
标准游标：
Declare MyCursor Cursor
For Select * From Master_Goods
只读游标
Declare MyCusror Cursor
For Select * From Master_Goods
For Read Only
可更新游标
Declare MyCusror Cursor
For Select * From Master_Goods
For UpDate
打开游标使用Open语句用于打开Transaction-SQL服务器游标，执行Open语句的过程中就是按照Select语句进行填充数据，打开游标以后游标位置在第一行。
打开游标
全局游标：Open Global MyCursor 局部游标: Open MyCursor
读取游标数据：在打开游标以后，使用Fetch语句从Transaction-SQL服务器游标中检索特定的一行。使用Fetch操作，可以使游标移动到下一个记录，并将游标返回的每个列得数据分别赋值给声明的本地变量。
Fetch [Next | Prior | First | Last | Absolute n | Relative n ] From MyCursor
Into @GoodsID,@GoodsName
其中:Next表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。默认的读取选项为Next
Prior表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回，并且把游标置于第一行之前。
First表示返回结果集中的第一行，并且将其作为当前行。
Last表示返回结果集中的最后一行，并且将其作为当前行。
Absolute n 如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n为0，则返回当前行。
Relative n 如果n为正数，则返回从当前行开始的第n行，如果n为负,则返回从当前行之前的第n行，如果为0，则返回当前行。
关闭游标调用的是Close语句，方式如下：Close Global MyCursor Close MyCursor
释放游标调用的是Deallocate语句，方法如下：Deallocate Glboal MyCursor Deallocate MyCursor
游标实例:
Declare MyCusror Cursor Scroll
For Select * From Master_Goods Order By GoodsID
Open MyCursor
Fetch next From MyCursor
Into @GoodsCode,@GoodsName
While(@@Fetch_Status = 0)
Begin
Begin
Select @GoodsCode = Convert(Char(20),@GoodsCode)
Select @GoodsName = Convert(Char(20),@GoodsName)
PRINT @GoodsCode + ':' + @GoodsName
End
Fetch next From MyCursor
Into @GoodsCode,@GoodsName
End
Close MyCursor
Deallocate MyCursor
修改当前游标的数据方法如下:
UpDate Master_Goods Set GoodsName = 'yangyang8848' Where Current Of MyCursor;
删除当前游标行数据的方法如下:
Delete From Master_Goods Where Current Of MyCursor
Select @@CURSOR_ROWS 可以得到当前游标中存在的数据行数。注意：此变量为一个连接上的全局变量，因此只对应最后一次打开的游标。
select * from dbo.Products
select * from dbo.Orders
select * from [Order Details]
select * from dbo.Categories Select b.ProductID,datepart(Year,a.OrderDate) as [Year],datepart(Month,a.OrderDate) as [Month],Sum(b.Quantity) as Quantity, c.ProductName,Count(a.OrderID) as Orders from Orders a Inner Join [Order Details] b On a.OrderID = b.OrderID Inner Join Products c On b.ProductID=c.ProductID Group By b.ProductID,a.OrderDate,c.ProductName Order by b.ProductID Asc,a.OrderDate Asc go
--第一题
Alter PROCEDURE USP_Product1 AS BEGIN DECLARE @ID int,@TempID int DECLARE @ProductName NVARCHAR(50) DECLARE @Year NVARCHAR(4) DECLARE @Month NVARCHAR(2) DECLARE @OrderCount int DECLARE @DetailsCount int DECLARE @TotalCount int DECLARE @TotalDetails int DECLARE Cursor_Product CURSOR LOCAL FOR
--STEP1 声明游标 Select b.ProductID,datepart(Year,a.OrderDate) as [Year],datepart(Month,a.OrderDate) as [Month],Sum(b.Quantity) as Quantity, c.ProductName,Count(a.OrderID) as Orders from Orders a Inner Join [Order Details] b On a.OrderID = b.OrderID Inner Join Products c On b.ProductID=c.ProductID Group By b.ProductID,a.OrderDate,c.ProductName Order by b.ProductID Asc,a.OrderDate Asc FOR READ ONLY
CREATE TABLE #ProductDetails (产品编号 INT ,产品名称 NVARCHAR(50),月份 NVARCHAR(10),订单总数 INT,出货总量 INT)
--创建临时表 set @TempID=null --判断是否为同一个商品 set @TotalCount = 0 set @TotalDetails = 0 OPEN Cursor_Product
--STEP2 打开游标 WHILE(1=1) BEGIN
--STEP3 从游标中提取行 FETCH NEXT FROM Cursor_Product INTO @ID,@Year,@Month,@DetailsCount,@ProductName,@OrderCount IF(@@FETCH_STATUS<>0)BREAK
--跳出循环 if @TempID is null
--判断是第一次的话为@TempID赋值 BEGIN set @TempID=@ID END if @TempID<>@ID
--判断是否需要插入小计 BEGIN insert into #ProductDetails Values(null,null,N'小计：',@TotalCount,@TotalDetails) set @TotalCount = 0 set @TotalDetails = 0 set @TempID=@ID Continue
--跳入下次循环 END set @TotalCount = @TotalCount+@OrderCount set @TotalDetails = @TotalDetails+@DetailsCount insert into #ProductDetails Values(@ID,@ProductName,Convert(Nvarchar(50),@Year)+N'年'+Convert(Nvarchar(50),@Month)+N'月',@OrderCount,@DetailsCount) END select * from #ProductDetails CLOSE Cursor_Product
--STEP4 关闭游标 DEALLOCATE Cursor_Product
--STEP5 释放游标 END GO EXEC USP_Product1 -- DROP Proc USP_Product1 GO Select c.ProductName,Count(a.OrderID) as Orders,SUM(c.UnitPrice*b.Quantity) as Price from Orders a Inner Join [Order Details] b On a.OrderID = b.OrderID Inner Join Products c On b.ProductID=c.ProductID Where c.CategoryID=1 Group By c.ProductName Order by c.ProductName Asc Go
--第二题 Alter PROCEDURE USP_Product2 AS BEGIN DECLARE @ID int,@Temp int DECLARE @CategoryName NVARCHAR(50) DECLARE @ProductName NVARCHAR(50) DECLARE @POCount int DECLARE @POPrices money --小计 DECLARE @OrderCount int DECLARE @OrderPrices money --总计 DECLARE @Orders int DECLARE @Prices money
-- 声明游标1 DECLARE Cursor_Categories CURSOR LOCAL FOR select CategoryID,CategoryName from Categories FOR READ ONLY CREATE TABLE #ProductDetails (产品类别 INT ,类别名称 NVARCHAR(50),产品名称 NVARCHAR(50),下单总次数 INT,下单总价格 INT)
--创建临时表 --小计 SET @OrderCount = 0 SET @OrderPrices = 0
--总计 SET @Orders = 0 SET @Prices = 0 OPEN Cursor_Categories
-- 打开游标1 WHILE(1=1) BEGIN
-- 从游标1中提取行 FETCH NEXT FROM Cursor_Categories INTO @ID,@CategoryName IF(@@FETCH_STATUS<>0)BREAK
--跳出循环 DECLARE Cursor_Product CURSOR LOCAL FOR
-- 声明游标2 Select c.ProductName,Count(a.OrderID) as Orders,SUM(c.UnitPrice*b.Quantity) as Price from Orders a Inner Join [Order Details] b On a.OrderID = b.OrderID Inner Join Products c On b.ProductID=c.ProductID Where c.CategoryID=@ID Group By c.ProductName Order by c.ProductName Asc FOR READ ONLY Set @Temp = null OPEN Cursor_Product
-- 打开游标2 WHILE(1=1) BEGIN FETCH NEXT FROM Cursor_Product INTO @ProductName,@POCount,@POPrices IF(@@FETCH_STATUS<>0)BREAK
--跳出循环 if(@Temp is Null) -- 第一次进入 BEGIN SET @Temp=1 SET @OrderCount=@POCount SET @OrderPrices=@POPrices Insert Into #ProductDetails Values(@ID,@CategoryName,@ProductName,@POCount,@POPrices) END Else BEGIN SET @OrderCount= @OrderCount + @POCount SET @OrderPrices= @OrderPrices + @POPrices Insert Into #ProductDetails(产品名称,下单总次数,下单总价格) Values(@ProductName,@POCount,@POPrices) END END Insert Into #ProductDetails(产品名称,下单总次数,下单总价格) Values('小计：',@OrderCount,@OrderPrices)
--累加总计 SET @Orders = @Orders + @OrderCount SET @Prices = @Prices + @OrderPrices CLOSE Cursor_Product
-- 关闭游标2 DEALLOCATE Cursor_Product
-- 释放游标2 END Insert Into #ProductDetails(产品名称,下单总次数,下单总价格) Values('总计：',@Orders,@Prices) select * from #ProductDetails CLOSE Cursor_Categories
-- 关闭游标1 DEALLOCATE Cursor_Categories
-- 释放游标1 END GO EXEC USP_Product2 -- DROP PROC USP_Product2
