Oracle学习（11）：PLSQL程序设计

浏览数：32 / 时间：2015年06月12日

PL/SQL程序结构及组成

什么是PL/SQL?

?PL/SQL（Procedure Language/SQL）

?PLSQL是Oracle对sql语言的过程化扩展

?指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

SQL优点

交互式非过程化；

数据操纵功能强；

自动导航语句简单；

调试容易使用方便。

把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

常用的结合语言

lPlsql(oracle),Transact-sql(SQLserver)

PL/SQL的程序结构

declare

说明部分（变量说明，光标申明，例外说明〕

begin

语句序列（DML语句〕…

exception

例外处理语句

End;

变量和常量的说明

l说明变量（char,varchar2, date, number, boolean,long)

基本数据类型变量
1. 基本数据类型
     Number 数字型
     Int 整数型
     Pls_integer 整数型，产生溢出时出现错误
     Binary_integer 整数型，表示带符号的整数
     Char 定长字符型，最大255个字符
     Varchar2 变长字符型，最大2000个字符
     Long 变长字符型，最长2GB
     Date 日期型
     Boolean 布尔型（TRUE、FALSE、NULL三者取一）
     在PL/SQL中使用的数据类型和Oracle数据库中使用的数据类型，有的含义是完全一致的，
有的是有不同的含义的。
2. 基本数据类型变量的定义方法
     变量名类型标识符 [not null]:=值;
     declare
         age number(3):=26; --长度为3，初始值为26
     begin
         commit;
     end;
     其中，定义常量的语法格式：
     常量名 constant 类型标识符 [not null]:=值;
     declare
         pi constant number(9):=3.1415926;--为pi的数字型常量，长度为9，初始值为3.1415926
     begin
         commit;
     end;

if语句

三种if语句

1. IF 条件 THEN 语句1;

语句2;

END IF;

2. IF 条件 THEN 语句序列1；

ESLE 语句序列 2；

END IF；

3. IF 条件 THEN 语句;

ELSIF 语句 THEN 语句;

ELSE 语句;

END IF;

小知识：获取从键盘输入的数据

?从键盘输入：

accept num prompt ‘请输入一个数字‘;

?得到键盘输入的值：

pnum number := &num;

循环语句

三种循环语句

1. WHILE total <= 25000 LOOP

.. .

total : = total + salary;

END LOOP;

2. Loop

EXIT [when 条件];

……

End loop

3. FOR I IN 1 . . 3 LOOP

语句序列 ;

END LOOP ;

光标(Cursor)==ResultSet

说明光标语法：

1.定义光标

CURSOR 光标名 [ (参数名数据类型[,参数名数据类型]...)]

IS SELECT 语句;

用于存储一个查询返回的多行数据

例如：

cursorc1 is select ename from emp;

2.打开光标： openc1; (打开光标执行查询)

3.取一行光标的值：fetch c1 into pjob;(取一行到变量中)

4.关闭光标： close c1;(关闭游标释放资源)

注意：上面的pjob必须与emp表中的job列类型一致：

?定义：pjobemp.empjob%type;

示例

技术分享

带参数的光标

定义语句：

cursor c2(jobc varchar2)

select ename,salfrom emp

where job=jobc;

执行语句:

Open c2(‘clerk’);

Oracle的异常处理

例外

l例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。

系统定义例外

?No_data_found (没有找到数据)

?Too_many_rows (select …into语句匹配多个行)

?Zero_Divide ( 被零除)

?Value_error (算术或转换错误)

?Timeout_on_resource (在等待资源时发生超时)

用户定义例外及处理例外

DECLARE

My_job char(10);

v_sal emp.sal%type;

No_data exception;

cursor c1 is select distinct jobfrom emp order by job;

begin

open c1;

Fetch c1 into v_job;

IF c1%notFOUND then raiseno_data;

end if;

…

EXCEPTION

WHEN no_data THEN insert into empvalues(‘fetch语句没有获得数据或数据已经处理完‘);

END;

在declare节中定义例外

?out_of exception ;

在可行语句中引起例外

?raise out_of ；

在Exception节处理例外

?when Out_of then …

两种赋值语句

利用:=赋值

lvar1:=‘this is a argument‘;
lemp_rec.sal:= sal*2 + nvl(comm,0);
lsum_sal:=sum_sal+v_sal;

利用into赋值

lFETCH c1 INTO e_eno , e_sal ;

commit语句

l结束当前事务, 使当前事务所执行的全部修改永久化。

在执行完DML语句之后一定不要忘记在代码后面加上commit来提交！

注释

两种注释格式：

-- This is a comment

或

/* This is a comment */

实例示例

示例1

为员工长工资。从最低工资调起每人长10％，但工资总额不能超过5万元,请计算长工资的人数和长工资后的工资总额，并输出输出长工资人数及工资总额。

可能用到的SQL语句：

?select empno,sal from emp order by sal ;

?select sum(sal) into s_sal from emp;

答案：

/*
为员工长工资。从最低工资调起每人长10％，但工资总额不能超过50万元,
请计算长工资的人数和长工资后的工资总额，并输出输出长工资人数及工资总额。

先写出可能用到的sql语句
select empno,sal from emp order by sal;
select sum(sal) from emp;
*/

set serveroutput on

declare
cursor c1 is select empno,sal from emp order by sal;
salTotal NUMBER; --记录工资总额
empCount NUMBER := 0; --涨工资的人数

pempno emp.empno% TYPE; --记录员工的编号
psal emp.sal%type; --记录员工的工资
begin
--得到当前总工资
select sum(sal) into salTotal from emp;
--打开游标
open c1;
--执行循环
while salTotal <= 50000
loop
fetch c1 into pempno, psal;--取出一条记录
exit when c1%notfound;
update emp set sal = sal * 1.1 where empno = pempno; --执行加薪
--记录涨工资后的总额
salTotal := salTotal + psal*0.1;
--记录涨工资的人数
empCount := empCount + 1;
end loop;
close c1;
commit;

dbms_output.put_line(‘涨工资人数:‘ || empCount || ‘ 工资总额:‘ || salTotal);
end;
/

示例2

l用PL/SQL语言编写一程序，实现按部门分段（6000以上、(6000，3000)、3000元以下)统计各工资段的职工人数、以及各部门的工资总额(工资总额中不包括奖金)，参考如下格式：

部门小于3000数 3000-6000 大于6000 工资总额

10 2 1 0 8750

20 3 2 0 10875

30 6 0 0 9400

l提示：可以创建一张新表用于保存数据

createtable msg1

(deptno number,

emp_num1 number,

emp_num2 number,

emp_num3 number,

sum_salnumber);

/*
用PL/SQL语言编写一程序，实现按部门分段（6000以上、(6000，3000)、3000元以下)统计各工资段的职工人数、
以及各部门的工资总额(工资总额中不包括奖金)

先写出可能用到的查询语句
a = select distinct deptno from dept;
select sal from emp where deptno= a中的某个值;

关于结果的输出：
1. 直接输出在屏幕上
2. 输出到一张表中
create table salcount
(deptno number, --部门号
sg1 int, --3000以下的人数
sg2 int, -- 3000~6000的人数
sg3 int -- 6000以上的人数
);
*/

declare
--定义两个游标保存结果
cursor c1 is select distinct deptno from dept;
cursor c2(pdno number) is select sal from emp where deptno=pdno;

--定义三个变量用于保存每个部门三个工资段的人数
count1 NUMBER;
count2 number;
count3 number;

--记录c1游标中的部门号
pdeptno dept.deptno% TYPE;
--记录c2游标中的薪水值
psal emp.sal% TYPE;
begin
open c1;--打开c1 获得所有部门号
loop
fetch c1 into pdeptno;--取一个部门号
exit when c1%notfound;
--计数器清零
count1 := 0;
count2 := 0;
count3 := 0;
--得到该部门的所有员工
open c2(pdeptno);
loop
fetch c2 into psal; --得到该员工的工资
exit when c2%notfound;
if psal <=3000 then count1 := count1 + 1;
elsif psal > 3000 and psal <=6000 then count2 := count2 + 1;
else count3 := count3 + 1;
end if;
end loop;
close c2;

--保存该部门的统计结果
insert into salcount values(pdeptno,count1,count2,count3);
commit;
end loop;
close c1;
end;
/

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

Oracle学习（11）：PLSQL程序设计