Oracle数据库第5讲

1、oracle第第5讲讲1. pl/sql的介绍的介绍 2. pl/sql的基础的基础主讲 李 珩oracle oracle 数据库数据库pl/sql的介绍的介绍 pl/sql是什么是什么pl/sql(procedural language/sql) 是oracle在标准的sql语言上的扩展. pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误,这样使得它的功能变得更加强大。 学习必要性学习必要性图提高应用程序的运行性能模块化的设计思想分页的过程，订单的过程，转账的过程.减少网络传输量提高安全性 缺点移植性不好. 主讲 李 珩oracl

2、e oracle 数据库数据库pl/sql的介绍的介绍用什么开发用什么开发pl/sql sqlplus开发工具开发工具sqlplus是oracle公司提供的一个工具，这个因为我们在以前介绍过的:举一个简单案例:编写一个存储过程，该过程可以向某表中添加记录. pl/sql developer开发工具开发工具pl/sql developer 是用于开发pl/sql块的集成开发环境(ide), 他是一个独立的产品，而不是oracle的一个附带品。举一个简单案例:编写一个存储过程，该过程可以删除某表记录.主讲 李 珩oracle oracle 数据库数据库创建过程基本语法：create procedu

3、re 过程名(参数1)isbegin执行语句;end;/执行过程语法：exec 过程名(传入参数)pl/sql基础知识基础知识介绍介绍 介绍介绍开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如:分页存储过程模块、订单处理存储过程模块、转账存储过程模块. 而且如果使用pl/sql编程，我们可以轻松的完成非常复杂的查询要求.主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识pl/sql可以做什么可以做什么 简单分类简单分类 |-

4、过程(存储过程)|-函数 块(编程)-|-触发器|-包主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识编写规范编写规范 编写规范编写规范注释单行注释 -多行注释/*.*/来划分标识符号的命名规范1)当定义变量时，建议用v_作为前缀 v_sal2)当定义常量时,建议用c_作为前缀 c_rate3)当定义游标时,建议用 _cursor作为后缀 emp_cursor;4)当定义例外时，建议用 e_作为前缀 e_error主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识pl/sql块介绍块介绍 介绍介绍块(block)是pl/sql的基本

5、程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块；但是如果要想实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块.主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识pl/sql块结构块结构 块结构示意图块结构示意图pl/sql块由三个部分构成:定义部分、执行部分、例外处理部分.如下所示:declare/*定义部分-定义常量、变量、游标、例外、复杂数据类型*/begin/*执行部分-要执行的pl/sql语句和sql语句*/exception/*例外处理部分-处理运行的各种错误*

6、/end;可以和java编程结构做一个简单的比较定义部分是从定义部分是从declaredeclare开始的，该部分是开始的，该部分是可选的。执行部分是从可选的。执行部分是从beginbegin开始的开始的, ,该部该部分是必须的。例外处理部分是从分是必须的。例外处理部分是从exceptionexception开始的，该部分是可选的开始的，该部分是可选的主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识pl/sql实例实例 实例实例1-只包括执行部分的只包括执行部分的pl/sql块块相关说明:dbms_output是oracle所提供的包（类似java的开发包），该

7、包包含一些过程，put_line就是dbms_output包的一个过程。案例：输出hello,world 主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识pl/sql实例实例 实例实例2-包含定义部分和执行部分的包含定义部分和执行部分的pl/sql块块相关说明相关说明:& 表示要接收从控制台输入的变量| 表示把两个串拼接案例：根据用户输入的雇员编号，显示该雇员的名字 主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识pl/sql实例实例 实例实例3-包含定义部分、执行部分和例外处理部分包含定义部分、执行部分和例外处理部分为了

8、避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理,这个很有必要：比如在实例2中，如果输入了不存在的雇员号，应当做例外处理.有时出现异常，希望用另外的逻辑处理，网示比如，如果不存在就加入编号为1 ,名字为马大哈这么一个人.我们看看如何完成的要求相关说明:oracle事先预定义了一些例外，no_data_found 就是找不到数据的例外.主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识过程过程快速入门快速入门 过程过程过程用于执行特定的操作.当建立过程时，既可以指定输入参数(in),也可以指定输出参数(out).通过在过程中使用输入

9、参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境.在sqlplus中可以使用create procedure 命令来建立过程。实例如下实例如下:请考虑编写一个过程，可以输入雇员名,新工资 可修改雇员的工资如何调用过程有两种方法:exec 过程名(参数值.) call 过程名过程名(参数值参数值.)create procedure 过程名过程名(变量变量 in 变量类型变量类型，变量，变量 out 变量类型变量类型) is定义变量定义变量begin执行语句执行语句;end;主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识过程过程

10、快速入门快速入门 java中调用过程中调用过程如何在java程序中调用一个存储过程课堂小练习：编写一个过程，可以接受id和薪水，更新薪水，如果id不存在，需要在exception中捕获，并给出提示!需要在控制台，和java程序中都调用?如何使用过程返回值。特别说明特别说明: 对于过程我们会在以后给大家详细具体的介绍，现在请大家对于过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念先有一个概念.主讲 李 珩oracle oracle 数据库数据库动手体验：我们写一个java程序来调用前面的存储过程pl/sql基础知识基础知识函数函数快速入门快速入门 函数函数 函数用于返回特定的数据,当

11、建立函数时，在函数头部必须包含return 子句，而在函数体内必须包含return语句返回的数据。我们可以使用 create function 来建立函数 ,实际案例:在在sqlplus 中调用函数中调用函数同样我们可以在同样我们可以在java 程序中调用该函数程序中调用该函数select annual_income(SCOTT) from dual;/这样这样可以通过可以通过rs.getInt(1)得到返回的结果得到返回的结果主讲 李 珩oracle oracle 数据库数据库案例：请编写一个函数，可以接收用户名案例：请编写一个函数，可以接收用户名并返回该用户的年薪并返回该用户的年薪.cre

12、ate function 函数名(参数1)return 数据类型 is定义变量;begin执行语句;end;var 变量名 变量类型call 函数名(参数值)into:变量名;print 变量名pl/sql基础知识基础知识包包 包包包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。我们可以使用我们可以使用create package 命令来创建包命令来创建包:实例：create or replace package 包名 isprocedure 过程名(变量名 变量类型);function 函数名(变量名 变量类型) return 数据类型;end;包的规范只包含了过程和函数的说明，但

13、是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数，主讲 李 珩oracle oracle 数据库数据库请编写一个包，该包有一个过程，该过程可以接收用户名和新的薪水，（将来用于通过用户名去更新薪水）还有一个函数，该函数可以接收一个用户名（将来要实现得到该用户的年薪是多少）pl/sql基础知识基础知识包包建立包体可以使用建立包体可以使用create package body 命令命令create or replace package body 包名 isprocedure 过程名(变量名 变量类型) isbegin执行语句;end;function 函数名(变量名 变量类型)retur

14、n 数据类型 is定义变量;begin执行语句;end;end;主讲 李 珩oracle oracle 数据库数据库案例：请实现前面定义的包中的过程和函数pl/sql基础知识基础知识包包如何调用包的过程或是函数如何调用包的过程或是函数当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名.如:特别说明:包是pl/sql中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力。主讲 李 珩oracle oracle 数据库数据库exec 方案名.包名.过程名(参数值.);call 方案名.包名.过程名(参数值);测试前面包中的函数和过程名是否正确

15、调用！pl/sql基础知识基础知识触发器触发器 触发器简单介绍触发器简单介绍触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。 特别说明特别说明:我们会在我们会在后面详细后面详细为大家介绍触发器的使用，因为触发器是为大家介绍触发器的使用，因为触发器是非常有用的非常有用的,可维护数据库的安全和一致性。可维护数据库的安全和一致性。主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识定义并使

16、用变量定义并使用变量 介绍介绍在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有:标量类型(scalar)复合类型(composite)参照类型(reference)lob(large object)主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识定义并使用变量定义并使用变量 标量标量(scalar)-常用类型常用类型在编写pl/sql块时,如果要使用变量,需在定义部分定义变量。pl/sql中定义变量和常量的语法如下中定义变量和常量的语法如下:identifier constant datatype not null :=| default

17、 expridentifier:名称constant:指定常量.需要指定它的初始植,且其值是不能改变的datatype:数据类型not null:指定变量值不能为null:=给变量或是常量指定初始植default 用于指定初始植expr:指定初始植的pl/sql表达式,可是文本值、其它变量、函数等举例说明吧举例说明吧主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识定义并使用变量定义并使用变量 标量定义的案例标量定义的案例定义一个变长字符串v_enamevarchar2(10);定义一个小数 范围 -9999.999999.99v_salnumber(6,2);定

18、义一个小数并给一个初始植为5.4 :=是pl/sql的赋值号v_sal2number(6,2):=5.4定义一个日期类型的数据 v_hiredatedate;定义一个布尔变量，不能为空，初始植为falsev_valid boolean not null default false;主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识定义并使用变量定义并使用变量 标量标量(scalar)-(scalar)-使用标量使用标量在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前加冒号(:=)案例：以输入员工号，显示

19、雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用,看看如何编写.主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识定义并使用变量定义并使用变量 标量标量(scalar)-使用使用%type类型类型对于上面的pl/sql块有一个问题：就是如果员工的姓名超过了5字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度,我们看看这个怎么使用：标识符名 表名.列名%type; 主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识定义并使用变量定

20、义并使用变量 复合变量复合变量(composite)-(composite)-介绍介绍用于存放多个值的变量。常用的包括这: pl/sql记录pl/sql表主讲 李 珩oracle oracle 数据库数据库pl/sql基础知识基础知识定义并使用变量定义并使用变量 复合类型复合类型-pl/sql-pl/sql记录记录类似与高级语言中的结构体,需要注意的是,当引用pl/sql记录成员时,必须要加记录变量作为前缀(记录变量.记录成员)如下：主讲 李 珩oracle oracle 数据库数据库请编写一个过程，该过程可以接收一个用户编号，并显示该用户的名字，薪水，请编写一个过程，该过程可以接收一个用户编

21、号，并显示该用户的名字，薪水，工作岗位工作岗位（注意：要求用（注意：要求用pl/sql记录实现记录实现)type type 自定义的自定义的pl/pl/sqlsql记录名记录名 is recordis record( (变量名变量名 变量类型变量类型, ,变量名变量名 变量类型变量类型, ,); );/使用自定义的使用自定义的pl/pl/sqlsql记录记录变量名变量名 自定义的自定义的pl/pl/sqlsql记录名记录名; ;pl/sql基础知识基础知识定义并使用变量定义并使用变量 复合类型复合类型-pl/sql-pl/sql表表( (了解即可了解即可）相当于高级语言中的数组.但是需要注意的

22、是在高级语言中数组的下标不能为负数,而pl/sql是可以为负数的,并且表元素的下标没有限制.实例如下declaredeclaretype type aaaa_table_type_table_type is table of is table of emp.ename%typeemp.ename%typeindex by index by binary_integerbinary_integer; ;aaaa_table_table aa_table_typeaa_table_type; ;- -定义一个变量定义一个变量: :aa_tableaa_table : :类型类型beginbeginselect select enameename into into aa_table(aa_table(-1-1) ) from from empemp where where empnoempno=7788;=7788;dbms_output.put_line(dbms_output.put_line(员工名员工名:| :| aa_table(aa_table(-1-1) ); );end;end;说明：说明：aaaa_table_type_table_type是是p