PL/SQL PROBLEM (已解决)

html

who can help me fix this problem (sorry cannot type chinese word)


SET SERVEROUTPUT ON SIZE 4000
DECLARE
v_empno EMP.EMPNO%TYPE;
v_ename EMP.ENAME%TYPE;
v_job EMP.JOB%TYPE;
v_sal EMP.SAL%TYPE;
bonus NUMBER;
BEGIN
SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = empno;
CASE
WHEN v_job = 'PRESIDENT' THEN
IF v_sal <= 5000 THEN bonus := 5000*3;
ELSE bonus := 5000*5;
END IF;
WHEN v_job = 'MANAGER' THEN
IF v_sal <= 3000 THEN bonus := 3000*3;
ELSE bonus := 3000*4;
END IF;
WHEN v_job = 'SALESMAN' THEN
IF v_sal <= 1300 THEN bonus := 1300*3;
ELSE bonus := 1300*4;
END IF;
WHEN v_job = 'CLERK' THEN
IF v_sal <= 1000 THEN bonus := 1000*3;
ELSE bonus := 1000*4;
END IF;
WHEN v_job = 'ANALYST' THEN
IF v_sal <= 2000 THEN bonus := 2000*3;
ELSE bonus := 2000*4;
END IF;
ELSE
BEGIN
DBMS_OUTPUT.PUT_LINE('No raise for this job');
END;
END CASE;
DBMS_OUTPUT.PUT_LINE(v_ename || v_job || v_sal || bonus);
END;
/

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 8

[ 本帖最后由 html 于 15-2-2007 06:18 PM 编辑 ]

神仙祖宗

你的empno是什么？

html

原帖由 神仙祖宗 于 13-2-2007 10:07 AM 发表
你的empno是什么？

this is the right one (how to fix please....)


SQL> SET SERVEROUTPUT ON SIZE 4000
SQL> DECLARE
  2  v_ename EMP.ENAME%TYPE;
  3  v_job EMP.JOB%TYPE;
  4  v_sal EMP.SAL%TYPE;
  5  bonus NUMBER;
  6  BEGIN
  7  SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP
  8  CASE
  9  WHEN v_job = 'PRESIDENT' THEN
10  IF v_sal <= 5000 THEN bonus := 5000*3;
11  ELSE bonus := 5000*5;
12  END IF;
13  WHEN v_job = 'MANAGER' THEN
14  IF v_sal <= 3000 THEN bonus := 3000*3;
15  ELSE bonus := 3000*4;
16  END IF;
17  WHEN v_job = 'SALESMAN' THEN
18  IF v_sal <= 1300 THEN bonus := 1300*3;
19  ELSE bonus := 1300*4;
20  END IF;
21  WHEN v_job = 'CLERK' THEN
22  IF v_sal <= 1000 THEN bonus := 1000*3;
23  ELSE bonus := 1000*4;
24  END IF;
25  WHEN v_job = 'ANALYST' THEN
26  IF v_sal <= 2000 THEN bonus := 2000*3;
27  ELSE bonus := 2000*4;
28  END IF;
29  ELSE
30  BEGIN
31  DBMS_OUTPUT.PUT_LINE('No raise for this job');
32  END;
33  END CASE;
34  DBMS_OUTPUT.PUT_LINE(v_ename || v_job || v_sal || bonus);
35  END;
36  /
SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP
                                        *
ERROR at line 7:
ORA-06550: line 8, column 6:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 1:
PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimite
ORA-06550: line 13, column 1:
PLS-00103: Encountered the symbol "WHEN"

[ 本帖最后由 html 于 13-2-2007 11:50 AM 编辑 ]

神仙祖宗

我不会oracle

不过从逻辑上来看，第一楼的empno应该是一个变量吧？那么你的错误就是没有给个值与empno。

max^^

ORA-01422: exact fetch returns more than requested number of rows

是因为你的select statement return 多过一个row，这种case应该用CURSOR来做

html

原帖由 max^^ 于 13-2-2007 05:04 PM 发表
ORA-01422: exact fetch returns more than requested number of rows

是因为你的select statement return 多过一个row，这种case应该用CURSOR来做

CURSOR 什么来的? 没学过....

cristiano~7

原帖由 html 于 12-2-2007 11:44 PM 发表
who can help me fix this problem (sorry cannot type chinese word)


SET SERVEROUTPUT ON SIZE 4000
DECLARE
v_empno EMP.EMPNO%TYPE;
v_ename EMP.ENAME%TYPE;
v_job EMP.JOB%TYPE;
v_ ...

ORA-01422: exact fetch returns more than requested number of rows
是因為得到的結果是多于一個row的

SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = EMPNO;
要改成
SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = &EMPNO;

當你execute這個pl/sql時,他會叫你輸入&EMPNO
因為EMPNO是PRIMARY KEY(相信是),所以得到的結果一定是最多一個row就沒有error了
在SELECT ... INTO ...當多于一個row或者沒有row就會有error,或者叫做exception比較恰當
以下是一些exception處理的例子
BEGIN
SELECT ... INTO ... (當你使用SELECT ... INTO ... 代表你expect的只是一個row)
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS EXCEPTION');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND EXCEPTION');
END;
通常TOO_MANY_ROWS EXCEPTION是因為sql stament本身的問題
NO_DATA_FOUND EXCEPTION常用,比如說在
SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = &EMPNO;
USER可能輸入不正確的EMPNO而導致sql return 0 row
BEGIN
SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = &EMPNO;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('INVALID EMPNO');
END;


另外在你的例子中是要search by ENAME,因為ENAME應該不是UNIQUE的(可能兩個人相同名字)
就要使用CURSOR了,可以想象成它是存多個RECORD,要OPEN來開始,然后FETCH 來拿一筆資料, 可以用LOOP來拿全部資料,最后要CLOSE來結束,不然會有ERROR
DECLARE
  ...
  CURSOR C_EMP IS SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = &ENAME;
  EMP C_EMP%ROWTYPE;
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP INTO EMP;
EXIT WHEN C_EMP%NOTFOUND;
...
DBMS_OUTPUT.PUT_LINE(v_ename || v_job || v_sal || bonus);
END LOOP;
CLOSE C_EMP;
或者直接用LOOP也可以
DECLARE
  ...
  CURSOR C_EMP IS SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = &ENAME;
BEGIN
FOR EMP IN C_EMP LOOP
...
DBMS_OUTPUT.PUT_LINE(v_ename || v_job || v_sal || bonus);
END LOOP;
END;

html

原帖由 cristiano~7 于 13-2-2007 10:34 PM 发表


ORA-01422: exact fetch returns more than requested number of rows
是因為得到的結果是多于一個row的

SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = EMPNO;
要改成
SEL ...

thank your for ur comment..

html

i had modified the code as below


DECLARE
v_empno EMP.EMPNO%TYPE;
v_ename EMP.ENAME%TYPE;
v_job EMP.JOB%TYPE;
v_sal EMP.SAL%TYPE;
v_hiredate EMP.HIREDATE%TYPE;
bonus NUMBER;
today_date DATE;
year_work NUMBER;
BEGIN
today_date := SYSDATE;
year_work := TRUNC((today_date - v_hiredate)/365.25);
SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = &EMPNO;
CASE
WHEN v_job = 'PRESIDENT' THEN
IF v_sal <= 5000 THEN bonus := 5000*3;
ELSE bonus := 5000*5;
END IF;
WHEN v_job = 'MANAGER' THEN
IF v_sal <= 3000 THEN bonus := 3000*3;
ELSE bonus := 3000*4;
END IF;
WHEN v_job = 'SALESMAN' THEN
IF v_sal <= 1300 THEN bonus := 1300*3;
ELSE bonus := 1300*4;
END IF;
WHEN v_job = 'CLERK' THEN
IF v_sal <= 1000 THEN bonus := 1000*3;
ELSE bonus := 1000*4;
END IF;
WHEN v_job = 'ANALYST' THEN
IF v_sal <= 2000 THEN bonus := 2000*3;
ELSE bonus := 2000*4;
END IF;
ELSE
BEGIN
DBMS_OUTPUT.PUT_LINE('No raise for this job');
END;
END CASE;
DBMS_OUTPUT.PUT_LINE(' ' || v_ename || ' ' || v_job || ' ' || v_sal || ' ' || bonus || ' ' || year_work);
END;
/

This is the output

Enter value for empno: 7839
old  13: SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = &EMPNO;
new  13: SELECT ENAME,JOB,SAL INTO v_ename,v_job,v_sal FROM EMP WHERE EMPNO = 7839;
KING PRESIDENT 5000 15000
PL/SQL procedure successfully completed.

why the year_work value didn't print?? anybody know?

[ 本帖最后由 html 于 14-2-2007 05:21 PM 编辑 ]

cristiano~7

原帖由 html 于 13-2-2007 11:46 PM 发表
i had modified the code as below


SET SERVEROUTPUT ON SIZE 4000
DECLARE
v_empno EMP.EMPNO%TYPE;
v_ename EMP.ENAME%TYPE;
v_job EMP.JOB%TYPE;
v_sal EMP.SAL%TYPE;
v_hiredate EMP.HIREDATE ...

today_date := SYSDATE;
year_work := TRUNC((today_date - v_hiredate)/365.25);

那是因為v_hiredate是null,所以year_work是null
可以修改select ... into ... 來設定v_hiredate

html

cristiano ... can ur help me check wht the problem ?



SQL> DECLARE
  2  v_ename EMP.ENAME%TYPE;
  3  v_deptno EMP.DEPTNO%TYPE;
  4  v_sal EMP.SAL%TYPE;
  5  v_hiredate EMP.HIREDATE%TYPE;
  6  bonus NUMBER;
  7  today_date DATE;
  8  year_work NUMBER;
  9  BEGIN
10  SELECT ENAME,DEPTNO,SAL,HIREDATE INTO v_ename,v_deptno,v_sal,v_hiredate FROM EMP WHERE ENAME = &ENAME;
11  today_date := SYSDATE;
12  year_work := TRUNC((today_date - v_hiredate)/365.25);
13  CASE
14  WHEN
15  v_deptno = '10' THEN
16  IF year_work > 10 AND v_sal > 2500 THEN bonus := v_sal * 3;
17  ELSIF year_work > 10 AND v_sal < 2500 THEN bonus := v_sal * 2.5;
18  ELSE bonus := v_sal * 1.5;
19  END IF;
20  WHEN v_deptno = '20' THEN
21  IF year_work > 10 AND v_sal > 2500 THEN bonus := v_sal * 4;
22  ELSIF year_work > 10 AND v_sal < 2500 THEN bonus := v_sal * 3;
23  ELSE bonus := v_sal * 2.5;
24  END IF;
25  WHEN v_deptno = '30' THEN
26  IF year_work > 10 AND v_sal > 2500 THEN bonus := v_sal * 3;
27  ELSIF year_work > 10 AND v_sal < 2500 THEN bonus := v_sal * 2;
28  ELSE bonus := v_sal * 1.5;
29  END IF;
30  WHEN v_deptno = '40' THEN
31  IF year_work > 10 AND v_sal > 2500 THEN bonus := v_sal * 2.5;
32  ELSIF year_work > 10 AND v_sal < 2500 THEN bonus := v_sal * 2;
33  ELSE bonus := v_sal * 1.5;
34  END IF;
35  ELSE
36  BEGIN
37  DBMS_OUTPUT.PUT_LINE('No raise for this job');
38  END;
39  END CASE;
40  DBMS_OUTPUT.PUT_LINE(' ' || v_ename || ' ' || v_deptno || ' ' || year_work || ' ' || ' ' || v_sal || ' ' || bonus);
41  END;
42  /


Enter value for ename: KING
old  10: SELECT ENAME,DEPTNO,SAL,HIREDATE INTO v_ename,v_deptno,v_sal,v_hiredate FROM EMP WHERE ENAM
new  10: SELECT ENAME,DEPTNO,SAL,HIREDATE INTO v_ename,v_deptno,v_sal,v_hiredate FROM EMP WHERE ENAM
SELECT ENAME,DEPTNO,SAL,HIREDATE INTO v_ename,v_deptno,v_sal,v_hiredate FROM EMP WHERE ENAME = KING;
                                                                                               *
ERROR at line 10:
ORA-06550: line 10, column 96:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored

---------This is EMP table-------------------------------------------------------------
CREATE TABLE EMP (
EMPNO               NUMBER(4) NOT NULL,
ENAME               VARCHAR2(10),
JOB                 VARCHAR2(9),
MGR                 NUMBER(4) CONSTRAINT EMP_MGR_FK REFERENCES EMP (EMPNO),
HIREDATE            DATE,
SAL                 NUMBER(7,2),
COMM                NUMBER(7,2),
DEPTNO              NUMBER(2) NOT NULL,
CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO));
--------------------------------------------------------------------------------------

[ 本帖最后由 html 于 14-2-2007 11:00 PM 编辑 ]

html

问题已解决。。请版主删贴