Use pl/sql, cursor and for loop to count table

  
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );

Table created.


SQL> INSERT INTO EMP VALUES(2, 'Jack', 'Tester', 6,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(3, 'Wil', 'Tester', 6,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(4, 'Jane', 'Designer', 9,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(5, 'Mary', 'Tester', 6,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(7, 'Chris', 'Designer', 9,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(8, 'Smart', 'Helper', 4,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES(9, 'Peter', 'Manager', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES(10, 'Take', 'Tester', 6,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES(13, 'Fake', 'Helper', 4,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL>
SQL>
SQL> declare
  2   cnt pls_integer := 0;
  3   begin
  4       for i in (select ename from emp) loop
  5           cnt := cnt + 1;
  6       end loop;
  7       dbms_output.put_line(cnt);
  8   end;
  9  /
9

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

Related examples in the same category

1.	Create a cursor in for statement
2.	The %FOUND, %NOTFOUND, and %ROWCOUNT cursor attributes can be used to guide a loop
3.	A function that uses a CURSOR FOR Loop
4.	Use 'for loop' to loop through the cursor
5.	A subquery in the FROM clause of a cursor within a cursor FOR loop
6.	Assign value from cursor to a table collection by index
7.	Calculation based on cursor value
8.	For each row in a cursor
9.	Output value in cursor one by one
10.	Simple Loop and fetch cursor

Use pl/sql, cursor and for loop to count table : CURSOR FOR Loop « Cursor « Oracle PL / SQL

Related examples in the same category