6、oracle入门篇

1、游标

概念：可以存放多个对象，多行记录。

列子：

---输出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;

2、存储过程

概念： 存储过程就是提前已经编译好的一段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;

----测试p1
declare

begin
  p1(7788);
end;

列子2：

----通过存储函数实现计算指定员工的年薪
----存储过程和存储函数的参数都不能带长度
----存储函数的返回值类型不能带长度
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来修饰。

2、存储函数

----使用存储函数来实现提供一个部门编号，输出一个部门名称。
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;

存储过程和存储函数的区别

语法区别：关键字不一样，存储函数比存储过程多了两个return。

本质区别：存储函数有返回值，而存储过程没有返回值。
如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值，
而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。

3、触发器

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

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

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

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

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

--查看数据
select * from person;

----触发器实现主键自增。【行级触发器】
---分析：在用户做插入操作的之前，拿到即将插入的数据，给该数据中的主键列赋值。
---s_person序列是需要自己定义创建。

create or replace trigger auid
before
insert
on person
for each row
declare

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

--查询person表数据
select * from person;

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

insert into person values (1, 'b');
commit;