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

On Wed, 9 Apr 2008 10:42:48 -0700 (PDT), chris <lazyboy_2k_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. 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

One of the first rules is you should never do procedurally what you can do non-procedurally

define my_month = '&1'
SELECT employee_id
, last_name
,
decode(TO_CHAR(hire_date, 'month')

           , '&my_month','&my_month employees'
            ,'other employees') "hire_date"

No PL/SQL needed.

-- 
Sybrand Bakker
Senior Oracle DBA