【oracle 学习笔记 -6】

create or replace procedure PrintStudentInfo is begin for student_cur in (select * from student) loop dbms_output.put(student_cur.sid || '/'); dbms_output.put(student_cur.sname|| '/'); dbms_output.put_line(student_cur.saddress); end loop; end PrintStudentInfo

create or replace package USER_PKG is TYPE student_info is record ( ID student.sid%type, name student.sname%type, birth student.sbirth%type, addr student.saddress%type ); procedure PrintStudentInfo; function GetStudentInfo(id in varchar2) return student_info; end USER_PKG;

create or replace package body USER_PKG is cursor student_cur is select sid,sname,sbirth,saddress from student; procedure PrintStudentInfo is StudentInfo student_info; begin open student_cur; fetch student_cur into StudentInfo; while student_cur%found loop dbms_output.put(StudentInfo.ID || '/'); dbms_output.put(StudentInfo.name|| '/'); dbms_output.put(StudentInfo.birth|| '/'); dbms_output.put_line(StudentInfo.addr|| '/'); fetch student_cur into StudentInfo; end loop; close student_cur; exception when invalid_cursor then dbms_output.put_line('invalid cursor'); when cursor_already_open then dbms_output.put_line('cursor already open'); end PrintStudentInfo; function GetStudentInfo(id in varchar2) return student_info is StudentInfo student_info; begin select sid,sname, sbirth,saddress into StudentInfo from student where sid = id; return StudentInfo; exception when no_data_found then dbms_output.put_line('no data found'); end GetStudentInfo; end USER_PKG;