一、PL/SQL概述
1.PL/SQL(Procedural Language/SQL,过程语言/SQL)
它是结合Oracle过程语言和结构化查询语言的一种扩展语言
PL/SQL支持多种数据类型,可以使用条件语句和循环语句等控制结构
PL/SQL可用于创建存储过程、触发器和程序包,也可以用来处理业务规则、 数据库事件或给SQL命令的执行添加程序逻辑
2.优点
支持SQL
支持面向对象编程(OOP)
更好的性能
可移植性
与SQL集成
安全性
二、PL/SQL基本结构
1.PL/SQL语言是程序化程序设计语言。
块(Block)是PL/SQL程序中最基本的结构,所有PL/SQL程序都是由块组成。
块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现,也可以相互嵌套(一个块在另一个块的内部)
2.块的分类
匿名块
匿名块是出现在应用程序中的没有名字且不存储到数据库中的块
匿名块出现在SQL语句可以出现的地方,它们可以调用其他程序,却不能 被其他程序调用
命名块
命名块是一种带有标签的匿名块,标签为块指定了一个名称
子程序
子程序是存储在数据库中的过程(procedure)、函数(function),生 成之后可以被多次执行
3.PL/SQL的块由变量声明、程序代码和异常处理代码3部分组成:
DECLARE
标记声明部分
变量的声明,必须要在begin前面
声明一些变量、常量、用户定义的数据类型及游标
name varchar(30); --声明时不设置值
name varchar(30):=‘Jack’;--声明带有默认值
name preson.name%type; --直接引用一个表的数据类型
BEGIN
标记主程序体部分开始
主程序体,在这里可以加入各种合法语句
EXCEPTION
标记异常处理部分开始
异常处理程序,当程序中出现错误时执行这一部分
END
标记主程序体结束部分
基本语法:
declare
说明部分 (变量说明,例外说明 〕
begin
语句序列 (DML语句〕…
exception
例外处理语句
End;
三、PL/SQL字符集
1.PL/SQL语言有效字符包括以下三类
所有大写和小写英文字母
0~9的阿拉伯数字
操作符,包括(、)、+、-、*、/、<、>、!、=、@、%等
2.PL/SQL标识符
标识符的最大长度为30个字符,不区分大小写,但建议在标识符中适当使用 大小写,以增加程序的可读性。
四、PL/SQL变量和常量
1.在PL/SQL程序运行时,需要定义一些变量来存放一些数据。
常量和变量在使用前必须声明,可以使用DECLARE对变量进行声明,语法如 下:
DECLARE
<变量名> <变量类型>:=默认值;
….
在DECLARE块中可以同时声明多个常量和变量。声明普通常量或变量是需要 说明以下信息:
常量或变量的名称
常量或变量的数据类型
2.说明变量 (char, varchar2, date, number, boolean, long)
varl char(15); 说明变量名、数据类型和长度后用分号结束说明语
married boolean :=true;
psal number(7,2);
my_name emp.ename%type; 引用型变量,即my_name的类型与emp表中ename列的类型一样
emp_rec emp%rowtype; 记录变量集
记录变量分量的引用:
emp_rec.ename:='ADAMS';
3.声明常量
声明常量的基本格式如下:
<常量名> constant <数据类型> := <值>;
:= 为赋值语句
关键字constant表示声明的是常量。常量一旦定义,在以后的使用中其值不再改变。
一些固定的大小为了防止有人改变,最好定义成常量。
例如 Pass_Score constant INTEGER := 60 ;
4.声明变量
声明变量的基本格式如下:
<变量名> <数据类型>[(宽度) := <初始值>];
变量声明是没有关键字,但要指定数据类型,宽度和初始值可以定义也可以不定义。
例如
Address VARCHAR2(30) := ‘地址未知’;
PL/SQL对一个未初始化的变量,将被默认赋值为NULL
例如
Address VARCHAR2(30);
5.PLSQL案例
PLSQL案例----1
--打开输出
et severoutput on
--声明一个变量并输出
declare
name varchar(10):=‘HelloWorld';
begin
dbms_output.put_line(name);
end;
PLSQL案例----2
SET SERVEROUTPUT ON;
DECLARE
Pass_Score constant INTEGER:=60;
Address VARCHAR2(30):='北京海淀区';
BEGIN
DBMS_OUTPUT.PUT_LINE(Pass_Score);
DBMS_OUTPUT.PUT_LINE(Address);
END;
使用SET SERVEROUTPUT ON命令设置环境变量SERVEROUTPUT为打开状态,从而使PL/SQL程序能 够在SQL*Plus中输出结果
使用函数DBMS_OUTPUT.PUT_LINE()可以输出参数的值
LSQL案例----3
匿名块示例:
创建一个匿名程序块,该程序块用于接收用户输入的员工编号,查询并输出该员工的姓 名,还 用于处理用户输入的员工编号不存在的异常
set serveroutput on ---set serveroutput on设置输出、显示环境变量
set verify off ---set verify off : 设置是否进行数据校验。
DECLARE ---定义块变量
v_ename varchar2(50);
BEGIN ---执行业务逻辑
select ename into v_ename from emp where empno=&eno;
dbms_output.put_line('您要查找的姓名是:'|| v_ename);
EXCEPTION ---异常处理部分
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('输入的员工编号不存在!');
END;
五、数据类型
1.%TYPE数据类型
当定义PL/SQL变量存放值时,必须确保变量使用合适的数据类型和长度,否则可能会在运行 过程中出现PL/SQL运行错误
为了避免这种不必要的错误,可以使用%TYPE属性来定义变量
当使用%TYPE属性定义变量时,Oracle会自动地按照数据库表列或其他变量来确定新变量的 类型和长度
2.%ROWTYPE数据类型
如果一张表中包含较多的列,则可以使用%ROWTYPE来定义一个表示表中一行记录的变量
示例:
根据输入的员工编号查找该员工信息,及其所在部门的信息。
declare
v_ename emp.ename%type; --定义和dept相同的行类型
v_deptno dept.deptno%type; --定义和dept相同的行类型
v_dept_row dept%rowtype;
begin
select ename ,deptno into v_ename,v_deptno from emp where empno=&eno;
select * into v_dept_row from dept where deptno=v_deptno;
dbms_output.put_line('您要查找的员工是:'||v_ename||',所在部门信为:');
dbms_output.put_line('部门编号:'||v_deptno||' 部门名称:'||v_dept_row.dname||' 所在地:'||v_dept_row.loc);
end;
3.%record数据类型
类似高级语言中的结构
首先需要定义记录类型和记录变量
当引用记录成员时,必须将记录变量作为前缀
示例:
根据输入的员工编号输出该员工的姓名、基本工资、奖金及实发工资。
Type record_name is record
(
V1 data_type1 [not null] [:=default_value],
V2 data_type2 [not null] [:=default_value],
Vn data_typen [not null] [:=default_value]
);
declare
type emp_record_type is RECORD((--定义一个记录类型,包含员工信息
ename emp.ename%type,
sal emp.sal%type,
comm emp.comm%type,
total_sal sal%type);
v_emp_record emp_record_type;--声明记录类型变量
begin
select ename,sal,nvl(comm,0),sal+nvl(comm,0) into v_emp_record
from emp where empno=7369;
dbms_output.put_line('员工姓名:'|| v_emp_record.ename);
dbms_output.put_line('基本工资:'|| v_emp_record.sal);
dbms_output.put_line('奖金:'|| v_emp_record.comm);
dbms_output.put_line('实发工资:'|| v_emp_record.total_sal);
end;
4.TABLE数据类型
TABLE(索引表)相当于一个键值集合,键是唯一的,用于查找对应的值。键可以是整数或字符串
示例:
将部门编号是10和20的部门信息存储到table类型中,然后输出其信息。
declare
type dept_table_type is table of dept%rowtype
index by binary_integer;
v_dept_table dept_table_type;
begin
select * into v_dept_table(0) from dept where deptno=10;
select * into v_dept_table(1) from dept where deptno=20;
dbms_output.PUT_LINE('编号:'||v_dept_table(0).deptno||' 名称:'||v_dept_table(0).dname||' 所在地:'|| v_dept_table(0).loc);
dbms_output.PUT_LINE('编号:'||v_dept_table(1).deptno||' 名称:'||v_dept_table(1).dname||' 所在地:'|| v_dept_table(1).loc);
end;
六、PL/SQL程序的执行部分
1.PL/SQL程序的执行部分包括
赋值语句
流程控制语句
SQL语句
游标语句
2.接收用户的输入赋值
在declare之前,可以通过
accept someVar prompt ‘提示信息’;要求用户输入
在后面的代码中,可以通过地址引用&someVal接收这值
--接收用户的输入
set serveroutput on
--要求用户输入一个数字,如果不输入后面也可以直接引用,只是一个提示而已
accept age prompt 'plz enter a number:';
declare
nn number;
begin
nn := &age;
dbms_output.put_line('你输入的信息是'||nn);
end;
3.从查询中结果中赋值
用into关键字可以将查询结果的值,设置给变量:
set serveroutput on
declare
id number(4);
nm varchar(30);
begin
select empno,ename into id,nm from emp where empno=7369;
dbms_output.put_line('编号'||id||'名称'||nm);
--以下是异常处理 -–可选
exception
when NO_DATA_FOUND then
dbms_output.put_line('没有你要查询的数据');
when others then
dbms_output.put_line('其他错误');
end;
七、流程控制语句
1.流程控制语句是所有过程性程序语言的关键
PL/SQL的主要控制语句如下:
if...then elsif … then end if;
判断if正确则执行then,否则执行else(elsif为嵌套判断)
注意elsif,里面少一下e.
Case var when … then when … then end
有逻辑的从数值中做出选择
Loop exit end loop
循环控制,用判断语句执行exit
Loop exit when … end loop
同上,当when为真时执行exit
while..loop end loop
当while为真时循环
for...in...loop end loop
已知循环次数的循环
if语句
条件控制——if
IF 条件表达式 THEN
语句段
END IF;
---------------------------
IF 条件表达式 THEN
语句段1
ELSE
语句段2
END IF;
----------------------------
IF 条件表达式1 THEN
语句段1
ELSIF 条件表达式2 THEN
语句段2
ELSIF 条件表达式3 THEN
语句段3
......
ELSIF 条件表达式n
语句段n
END IF;
注意:是elsif 不是elseif,也不是else if
if案例
员工奖金发放:
输入员工编号,如果该员工
原来没有奖金,则按照工资的10%发放
原来有奖金但不超过1000的,补到1000;
其余的按照原来奖金基础再加上10%发放;
declare
v_emp emp%rowtype;
begin
v_emp.empno:=&no;
select * into v_emp from emp where empno=v_emp.empno;
dbms_output.PUT_LINE('更新前的奖金'||nvl(v_emp.comm,0));
if v_emp.comm is null then
update emp set comm=v_emp.sal*0.1 where empno=v_emp.empno;
elsIf v_emp.comm<1000 then
update emp set comm=1000 where empno=v_emp.empno;
else
update emp set comm=comm+comm*0.1 where empno=v_emp.empno;
end if;
end;
2.在查询是使用简单的case
在查询中使用case语句:
SQL> select (case id when 2 then '222' else '33' end) from t6;
SQL> --上面的示例等于
SQL> select (case when id=2 then '2222' else '333' end) from t6;
条件控制——case
CASE
WHEN 条件表达式1 THEN
语句段1;
WHEN 条件表达式2 THEN
语句段2;
......
ELSE
语句段n;
END CASE;
case应用案例
case应用案例
case应用案例
根据员工编号输出员工工资级别
sal<2000 A级工资
sal>=2000 and sal<3000 B级工资
其余 C级工资
declare
v_sal emp.SAL%type;
begin
select sal into v_sal from emp where empno=&empno;
case
when v_sal<2000 then dbms_output.put_line('A级工资');
when v_sal>=2000 and v_sal<3000
then dbms_output.put_line('B级工资');
else dbms_output.put_line('C级工资');
end case;
exception
when no_data_found then
dbms_output.put_line('员工编号不存在');
end;
3.循环语句LOOP..EXIT..ENDLOOP
此语句的功能是重复执行循环体中的程序块,直到执行EXIT语句,则退出循环。
LOOP...EXIT...END语句的语法结构如下
LOOP
<程序块 1>
IF <条件表达式> THEN
EXIT
END IF
<程序块 2>
END LOOP;
计算1~4累加
SET ServerOutPut ON;
DECLARE
varNum INTEGER := 1;
varSum INTEGER := 0;
BEGIN
LOOP
varSum := varSum + varNum;
dbms_output.put_line(varNum);
IF varNum = 4 THEN
EXIT;
END IF;
dbms_output.put_line('+');
varNum := varNum + 1;
END LOOP;
dbms_output.put_line('=');
dbms_output.put_line(varSum);
END;
基本循环
定义一个dept类型的表结构
手工添加3条数据
然后用循环将其数据添加到dept表中。
LOOP
语句段;
EXIT [WHEN 条件表达式]
END LOOP;
declare
type dept_table_type is table of dept%rowtype ------定义表结构
index by binary_integer;
i number(1):=0; ---声明循环控制变量
v_dept_table dept_table_type;
begin
v_dept_table(0).deptno:='50';
v_dept_table(0).dname:='研发部';
v_dept_table(0).loc:='北京';
v_dept_table(1).deptno:='60';
v_dept_table(1).dname:='开发部';
v_dept_table(1).loc:='上海';
v_dept_table(2).deptno:='70';
v_dept_table(2).dname:='推广部';
v_dept_table(2).loc:='北京';
loop
if i>2 then exit; end if;
insert into dept values
( v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);
i:=i+1;
end loop;
end;
3.EXIT WHEN
此循环语句的功能是重复执行循环体中的程序块,直到满足EXIT WHEN后面的判断语句,则退出循环。
LOOP...EXIT WHEN...END语句的语法结构如下:
LOOP
<程序块 1>
EXIT WHEN <条件表达式>
<程序块 2>
END LOOP;
重新实现1~4累加
SET ServerOutPut ON;
DECLARE
varNum INTEGER := 1;
varSum INTEGER := 0;
BEGIN
LOOP
varSum := varSum + varNum;
dbms_output.put_line(varNum);
EXIT WHEN varNum = 4;
dbms_output.put_line('+');
varNum := varNum + 1;
END LOOP;
dbms_output.put_line('=');
dbms_output.put_line(varSum);
END;
4.WHILE..LOOP..END LOOP
此语句的功能是当WHILE后面的语句条件成立时,重复执行循环体中的程序块。
WHILE...LOOP...END LOOP语句语法结构如下:
WHILE <条件表达式> LOOP
<程序块>
END LOOP;
再次实现1~4累加
SET ServerOutPut ON;
DECLARE
varNum INTEGER := 1;
varSum INTEGER := 0;
BEGIN
WHILE varNum <= 4 LOOP
varSum := varSum + varNum;
dbms_output.put_line(varNum);
IF varNum < 4 THEN
dbms_output.put_line('+');
END IF;
varNum := varNum + 1;
END LOOP;
dbms_output.put_line('=');
dbms_output.put_line(varSum);
END;
while循环
定义一个dept类型的表结构
手工添加3条数据
然后用循环将其数据添加到dept表中。
WHILE 条件表达式 LOOP
语句段;
END LOOP;
declare
type dept_type is table of dept%rowtype
index by binary_integer;
i number := 0;
v_dept_type dept_type;
begin
v_dept_type(0).deptno := 50;
v_dept_type(0).dname := '研发1部';
v_dept_type(0).loc := '北京';
v_dept_type(1).deptno := 60;
v_dept_type(1).dname := '研发2部';
v_dept_type(1).loc := '上海';
v_dept_type(2).deptno := 70;
v_dept_type(2).dname := '研发1部';
v_dept_type(2).loc := '广州';
while i <= 2 loop
insert into dept
(deptno, dname, loc)
values
(v_dept_type(i).deptno, v_dept_type(i).dname, v_dept_type(i).loc);
i := i + 1;
end loop;
end;
此语句定义一个循环变量,并指定循环变量的初始值和终止值。每循环一次循环变量自动加1.
FOR...IN...LOOP...END LOOP语句的语法如下
FOR <循环变量> IN <初始值>..<终止值> LOOP
<程序块>
END LOOP;
再次实现1~4累加
SET ServerOutPut ON;
DECLARE
varNum INTEGER := 1;
varSum INTEGER := 0;
BEGIN
FOR varNum IN 1..4 LOOP
varSum := varSum + varNum;
dbms_output.put_line(varNum);
IF varNum < 4 THEN
dbms_output.put_line('+');
END IF;
END LOOP;
dbms_output.put_line('=');
dbms_output.put_line(varSum);
END;
for循环
默认情况下每次循环,控制变量会自动增加1;
如果指定了reverse选项,则每次循环变量自动减少1。
FOR 循环变量 in [REVERSE] 初值表达式..终值表达式 LOOP
语句段;
END LOOP;
declare
type dept_type is table of dept%rowtype
index by binary_integer;
i number := 0;
v_dept_type dept_type;
begin
v_dept_type(0).deptno := 50;
v_dept_type(0).dname := '研发1部';
v_dept_type(0).loc := '北京';
v_dept_type(1).deptno := 60;
v_dept_type(1).dname := '研发2部';
v_dept_type(1).loc := '上海';
v_dept_type(2).deptno := 70;
v_dept_type(2).dname := '研发1部';
v_dept_type(2).loc := '广州';
for i in 0..v_dept_type.count - 1 loop
insert into dept
(deptno, dname, loc)
values
(v_dept_type(i).deptno, v_dept_type(i).dname, v_dept_type(i).loc);
end loop;
end;
八、异常处理
PL/SQL程序在运行过程中,可能会出现错误或异常现象
例如:无法建立到Oracle的连接或用0做除数。好的程序应该对可能发生的异常情况进行处理,异常处理代码在EXCEPTION块中实现
可以使用WHEN语句来定义异常。WHEN语句的使用方法如下:
EXCEPTION
WHEN <异常情况名> THEN
<异常处理代码>
WHEN <异常情况名> THEN
<异常处理代码>
...
WHEN OTHERS THEN
<异常处理代码>
预定义异常种类
http://blog.itpub.net/22678696/viewspace-1116535/
例子:
向一个NUMBER类型的变量赋值字符串时,导致异常的发生
SET ServerOutPut ON;
DECLARE
varNum NUMBER;
BEGIN
varNum := 'abc';
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line('VALUE_ERROR');
END;
使用SQLCODE , SQLERRM输出错误信息:
set serveroutput on;
declare
j integer:=0;
begin
j:='Jack';
--如果出错,直接去异常处执行,以下行不会输出
dbms_output.put_line('Value is setted');
exception
when others then
--在异常中默认使用sqlerrm输出信息
dbms_output.put_line(SQLCODE||'Other errors '||sqlerrm);
end;
预定义异常
begin
insert into dept values(10,test',test');
exception
when dup_val_on_index then
dbms_output.put_line('违反唯一约束!');
when others then
dbms_output.put_line('发生其他错误!');
end;