Oracle数据库编程

前言

pl/sql编程语言特点

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

表1：emp

表2：dept

表3：person

1 声明方法

select * from emp;
declare
  i number(2) := 10; --数字变量`在这里插入代码片`
  s varchar(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 = 7900; --通过查询，然后into赋值。
  dbms_output.put_line(ena);
  select * into emprow from emp where empno = 7900; --通过查询，然后into赋值。
  dbms_output.put_line(emprow.ename || '的工作是：' ||emprow.job); --通过 || 连接字符串。
end;

2 pl/sql中的if判断

---pl/sql中的if判断
---输入小于18的数字，输出未成年
---输入大于18小于40的数字，输出中年人
---输入大于40的数字，输出老年人
declare
  age number(3) := &age; --输入一个数
begin
  if age<18 then
    dbms_output.put_line(age || '岁的未成年');
    elsif age<40 then
      dbms_output.put_line(age || '岁的中成年');
      else
        dbms_output.put_line(age || '岁的老成年');
        end if;
end;
--if语句结构和java类似，在此不多赘述，参考下例。
declare
  age number(3) := &age; --输入一个数
begin
  if age<18 then
    dbms_output.put_line(age || '岁的未成年');
        end if;
end;

3 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;

4 游标

概念：

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

---输出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 e where e.deptno = eno; 
    en emp.empno%type;
begin
    open c2(10);
         loop
              fetch c2 into en;
              exit when c2%notfound;
              update emp e set sal=(sal+100) where e.empno = en;
              commit;
         end loop;
    close c2;      
end;
----查询10号部门员工信息
select * from emp e where e.deptno = 10;

5 存储过程

概念：

存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端。

可以直接被调用。这一段pl/sql一般都是固定步骤的业务。

----给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is

begin
  update emp set sal = sal +100 where emp.empno = eno;
  commit;
end;
--执行p1的存储过程
declare

begin
  p1(7788);
end;
select * from emp where empno = 7788;

6 存储函数

6.1 使用案例

注意：

存储过程和存储函数的参数都不能带长度

存储函数的返回值类型不能带长度

----通过存储函数实现计算指定员工的年薪
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;

6.2 out参数

案例：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;

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

1. 语法区别：

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

2. 本质区别：

   存储函数有返回值，而存储过程没有返回值。

   如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。

   即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值，

   而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。

   我们可以使用存储函数有返回值的特性，来自定义函数。

   而存储过程不能用来自定义函数。

----案例需求：查询出员工姓名，员工所在部门名称。
----案例准备工作：把scott用户下的dept表复制到当前用户下。
--复制scott用户下的dept表
create table dept as select * from scott.dept;
select * from dept;
--通过传统方法实现案例需求
select e.ename,d.dname
from dept d, emp e
where d.deptno = e.deptno;
----使用存储函数来实现提供一个部门编号，输出一个部门名称。
create or replace function f_getdname(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;
--测试f_getdname
select ename,f_getdname(deptno) from emp;

7 触发器

概念：

就是制定一个规则，在我们做增删改操作的时候，

只要满足该规则，自动触发，无需调用。

语句级触发器：

不包含有for each row的触发器。

行级触发器：

包含有for each row的就是行级触发器。

加for each row是为了使用:old或者:new对象或者一行记录，并且涉及到数据的变化。

7.1 语句级触发器

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

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

insert into person values(4,'李四');
commit;

select * from person;
--删除生日那一列表结构
alter table person drop column gender;

7.2 行级触发器

---不能给员工降薪
---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(-20002,'不能给员工降薪');
   end if;

end;

update emp set sal = sal-100 where empno=7788;
commit;
select * from emp where empno = 7788;


----触发器实现主键自增。【行级触发器】
---分析：在用户做插入操作的之前，拿到即将插入的数据，
------给该数据中的主键列赋值。
create sequence s_person;
select s_person.nextval from person;
select s_person.currval from dual;

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

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

insert into person (pname) values('王五');
commit;

select * from person;