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编程，存储过程，存储函数，包管理