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

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 ... Received on Thu Apr 10 2008 - 09:19:30 CDT