Implicit Cursors
PL/SQL
implicitly declares a cursor for all SQL data manipulation statements
on a set of rows, including queries that return only one row
For queries that return more than one row, you can explicitly declare a cursor to process the rows individually
We can refer to the most recent implicit cursor by the name SQL
Implicit Cursors - Attributes
%FOUND Attribute: used to check whether a DML Statement has affected one or many rows
Until the DML or SELECT statement is executed, it yields NULL
Yields TRUE if the DML affects one or more rows, or a SELECT INTO statement returns one or more rows.
Otherwise yields FALSE.
%ISOPEN Attribute: always FALSE for Implicit Cursors
Yields TRUE if the cursor is open else returns FALSE.
Oracle
closes the SQL cursor automatically after executing its associated SQL
statement. As a result, %ISOPEN always yields FALSE.
%NOTFOUND Attribute: used to check whether a DML statement has failed to change rows
%NOTFOUND is the logical opposite of %FOUND
Yields TRUE if the DML statement affects no rows, or a SELECT INTO statement returns no rows
Otherwise it yields FALSE
%ROWCOUNT Attribute: used to find out how many rows are affected so far
%ROWCOUNT yields the number of rows affected by the DML statement, or returned by a SELECT INTO statement
Yields 0 if the DML statement affected no rows, or a SELECT INTO statement returned no rows
Implicit Cursor: Example 1
Using SQL%ROWCOUNT
DECLARE
v_tot_rows NUMBER (3);
BEGIN
DELETE FROM emp
WHERE deptno = 10;
/* PL/SQL will use an implicit cursor to process the above statement */
v_tot_rows := SQL%ROWCOUNT;
/* Implicit Attribute %ROWCOUNT is used to find the number of rows affected by the DELETE command */
DBMS_OUTPUT.put_line('Total records deleted : ' || v_tot_rows);
END;
Implicit Cursor: Example 2
Using SQL%FOUND
DECLARE
v_empno NUMBER (4) := &eno;
BEGIN
DELETE FROM emp
WHERE empno = v_empno;
IF SQL%FOUND
THEN
/* Making use of %FOUND to find out whether at least one row was affected by the DELETE statement */
DBMS_OUTPUT.put_line('Delete successful');
END IF;
END;
Hi Dayakar,
ReplyDeleteAllow me to show my gratitude bloggers. You guys are like unicorns. Never seen but always spreading magic. Your content is yummy. So satisfied.
I've tried installing Patch above for SQL Server 2008 R2 in a Windows Server 2012 R2 and I've received the following message when I just start the installation:
Unable to generate a temporary class (result=1). error CS0016: Could not write to output file 'c:\Install_TEMP\AppData\Local\Temp\2\ny0nmqmd.dll' -- 'Could not execute CVTRES.EXE.'
Followed by:
CVTRES.EXE
The program can't start
because MSVCR80.dll is missing from your computer. Try reinstalling the program to fix this problem.
Super likes !!! for this amazing post. I thinks everyone should bookmark this.
Obrigado,
Irene Hynes
Hello Dayakar,
ReplyDeleteYour writing shines! There is no room for gibberish here clearly you have explained about Oracle PL/SQL Implicit Cursor Attributes. Keep writing!
I have a number of tables with partitions by month.
Each database logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.
My first script where I input the actual table works fine - as in it will drop the partitions which meet the criteria (see below)
Code (SQL):
DECLARE
v_sql varchar2(500);
v_date DATE;
v_partition_name user_tab_partitions.partition_name%TYPE;
v_high_value user_tab_partitions.high_value%TYPE;
CURSOR c1 IS SELECT PARTITION_NAME, HIGH_VALUE
FROM user_tab_partitions WHERE TABLE_NAME='TEST_YFS_BAT_LOC_H' AND PARTITION_NAME!='P0';
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_partition_name, v_high_value;
EXIT WHEN c1%NOTFOUND;
v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');
IF v_date < (sysdate - 30) THEN
v_sql := 'alter table TEST_YFS_BAT_LOC_H drop partition ' || v_partition_name;
EXECUTE immediate v_sql;
END IF;
END LOOP;
CLOSE c1;
END;
/
But I want to be able to drop all partitions from all tables belonging to this schema. So how can I do something similar which goes through all the tables and partitions and does the same thing?
I am executing this script as the schema owner with 100+ tables
But great job man, do keep posted with the new updates.
Kind Regards,
Kevin
Hey,
ReplyDeleteI learnt so much in such little time about #topic. Even a toddler could become smart reading of your amazing articles.
I have requirement like
select COL1,Col2 from A
minus
Select COL1,COL2 from B;
both tables have 50+ millions record so I want to automate a job which will execute this query for 1millions records for one time and then again 1 millions for second time...like loop and store output in record type.
But nice Article Mate! Great Information! Keep up the good work!
Kind Regards,