PL/SQL基础

最新推荐文章于 2022-08-11 19:49:33 发布

hx_wang007

最新推荐文章于 2022-08-11 19:49:33 发布

阅读量487

点赞数 3

分类专栏： oracle 文章标签：数据库 oracle plsql

本文链接：https://blog.csdn.net/hx_wang007/article/details/52156718

版权

oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1 PL/SQL概述

pl/sql是oracle对sql语言的过程化扩展。（在sql中增加了过程处理语句如分支，循环等）使sql语言具有过程处理能力。

1.1 PL/SQL的提出

pl/sql是面向过程的语言，结合了sql语言的数据操纵能力如增删改查和过程语言的数据处理能力。而不同数据库对sql有不同的扩展。

oracle: pl/sql
db2: sql/pl
sql server: t-sql

2 基础语法

[declare] 声明部分可选
begin
执行部分必须
如打印helloword
dbms_output.put_line(‘hello word’);
[exception] 异常处理部分可选
end;

2.1 基本类型变量

pl/sql 有以下几种类型的变量分别是
number,boolean,char,varchar2,date,long
我们通常在declare部分声明变量，可以采用以下两种方式对变量赋值

:= 比如 vsal number:=1000;(一般用于给单个变量赋值)
into 一般在pl/sql程序体中将某个字段值赋给变量(可以以多对多的形式对变量进行赋值) 比如

declare
vsal number;
vname varchar2;
begin
select sal,ename into vsal,vname from emp where empno=666;
end;

2.2 引用类型变量

我们也可以以表中某个字段的类型作为变量的类型，引用类型变量的语法格式为
变量名表名.列名%type
如 esal emp.sal%type;

2.3 记录型变量

记录型变量代表的是表中的一行数据，以一整行的数据作为一个变量类型
把记录型变量理解为一个数组，而在这个数组中的每一个元素代表这一行中的每一列数据。格式为
变量名表名%rowtype
记录变量分量的使用变量名.字段名
比如我们输出员工号为666的员工的姓名和薪水

set serveroutput on  --打开屏幕输出开关
declare
emp_info emp%rowtype;
begin
select * into emp_info from emp where empno=666;
dbms_output.put_line('姓名是：'||emp_info.ename||'薪水为'||emp_info.sal);
end;

2.4 选择语句的使用

基本形式:
if 条件 then 语句;
elsif 条件 then 语句;
else 语句;
end if;

2.5 循环语句的使用

2.5.1 while语句

基本形式:
while 条件 loop
执行部分
end loop;
表示当满足条件时执行执行部分的内容，不满足条件时退出循环。
示例：打印数字1-10

set serveroutput on
declare
num number:=1;
begin
while num<11 loop
dbms_output.put_line(num);
num:=num+1;
end loop;
end;

2.5.2 loop语句

基本形式:
loop
执行部分
exit when 条件；
执行部分
end loop;
这里需要说明的是当条件为true时退出循环，执行部分可以根据我们的需要写在上边或下边，或上下各写一部分。
示例：打印数字1-10

set serveroutput on
declare
num number:=1;
begin
loop
exit when num>10;
dbms_output.put_line(num);
num:=num+1;
end loop;
end;

2.5.3 for语句

基本形式：
for 循环变量 in [reverse] 下限..上限 loop
执行部分
loop;
这里需要说明的是for语句是一个可控制循环次数的循环控制语句。它有一个循环计数器来控制循环进行的次数。

循环变量用来做计数器，默认情况下是自动递增的。
reverse 加这个关键字表示计数器是递减的。
下限计数器的下限值，当计数器的值小于下限值是会终止循环。
上限计数器的上限值，当计数器的值大于上限值是会终止循环。

示例：打印倒序数字1-10

set serveroutput on
declare
num number:=1;
begin
for num in reverse 1..10
loop
dbms_output.put_line(num);
end loop;
end;

以上三种循环其实loop循环是用的最多的，在游标的使用中就能发现loop循环是最方便的。

3 游标(光标)

3.1 游标的概述

游标就是一个结果集，我们通过游标pl/sql程序可以一次性处理查询结果集中的一行或多行，并可以对该行数据进行特定的操作。从而为用户在处理数据的过程提供很大方便。

3.2 游标的基础语法和示例

创建一个游标
cursor 游标名 [(参数名数据类型)] is select语句
光标的遍历
fetch 光标名 into 变量名
光标的属性
-%found 如果至少影响到一行数据该属性true,否则false
-%notfound 与found属性相反
-%rowcount 返回受影响的行数
光标的使用过程
1 声明光标
2 打开光标
3 读取光标
4 关闭光标
示例：统计不同年份入职的员工人数

set serveroutput on
declare
--声明游标
cursor edate is select to_char(hiredate,'yyyy') from emp;
--为游标定义对应的变量
countyear varchar2(4);
--保存不同年份入职的员工数
count80 number:=0;
count81 number:=0;
count82 number:=0;
begin
--打开游标
open edate;
loop
--取一条记录赋给变量
fetch edate into countyear;
--取不到记录时退出循环
exit when edate%notfound;
--根据不同年份统计计数
if countyear='1980' then  count80:=count80+1;
elsif countyear='1981' then  count81:=count81+1;
else count82:=count82+1;
end if;
end loop;
--关闭游标
close edate;
dbms_output.put_line('1980年入职'||count80||'个');
dbms_output.put_line('1981年入职'||count81||'个');
dbms_output.put_line('1982年入职'||count82||'个');
end;

示例：查询某个部门的员工姓名（带参数的游标）

set serveroutput on
declare
--创建一个游标
cursor dcor(dno number) is select ename from emp where depno=dno;
--定义游标对应变量
dname emp.ename%type;
begin
--打开游标时传入实参
open dcor(30);
loop
--遍历游标
fetch dcor into dname;
exit when dcor%notfound;
dbms_output.put_line(dname);
end loop;
close dcor;
end;

4 例外（异常）

例外是程序设计语言提供的一种功能，用来对程序发生异常时的处理，提高程序的健壮性和容错性。
pl/sql异常处理代码放在exception代码块中，通常包括预定义异常和自定义异常。
常见的预定义异常有：

No_data_found(没有找到数据)
Too_many_rows(select …into语句匹配多行)
Zero_Divide(被零除)
Value_error(算术或转换错误)
Timeout_on_resource(在等待资源时发生超时)

语法格式为
begin
程序体
[exception]
异常处理语句
end;
实例：一个select语句匹配多行异常

set serveroutput on
declare
bname emp.ename%type;
begin
select ename into bname from emp where deptno=20;
exception
--异常类型，可以有多个
when Too_many_rows then dbms_output.put_line('匹配多行');
--其他异常
when others then dbms_output.put_line('其他异常');
end;

我们要尽可能的捕获所有例外。实际开发中我们可能需要根据业务逻辑抛出一些自定义异常。用法：
在declare部分定义一个变量，类型为exception.当需要抛出时用raise关键字，然后在exception中捕获就好了。
示例：

set serveroutput on
declare 
cursor c_emp is select ename from emp where deptno=50;
p_ename emp.ename%type;
--定义一个例外
no_emp_found exception;
begin
open c_emp;
--获取一条记录
fetch c_emp into p_ename;
--如果没有查到则抛出自定义例外
if c_emp%notfound then 
raise no_emp_found;
end if;
--此处，当前一句抛出例外执行完exception后，
--oracle会自动启动一个pmon(process monitor)的一个进程
--将pl/sql程序中未关闭的资源释放
--所以 close c_emp; 还是会执行的
close c_emp;
--捕获例外
exception 
when no_emp_found then dbms_output.put_line('没有该部门下的员工');
when others then dbms_output.put_line('其他例外');
end;