PL/SQL学习总结

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/l1357852347/article/details/76033102

​ 最近好久没写博客了,感觉有点怠惰了,感觉需要勤勉一下啦,这里总结一下前不久自学的plsql的一些知识点,并结合网上(实在不知道真正的出处)找到的plsql的训练题进行总结。

什么是PL/SQL

​ PL/SQL(Procedure Language/SQL)是Oracle对sql语言的过程化扩展,指在SQL命令语言中增加了过程处理语句(如分支、循环等),使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来,使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

基本语法结构

准备工作

​ 我使用的是HR用户的表,所以首先需要登录HR用户。

基本格式

--declare

--声明的变量、类型、游标

begin

--程序的执行部分(类似于java里的main()方法)

dbms_output.put_line('helloworld');

--exception

--针对begin块中出现的异常,提供处理的机制

--when .... then ...

--when .... then ...

end;

变量

  • 使用一个变量
declare

--声明一个变量

v_name varchar2(25);

begin

--通过 select ... into ... 语句为变量赋值

select last_name into v_name

from employees

where employee_id = 186;

-- 打印变量的值

dbms_output.put_line(v_name);

end;
  • 使用多个变量
declare

--声明变量

v_name varchar2(25);

v_email varchar2(25);

v_salary number(8, 2);

v_job_id varchar2(10);

begin

--通过 select ... into ... 语句为变量赋值

--被赋值的变量与SELECT中的列名要一一对应

select last_name, email, salary, job_id into v_name, v_email, v_salary, v_job_id

from employees

where employee_id = 186;

-- 打印变量的值

dbms_output.put_line(v_name || ', ' || v_email || ', ' || v_salary || ', ' || v_job_id);

end;

类型

  • 自定义记录类型
declare

--定义一个记录类型

type emp_record is record(

v_name varchar2(25),

v_email varchar2(25),

v_salary number(8, 2),

v_job_id varchar2(10));

--声明自定义记录类型的变量

v_emp_record emp_record;

begin

--通过 select ... into ... 语句为变量赋值

select last_name, email, salary, job_id into v_emp_record

from employees

where employee_id = 186;

-- 打印变量的值

dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' || v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);

end;
  • 使用 %type 定义变量,动态的获取数据的声明类型
declare

--定义一个记录类型

type emp_record is record(

v_name employees.last_name%type,

v_email employees.email%type,

v_salary employees.salary%type,

v_job_id employees.job_id%type);

--声明自定义记录类型的变量

v_emp_record emp_record;

begin

--通过 select ... into ... 语句为变量赋值

select last_name, email, salary, job_id into v_emp_record

from employees

where employee_id = 186;

-- 打印变量的值

dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' || v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);

end;
  • 使用 %rowtype
declare

--声明一个记录类型的变量

v_emp_record employees%rowtype;

begin

--通过 select ... into ... 语句为变量赋值

select * into v_emp_record

from employees

where employee_id = 186;

-- 打印变量的值

dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' || v_emp_record.hire_date);

end;

通过变量实现CRUD操作

declare

v_emp_record employees%rowtype;

v_employee_id employees.employee_id%type;

begin

--使用赋值符号位变量进行赋值

v_employee_id := 186;

--通过 select ... into ... 语句为变量赋值

select * into v_emp_record

from employees

where employee_id = v_employee_id;

-- 打印变量的值

dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||

v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' || v_emp_record.hire_date);

end;
declare

v_emp_id employees.employee_id%type;

begin

v_emp_id := 109;

delete from employees

where employee_id = v_emp_id;

--commit;

end;

条件判断

使用 IF … THEN … ELSIF … THEN …ELSE … END IF

要求: 查询出 150号 员工的工资, 若其工资大于或等于 10000 则打印 ‘salary >= 10000’;

若在 5000 到 10000 之间, 则打印 ‘5000<= salary < 10000’; 否则打印 ‘salary < 5000’

declare

v_salary employees.salary%type;

begin

--通过 select ... into ... 语句为变量赋值

select salary into v_salary

from employees

where employee_id = 150;

dbms_output.put_line('salary: ' || v_salary);

-- 打印变量的值

if v_salary = 10000 then

dbms_output.put_line('salary = 10000');

elsif v_salary = 5000 then

dbms_output.put_line('5000 = salary  10000');

else

dbms_output.put_line('salary  5000');

end if;

使用 CASE … WHEN … THEN …ELSE … END

对于相同的题目可以使用case…when的方式:

declare

v_sal employees.salary%type;

v_msg varchar2(50);

begin

select salary into v_sal

from employees

where employee_id = 150;

--case 不能向下面这样用

/*

case v_sal when salary = 10000 then v_msg := '=10000'

when salary = 5000 then v_msg := '5000= salary  10000'

else v_msg := 'salary  5000'

end;

*/

v_msg :=

case trunc(v_sal / 5000)

when 0 then 'salary  5000'

when 1 then '5000= salary  10000'

else 'salary = 10000'

end;

dbms_output.put_line(v_sal ||','||v_msg);

end;

使用 CASE … WHEN … THEN … ELSE … END

要求: 查询出 122 号员工的 JOB_ID, 若其值为 ‘IT_PROG’, 则打印 ‘GRADE: A’;

‘AC_MGT’, 打印 ‘GRADE B’,

‘AC_ACCOUNT’, 打印 ‘GRADE C’;

否则打印 ‘GRADE D’

declare

--声明变量

v_grade char(1);

v_job_id employees.job_id%type;

begin

select job_id into v_job_id

from employees

where employee_id = 122;

dbms_output.put_line('job_id: ' || v_job_id);

--根据 v_job_id 的取值, 利用 case 字句为 v_grade 赋值

v_grade :=

case v_job_id when 'IT_PROG' then 'A'

when 'AC_MGT' then 'B'

when 'AC_ACCOUNT' then 'C'

else 'D'

end;

dbms_output.put_line('GRADE: ' || v_grade);

end;

循环结构

LOOP … EXIT WHEN … END LOOP

使用循环语句打印 1 - 100:

declare

--初始化条件

v_i number(3) := 1;

begin

loop

--循环体

dbms_output.put_line(v_i);

--循环条件

exit when v_i = 100;

--迭代条件

v_i := v_i + 1;

end loop;

end;

WHILE … LOOP … END LOOP

declare

--初始化条件

v_i number(3) := 1;

begin

--循环条件

while v_i = 100 loop

--循环体

dbms_output.put_line(v_i);

--迭代条件

v_i := v_i + 1;

end loop;

end;

FOR … LOOP … END LOOP

begin

for i in 1 .. 100 loop

dbms_output.put_line(i);

end loop;

end;

打印 1 - 100 之间的所有素数

使用when:

declare

v_flag number(1):=1;

v_i number(3):=2;

v_j number(2):=2;

begin

while (v_i=100) loop

while v_j = sqrt(v_i) loop

if (mod(v_i,v_j)=0) then v_flag:= 0;end if;

v_j :=v_j +1;

end loop;

if(v_flag=1) then dbms_output.put_line(v_i);end if;

v_flag :=1;

v_j := 2;

v_i :=v_i +1;

end loop;

end;

使用for结构:

declare

--标记值, 若为 1 则是素数, 否则不是

v_flag number(1) := 0;

begin

for i in 2 .. 100 loop

v_flag := 1;

for j in 2 .. sqrt(i) loop

if i mod j = 0 then

v_flag := 0;

end if;

end loop;

if v_flag = 1 then

dbms_output.put_line(i);

end if;

end loop;

end;

使用goto:

declare

--标记值, 若为 1 则是素数, 否则不是

v_flag number(1) := 0;

begin

for i in 2 .. 100 loop

v_flag := 1;

for j in 2 .. sqrt(i) loop

if i mod j = 0 then

v_flag := 0;

goto label;

end if;

end loop;

&lt;label&gt;

if v_flag = 1 then

dbms_output.put_line(i);

end if;

end loop;

end;

游标

游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果。我们可以把游标理解为PL/SQL中的结果集。

游标使用语法

open 游标名称 
loop 
   fetch 游标名称 into 变量   
   exit  when  游标名称%notfound 
end loop; 
close 游标名称 

打印出 80 部门的所有的员工的工资:salary: xxx

declare

--1. 定义游标

cursor salary_cursor is select salary from employees where department_id = 80;

v_salary employees.salary%type;

begin

--2. 打开游标

open salary_cursor;

--3. 提取游标

fetch salary_cursor into v_salary;

--4. 对游标进行循环操作: 判断游标中是否有下一条记录

while salary_cursor%found loop

dbms_output.put_line('salary: ' || v_salary);

fetch salary_cursor into v_salary;

end loop;

--5. 关闭游标

close salary_cursor;

end;

打印出 manager_id 为 100 的员工的 last_name, email, salary 信息

declare

--声明游标

cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;

--声明记录类型

type emp_record is record(

name employees.last_name%type,

email employees.email%type,

salary employees.salary%type

);

-- 声明记录类型的变量

v_emp_record emp_record;

begin

--打开游标

open emp_cursor;

--提取游标

fetch emp_cursor into v_emp_record;

--对游标进行循环操作

while emp_cursor%found loop

dbms_output.put_line(v_emp_record.name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary );

fetch emp_cursor into v_emp_record;

end loop;

--关闭游标

close emp_cursor;

end;

利用游标, 调整公司中员工的工资

工资范围 调整基数

0 - 5000 5%

5000 - 10000 3%

10000 - 15000 2%

15000 - 1%

declare

--定义游标

cursor emp_sal_cursor is select salary, employee_id from employees;

--定义基数变量

temp number(4, 2);

--定义存放游标值的变量

v_sal employees.salary%type;

v_id employees.employee_id%type;

begin

--打开游标

open emp_sal_cursor;

--提取游标

fetch emp_sal_cursor into v_sal, v_id;

--处理游标的循环操作

while emp_sal_cursor%found loop

--判断员工的工资, 执行 update 操作

--dbms_output.put_line(v_id || ': ' || v_sal);

if v_sal = 5000 then

temp := 0.05;

elsif v_sal= 10000 then

temp := 0.03;

elsif v_sal = 15000 then

temp := 0.02;

else

temp := 0.01;

end if;

--dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);

update employees set salary = salary * (1 + temp) where employee_id = v_id;

fetch emp_sal_cursor into v_sal, v_id;

end loop;

--关闭游标

close emp_sal_cursor;

end;

带参数的游标

declare

--定义游标

cursor emp_sal_cursor(dept_id number, sal number) is

select salary + 1000 sal, employee_id id

from employees

where department_id = dept_id and salary  sal;

--定义基数变量

temp number(4, 2);

begin

--处理游标的循环操作

for c in emp_sal_cursor(sal = 4000, dept_id = 80) loop

--判断员工的工资, 执行 update 操作

--dbms_output.put_line(c.id || ': ' || c.sal);

if c.sal = 5000 then

temp := 0.05;

elsif c.sal = 10000 then

temp := 0.03;

elsif c.sal = 15000 then

temp := 0.02;

else

temp := 0.01;

end if;

dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);

--update employees set salary = salary * (1 + temp) where employee_id = c.id;

end loop;

end;

隐式游标

更新指定员工 salary(涨工资 10),如果该员工没有找到,则打印”查无此人” 信息

begin

update employees set salary = salary + 10 where employee_id = 1005;

if sql%notfound then

dbms_output.put_line('查无此人!');

end if;

end;

异常处理

在运行程序时出现的错误叫做异常,发生异常后,语句将停止执行,控制权转移到 PL/SQL 块的异常处理部分

异常有两种类型:

  • 预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

  • 用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常,自定义的异常通过 RAISE 语句显式引发

declare
  cursor cemp is select ename from emp where deptno=50;
  pename emp.ename%type;

  --自定义例外
  no_emp_found exception;
begin
  open cemp;
  --取第一条记录
  fetch cemp into pename;
  if cemp%notfound then
    --抛出例外
    raise no_emp_found;
  end if;


  close cemp;

exception
  when no_emp_found then dbms_output.put_line('没有找到员工');
  when others then dbms_output.put_line('其他例外');

end;

更新指定员工工资,如工资小于300,则加100;对 NO_DATA_FOUND 异常, TOO_MANY_ROWS 进行处理

declare

v_sal employees.salary%type;

begin

select salary into v_sal from employees where employee_id = 100;

if(v_sal  300) then update employees set salary = salary + 100 where employee_id = 100;

else dbms_output.put_line('工资大于300');

end if;

exception

when no_data_found then dbms_output.put_line('未找到数据');

when too_many_rows then dbms_output.put_line('输出的数据行太多');

end;

存储函数和过程

存储函数

存储函数又称为自定义函数。可以接收一个或多个参数,返回一个结果。在函数中我们可以使用P/SQL进行逻辑的处理。

存储函数的语法格式

CREATE [ OR REPLACE ]  FUNCTION  函数名称 
 (参数名称 参数类型, 参数名称 参数类型, ...) 
RETURN 结果变量数据类型 
IS 
  变量声明部分; 
BEGIN 
  逻辑部分; 
  RETURN 结果变量; 
[EXCEPTION  
  异常处理部分] 
END; 

返回一个 “helloworld” 的字符串

create or replace function hello_func

return varchar2

is

begin

return 'helloworld';

end;

执行函数

begin

dbms_output.put_line(hello_func());

end;

定义一个函数: 获取给定部门的工资总和, 要求:部门号定义为参数, 工资总额定义为返回值

create or replace function sum_sal(dept_id number)

return number

is

cursor sal_cursor is select salary from employees where department_id = dept_id;

v_sum_sal number(8) := 0;

begin

for c in sal_cursor loop

v_sum_sal := v_sum_sal + c.salary;

end loop;

--dbms_output.put_line('sum salary: ' || v_sum_sal);

return v_sum_sal;

end;

执行函数

begin

dbms_output.put_line(sum_sal(80));

end;

存储过程

存储过程是被命名的PL/SQL块,存储于数据库中,是数据库对象的一种。应用程序可以调用存储过程,执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果,存在区别如下:

  1. 存储函数中有返回值,且必须返回;而存储过程没有返回值,可以通过传出参数返回多个值。
  2. 存储函数可以在select语句中直接使用,而存储过程不能。过程多数是被应用程序所调用。
  3. 存储函数一般都是封装一个查询结果,而存储过程一般都封装一段事务代码。

定义一个存储过程: 获取给定部门的工资总和(通过 out 参数), 要求:部门号和工资总额定义为参数

create or replace procedure sum_sal_procedure(dept_id number, v_sum_sal out number)

is

cursor sal_cursor is select salary from employees where department_id = dept_id;

begin

v_sum_sal := 0;

for c in sal_cursor loop

--dbms_output.put_line(c.salary);

v_sum_sal := v_sum_sal + c.salary;

end loop;

dbms_output.put_line('sum salary: ' || v_sum_sal);

end;

[执行]

declare

v_sum_sal number(10) := 0;

begin

sum_sal_procedure(80,v_sum_sal);

end;

触发器

​ 数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时,Oracle自动地执行触发器中定义的语句序列。

触发器可用于

  • 数据确认

  • 实施复杂的安全性检查

  • 做审计,跟踪表上所做的数据操作等

  • 数据的备份和同步

触发器分类

  • 前置触发器(BEFORE)

  • 后置触发器(AFTER)

触发器的语法格式

CREATE  [or REPLACE] TRIGGER  触发器名 
   BEFORE | AFTER 
   [DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]] 
   ON  表名 
   [FOR EACH ROW  ][WHEN(条件) ] 
declare 
    …… 
begin 
   PLSQL 块  
End ;

一个helloworld级别的触发器

create or replace trigger hello_trigger

after

update on employees

--for each row

begin

dbms_output.put_line('hello...');

--dbms_output.put_line('old.salary:'|| :OLD.salary||',new.salary'||:NEW.salary);

end;

行级触发器: 每更新 employees 表中的一条记录, 都会导致触发器执行

create or replace trigger employees_trigger

after

update on employees

for each row

begin

dbms_output.put_line('修改了一条记录!');

end;
展开阅读全文

没有更多推荐了,返回首页