文章目录
PLSQL 编程
- PL /SQL是一种高级数据库程序设计语言
- 该语言集成于数据库服务器中
- PL/SQL代码可以对数据进行快速高效的处理
PL/SQL包括两部分,一部分是数据库引擎部分;另一部分是可嵌入到许多产品(如C语言,JAVA语言等)工具中的独立引擎。可以将这两部分称为:数据库PL/SQL和工具PL/SQL
PL/SQL 块
PL/SQL 程序由三个块组成:即声明部分、执行部分、异常处理部分
PL/SQL 块的结构如下:
DECLARE
/* 声明部分: 在此声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数 */
BEGIN
/* 执行部分: 过程及SQL 语句 , 即程序的主要部分 */
EXCEPTION
/* 执行异常部分: 错误处理 */
END;
其中,执行部分是必须 (BEGIN 与 END)
PL/SQL块可以分为三类:
- 无名块:动态构造,只能执行一次。
- 子程序:存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。
- 触发器:当数据库发生操作时,会触发一些事件,从而自动执行相应的程序。
DECLARE
str varchar(20);
i number;
BEGIN
str := 'HelloWorld';
i := '1';
dbms_output.put_line(str);
dbms_output.put_line(i);
EXCEPTION
when others then
dbms_output.put_line('输出有误');
END;
DECLARE
str varchar(20);
i number;
BEGIN
str := 'HelloWorld';
i := 'a';
dbms_output.put_line(str);
dbms_output.put_line(i);
EXCEPTION
when others then
dbms_output.put_line('输出有误');
END;
注:
- str varchar2(18); 声明一个变量,变量名称在前,类型在后
- str := ‘HelloWorld’; 为变量str赋值, := 是一个赋值运算符
- dbms_output.put_line(str); 输出语句,相当于System.out.println();
在变量声明时,若使用了 NOT NILL ,则必须为变量指定初始值,若使用了 CONSTANT (常量),则必须为变量指定初值,并且初值不可改变。
DECLARE
age number not null := 1;
-- 数据类型 := 默认值
age constant number := 1;
数据类型
特殊类型
记录类型
相当于Java中的类
DECLARE
TYPE record_type is RECORD( -- 相当于Java中的类
name varchar2(20),
age number
);
V_stu record_type;
BEGIN
V_stu.name := '张三';
V_stu.age := 22;
dbms_output.put_line(V_stu.name || V_stu.age);
END;
引用类型
%type
将表中的列(或所有列)起一个名字用来引用
-- 引用类型(将表中的指定的列起一个别名)
-- 使用plsql程序来查询员工的姓名
DECLARE
emprec emp.ename%type;
BEGIN
select ename into emprec from emp where empno=7499;
dbms_output.put_line(emprec);
END;
%rowtype
-- 引用类型
-- 查询某一个员工的指定信息(ename、job)
DECLARE
e emp%rowtype;
BEGIN
select * into e from emp where empno=7499;
dbms_output.put_line(e.ename||' '||e.job);
END;
运算符
- 关系运算符
- 一般运算符
- 逻辑运算符
-- 在程序中输入一个值 根据输入的值 来输出不同的内容
DECLARE
done boolean;
BEGIN
done := &done; -- 变量值由用户输入
dbms_output.put_line(
case
when done is null then 'unknow'
when done then 'yes'
when not done then 'no'
end
);
END;
```k
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210304185014135.png)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210304185031960.png)
```sql
-- 循环输出10--1的数字
DECLARE
i number;
BEGIN
i := 10;
while i>0 loop
dbms_output.put_line(i);
i := i-1;
end loop;
END;
根据工号(empno)查询员工的信息:
-- 根据工号查询员工的姓名,年薪
DECLARE
emp_id emp.empno%TYPE := &emp_id;
emp_name emp.ename%TYPE;
year_sal emp.sal%TYPE;
BEGIN
select ename,(sal + NVL(comm,0))*12 into emp_name,year_sal
from emp where empno=emp_id;
dbms_output.put_line(emp_name||' '||year_sal);
END;
使用plsql完成对数据库赋值:
给emp表中插入一条记录:
-- 给Emp表中插入一条记录
DECLARE
v_ename varchar2(20) := 'bill';
v_sal number(7,2) := 1234.56;
v_deptno number(2) := 10;
v_empno emp.empno%Type := 1086;
BEGIN
insert into emp (empno,ename,sal,deptno)
values(v_empno,v_ename,v_sal,v_deptno);
commit;
END;
分支结构(if)
If分支:
- 语法1:
if 条件 then 语句;
End if;
- 语法2:
if 条件 then 语句1;
Else 语句2
End if;
- 语法3:
if 条件 then 语句1;
Elsif 条件 then 语句2;
Elsif 条件 then 语句3;
…
Else 语句n;
End if;
-- 从键盘输入数字1,则输出"您输入的值为1"
DECLARE
num number:=#
BEGIN
if num=1 then
dbms_output.put_line('您输入的值为1');
end if;
END;
-- 从键盘输入数字1 则输出 ‘您输入的值为1’ 否则 输出‘您输入的值不为1’
DECLARE
num number:=#
BEGIN
if num=1 then
dbms_output.put_line('您输入的值为1');
else
dbms_output.put_line('您输入的值不为1');
end if;
END;
-- 查询员工的薪资,如果薪资 < 1000 则输出工资等级为一级,1000--2000 为二级,2000--3000为三级,3000以上为四级
-- 7369 1086 7782 7902
DECLARE
v_sal emp.sal%type;
v_empno emp.empno%type := &v_empno;
BEGIN
select sal into v_sal from emp where empno=v_empno;
if v_sal<1000 then
dbms_output.put_line('工资等级为一级');
elsif v_sal>=1000 and v_sal<2000 then
dbms_output.put_line('工资等级为二级');
elsif v_sal>=2000 and v_sal<3000 then
dbms_output.put_line('工资等级为三级');
else
dbms_output.put_line('工资等级为四级');
end if;
END;
循环结构(loop)
语法:
-- 语法一:
while 条件 loop
输出语句;
end loop;
-- 语法二
loop
exit when 条件;
输出语句;
end loop;
-- 语法三
for var in start..end loop
输出语句;
end loop;
-- var-变量名 start-开始 end-结束
-- 使用loop 输出1~100
-- 方式一(when 条件 loop)
DECLARE
num number := 1;
BEGIN
while num <= 100 loop
dbms_output.put_line(num);
num := num + 1;
end loop;
END;
-- 方式二(exit when 条件)
DECLARE
num number := 1;
BEGIN
loop
exit when num > 100;
dbms_output.put_line(num);
num := num + 1;
end loop;
END;
-- 方式三(for var in start..end loop)
DECLARE
num number := 1;
BEGIN
for num in 1..100 loop
dbms_output.put_line(num);
end loop;
END;
游标(cursor)
查询返回多条记录的情况的处理,使用游标可以存储和查询返回的多条数据。
格式:
cursor 游标名[参数名1 数据类型,参数2 数据类型...] is select语句;
游标的使用步骤:
- 声明一个游标
- 打开游标: open 游标名 (之后可以查询游标)
- 从游标中取一行值: fetch 游标名 into 变量
- 关闭游标: close 游标名 (释放资源)
- 结束方式: exit when 游标名%notfound
实例:
-- 游标的使用
-- 查询emp表中的所有员工的编号、姓名、薪资(empno,ename,sal)
DECLARE
cursor allemp is select * from emp; -- 声明游标
pemp emp%rowtype; -- 声明变量,存储一条记录
BEGIN
open allemp; -- 打开游标
-- 使用loop循环输出所有员工的信息
loop
-- 从游标中取一条记录,存到变量pemp中
fetch allemp into pemp;
dbms_output.put_line(pemp.empno||' '||pemp.ename||' '||pemp.sal);
exit when allemp%notfound;
end loop;
close allemp;
END;
运行结果:
实例:
-- 按照员工的职位涨工资
-- SALESMAN 涨1000元,MANAGER 涨800元,其他员工涨400元
DECLARE
cursor cp is select * from emp1;
addsal emp1.sal%type;
pemp emp1%rowtype;
BEGIN
open cp;
loop
fetch cp into pemp;
exit when cp%notfound;
if pemp.job='SALESMAN' then
addsal := 1000;
elsif pemp.job='MANAGER' then
addsal := 800;
else
addsal := 400;
end if;
dbms_output.put_line(pemp.empno||' '||pemp.ename||' '||(pemp.sal+addsal));
end loop;
close cp;
END;
运行前:
运行后:
实例:
-- 编写plsql程序 为部门编号为30的员工涨奖金100
DECLARE
cursor cp(num emp1.deptno%type) is select * from emp1 where deptno=num;
pemp emp1%rowtype;
BEGIN
open cp(30);
loop
fetch cp into pemp;
exit when cp%notfound;
dbms_output.put_line(pemp.empno||' '||pemp.ename||' '||(NVL(pemp.comm,0)+100));
end loop;
close cp;
END;
运行前:
运行后:
存储过程
存储过程是在数据库系统中,一组为了完成特定功能的 SQL 语句,经过编译后存储在数据库中,用户通过指定存储过程中的名字并传递给相应的参数,来调用存储过程,执行并返回。
语法:
create (or replace) procedure 过程名
(参数名 in/out 数据类型) as
BEGIN
PLSQL 程序体;
END;
新建->程序窗口->空白
实例:
文件->新建->程序窗口->空白
-- 新建一个存储过程
create or replace procedure helloworld
begin
dbms_output.put_line('Hello');
end helloworld;
文件->新建->SQL窗口
-- 调用执行存储过程
begin
helloworld;
end;
运行结果:
实例:
-- 新建一个存储过程
--给指定的员工涨薪100,并打印涨薪前和涨薪后的工资
-- 由于给指定的员工涨薪 通过员工的工号 因此此时的存储过程就带有参数
create or replace procedure addSal(eno in number) is
pemp emp1%rowtype;
begin
select * into pemp from emp1 where empno=eno;
-- 更新数据
update emp1 set sal = sal + 100 where empno=eno;
dbms_output.put_line('涨薪前: '||pemp.sal||' 涨薪后: '||(pemp.sal+100));
end addsal;
-- 调用执行存储过程
begin
addSal(eno=>7499); -- 名称传参
-- addSal(7499); -- 位置传参
commit;
end;
运行结果:
存储函数
语法:
create or replace function 函数名(参数 in/out 数据类型) return 数据类型 is 结果变量 数据类型;
BEGIN
函数体;
return (结果变量);
END;
存储过程和存储函数的区别:
- 一般,存储过程和存储函数的区别在于,存储函数可以有一个返回值,而存储过程是没有返回值的。
- 但是,过程和函数都可以通过 out 指定一个或多个输出函数,故我们也可以用 out 函数,在过程和函数中实现返回值的操作。
实例1:
-- 使用函数来查询指定员工的年薪
create or replace function empincome(eno in emp.empno%type)
return number
is psal emp.sal%type; -- 返回结果的时候,结果的保存变量
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno = eno;
return psal*12+NVL(pcomm,0);
end empincome;
-- 调用函数
declare
income number;
begin
income := empincome(7369);
dbms_output.put_line(income);
end;
运行结果:
实例2:
-- 存储过程的实现,使用out参数来返回结果
create or replace procedure empincomepro(eno in emp.empno%type,yearsal out number)
is
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno = eno;
yearsal := psal*12 + NVL(pcomm,0);
end empincomepro;
调用存储过程:
传参方式:位置传参、名称传参、混合传参
-- 存储过程的调用(使用不同的传参方式)
-- 位置传参
declare
income number;
begin
empincomepro(7369,income);
dbms_output.put_line(income);
end;
-- 名称传参
declare
income number;
begin
empincomepro(eno => 7369,yearsal => income);
dbms_output.put_line(income);
end;
-- 名称传参(不受位置的限制)
declare
income number;
begin
empincomepro(yearsal => income,eno => 7369);
dbms_output.put_line(income);
end;
-- 混合传参
declare
income number;
begin
empincomepro(7369,yearsal => income);
dbms_output.put_line(income);
end;
运行结果:
实例3:
-- 获取某部门的工资总和及部门人数
create or replace function get_salary(
dept_no number, -- 没有明确指定in/out,默认in
emp_count out number
)
return number is v_sum number; -- 返回结果,结果的保存变量
begin
select sum(sal),count(*) into v_sum,emp_count from emp where deptno = dept_no;
return v_sum;
end get_salary;
-- 调用函数(查看30 部门的工资总和及部门人数)
declare
v_num number; -- 输出参数,保存部门人数
v_sum number; --返回值,保存工资总和
begin
v_sum := get_salary(30,emp_count=>v_num);
dbms_output.put_line('工资总和为:'||v_sum||',人数为:'||v_num);
end;
运行结果:
在使用混合传参的时候,如果第一参数使用了名称表示法,则其后的所有的参数的传递必须使用名称表示法。
异常的处理:
-- 获取某部门的工资总和及部门人数
create or replace function get_salary(
dept_no number, -- 没有明确指定in/out,默认in
emp_count out number
)
return number is v_sum number; -- 返回结果,结果的保存变量
begin
select sum(sal),count(*) into v_sum,emp_count from emp where deptno = dept_no;
return v_sum;
exception
when NO_DATA_FOUND then
dbms_output.put_line('您所查询的数据不存在');
when OTHERS then
dbms_output.put_line('发生异常');
end get_salary;
-- 调用函数(查看30 部门的工资总和及部门人数)
declare
v_num number; -- 输出参数,保存部门人数
v_sum number; --返回值,保存工资总和
v_deptno number;
begin
v_sum := get_salary(&v_deptno,emp_count=>v_num);
dbms_output.put_line('工资总和为:'||v_sum||',人数为:'||v_num);
end;
运行:
运行:(emp 表中没有40部门)
触发器
触发器是一个与表相关连的、存储 PLSQL 的程序,每当发出一个特定的数据操作语句时,Oracle 会自动执行的一个 PLSQL 程序。
-
触发器的应用时机:
- 数据的确认
- 实施复杂的安全性检查
- 做审计,跟踪表上所作的数据操作等
- 数据的备份和同步
-
触发器的类型
- 语句级触发器:在指定的操作语句之前或之后执行一次,这条语句可以影响任意行数
- 行级触发器:(for each row)触发语句作用的每一条记录都被触发;在行级触发器中,可以使用 old 和 new 伪记录来记录识别值的状态。
语法(语句级触发器):
create or replace trigger 触发器名称
(brfore/after)
(delete/insert/update of 列名) on 表名
for each row (when 条件)
begin
PLSQL 程序;
end;
示例(语句级触发器):
-- 插入员工信息后,打印一句话:新员工插入成功
create or replace trigger insertprint
after insert on emp1
declare
begin
dbms_output.put_line('新员工插入成功');
end insertprint;
当我们执行添加时:
insert into emp1(empno,ename,job,sal,comm) values(7788,'Mike','快递员',6000,300);
会有如下提示:
在文件中的显示:
可以看到,当我们定义好触发器之后,我们不需要自己主动的去调用,当操作满足触发器的触发条件时,Oracle 会自动执行触发器。
行级触发器中存在两个伪变量:
old | new | |
---|---|---|
insert | 所有的字段都是null | 将要插入的数据 |
update | 更新以前改行的值 | 更新后的值 |
delete | 删除以前改行的值 | 所有的字段都是空 |
示例:
-- 当更新员工的薪资时,涨薪之后的薪资的值一定要大于涨薪之前的值
create or replace trigger updateSal
before update of sal on emp1
for each row
begin
if
:old.sal>=:new.sal
then
raise_application_error(-20002,'涨薪后的薪资必须大于涨薪前的薪资'); -- -20002
end if;
end updateSal;
-- 测试
update emp1 set sal = sal - 100;
运行结果:
使用触发器完成删除记录的备份:
当删除 emp1 表中的数据时,将删除的记录保存到删除备份表中。
- 创建备份记录表(将表emp1 的列名复制)
create table emp_bak as select * from emp1 where 1=2;
- 触发器
-- 当删除 emp1 表中的数据时,将删除的记录保存到删除备份表中。
create or replace trigger delemp_bak
before delete on emp1 for each row
declare
begin
insert into emp_bak(empno,ename,job,mgr,sal,comm,hiredate,deptno)
values(:old.empno,:old.ename,:old.job,:old.mgr,:old.sal,:old.comm,:old.hiredate,:old.deptno);
end deptno_bak;
- 删除emp1 表中的数据
delete from emp1 where empno = 7788;
- 查询 emp_bak 表中的数据
select * from emp_bak;
查询结果:
包
包的使用格式:
create package 包名 as 存储过程或函数
- 定义包
create package emp_addsal as
procedure getsal(eno emp.empno%type);
end;
- 包体
-- 包体
create or replace package boby emp_addsal as
procedure getsal(eno emp.empno%type) is
v_esal emp.sal%type;
begin
select sal into v_esal from emp where empno=eno;
dbms_output.put_line(v_esal);
end getsal;
end emp_addsal;
- 执行
-- 执行
declare
eno emp.empno%type:=&eno;
begin
emp_addsal.getsal(eno);
end;
数据库管理和设计
数据库备份
- 数据库的三种备份方法:导入/导出(EXP/IMP)、热备份和冷备份。导出备份是逻辑备份,热备份和冷备份是物理备份。
- 冷备份需要备份的文件