第十一课: 程序包
关于本节的分析
内容：
教学重点与难点 教学重点： 教学难点： 3、教学目标
知识目标： 能力目标： 情感目标：
二、教学方法
本课采用边讲边练习的方式教学，让学生亲自动手，逐步熟悉各种语句的使用。
1
ORACLE备课教案——PL/SQL编程基础
教学过程
A.包说明及主体
格式：Create [ Or Replace] Package 包名 IS|AS 变量声明|类型定义|异常声明|游标声明|函数说明|过程说明 Pragma restrict references(函数名，WNDS[,WNPS][,RNDS][,RNDS]) End[包名];
B.格式：Create [Or Replace] Package Body 包名 IS|AS /*包体名一定要已经定义的包名同*/ 变量声明|类型定义|异常声明|游标声明|函数定义|过程定义 End[包体名];
C.包调用 包名.类型名； 包名.函数名[参数表]; 包名..过程名[参数表];
显示绑定到引用游标变量上的值——包调用 set AutoPrint on
1.variable tempCur RefCursor;
2.exec StudentPackage.ReturnStudent(:tempCur); --：表示SQLPLUS中的环境变量
D.数据字典
user_objects、user_source
E.包的修改和删除
drop package [Body] 包名；
drop package body studentPackage;
F.包中的方法可以重载，只依赖于参数不同，与JAVA一样。
建立包体的注意事项：
1.包体只能在包说明被编译后才进行编译
2.在包说明中的过程、函数名称必须严格地与包体中实现部分的过程、函数名称相匹配。
3.在包体的执行部分可以对包说明中声明的变量进行初始化，因为包不能传递参数，所以只能通过这种方法进行初始化。包的初始化只在第一次调用包的时候运行一次。
4.在包体中声明的数据类型、变量、常量都是私有的，只能在包体中使用。但在包体中可以使用在包说明中声明的数据类型、变量、常量。
---------------------------------------------------------------------------------------------------------------------------------------------- create user matrix identified by matrix
-- Grant/Revoke role privileges grant connect to matrix;
grant aq_administrator_role to MATRIX with admin option; grant resource to MATRIX with admin option;
Create table student ( StuId varchar(10) not null primary key, StuName varchar(10) not null, Sex char(2)
2
ORACLE备课教案——PL/SQL编程基础
) go
insert into student values('101', '关羽', '男'); insert into student values('102', '张飞', '男'); insert into student values('103', '赵云', '男'); insert into student values('104', '刘备', '男');
Create table subject ( subId varchar(10) primary key, subName varchar(30) not null ) go
insert into subject values('s001', 'ORACLE'); insert into subject values('s002', 'JAVA'); insert into subject values('s003', '.net');
Create or replace package StudentPackage --包说明创建 is Type curRefStudent is Ref Cursor Return Student%RowType; procedure SelectStudent(FindID in Student.Stuid%type); procedure InsertStudent(NewStudent in student%RowType); procedure UpdateStudent(NewStudent in Student%RowType); procedure DeleteStudent(DelID in student.stuid%Type); procedure ReturnStudent(inOutstu in out curRefStudent); Function ReturnRecordCount Return Number; End StudentPackage; /
Create or replace Package Body StudentPackage As Procedure SelectStudent (FindID in Student.stuid%type) AS Cursor findCur is Select * from student where stuid=FindID; begin For S in FindCur Loop DBMS_Output.Put_Line(S.stuID|| ' ' ||S.StuName||' ' || S.sex); End Loop; Exception When NO_DATA_FOUND then DBMS_Output.put_Line('没有查到ID为：' || FIndID||'的记录'); When OTHERS THEN DBMS_OUTPUT.PUT_LINE('查询过程中发生意外情况'); end SelectStudent; Procedure InsertStudent(NewStudent in Student%RowType) AS iRec Integer; Not_Exists_Student Exception; Begin Select count(*) into iRec from student where stuId=newstudent.stuID; if iRec > 0 then
3
ORACLE备课教案——PL/SQL编程基础
Raise Not_Exists_Student; Else Insert into Student values(newStudent.stuid,newStudent.stuName,newStudent.sex); commit; end if; Exception when NOT_EXISTS_STUDENT then DBMS_OUTPUT.PUT_LINE('要插入的编号为：' ||NewStudent.stuid||'的记录已经存在'); when OTHERS then DBMS_OUTPUT.PUT_LINE('插入的记录操作过程中出现错误'); end InsertStudent;
Procedure UpdateStudent(newStudent in Student%Rowtype) AS iRec Integer; begin Select Count(*) into iRec From student Where stuid = newStudent.stuid; if iRec=0 then DBMS_OUTPUT.PUT_LINE('编号为：'||NewStudent.stuid||'的记录不存在，修改失败'); else Update Student set stuname=newStudent.stuName,Sex=newStudent.Sex where stuid=newStudent.stuID; Commit; DBMS_OUtPUT.PUT_LINE('修改操作成功'); end if; exception when no_data_found then dbms_output.put_line('编号为：' ||newstudent.stuID||'的记录不存在，修改失败'); when others then dbms_output.put_line('执行修改操作时发生意外情况，修改未成功'); end updateStudent;
procedure deleteStudent(DelID in student.stuid%type) as iRec Integer; begin select count(*) into iRec from student where stuID=DelID; if iRec=0 then DBMS_OUTPUT.PUT_LINE('编号为：'||DelID||'的记录不存在，删除操作失败'); else delete from student where stuid = delID; Commit; DBMS_OUtPUT.PUT_LINE('删除操作成功'); end if; exception when others then dbms_output.put_line('执行删除操作时发生意外情况，删除未成功'); end deleteStudent;
\\*引用游标*\\
procedure returnstudent(inoutstu in out curRefStudent) as Begin open inOutStu For Select * from student; end returnstudent;
Function ReturnRecordCount Return Number as RecCount number(10);
4
ORACLE备课教案——PL/SQL编程基础
begin select count(*) into RecCount From Student; Return recCount; Exception when Others then DBMS_OUTPUT.PUT_LINE('查询表中记录数时发生意外情况'); end ReturnRecordCount; end studentPackage;
-------------------------------------------------------------------------------------------------------------- 1.调用studentPackage中的InsertStudent过程 declare newStu Student%RowType; begin newStu.Stuid:='102001'; newStu.Stuname:='傻鸟'; newStu.Sex:='男'; StudentPackage.InsertStudent(newStu); end; 2.
declare newStu student%rowType; begin newStu.Stuid:='102001'; newStu.Stuname:='傻鸟'; newStu.Sex:='男'; StudentPackage.UpdateStudent(newStu); exception when dup_val_on_index then dbms_output.put_line('唯一约束被破坏'); when others then dbms_output.put_line('更新过程出现错误'); end; / 3. begin studentPackage.deleteStudent('888'); end; / 4. begin DBMS_OUTPUT.PUT_LINE(StudentPackage.ReturnRecordCount);--没有参数的函数可以不使用括号 end;
-------------------------------------------------------
在数据包中使用REFCURSOR变量 create or replace package dept_data as type deptcurtyp is ref cursor return dept%rowtype; procedure open_dept_cv(dept_cv in out deptcurtyp); end dept_data;
create or replace package body dept_data as
5
ORACLE备课教案——PL/SQL编程基础
procedure open_dept_cv(dept_cv in out deptcurtyp) is begin open dept_cv for select * from dept; end open_dept_cv; end dept_data;
var a refcursor; set autoprint on
exec dept_data.open_dept_cv(:a);
---------------------------------------------------------------------------------------------------------------------------------------------- create or replace package emp_package is g_deptno number(3):=30; procedure add_employee
(eno number,name varchar2,salary number,dno number default g_deptno); procedure fire_employee(eno number);
function get_sal(eno number) return number; end emp_package;
create or replace package body emp_package is function validate_deptno(v_deptno number) return boolean is
v_temp int; begin
select 1 into v_temp from dept where deptno=v_deptno; return true; exception
when no_data_found then return false; end;
procedure add_employee(eno number,name varchar2,salary number,dno number default g_deptno) is begin
if validate_deptno(dno) then
insert into emp(empno,ename,sal,deptno) values(eno,name,salary,dno); else
raise_application_error(20010,'不存在该部门'); end if; exception
when dup_val_on_index then
raise_application_error(-20011,'该雇员已经存在'); end;
procedure fire_employee(eno number) is begin
delete from emp where empno=eno; if sql%notfound then
raise_application_error(-20012,'该雇员不存在'); end if; end;
function get_sal(eno number) return number is
6
ORACLE备课教案——PL/SQL编程基础
v_sal emp.sal%type; begin
select sal into v_sal from emp where empno=eno; return v_sal; exception
when no_data_found then
raise_application_error(-20012,'该雇员不存在'); end;
end emp_package;
7
因篇幅问题不能全部显示，请点此查看更多更全内容