Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
SQL%FOUND
Were any rows affected
Returns NULL if not statement has been run, TRUE if a SELECT statement returned one or more row: Otherwise FALSE.
conn uwclass/uwclass@pdbdev
set serveroutput on
DECLARE
x servers.srvr_id%TYPE;
BEGIN
SELECT srvr_id
INTO x
FROM servers
WHERE srvr_id > 100
AND rownum = 1;
IF SQL%FOUND THEN
dbms_output.put_line('Found Server ID: ' || TO_CHAR(x));
END IF;
END;
/
SQL%ISOPEN
Is the cursor open
Always returns FALSE with internal cursors but useful with explicitly declared cursors
conn uwclass/uwclass@pdbdev
DECLARE
CURSOR iCur IS
SELECT srvr_id
FROM servers;
BEGIN
IF iCur%ISOPEN THEN
dbms_output.put_line('1: ' || 'iCur Is Open');
ELSE
dbms_output.put_line('1: ' || 'iCur Is Not Open');
END IF;
OPEN iCur;
IF iCur%ISOPEN THEN
dbms_output.put_line('2: ' || 'iCur Is Open');
ELSE
dbms_output.put_line('2: ' || 'iCur Is Not Open');
END IF;
END;
/
SQL%NOTFOUND
Were no rows affected
conn uwclass/uwclass@pdbdev
set serveroutput on
BEGIN
DELETE FROM servers
WHERE srvr_id < 0;
IF SQL%NOTFOUND THEN
dbms_output.put_line('No Rows Found That Match The Filter Condition');
END IF;
END;
/
SQL%ROWCOUNT
Counter for the number of rows affected by a statement
conn uwclass/uwclass@pdbdev
CREATE TABLE t (
testcol VARCHAR2(10));
INSERT INTO t (testcol) VALUES ('ABCDEFG');
INSERT INTO t (testcol) VALUES ('ABCXEFG');
INSERT INTO t (testcol) VALUES ('ABCYEFG');
INSERT INTO t (testcol) VALUES ('ABCDEFG');
INSERT INTO t (testcol) VALUES ('ABCZEFG');
COMMIT;
SELECT * FROM t;
BEGIN
UPDATE t
SET testcol = 'UPDATED'
WHERE testcol LIKE '%D%';
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO t
(testcol)
VALUES
('12345567');
END IF;
END;
/
SELECT * FROM t;
BEGIN
UPDATE t
SET testcol = 'UPDATED'
WHERE testcol LIKE '%S%';
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO t
(testcol)
VALUES
('0000000');
END IF;
END;
/