PL/SQL学习总结

最新推荐文章于 2024-04-18 19:57:46 发布

l1357852347

最新推荐文章于 2024-04-18 19:57:46 发布

阅读量1k

点赞数

分类专栏： sql 文章标签： plsql oracle

本文链接：https://blog.csdn.net/l1357852347/article/details/76033102

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近好久没写博客了，感觉有点怠惰了，感觉需要勤勉一下啦，这里总结一下前不久自学的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块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

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

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

定义一个存储过程: 获取给定部门的工资总和(通过 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;

l1357852347

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL学习总结

最近好久没写博客了，感觉有点怠惰了，感觉需要勤勉一下啦，这里总结一下前不久自学的plsql的一些知识点，并结合网上（实在不知道真正的出处）找到的plsql的训练题进行总结。什么是PL/SQL PL/SQL（Procedure Language/SQL）是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。把SQL
复制链接

扫一扫