7. plsql 语句/流程控制/游标使用/异常处理/存储函数/存储过程/触发器
-- PL/SQL 是一种编程语言
-- 是 Oracle 针对 sql 语句的扩展, 只支持 Oracle 数据库
-- Oracle 的很多高级功能只能通过 plsql 来完成
declare
-- 声明的变量/ 类型/ 游标
begin
-- 程序的执行部分(类似于 Java 里的 main() 方法)
dbms_output.put_line('hello world');
exception
-- 针对begin块中出现的异常, 提供处理机制
-- when... then
-- when... then
end;
/*ORACLE中dbms_output.put_line输出不了的问题。
1. 开启oracle输出
说是oracle默认是关闭的。 要执行 :
set serveroutput on; --在windows cmd 中执行 sqlplus 命令*/
--======================================================
declare
--声明变量
-- 程序变量 v_name,例如: v_name
-- 常量 c_name, 例如:c_company_name
-- 游标变量 name_cursor, 例如: emp_cursor;
-- 异常标识 e_name, 例如: e_too_many;
-- 表类型 name_table_type 例如: emp_record_type
-- 表: name_table 例如: emp;
-- 记录类型: name_record 例如: emp_record;
-- sql*plus 替代变量: p_name 例如:emp_record;
-- 绑定变量: g_name 例如: g_year_sal;
v_sal employees.salary%type; -- 与employees 表 salary 的类型一致
v_email varchar2(20);
v_hire_date date := '12-2月-1995'; -- 变量赋值用 :=
begin
-- sql 语句的操作: select.. into...from ...where...
select salary, email, hire_date into v_sal, v_email, v_hire_date from employees where employees_id = 100;
-- 打印
dbms_output.put_line(v_sal,||','||v_email||','||v_hire_date);
end;
--=========================================================
-- 记录类型: 类似于 类 的概念, 可以声明一个对象, 调用对象的参数/方法
/*记录类型是把逻辑相关的数据作为一个单元存储起来, 称作PL/SQL RECORD 的域(field), 其作用是存放互不相同但逻辑相关的信息.*/
declare
-- 声明一个记录类型 (相当于定义一个类)
type emp_record is record(
v_sal employees.salary%type,
v_email employees.email%type,
v_hire_date employees.hire_date%type
);
-- 定义一个记录类型的成员变量 (相当于定义一个类的对象, 用对象调用方法/参数)
v_emp_record emp_record;
begin
-- sql语句的操作: select...into...from...where...
select salary, email, hire_date into v_emp_record
from employees where employees_id = 100;
-- 打印
dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp.record.v_hire_date);
end;
/*
1. pl/sql 基本的语法格式;
2. 记录类型 type...is record(,,,);
3. 流程控制:
3.1 条件判断
方式1: if...then elsif then... else... end if; (注意: 是 elsif )
方式2: case... when...then...when...end;
3.2 循环结构 (三种)
方式1: loop...exit when... end loop;
方式2: while... loop... end loop;
方式3: for i in ... loop ... end loop;
3.3 goto/exit
4. 游标的使用(类似于 Java 中的 Iterator)
5. 异常的处理(三种)
6. 会写一个存储函数(有返回值)/ 存储过程(没有返回值)
7. 会协议个触发器(操作什么东西, 就自动触发)
*/
-- 例子1:
declare
type emp_record is record(
v_name varchar2(20),
v_sal number(10, 2),
v_hiredate date
);
v_emp_record emp_record;
v_name1 v_emp_record.v_name%type; -- 定义一个变量 与 对象的某个变量一致
begin
v_name1 := '刘德华'; -- 变量赋值
v_emp_record.v_name := v_name1; -- 动态获取变量 (:= 赋值符号 ) (=> 关系符号) (. 范围运算符)
v_emp_record.v_sal := 12000;
v_emp_record.v_hiredate := to_date('1995-2-12', 'YYYY-MM-DD');
dbms_output.put_line('name: ' || v_emp_record.v_name || ' sal: ' || v_emp_record.v_sal || ' hire_date: ' || v_emp_record.v_hiredate );
end;
-- 例子2:
declare
v_emp_record employees%rowtype; -- %rowtype 声明对象的列与 employees表的结构一致(列名/数量/类型 都一致) , 更简洁
begin
v_emp_record.v_name := '刘德华';
v_emp_record.v_sal := 12000;
v_emp_record.v_hiredate := to_date('1995-2-12', 'YYYY-MM-DD');
dbms_output.put_line('name: ' || v_emp_record.v_name || ' sal: ' || v_emp_record.v_sal || ' hire_date: ' || v_emp_record.v_hiredate );
end;
*/
-- 例子3: 执行 update
declare
v_emp_id number(10);
begin
v_emp_id := 123;
update employees
set selary = salary + 100
where employee_id = v_emp_id;
dbms_output.put_line('执行成功');
end;
-- 例子4: if...then...; elsif...then...; else...; end if;
declare
v_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id = 130;
if v_sal >= 10000 then dbms_output.put_line('salary >= 10000');
elsif v_sal >= 5000 then dbms_output.put_line('5000<= salary <= 10000');
else dbms_output.put_line('salary < 5000');
end if;
end;
-- 例子4.2: 也可以这么写
declare
v_sal number(10, 2);
v_msg varchar2(50);
begin
v_sal := 5645;
if v_sal >= 10000 then v_msg := 'salary >= 10000';
elsif v_sal >= 5000 then v_msg := '5000 <= salary <= 10000';
else v_msg := 'salary < 5000';
end if;
dbms_output.put_line(v_msg);
end;
-- 例子5:
/*
case selecter
when expression1 then result1
when expression2 then result2
...
when expressionN then resultN
[else resultN+1]
end;
*/
declare
v_sal number(10, 2);
v_msg varchar2(50);
begin
select salary into v_sal from employees where employee_id = 150;
-- case when..then 限制较多, 请注意!
v_msg := -- 类似于Java 的 switch...case.. case 后面只能跟数字; when 后面也只能写数字, 不能写表达式 v_msg := xxx, 只能是一个常量;
case trunc(v_sal/5000) when 0 then 'salary >= 10000' -- 注意: when 后面必须跟 数字/常量 when 后面不能赋值, 赋值的话 变量对整个 case 赋值
when 1 then '5000 <= salary <= 10000' -- 变量赋值不能写 then 后面, 每一个 when 后面不能加逗号或分号
else 'salary < 5000'
end;
dbms_output.put_line(v_msg);
end;
-- 例子5.2
declare
v_job_id varchar2(10);
v_temp varchar2(10);
begin
select job_id into v_job from employees where employee_id = 122;
v_temp:=
case v_job_id when 'IT_PROG' then 'A'
when 'AC_MGT' then 'B'
when 'AC_ACCOUNT' then 'C'
else 'D'
dbms_output.put_line(v_job || ',' || v_temp);
end;
-- loop 例子1:
-- 使用循环语句打印 1-100.
-- 1.初始化条件 2.循环体 3.循环条件 4. 迭代条件
declare
-- 1.初始化条件
v_i number(5) := 1;
begin
loop
-- 2. 循环体
dbms_output.put_line(v_i);
-- 3. 循环条件
exit when v_i > 100;
-- 4. 迭代条件
v_i := v_i + 1;
end loop;
end;
-- while..loop 例子2:
-- 使用循环语句打印 1-100.
-- 1.初始化条件 2.循环体 3.循环条件 4. 迭代条件
declare
-- 1.初始化条件
v_i number(5) := 1;
begin
while v_i <= 100 loop
dbms_output.put_line(v_i);
v_i := v_i + 1;
end loop;
end;
-- for loop
-- 例子1:
begin
for c in 1..100 loop
dbms_output.put_line(c);
end loop;
end;
-- 例子2:
begin
for c in reverse 1..100 loop -- 加上 reverse 从100 到 1 反着 循环
dbms_output.put_line(c);
end loop;
end;
-- 例子3:
-- 输出 2-100 之间的质数
declare
v_i number(3) := 2;
v_j number(3) := 2;
v_flag number(1) := 1;
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_j := 2;
v_i := v_i + 1;
v_flag := 1;
end loop;
end;
-- 例子4:
-- 输出 2-100 之间的质数 for
declare
v_flag number(1) := 1;
begin
for v_i in 2..100 loop
for v_j in 2..sqrt(v_i) loop
if mod(v_i, v_j) = 0 then v_flag := 0;
end if;
end loop;
if v_flag = 1 then dbms_output.put_line(v_i);
end if;
v_flag := 1;
end loop;
end;
-- 例子5:
-- 打印: 1--100的自然数, 当打印到50时, 跳出循环, 输出打印结束
begin
for i in 1..100 loop
if 1 = 50 then goto label;
end if;
dbms_output.put_line(i);
end loop;
<<label>>
dbms_output.put_line('打印结束');
end;
-- 例子6:
-- 打印: 1--100的自然数, 当打印到50时, 跳出循环, 输出打印结束
begin
for i in 1..100 loop
if 1 = 50 then dbms_output.put_line('打印结束');
exit;
end if;
dbms_output.put_line(i);
end loop;
end;
-- 游标的使用: 对于处理多行数据的使用, 经常使用 游标
-- 游标是一个指向上下文的句柄(handle)或指针.
/*
sql语句 游标
非查询语句 隐式的
结果是单行的查询语句 隐式的或显示的
结果是多行的查询语句 显示的
*/
/*
游标属性:
%found 布尔型属性, 当最近一次读取记录时成功返回, 值位true
%notfound 布尔型属性, 与 %found 相反
%isopen 布尔型属性, 当游标已打开时, 返回 true
%rowcount 数字型属性, 返回已从游标中读取的记录数
*/
-- 打印出 80 部门的所有员工的工资
-- 游标例子 1:
declare
type emp_record is record(
v_sal employees.salary%type;
v_empid employees.employee_id%type;
)
-- 声明一个记录类型的变量
v_emp_record emp_record;
-- 1 定义游标
cursor emp_sal_cursor is select salary, employees_id from employees where department_id = 80;
begin
-- 2 打开游标
open emp_sal_cursor;
-- 3 提取游标,
fetch emp_sal_cursor into v_emp_record;
while emp_sal_cursor%found loop -- 因为游标是多条数据, 所以用 循环取出来, 游标%found 判断还有值
dbms_output.put_line('emp_id:' || v_emp_record.v_empid || 'salary:' || v_emp_record.v_sal);
fetch emp_sal_cursor into v_sal, v_empid;
end loop;
-- 4 关闭游标
close emp_sal_cursor;
end;
-- 游标的 for 循环, 使用 for 循环可以简写
/* plsql 提供了 游标 for循环, 自动执行游标的 open/ fetch/ close 语句和循环语句的功能;
当进入循环时, 游标 for 循环语句自动打开游标, 并提取第一行游标数据, 当程序处理完当前所提取的数据而进入下一次循环时,
游标 for 循环语句自动提提取下一行数据供程序处理, 当提取完结果集合中的所有数据行后结束循环, 并自动关闭游标*/
declare
type emp_record is record(
v_sal employees.salary%type;
v_empid employees.employee_id%type;
)
-- 声明一个记录类型的变量
v_emp_record emp_record;
-- 1 定义游标
cursor emp_sal_cursor is select salary, employees_id from employees where department_id = 80;
begin
for c in emp_sal_cursor loop
dbms_output.put_line('emp_id:' || v_emp_record.v_empid || 'salary:' || v_emp_record.v_sal);
end loop;
end;
-- 用游标修改 update 记录
declare
cursor emp_sal_cursor is select employee_id, salary from employees;
-- 用于记录调整参数
v_temp number(4, 2);
v_empid employees.employee_id%type;
v_sal employees.salary%type;
begin
open emp_sal_cursor;
fetch emp_sal_cursor into v_empid, v_sal;
while emp_sal_cursor%found loop
if v_sal < 5000 then v_temp := 0.05;
elsif v_sal < 10000 then v_temp := 0.03;
elsif v_sal < 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;
dbms_output.put_line(v_empid || ', ' || v_sal);
update employees
set salary = salary * (1 + v_temp)
where employees_id = v_empid;
fetch emp_sal_cursor into v_empid, v_sal;
end loop;
close emp_sal_cursor;
end;
---===============================\
update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0,0.05,
1, 0.03,
2, 0.02,
0.01)));
-- 使用记录调整基数
declare
cursor emp_sal_cursor is select employee_id, salary from employees;
-- 用于记录调整参数
v_temp number(4, 2);
v_empid employees.employee_id%type;
v_sal employees.salary%type;
begin
for c in emp_sal_cursor loop
if c.salary < 5000 then v_temp := 0.05;
elsif c.salary < 10000 then v_temp := 0.03;
elsif c.salary < 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;
update employees
set salary = salary * (1 + v_temp)
where employee_id = c.employee_id;
end loop;
end;
-- 带参数的游标
declare
-- 定义游标
cursor emp_sal_cursor(dept_id number, sal number) is
select salary + 1000 sal, employee_id id
where department_id = dept_id and salary > sal;
-- 定义基数变量
temp number(4, 2);
begin
-- 处理游标的循环操作
for c in emp_sal_cursor(sal=>4900, dept_id=>80) loop
-- 判断员工工资, 执行update 操作
-- dbms_output.put_line(c.id || ': ' || c.sal);
if c.salary < 5000 then v_temp := 0.05;
elsif c.salary < 10000 then v_temp := 0.03;
elsif c.salary < 15000 then v_temp := 0.02;
else v_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;
-- 隐式游标:
begin
update employees
set salary = salary + 10;
where employee_id = 1001;
if sql%notfound then dbms_output.put_line('查无此人'); -- 判断 sql 语句是否查到此人, 查不到, 则返回打印结果;
end if;
end;
-- ==============异常错误处理
/*
1. 预定义错误(Predefined) : 大约有 24 个
2. 非预定义错误( Predefined): 其他标准的 Oracle 错误
3. 用户定义的错误(User_define) : 用户在程序中定义, 然后显示地在程序中将其引发.
*/
-- 如果报错, 可以用 show errors 语句查看错误原因
/*
异常格式:
EXCEPTION:
WHEN first_exception THEN <code to handle first exception>
WHEN second_exception THEN <code to handle second exception>
WHEN OTHERS THEN <code to handle others exception>
END;
*/
-- 例子1: 异常 预定义错误(Predefined) : 大约有 24 个
declare
v_salary employees.salary%type;
begin
select salary into v_salary
from employees
where employee_id > 100;
dbms_output.put_line(v_salary);
exception
when too_many_rows then dbms_output.put_line('输出的行数跳多了');
when others then dbms_output.put_line('出现其他类型异常');
end;
----------
-- 例子2 异常 非预定义错误( Predefined): 其他标准的 Oracle 错误
declare
e_deleted_exception exception; -- 定义异常情况
-- 将定义好的异常情况, 与标准的 Oracle 错误联系起来, 使用 pragma exception_init 语句
-- pragma exception_init (<异常情况>, <错误代码>);
pragma exception_init(e_deleteid_exception, -2292);
begin
delete from employees where employee_id = 100;
exception
when e_deleteid_exception then dbms_output.put_line('违反完整性约束条件, 故不可删除此用户');
end;
----------
-- 例子3 用户自定义异常
-- 当一个异常出现时, 会隐含触发异常错误, 用户定义的异常错误是通过显示使用 RAISE 语句来触发. 当引发一个异常错误时,控制就转向 exception 块异常错误部分
declare
e_too_high_sal exception;
v_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id = 100;
if v_sal > 10000 then
raise e_too_high_sal; -- raise 抛出异常
end if
exception
when e_too_high_sal then dbms_output.put_line('工资太高了');
end;
-----------存储函数和过程---------------------------------
/*Oracle 提供可以把 PL/SQL 程序存储在数据库中, 并可以在任何地方运行它们, 这样就叫存储过程/函数. 过程和函数统称为 PL/SQL 子程序. 他们被命名为
PL/SQL 块,均存储在数据库中, 并通过输入/输出 参数 与其他调用者交换信息. 过程和函数唯一的区别是函数总向调用者返回数据, 而过程不返回数据*/
/*
create [or replace] function function_name
[(argument [{in | in out}] type -- 传入参数
argument [{in | out | in out}] type]
[authid definer | current_user]
return return_type
{is | as}
<类型. 变量的说明> -- 函数使用过程中, 需要声明的变量, 记录类型, cursor
begin
-- 函数的执行体
exception
-- 函数执行中的异常
end;
*/
-- 简单函数
create or replace function hello_world
return varchar2
is
begin
return 'helloworld';
dbms_output.put_line('helloworld');
end;
--===============================================
-- 执行函数 方法1 编辑 ,然后在命令行执行
begin
dbms_output.put_line(hello_world);
end;
-- 执行函数 方法2
select hello_world from dual;
--================================================
create or replace function hello_world(v_logo varchar2) -- 函数传参
return varchar2
is
begin
return 'helloworld' || v_logo;
end;
-- 运行 可以用 plsql 语句
begin
dbms_output.put_line(hello_world('gupan'));
end;
--或
select hello_world('gupan') from dual;
-- 两个数之和
create or replace function add_params(v_num1 number, v_num2 number)
return number
is
v_sum number(10);
begin
v_sum := v_num1 + v_num2;
return v_sum;
end;
-- 求工资总和
create or replace function get_sal(dept_id number)
return number
is
v_sumsal number(10) := 0;
cursor salary_cursor is select salary from employees where dapartment_id = dept_id;
begin
for c in salary_cursor loop
v_sumsal := v_sumsal + c.salary;
end loop;
return v_sumsal;
end;
-- 用 out 实现多个返回值
create or replace function get_sal(dept_id number, total_count out number) -- 因为函数只能有一个返回值, plsql 通过out型的参数实现有多个返回值
return number
is
v_sumsal number(10) := 0;
cursor salary_cursor is select salary from employees where dapartment_id = dept_id;
begin
total_count := 0;
for c in salary_cursor loop
v_sumsal := v_sumsal + c.salary;
total_count := total_count + 1;
end loop;
return v_sumsal;
end
----使用上面的函数
declare
v_num number(5) := 0;
begin
dbms_output.put_line(get_sal(80, v_num)); -- 上传本地的参数 v_num
dbms_output.put_line(v_num); -- 输出 v_num 改变后的值
end;
----==============存储过程
--- 定义一个存储过程; 获取给定部门的工资总和,(通过 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
sumsal := 0;
for c in salary_cursor loop
sumsal := sumsal + c.salary;
end loop;
adms_output.put_line(sumsal);
end;
--- 用 PL/SQL 语句调动函数
declare
v_sal number(20) := 0;
begin
sum_sal_procedure(80, v_sal);
end;
-- =============触发器
/*
触发器在数据库中以独立的对象存储, 他与存储过程不同的是, 存储过程通过其他程序启动运行 或直接启动运行, 而触发器是由一个事件来启动运行,
即: 触发器是当某个事件发生时自动隐式运行, 并且,触发器不能接受参数.
oracle 事件指的是 对数据库的表进行的 insert/ update/ delete 操作或对视图进行类似操作.
oracle将触发器的功能扩展到了触发 oracle, 如数据库的启动和关闭等
*/
/*
触发器组成:
触发事件: insert / update/ delete
触发事件: 该TRIGGER 是在触发事件发生之前(before) 还是之后 (after) 触发, 也就是触发事件和 该 TRIGGER 的操作顺序
触发器本身: 该TRIGGER 被触发后的目的和意图, 也就是, 触发器要做的事情
触发频率: 触发器内定义的动作被执行的次数, 即 语句级( STATEMENT)触发器 和 行级(ROW) 触发器
语句级( STATEMENT)触发器 : 当某触发事件发生时, 该触发器只执行一次
行级(ROW)触发器: 当某触发器事件发生时, 对受到该操作影响的每一行数据, 触发器都单独执行一次
*/
/* 触发器语法:
create or replace trigger trigger_name
{before | after}
{insert | delete | update [of column [,column...]]}
on [schema.] table_name
[for each row]
[when condition]
trigger_body
*/
create or replace trigger update_emp_trigger
after
update on employees -- 在 employees 表上 update 后 对每一行触发 触发helloworld;
for each row
begin
dbms_output.put_line('hello world');
end;
--没删除一条表里的数据, 在备份表里就添加一条删除的数据
create or replace trigger delete_emp_trigger
before
delete on my_emp
for each row
begin
insert into my_emp_bak
values(:old.employee_id, :old.salary);
end;
浙公网安备 33010602011771号