oracle程序包的原理和使用

b47k1902x3

程序包


程序包的定义：
  程序包是对相关过程、函数、变量、游标和异常等对象的封装
  程序包由规范和主体两部分组成






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


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

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;--声明显示游标，但是不能跟isselect子句 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;