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

From: <sybrandb_at_hccnet.nl>
Date: Thu, 10 Apr 2008 07:04:01 +0200
Message-ID: <mh7rv3lf84r2ff5j21n6smht8fokkqgbfb@4ax.com>


On Wed, 9 Apr 2008 15:32:35 -0700 (PDT), chris <lazyboy_2k_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
Received on Thu Apr 10 2008 - 00:04:01 CDT

Original text of this message