Systems Engineering and RDBMS

Rowcounts in DML code

Posted by decipherinfosys on March 23, 2007

Oracle provides SQL%ROWCount to see how many rows are affected by previously executed DML statements. It should be checked immediately after DML statement is fired. Following is the small code snippet to see how many rows are affected by insert and update statement.

Let us create the table first.

CREATE TABLE TEST(COL1 VARCHAR2(30),COL2 DATE);

Now run following PL/SQL block from SQL*Plus prompt to check the number of rows.

SET SERVEROUTPUT ON

DECLARE v_RowCount NUMBER(9);
BEGIN

INSERT INTO TEST(COL1, COL2)
SELECT table_name, sysdate FROM User_tables;
v_RowCount := SQL%RowCount;
dbms_output.put_line(‘Rows Inserted = ‘ || v_RowCount);

UPDATE TEST
SET COL2 = sysdate – 1;

v_RowCount := SQL%RowCount;
dbms_output.put_line(‘Rows Updated = ‘ || v_RowCount);

END;
/

Output will be as shown under. You may see different number as it is based on number of records in the user_tables table.

Rows Inserted = 5
Rows Updated = 5

PL/SQL procedure successfully completed.

MS SQL Server provides the non-deterministic built-in function @@ROWCOUNT to see the number of rows affected by last statement. One thing to notice here is that it could be any valid SQL Statement (select, insert, update, delete).

Following is the small T-SQL block which shows the result of affected rows. First create the table.

CREATE TABLE TEST(COL1 VARCHAR(30),COL2 DATETIME)
GO

Now execute following T-SQL block either from Query Analyzer or from Management Studio to see the outcome.
SET NOCOUNT ON

DECLARE @ROWCOUNT INT

INSERT INTO TEST(COL1, COL2)
SELECT table_name,getDate() FROM INFORMATION_SCHEMA.TABLES
WHERE table_Type = ‘BASE TABLE’
SET @ROWCOUNT = @@ROWCOUNT
PRINT ‘Rows Inserted = ‘ + CAST(@ROWCOUNT AS VARCHAR(10))

UPDATE TEST
SET COL2 = getDate() – 1
SET @ROWCOUNT = @@ROWCOUNT
PRINT ‘Rows Updated = ‘ + CAST(@ROWCOUNT AS VARCHAR(10))

SET @ROWCOUNT = @@ROWCOUNT
PRINT ‘Rows Affected = ‘ + CAST(@ROWCOUNT AS VARCHAR(10))

GO

Following is the output.

Rows Inserted = 108
Rows Updated = 214
Rows Affected = 0

If you notice, in the last result record, it shows that Rows affected are 0. And it is the number of rows affected by PRINT statement as per our T-SQL block. So if we want to check number of rows affected , it should be immediately after DML statement.

DB2 LUW provides GET DIAGNOSTICS statement to provide the information about previously executed statement. Following is the stored procedure to check the number of rows affected by previously executed statement. Make sure that statement terminator is defined as ‘@’. If it is other than ‘@’, then please change the statement terminator. Here also we will create the table first and followed by creation of stored procedure.

CREATE TABLE TEST(COL1 VARCHAR(30), COL2 TIMESTAMP)@

CREATE PROCEDURE TEST_PROC(OUT V_RowCount INT)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN

INSERT INTO TEST(COL1,COL2)
SELECT TABNAME,CURRENT TIMESTAMP
FROM SYSCAT.tables
FETCH FIRST 15 ROWS ONLY;

GET DIAGNOSTICS v_RowCount = ROW_COUNT;

END
@

Now let us call the stored procedure. Since there is only one out parameter defined in the stored procedure, following is the execution syntax followed by result.

CALL TEST_PROC(?)

Value of output parameters
————————–
Parameter Name  : V_ROWCOUNT
Parameter Value : 15

Return Status = 0

Similarly we can use GET DIAGNOSTICS statement for update statements as well.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: