Oracle-PL/SQL基础

最新推荐文章于 2023-06-12 08:59:14 发布

脸白的不行

最新推荐文章于 2023-06-12 08:59:14 发布

阅读量247

点赞数

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

数据库专栏收录该内容

1 篇文章

订阅专栏

本文全面介绍了PL/SQL的基础知识，包括PL/SQL的特点、基本语法、变量类型、流程控制语句、游标操作等内容，并通过丰富的示例代码展示了如何在Oracle数据库中编写高效稳定的程序。

概述

PL/SQL简介

pl/sql（procedural language/sql）是Oracle在标准的sql语言上的扩展，pl/sql不仅允许嵌入Sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。

pl/sql是面向过程的语言。

这里写图片描述

不同数据库的SQL扩展

PL/SQL 是Oracle数据的SQL扩展。
SQL/PL 是DB2数据库的SQL扩展。
T-SQL 是SQL Server数据库的SQL扩展。

PL/SQL的必要性：

1、提高应用程序的运行性能。
2、模块化的设计思想。
3、减少网络传输量。
4、提高安全性。

PL/SQL编写规范

1、注释

单行注释–
sql>select * from emp where empno=7788; –取得员工信息

多行注释

/………………../

2、标识符号的命名规范

（1）当定义变量时，建议用v_作为前缀 v_sal。

（2）当定义常量时，建议用c_作为前缀 c_rate。

（3）当定义游标时，建议用_cursor作为后缀emp_cursor。

（4）当定义例外时，建议用e_作为前缀 e_error。

PL/SQL块

块（block）是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块；但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其他的pl/sql块。

块结构示意图

pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分。

declear：定义部分是从declare开始的，这部分是可选的。定义常量、变量、游标例外、复杂数据类型。

begin：执行部分是从begin开始的，这部分是必须的。

exception：例外处理部分是exception开始的，该部分可选的。

这里写图片描述

第一个PL/SQL程序

PLSQL中的命令窗口

SQL> set serveroutput on ;--打开输出
SQL> declare
--定义部分，如果没有定义，declare可以省略。可选，定义常量、变量、游标、例外、复杂数据类型
2 begin
--程序
3 dbms_output.put_line('66666666');
4 end;
5 /
66666666
PL/SQL procedure successfully completed
SQL> /
--表示执行上一个PL/SQL块。
66666666
PL/SQL procedure successfully completed

这里写图片描述

PL/SQL基础语法

程序结构

declare
说明部分（变量说明\游标声明\例外说明）
begin
语句序列(DML语句)
exception
例外处理语句
end ;
/

基本变量类型

定义

基本变量类型：
char 、 varchar2、date、number、boolean、long

举例：

var1 char(20);
married boolean := false ;
psal number(7,2);

栗子

SQL> set serveroutput on ;
SQL>
SQL> declare
2 -- 定义基本变量类型
3 v_name varchar2(20);--varchar2 字符串类型
4 v_num number(7,2);--number 数字类型
5 v_date date ;--date 日期类型
6
7 begin
8
9 v_name :='小工匠';
10 dbms_output.put_line('name:'||v_name);
11
12 v_num :=999;
13 dbms_output.put_line('num:'||v_num);
14
15 v_date :=sysdate;
16 dbms_output.put_line('数据库时间:'||v_date);
17
18 dbms_output.put_line('明天的时间:'||(v_date+1));
19 end ;
20 /
name:小工匠
num:999
数据库时间:22-JUN-16
明天的时间:23-JUN-16
PL/SQL procedure successfully completed
SQL>

引用型变量 %type

定义

使用%TYPE类型的变量

举例

v_name emp.ename%type ;

栗子

打印7369员工的姓名和薪水信息

SQL> set serveroutput on ;
SQL> declare
2 --打印 7369 员工的姓名和薪水信息
3 /* 可以使用基本类型来定义变量的类型，推荐使用引用型变量来定义变量类型*/
4 --vname varchar2(20);
5 --v_sal number;
6
7 --定义引用变量
8 v_name emp.ename%type ;
9 v_sal emp.sal%type;
10 begin
11 --业务操作，变量赋值 (两种赋值方式 1. v_sal := 20 第二种 into的方式 )
12 select ename, sal into v_name, v_sal from emp e where e.empno = 7369;
13 --输出信息
14 dbms_output.put_line(v_name||'的薪水是'||v_sal);
15 end;
16 /
SMITH的薪水是800
PL/SQL procedure successfully completed
SQL>

这里写图片描述

记录型变量 %rowtype

定义

%rowtype 记录型变量代表表中的一行，而一行中有很多列。

举例

emp_rec emp%rowtype

记录型变量分量的引用

手工赋值
emp_rec.ename := 'ADMIN'
或者
into 赋值之后，使用 emp_rec.enmae获取

栗子

SQL> set serveroutput on ;
SQL> declare
2 --打印 7369 员工的姓名和薪水信息
3
4 --定义记录型变量，得到 7369一行的所有列的信息
5 v_emp_rec emp%rowtype ;
6 begin
7 --业务sql
8 select * into v_emp_rec from emp a where a.empno=7369;
9 --获取姓名和薪水，并打印
10 dbms_output.put_line(v_emp_rec.ename||'的薪水是'||v_emp_rec.sal);
11 end ;
12 /
SMITH的薪水是800
PL/SQL procedure successfully completed
SQL>

这里写图片描述

if语句的使用

形式一

if 条件 then 语句1;
语句2；
end if;

形式2

if 条件 then 语句1;
else 语句2;
end if;

形式3 注意elsif

elsif没有e 且是连在一起的

if 条件 then 语句;
elsif 条件 then 语句;
else 语句;
end if;

栗子

/*
判断用户从键盘输入的数字
1、如何使用if语句
2、接收一个键盘输入（从键盘上获取的都是字符串）
*/
set serveroutput on ;
--接收一个键盘输入
--num ：地址值，含义是：在该地址上保存了输入的值，获取地址值上对应的值，需要使用 &
accept num prompt'请输入一个数字';
declare
--定义变量保存用户从键盘输入的数字这里并没有对异常进行处理，输入字符串，会抛异常
pnum number := &num;
begin
--执行if 语句进行条件判断提示信息不能使用双引号，必须使用单引号，否则报错
if pnum = 0 then dbms_output.put_line('输入的数字是'||pnum);
elsif pnum = 1 then dbms_output.put_line('输入的数字是'||pnum);
elsif pnum = 2 then dbms_output.put_line('输入的数字是'||pnum);
else dbms_output.put_line('其他数字'||pnum);
end if;
end;
/

这里写图片描述

循环语句的使用

形式1

while 条件 loop
.....
end loop;

当条件满足时，执行循环体，不满足时，退出循环体。

set serveroutput on ;
declare
--定义循环变量初始值为1 记得加上变量的类型否则报错
v_num number :=1 ;
begin
while v_num <=5 loop
--打印 v_num
dbms_output.put_line(v_num);
--变量+1 不能使用v_num++的形式，oracle不支持这种写法
v_num :=v_num+1 ;
end loop;
end;
/

这里写图片描述

形式2

loop
exit [when 条件];
.....
end loop;

条件成立时，退出循环体，不成立时执行循环体。

set serveroutput on ;
declare
--定义循环变量
v_num number :=1 ;
begin
loop
exit when v_num>10 ; --记得加标点符号
dbms_output.put_line(v_num);
v_num := v_num+1;
end loop ;
end;
/

这里写图片描述

推荐使用第二种，因为在控制游标的时候比较方便。

形式3

for i in 1 .. 10 loop
语句;
end loop;

set serveroutput on ;
declare
v_num number :=1 ;
begin
for i in 1 .. 5 loop -- .. 前后可以有空格也可以没有
dbms_output.put_line(v_num);
v_num := v_num+1;
end loop;
end;
/

这里写图片描述

游标

游标的概念

游标（游标）：一个结果集
不能把一个集合赋值给一个基本型变量，否则会抛出 too many rows的异常。

可带参数，可不带参数。

游标的语法

这里写图片描述

cursor 游标名 [(参数名数据类型[,参数名，数据类型]...)]
is select 语句;

比如

cursor c1 is select enama from emp;

操作游标的步骤

打开游标

open c1;(打开游标，执行查询 即执行游标的查询语句)

取一行游标的值

fetch c1 into v_name;(  取一行到变量中)

最开始时，游标指向集合的第一条记录，记录返回后，指针移动到下一条记录。

这里写图片描述

关闭游标

close c1 ;(关闭游标释放资源)

栗子

使用游标查询员工姓名和工资，并打印。

Loop循环游标

推荐写法

....
Loop
Fetch 游标名 InTo 临时记录或属性类型变量；
Exit When 游标名%NotFound；
End Loop；
.....

/*使用游标查询员工姓名和工资，并打印*/
set serveroutput on ;
declare
--定义游标推荐将游标和游标对应的变量写在一块，比较好维护
cursor cemp is select e.ename , e.sal from emp e ;
--定义游标对应的变量这里使用引用型变量
v_name emp.ename%type ;
v_sal emp.sal%type;
begin
--打开游标
open cemp ;
--循环获取游标中的值
loop
--取一条数据
fetch cemp into v_name,v_sal ; -- into 后变量的顺序一定要和定义游标时select的字段对应
exit when cemp%notfound ;
--打印
dbms_output.put_line(v_name||'的工资是'||v_sal);
end loop;
--关闭游标
if cemp%isopen then
close cemp;
dbms_output.put_line('Closing...');
end if;
end;
/

这里写图片描述

或者

For 循环游标

循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。

For 变量名 In 游标名
Loop
数据处理语句；
End Loop；

/*使用游标查询员工姓名和工资，并打印*/
set serveroutput on ;
declare
--定义游标推荐将游标和游标对应的变量写在一块，比较好维护
cursor cemp is select e.ename , e.sal from emp e ;
--定义游标对应的变量这里使用引用型变量
v_name emp.ename%type ;
v_sal emp.sal%type;
begin
--循环获取游标中的值
for c in cemp LOOP
select c.ename , c.sal into v_name,v_sal from dual;
dbms_output.put_line(v_name||'的工资是'||v_sal);
end loop;
end;
/

这里写图片描述

实例：给员工涨工资

/*
给员工涨工资。总裁涨1000，经理涨800，普通员工涨400；
*/
declare
--定义游标
cursor c_emp is select empno,empjob from emp;
--定义游标对应的变量
p_empno emp.empno%type;
p_empjob emp.empjob%type;
begin
--打开游标
open c_emp;
--取出一个员工
loop
fetch c_emp into p_empno,p_empjob;
exit when c_emp%notfound;
--判断职位
if p_empjob = 'PRESIDENT' then
update emp set sal=sal+1000 where empno=p_empno;
elsif p_empjob = 'MANAGER' then
update emp set sal= sal+800 where empno = p_empno;
else
update emp set sal=sal+400 where empno=p_empno;
end if;
end loop;
--关闭游标
if c_emp%isopen then
close c_emp;
end if ;
--提交事务，oracle默认的隔离级别是read committed ，不同连接只能读取提交之后的；
commit;
dbms_output.put_line('涨工资完成');
end;
/

游标的属性

游标的4个属性

%Found ：Fetch语句（获取记录）执行情况 True or False
%NotFound : 最后一条记录是否提取出 True or False
%ISOpen ：游标是否打开True or False
%RowCount ：游标当前提取的行数

游标数的限制

Oracle默认的一个会话最多可以打开300个游标.

可以通过使用 show parameter cursors; （表示模糊查询 %cursors%）

查看包含cursors的参数设置

这里写图片描述

修改游标数的限制

使用DBA权限的用户

alter system set open_cursors=400 scope = both;

其中scope的取值：both，memory，spfile

memory：表示只更改当前实例，不更改参数文件
spfile：表示只更改参数文件，不更改当前示例，数据库服务需要重启
both：表示上边两个同时更改

带参数的游标

注意定义（带参数）和打开游标（传递参数）时的区别。其余的和无参的游标一样。

set serveroutput on ;
declare
--定义带参数的游标
cursor cemp (dno number) is select ename from emp where deptno =dno;
--定义游标中对应的变量
v_name emp.ename%type;
begin
--打开游标传入对应的入参
open cemp(10);
--loop循环遍历游标
loop
fetch cemp into v_name;
exit when cemp%notfound;
dbms_output.put_line(v_name);
end loop;
close cemp;
end;
/

这里写图片描述

显式游标和隐式游标

上面介绍的是显式游标，下面说下隐式游标

DML操作和单行SELECT语句会使用隐式游标，它们是：

插入操作：INSERT
更新操作：UPDATE
删除操作：DELETE
单行查询操作：SELECT … INTO …

隐式游标的名字为SQL，这是由ORACLE 系统定义的。
对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。

格式调用为： SQL%

隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。

游标的属性有四种，如下所示。

隐式游标的属性返回值类型意义
SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数
SQL%FOUND 布尔型值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假

DECLARE
v_rows NUMBER;
BEGIN
--更新数据
UPDATE employees SET salary = 30000
WHERE department_id = 90 AND job_id = 'AD_VP';
--获取默认游标的属性值
v_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
--删除指定雇员；如果部门中没有雇员，则删除部门
DELETE FROM employees WHERE department_id=v_deptno;
IF SQL%NOTFOUND THEN
DELETE FROM departments WHERE department_id=v_deptno;
END IF;
END;

例外

例外的概念

在oracle中错误被叫做例外：分为系统例外和自定义例外。

系统例外比如：
- No_data_found 没有找到数据
- Too_many_rows select..into语句匹配多个行
- Zero_Divide 被零除
- Value_error 算数或转换错误，算术错误比如说负数开平方
- Timeout_on_resource 在等待资源时发生超时，常见于分部署数据库。

系统例外之no_data_found

/*系统例外 no_data_found*/
set serveroutput on ;
--查询empno为222的姓名
declare
--定义引用型变量
v_name emp.ename%type;
begin
--业务sql
select ename into v_name from emp where empno=222;
exception
when no_data_found then dbms_output.put_line('no data found');
when others then dbms_output.put_line('others exception');
end;
/

这里写图片描述

系统例外之too_many_rows

/*系统例外 too_many_rows */
set serveroutput on ;
--查询 10号部门的员工
declare
--定义引用型变量
v_name emp.ename%type;
begin
--业务sql
select ename into v_name from emp where deptno=10;
exception
when too_many_rows then dbms_output.put_line('too_many_rows');
when others then dbms_output.put_line('others exception');
end;
/

这里写图片描述

系统例外之zero_divide

/*系统例外 zero_divide */
set serveroutput on ;
declare
--定义引用型变量
v_num number;
begin
--业务sql
v_num := 1/0;
exception
when zero_divide then dbms_output.put_line('zero_divide');
dbms_output.put_line('0不能做除数');
when others then dbms_output.put_line('others exception');
end;
/

这里写图片描述

系统例外之value_error

/*系统例外 value_error */
set serveroutput on ;
declare
--定义引用型变量
v_num number;
begin
--业务sql
v_num := 'xiaogongjiang';
exception
when value_error then dbms_output.put_line('value_error');
when others then dbms_output.put_line('others exception');
end;
/

这里写图片描述

自定义例外

步骤

定义变量，类型是exception
使用raise抛出自定义意外

栗子

--自定义例外：（没有查找到的例外no_emp_found）
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;
/

这里写图片描述

PL/SQL调测

可以在pl/sql工具中新建测试窗口，调测过程和调测存过的方式一样，可以一步一步的跟踪sql执行的过程。

这里写图片描述

案例

运用瀑布模型完成PLSQL程序的设计

瀑布模型

1.需求分析
2.设计
- 2.1概要设计
- 2.2详细设计
3.编码coding
4.测试Testing
5.上线（部署）

拿到一个需求后，不找着急写程序，先分析明白了

sql语句
变量初始值
变量如何获取
….

案例：统计每年入职的员工人数

分析过程：
每年入职的员工人数
1.所有的年份集合–>定义cursor保存
2.每个员工的入职年份–>定义v_hiredate保存
3.每年入住的人数总和–>定义v_count_XX保存

先把大框架打起来

declare
---定义变量balabala
begin
---业务逻辑
end;
/

然后再一步一步的填充业务逻辑。

set serveroutput on ;
declare
--入职年份的游标
cursor hiredate_cursor is select to_char(hiredate,'yyyy') from emp;
--定义入职年份
v_hiredate varchar2(4);
--定义每个年份入职人员总数
v_count_80 number :=0;
v_count_81 number :=0;
v_count_82 number :=0;
v_count_87 number :=0;
begin
--打开游标
open hiredate_cursor ;
--loop循环遍历游标
loop
--取值
fetch hiredate_cursor into v_hiredate ;
exit when hiredate_cursor%notfound ;
if v_hiredate = '1980' then v_count_80 :=v_count_80+1 ;
elsif v_hiredate = '1981' then v_count_81 :=v_count_81+1 ;
elsif v_hiredate ='1982' then v_count_82 :=v_count_82+1;
elsif v_hiredate ='1987' then v_count_87 :=v_count_87+1;
end if ;
end loop;
--关闭游标
close hiredate_cursor;
dbms_output.put_line('总共入职人数:'||(v_count_80+v_count_81+v_count_82+v_count_87));--加括号
dbms_output.put_line('80入职的:'||v_count_80);
dbms_output.put_line('81入职的:'||v_count_81);
dbms_output.put_line('82入职的:'||v_count_82);
dbms_output.put_line('87入职的:'||v_count_87);
end;
/