一、PL/SQL的基本概念:
PL/SQL(Procedural Language/SQL)是一种过程化语言,在PL/SQL中可以通过IF语句或
LOOP语句实现控制程序的执行流程,甚至可以定义变量,以便在语句之间传递数据信息,这样PL/SQL语言
就能够实现操控程序处理的细节过程。
二、PL/SQL块结构:
声明部分(用DECLARE开头)、执行部分(以BEGIN开头)和异常处理部分(以EXCEPTION开头)
[DECLARE]
--声明部分,可选
BEGIN
--执行部分,必须
[EXCEPTION]
--异常处理部分,可选
END
案例1:
-- 定义一个PL/SQL代码块,计算两个整数的和与这两个整数的差的商
declare
a int:=100;
b int:=200;
c number;
begin
c:=(a+b)/(a-b);
dbms_output.put_line(c);
exception
when zero_divide then
dbms_output.put_line('除数不能为0');
end;
三、代码注释和标识符
1、单行注释:由两个连接字符"--"开始
2、多行注释:/* */
3、PL/SQL字符集:所有PL/SQL程序元素都是右一些字符序列组合而成
合法的字符集主要包括以下内容:
1)大写和小写字母:A-Z或者a-z
2)数字:0-9
3)非显示的字符:制表符、空格和回车
4)数学符号:+ - * / > < =等
5)间隔符:包括(),{},[],? ; , @ # % $ &等
四、数据类型和定义变量和常量
1、数值类型
2、字符类型
3、日期类型
4、布尔类型
5、特殊数据类型:
1)%TYPE类型:使用%TYPE关键字可以声明一个与指定列名称相同的数据类型 (%就类似于java中的.)
declare
var_job emp.job%type;
案例如下:
-- 在scott模式下,使用%type类型的变量输出emp表中编号为7369的员工名册和职务信息
declare
var_ename emp.ename%type; -- 声明与ename列类型相同的变量
var_job emp.job%type;-- 声明与job列类型相同的变量
begin
select ename,job
into var_ename,var_job from emp
where empno = 7369;
dbms_output.put_line(var_ename||'的职务是'||var_job);
end;
2)RECORD类型(记录类型):使用该类型的变量可以储存由多个列值组成的一行数据。
语法:
type record_type is record(
var_member1 data_type [not null][:default_value],
var_membern data_type [not null][:default_value]
)
record_type:表示要定义的记录类型的名称
var_member1:表示该记录类型的成员变量名称
data_type:表示成员变量的数据类型
案例:
-- 声明一个记录类型emp_type,然后使用该类的变量存储emp表中的一条记录信息,并输出这条记录信息
declare
type emp_type is record --声明record类型emp_type 此时emp_type只是声明记录类型,并不能直接用
(
var_ename varchar2(20),--定义字段/成员变量
var_job varchar2(20),
var_sal number
);
empinfo emp_type;--定义变量 此时才能直接通过 . 来访问
begin
select ename,job,sal
into empinfo
from emp where empno = 7369;--检索数据
-- 输出雇员信息
dbms_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||'、工资是'||empinfo.var_sal);
end;
3)%ROWTYPE类型:结合了%TYPE类型和RECORD类型变量的优点,它可以根据数据表中行的结构定义一种特殊的类型
用来存储从数据表中检索到的一行的数据。
rowVar_name table_name%rowtype;
rowVar_name:表示可以存储一行数据的变量名
table_name:指定的表名
案例:
--声明一个%ROWTYPE类型的变量rowVar_emp,然后使用该变量存储emp表中的一行数据
declare
rowVar_emp emp%rowtype;--定义能够存储emp表中一行数据的变量 可以直接访问
begin
select * into rowVar_emp
from emp
where empno = 7369;
dbms_output.put_line('雇员'||rowVar_emp.ename||'的编号是'||rowVar_emp.empno||',职务是'||rowVar_emp.job);
end;
五、定义变量和常量
1、定义变量
<变量名> <数据类型>[(长度):=<初始值>];
var_countryname varchar2(50):='中国';
2、定义常量
<常量名> constant<数据类型>:=<常量值>;
con_day constant integer:=365;
六、流程控制语句
1、选择语句
1)if...then语句
if<condition_expression> then
plsql语句
end if;
案例:
-- if...then案例
declare
var_name1 varchar2(50);
var_name2 varchar2(50);
begin
var_name1:='East';
var_name2:='xiaoke';
if length(var_name1)<length(var_name2) then
dbms_output.put_line('字符串'||var_name1||'的长度比字符串'||var_name2||'的长度小');
end if;
end;
2)if..then...else
if<condition_expression> then
plsql语句
else
plsql语句
endif;
案例1:
-- if..then..else案例
declare
age int:=55;
begin
if age>=56 then
dbms_output.put_line('您可以申请退休了');
else
dbms_output.put_line('您小于56岁,不可以申请退休了!');
end if;
end;
3)if...then...elsif:实现多分支判断选择
if<conditon_expression1> then
plsql语句
elsif<condtion_expression2> 注意是elsif 而不是else if
plsql语句
else
plsql语句
end if;
案例1:
declare
month int:=10;--定义整型变量并赋值
begin
if month>=0and month<=3 then --判断春季
dbms_output.put_line('这是春季');
elsif month>=4 and month<=6 then
dbms_output.put_line('这是夏季');
elsif month>=7and month<=9 then
dbms_output.put_line('这是秋季');
elsif month>=10 and month<=12 then
dbms_output.put_line('这是冬季');
else
dbms_output.put_line('对不起,月份不合法!');
end if;
end;
4)case语句
case<selector>
when <expression_1> then plsql语句1;
when <expression_2> then plsql语句2;
else
plsql语句;
end case;
案例:
-- case语句
declare
season int:=3;
aboutinfo varchar2(50);--储存月份信息
begin
case season --判断季节
when 1 then
aboutinfo:=season||'季节包括1,2,3月份';
when 2 then
aboutinfo:=season||'季节包括4,5,6月份';
when 3 then
aboutinfo:=season||'季节包括7,8,9月份';
else
aboutinfo:=season||'季节不合法';
end case;
dbms_output.put_line(aboutinfo);
end;
2、循环结构
1)loop语句
loop loop和end loop 可理解为java中的“大括号”
plsql语句
exit when end_condition
end loop;
案例:
declare
sum_i int:=0;
i int:=0;
begin
loop
i:=i+1;
sum_i:=sum_i+i;
exit when i = 100;
end loop;
dbms_output.put_line('前100个自然数的和是:'||sum_i);
end;
2)while语句
while condition_expression loop
plsql语句
end loop;
案例:
--while语句
declare
sum_i int:=0;
i int:=0;
begin
while i<=99 loop
i:=i+1;
sum_i:=sum_i+i;
end loop;
dbms_output.put_line('前100个自然数的和是:'||sum_i);
end;
3)for语句
for variable_counter_name in [reverse] lower_limit..upper_limit loop reverse为反转的意思
plsql语句
end loop;
variable_counter_name:表示一个变量,通常为整数类型,用来作为计数器
lower_limit:计数器的下限值
upper_limit:计数器的上限值
案例:
declare
sum_i int:=0;
begin
for i in reverse 1..100 loop
if mod(i,2)=0 then
sum_i:=sum_i+i;
end if;
end loop;
dbms_output.put_line('前100个自然数中偶数之和是:'||sum_i);
end;
============PL/SQL游标===========
一、游标的基本概念:
游标提供了一种从表中检索数据并进行操作的灵活手段,游标主要用在服务器上,处理由客户端发送给服务器的SQL语句。
游标的作用:就相当于指针,通过游标PL/SQL程序可以一次处理查询结果集中的一行
并可以对该行数据执行特定操作,从而为用户在处理数据的过程中提供了很大方便。
游标分类:通过游标操作数据主要使用显式游标和隐式游标
二、显式游标
概念:显式游标是由用户声明和操作的一种游标,通常用于操作查询结果集(即由select语句返回的查询结果)
处理数据的步骤:
1)声明游标
cursor curname[input_parameter1,[,input_parameter2]]
[return ret_type]
is select_sentence;
cur_name:表示所声明的游标名称
ret_type:表示执行游标操作后的返回值类型
select_sentence:游标所使用的select语句,它为游标的反复读取提供结果集
input_parameter1:作为游标的输入参数,可以有多个
声明一个游标,用来读取emp表中职务为销售员(SALESMAN)的雇员信息
declare
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp
where job=var_iob;
2)打开游标
open cur_name(para_value1,[para_value2]...)
open cur_emp('MANAGER');
在打开游标过程中,程序首先将符合条件的记录送入内存中,然后在将指针,指向第一条记录。
3)读取游标
读取游标就是将结果集中的数据保存到变量中,读取游标使用fetch..into
fetch cur_name into {variable}
4)关闭游标
游标使用完毕后需要关闭,以释放系统资源
close cur_name
注意:读取游标可能是个反复操作的步骤,因为游标每次只能读取一行数据,所以对于多条记录
需要反复读取,直到游标读取不到数据为止。
--游标案例
-- 声明一个检索emp表中雇员信息的游标,然后打开游标,并制定检索职务是MANAGER的雇员信息
--接着使用fetch..into语句和while循环读取由表中的所有雇员信息最后输出读取的雇员信息
declare
/*声明游标,检索雇员信息*/
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp where job=var_job;
type record_emp is record --声明一个记录类型
(
/*定义当前记录的成员变量*/
var_empno emp.empno%type,
var_ename emp.ename%type,
var_sal emp.sal%type
);
emp_row record_emp;--声明一个record_emp类型的变量
begin
open cur_emp('MANAGER');--打开游标
fetch cur_emp into emp_row;--先让指针只想结果集中的第一行,并将值保存到emp_row中
while cur_emp%found loop
dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||'的工资是'||emp_row.var_sal);
fetch cur_emp into emp_row;--让指针只想结果集中的下一行,并将值保存到emp_row中
end loop;
close cur_emp;--关闭游标
end;
三、游标属性
无论是显式游标还是隐式游标,都具有%found、%notfound、%isopen、%rowcount 4个属性
%notfound fetch是否提到数据 没有true 提到false
%found fetch是否提到数据 有true 没提到false
%rowcount 返回sql语句影响的行数
%isopen 布尔值 游标是否打开
--游标属性案例
--声明一个游标用于检索指定员工编号的雇员信息,然后使用游标%found属性来判断是否检索到指定员工编号的雇员信息
declare
var_ename varchar2(50);--声明变量,用来存储雇员名称
var_job varchar2(50);--声明变量,用来存储雇员的职务
/*声明游标检索指定员工编号的雇员信息*/
cursor cur_emp --定义游标检索指定编号的记录信息
is select ename,job
from emp
where empno=7499;
begin
open cur_emp;--打开游标
fetch cur_emp into var_ename,var_job;
if cur_emp%found then --若检索到数据记录,则输出雇员信息
--dbms_output.put_line('编号是7499的雇员名称为:'||var_ename||',职务是:'+var_job);
dbms_output.put_line('编号是7499的雇员名称为:'||var_ename||'的职务是:'||var_job);
else
dbms_output.put_line('无数据记录');--提示无记录信息
end if;
end;
四、隐式游标
在执行SQL语句时,Oracle会自动创建一个隐式游标。这个游标在内存中处理该语句的工作区域。
隐式游标主要是处理数据操纵语句(UPDATE、DELETE语句)
由于隐式游标也有属性,当使用隐式游标的属性时,需要在属性前面加上隐式游标的默认名称-SQL
--隐式游标案例
--在SCOTT模式下,把emp表中销售人员(即SALESMAN)的工资上调20%,然后使用隐式游标sql的%rowcount属性输出上调工资的员工数量
begin
update emp
set sal = sal*(1+0.2)
where job = 'SALESMAN';
if sql%notfound then
dbms_output.put_line('没有雇员需要上调工资');
else
dbms_output.put_line('有'||sql%rowcount||'个雇员工资上调20%');
end if;
end;
五、通过for语句循环游标
for var_auto_record in cur_name loop
plsql语句
end loop;
var_auto_record:自动的RECORD类型的变量,可以是任意合法的变量名称
cur_name:指定的游标名称
案例1:
-- 使用隐式游标和for语句检索出职务是销售员的雇员信息并输出
begin
for emp_record in (select empno,ename,sal from emp where job='SALESMAN')
loop
dbms_output.put('雇员编号:'||emp_record.empno);
dbms_output.put(';雇员名称:'||emp_record.ename);
dbms_output.put_line(';雇员工资:'||emp_record.sal);
end loop;
end;
案例2:
--使用显式游标和for语句检索出部门编号是30的雇员信息并输出
declare
cursor cur_emp is
select * from emp
where deptno = 30;
begin
for emp_record in cur_emp
loop
dbms_output.put('雇员编号:'||emp_record.empno);
dbms_output.put('雇员名称:'||emp_record.ename);
dbms_output.put_line('雇员职务:'||emp_record.job);
end loop;
end;