使用游标实现 declare cursor A IS select count(1),t.xx from A t group by t.xx; V_COUNT NUMBER; V_XX VARCHAR2(300); BEGIN OPEN A; LOOP FETCH A INTO V_COUNT,V_XX; EXIT WHEN A%NOTFOUND; --在这增加语句循环处理每条记录 END LOOP; CLOSE A; END;
1楼答案不太准确,会多出一条记录,我改了一下,是对scott用户下emp表的操作,直接打印信息,如下: create or replace procedure sp_emp_store(U_count out number,u_xx out varchar2) is cursor A IS select count(1),e.deptno from emp e group by e.deptno; BEGIN OPEN A; LOOP FETCH A INTO U_count,u_xx; if A%found then DBMS_OUTPUT.PUT_LINE(u_xx||'有'||U_count); end if; exit when A %notfound;--在这增加语句循环处理每条记录 END LOOP; CLOSE A; END;
代码: create or replace procedure sp_emp_store(U_count out number,u_xx out varchar2) is cursor A IS select count(1),e.deptno from emp e group by e.deptno; BEGIN OPEN A; LOOP FETCH A INTO U_count,u_xx; if A%found then DBMS_OUTPUT.PUT_LINE(u_xx||'有'||U_count); end if; exit when A %notfound;--在这增加语句循环处理每条记录 END LOOP; CLOSE A; END; 结果: 30有5 20有5 10有2 代码:create or replace procedure sp_emp_store(U_count out number,u_xx out varchar2) is cursor A IS select count(1),e.deptno from emp e group by e.deptno; BEGIN OPEN A; LOOP FETCH A INTO U_count,u_xx; DBMS_OUTPUT.PUT_LINE(u_xx||'有'||U_count); exit when A %notfound;--在这增加语句循环处理每条记录 END LOOP; CLOSE A; END; 结果:30有5 20有5 10有2 10有2这是表中数据:你那是先执行后判断,我的在中间添加了一个判断
declare
cursor A IS select count(1),t.xx from A t group by t.xx;
V_COUNT NUMBER;
V_XX VARCHAR2(300);
BEGIN
OPEN A;
LOOP
FETCH A INTO V_COUNT,V_XX;
EXIT WHEN A%NOTFOUND;
--在这增加语句循环处理每条记录
END LOOP;
CLOSE A;
END;
create or replace procedure sp_emp_store(U_count out number,u_xx out varchar2)
is
cursor A IS select count(1),e.deptno from emp e group by e.deptno;
BEGIN
OPEN A;
LOOP
FETCH A INTO U_count,u_xx;
if A%found then
DBMS_OUTPUT.PUT_LINE(u_xx||'有'||U_count);
end if;
exit when A %notfound;--在这增加语句循环处理每条记录
END LOOP;
CLOSE A;
END;
注意我exit when和操作的前后顺序,我是先判断的exit when再执行的操作
create or replace procedure sp_emp_store(U_count out number,u_xx out varchar2)
is
cursor A IS select count(1),e.deptno from emp e group by e.deptno;
BEGIN
OPEN A;
LOOP
FETCH A INTO U_count,u_xx;
if A%found then
DBMS_OUTPUT.PUT_LINE(u_xx||'有'||U_count);
end if;
exit when A %notfound;--在这增加语句循环处理每条记录
END LOOP;
CLOSE A;
END;
结果:
30有5
20有5
10有2
代码:create or replace procedure sp_emp_store(U_count out number,u_xx out varchar2)
is
cursor A IS select count(1),e.deptno from emp e group by e.deptno;
BEGIN
OPEN A;
LOOP
FETCH A INTO U_count,u_xx;
DBMS_OUTPUT.PUT_LINE(u_xx||'有'||U_count);
exit when A %notfound;--在这增加语句循环处理每条记录
END LOOP;
CLOSE A;
END;
结果:30有5
20有5
10有2
10有2这是表中数据:你那是先执行后判断,我的在中间添加了一个判断