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!

How to switch columns for every call of a procedure.,

Discussion in 'SQL PL/SQL' started by Vicky, Mar 20, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hii.,.

    I gonna update a table having only 1 record through a procedure.,

    During the first call, i should put the entry in the table.,

    Once the entry comes., I should update the table.,

    For every update, i wanna switch the column for IP.,

    EX: There are 5 columns for IP, like IP1,IP2,IP3,IP4,IP5.,

    first time, Ip value should be updated in IP1..
    Like wise, it should update the other columns during successive calls.,

    After updating IP5, it should again start updating from IP1.,


    for ex:

    desc emp
    Name Null Type
    ----------- ---- -------------
    EMP_ID NUMBER
    IP1 VARCHAR2(10)
    IP2 VARCHAR2(10)
    IP3 VARCHAR2(10)
    IP4 VARCHAR2(10)
    IP5 VARCHAR2(10)
    LAST_UPDATED_IP VARCHAR2(10)

    Any Idea Guys,.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The first problem I see is how the IP columns are defined; an IP address can be 15 characters long (xxx.xxx.xxx.xxx) so the columns need to be able to accept an address that long and they can't. The rest of the problem is fairly simple to address:


    Code (SQL):

    SQL> CREATE TABLE emp_ip_add(
      2          emp_id  NUMBER NOT NULL,
      3          ip1     varchar2(15),
      4          ip2     varchar2(15),
      5          ip3     varchar2(15),
      6          ip4     varchar2(15),
      7          ip5     varchar2(15),
      8          last_updated_ip varchar2(15));


    TABLE created.


    SQL>
    SQL> CREATE OR REPLACE PROCEDURE emp_ip_update(p_empid IN NUMBER, p_ip varchar2)
      2  IS
      3          vempct  NUMBER:=NULL;
      4          vip1    varchar2(15):=NULL;
      5          vip2    varchar2(15):=NULL;
      6          vip3    varchar2(15):=NULL;
      7          vip4    varchar2(15):=NULL;
      8          vip5    varchar2(15):=NULL;
      9          vlastip varchar2(15):=NULL;
     10  BEGIN
     11          SELECT COUNT(*) INTO vempct FROM emp_ip_add WHERE emp_id = p_empid;
     12
     13          IF vempct = 0 THEN
     14                  INSERT INTO emp_ip_add(emp_id, ip1, last_updated_ip)
     15                  VALUES (p_empid, p_ip, p_ip);
     16          ELSE
     17                  SELECT ip1,ip2,ip3,ip4,ip5,last_updated_ip INTO vip1,vip2,vip3,vip4,vip5,vlastip FROM emp_ip_add WHERE emp_id = p_empid;
     18                  IF vip2 IS NULL OR vip1 = vlastip THEN
     19                          UPDATE emp_ip_add
     20                          SET ip2 = p_ip, last_updated_ip = p_ip
     21                          WHERE emp_id = p_empid;
     22                  elsif vip3 IS NULL OR vip2 = vlastip THEN
     23                          UPDATE emp_ip_add
     24                          SET ip3 = p_ip, last_updated_ip = p_ip
     25                          WHERE emp_id = p_empid;
     26                  elsif vip4 IS NULL OR vip3 = vlastip THEN
     27                          UPDATE emp_ip_add
     28                          SET ip4 = p_ip, last_updated_ip = p_ip
     29                          WHERE emp_id = p_empid;
     30                  elsif vip5 IS NULL OR vip4 = vlastip THEN
     31                          UPDATE emp_ip_add
     32                          SET ip5 = p_ip, last_updated_ip = p_ip
     33                          WHERE emp_id = p_empid;
     34                  elsif vip5 = vlastip THEN
     35                          UPDATE emp_ip_add
     36                          SET ip1 = p_ip, last_updated_ip = p_ip
     37                          WHERE emp_id = p_empid;
     38                  END IF;
     39          END IF;
     40
     41          commit;
     42  END;
     43  /


    PROCEDURE created.


    SQL>
    SQL> SHOW errors PROCEDURE emp_ip_update;
    No errors.
    SQL>
    SQL> EXEC emp_ip_update(1000, '100.150.200.249')


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM emp_ip_add;


        EMP_ID IP1             IP2             IP3             IP4             IP5             LAST_UPDATED_IP
    ---------- --------------- --------------- --------------- --------------- --------------- ---------------
          1000 100.150.200.249                                                                 100.150.200.249


    SQL>
    SQL> EXEC emp_ip_update(1000, '100.150.200.250')


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM emp_ip_add;


        EMP_ID IP1             IP2             IP3             IP4             IP5             LAST_UPDATED_IP
    ---------- --------------- --------------- --------------- --------------- --------------- ---------------
          1000 100.150.200.249 100.150.200.250                                                 100.150.200.250


    SQL>
    SQL> EXEC emp_ip_update(1000, '100.150.200.251')


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM emp_ip_add;


        EMP_ID IP1             IP2             IP3             IP4             IP5             LAST_UPDATED_IP
    ---------- --------------- --------------- --------------- --------------- --------------- ---------------
          1000 100.150.200.249 100.150.200.250 100.150.200.251                                 100.150.200.251


    SQL>
    SQL> EXEC emp_ip_update(1000, '100.150.200.252')


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM emp_ip_add;


        EMP_ID IP1             IP2             IP3             IP4             IP5             LAST_UPDATED_IP
    ---------- --------------- --------------- --------------- --------------- --------------- ---------------
          1000 100.150.200.249 100.150.200.250 100.150.200.251 100.150.200.252                 100.150.200.252


    SQL>
    SQL> EXEC emp_ip_update(1000, '100.150.200.253')


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM emp_ip_add;


        EMP_ID IP1             IP2             IP3             IP4             IP5             LAST_UPDATED_IP
    ---------- --------------- --------------- --------------- --------------- --------------- ---------------
          1000 100.150.200.249 100.150.200.250 100.150.200.251 100.150.200.252 100.150.200.253 100.150.200.253


    SQL>
    SQL> EXEC emp_ip_update(1000, '100.150.200.254')


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM emp_ip_add;


        EMP_ID IP1             IP2             IP3             IP4             IP5             LAST_UPDATED_IP
    ---------- --------------- --------------- --------------- --------------- --------------- ---------------
          1000 100.150.200.254 100.150.200.250 100.150.200.251 100.150.200.252 100.150.200.253 100.150.200.254


    SQL>
     

    Of course this is a simple example with no exception handling; you should include exception handlers to deal with any possible errors that could arise.
     
    Vicky likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    Another version - with Dynamic SQL:

    Code (Text):
    CREATE OR REPLACE PROCEDURE emp_ip_update(
        p_empid IN NUMBER,
        p_ip VARCHAR2)
    IS
      vip1    VARCHAR2(15):=NULL;
      vip2    VARCHAR2(15):=NULL;
      vip3    VARCHAR2(15):=NULL;
      vip4    VARCHAR2(15):=NULL;
      vip5    VARCHAR2(15):=NULL;
      vnextip VARCHAR2(1) :=NULL;
      vsql    VARCHAR2(4000);
    BEGIN
      SELECT
        CASE
          WHEN NVL(last_updated_ip, '1') = '5'
          THEN '1'
          ELSE TO_CHAR(NVL(last_updated_ip, '1')+1)
        END
      INTO vnextip
      FROM emp_ip_add
      WHERE emp_id = p_empid;
      --
      vsql := 'UPDATE emp_ip_add ' || 'SET ip' || vnextip
         || ' = '''  || p_ip || ''', last_updated_ip = '''
         || vnextip || ''' WHERE emp_id = ' || p_empid;
      EXECUTE IMMEDIATE vsql;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      vsql := 'INSERT INTO emp_ip_add(emp_id, ip1, last_updated_ip) '
        || 'VALUES (' || p_empid || ', '''|| p_ip ||''', ''1'')';  
      EXECUTE IMMEDIATE vsql;
    END;
    /
    N.B: This assumes that in the "last_updated_ip" field you have the number of the column which has last been updated.
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks David., And Rajen we're not updating last_updated_ip with the number of the column which got updated @ last but the IP value, which has been updated.