Hi everyone. Recently I've approached the topic that you read in the title of this thread and I tried really hard to understand it but I fail at solving this exercise here:
CREATE TABLE t2 (testcol NUMBER);
CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS
i INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM t2;
RETURN i;
END howmanyrows;
/
CREATE OR REPLACE PROCEDURE testproc IS
a INTEGER;
b INTEGER;
c INTEGER;
BEGIN
SELECT COUNT(*)
INTO a
FROM t2;
INSERT INTO t2 VALUES (1);
COMMIT;
INSERT INTO t2 VALUES (2);
INSERT INTO t2 VALUES (3);
b := howmanyrows;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5);
INSERT INTO t2 VALUES (6);
COMMIT;
SELECT COUNT(*)
INTO c
FROM t2;
dbms_output.put_line(a);
dbms_output.put_line(b);
dbms_output.put_line(c);
END testproc;
/
exec testproc;
If I run the exec tesproc what I get, at first is "0,3,6" and I really am struggling to understand why. Can someone gently explain step by step what this procedure is doing and why I get these results by running the execute procedure? Thanks in advance