第5章存储过程和函数

第五章存储过程和函数理论部分本章目标学会在Oracle中使用过程学会在Oracle中使用函数学会在Oracle中使用视图学会在Oracle中使用序列过程简介什么是过程将SQL或者PL/SQL代码块集中用于完成特定功能的集合过程的结构声明部分：包括类型、变量、游标执行部分：完成功能而编写的SQL语句或则是PL/SQL代码块异常处理部分过程的应用创建过程创建过程不使用DECLARE关键字在创建过程时可指定参数CREATE[ORREPLACE]PROCEDUREprocedure_name[(parameter1[{IN|OUT|INOUT}param1_typeparameter2[{IN|OUT|INOUT}param2_type……parameterN[{IN|OUT|INOUT}paramN_type]]{IS|AS}procedure_body;创建过程可选参数SQL语句或者PL/SQL语句块过程的应用--创建名称为ShowInfo的过程CREATEOR

REPLACEPROCEDUREShowInfo--声明一个输入参数(MajorINvarchar2)AS--声明一个游标,在查询语句中使用输入参数值作为查询条件CURSORs_infoISselectfirstname,lastnamefromstudentwheremajor=Major;BEGIN--使用FOR循环提取游标数据

FORs_stuINs_infoLOOPDbms_Output.put_line(s_stu.firstname||''||s_stu.lastname);ENDlOOP;END;声明过程设置参数过程具体实现过程的调用与删除过程的调用与删除--已定义好一个过程ShowInfo,接收一个参数--调用有参过程BEGINShowInfo('MUSIC');END;--调用无参过程BEGINShowInfo;END;--删除过程DROPPROCEDURE

过程名通过过程名进行调用，并传递参数无参过程直接调用过程名称删除过程函数简介什么是函数与过程类似，是一组SQL语句或者PL/SQL语句块的集合，同时能够返回执行结果函数的结构声明部分：包括类型、变量、游标执行部分：完成功能而编写的SQL语句或则是PL/SQL代码块异常处理部分函数的应用创建函数创建函数不使用DECLARE关键字在创建函数时可指定参数CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter1[{IN|OUT|INOUT}param1_typeparameter2[{IN|OUT|INOUT}param2_type……parameterN[{IN|OUT|INOUT}paramN_type]]RETURNreturntype{IS|AS}function_body;创建过程可选参数指定返回结果类型函数的应用函数题内允许有多个return执行return语句，函数将执行结束并返回结果--创建函数CREATEORREPLACEFunctionGETCOUNT(MajorINvarchar2)--声明返回类型returnnumberASf_countnumber;BEGIN--使用INTO语句将结果赋值给变量selectcount(*)intof_countfromstudentswheremajor=Major;--使用RETURN语句返回returnf_count;END;声明函数设置返回类型返回结果过程的调用与删除过程的调用与删除执行那个DROP语句后，函数将被永久删除--调用函数--DECLARE--声明变量接收函数的返回值

v_countnumber;BEGIN

v_count:=GETCOUNT('MUSIC');

Dbms_Output.put_line(v_count);END;--删除过程DROPFUNCTION

函数名定义变量接收函数返回值调用函数并传递参数删除函数过程与函数的比较过程与函数的比较

过程函数标识符PROCEDUREFUNCTION返回值必须使用变量形参用函数名直接返回赋值不能赋值并定义类型可以定义类型，并直接赋值调用方式独立的过程调用句以表达式方式调用目的完成一系列的数据处理获得函数返回值Oracle中的包什么是包包是有存储在一起的相关对象组成的PL/SQL结构用于逻辑组合相关的自定义类型、变量、游标、过程和函数包的组成包的规范（又称包头）用于定义常量、变量、游标、过程和函数等用户与程序的接口可以在保内引用，也可以被外部程序调用包的主体是包规范的实现，包括变量、游标、过程和函数等包体内的内容不能被外部应用程序调用包规范的创建创建包的规范CREATE[ORREPLACE]PACKAGEpackage_name[AUTHID{USER|DEFINER}]{IS|AS}[PRAGMASERIALLY_REUSABLE;][COLLECTION_TYPE_DEFINITION][RECORD_TYPE_DEFINITION][SUBTYPE_DEFINITION][COLLECTION_DECLARATION][CONSTANT_DECLARATION][EXCEPTION_DECLARATION][VARIABLE_DECLARATION][CURSOR_DECLARATION][FUNCTION_DECLARATION][PROCEDURE_DECLARATION]END[package_name];允许定义的规范内容包规范的创建创建包的规范--创建包头--createorreplacepackageemp_packageas--声明存储过程,用于插入一条记录--

proceduremy_proc(

lend_numvarchar2,

lend_namevarchar2,

ledn_sexvarchar2,

majorvarchar2);endemp_package;

定义包头在包内定义存储过程包头结束包体的创建创建包的主体[CREATE[ORREPLACE]PACKAGEBODYpackage_name[AUTHID{USER|DEFINER}]{IS|AS}[PRAGMASERIALLY_REUSABLE;][COLLECTION_TYPE_DEFINITION][RECORD_TYPE_DEFINITION][SUBTYPE_DEFINITION][COLLECTION_DECLARATION][CONSTANT_DECLARATION][EXCEPTION_DECLARATION][VARIABLE_DECLARATION][CURSOR_DECLARATION][FUNCTION_DECLARATION][PROCEDURE_DECLARATION][BEGINstatements]END[package_name];]使用BODY标识包体对包头的实现包体的创建创建包的主体--创建包体--createorreplacepackagebodyemp_packageas--存储过程的实现--proceduremy_proc(

lend_numvarchar2,

lend_namevarchar2,

lend_sexvarchar2,

majorvarchar2)is

begin

insertintoemp(lnum,lname,lsex,major)values

(lend_num,lend_name,lend_sex,major);

endmy_proc;endemp_package对包头内定义的过程具体实现包的调用包的调用使用包名作为前缀，对包中的对象进行调用package_name.type_name;BEGIN

emp_package.my_proc('1010','Loader','male','music');END;调用emp_package包中的存储过程Oracle的内置包Oracle内置的包

包名称说明DBMS_ALERT用于数据库报警，允许会话间通信DBMS_JOB用于任务调度服务DBMS_LOB用于大对象类型操作DBMS_PIPE用于数据库管道，允许会话间通信DBMS_SQL用于执行动态SQLDBMS_FILE用于文本文件的输入与输出视图视图的特点是一个数据库中虚拟的表经过查询操作形成的结果具有普通表的结构不能实现数据的存储对视图的修改将会影响实际的数据表视图的应用创建视图VIEW：用于创建视图CHECKPOINT：必须要满足的视图的约束删除视图CREATE[ORREPLACE]VIEWview_nameAS<selectstatements>[WITHCHECKOPTION]DROPVIEWview_name视图的应用通过视图查询学生信息创建视图从视图中读取数据删除视图--创建视图--createorreplaceviewv_studentasselect*fromstudents;--从视图中检索数据--select*fromv_student;--删除视图--DROPVIEWv_student;序列序列的概念是一个数据库的内置对象产生一系列唯一的数值序列的作用实现类似SQLServer中主键自增的效果序列的应用序列的创建序列的修改CREATESEQUENCEseq_name[incrementbyn][startwithn][maxvaluen|nomaxvalue][minvaluen|nominvalue];SEQUENCE声明序列--修改序列ALTERSEQUENCEseq_name[incrementbyn][maxvaluen|nomaxvalue][minvaluen|nominvalue];使用ALTER修改序列序列的应用序列的删除序列的调用DROPSEQUENCEseq_name--创建序列--createsequenceseq_IDminvalue1maxvalue1000startwith1incrementby1cache20;--查看未生成新序列之前的id--selectidfromtest;--调用序列--insertINTOtest(id)values(seq_id.nextval);--查看产生的序列--selectidfromtest;创建一个名称为seq_ID的序列调用nextval获取序列生成的数值数据库链什么是数据库链是一个建立在本地的路径用于方便地实现不同数据库之间的通信数据库链什么是数据库链是一个建立在本地的路径用于方便地实现不同数据库之间的通信数据库链的创建DATABASELINK：表示创建数据库链SERVERNAME/SERVERURL：表示在连接时使用的服务名或者包含服务完整信息的路径CREATE[PUBLIC]DATABASELINKlink_nameCONNECTTOusernameIDENTIFIEDBYpasswordUSING'SERVERNAME/SERVERURL';数据库链的使用调用数据库链数据库链的创建--创建名称为link_goods的数据库链--createdatabaselinklink_goodsconnecttojbitidentifiedbybdqnusing'(DESCRIPTION=(ADDRESS_LIST