NEXT & PRIOR Table Attributes : Table of Type « PL SQL « Oracle PL / SQL






NEXT & PRIOR Table Attributes

    
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );

Table created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, 'Mar', 'Wells','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, 'Man', 'Kyte','Economics', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, 'Pat', 'Poll','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, 'Tim', 'Viper','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, 'Barbara', 'Blues','Economics', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, 'David', 'Large','Music', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, 'Rose', 'Bond','Music', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);

1 row created.

SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL>
SQL> DECLARE
  2    TYPE t_MajorTable IS TABLE OF lecturer.major%TYPE
  3      INDEX BY BINARY_INTEGER;
  4    v_Majors t_MajorTable;
  5    v_Index  BINARY_INTEGER;
  6  BEGIN
  7    v_Majors(-7) := 'Computer Science';
  8    v_Majors(4) := 'History';
  9    v_Majors(5) := 'Economics';
 10
 11    v_Index := v_Majors.FIRST;
 12    LOOP
 13      INSERT INTO MyTable (num_col, char_col)
 14        VALUES (v_Index, v_Majors(v_Index));
 15      EXIT WHEN v_Index = v_Majors.LAST;
 16      v_Index := v_Majors.NEXT(v_Index);
 17    END LOOP;
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from MyTable;

   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
        -7 Computer Science
         4 History
         5 Economics

SQL>
SQL> drop table lecturer;

Table dropped.

SQL>
SQL> drop table MyTable;

Table dropped.

SQL>
SQL>

   
    
    
  








Related examples in the same category

1.Create a function to convert string type variable to date type variable
2.Fetch a bulk into a table structure
3.Define a nested table type for each column
4.Extend once, outside the loop for better performance
5.A package to manage a list of employees
6.Fill table of custom type and use it in for loop to insert
7.Table of custome type indexed by BINARY_INTEGER
8.Reference type attribute through index
9.Use for loop to fill a table collection
10.Select bulk collect into
11.Use for loop to insert value to table collection and then use table collection in another insert statement
12.The EXISTS Table Attribute
13.FIRST & LAST Table Attributes
14.Uses the COUNT method to display the number of rows contained in a table collection
15.How to do a bulk collect into a nested table.
16.How to do a bulk collect into an associative array
17.Table collection indexed by column type