Oracl中PL/SQL编程
概述: PL/SQL是oracle的专用语言,它对标准的SQL语言的扩展.SQL语句可以嵌套在PL/SQL语言中,并结合处理语句。举例,一般在银行系统中,都是事先写好的sql语句来处理业务,为了保证安全性,这些都是不可见的。就可以使用PL/SQL来完整我们需要的功能处理。
一、PL/SQL程序结构及组成
使用了程序块的结构组织的代码.最简单的程序块是一种被称为”匿名块”的程序块,匿名块是指不会被oracle存储并且不能够重用程序块。
PL/SQL程序通常包括3部分:Declare部分、exception部分、Begin和end部分.
Begin和end是PL/SQL的必须部分,总是begin开始end结束.
Declare部分包含定义变量、常量和游标等类型的代码.
Begin与end之间可以嵌套begin与end及exception.
//打开服务器的输出
set serveroutput on;
程序结构:
declare
说明部分 (变量说明,光标申明,例外说明〕
begin
语句序列 (DML语句〕…
exception
例外处理语句
End;
变量与常量的声明:
说明变量类型:char, varchar2, date, number, boolean, long
引用变量emp.ename%type:引用emp表中ename字段的类型
emp%rowtype 记录型变量,可直接加点加字段名访问字段值
变量名 类型(字符,日期,数字,布尔)[:=缺省值];
常量名 constant 类型(字符,日期,数字,布尔) [:=缺省值];
变量与常量的区别:
变量可以在任何部分进行赋值.
而常量在declare中声明并且赋值.
注意:都是在declare中声明
作用域:
是能够引用变量名称这样的标识符的程序块。
二、逻辑语言
a) 条件判断语句
i. If语句
IF 条件 THEN 语句1;
语句2;
end if;
ii. If .. Else语句
IF 条件 THEN 语句序列1;
ESLE 语句序列 2;
END IF;
iii. IF elsif else 语句;
IF 条件 THEN 语句;
ELSIF 语句 THEN 语句;
ELSE 语句;
END IF;
b) 循环语句
i. While循环
WHILE total <= 25000 LOOP
.. .
total : = total + salary;
END LOOP;
ii. 根据条件跳出循环
Loop
EXIT [when 条件];
……
End loop
iii. For循环
FOR I IN 1 . . 3 LOOP
语句序列 ;
END LOOP ;
三、游标(光标) cursor
概述:游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)。在oracle数据库中可以使用显示或隐式两种游标。
a) 隐式游标
在执行一个sql语句时,oracle服务器将自动创建一个隐式游标,这个游标是内存中处理该语句的工作区域,其中存储了执行SQL语句的结果.通过游标的属性可获得sql语句执行的结果以及游标状态信息。
游标的主要属性如下:
%found 布尔属性 如果sql语句至少影响一行则为true 否则为false
%notfound 布尔属性与%found相反。
%isopen 布尔属性游标是否打开 打开为true 否则为false
%rowcount 数字属性返回受sql语句影响的行数
b) 显示游标
是在PL/SQL程序中使用包含select语句来声明的游标。如果需要处理从数据库中检索的一组记录,则可以使用显示游标.使用显示游标处理数据需要四个步骤:声明游标,打开游标,检索数据,关闭游标。
i. 声明游标
声明游标就是通过定义游标的名称,游标的特性来声明游标,以及打开游标后就可调用查询语句,声明的语法如下:
Cursor cursor_name[parameter[,parameter]….]
[return return_type] is select_statement;
Parameter作为游标的输入参数,它可以让用户在打开游标式,向游标传递值;语法如下:
Parameter_name [in] datatype[{:=|default} expression]\
举例:
declare
cursor emp_cursor (pno in number(4) default 7369)
is select * from emp
where empno=pno;
ii. 打开游标
就是指执行声明游标时指定的查询语句。打开的方式只需使用open打开语法:
Open cursor_name(参数);
如果没有指定参数就采用默认值执行select语句
iii. 检索数据
检索数据就是从检索到的结果集中获取数据保存到变量中,以便变量进行处理。
使用fetch语句找出结果集中的单行,并从中提取单个值传递给主变量。
语法如下:
Fetch cursor_name into [variable_list[record_variable]]
变量用于存储检索的数据
iv. 关闭游标
close 游标名称
v. 游标for循环
依次读取结果集中的行,当for循环开始时,游标会自动打开(不需要使用open方法开启),每循环读取一次,系统自动读取当前数据(不需要使用fetch),当退出for循环时,游标也会自动关闭(不需要使用close方法)。
vi. 游标变量
游标变量也可以处理多行查询结果集。
游标变量的定义包括两个步骤:
1、 定义cursor类型的指针
语法:
Type ref_cursor_name is ref cursor[return return_type]
举例:
Type var_cursor_name is ref cursor;
2、 定义ref cursor类型的变量
v_rc var_cursor_name;
综合写法如下:
Type var_cursor_name is ref cursor;
v_rc var_cursor_name;
上面的综合声明的游标变量称为弱的ref cursor类型,因为它没有指明游标返回的结果,因此它可以指向任何一个具有多列的select查询结果.
相对于上面还有一种称为:强ref cursor类型.
声明方式如下:
Type varcursorName is ref cursor return emp%rowtype; //指明了返回的结果
Vcn varcursorName; //声明一个强的ref cursor类型的变量
使用游标变量与游标使用方式一样,也需要声明,打开,检索,关闭游标变量。
vii. 使用游标更新数据库
定位游标之后进行删除|修改指定的数据行更新的时候需要使用for update选项语法如下:
Cursor cursor_name is select_statement;
For update[of column[,column],[nowait]]
Of用来指定要锁定的列,如果忽略of那么表中选择的数据行都将锁定。如果被锁定行已经被锁定了,那么必须等待释放才能锁定对于这种情况我们可以使用nowait语句。
当使用for update语句声明游标后,可以再delete|update语句中使用where current of子句,修改|删除游标结果集中当前行对应的表中的数据。
语法如下:
Where { current of cursor_name|search_condition}
四、实例代码:
实例1:统计每年入职的员工个数
declare
cursor cl isselectcount(*),to_char(hiredate,'yyyy')from emp groupby to_char(hiredate,'yyyy');
emp_num number;
emp_year char(4);
emp_count number:=0;
emp_row1 varchar2(100):='total';
emp_row2 varchar2(100):='';
begin
selectcount(*)into emp_count from emp;
emp_row2:=emp_row2||' '||emp_count;
open cl;
loop
fetch cl into emp_num,emp_year;
exitwhen cl%notfound;
emp_count:=emp_count+emp_num;
emp_row1:=emp_row1||' '||emp_year;
emp_row2:=emp_row2||' '||emp_num;
endloop;
close cl;
dbms_output.put_line(emp_row1);
dbms_output.put_line(emp_row2);
end;
实例二:为员工长工资。从最低工资调起每人长10%,但工资总额不能超过50万元,请计算长工资的人数和长工资后的工资总额,并输出输出长工资人数及工资总额。
declare
cursor cl isselect empno,sal from emp orderby sal;
salCount number;
empCount number:=0;
emp_no emp.empno%type;
emp_sal emp.sal%type;
begin
selectsum(sal)into salCount from emp;
open cl;
while salCount<50000
loop
fetch cl into emp_no,emp_sal;
exitwhen cl%notfound;
update emp set sal=sal*1.1where empno=emp_no;
salCount:=salCount+emp_sal*0.1;
empCount:=empCount+1;
endloop;
close cl;
dbms_output.put_line('长工资人数'||empCount||'长工资后总额'||salCount);
end;
实例三:用PL/SQL语言编写一程序,实现按部门分段(6000以上、(6000,3000)、3000元以下)统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金),参考如下格式:
部门 小于3000数 3000-6000 大于6000 工资总额
10 2 1 0 8750
20 3 2 0 10875
30 5 0 0 8450
declare
cursor cl isselect deptno,sum(sal)from emp groupby deptno orderby deptno asc;
emp_deptno emp.deptno%type;
emp_salsum number;
sal1 number;
sal2 number;
sal3 number;
begin
open cl;
loop
fetch cl into emp_deptno,emp_salsum;
exitwhen cl%notfound;
selectcount(*)into sal1 from emp where deptno=emp_deptno and sal<3000;
selectcount(*)into sal2 from emp where deptno=emp_deptno and(sal between3000and600);
selectcount(*)into sal3 from emp where deptno=emp_deptno and sal>6000;
dbms_output.put_line(emp_deptno||' '||sal1||' '||sal2||' '||sal3||' '||emp_salsum);
endloop;
end;