CODEWITHSUNDEEP
sqloracle-databaseplsql
Matt
Matt

Reputation: 77

Trying to print via a table of records

I'm trying to print using a table of records. The original table looks as follows:

 CREATE TABLE dept1
 ( DEPTNO NUMBER(3) PRIMARY KEY,
 DNAME VARCHAR2(20),
 LOC VARCHAR2(20)
  );

Dept = {(10, 'ACCOUNTING', 'NEW YORK'),
 (20, 'RESEARCH', 'DALLAS'),
 (30, 'SALES', 'CHICAGO'),
 (40, 'OPERATIONS', 'DALLAS'),
 (50, 'MARKETING', 'BOSTON')}

What I think I'm doing with this code is creating a procedure print_deptlist that will print all the records from the record deptlist that I created: We're required to use that method

CREATE OR REPLACE PROCEDURE print_deptlist IS
deptlist dept1%rowtype;
Begin
  Select * into deptlist from dept1;
  DBMS_OUTPUT.PUT_LINE(DEPTLIST.deptno || ',' || deptlist.dname  || ',' || deptlist.loc);
end;
/

See above...

I'm expecting results that looks lists the column names with the records below:

Upvotes: 1

Views: 37

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65353

You can use such a loop statement through

BULK COLLECT which is SELECT statements that retrieve multiple rows with a single fetch, also improving the speed of data retrieval 

SQL> SET SERVEROUTPUT ON 

SQL> CREATE OR REPLACE PROCEDURE print_deptlist IS
    TYPE tab_dept IS TABLE OF dept1%rowtype;
    deptlist tab_dept;
BEGIN
  SELECT * BULK COLLECT INTO deptlist FROM dept1;
   FOR i IN 1..deptlist.count
   LOOP
    dbms_output.put_line(deptlist(i).deptno || ',' || deptlist(i).dname 
                                            || ',' || deptlist(i).loc);
   END LOOP;
END;
/

Demo

Upvotes: 1

Related Questions