PL/SQL

oracle中的编程语言

一. 概念：

SQL 结构化查询语言，用户和数据库之间沟通的语言

PL/SQL 使用过程功能扩展SQL，带有过程控制的SQL

二. 优点：

将过程化控制集成在SQL上，从而提高效率，优化数据库的性能

三. 块(快结构是最基本的单位)

概念：由代码块组成，每一个块形成一个任务或逻辑单元，通过逻辑条件、循环等来实现一些复杂的功能
组成：声明部分、执行部分、异常处理部分

基本语法：
declare
–声明的内容（变量，常量，游标等）
begin
–执行的内容(这个块要实现的任务或功能，如果能正常执行，则直接跳到end，如果不能正常执行，则执行exception)
exception–异常处理
end;

declare——宣布、声明 begin——开始 exception——异常 end——结束

dbms——数据库管理系统 output——输出 line——行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
begin
  dbms_output.put_line('你好，我叫张鹏飞');
end;

begin
  dbms_output.put('我们今天玩了警察抓小偷的游戏');--put会把内容保存在缓存中
  dbms_output.put_line('，朱精卫输了小游戏');--输出到行时，会先输出缓存内容，在输出本行内容
end;


begin
  dbms_output.put_line('我们今天玩了警察抓小偷的游戏');
  dbms_output.new_line;--把缓存中的信息输出到新的行中，如果缓存中没有信息，mew——line也会占一行
  dbms_output.put('，朱精卫输了小游戏');
  dbms_output.new_line;
end;

select * from emp;
select * from dept;

begin
  dbms_output.put('我们今天玩了警察抓小偷的游戏');--put会把内容保存在缓存中
  dbms_output.put_line('，朱精卫输了小游戏');--输出到行时，会先输出缓存内容，在输出本行内容
end;
/
begin
  dbms_output.put_line('我们今天玩了警察抓小偷的游戏');
  dbms_output.new_line;--把缓存中的信息输出到新的行中，如果缓存中没有信息，mew——line也会占一行
  dbms_output.put('，朱精卫输了小游戏');
  dbms_output.new_line;
end;

注意点：
1.在基本语法中，begin end 是必须存在的关键词，其他都可省略。
2.在 begin end 每一个单独的语句后面必定带有分号，end 后面也必定带有分号。
3.最外层两个单独的匿名块不能并列存在，需要用斜杠划分。
4.在 匿名块 中的 匿名块可以并列存在，不会报错。

变量于常量
变量：值可以变化的量，创建变量时可以不赋值。–创建好变量后，后期我们可以通过重新赋值的方式改变值。
常量：值不能变化的量，创建常量时必须立即赋值，并且值不能被改变。

–命名规则
1.英文字母开头
2.取名要有意义

语法：
declare
–声明的内容（变量，常量，游标等）
变量名 数据类型(字符长度)–声明一个变量
常量名 constant 数据类型(字符长度)–声明一个常量
begin
–执行的内容(这个块要实现的任务或功能，如果能正常执行，则直接跳到end，如果不能正常执行，则执行exception)
exception–异常处理
end;

变量的声明方法：

1
2
3
4
5
6
7
8
9
declare
  v_xuehao number(2);--声明一个变量，没有赋值
  v_xuehao2 number(2):= 2;--声明一个变量，并且赋值为1
begin
  v_xuehao := 1;--给变量赋值
  dbms_output.put_line(v_xuehao);
  v_xuehao2 := 3;--用新值改变旧值
  dbms_output.put_line(v_xuehao2);
end;

变量的赋值方法：

1.声明的同时赋值
2.在 begin end 中赋值–在使用这个变量之前，先赋值

常量的声明方式：

1
2
3
4
5
6
declare
  c_xuehao constant number(2):= 2;--声明一个常量，并且赋值为1
begin
  c_xuehao ：= 1;--报错，常量不能修改赋值
  dbms_output.put_line(v_xuehao);
end;

注意点：

  1. 常量在声明时必须给与赋值
  2. 常量无法在 begin end 赋值

例：
声明一个变量 v_sal 并赋值1000(周薪)，把一周的天数声明成常量c_day ,打印出’日薪为XXX’，要求保留两位小数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
declare
  v_sal number(4):=1000;
  c_day constant number(1):=7;
begin
  dbms_output.put('日薪为');
  dbms_output.put_line(round(v_sal/c_day,2));
end;

declare
  v_sal number(4):=1000;
  c_day constant number(1):=7;
begin
 dbms_output.put_line('日薪为：'||round(v_sal/c_day,2));
end;

declare
  v_sal number(4):=1000;
  c_day constant number(1):=7;
  v_rixin number(7,2);
begin
  v_rixin := v_sal/c_day;
  dbms_output.put_line('日薪为：'||v_rixin);
end;

声明变量，打印出’距离中秋还有XX天’

1
2
3
4
5
6
7
8
9
10
declare
  v_day number(2);
begin
  v_day:=to_date('2022-09-10','yyyy-mm-dd')-sysdate;
  dbms_output.put_line('距离中秋还有'||v_day||'天');
end;

begin
  dbms_output.put_line('距离中秋还有'||trunc((to_date('2022-09-10','yyyy-mm-dd')-sysdate))||'天');
end;

PLSQL块的嵌套

begin end 中可以放多个 begin end

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
begin
  dbms_output.put_line('这是外边的块');
  begin
    dbms_output.put_line('这是里边的块');
  end;
end;

begin
  dbms_output.put('这是外边的块');
  begin
    dbms_output.put_line('这是里边的块');
  end;
end;

begin
  begin
    dbms_output.put('这是里边的块');
  end;
  dbms_output.put_line('这是外边的块');
end;

begin
  begin
    dbms_output.put_line('111111');
    begin
      dbms_output.put_line('222222');
    end;
    begin
      dbms_output.put_line('333333');
    end;
  end;
  dbms_output.put_line('444444');
end;
----按照 1 2 3 4 的顺序打印
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
declare
  v_xuehao number(2) :=1;
begin
  declare
    v_chengji number(4,1):=95;
  begin
    dbms_output.put_line('小明的学号为：'||v_xuehao||'，成绩为：'||v_chengji||'。');
  end;
  dbms_output.put_line('小明的学号为：'||v_xuehao||'，成绩为：'||v_chengji||'。');--报错，识别不到v_chengji
end;


declare
  v_xuehao number(2) :=1;
  v_chengji number(4,1):=95;
begin
  declare

  begin
    dbms_output.put_line('小明的学号为：'||v_xuehao||'，成绩为：'||v_chengji||'。');
  end;
  dbms_output.put_line('小明的学号为：'||v_xuehao||'，成绩为：'||v_chengji||'。');
end;

注意点：
1.因为匿名块嵌套时，里面的块能识别外边块的声明，外边块不能识别里边块的声明。
所以在使用块嵌套时，所有的声明最好全部放在最外边的块中。

2.两个平级的块互相读不到对方的声明部分。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
declare

begin
  declare
      v_xuehao number(2) :=1;
  begin
    dbms_output.put_line('小明的学号为：'||v_xuehao||'，成绩为：'||v_chengji||'。');
  end;
  declare
    v_chengji number(4,1):=95;
  begin
    dbms_output.put_line('小明的学号为：'||v_xuehao||'，成绩为：'||v_chengji||'。');
  end;
end;

赋值方法

  1. := /英文的冒号+等号/ 直接赋值
  2. :=&弹窗提示名 /*英文的冒号 + 等号 + & + 提示 ( shift+7 ) */ 弹窗赋值–给变量赋予用户输入的值
  3. select into 赋值 /不直接赋值，而是把select语句中查到的值通过into赋给变量/

弹窗赋值使用方法一

declare
v_no number :=&学号;
v_name varchar2(20):=’&姓名’;–使用弹窗给字符串赋值时，’&弹窗提示名’ 需要用单引号括起来
v_class number :=&班级;
v_date date := to_date(‘&入学日期’,’yyyy/mm/dd’);
begin
dbms_output.put_line(‘学号为：’||v_no||’，姓名为：’||v_name||’，班级为：’||v_class||’，入学日期为：’||v_date||’。’);
end;

弹窗赋值使用方法二

declare
v_no number :=&学号;
v_name varchar2(20):=&姓名;–在弹窗内输入值时，字符串类型需要单引号括起来
v_class number :=&班级;
v_date date :=&入学日期;–在弹窗内输出日期时，需要使用转换函数把日期转为date类型
begin
dbms_output.put_line(‘学号为：’||v_no||’，姓名为：’||v_name||’，班级为：’||v_class||’，入学日期为：’||v_date||’。’);
end;

思考题：
1.在块中能不能直接使用 select 查询信息，比如查询员工’SMITH’的薪资–不能，会报错
select sal from emp where ename = ‘SMITH’;

declare
v_sal number;
begin
select sal into v_sal from emp where ename = ‘SMITH’;–可以通过select into 赋值的方法在块中使用select语句
dbms_output.put_line(‘史密斯的薪资为：’||v_sal);
end;

2.打印出emp表中 员工编号为7369的员工姓名与所在部门
declare
v_ename varchar2(20);
v_deptno number;
begin
select ename,deptno into v_ename,v_deptno from emp where empno=7369;
dbms_output.put_line(‘员工编号为7369的员工姓名’||v_ename||’与所在部门’||v_deptno);
end;

3.打印出emp表中 员工编号为7369的员工姓名与部门所在地
declare
v_ename varchar2(20);
v_loc varchar2(20);
begin
select ename, loc
into v_ename, v_loc
from emp a
join dept b
on a.deptno = b.deptno
where empno = 7369;
dbms_output.put_line(‘员工姓名为’ || v_ename || ‘部门所在地是’ || v_loc);
end;

声明变量（日期型），当手动输入一个日期后，打印出这是本年的第几周 格式为’XX在本年的第Y周’

1
2
3
4
5
declare 
v_date date :=to_date('&日期','yyyy-mm-dd');
begin
  dbms_output.put_line(v_date||'在本年的第'||to_char(v_date,'ww')||'周');
end;

打印出emp表中 薪资为5000 的员工姓名、薪资与部门名称

1
2
3
4
5
6
7
8
9
10
declare
v_ename varchar2(7);
v_sal number(5):=5000;
v_dname varchar2(10);
begin
  select ename,sal,dname into v_ename,v_sal,v_dname from emp a join dept b on a.deptno=b.deptno where sal=5000;
  dbms_output.put_line(v_ename||'  '||v_sal||'  '||v_dname);
end;

select ename,sal,dname  from emp a join dept b on a.deptno=b.deptno and sal=5000;--on 跟 where 的区别

声明一个数值型变量v_deptno，输入赋值
查询并打印部门编号为v_deptno且在此部门内工资最低的员工姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
declare
v_deptno number:=&部门编号;
v_ename varchar2(10);
begin
  select ename
    into v_ename
    from (select ename
            from emp
           where sal in (select min(sal) from emp where deptno = v_deptno)
             and deptno = v_deptno) where rownum =1; 
  dbms_output.put_line('部门编号'||v_deptno||'员工姓名'||v_ename);
end;


declare
v_deptno number:=&部门编号;
v_ename varchar2(10);
begin
  select ename
    into v_ename
    from (select ename
            from emp
           where sal = 3000
             and deptno = v_deptno) where rownum =1; 
  dbms_output.put_line('部门编号'||v_deptno||'员工姓名'||v_ename);
end;

思考题：
删除掉表内重复数据的方法？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create table emp_copy as select * from emp;
create table emp_copy1 as select * from emp_copy;
select * from v_emp_copy;
select * from emp_copy1;
select * from emp_copy;


 --创建表用于备份数据
create table v_emp_copy as select  empno,ename,job,mgr,hiredate,sal,comm,deptno from emp_copy  group by empno,ename,job,mgr,hiredate,sal,comm,deptno having count(1)>1;
select * from v_emp_copy;

--删除数据
delete from emp_copy where empno in (select empno from (select  empno,ename,job,mgr,hiredate,sal,comm,deptno,count(1) from emp_copy  group by empno,ename,job,mgr,hiredate,sal,comm,deptno having count(1)>1) group by empno);
select * from emp_copy for update;

--重新插入重复数据
insert into emp_copy select  * from v_emp_copy;
select * from emp_copy;
select * from emp;

--删除表释放空间
drop table v_emp_copy
drop view v_emp_copy

例题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare
v_deptno number:=&部门编号;
v_xingming varchar2(10);
begin
  select ename into v_xingming from emp where (sal,deptno) in(select min(sal),deptno from emp where deptno=v_deptno group by deptno);
  dbms_output.put_line('部门编号'||v_deptno||'员工姓名'||v_xingming);
end;

select ename from emp where (sal,deptno) in(select min(sal),deptno from emp where deptno=10 group by deptno);

declare
v_sal number;
begin
  select sal into v_sal from emp where sal = 3000;--报错，实际返回的行数多于一行
  dbms_output.put_line(v_sal);
end;

打印工资第5名的员工姓名

1
2
3
4
5
6
7
8
declare 
v_ename varchar2(10);
begin 
 select ename into v_ename from (select ename,rownum r from (select * from emp order by sal desc))where r  between  5 and 5;
 dbms_output.put_line('姓名：'||v_ename);
end;

select ename into v_ename from (select ename,rownum paixu from (select * from emp order by sal desc)) where paixu = 5;

打印出KING的员工编号和入职时间(精确到天)和对应的部门名称

1
2
3
4
5
6
7
8
declare
  v_empno number;
  v_date date;
  v_dname varchar2(10);
begin
  select empno,hiredate,dname into v_empno,v_date,v_dname from emp A join dept B on A.deptno = B.deptno and A.ename = 'KING';
  dbms_output.put_line('员工编号：'||v_empno||'，入职时间：'||v_date||'，部门名称：'||v_dname);
end;