在scott下set serveroutput on
declare
type TempRecord is record
(
ID number,
Name varchar(10),
Sal number,
stax number,
Info varchar(20)
);
type TempRecorder is ref cursor
return TempRecord;
recorder TempRecord;
temp TempRecorder;
begin
open temp for select empno,ename,sal from emp;
loop
fetch temp into recorder;
if recorder.Sal>=4000 then
recorder.stax:=recorder.sal*0.2;
recorder.Info:='0.2';
else
recorder.stax:=recorder.sal*0.1;
recorder.Info:='0.1';
end if;
insert into Tax(empno,ename,stax,memo)
values(recorder.ID,recorder.Name,recorder.stax,recorder.Info);
exit when temp%notfound;
end loop;
close temp;
end;
/错误信息:open temp for select empno,ename,sal from emp;
*
ERROR at line 15:
ORA-06550: line 15, column 15:
PLS-00382: expression is of wrong type
ORA-06550: line 15, column 1:
PL/SQL: SQL Statement ignored
declare
type TempRecord is record
(
ID number,
Name varchar(10),
Sal number,
stax number,
Info varchar(20)
);
type TempRecorder is ref cursor
return TempRecord;
recorder TempRecord;
temp TempRecorder;
begin
open temp for select empno,ename,sal from emp;
loop
fetch temp into recorder;
if recorder.Sal>=4000 then
recorder.stax:=recorder.sal*0.2;
recorder.Info:='0.2';
else
recorder.stax:=recorder.sal*0.1;
recorder.Info:='0.1';
end if;
insert into Tax(empno,ename,stax,memo)
values(recorder.ID,recorder.Name,recorder.stax,recorder.Info);
exit when temp%notfound;
end loop;
close temp;
end;
/错误信息:open temp for select empno,ename,sal from emp;
*
ERROR at line 15:
ORA-06550: line 15, column 15:
PLS-00382: expression is of wrong type
ORA-06550: line 15, column 1:
PL/SQL: SQL Statement ignored
DECLARE
TYPE TEMPRECORD IS RECORD(
ID NUMBER,
NAME VARCHAR(10),
SAL NUMBER,
STAX NUMBER,
INFO VARCHAR(20));
TYPE TEMPRECORDER IS REF CURSOR RETURN TEMPRECORD;
RECORDER TEMPRECORD;
TEMP TEMPRECORDER;
BEGIN
OPEN TEMP FOR
SELECT EMPNO, ENAME, SAL,1,'xxx' FROM EMP;
LOOP
FETCH TEMP
INTO RECORDER;
IF RECORDER.SAL >= 4000 THEN
RECORDER.STAX := RECORDER.SAL * 0.2;
RECORDER.INFO := '0.2';
ELSE
RECORDER.STAX := RECORDER.SAL * 0.1;
RECORDER.INFO := '0.1';
END IF;
INSERT INTO TAX
(EMPNO, ENAME, STAX, MEMO)
VALUES
(RECORDER.ID, RECORDER.NAME, RECORDER.STAX, RECORDER.INFO);
EXIT WHEN TEMP%NOTFOUND;
END LOOP;
CLOSE TEMP;
END;
/