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

From: steph <stephan0h_at_yahoo.de>
Date: Thu, 10 Apr 2008 12:04:13 -0700 (PDT)
Message-ID: <b7be6a2c-ecef-4331-8b96-b3bac9a81944@1g2000prg.googlegroups.com>


On 10 Apr., 16:41, sybrandb <sybra..._at_gmail.com> wrote:
> On Apr 10, 4:19 pm, steph <stepha..._at_yahoo.de> wrote:
>
> > On 10 Apr., 15:33, sybrandb <sybra..._at_gmail.com> wrote:
>
> > > On Apr 10, 11:30 am, Barry Bulsara <bbulsar..._at_hotmail.com> wrote:
>
> > > > On Apr 10, 6:04 am, sybra..._at_hccnet.nl wrote:
>
> > > > > On Wed, 9 Apr 2008 15:32:35 -0700 (PDT), chris <lazyboy..._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
>
> > > ...
>
> > > Erfahren Sie mehr »
>
> > is it really necessary to use this explicit language in this forum?
> > this has nothing to do with comp.databases.oracle.server anymore! and
> > some people wonder, why the number of posts is going down in here ...
>
> Please note I was responding to somone who has insulted me many times
> before, and who right now, called me 'an antisocial wanker'
> Regrettably I can't stay polite at such a gross insult.
> Can you?
>
> --
> Sybrand Bakker
> Senior Oracle DBA

i try to become subtle ... Received on Thu Apr 10 2008 - 14:04:13 CDT

Original text of this message