1:
set serveroutput on size 200000
DBMS_OUTPUT.enable
declare ll_Count integer
--SQL Using Count(*) and ROWNUM
SELECT Count(*) INTO :ll_Count FROM table
SELECT Count(*) INTO :ll_Count FROM table
WHERE ROWNUM < 2IF (ll_Count IS NULL) THEN
DBMS_OUTPUT.PUT_LINE ('查询的结果为空!');
RETURN;
ELSE
DBMS_OUTPUT.PUT_LINE ('查询的结果为: '||ll_Count);
END IF;2: use CURSOR
DECLARE
CURSOR c1 IS SELECT Count(*) FROM data_table;
result NUMBER;
BEGIN
IF c1%ISOPEN THEN
if c1>0 then
open c1;
loop
fetch c1 into :result;
exit when c1%notfound;
end loop;
close c1;
end if
end if3:use procedure
create or replace procedure p_check_null
as
CURSOR c1 IS SELECT Count(*) FROM data_table;
rec p_check_null%ROWTYPE;
status NUMERIC;
BEGIN
set serveroutput on size 2000000;
DBMS_OUTPUT.enable;
OPEN c1;
LOOP -- recompile each stored program object
FETCH c1 INTO rec;
EXIT WHEN c1%NOTFOUND;DBMS_OUTPUT.put_line('查询的结果为: '||rec);
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
BEGIN
status := SQLCODE;
DBMS_OUTPUT.put_line(' FAILED with ' || SQLERRM(status));
IF (c1%ISOPEN) THEN
CLOSE c1;
END IF;
EXCEPTION WHEN OTHERS THEN
NULL; -- do nothing
END;
END;
/
set serveroutput on size 200000
DBMS_OUTPUT.enable
declare ll_Count integer
--SQL Using Count(*) and ROWNUM
SELECT Count(*) INTO :ll_Count FROM table
SELECT Count(*) INTO :ll_Count FROM table
WHERE ROWNUM < 2IF (ll_Count IS NULL) THEN
DBMS_OUTPUT.PUT_LINE ('查询的结果为空!');
RETURN;
ELSE
DBMS_OUTPUT.PUT_LINE ('查询的结果为: '||ll_Count);
END IF;2: use CURSOR
DECLARE
CURSOR c1 IS SELECT Count(*) FROM data_table;
result NUMBER;
BEGIN
IF c1%ISOPEN THEN
if c1>0 then
open c1;
loop
fetch c1 into :result;
exit when c1%notfound;
end loop;
close c1;
end if
end if3:use procedure
create or replace procedure p_check_null
as
CURSOR c1 IS SELECT Count(*) FROM data_table;
rec p_check_null%ROWTYPE;
status NUMERIC;
BEGIN
set serveroutput on size 2000000;
DBMS_OUTPUT.enable;
OPEN c1;
LOOP -- recompile each stored program object
FETCH c1 INTO rec;
EXIT WHEN c1%NOTFOUND;DBMS_OUTPUT.put_line('查询的结果为: '||rec);
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
BEGIN
status := SQLCODE;
DBMS_OUTPUT.put_line(' FAILED with ' || SQLERRM(status));
IF (c1%ISOPEN) THEN
CLOSE c1;
END IF;
EXCEPTION WHEN OTHERS THEN
NULL; -- do nothing
END;
END;
/
判断 a 的值是否为 0
select RoleID Into PExistFlag from gh_WF_Role where RoleName = PRoleName;
if PExistFlag Is Null Then
/*如果不存在则保存到数据库中并返回状态值1*/
insert into gh_WF_Role(roleid,RoleName) Values (CreateID,PRoleName);
End If;