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

<sybrandb_at_hccnet.nl> schreef in bericht news:hc1qv3p1cnqgtpjj4gcqog2ferqk3ptcmr_at_4ax.com...
> 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"
> FROM employees;
>
> No PL/SQL needed.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Unless you have to use it in a program.... which most programmers have to do.... but with all the dbms_output.put_lines I guess you're right this time.

Shakespeare Received on Wed Apr 09 2008 - 14:42:56 CDT