pl/sql编程/存储过程/存储函数/触发器

pl/sql编程语言
pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。
pl/sql编程语言比一般的过程化编程语言，更加灵活高效。
pl/sql编程语言主要用来编写存储过程和存储函数等。
结构：

declare
--变量声明区域
begin
--逻辑操作区域
end；
--可以把整个结构理解为java中的main方法

实例

---声明方法
---赋值操作可以使用:=也可以使用into查询语句赋值
declare
--变量声明区域
    i number(2) := 10;
    s varchar2(10) := '小明';
    ena emp.ename%type;---引用型变量
    emprow emp%rowtype;---记录型变量
begin
    dbms_output.put_line(i);--类似与控制台输出
    dbms_output.put_line(s);
    select ename into ena from emp where empno = 7788;--将查询到的数据通过into赋值到ena
    dbms_output.put_line(ena);
    select * into emprow from emp where empno = 7788;
    dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job);
end;

pl/sql中的if判断
—输入小于18的数字，输出未成年
—输入大于18小于40的数字，输出中年人
—输入大于40的数字，输出老年人

declare
  i number(3) := ⅈ--从控制台接收数据
begin
  if i<18 then
    dbms_output.put_line('未成年');
  elsif i<40 then
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if;
end;

pl/sql中的loop循环

---用三种方式输出1到10是个数字
---while循环
declare
  i number(2) := 1;
begin
  while i<11 loop
     dbms_output.put_line(i);
     i := i+1;
  end loop;  
end;
---exit循环
declare
  i number(2) := 1;
begin
  loop
    exit when i>10;
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;
---for循环
declare

begin
  for i in 1..10 loop
     dbms_output.put_line(i);  
  end loop;
end;

游标

---游标：可以存放多个对象，多行记录。
---输出emp表中所有员工的姓名
declare
  cursor c1 is select * from emp;
  emprow emp%rowtype;
begin
  open c1;
     loop
         fetch c1 into emprow;
         exit when c1%notfound;
         dbms_output.put_line(emprow.ename);
     end loop;
  close c1;
end;

-----给指定部门员工涨工资
declare
  cursor c2(eno emp.deptno%type) 
  is select empno from emp where deptno = eno;
  en emp.empno%type;
begin
  open c2(10);
     loop
        fetch c2 into en;
        exit when c2%notfound;
        update emp set sal=sal+100 where empno=en;
        commit;
     end loop;  
  close c2;
end;

游标的另一种遍历数据的方式

存储过程
–存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端
or replace 译为如果本次创建的存储过程/存储函数/视图。。。。。存在的话就替换它如果不存在就新建一个

---可以直接被调用。这一段pl/sql一般都是固定步骤的业务。
----给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is
begin
   update emp set sal=sal+100 where empno = eno;
   commit;
end;

select * from emp where empno = 7788;
----测试p1
declare

begin
  p1(7788);
end;

----通过存储函数实现计算指定员工的年薪
----存储过程和存储函数的参数都不能带长度
----存储函数的返回值类型不能带长度
create or replace function f_yearsal(eno emp.empno%type) return number
is
  s number(10);     
begin
  select sal*12+nvl(comm, 0) into s from emp where empno = eno;
  return s;
end;

----测试f_yearsal
----存储函数在调用的时候，返回值需要接收。
declare
  s number(10); 
begin
  s := f_yearsal(7788);
  dbms_output.put_line(s);
end;

---out类型参数如何使用
---使用存储过程来算年薪
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
   s number(10);
   c emp.comm%type;
begin
   select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
   yearsal := s+c;
end;

---测试p_yearsal
declare
  yearsal number(10);
begin
  p_yearsal(7788, yearsal);
  dbms_output.put_line(yearsal);
end;

in和out类型参数的区别是什么？
凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰。

存储过程和存储函数的区别
语法区别：关键字不一样，
存储函数比存储过程多了两个return。
本质区别：存储函数有返回值，而存储过程没有返回值。
如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值，
而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。
自定义函数

----我们可以使用存储函数有返回值的特性，来自定义函数。
----而存储过程不能用来自定义函数。
----案例需求：查询出员工姓名，员工所在部门名称。
----案例准备工作：把scott用户下的dept表复制到当前用户下。
create table dept as select * from scott.dept;
----使用传统方式来实现案例需求
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
----使用存储函数来实现提供一个部门编号，输出一个部门名称。
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
  dna dept.dname%type;
begin
  select dname into dna from dept where deptno = dno;
  return dna;
end;
---使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称。
select e.ename, fdna(e.deptno)
from emp e;

触发器
—触发器，就是制定一个规则，在我们做增删改操作的时候，
----只要满足该规则，自动触发，无需调用。
----语句级触发器：不包含有for each row的触发器。
----行级触发器：包含有for each row的就是行级触发器。
-----------加for each row是为了使用:old或者:new对象或者一行记录。
语句级触发器

----插入一条记录，输出一个新员工入职
create or replace trigger t1
after  --触发时间（dml语句执行前触发还是执行后触发 after：执行后触发 before：执行前触发）
insert --操作类型（例如：update delcet）
on person --触发器所在的表
declare

begin
  dbms_output.put_line('一个新员工入职');
end;
---触发t1
insert into person values (1, '小红');
commit;
select * from person;

行级别触发器

---不能给员工降薪
---raise_application_error(-20001~-20999之间, '错误提示信息');
create or replace trigger t2
before
update  --触发事件可以是一个或多个，如果多个触发事件使用or链接
on emp
for each row  --行级触发器标识
--when           --触发条件如果不写此句就默认为每次执行触发事件都触发此触发器
declare

begin
  if :old.sal>:new.sal then
     raise_application_error(-20001, '不能给员工降薪');
  end if;
end;
----触发t2
select * from emp where empno = 7788;
update emp set sal=sal-1 where empno = 7788;
commit;

语句级触发器与行级别触发器的区别
语法上：行级别触发器要比语句级触发器多出 for each row 语句
操作上：行级别触发器可以操作old和new而语句级触发器则不能