---------- android培训 、java培训、期待与您交流! ----------
学习目标:1,理解oracle的pl/sql的概念
2,掌握pl/sql编程技术(包括编写过程、函数、触发器...)
学习的必要性:
1,提高应用程序的运行性能
2,模块化的设计思想
3,减少网络传输量
4,提高安全性
名言:程序要用灵魂,一定要让你的东西做得好一些。
pl/sql的缺点:
1,移植性不好
1,pl/sql的介绍
pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言,还可以定义变量种常量,允许使用条件语句和循环语句,允许使用例外处理各种错误,这样使得它的功能变得更加强大。
a,过程,函数,触发器是pl/sql编写
b,过程,函数,触发器是在oracle中
c,pl/sql是非常强大的数据库过程语言
d,过程,函数可以在java程序中调用
·sql plus开发工具
sql plus是oracle公司提供的一个工具,这个之前介绍过的。
举一个简单的案例:
编写一个存储过程,该过程可以向某表中添加记录。
·pl/sql developer开发工具
pl/sql developer是用于开发pl/sql块的集成开发环境(ide),它是一个独立的
产品,而不是oracle的一个附带品。
举一个简单的案例:
编写一个存储过程,通过该过程可以删除某表记录。
2,pl/sql的基础
开发人员使用pl/sql编写应用模块时,不仅需要掌握sql语句的编写方法,还要要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句,从而可以编写非常有用的功能模块。比如:分页存储过程模块、订单处理存储过程模块、转账存储过程模块...而且如果使用pl/sql编程,我们可以轻松的完成非常复杂的查询要求。
·简单分类
----过程(存储过程)
----函数
块(编程)
----触发器
-----包
·编写规范
1)注释
a) 单行注释 --
select * from emp where empno=7788;--取得员工信息
b)多行注释 用 /* ...*/ 来标记
2)标识符号的命名规范
a) 当定义变量时,建议用v_作为前缀 v_sal
b) 当定义常量时,建议用c_作为前缀 c_rate
c) 当定义游标时,建议用_cursor作为后缀 emp_cursor
d) 当定义例外时,建议用 e_作为前缀 e_error
·块,是pl/sql的基本程序单元,编写pl/sql程序实际上就是编写pl/sql块。
要完成相对简单的应用功能,可以只需要编写一个pl/sql;但是是如果要 想实现复杂的功能,可以需要在一个pl/sql块中嵌套其它的pl/sql块。
1)块结构示意图
pl/sql块由三个部分构成:定义部分、执行部分、例外处理部分。
如下所示:
declear
/*定义部分-------定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分-------要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分------处理运行的各种错误*/
end;
2)实例1只包括执行部分的pl/sql块
set serverout put on --打开输出选项
begin
dbms_outpput.put_line(‘hello’);
end;
相关说明:
dbms_output是oracle所提供的包,该包包含一些过程,put_line就是dbms_output的一个过程。
3)实例2包含定义部分和执行部分的pl/sql
declare
v_ename varchar2(5); --定义字符串变量
begin
insert ename into v_eanme from emp where empno=&no;
dbms_output.put_line(‘雇员名:’ || v_ename);
end;
相关说明:
&表示要接收从控制台输入的变量。
4)实例3包含定义部分、执行部分和例外处理部分
为了避免pl/sql程序的运行错误,提高pl/sql的健壮性,应该对可能出现的错误进行处理,这个很有必要:
a,比如实例2中,如果输入了不存在的雇员号,应当做例外处理。
b,有时出现异常,希望用另外的逻辑处理,我们看看如何完成a
的要求
相关说明:oracle事先预定义了一些例外,no_data_found就是找不到数据的例外。
·过程
过程用于执行特定的操作,当建立过程时,既可以指定输入参数(in),也可以指定输出参数(out)。通过在过程中使用输入参数,可以将数据传递到执行部分;通过使用输出参数,可以将执行部分的数据传递到应用环境,在sqlplus中可以使用create procedure 命令来建立过程。
实例如下:
a,请考虑编写一个过程,可以输入雇员名,新工资 可以修改雇
员的工资。
b,如何调用过程有两种方法。exec and call
c,如何在java程序中调用一个存储过程。
?如何使用过程返回值
·函数
函数用于的返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return语句返回的数据。我们可以使用create function 来建立函数。
实际案例:输入雇员的姓名,返回该雇员的年薪
create function xp_fun2(xpName varchar2) return
number is YearSal number(7,2);
begin --执行部分
select sal*12+nvl(comm,0)*12 into YearSal from emp where ename=xpName;
return YearSal;
end;
在sqlplus中调用函数
sql>var result number
sql>call xp_fun2(‘SCOTT’) into:result;
sql>print result;
同样我们可以在java程序中调用该函数
select annual_income(‘SCOTT’) from dual;//这样
可以通过
·包
包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成。
1)我们可以使用create package命令来创建包:
实例:
create package xp_package is
procedure update_sal(name varchar2,newsl number);
function annual_income(name varchar2) return number;
end
包的规范只包含了过程和函数的说明,但没有过程和函数的实现代码。包体用于实现规范中的过程和函数。
2)建立包体可以使用create package body命令。
create package body xp_package is
procedure update_sal(name varchar2,newsal number) is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2)
return number is annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary
from emp where ename=name;
return annual_salary;
end;
end;
3) 如何调用包的过程或是函数
当调用包的过程或是函数时,在过程和函数前需要带有包名,如果需要访问其它方案的包,还还需要在包名前加方案名。
如:
sql>call sp_package.update_sal(‘SCOTT’,1500);
特别说明:
包是pl/sql中非常重要的部分,我们在使用过程分页时,将会再次体验它的威力呵呵。
·触发器
触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件和触发的操作,常用的触发事件包括insert,update,delete语句,而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。
特别说明:
我们会在后面详细为大家介绍触发器的使用,因为触发器是非常有用的,可维护数据库的安全和一致性。
·定义并使用变量
在编写pl/sal程序时,可以定义变量和常量;在pl/sal程序中包括有:
1)标题类型(scalar)
标量:在编写pl/sql块时,如果要使用变量,需要定义部分定义变量。
pl/sql中定义变量和常量的语法如下:
indentifier constant datatype not null := default expr;
indentifier:名称
constant:指定常量,需要指定它的初始值,且其值是不能改变的。
datatype:数据类型
not null:指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr : 指定初始值的pl/sql表达式,可是文本值、其它变量、函数等。
·标量案例:
1定义一个变长字符串
v_ename varchar2(10);
2,定义一个小数,范围-9999.99 ~ 9999.99
v_sal number(6,2);
3,定义一个小数并给一个初始值为5.4 := 是pl/sql的赋值符号
v_sal2 number(6,2):=5.4;
4,定义一个日期类型的数据
v_hiredate date;
5,定义一个布尔变量,不能为空,初始值为false
v_valid boolean not null default false;
案例:输入员工号,显示雇员姓名、工资、个人所得税(0.03),说明变量的使用。
declare
c_tax_rate number(3,2):=0.03;
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal:=v_sal*c_tax_rate;--计算所得税
dbms_output.put_line('姓名是:'||v_ename||'工资:'||v_sal||'交税: '||v_tax_sal);
end;
·标量(scalar)使用%type类型
对于上面的pl/sql块有一个问题:
就是如果员工的姓名超过了5个字符的话,就会有错误,为了降低pl/sql程序的维护工作量,可以使用%type属性定义变量,这样它会按照数据库列来确定你定义的变量的类型和长度,
例如:标识符名 表名.列名%type;
2)复合类型(composite),用于存放多个值的变量,主要包括这几种:
a,pl/sql记录
类似与高级语言中的结构体,需要注意的是,当引用pl/sql记录成员时,
必须要加记录变量作为前缀(记录变量.记录成员)如下:
declare
type emp_record_type is record(
name emp.ename%type;
salary emp.sal%type;
title emp.job%type);
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_line(‘员工名:’||sp_record.name);
end;
b,pl/sql表
相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下标不能为负数,而pl/sql是可以为负数的,并且元素的下标没限制。实例如下:
declare
type sp_table_type is table of emp.ename%type
index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line(‘员工:’||sp_table(0));
end;
说明:
sp_table_type 是pl/sql表类型
emp.ename%type 指定表的元素的类型和长度
sp_table 为pl/sql表变量
sp_table(0) 则表示下标为0的元素
c,嵌套表
d,varray
3)参照类型(reference)
参数变量是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。在编写pl/sql程序时,可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。
a,参照变量 ref cursor游标变量
使用游标时,定义游标时不需要指定相应的select语句,但是当使用游标时(open时)需要指定select语句,这样一个游标就与一个select语句结合了。实例如下:
请使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工姓名和他的工资,在些基础上,如果某个员工的工资低于200元,就增加100元。
declare
type sp_emp_cursor is ref cursor; --定义游标类型
test_cursor sp_emp_cursor; --定义游标变量
v_ename emp.ename%type; --定义两个变量
v_sal emp.sal%type;
begin
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断工资高低,决定是否更新
if v_sal<200 then update emp set sal=v_sal+100
where ename=v_ename;
--判断是否test_cursor为空
exit when test_cursor%notfound;
dbms_output.put_line('姓名:'||v_ename||'工资:'||v_sal);
end loop;
end;
4)lob(large object)