1 2 Previous Next 21 Replies Latest reply: Aug 2, 2011 11:19 AM by 799603 RSS

    Accepting user input in pl/sql procedure

    799603
      Can someone kindly explain me how can i accept user input in the below stored procedure
      CREATE OR REPLACE PROCEDURE calsal
      IS
         CURSOR maxsal
         IS
            SELECT *
              FROM emp;
      
         max_sal   emp.sal%TYPE;
      BEGIN
         SELECT MAX (sal)
           INTO max_sal
           FROM emp;
      
         FOR i IN maxsal
         LOOP
            IF i.sal = max_sal
            THEN
               DBMS_OUTPUT.put_line (   ' Maximum salary plus bonus of '
                                     || i.empno
                                     || ' is '
                                     || (i.sal + i.sal * 0.5)
                                    );
            END IF;
         END LOOP;
      END;
      The above program works fine,but if i want to keep a WHERE condition in line No 12 i.e., WHERE deptno:=&deptno..My logic doesn't work by just putting like this.So,anyone can explain me what should i do in order to accept department number from user.

      Thanks in advance!!
        • 1. Re: Accepting user input in pl/sql procedure
          Boneist
          By a user, I hope you mean a front end program of some description?!

          Typically, you would do this by adding a parameter to the procedure, and then when you call the procedure, you pass in the parameter.
          • 2. Re: Accepting user input in pl/sql procedure
            Solomon Yakobson
            Add parameter to stored procedure:
            CREATE OR REPLACE PROCEDURE calsal(p_deptno number)
            IS
               CURSOR maxsal
               IS
                  SELECT *
                    FROM emp;
             
               max_sal   emp.sal%TYPE;
            BEGIN
               SELECT MAX (sal)
                 INTO max_sal
                 FROM emp;
                 WHERE deptno = p_deptno;
             
               FOR i IN maxsal
               LOOP
                  IF i.sal = max_sal
                  THEN
                     DBMS_OUTPUT.put_line (   ' Maximum salary plus bonus of '
                                           || i.empno
                                           || ' is '
                                           || (i.sal + i.sal * 0.5)
                                          );
                  END IF;
               END LOOP;
            END;
            /
            SY.
            • 3. Re: Accepting user input in pl/sql procedure
              799603
              Nope,i am a starter in pl/sql programming.
              • 4. Re: Accepting user input in pl/sql procedure
                sb92075
                sandy wrote:
                Can someone kindly explain me how can i accept user input in the below stored procedure
                CREATE OR REPLACE PROCEDURE calsal
                IS
                CURSOR maxsal
                IS
                SELECT *
                FROM emp;
                
                max_sal   emp.sal%TYPE;
                BEGIN
                SELECT MAX (sal)
                INTO max_sal
                FROM emp;
                
                FOR i IN maxsal
                LOOP
                IF i.sal = max_sal
                THEN
                DBMS_OUTPUT.put_line (   ' Maximum salary plus bonus of '
                || i.empno
                || ' is '
                || (i.sal + i.sal * 0.5)
                );
                END IF;
                END LOOP;
                END;
                The above program works fine,but if i want to keep a WHERE condition in line No 12 i.e., WHERE deptno:=&deptno..My logic doesn't work by just putting like this.So,anyone can explain me what should i do in order to accept department number from user.

                Thanks in advance!!
                PL/SQL runs inside the RDBMS engine & has no real contact with the outside world or the "end user".
                An appropriately programmed User Interface is required; which involves some other programming language.
                • 5. Re: Accepting user input in pl/sql procedure
                  799603
                  Yup,basics but good timing...But,the above program considers only the max salary of all the records rather than other departments..

                  exec sal(10) only yields the result..In the place of 10,if we change them to different deptno's the max salary of that particular dept doesn't come..So,what should i do in order to achieve that.
                  • 6. Re: Accepting user input in pl/sql procedure
                    Solomon Yakobson
                    Please explain desired stored procedure logic. What SP is supposed to do.

                    SY.
                    • 7. Re: Accepting user input in pl/sql procedure
                      799603
                      I need a stored procedure such that an employee who is working in 'x' department and earning max salary should get a bonus of '.5%' and an employee who is having minimum salary in that department should get '10%'
                      • 8. Re: Accepting user input in pl/sql procedure
                        Frank Kulash
                        Hi,

                        You don't need a stored procedure or PL/SQL for that; you can do it in SQL*Plus:
                        SELECT       deptno
                        ,       empno
                        ,       ename
                        ,       sal
                        ,       CASE
                                  WHEN  sal = MAX (sal) OVER (PARTITION BY deptno)  THEN  sal * 1.05
                                  WHEN  sal = MIN (sal) OVER (PARTITION BY deptno)  THEN  sal * 1.10
                                                                                            ELSE  sal * 1.075
                               END          AS new_sal
                        FROM       scott.emp
                        WHERE       deptno     IN (&deptno_to_show)
                        ORDER BY  deptno
                        ,            sal
                        ;
                        This version allows you to enter either a single deptno, or a comma-delimited list of deptnos. For example, if you enter "20,10" THE OUTPUT IS:
                        `   DEPTNO      EMPNO ENAME             SAL    NEW_SAL
                        ---------- ---------- ---------- ---------- ----------
                                10       7934 MILLER           1300       1430
                                10       7782 CLARK            2450    2633.75
                                10       7839 KING             5000       5250
                                20       7369 SMITH             800        880
                                20       7876 ADAMS            1100     1182.5
                                20       7566 JONES            2975   3198.125
                                20       7902 FORD             3000       3150
                                20       7788 SCOTT            3000       3150
                        sandy wrote:I need a stored procedure such that an employee who is working in 'x' department and earning max salary should get a bonus of '.5%' and an employee who is having minimum salary in that department should get '10%'
                        What if the sal is neither the greatest nor the smallest in the department? The query above gives those people a 7.5% raiise.

                        If you need to do this in PL/SQL, you can put it in a procedure. In that case, use an argument, as Solomon suggested. (This will only work for a single department, not a comma-delimited list.) If you call the procedure from SQL*Plus, you can ask the user for a department number when you call the procedure.
                        • 9. Re: Accepting user input in pl/sql procedure
                          799603
                          I really appreciate that SQL Query,i couldn't have achieved that.But, i am supposed to create a package with the above said requirements,that is what concerns me.If you have any suggestions regarding that you are highly welcome.

                          BTW,Thanks a lot for that SQL Query
                          • 10. Re: Accepting user input in pl/sql procedure
                            Solomon Yakobson
                            And what if there are multiple employees earning max or min salary. I'll assume all will get corresponding bonus. I will also asume that if all department employees get same salary (so they are both min and max) they will get .5% bonus:
                            CREATE OR REPLACE
                              PROCEDURE calsal(p_deptno number)
                                IS
                                    CURSOR maxsal
                                      IS
                                        SELECT  empno,
                                                CASE
                                                  WHEN cnt = 1 THEN SAL * 1.005
                                                  WHEN rank_max = 1 THEN SAL * 1.005
                                                  WHEN rank_min = 1 THEN SAL * 1.1
                                                END sal_and_bonus
                                          FROM  (
                                                 SELECT  empno,
                                                         COUNT(DISTINCT SAL) OVER() cnt,
                                                         RANK() OVER(ORDER BY SAL DESC) rank_max,
                                                         RANK() OVER(ORDER BY SAL) rank_min,
                                                         sal
                                                   FROM  emp
                                                   WHERE deptno = p_deptno
                                                )
                                         WHERE rank_max = 1
                                            OR rank_min = 1;
                                BEGIN
                                    FOR v_rec IN maxsal LOOP
                                      DBMS_OUTPUT.put_line (   ' Salary plus bonus of '
                                                           || v_rec.empno
                                                           || ' is '
                                                           || v_rec.sal_and_bonus
                                                          );
                                    END LOOP;
                            END;
                            /
                            SY.

                            Edited by: Solomon Yakobson on Aug 1, 2011 1:50 PM
                            • 11. Re: Accepting user input in pl/sql procedure
                              Frank Kulash
                              Hi,
                              sandy wrote:
                              I really appreciate that SQL Query,i couldn't have achieved that.But, i am supposed to create a package with the above said requirements,that is what concerns me.If you have any suggestions regarding that you are highly welcome.

                              BTW,Thanks a lot for that SQL Query
                              If you need use PL/SQL, then go ahead and use PL/SQL. Just change &deptno_to_show to the argument of the procedure (following Solomon's suggestion); that's the only change you need to make to run that query in PL/SQL. (You may want to change the SELECT clause, to include only the values you need.)
                              CREATE OR REPLACE PROCEDURE calsal (p_deptno  IN  emp.deptno%TYPE)
                              IS
                                 CURSOR sal_cursor
                                 IS
                                      SELECT       deptno
                                   ,       empno
                                   ,       ename
                                   ,       sal
                                   ,       CASE
                                              WHEN  sal = MAX (sal) OVER (PARTITION BY deptno)  THEN  sal * 1.05
                                              WHEN  sal = MIN (sal) OVER (PARTITION BY deptno)  THEN  sal * 1.10
                                                                                                       ELSE  sal * 1.075
                                            END          AS new_sal
                                   FROM       emp
                                   WHERE       deptno     = p_deptno
                                   ORDER BY  deptno
                                   ,            sal
                                   ;
                              BEGIN
                                 FOR i IN sal_cursor
                                 LOOP
                                     ...
                              The query I posted checks for the highest and lowest sals inside the query itself; you don't need a separate query to do that, or a variable to hold that value.
                              • 12. Re: Accepting user input in pl/sql procedure
                                799603
                                Thank you so much,it really meets my requirement.

                                :)
                                • 13. Re: Accepting user input in pl/sql procedure
                                  Solomon Yakobson
                                  There was cut & paste error in my code. Should be:
                                  CREATE OR REPLACE
                                    PROCEDURE calsal(p_deptno number)
                                      IS
                                          CURSOR maxsal
                                            IS
                                              SELECT  empno,
                                                      CASE
                                                        WHEN cnt = 1 THEN SAL * 1.005
                                                        WHEN rank_max = 1 THEN SAL * 1.005
                                                        WHEN rank_min = 1 THEN SAL * 1.1
                                                      END sal_and_bonus
                                                FROM  (
                                                       SELECT  empno,
                                                               COUNT(DISTINCT SAL) OVER() cnt,
                                                               RANK() OVER(ORDER BY SAL DESC) rank_max,
                                                               RANK() OVER(ORDER BY SAL) rank_min,
                                                               sal
                                                         FROM  emp
                                                         WHERE deptno = p_deptno
                                                      )
                                               WHERE rank_max = 1
                                                  OR rank_min = 1;
                                      BEGIN
                                          FOR v_rec IN maxsal LOOP
                                            DBMS_OUTPUT.put_line (   ' Salary plus bonus of '
                                                                 || v_rec.empno
                                                                 || ' is '
                                                                 || v_rec.sal_and_bonus
                                                                );
                                          END LOOP;
                                  END;
                                  / 
                                  SY.
                                  • 14. Re: Accepting user input in pl/sql procedure
                                    799603
                                    Thanks for the reply
                                    1 2 Previous Next