Oracle入门6（plsql语言）
游标， 触发器， plsql存储过程， 存储函数， 包
plsql编程语言
-- 普通用户需要获取编程权限
grant create procedure to 用户名;
grant execute any procedure to 用户名;
grant debug connect session to 用户名;
-- 声明你在plsql中需要使用的变量
-- plsql语言和sql语句的数据类型是通用的
-- "="用于做比较，赋值使用":="
declare
sname varchar2(20):='yxy';
-- default表示默认值，只能在declare中使用
sname2 varchar2(20) default 'wzf';
-- 所有plsql代码都必须有begin end
begin
dbms_output.put_line(sname||'as'||sname2||'together');
end;
-- constant 常量修饰符 plsql中定义的常量，赋初值之后不可修改 (就tm是const)
declare
pi constant number:=3.14;
r number default 3;
area number;
begin
area:= pi*r*r;
dbms_output.put_line('area equal: '||area);
end;
-- 标量数据类型
-- 该类型变量只有一个值，包括数字，字符，日期，布尔
-- timestamp 高精度日期类型
-- binary_integer 介于-231~231的整数
-- natural 从0开始的自然数
-- naturaln 非null的自然数
-- positive 正整数
-- positiven 非空正整数
-- real 18位精度的浮点数
-- 属性数据类型
-- 当声明的变量值是数据库表的某一行和某一字段时使用
-- %rowtype（存储一行数据）:
-- 在书写查询语句时必须确保查询到的为一条，否则报错
declare
myemp emp%rowtype; -- 声明一个叫做myemp的变量，可以用来存储emp表的信息
begin
-- 将查询到的行数据导入到myemp变量中
select * into myemp from emp where empno = 7934;
dbms_output.put_line(myemp.ename||' -- '||myemp.sal);
end;
-- %type（数据类型借用）:
-- 引用某一个变量或者数据库表字段的数据类型，作为自己的数据类型
declare
sal emp.sal%type; -- 定义一个变量叫sal，数据类型和emp表的sal字段一致
mysal number(4) := 3000;
totalsal mysal%type; -- 定义变量totalsal,数据类型和mysql一致
begin
select sal into sal from emp where empno = 7934;
totalsal := sal + mysal;
dbms_output.put_line(totalsal);
end;
-- plsql范围运算符：.. (1..100)(1~100所有自然数)
-- 逻辑运算：and or not
-- 顺序结构 分支结构 循环结构
if 条件一 then
(............)
elsif 条件二 then
(............)
else
(............)
end if;
-- -------------------------------------
declare
newsal emp.sal%type;
begin
select sal into newsal from emp where ename = 'JAMES';
if newsal > 900 then
update emp set sal = sal + 10000 where ename = 'JAMES';
elsif newsal = 900 then
update emp set sal = 0 where ename = 'JAMES';
else
update emp set sal = sal - 500 where ename = 'JAMES';
end if;
commit;
end;
-- while loop, for in loop
-- 求 1-100 的和
-- --------while loop------------
declare
counter number(3) := 0;
sumResult number := 0;
begin
while counter < 100 loop
counter := counter + 1;
sumResult := sumResult + counter;
end loop;
dbms_output.put_line(sumResult);
end;
-- --------for in loop------------
declare
counter number(3) := 0;
sumResult number := 0;
begin
for counter in 1..100 loop
sumResult := sumResult + counter;
end loop;
dbms_output.put_line(sumResult);
end;
-- 利用循环结构实现emp表信息的遍历
-- %rowtype类型的变量一次只能存储一行数据，我们需要借助rownum，通过循环和rownum
-- 每一次循环读取表的一行数据，存储到%rowtype变量中，然后打印，一直到循环结束
-- plsql中无法直接舒勇rownum作为比对条件，需要使用子查询处理rownum
declare
emp_row emp%rowtype;-- 用来存储emp表一行记录的变量
v_count number(2); -- 用来记录表的行数
v number(2) default 0; -- 记录循环次数
begin
select count(*) into v_count from emp;
loop
v := v+1;
-- 赋值行数据时，逐列赋值
select a.empno, a.ename, a.job, a.mgr, a.hiredate, a.sal, a.comm, a.deptno into emp_row from(select rownum as r, e.* from emp e) a where a.r = v;
dbms_output.put_line(emp_row.ename || ' -- ' ||emp_row.sal);
exit when v = v_count;
end loop;
end;
触发器
在指定事件触发的时候，自动执行的sql语句
-- DML触发器 当我们对指定数据进行DML操作的时候，执行触发器
-- 语句级触发器
-- 行级触发器
-- before触发器 在触发器事件发生之前，触发器的代码就会执行
-- after触发器 在触发器事件发生之后，执行触发器
create [or replace] trigger 触发器名称
{before|after} 触发条件 on 表名
[for each row] -- 如果加上这一行就说明是行级触发器
begin
-- 触发器内容（plsql）
declare
(............)
begin
(............)
end;
end;
-- 赋予创建触发器权限
grant create trigger to 用户名;
-- ------------------------------------
-- 将所有对student表的操作都记录到stu_log表中
create or replace trigger modify_stu1
after insert or delete or update of stu_name on student
-- 当进行增加，删除，更新stu_name字段（update of stu_name）操作时执行触发器
for each row
begin
if inserting then -- 如果是insert操作触发触发器
insert into stu_log values(1, 'insert', sysdate, :new.stu_name);
-- :new.stu_name代表的是插入语句中的stu_name字段的值
elsif deletong then -- 如果是delete操作触发触发器
insert into stu_log values(2, 'delete', sysdate, :old.stu_name);
-- :old.stu_name代表的是被删除的旧数据对应的stu_name的值
elsif updating then -- 如果是update操作触发触发器
insert into stu_log values(3, 'update_old', sysdate, :old.stu_name);
insert into stu_log values(4, 'update_new', sysdate, :new.stu_name);
end if;
end;
-- ------------------------------------
-- 语句级触发器 + before触发器，用来控制用户对表的操作，避免用户删除数据
create or replace trigger control_stu_log
before delete or update on stud_log
begin
if deleting then
raise_application_error(-20001, '该表不允许删除行为');
-- raise_application_error 主动生成错误（错误码必须>20000）
elsif updating then
raise_application_error(-20002, '该表不允许更新行为');
end if;
end;
游标
用来遍历多行数据的工具，分为：显式游标，隐式游标（类似指针）
显式游标
-- 查询emp表的所有员工的姓名和薪水
declare
cursor c_cur
is
select ename, sal from emp;
-- 游标需要对应一个sql语句，用来代表游标所需要获取的数据范围
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open c_cur; -- 游标在使用之前需要打开
fetch c_cur into v_ename, v_sal; -- 从游标中将数据提取出来
-- 游标中默认的指针指向为空，当执行fetch的时候，游标会读取到第一行数据的内存地址，而into会将读取到的数据存储到本地定义的变量中
while c_cur%found loop -- 对当前游标指针中是否有数据进行判断，如果没有则循环停止
-- %found 判断当前游标最近一次对数据的提取是否成功，成功为true，失败为false
-- %notfound 获取的布尔值于%found相反
-- %isopen 判断游标是否被打开
-- %rowcount 返回游标已经读取的记录数量
dbms_output.put_line(v_ename || ' -- ' || v_sal);
fetch c_cur into v_ename, v_sal; -- 再一次提取
end loop;
end;
隐式游标
-- 隐式游标省略了游标的打开，关闭，判断，fetch提取
-- 查询emp表的所有员工的姓名和薪水
declare
cursor c_cur
is
select ename, sal from emp;
begin
-- v_row相当于临时定义的一个用来接受数据的%rowtype类的变量，负责接收从游标中所提取的数据
for v_row in c_cur loop
dbms_output.put_line(v_row.ename || ' -- ' || v_row.sal);
end loop;
end;
declare
cursor c_cur
is
select empno, sal, job from emp;
begin
for v_row in c_cur loop
if v_row.sal < 1200 then
update emp set sal = sal + 50;
end if;
if v_row.sal > 500 and v_row.job = 'SALESMAN' then
delete from emp where empno = v_row.empno;
end if;
end loop;
end;
存储过程&存储函数
之前所有的plsql代码都存在问题：无法长期的保存在数据库中（匿名存储过程）
-- 定义一个存储过程，用来获取emp表的总人数(会长期存储在数据库里)
create or replace procedure emp_count
as
v_count number(2);
begin
select count(*) into v_count from emp;
dbms_output.put_line('emp表总人数为' || v_count);
end;
-- 调用存储过程
begin
emp_count;
end;
-- 删除存储过程
drop procedure 名称;
-- 可以在存储过程中使用游标，并且调用其他存储过程
-- 定义一个存储过程，用来展示所有员工信息和员工的总人数
create or replace procedure emp_list
as
cursor emp_cursor
is
select empno, ename, sal from emp;
begin
for emp_row in emp_cursor loop
dbms_output.put_line(emp_row.empno || ' -- ' || emp_row.ename || ' -- ' || emp_row.sal);
end loop;
emp_count;
end;
希望可以根据不同的条件，让存储过程获取不同的结果
in 参数：用于从存储过程外向存储过程内传递数据
out 参数：用于从存储过程内，向存储过程外传递数据
in out 参数：兼具上面两个参数的功能
in参数
-- 编写一个给指定员工增加工资的存储过程
-- 参数的名称：存储过程或者函数的参数一般使用p开头
create or replace procedure change_sal(p_empno in number default 7788, p_raise in number default 10)
as
v_empno number(5);
v_ename varchar2(19);
v_sal number(5);
begin
select ename, sal, empno into v_ename, v_sal, v_empno from emp where empno = p_empno;
if v_empno = 7369 then
dbms_output.put_line('7369 不需要加薪');
raise_application_error(-20001,'7369 不需要加薪');
end if;
update emp set sal = sal + p_raise where empno = p_empno;
dbms_output.put_line(v_ename || ' 的工资被修改为：' || (v_sal + p_raise));
exception when others then
dbms_output.put_line('发生错误！');
rollback;
end;
out 参数
create or replace procedure emp_count(p_total out number)
as
begin
select count(*) into p_total from emp;
end;
-- 在匿名plsql中，调用存储过程，将存储过程的out参数的值传递给你存储的变量
declare
v_empcount number;
begin
emp_cou(v_empcount);
dbms_output.put_line('总人数为: ' || v_empcount);
end;
in out 参数
-- 定义一个存储过程，给指定的姓名前添加前缀
create or replace procedure add_tmp(p_name in out varchar2)
as
begin
p_name := 'tmp' || p_name;
end;
-- 调用
declare
v_name varchar2(100);
begin
v_name := 'robot1';
add_tmp(v_name);
dbms_output.put_line('reset name: ' || v_name);
end;
存储函数
存储函数和存储过程几乎一致，区别在于存储函数拥有参数，但只能是 in 类型，in 可以省略不写，在定义存储函数的时候，会定义return类型
-- 创建一个存储函数，通过empno获取ename
create or replace function get_emp_ename(p_empno number default 7788)
return varchar2 -- 定义返回值的数据类型
as
v_ename varchar2(10);
begin
select ename into v_ename from emp where empno = p_empno;
return(v_ename);
-- 异常处理（针对select查询语句）
exception when no_data_found then
dbms_output.put_line('no found！');
return(null);
when too_many_rows then
dbms_output.put_line('too many rows！');
return(null);
when others then
dbms_output.put_line('other error！');
return(null);
end;
-- 调用
declare
v_ename varchar2(20);
begin
v_ename := get_emp_ename(7900);
dbms_output.put_line('name is ' || v_ename);
end;
包管理（package）
包由包头和包体组成，需要在包头中声明，所有的存储过程或者存储函数，在包体中给出存储过程或者存储函数的具体实现
Public公有元素：在包头中声明的变量，整个应用层都可以访问这些元素
private私有元素：在包体的说明部分中声明的变量，只能被包内部的其他元素访问
local本地元素：在包中包含的存储过程和函数的声明部分声明的变量
包可以比较方便的讲存储过程和存储函数组织在一起，每一个包都是独立的，在不同的保重，存储过程和存储函数的名称可以重复
-- 创建一个简化版的，用来管理emp信息的包，这个包可以用来从emp表获取员工信息，修改员工名称，修改工资等。。。
-- 在创建包的时候，包和包体可以一起编译，也可以分开编译
-- 如果是一起编译，需要包头写在前，包体写在后，使用（/）分割
-- 包头中定义的存储函数，存储过程，必须在包体中实现
create or replace package employe -- 包头声明部分
is
procedure show_detail; -- 包头中声明了一个存储过程，用来展示信息
procedure get_employe(p_empno number); -- 声明一个存储过程，用来获取雇员信息
procedure save_employe;
procedure change_name(p_newname varchar2);
procedure change_sal(p_newsal number);
end employe; -- 包头部分声明结束
/ -- 包头和包体的分割符
create or replace package body employe -- 包体部分，需要提供包头中定义的所有存储过程或者函数的代码实现
is
emp_row emp%rowtype; -- 在包体中所定义的变量（private）
procedure show_detail -- 实现show_detail存储过程
as
begin
dbms_output.put_line('==雇员信息如下==');
dbms_output.put_line('雇员编号为：' || emp_row.empno);
dbms_output.put_line('雇员姓名为：' || emp_row.ename);
dbms_output.put_line('雇员职位为：' || emp_row.job);
dbms_output.put_line('雇员工资为：' || emp_row.sal);
dbms_output.put_line('雇员部门编号为：' || emp_row.deptno);
end show_detail;
procedure get_employe(p_empno number) -- 实现get_employe存储过程
as
begin
select * into emp_row from emp where empno = p_empno;
end get_employe;
procedure save_employe -- 实现save_employe存储过程
as
begin
update emp set ename = emp_row.ename, sal = emp_row.sal where empno = emp_row.empno;
dbms_output.put_line('==雇员信息保存成功==');
end save_employe;
procedure change_name(p_newname varchar2) -- 实现change_name存储过程
as
begin
emp_row.ename := p_newname;
dbms_output.put_line('==名称修改成功==');
end change_name;
procedure change_sal(p_newsal number) -- 实现change_sal存储过程
as
begin
emp_row.sal := p_newsal;
dbms_output.put_line('==工资修改成功==');
end change_sal;
end employe;
-- 包测试：调用包里的存储函数
-- 包是长期存储在数据库中的，我们在包体中定义的变量只要被赋值过，就会长期存储在数据库中
begin
employe.get_employe(7900);
employe.show_detail;
employe.change_sal(666);
employe.change_name('robot01');
end;
高级包管理
要求每一个包都有完整的CRUD（增删改查）功能，且内嵌存储函数，包内可调用
-- 创建一个包，对emp表进行完整的增删改查（CRUD）
create or replace package emp_pk -- 包头，声明存储过程和函数
is
v_emp_count number(5); -- 存放雇员总人数（public）
procedure init(p_max number, p_min number); -- 负责初始化雇员人数，并且定义工资修改的上下限
procedure list_emp; -- 展示雇员信息
procedure insert_emp(p_empno number, p_ename varchar2, p_job varchar2, p_sal number); -- 插入雇员信息
procedure delete_emp(p_empno number); -- 删除雇员信息
procedure change_emp_sal(p_empno number, p_sal number); -- 修改雇员工资
end emp_pk;
/ -- 包头和包体的分割符
create or replace package body emp_pk
is
v_message varchar2(50); -- 存放用来输出的信息
v_max_sal number(7); -- 后续在定义工资最大值的时候，用来在包中存储最大值的变量
v_min_sal number(7); -- 后续在定义工资最小值的时候，用来在包中存储最小值的变量
-- 在包头中声明的存储过程或者函数，可以被所有其他用户调用
-- 但是，如果没有在包头中声明的函数，只能在当前包中被调用，外界不可以调用
function exist_emp(p_empno number) return boolean; -- 定义一个判断雇员是否存在的函数
procedure show_message; -- 定义一个用来展示数据的存储过程
function exist_emp(p_empno number) return boolean
is
v_num number;
begin
select count(*) into v_num from emp where empno = p_empno;
if v_num = 1 then
return true;
else
return false;
end if;
end exist_emp;
procedure show_message
as
begin
dbms_output.put_line('==='||v_message||'===');
end show_message;
procedure init(p_max number, p_min number) -- 实现init存储过程
as
begin
v_max_sal := p_max;
v_min_sal := p_min;
v_message := '初始化工作已经完成！';
show_message;
end init;
procedure list_emp -- 实现list_emp存储过程
as
begin
dbms_output.put_line('==姓名==职位==工资==');
-- 游标的最简单的写法，相当于定义了一个匿名游标，此游标不可以复用，只能在本存储过程中使用
for emp_rec in (select * from emp) loop
dbms_output.put_line(emp_rec.ename || ' ' || emp_rec.job || ' ' || emp_rec.sal);
end loop;
end list_emp;
procedure insert_emp(p_empno number, p_ename varchar2, p_job varchar2, p_sal number) -- 实现insert_emp存储过程
as
begin
if not exist_emp(p_empno) then
insert into emp (empno, ename, job, sal) values (p_empno, p_ename, p_job, p_sal);
commit;
v_emp_count := v_emp_count + 1; -- 总人数加一
v_message := '雇员'||p_empno||'已经成功插入！';
else
v_message := '雇员'||p_empno||'已经存在，插入失败！';
end if;
show_message;
-- 异常处理
exception when others then
v_message := '发生错误，插入失败！';
show_message;
end insert_emp;
procedure delete_emp(p_empno number) -- 实现delete_emp存储过程
as
begin
if exist_emp(p_empno) then
delete from emp where empno = p_empno;
commit;
v_emp_count := v_emp_count - 1; -- 总人数减一
v_message := '雇员'||p_empno||'已经成功删除！';
else
v_message := '雇员'||p_empno||'不存在，删除失败！';
end if;
show_message;
-- 异常处理
exception when others then
v_message := '发生错误，删除失败！';
show_message;
end delete_emp;
procedure change_emp_sal(p_empno number, p_sal number) -- 实现change_emp_sal存储过程
as
begin
if(p_sal > v_max_sal or p_sal < v_min_sal) then
v_message := '工资超限！';
elsif not exist_emp(p_empno) then
v_message := '雇员'||p_empno||'不存在，修改失败！';
else
update emp set sal = p_sal where empno = p_empno;
commit;
v_message := '雇员'||p_empno||'已经成功修改！';
end if;
show_message;
exception when others then
v_message := '发生错误，修改失败！';
show_message;
end change_emp_sal;
end emp_pk;
-- 将包的执行权限赋予其他用户
grant excute on emp_pk to 用户名;
-- 包头中的公有变量，可以被外部直接访问，包名 + 变量名
dbms_output.put_line(包名.变量名);
数据库总结
DML，DDL语句，建表，alter，DQL查询语句，索引，视图，序列，触发器，表空间，同义词，plsql编程，存储过程，存储函数，包管理
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 零经验选手，Compose 一天开发一款小游戏！
· 因为Apifox不支持离线，我果断选择了Apipost！
· 通过 API 将Deepseek响应流式内容输出到前端