Re: pl/sql: display employees who hired in specific month
Date: Wed, 9 Apr 2008 15:32:35 -0700 (PDT)
Message-ID: <302864f0-f9ec-4a72-a36b-c4f58ce6e836@k10g2000prm.googlegroups.com>
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
Received on Wed Apr 09 2008 - 17:32:35 CDT