PLSQL编程

PLSQL编程

定义

/*
     PLSQL编程 : procedure Language 过程语言 Oracle对SQL的一个扩展
             让我们能够像在java中一样写 if else else if 条件, 还可以编写循环逻辑 for while
             
             declare
                --声明变量
                变量名 变量类型;
                变量名 变量类型 := 初始值;
                  vsal emp.sal%type;  --引用型的变量  
                  vrow emp%rowtype;   --声明记录型变量          
             begin
                --业务逻辑
             end;
             
             dbms_output.put_line()相当于java中 syso 
*/

• 案例

declare
   i varchar2(10) := '张三';          
begin
  dbms_output.put_line(i);
end;

--查询7369的工资,并且打印出来
declare 
  --定义引用类型变量
  vsal emp.sal%type;
begin
  select sal into vsal from emp where empno = 7369;
  dbms_output.put_line(vsal);
end;

--查询7369的员工信息,并且打印出来

declare
  vrow emp%rowtype;
begin
  select * into vrow from emp where empno = 7369;
  dbms_output.put_line('姓名：'||vrow.ename || '工资：'|| vrow.sal);
end;

条件判断

• 定义

    /*
      PL条件判断
         
         if then
         
         elsif then
           
         else 
         
         end if;
    */
  

• 示例

--根据不同年纪,输出相关内容
declare
     --定义变量
     vage number := 15;
begin
  if vage > 0 and vage < 10 then
    dbms_output.put_line('小屁孩');
  elsif vage >= 10 and vage <= 20 then
    dbms_output.put_line('青年');
  else
    dbms_output.put_line('老年');
  end if;
end;

循环操作

• while循环

 while 循环
      while 条件 loop
        
      end loop;
--输出1~10
declare
  i number := 1;
begin
  while i <= 10 loop
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;

• for循环

 for循环
      for 变量  in [reverse] 起始值..结束值 loop            --reerse:翻转的意思
        
      end loop;

--输出1~10

declare

begin
  for i in reverse 1..10 loop
    dbms_output.put_line(i);
  end loop;
  
end;

• loop循环

loop循环  
      loop
        exit when 条件
      end loop;

--输出1~10
declare 
  i number := 1;
begin
  loop
    exit when i>10;
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;

输出菱形（没听懂）

/*

   *
  ***
 *****
  ***
   *   
输出 m  
   x : [-m,m]
   y : [-m,m]
   
   输出所有满足条件的 : abs(y)+abs(x) <=m
   
   m取值
*/
--使用PLSQL输出菱形
declare
   m number := 10;
begin
   for x in -m..m loop
     for y in -m..m loop
       if abs(y) + abs(x) <= m then
         dbms_output.put('*');
       else
         dbms_output.put(' ');
       end if;      
     end loop;
     dbms_output.new_line();
   end loop;  
end;

--使用PLSQL输出三角形,只要是三个角
declare
   m number := 10;
begin
   for x in reverse -m..m loop
     for y in -m..m loop
       if abs(y) + abs(x) <= m and x>=0 then
         dbms_output.put('*');
       else
         dbms_output.put(' ');
       end if;      
     end loop;
     dbms_output.new_line();
   end loop;  
end;

游标

/*
   游标：用来操作查询结果集，相当于JDBC中的resultSet
       语法：cursor 游标名 is 查询结果
       
       
       开发步骤：
            1. 声明游标
            2. 打开游标
            3. 从游标中取数据
                 fetch 游标名 into 变量
                 游标名%found：找到数据
                 游标名%notfound：没找到数据
            4. 关闭游标
                 close 游标名 
*/
--输出员工表中所有员工的姓名和工资
declare 
  --声明游标
  cursor vrows is select * from emp;
  --定义变量（记录型变量）
  vrow emp%rowtype;
begin
  --打开游标
  open vrows;
  loop
       --从游标中取出数据(循环取数据)
       fetch vrows into vrow;
       --设置循环结束条件
       exit when vrows%notfound;
       dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
  end loop;
  --关闭游标
  close vrows;
end;

--使用游标查询指定10号部门中所有员工的姓名和工资
declare
  --声明游标
  cursor vrows(dnum number) is select * from emp where deptno = dnum;
  --创建变量(记录型变量)
  vrow emp%rowtype;
begin
  --打开游标
  open vrows(10);
  --开启循环
       loop
         --从游标中取数据
         fetch vrows into vrow;
         --设置循环条件
         exit when vrows%notfound; 
           dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
       end loop;
  --关闭游标
  close vrows;
end;

/*
    系统引用游标：
        1. 声明游标：游标名 sys_refcursor
        2. 打开游标：open 游标名 for 结果集
        3. 从游标中获取数据
        4. 关闭游标
*/
--使用游标查询指定10号部门中所有员工的姓名和工资
declare 
  --声明系统引用游标
  vrows sys_refcursor;
  --声明变量
  vrow emp%rowtype;
begin 
  --打开游标
  open vrows for select * from emp;
  loop
    --从游标中获取数据
    fetch vrows into vrow;
    exit when vrows%notfound;
          dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
  end loop;
  close vrows;
end;

--扩展：使用for循环遍历游标
declare
  --定义游标
  cursor vrows is select * from emp;
  --定义变量
  vrow emp%rowtype;
begin
  --遍历游标
  for vrow in vrows loop
     dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal||'职位：'||vrow.job);
  end loop;
end;

--练习：按照员工职位给员工涨工资（总经理涨一千，经理涨八百，普通员工涨四百）

declare 
  --定义游标
  cursor vrows is select * from emp;
  --定义变量
  vrow emp%rowtype;
begin 
  --开启游标
  open vrows;
  --循环取数据
  loop 
    --取数据
    fetch vrows into vrow;
    --退出条件
    exit when vrows%notfound;
    --进行判断
    if vrow.job = 'PRESIDENT' then
      update emp set sal = sal + 1000 where empno = vrow.empno;
    elsif vrow.job = 'MANAGER' then 
      update emp set sal = sal +800 where empno = vrow.empno;
    else
      update emp set sal = sal +400 where empno = vrow.empno;
    end if;
  end loop;
  --关闭游标
  close vrows;
  --提交事务
  commit;
end;

意外(异常)

/*
        意外(例外)：程序运行过程中发生的异常，相当于是JAVA中的异常
        declare 
          --声明变量
        begin
          --业务逻辑
        exception
          --处理异常
          when 异常1 then
            ...
          when 异常2 then
            ...
          when others then
            ...处理其它异常
        end;
        
        
        zero_divide：除零异常
        value_error：类型转换异常
        too_many_rows：查询出多条记录但是赋值给了rowtype记录一行数据的变量
        no_data_found：没有找到记录
        
        sqlerrm
*/

declare
  vi number;
  vrow emp%rowtype;
begin
  --vi := 8/0;
  --vi := 'aaa'
  --select * into vrow from emp;
  select * into vrow from emp where empno = 1234567;
exception
  when zero_divide then
    dbms_output.put_line('发生了除零异常');
  when value_error then
    dbms_output.put_line('发生了类型转换异常');
  when too_many_rows then
    dbms_output.put_line('查询出多行记录，却赋值给了rowtype记录一行的数据变量');
  when no_data_found then
    dbms_output.put_line('发生了没有找到记录异常');
  when others then
     dbms_output.put_line('发生了其他异常：'||sqlerrm);
end;  

/*
     自定义异常
       异常名 exception;         定义异常(就像定义了一个异常类型的变量)
       raise 异常名;             抛出异常
*/
--查询一个指定编号的员工没有找到就抛出自定义异常
declare
  --声明一个游标
  cursor vrows is select * from emp where empno = 8888;
  --声明一个记录型变量
  vrow emp%rowtype;
  --声明一个自定义异常
  no_emp exception;
begin
  --打开游标
  open vrows;
  --取数据
  fetch vrows into vrow;
  --进行判断
  if vrows%notfound then
    raise no_emp;
  end if;
  --关闭游标
  close vrows;
exception
  when no_emp then
    dbms_output.put_line('发生了自定义异常：'||sqlerrm);
end;

存储过程

/*
    存储过程：实际上是封装在服务器上的一段PLSQL代码，已经编译好了的代码
              客户端调用存储过程，执行效率会非常的高效
         语法：
              create [or replace] procedure 存储过程的名称(参数名 in|out 参数类型,参数名 in|out 参数类型)
              is|as
              --声明部分
              begin
              --业务逻辑
              end;
*/
--对指定的员工涨薪，并打印涨薪前和涨薪后的工资
/*
     1. 需要传入两个参数
        员工编号
        要涨的工资
     2. 
*/

create or replace procedure proc_updatesal(vempno in number,vnum in number)
is
  --声明一个变量存储工资
  vsal number;
begin
  --查询涨工资前的数据
  select sal into vsal from emp where empno = vempno;
  --输出涨薪前工资
  dbms_output.put_line('涨薪前'||vsal);
  --更新工资
  update emp set sal = vsal + vnum where empno = vempno;
  --输出涨薪后工资
  dbms_output.put_line('涨薪后'||(vsal+vnum));
  --提交
  commit;
end;


--调用封装的过程
  --调用方法一
         call proc_updatesal(7788,10);
  ----调用方法二(用的最多的方式)
         declare 
         
         begin
           proc_updatesal(7788,10);
         end;

存储函数

/*
   存储函数：实际上是封装在PLSQL中的一段代码，已经编译好了的代码
         
         语法：
         create [or replace] function 存储函数名称(参数 in|out 参数类型,参数 in|out 参数类型) return 参数类型
         is|as
         --声明部分
         begin
         --逻辑部分
         end;
         
         存储函数和存储过程的区别
                1. 本质上没有区别
                2. 函数存在的意义是给过程调用(存储函数是给存储过程调用的)
                3. 函数可以在slq语句里面使用
                4. 存储过程能实现的，存储函数也能实现。存储函数能实现的，存储过程也能实现
*/
--查询指定员工的年薪

/*
     参数：员工编号
     返回值：年薪
*/
create or replace function func_getsal(vempno in number) return number
is
  --创建临时参数
  vtotalsal number;
begin
  --计算年薪，将数值赋值给vtotalsal
  select sal*12+nvl(comm,0) into vtotalsal from emp where empno = vempno;
  --将年薪返回
  return vtotalsal;
end;

--调用存储函数
declare
  --定义一个参数存储年薪
  vsal number;
begin
    vsal := func_getsal(7788);
    --输出年薪
    dbms_output.put_line(vsal);
end;

--查询员工的姓名及年薪
select ename,func_getsal(empno) from emp;


--查询指定员工的年薪（存储过程实现）

/*
     参数：员工编号
     返回值：年薪
*/
create or replace procedure proc_getsal(vempno in number,vtotalsal out number)
is

begin
  --计算年薪，将数值赋值给vtotalsal
  select sal*12+nvl(comm,0) into vtotalsal from emp where empno = vempno;
end;


--调用
declare 
  vtotalsal number;
begin
  proc_getsal(7788,vtotalsal);
  dbms_output.put_line(vtotalsal);
end;

JAVA调用Oracle中的存储函数和存储过程

package com.lld.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

import org.junit.Test;

import oracle.jdbc.driver.OracleTypes;

public class TestProcedure {

	/*
	  1.导入驱动包
	  2.注册驱动
	  3.获取连接
	  4.获取执行SQL的statement
	  5.封装参数
	  6.执行SQL
	  7.获取结果
	  8.释放资源  
	 */
	 //调用存储过程
	@Test
	public void test1() throws Exception{
		//2. 注册驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//3. 获取连接
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String username = "scott";
		String password = "1";
		Connection conn = DriverManager.getConnection(url, username, password);
		//4. 获取执行SQL的statement
		String sql = "{call proc_getsal(?,?)}";
		CallableStatement state = conn.prepareCall(sql);
		//5. 封装参数
		//设置输入参数
		state.setInt(1, 7788);
		//设置输出参数
		state.registerOutParameter(2, OracleTypes.NUMBER);
		//6. 执行SQL
		state.execute();
		//7. 获取结果
		int totalsal = state.getInt(2);
		System.out.println("存储过程输出的年薪：" + totalsal);
		//8. 释放资源
		state.close();
		conn.close();
	}
	//调用存储函数
	@Test
	public void test2() throws Exception{
		//2. 注册驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//3. 获取连接
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String username = "scott";
		String password = "1";
		Connection conn = DriverManager.getConnection(url, username, password);
		//4. 获取执行SQL的statement
		String sql = "{?= call func_getsal(?)}";
		CallableStatement state = conn.prepareCall(sql);
		//5. 封装参数
		//注册返回参数
		state.registerOutParameter(1, OracleTypes.NUMBER);
		//设置输入参数
		state.setInt(2, 7788);
		//6. 执行SQL
		state.execute();
		//7. 获取结果
		int totalsal = state.getInt(1);
		System.out.println("存储函数输出的年薪：" + totalsal);
		//8. 释放资源
		state.close();
		conn.close();
	}
}

触发器

/*
        触发器：当用户执行了insert|delete|update这些操作后，可以触发其他的一系列动作|业务逻辑
           作用：在动作之前之前或者之后触发业务逻辑
           语法：
                create [or replace] trigger 触发器名称
                before | after
                insert | update | delete
                on 表名
                [for each row]
                declare
                
                begin
                  
                end;
        
        触发器的分类
                语句级触发器：不管影响多少行，就触发一次
                行级触发器：影响多少行就触发多少次：在on后面加上for each row
*/
--插入员工之后输出一句话：欢迎加入黑马程序员
create or replace trigger tri_test1
after
insert
on emp
declare

begin
  dbms_output.put_line('欢迎加入黑马程序员');
end;

--测试
insert into emp(empno,ename) values(1234,'曲秃');

--周六老板不在,不能办理入职
--1. 在插入数据之前判断今天是周几
--2. 如果是周六就不能插入

create or replace trigger tri_test2
before
insert
on emp
declare
  vday varchar2(20);
begin
  /*
     获取系统当前日期：sysdate
     将日期转换为周：to_char(sysdate,'day')
     去除两端空格：trim()
  */
  select trim(to_char(sysdate,'day')) into vday from dual;
  if vday = '星期日' then
    dbms_output.put_line('老板不在不能办理');
    --抛出系统异常
    raise_application_error(-20001,'老板不在，不能办理入职');
  end if;
end;

--测试
insert into emp(empno,ename) values(1452,'曲秃2');

--判断员工涨工资后，新的工资一定要大于旧的工资

create or replace trigger tri_upda
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 -10;

--结和触发器使用序列
--在插入数据时，自动给pid赋值(因为在Oracle中没有自动增长)

--创建序列
create sequence seq_person_pid;

--创建表
create table person(
       pid number primary key,
       pname varchar2(20)
   );
   
--创建触发器。在插入数据之前对pid赋值
create or replace trigger tri_add_person_pid
before
insert
on person
for each row
declare

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

--测试

insert into person values(null,'曲秃');