1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PLSQL records

Discussion in 'SQL PL/SQL' started by bangla123, Jul 22, 2012.

  1. bangla123

    bangla123 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I am trying this below in oracle 10g express. Could you please tell me how I can select data from the table?

    CREATE TYPE Worker AS OBJECT (name VARCHAR2(25), dept VARCHAR2(15));
    /
    CREATE TABLE teams (team_no NUMBER, team_member Worker);

    insert into teams
    values ( 5 , team_member ( worker('a' , '2')))

    DECLARE
    team_rec teams%ROWTYPE;
    BEGIN
    team_rec.team_no := 5;
    team_rec.team_member := Worker('Paul Ocker', 'Accounting');
    UPDATE teams SET ROW = team_rec;
    END;

    select team_no, s.name
    from teams , object(teams.team_member)s


    ORA-00933: SQL command not properly ended


    Thanks.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're going about this the wrong way; see the example below:

    Code (SQL):
    SQL> COLUMN team_member format a50
    SQL>
    SQL> CREATE TYPE Worker AS OBJECT (name VARCHAR2(25), dept VARCHAR2(15));
    2 /
     
    TYPE created.
     
    SQL> CREATE TABLE teams (team_no NUMBER, team_member Worker);
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
    2 INTO teams
    3 VALUES ( 5 , worker('a' , '2'))
    4 INTO teams
    5 VALUES ( 6 , worker('b' , '2'))
    6 INTO teams
    7 VALUES ( 7 , worker('c' , '2'))
    8 INTO teams
    9 VALUES ( 8 , worker('d' , '2'))
    10 INTO teams
    11 VALUES ( 9 , worker('e' , '2'))
    12 INTO teams
    13 VALUES ( 4 , worker('f' , '2'))
    14 INTO teams
    15 VALUES ( 3 , worker('g' , '2'))
    16 SELECT * FROM dual;
     
    7 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> DECLARE
    2 team_rec teams%ROWTYPE;
    3 BEGIN
    4 FOR team_rec IN (SELECT * FROM teams) loop
    5 IF team_rec.team_no = 5 THEN
    6 team_rec.team_member := Worker('Paul Ocker', 'Accounting');
    7 UPDATE teams SET ROW = team_rec WHERE team_no = 5;
    8 elsif team_rec.team_no = 8 THEN
    9 team_rec.team_member := Worker('Rambo Smackdoodle', 'Collections');
    10 UPDATE teams SET ROW = team_rec WHERE team_no = 8;
    11 elsif team_rec.team_no = 3 THEN
    12 team_rec.team_member := Worker('Quato Flang', 'Sales');
    13 UPDATE teams SET ROW = team_rec WHERE team_no = 3;
    14 ELSE
    15 team_rec.team_member := Worker('Greech Hongnaut', 'Administration');
    16 UPDATE teams SET ROW = team_rec WHERE team_no NOT IN (3,5,8);
    17 END IF;
    18 END loop;
    19 END;
    20 /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT team_no, team_member team_mbr FROM teams;
     
    TEAM_NO TEAM_MBR(NAME, DEPT)
    ---------- --------------------------------------------------
    5 WORKER('Paul Ocker', 'Accounting')
    4 WORKER('Greech Hongnaut', 'Administration')
    4 WORKER('Greech Hongnaut', 'Administration')
    8 WORKER('Rambo Smackdoodle', 'Collections')
    4 WORKER('Greech Hongnaut', 'Administration')
    4 WORKER('Greech Hongnaut', 'Administration')
    3 WORKER('Quato Flang', 'Sales')
     
    7 ROWS selected.
     
    SQL>
    SQL> SELECT team_member FROM teams WHERE team_no = 5;
     
    TEAM_MEMBER(NAME, DEPT)
    --------------------------------------------------
    WORKER('Paul Ocker', 'Accounting')
     
    SQL>
    SQL> SELECT team_member FROM teams WHERE team_no IN (3,5,8);
     
    TEAM_MEMBER(NAME, DEPT)
    --------------------------------------------------
    WORKER('Paul Ocker', 'Accounting')
    WORKER('Rambo Smackdoodle', 'Collections')
    WORKER('Quato Flang', 'Sales')
     
    SQL>
    SQL> SELECT team_member FROM teams WHERE team_no NOT IN (3,5,8);
     
    TEAM_MEMBER(NAME, DEPT)
    --------------------------------------------------
    WORKER('Greech Hongnaut', 'Administration')
    WORKER('Greech Hongnaut', 'Administration')
    WORKER('Greech Hongnaut', 'Administration')
    WORKER('Greech Hongnaut', 'Administration')
     
    SQL>
    Notice that the update statements need conditions to isolate which rows in the table need updating otherwise the entire table gets updated with each update statement and you lose any changes from prior updates (not to mention that the prior updates are also wrongly updating the entire table). Notice also that for the rest of the teams the team numbers are lost as they are all set to 4 (the first team_no value that meets the condition in the if and subsequent update). Changing the code:

    Code (SQL):
    SQL> DECLARE
      2  team_rec teams%ROWTYPE;
      3  BEGIN
      4          FOR team_rec IN (SELECT * FROM teams) loop
      5                  IF team_rec.team_no = 5 THEN
      6                          team_rec.team_member := Worker('Paul Ocker', 'Accounting');
      7                          UPDATE teams SET ROW = team_rec WHERE team_no = 5;
      8                  elsif team_rec.team_no = 8 THEN
      9                          team_rec.team_member := Worker('Rambo Smackdoodle', 'Collections');
     10                          UPDATE teams SET ROW = team_rec WHERE team_no = 8;
     11                  elsif team_rec.team_no = 3 THEN
     12                          team_rec.team_member := Worker('Quato Flang', 'Sales');
     13                          UPDATE teams SET ROW = team_rec WHERE team_no = 3;
     14                  elsif team_rec.team_no = 4 THEN
     15                          team_rec.team_member := Worker('Greech Hongnaut', 'Administration');
     16                          UPDATE teams SET ROW = team_rec WHERE team_no = 4;
     17                  elsif team_rec.team_no = 6 THEN
     18                          team_rec.team_member := Worker('Greech Hongnaut', 'Administration');
     19                          UPDATE teams SET ROW = team_rec WHERE team_no = 6;
     20                  elsif team_rec.team_no = 7 THEN
     21                          team_rec.team_member := Worker('Greech Hongnaut', 'Administration');
     22                          UPDATE teams SET ROW = team_rec WHERE team_no = 7;
     23                  elsif team_rec.team_no = 9 THEN
     24                          team_rec.team_member := Worker('Greech Hongnaut', 'Administration');
     25                          UPDATE teams SET ROW = team_rec WHERE team_no = 9;
     26                  END IF;
     27          END loop;
     28  END;
     29  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT team_no, team_member team_mbr FROM teams;
     
       TEAM_NO TEAM_MBR(NAME, DEPT)
    ---------- --------------------------------------------------
             5 WORKER('Paul Ocker', 'Accounting')
             6 WORKER('Greech Hongnaut', 'Administration')
             7 WORKER('Greech Hongnaut', 'Administration')
             8 WORKER('Rambo Smackdoodle', 'Collections')
             9 WORKER('Greech Hongnaut', 'Administration')
             4 WORKER('Greech Hongnaut', 'Administration')
             3 WORKER('Quato Flang', 'Sales')
     
    7 ROWS selected.
     
    SQL>
    SQL> SELECT team_member FROM teams WHERE team_no = 5;
     
    TEAM_MEMBER(NAME, DEPT)
    --------------------------------------------------
    WORKER('Paul Ocker', 'Accounting')
     
    SQL>
    SQL> SELECT team_member FROM teams WHERE team_no IN (3,5,8);
     
    TEAM_MEMBER(NAME, DEPT)
    --------------------------------------------------
    WORKER('Paul Ocker', 'Accounting')
    WORKER('Rambo Smackdoodle', 'Collections')
    WORKER('Quato Flang', 'Sales')
     
    SQL>
    SQL> SELECT team_member FROM teams WHERE team_no NOT IN (3,5,8);
     
    TEAM_MEMBER(NAME, DEPT)
    --------------------------------------------------
    WORKER('Greech Hongnaut', 'Administration')
    WORKER('Greech Hongnaut', 'Administration')
    WORKER('Greech Hongnaut', 'Administration')
    WORKER('Greech Hongnaut', 'Administration')
     
    SQL>