- SQL code
declare type column_type is record (v_employee_id hr.employees.EMPLOYEE_ID%type, v_first_name hr.employees.FIRST_NAME%type, v_last_name hr.employees.LAST_NAME%type, v_phone_number hr.employees.PHONE_NUMBER%type, v_hire_date hr.employees.HIRE_DATE%type); column_type_record column_type; c1 cursor(j_id varchar2) return column_type is select EMPLOYEE_id, first_name, last_name, phone_number, hire_date from hr.employees where job_id= j_id; begin open c1(j_id >= '3100'); loop fetch c1 into column_type_record; if c1%found then dbms_output.PUT_LINE(column_type_record.v_employee_id||column_type_record.v_first_name||column_type_record.v_last_name||column_type_record.v_phone_number||column_type_record.v_hire_date); end if; end loop; close c1; end;
Error report:
ORA-06550: line 8, column 42:
PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
ORA-06550: line 11, column 27:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior external lan
ORA-06550: line 25, column 30:
PLS-00103: Encountered the symbol "end-of-file" 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-quote
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
------解决方案--------------------
- SQL code
declare type column_type is record (v_employee_id hr.employees.EMPLOYEE_ID%type, v_first_name hr.employees.FIRST_NAME%type, v_last_name hr.employees.LAST_NAME%type, v_phone_number hr.employees.PHONE_NUMBER%type, v_hire_date hr.employees.HIRE_DATE%type); column_type_record column_type; [b]cursor c1(j_id in varchar2)[/b] return column_type is select EMPLOYEE_id, first_name, last_name, phone_number, hire_date from hr.employees where job_id= j_id; begin [b]open c1('3100');[/b] loop fetch c1 into column_type_record; [b]exit when c1%notfound;[/b] if c1%found then dbms_output.PUT_LINE(column_type_record.v_employee_id||column_type_record.v_first_name||column_type_record.v_last_name||column_type_record.v_phone_number||column_type_record.v_hire_date); end if; end loop; close c1; end;
------解决方案--------------------
- SQL code
--改一下DECLARE TYPE column_type IS RECORD( v_employee_id hr.employees.EMPLOYEE_ID%TYPE, v_first_name hr.employees.FIRST_NAME%TYPE, v_last_name hr.employees.LAST_NAME%TYPE, v_phone_number hr.employees.PHONE_NUMBER%TYPE, v_hire_date hr.employees.HIRE_DATE%TYPE); column_type_record column_type; CURSOR c1(j_id VARCHAR2) RETURN column_type IS SELECT EMPLOYEE_id, first_name, last_name, phone_number, hire_date FROM hr.employees WHERE job_id = j_id;BEGIN OPEN c1(j_id => '3100'); LOOP FETCH c1 INTO column_type_record; EXIT WHEN c1%NOTFOUND; dbms_output.PUT_LINE(column_type_record.v_employee_id || column_type_record.v_first_name || column_type_record.v_last_name || column_type_record.v_phone_number || column_type_record.v_hire_date); END LOOP; CLOSE c1;END;