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 in 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('3100'); loop fetch c1 into column_type_record; exit when c1%notfound; 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; 注意粗体部分
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 in 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('3100'); loop fetch c1 into column_type_record; exit when c1%notfound; 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;注意粗体部分
--改一下 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;
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 => 'AD_VP'); 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; 结果又悲剧了,会话直接死了。
fetch c1 into column_type_record; exit when c1%notfound; --不加這一句會死循環的
DECLARE TYPE emp_record_type IS RECORD( f_name hr.employees.first_name%TYPE, h_date hr.employees.hire_date%TYPE); v_emp_record EMP_RECORD_TYPE; CURSOR c3(dept_id NUMBER, j_id VARCHAR2) RETURN EMP_RECORD_TYPE IS SELECT first_name, hire_date FROM hr.employees WHERE department_id = dept_id AND job_id = j_id; BEGIN OPEN c3(j_id => 'AD_VP', dept_id => 90); LOOP FETCH c3 INTO v_emp_record; IF c3%FOUND THEN DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是' ||v_emp_record.h_date); ELSE DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); EXIT; END IF; END LOOP; CLOSE c3; END; 那这个和我写的一样啊 但是没有死循环。。帮我解答一下,我现在挺纠结的。
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 in 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('3100');
loop
fetch c1 into column_type_record;
exit when c1%notfound;
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;
注意粗体部分
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 in 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('3100');
loop
fetch c1 into column_type_record;
exit when c1%notfound;
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;注意粗体部分
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;
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 => 'AD_VP');
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;
结果又悲剧了,会话直接死了。
exit when c1%notfound; --不加這一句會死循環的
难道
fetch c1 into column_type_record; 不是把游标里面的记录数据传给column_type_record 类型吗 ? 只要column_type_record是有限集合的话 那么就不会死循环啊。
DECLARE
TYPE emp_record_type IS RECORD(
f_name hr.employees.first_name%TYPE,
h_date hr.employees.hire_date%TYPE);
v_emp_record EMP_RECORD_TYPE; CURSOR c3(dept_id NUMBER, j_id VARCHAR2)
RETURN EMP_RECORD_TYPE
IS
SELECT first_name, hire_date FROM hr.employees
WHERE department_id = dept_id AND job_id = j_id;
BEGIN
OPEN c3(j_id => 'AD_VP', dept_id => 90);
LOOP
FETCH c3 INTO v_emp_record;
IF c3%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'
||v_emp_record.h_date);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c3;
END;
那这个和我写的一样啊 但是没有死循环。。帮我解答一下,我现在挺纠结的。
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
處理了退出呀
set serveroutput on
以后尽量自己摸索。