oracle基础-pl/sql编程语言(判断/循环/存储过程/存储函数/游标)

 -- pl/sql编程语言
 -- pl/sql编程语言是对sql语言的扩展，使得sql具有更加过程化变成特点
 -- pl/sql编程语言比一般的过程化编程语言，更加灵活高效
 -- pl/sql编程语言主要用来编写存储过程和存储函数等
 
 -- 声明方法
 -- 赋值操作可以使用:=也可以使用into查询语句赋值
 declare -- 用于声明变量
    i number(2) := 10;
    s varchar(10) := '小明';
    ena emp.ename%type; -- 引用型变量
    emprow emp%rowtype; -- 记录型变量
 begin -- 在begin和end之间使用变量
   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;

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

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

-- 存储过程

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

-- 创建这个存储过程
-- 加上or replace 之后，能够直接修改这个过程对应的内容;
-- 不加的话，在这个过程已经存在的情况下，会报错，错误提示下划线在过程名这里;
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
as -- 或者是is
   用于声明变量
begin 
   PLSQL子程序体;
end;

--案例
-- 给指定员工涨100元
create or replace procedure p1(eno emp.empno%type) -- 这里没有在eno后写in还是out 则默认为in;
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 函数名(Name in type, Name in type) return 数据类型 
is
  变量结果 数据类型;
begin
  return(结果变量);
end 函数名;

-- 案例
-- 通过 存储函数 实现 计算指定员工的年薪
-- 存储过程和存储函数的参数都不能带长度
create or replace function f_yearsal(eno emp.empno%type) return number 
is
  s number(10); -- 自己定义的变量，可以带长度 '(10)';
begin
  select sal*12 + nvl(comm, 0) into s from emp where empno = eno; -- comm指的是奖金;
  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;

declare
  ttl number; 
begin
  p_yearsal(7788, ttl);
  dbms_output.put_line(ttl);
end;

-- in 和 out 类型参数的区别:
-- 凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰;
-- 否则 其余的都用in;

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

-- 我们可以使用存储函数有返回值的特性，来自定义函数;
-- 而存储过程不能用来自定义函数;
-- 案例需求: 查询出员工姓名，员工所在部门名称;
-- 传统方式
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;

-- 使用存储函数来实现 提供一个部门编号,输出一个部门名称
create or replace function fdna(dno emp.deptno%type) return dept.dname%type
is
  n dept.dname%type;
begin
  select d.dname into n from dept d where d.deptno = dno;
  return n;
end;

select e.ename, fdna(e.deptno)from emp e;

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

-- 在触发器中触发语句与伪记录变量的值                             
--------------------------------------------------------------------
--  触发语句  |  :old                    |  :new                  --
--  Insert    |  所有字段都是空(null)    |  将要插入的数据        --
--  Update    |  更新以前该行的值        |  更新后的值            --
--  delete    |  删除以前该行的值        |  所有字段都为空(null)  --
--------------------------------------------------------------------

-- 触发器案例
-- 插入一条记录，输出一个新员工入职
create or replace trigger tl
after
insert 
on dept
declare

begin
  dbms_output.put_line('一个新部门添加成功');
end;

-- 进行触发(在Output中进行结果查看)
insert into dept values (50, 'CUSTOMER', 'CHINA' );

-- 行级触发器
-- 不能给员工降薪
-- 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;

-- 验证
update emp set sal = sal-1 where empno =7788;
commit;

--案例
--使用触发器实现主键自增 行级触发器
--分析： 在用户做插入操作之前，拿到即将插入的数据,
--       给该数据中的主键列赋值
create or replace trigger auid
before 
insert
on dept
for each row
declare
begin
  --首先创建一个序列s_dept
  select s_dept.nextval into :new.deptno from dual;
end;

--使用该触发器
insert into dept(dname,loc) values('HUMAN RESOURCE', 'CHINA');
commit;

-- 使用java来操作存储过程
-- jar包
-- oracle10g ojdbc14.jar
-- oracle11g ojdbc6.jar