3,491
社区成员
发帖
与我相关
我的任务
分享
declare num number;
begin
update emp set empno=123 where empno=111;
if sql%rowcount=0 then
dbms_output.put_line('不存在记录');
else
dbms_output.put_line('存在记录');
end if;
end;
declare num number;
begin
select count(*) into num from emp where empno=7999;
if sql%rowcount=0 then
dbms_output.put_line('不存在记录');
dbms_output.put_line(to_char(sql%rowcount));
else
dbms_output.put_line('存在记录');
dbms_output.put_line(to_char(sql%rowcount));
end if;
end;
/
-- 因为上面查询:哪怕表中没有符合条件的记录也会返回类似如下的结果:
scott@SZTYORA> select count(*) from emp where empno=7999;
COUNT(*)
----------
0
已选择 1 行。
-- 上面也是1条记录,所以:sql%rowcount当然就为1啦!
if SQL%ROWCOUNT=0 THEN
ret_code:=0;
elsif SQL%ROWCOUNT=1 then
ret_code:=1;
else
ret_code:=SQL%ROWCOUNT;
end if;
-- 怎么会呢?
eygle@SZTYORA> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO LOC
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ---------- ------------------------
123 luoyoumou MANAGER 7499 2011-04-01 17:57:43 4000 400 30 BEIJING
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 CHICAGO
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 CHICAGO
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 400 30 CHICAGO
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 CHICAGO
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 CHICAGO
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 DALLAS
已选择7行。
eygle@SZTYORA> declare num number;
2 begin
3 update emp set empno=123 where empno=8889;
4 if sql%rowcount=0 then
5 dbms_output.put_line('不存在记录');
6 dbms_output.put_line(to_char(sql%rowcount));
7 else
8 dbms_output.put_line('存在记录');
9 dbms_output.put_line(to_char(sql%rowcount));
10 end if;
11 end;
12 /
不存在记录
0
PL/SQL 过程已成功完成。
eygle@SZTYORA> declare num number;
2 begin
3 update emp set empno=111 where empno=123;
4 if sql%rowcount=0 then
5 dbms_output.put_line('不存在记录');
6 dbms_output.put_line(to_char(sql%rowcount));
7 else
8 dbms_output.put_line('存在记录');
9 dbms_output.put_line(to_char(sql%rowcount));
10 end if;
11 end;
12 /
存在记录
1
PL/SQL 过程已成功完成。
eygle@SZTYORA> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO LOC
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ---------- --------------------------
8888 luoyoumou MANAGER 7499 2011-04-01 17:57:43 4000 400 30 BEIJING
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 CHICAGO
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 CHICAGO
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 400 30 CHICAGO
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 CHICAGO
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 CHICAGO
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 DALLAS
已选择7行。
eygle@SZTYORA> declare num number;
2 begin
3 update emp set empno=123 where empno=8888;
4 if sql%rowcount=0 then
5 dbms_output.put_line('不存在记录');
6 else
7 dbms_output.put_line('存在记录');
8 end if;
9 end;
10 /
存在记录
PL/SQL 过程已成功完成。
eygle@SZTYORA> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO LOC
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ---------- --------------------------
123 luoyoumou MANAGER 7499 2011-04-01 17:57:43 4000 400 30 BEIJING
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 CHICAGO
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 CHICAGO
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 400 30 CHICAGO
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 CHICAGO
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 CHICAGO
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 DALLAS
已选择7行。