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!

procedures

Discussion in 'SQL PL/SQL' started by bnramesh8, Sep 11, 2014.

  1. bnramesh8

    bnramesh8 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    I want to write Insert,Update,Delete operations in a single procedure?
    How can i achieve it?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    It is very simple....

    create procedure

    Simple example:

    Code (SQL):

    DROP TABLE test_dml purge;
    CREATE TABLE test_dml
    (
     id  INT PRIMARY KEY,
     val varchar2(32)
    );
    INSERT INTO test_dml
    SELECT
           level,
           dbms_random.string('x',32)
    FROM dual
    CONNECT BY level <= 3;
    commit;
    CREATE OR REPLACE PROCEDURE prc_test_dml
    (
      p_cmd varchar2 ,
      p_rec test_dml%rowtype
    )
    AS
    BEGIN
        IF p_cmd = 'I' THEN    INSERT INTO test_dml VALUES p_rec;
        elsif p_cmd = 'U' THEN UPDATE test_dml SET val = p_rec.val WHERE id = p_rec.id;
        elsif p_cmd = 'D' THEN DELETE test_dml WHERE id = p_rec.id;
        END IF;
        dbms_output.put_line('Operation '||CASE p_cmd WHEN 'I' THEN 'Insert'
                                                      WHEN 'U' THEN 'Update'
                                                      WHEN 'D' THEN 'Delete'
                                           END
                             );                            
    END;
    /
    DECLARE
     l_rec test_dml%rowtype;
    BEGIN
        --insert
        FOR z IN 5 .. 8
        loop
            l_rec.id  := z;
            l_rec.val := dbms_random.string('x',32);
            prc_test_dml(p_cmd => 'I',p_rec => l_rec);
        END loop;    
       
        --update  
        FOR z IN 1 .. 2
        loop
            l_rec.id  := z;
            l_rec.val := 'update '||to_char(z);
            prc_test_dml(p_cmd => 'U',p_rec => l_rec);
        END loop;    

        --logilal delete
        FOR z IN 6 .. 9
        loop
            l_rec.id  := z;
            l_rec.val := 'delete '||to_char(z);
            prc_test_dml(p_cmd => 'U',p_rec => l_rec);
        END loop;        
        commit;
    END;
    /
    SELECT * FROM test_dml;
    DECLARE
     l_rec test_dml%rowtype;
    BEGIN
        -- delete
        FOR z IN 6 .. 9
        loop
            l_rec.id  := z;
            l_rec.val := 'delete '||to_char(z);
            prc_test_dml(p_cmd => 'D',p_rec => l_rec);
        END loop;        
        commit;
    END;
    /
    SELECT * FROM test_dml;

    SQL>


    TABLE created
     
    3 ROWS inserted
     
    Commit complete
     
    PROCEDURE created
     
    PL/SQL PROCEDURE successfully completed
     
                                         ID VAL
    --------------------------------------- --------------------------------
                                          1 UPDATE 1
                                          2 UPDATE 2
                                          3 BVAJ9YECVS0LEB0L21ZE8HUO0CDH6U8D
                                          5 EOOC5Y2KISEE2DHHBXPWQ9MB7O1GTMQ9
                                          6 DELETE 6
                                          7 DELETE 7
                                          8 DELETE 8
     
    7 ROWS selected
     
    PL/SQL PROCEDURE successfully completed
     
                                         ID VAL
    --------------------------------------- --------------------------------
                                          1 UPDATE 1
                                          2 UPDATE 2
                                          3 BVAJ9YECVS0LEB0L21ZE8HUO0CDH6U8D
                                          5 EOOC5Y2KISEE2DHHBXPWQ9MB7O1GTMQ9
     
    SQL>
     
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida