4 Replies Latest reply: Nov 19, 2012 10:09 PM by Gurujothi RSS

    Proc with little Logic:::::

    936903
      Please help me for this :

      These IN Parameters should insert as rows in emp table into empno column

      For example :
      p_VAL1 ----1,
      p_VAL1 ----2,
      p_VAL1 ----3

      These variables should insert as three rows in emp table

      EMP
      ---
      EMPNO
      1
      2
      3

      How can i modify the below procedure:

      CREATE TABLE SCOTT.EMP (EMPNO NUMBER);


      CREATE OR REPLACE PROCEDURE scott.proc_1
      (p_VAL1 IN NUMBER,p_VAL1 IN NUMBER,p_VAL1 IN NUMBER)
      IS
      BEGIN
      INSERT INTO scott.emp (EMPNO) ;

      commit;
      END scott.proc_1;
      /
        • 1. Re: Proc with little Logic:::::
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Proc with little Logic:::::
            SomeoneElse
            You can't have multiple parameters with the same name.

            This is a very odd way of writing a procedure for an insert. You will always have to call it with three empno. Really?

            Why not use 1 parameter and insert 1 row.

            And remove the commit. Leave that up to the calling environment.
            • 3. Re: Proc with little Logic:::::
              rp0428
              >
              These IN Parameters should insert as rows in emp table into empno column

              For example :
              p_VAL1 ----1,
              p_VAL1 ----2,
              p_VAL1 ----3
              >
              Well the thread title 'Proc with little Logic' is certainly appropriate since there is little logic to having a proc like that.

              Just use three insert statements
              INSERT INTO EMP (EMPNO) VALUES (p_val1);
              INSERT INTO EMP (EMPNO) VALUES (p_val2);
              INSERT INTO EMP (EMPNO) VALUES (p_val3);
              • 4. Re: Proc with little Logic:::::
                Gurujothi
                Hi,

                Try this,
                CREATE TABLE sample_insert(empno NUMBER);
                
                    
                CREATE PROCEDURE insert_empno (p_val1 NUMBER, p_val2 NUMBER, p_val3 NUMBER)
                IS
                BEGIN
                   INSERT INTO saminsert
                        VALUES (p_val1);
                
                   INSERT INTO saminsert
                        VALUES (p_val2);
                
                   INSERT INTO saminsert
                        VALUES (p_val3);
                END;
                
                
                BEGIN
                   insert_empno (10, 20, 30);
                END;
                
                
                SELECT *
                  FROM sample_insert;
                
                EMP_NO
                   10
                   20
                   30
                Regards,
                Guru

                Edited by: Gurujothi on 20 Nov, 2012 9:38 AM