Oracle笔记：pl/sql编程语言

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

• 声明方法
  赋值操作：可以使用 := 也可以使用 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;
  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) := &age;
begin
  if i<=18 then
    dbms_output.put_line(i || '是未成年');
  elsif i<40 then
    dbms_output.put_line(i || '是中年人');
  else 
    dbms_output.put_line(i || '是老年人');
  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;

• 游标：可以存放多个对象，多行记录，相当于java的数组。

--输出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;
--查询10号部门的员工工资信息
select * from emp where deptno = 10;

• 存储过程
  存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端可以直接被调用。这一段pl/sql一般都是固定步骤的业务。

create or replace procedure p1(eno emp.empno%type)
is

begin
  update emp set sal = sal-100 where empno = eno;
end;

--使用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类型参数
  in和out类型参数的区别？
  凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用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;

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

--案例需求：查询出员工的姓名、员工的所在部门名称

--使用传统方式实现案例需求
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;

• 触发器，就是制定一个规则，在我们做增删改操作的时候，只要满足该规则，自动触发，无需调用。

1. 语句级触发器：不包含有for each row 的触发器。
2. 行级触发器：包含有for each row的就是行级触发器。加入for each row是为了使用 :old或者 :new对象或者一行记录。

--语句级触发器:插入一条记录，输出一个新员工入职
create or replace trigger ti
after
insert 
on person
declare

begin
  dbms_output.put_line('一个新员工入职');
end;

--触发t1
insert into person values(1,'小明');

--行级触发器：不能给员工降薪
--raise_application_error(-20001~-20999之间，不能重复,'错误提示信息');
create or replace trigger t2
before
update
on emp
for each row
declare

begin
  if :old.sal>:new.sal then
    raise_application_error(-20001,'不能给员工降薪');
  end if;
end;

--触发t2
update emp set sal = sal-1 where empno = 7788;
commit;

• 触发器实现主键自增（行级触发器）

--在用户做插入操作之前，拿到即将要插入的数据，给该数据中的主键赋值。
create or replace trigger auid
before 
insert
on person
for each row
declare

begin
  select s_person.nextval into :new.pid from dual;
end;

--使用auid实现主键自增
insert into person (pname) values ('a');
insert into person values (1,'b');

select * from person;