Oracle入门文档(二)

----pl/sql编程语言

----pl/sql编程语言是对sql语言的扩展，使得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) := ⅈ

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;

 

 

----游标：类似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一般都是固定步骤的业务。

----给指定员工涨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来修饰，其余都用in，默认是in

 

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

----语法区别：关键字不一样，存储过程procedure，存储函数是function

----存储函数比存储过程多了两个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

insert

on person

declare

 

begin

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

end;

 

----触发t1

insert into person values(2,'憨憨');

commit;

select * from person;

 

----行级别触发器

----不能给员工降薪

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;

---查询person表数据

select * from person;

----使用auid实现主键自增

insert into person (pname) values ('哈哈哈');

commit;