Re: pl/sql: display employees who hired in specific month
Date: Wed, 09 Apr 2008 20:10:03 +0200
On Wed, 9 Apr 2008 10:42:48 -0700 (PDT), chris <lazyboy_2k_at_yahoo.com> wrote:
>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.
> 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';
> OPEN emp_cursor;
> FETCH emp_cursor INTO empno, lname, hiredate;
> IF hiredate = UPPER(my_month) THEN
> DBMS_OUTPUT.PUT_LINE ('Sep employees: ' || empno || lname
> DBMS_OUTPUT.PUT_LINE ('other employees: ' || empno ||
>lname || hiredate);
> END IF;
> EXIT WHEN emp_cursor%NOTFOUND;
> END LOOP;
> CLOSE emp_cursor;
One of the first rules is you should never do procedurally what you can do non-procedurally
define my_month = '&1'
, '&my_month','&my_month employees' ,'other employees') "hire_date"FROM employees;
No PL/SQL needed.
-- Sybrand Bakker Senior Oracle DBAReceived on Wed Apr 09 2008 - 13:10:03 CDT