oracle程序包的原理和使用

程序包

程序包的定义：

---

程序包是对相关过程、函数、变量、游标和异常等对象的封装

程序包由规范和主体两部分组成

优点：

---

模块化、更轻松的应用程序设计、信息隐藏、新增功能、性能更佳。

创建包头包体的基本操作如下：

createorreplacepackagepack1--创建包头/规范

is

aanumber:=1;--在包头声明的为公有变量

procedureupdate_student(update_rowinstudent%rowtype);--声明一个更新过程

procedureinsert_student(insert_rowinstudent%rowtype);--声明一个插入过程

endpack1;--结束包头

--Package created

createorreplacepackagebodypack1--创建包体/主体

is

bbnumber:=2;--在包体声明的变量类私有变量

procedureinsert_student(insert_rowinstudent%rowtype)--创建过程主体

as

begin

insertintostudent(id,name,age)values(insert_row.id,insert_row.name,insert_row.age);

dbms_output.put_line('bb = '||bb||'aa = '||aa);

endinsert_student;--结束过程主体

procedureupdate_student(update_rowinstudent%rowtype)--创建过程主体

as

begin

updatestudent ssets.name='赵北'wheres.id=update_row.id;

endupdate_student;--结束过程主体

endpack1;--结束主体/包体

--Warning: Package body created with compilation errors

SQL>showerror;--查询错误

ErrorsforPACKAGEBODYHR.PACK1:

LINE/COLERROR

----------------------------------------------------------------------------

5/1PLS-00103:出现符号 "BEGIN"在需要下列之一时：;iswithauthidas

clusterorderusingexternaldeterministicparallel_enable

pipelinedresult_cache 符号 ";" 被替换为 "BEGIN" 后继续。

10/3PLS-00103:出现符号 "PROCEDURE"

11/5PLS-00103:出现符号 "BEGIN"在需要下列之一时：;iswithauthidas

clusterorderusingexternaldeterministicparallel_enable

pipelinedresult_cache 符号 ";" 被替换为 "BEGIN" 后继续。

SQL>

SQL>ed--修改上次执行的代码块

SQL>/--执行修改的代码块

--Package body created

SQL>setserverouton;--打开输出开关

SQL>executedbms_output.put_line(pack1.aa);--包中的公共变量被输出

1

PL/SQLproceduresuccessfully completed

SQL>executedbms_output.put_line(pack1.bb);--包中的私有变量不被输出

begindbms_output.put_line(pack1.bb);end;

--ORA-06550: 第 1 行, 第 34 列:

--PLS-00302: 必须声明 'BB' 组件

--ORA-06550: 第 1 行, 第 7 列:

--PL/SQL: Statement ignored

declare

row_student student%rowtype;--声明行级变量

begin

row_student.id:=5;

row_student.name:='张飞';

row_student.age:=60;

pack1.insert_student(row_student);--调用包中的过程

end;

/

bb=2aa=1

PL/SQLproceduresuccessfully completed

SQL>select*fromstudent;

IDNAME AGE

----------- -------------------- -----------

1张三20

2李四25

3王五30

4麻子30

5张飞60

SQL>

declare

row_student student%rowtype;--声明行级变量

begin

row_student.id:=5;

row_student.name:='关羽';

row_student.age:=60;

pack1.update_student(row_student);--调用包中的过程

end;

/

PL/SQLproceduresuccessfully completed

SQL>select*fromstudent;

IDNAME AGE

----------- -------------------- -----------

1张三20

2李四25

3王五30

4麻子30

5赵北60

程序包中的游标：

---

q游标的定义分为游标规范和游标主体两部分

q在包规范中声明游标规范时必须使用RETURN子句指定游标的返回类型

qRETURN子句指定的数据类型可以是：

q用%ROWTYPE属性引用表定义的记录类型

q程序员定义的记录类型，例如TYPE EMPRECTYP IS RECORD(emp_idINTEGER,salaryREAL)来定义的。

q不可以是number, varchar2，%TYPE等类型。

-----------------------------在程序包中创建显示游标---------------

createorreplacepackagepack2--创建包头

is

cursorstudent_cursorreturnstudent%rowtype;--声明显示游标，但是不能跟is select子句

procedurestudent_pro;--声明过程

endpack2;

createorreplacepackagebodypack2--创建包体

is

cursorstudent_cursorreturnstudent%rowtypeisselect*fromstudent;--指定游标所关联的select

procedurestudent_pro

is

student_row student%rowtype;

begin

openstudent_cursor;

fetchstudent_cursorintostudent_row;

whilestudent_cursor%found

loop

dbms_output.put_line('学号 = '||student_row.id||'姓名 = '||student_row.name);

fetchstudent_cursorintostudent_row;

endloop;

closestudent_cursor;

endstudent_pro;

endpack2;

/

SQL>executepack2.student_pro;

学号=1姓名=张三

学号=2姓名=李四

学号=3姓名=王五

学号=4姓名=麻子

学号=5姓名=赵北

PL/SQLproceduresuccessfully completed

SQL>

-----------------------------在程序包中创建ref游标---------------

createorreplacepackagepack3

is

typeref_cursorisrefcursor;--声明一个ref游标类型

procedureref_student_pro;

endpack3;

--Package created

createorreplacepackagebodypack3

is

procedureref_student_pro

is

student_row student%rowtype;

student_ref_cursor ref_cursor;--声明一个ref游标类型的变量

begin

openstudent_ref_cursorforselect*fromstudent;

fetchstudent_ref_cursorintostudent_row;

whilestudent_ref_cursor%found

loop

dbms_output.put_line('学号 = '||student_row.id||'姓名 = '||student_row.name);

fetchstudent_ref_cursorintostudent_row;

endloop;

closestudent_ref_cursor;

endref_student_pro;

endpack3;

--Package body created

SQL>executepack3.ref_student_pro;

学号=1姓名=张三

学号=2姓名=李四

学号=3姓名=王五

学号=4姓名=麻子

学号=5姓名=赵北

PL/SQLproceduresuccessfully completed

SQL>

系统内置程序包：

---

--------------------------------DBMS_job包的使用方法：------------------------------------

createtabletest_job(date_signdate);

createorreplaceprocedurepro_test

is

begin

insertintotest_jobvalues(sysdate);

end;

SQL>variablejob1number;

SQL>

SQL>begin

2 dbms_job.submit(:job1,'pro_test;',sysdate,'sysdate + 1/1440');--Submit()过程，工作被正常地计划好。

3end;

4/

PL/SQLproceduresuccessfully completed

job1

---------

23

SQL>

SQL>begin

2 dbms_job.run(:job1);-- Run()过程用来立即执行一个指定的工作。这个过

程只接收一个参数。

3end;

4/

PL/SQLproceduresuccessfully completed

job1

---------

23

SQL>select*fromtest_job;

DATE_SIGN

-----------

19-1月-1523

SQL>select*fromtest_job;

DATE_SIGN

-----------

19-1月-1523

SQL>

SQL>begin

2 dbms_job.remove(:job1);--过程来删除一个已计划运行的工作。这个过程接收一个参数。

3end;

4/

PL/SQLproceduresuccessfully completed

job1

---------

23

SQL>

--------------------------------UTL_FILE包的使用方法：------------------------------------

createdirectorydir_utl_fileas'/u01/app/oracle/pl_sql_pacakge/test_utl_file';--创建目录

grantread,writeondirectorydir_utl_filetohr;--给用户赋予权限

createorreplaceprocedurepro_utl_file(path_fileinvarchar2,name_fileinvarchar2)

is

utl_file_contentsvarchar2(2000);--定义内存变量

utl_file_type utl_file.file_type;--定义文件类型变量

begin

utl_file_type:=utl_file.fopen(path_file,name_file,'r',2000);--打开文件

loop

utl_file.get_line(utl_file_type,utl_file_contents);--读取文件内容到内存变量中

dbms_output.put_line(utl_file_contents);--，并打印

endloop;

exception--异常处理部分

whenno_data_found

then

utl_file.fclose(utl_file_type);

end;

Procedurecreated

SQL>setserverouton

SQL>executepro_utl_file('DIR_UTL_FILE','utl_file');

DECLARE

V1VARCHAR2(32767);

F1 UTL_FILE.FILE_TYPE;

BEGIN

-- In this example MAX_LINESIZE is less than GET_LINE's length request

-- so the number of bytes returned will be 256 or less if a line terminator is seen.

F1:=UTL_FILE.FOPEN('MYDIR','MYFILE','R',256);

UTL_FILE.GET_LINE(F1,V1,32767);

UTL_FILE.FCLOSE(F1);

-- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024,

-- so the number of bytes returned will be 1024 or less if a line terminator is seen.

F1:=UTL_FILE.FOPEN('MYDIR','MYFILE','R');

UTL_FILE.GET_LINE(F1,V1,32767);

UTL_FILE.FCLOSE(F1);

-- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to

-- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024.

-- So the number of bytes returned will be 1024 or less if a line terminator is seen.

F1:=UTL_FILE.FOPEN('MYDIR','MYFILE','R');

UTL_FILE.GET_LINE(F1,V1);

UTL_FILE.FCLOSE(F1);

END;

PL/SQLproceduresuccessfully completed

SQL>

dbms_random的使用：

返回某年内的随机日期，分两步：

1,SELECTTO_CHAR(TO_DATE('01/01/03','mm/dd/yy'),'J')FROMDUAL;

2,SELECTTO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641,2452641+364)),'J')FROMDUAL;