Re: pl/sql: display employees who hired in specific month

On Apr 10, 6:04 am, sybra..._at_hccnet.nl wrote:
> On Wed, 9 Apr 2008 15:32:35 -0700 (PDT), chris <lazyboy..._at_yahoo.com>
> wrote:
>
>
>
>
>
> >On Apr 9, 11:50 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> >> Comments embedded.
> >> On Apr 9, 12:42 pm, chris <lazyboy..._at_yahoo.com> wrote:
>
> >> > Hi All,
>
> >> > I'm trying to write a simple script which displays all employees who
> >> > are hired in Sept in 1 output & the others are in a different output.
> >> > So far, when I run my script, all employees (including emp who are
> >> > hired in the month of Sept) are displayed in 'other employees'
> >> > section.  It looks like the 1st part of the IF condition doesn't
> >> > work.
>
> >> Of course it works, it simply doesn't work as you expect.
>
> >> > Any helps/suggestions are appreciated.
>
> >> > DECLARE
> >> >   CURSOR emp_cursor IS SELECT employee_id,  last_name,
> >> > TO_CHAR(hire_date, 'month') "hire_date"
> >> >         FROM employees;
> >> >    hiredate           VARCHAR2(30);
> >> >    empno             employees.employee_id%TYPE;
> >> >    lname              employees.last_name%TYPE;
> >> >    my_month        VARCHAR2(30)  := 'september';
>
> >> > BEGIN
> >> >    OPEN emp_cursor;
> >> >    LOOP
> >> >         FETCH emp_cursor INTO empno, lname, hiredate;
> >> >         IF hiredate = UPPER(my_month) THEN
> >> >            DBMS_OUTPUT.PUT_LINE ('Sep employees:  ' || empno || lname
> >> > || hiredate);
> >> >        ELSE
> >> >            DBMS_OUTPUT.PUT_LINE ('other employees:  ' || empno ||
> >> > lname || hiredate);
> >> >        END IF;
> >> >       EXIT WHEN emp_cursor%NOTFOUND;
> >> >    END LOOP;
> >> >    CLOSE emp_cursor;
> >> > END;
> >> > /
>
> >> > TIA,
> >> > -Chris
>
> >> Let's take your example, rewrite it slightly to use the EMP table
> >> instead and see why you can't get what you want:
>
> >> SQL> set serveroutput on size 1000000
> >> SQL> --
> >> SQL> -- Your code, using EMP table
> >> SQL> --
> >> SQL> DECLARE
> >>   2    CURSOR emp_cursor IS
> >>   3    SELECT empno,  ename,
> >>   4    TO_CHAR(hiredate, 'month') hire_date -- returns lower-case
> >> month
> >>   5    FROM emp;
> >>   6
> >>   7    hiredate           VARCHAR2(30);
> >>   8    empno              emp.empno%TYPE;
> >>   9    lname              emp.ename%TYPE;
> >>  10    my_month           VARCHAR2(30)  := 'september';
> >>  11
> >>  12
> >>  13  BEGIN
> >>  14     OPEN emp_cursor;
> >>  15     LOOP
> >>  16          FETCH emp_cursor INTO empno, lname, hiredate;
> >>  17          -- comparing lower case to upper case will never match
> >>  18          IF hiredate = UPPER(my_month) THEN
> >>  19             DBMS_OUTPUT.PUT_LINE ('Sep employees:  ' || empno || lname ||
> >> hiredate);
> >>  20         ELSE
> >>  21             DBMS_OUTPUT.PUT_LINE ('other employees:  ' || empno || lname ||
> >> hiredate);
> >>  22         END IF;
> >>  23        EXIT WHEN emp_cursor%NOTFOUND;
> >>  24     END LOOP;
> >>  25     CLOSE emp_cursor;
> >>  26  END;
> >>  27  /
> >> other employees:  7369SMITHdecember
> >> other employees:  7499ALLENfebruary
> >> other employees:  7521WARDfebruary
> >> other employees:  7566JONESapril
> >> other employees:  7654MARTINseptember
> >> other employees:  7698BLAKEmay
> >> other employees:  7782CLARKjune
> >> other employees:  7788SCOTTdecember
> >> other employees:  7839KINGnovember
> >> other employees:  7844TURNERseptember
> >> other employees:  7876ADAMSjanuary
> >> other employees:  7900JAMESdecember
> >> other employees:  7902FORDdecember
> >> other employees:  7934MILLERjanuary
> >> other employees:  7934MILLERjanuary
>
> >> PL/SQL procedure successfully completed.
>
> >> SQL>
> >> SQL>
> >> SQL> --
> >> SQL> -- Working code, providing the proper results
> >> SQL> --
> >> SQL> DECLARE
> >>   2    CURSOR emp_cursor IS
> >>   3    SELECT empno,  ename,
> >>   4    TO_CHAR(hiredate, 'month') hire_date -- returns lower-case
> >> month
> >>   5    FROM emp;
> >>   6
> >>   7    my_month           VARCHAR2(30)  := 'september';
> >>   8
> >>   9
> >>  10  BEGIN
> >>  11     FOR emprec in emp_cursor LOOP
> >>  12          -- comparing like case
> >>  13          IF emprec.hire_date = my_month THEN
> >>  14             DBMS_OUTPUT.PUT_LINE ('Sep employees:  ' || emprec.empno ||'
> >> '|| emprec.ename ||'  '|| emprec.hire_date);
> >>  15         ELSE
> >>  16             DBMS_OUTPUT.PUT_LINE ('other employees:  ' || emprec.empno
> >> ||'     '||emprec.ename ||'  '|| emprec.hire_date);
> >>  17         END IF;
> >>  18     END LOOP;
> >>  19  END;
> >>  20  /
> >> other employees:  7369  SMITH  december
> >> other employees:  7499  ALLEN  february
> >> other employees:  7521  WARD  february
> >> other employees:  7566  JONES  april
> >> Sep employees:  7654  MARTIN  september
> >> other employees:  7698  BLAKE  may
> >> other employees:  7782  CLARK  june
> >> other employees:  7788  SCOTT  december
> >> other employees:  7839  KING  november
> >> Sep employees:  7844  TURNER  september
> >> other employees:  7876  ADAMS  january
> >> other employees:  7900  JAMES  december
> >> other employees:  7902  FORD  december
> >> other employees:  7934  MILLER  january
>
> >> PL/SQL procedure successfully completed.
>
> >> SQL>
>
> >> Attention to detail is important.
>
> >> David Fitzjarrell
>
> >Thanks a lot for all of your helps David & All.  Actually, I just use
> >dbms_output pkgs to verify the if condition & I understand where
> >Sybrand is coming from & I'm all appreciating for that.  Below is my
> >actual codes & it still has errors errors when I'm running. Do you
> >happen to know where is the errors coming from?  THX ALOTS!!!
>
> >CREATE OR REPLACE PROCEDURE raise_sal
> >   ( empno    IN   employees.employee_id%TYPE,
> >     percent  IN   NUMBER)
> >IS
> >BEGIN
> >   UPDATE employees
> >   SET        salary = salary * (1 + percent/100)
> >   WHERE employee_id = empno;
> >   COMMIT;
> >END raise_sal;
> >/
>
> >-----
>
> >SET SERVEROUTPUT ON
> >SET VERIFY OFF
>
> >DROP TABLE emp_raise;
> >CREATE TABLE emp_raise AS (SELECT * FROM employees WHERE 1 = 2);
> >DROP TABLE emp_no_raise;
> >CREATE TABLE emp_no_raise AS (SELECT * FROM employees WHERE 1 = 2);
>
> >DECLARE
>
> >   CURSOR emp_cursor IS
> >         SELECT employee_id  empno,  first_name  fname, last_name
> >lname, email  email_addr,
> >                phone_number  phoneno, TO_CHAR(hire_date, 'month')
> >hire_date, job_id  jobid, salary  sal,
> >                commission_pct  comm, manager_id  mgr_id,
> >department_id   deptno
> >         FROM employees;
>
> >    my_month           VARCHAR2(30)  := 'september';
>
> >BEGIN
> >      FOR emp_rec IN emp_cursor LOOP
> >             IF emp_rec.hire_date = my_month THEN
> >                    raise_sal(empno => emp_rec.empno, percent => 30);
> >                    INSERT INTO emp_raise (employee_id, first_name,
> >last_name, email, phone_number,
> >                                                         hire_date,
> >job_id, salary, commission_pct, manager_id,
>
> >department_id)
> >                               VALUES (emp_rec.empno, emp_rec.fname,
> >emp_rec.lname, emp_rec.email_addr,
> >                                             emp_rec.phoneno,
> >emp_rec.hire_date, emp_rec.jobid, emp_rec.sal,
> >                                             emp_rec.comm,
> >emp_rec.mgr_id, emp_rec.deptno);
> >              ELSE
> >                   INSERT INTO emp_no_raise (employee_id, first_name,
> >last_name, email, phone_number,
> >                                                         hire_date,
> >job_id, salary, commission_pct, manager_id,
>
> >department_id)
> >                               VALUES (emp_rec.empno, emp_rec.fname,
> >emp_rec.lname, emp_rec.email_addr,
> >                                             emp_rec.phoneno,
> >emp_rec.hire_date, emp_rec.jobid, emp_rec.sal,
> >                                             emp_rec.comm,
> >emp_rec.mgr_id, emp_rec.deptno);
> >              END IF;
> >      END LOOP;
> >   END;
> > /
> >SELECT * FROM emp_raise;
> >SELECT * FROM emp_no_raise;
>
> >DECLARE
> >*
>
> >ERROR at line 1:
> >ORA-01858: a non-numeric character was found where a numeric was
> >expected
> >ORA-06512: at line 22
>
> Please don't post this unformatted junk.
> Please stop doing things procedurally, inside a loop, where they
> should be done non-procedurally, using SQL
> Please DON'T commit INSIDE a loop.
> Please DON'T use a loop, where you don't NEED one.
> Please learn SQL!
> Please learn PL/SQL!!!
> Please don't post this UNSCALABLE UTTER NONSENSICAL CRAP!
> And if you INSIST posting this CRAP, include a VERSION (4 digits)
>
> Why do you insist using this CRAP, when there are WAY BETTER
> solutions, as I presented to you? WHY? Want to be 'smart', Want to
> INSULT someone, who is trying to learn you something?
>
> Any explanation?
>
> Please return from your EVIL WAY to HELL.
>
> PL/SQL is NOT MICKEY SOFT!!!
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

> Sybrand Bakker, Senior Oracle DBA, and Employee from hell (3 years running) wrote:

> solutions, as I presented to you? WHY? Want to be 'smart', Want to
> INSULT someone, who is trying to learn you something?
>
> Any explanation?
>
> Please return from your EVIL WAY to HELL.
>
> PL/SQL is NOT MICKEY SOFT!!!

Calm down my little prima donna and don't forget you were prescribed that prozac for a reason.

Please keep posting the way you do so I can continue to appreciate how nice the people I work with are and how glad I am antisocial wankers like you don't work at my company.

By the way

> PL/SQL is NOT MICKEY SOFT!!!

He didn't say it was.

You can write bad code in SQL Server and Oracle and DB2 and MySQL and ...... You do not have to write code like this in SQL Server. You do not have to write code like this in Oracle.

Barry Received on Thu Apr 10 2008 - 04:30:28 CDT