oracle第三阶段

发表于   |   分类于   |   阅读次数

Oracle 第三阶段

PL/SQL存储语言

PL/SQL（Prodedure Language/SQL）
PLSQL 是Oracle对sql语言的过程化扩展，指在SQL命名语言中增加了过程

PLSQL语言

1
2
3
4
5
6
[delcare]
   -- 声明部分（变量，光标，例外）
begin
[excetion]
   -- 异常处理
end;

PLSQL的赋值

  • 定义number变量，定义PI常量，定义记录型变量，定义引用型变量
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    declare 
      i number:=1;               --变量赋值
      j number;
      PI constant number :=3.14; --常量赋值
      pemp emp%rowtype;          --记录型变量赋值
      pname emp.ename%type;      --引用型变量pname用的ename的数据类型
    begin
      j:=2;                      --变量也可以在这赋值
      dbms_output.put_line(i);
      dbms_output.put_line(j);
      select * into pemp from emp where empno=7788;       --记录型变量引用
      select ename into pname from emp where empno=7788;  --引用型变量引用
    end;

pemp 记录型变量，只能存储一条记录，用pemp.empno来取值

if分支

1
2
3
4
5
6
begin
     if 条件判断 then
     elsif 条件判断 then
     else
     end if;
end;
  • 从控制台输入一个数字，如果数字是1，则输出我是1
1
2
3
4
5
6
7
8
9
declare
  i number :=#
begin 
  if (i=1) then     --括弧可写可不写
     dbms_output.put_line('我是1');
  elsif
     dbms_output.put_line('我不是1');
  end if;
end；
  • 如果输入的年龄在18岁以下，输出未成年人，18~40：成年人，40以上 老年人
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    declare 
      i number :=&i;
    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;

loop循环

  1. 语法1：
    while 循环条件 loop
    end loop;
  2. 语法2：(常用)
    loop
    exit when 退出循环条件；
    end loop;
  3. 语法3:
    for i in 1..10 loop
    end loop;

  • 语法1实现在控制台输出1~10

    1
    2
    3
    4
    5
    6
    7
    8
    declare 
      i number :=1;
    begin
      while i<11 loop
         dbms_output.put_line(i); 
         i:=i+1;
      end loop;
    end;

  • 语法2实现在控制台输出1~10(掌握)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    declare 
      i number :=1;
    begin 
      loop
        exit when i >10;
        dbms_output.put_line(i); 
        i:=i+1;
      end loop;
    end;

  • 语法3实现在控制台输出1~10

1
2
3
4
5
6
7
declare
begin
   for i in 1..10 loop
   dbms_output.put_line(i); 
   end loop;
end;

光标（游标）

光标：就是在ＰＬ/SQL编程中结果集的概念

  • 语法：cursor 光标名称 is select查询语句;

  • 提取游标：

    1
    2
    3
    4
    5
    6
       open 游标名称;
         loop
           fetch 游标名称 into 变量;
           exit when 游标%notfound;
         end loop;
       close 游标名称;

  • 使用光标输出emp表中7369的员工信息

    1
    2
    3
    4
    5
    6
    7
    8
    9
    declare
       cursor emp_cur is select * from emp where empno=7369;
       pemp emp%rowtype;
    begin
       open emp_cur;
         fetch emp_cur into pemp;
         dbms_output.put_line(pemp.ename); 
       close emp_cur;
    end;

  • 输出指定部门的员工信息

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    declare
       cursor emp_cur(dno number) is select * from emp where deptno=dno;
       pemp emp%rowtype;
    begin
       open emp_cur(20);
         loop
            fetch emp_cur into pemp;
            exit when emp_cur%notfound;
            dbms_output.put_line(pemp.ename);
          end loop;
       close emp_cur;
    end;

  • 给部门编号为10 的员工涨工资

1
2
3
4
5
6
7
8
9
10
11
12
13
declare
   cursor emp_cur(dno number) is select * from emp where deptno=dno;
   pemp emp%rowtype;
begin
   open emp_cur(10);
     loop
        fetch emp_cur into pemp;
        exit when emp_cur%notfound;
        update emp set sal=sal+1000 where empno=pemp.empno;
        dbms_output.put_line(pemp.ename); 
     end loop;  
    close emp_cur;
end;

例外（异常）

  • 系统异常，被0除的zero_divide异常
1
2
3
4
5
6
7
8
declare 
  i number :=1;
begin
  i:=i/0;
exception
  when zero_divide then
   dbms_output.put_line('不能除0'); 
end;

  • 系统异常，设置错误的value_error异常

    1
    2
    3
    4
    5
    6
    7
    8
    declare 
      i number :=1;
    begin
      i:='hello';
    exception
      when value_error then
       dbms_output.put_line('赋值错误'); 
    end;

  • 系统异常，都可以通过others异常捕获

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    declare 
      i number :=1;
    begin
      i:=1/0;
    exception
      when value_error then
       dbms_output.put_line('赋值错误');
      when others then
       dbms_output.put_line('有错误');   
    end;

  • 自定义异常，查询部门编号为50员工（自定义exception）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    declare  
      cursor emp_cur is select * from emp where deptno =20;
      pemp emp%rowtype;
      no_date exception;
    begin
      open emp_cur;
        fetch emp_cur into pemp;
      if emp_cur%notfound then
        raise no_date;
      end if;
      exception
       when no_date then
        dbms_output.put_line('没有查询到数据！'); 
    end;

存储过程

定义：将提前编译好的一段plsql,存放在数据库端，供其他程序员调用

语法：

1
2
3
4
5
6
    create [or replace] procedure 过程名称（参数1 in|out 类型）
    as|is
            --声明一些变量(declare中的东西)
    begin
    end;

  • 声明pro_add_sal存储过程,作用是给指定员工涨100工资,并打印出涨前和涨后工资
    1
    2
    3
    4
    5
    6
    7
    8
    9
    create or replace procedure pro_add_sal(eno in number)
    as
      psal number;
    begin 
       select sal into psal from emp where empno=eno;
       dbms_output.put_line('涨前工资'||psal);
       update emp set sal = sal + 100 where empno=eno;
       dbms_output.put_line('涨后工资'||(psal+100));
    end;

存储过程中没有错误提示，要想看是否运行正确，就在左侧中看是否有procedure中是否有叉

  • 调用存储过程
1
2
3
begin;
  pro_add_sal(7788);
end;

  • 创建一个存储过程pro_emplist（输出参数为游标类型）

    • 输入一个部门编号，将此部门下的所有员工信息输出（游标变量为sys_refcursor)）

      1
      2
      3
      4
      5
      create or replace procedure pro_emplist(dno number,emplist out sys_refcursor)
      as
      begin
         open emplist for select * from emp where deptno=dno;
      end;

    • 调用:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      declare
        emplist sys_refcursor;
        pemp emp%rowtype;
      begin
        pro_emplist(10,emplist);
        loop 
          fetch emplist into pemp;
          exit when  emplist%notfound;
          dbms_output.put_line(pemp.empno || pemp.ename);   
        end loop;  
        close emplist;
      end;

存储函数

  • 语法：
    1
    2
    3
    4
    5
    6
    7
    8
        //参数中in代表从输入参数，out表示输出参数
        create or replace function 函数名（参数1 in|out 类型，参数2 in|out 类型） retrun 结果类型
        as|is
             --定义变量
        begin
          
            return 变量;--(变量的类型一定是跟 return的结果类型保持一致)
        end;

  1. 声明fun_emp_totalsal存储函数,查询指定员工的年薪

    • 声明

      1
      2
      3
      4
      5
      6
      7
      create or replace function fun_emp_totalsal(eno number) return number
      as
        psal number;
      begin 
         select sal*12+nvl(comm,0) into psal from emp where empno=eno;
         return psal;
      end;

    • 调用

      1
      2
      3
      4
      5
      6
      declare
        totalSal number;
      begin 
        totalSal:= fun_emp_totalsal(7788);
        dbms_output.put_line(totalSal);
      end;

  2. 声明pro_emp_totalsal存储过程,查询指定员工的年薪

    • 声明

      1
      2
      3
      4
      5
      create or replace function pro_emp_totalsal(eno number,totalSal out number)
      as
      begin
         select sal*12+nvl(comm,0) into totalSal from emp where empno=eno;
      end;

    • 调用

      1
      2
      3
      4
      5
      6
      declare
        total number;
      begin
        pro_emp_totalsal(7788,total);
        dbms_output.put_line(total);
      end;

  3. 声明fun_emp_dname存储函数,根据部门编号查询出部门名称

    • 声明

      1
      2
      3
      4
      5
      6
      7
      create or replace function fun_emp_dname(dno number) return dept.dname%type
      as
        pname dept.dname%type;
      begin
        select dname into pname from dept where deptno=dno;
        return pname;
      end;

    • 调用

      1
      2
      3
      4
      5
      6
      declare
        pname dept.dname%type;
      begin
        pname :=fun_emp_dname(10);
        dbms_output.put_line(pname);
      end;
  • 在select 调用存储函数
    select empno,ename,fun_emp_dname(deptno) from emp;

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

  • 区别：
    1. 语法不一样
    2. 存储函数必须有返回值
    3. 存储过程虽然没有返回值，但是可以指定输出参数类型
    4. 存储函数可以在select 中使用

java程序调用存储过程

  1. 导入Oracle10g驱动包 ojdbc14-10.2.0.1.0.jar
  2. 通过sql数据库
    1
    2
    3
    4
    5
    6
    7
    8
    9
    @Test
        public void test01() throws Exception{
            cs=conn.prepareCall("select * from emp");
            rs=cs.executeQuery();
            while(rs.next()){
            //两种方式
                System.out.println(rs.getObject(1)+"、"+rs.getString("ename"));
            }
        }
  • junit测试框架中有一个注解@before,意思就是在每次执行Junit的时候先执行这个Before，
    • 当然还有一个After注解，在最后执行
    • 所以可以加一个注解来初始化参数加一个销毁来关闭连接
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      private CallableStatement cs;
      private Connection conn ;
      private ResultSet rs;
      @Before
          public void init() throws Exception{
              String driver="oracle.jdbc.driver.OracleDriver";
              String url="jdbc:oracle:thin:@192.168.241.130:1521:orcl";
              String user="scott";
              String password="tiger";
              Class.forName(driver);
              //获取连接对象
              conn = DriverManager.getConnection(url,user,password);
          }
      @After
      public void destory() throws Exception{
          if(rs!=null){
              rs.close();
          }
          if (cs != null){
              cs.close();
          }
          if (conn != null){
              conn.close();
          }
      }

  1. 调用存储过程，没有结果集

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    //call pro_add_sal(eno number)
       @Test
        /**
         * 调用存储过程，没有结果集
         */
        public void test02() throws Exception{
            cs=conn.prepareCall("{call pro_add_sal(?)}");
            cs.setInt(1, 7369);
            cs.executeQuery();
        }

  2. 调用存储过程，有结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    //call pro_emp_totalsal(eno number,total out number)
    @Test
        /**
         * 调用有返回值的存储过程
         * @throws SQLException
         */
        public void test03() throws SQLException{
            cs=conn.prepareCall("{call pro_emp_totalsal(?,?)}");
            cs.setInt(1, 7788);
            // 注册一个输出参数
            cs.registerOutParameter(2, OracleTypes.NUMBER);
            cs.executeQuery();
            System.out.println(cs.getObject(2));
        }

  3. 调用存储过程，有结果值cursor类型

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    //pro_emplist(dno number,emplist out sys_refcursor)
    @Test
        /**
         * 调用存储过程，有结果值cursor类型
         */
        public void test04() throws Exception{
            cs=conn.prepareCall("{call pro_emplist(?,?)}");
            cs.setInt(1, 20);
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            //因为是cursor类型的所以需要强转一下CallableStatement为OracleCallableStatement
            //不强转的话没有取出cursor的方法
            cs.executeQuery();
            rs=((OracleCallableStatement)cs).getCursor(2);
            while(rs.next()){
                System.out.println(rs.getObject(1)+"-----"+rs.getObject(2));
            }
        }

触发器

触发器：验证数据是否合法，满足条件既触发

语法：

1
2
3
4
5
6
7
8
create or replace trigger 触发器名称
after|before
insert|update|delete
on 表
for each row  ---:new :old就是 插入，修改，删除 后的新旧值存在里面
              ---: 需要时写这句话,只要有:new :old 就要写这句话
begin
end;

  • 添加一个员工后打印一句话“一个新员工添加成功”

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create or replace trigger emp_insert_after
    after
    insert
    on emp
    begin
       dbms_output.put_line('一个新员工添加成功');
    end;
    insert into emp (empno,ename) values (8888,'陈老师');
    select * from emp;

  • 不能在休息时间办理入职

    • raise_application_error(参数1，参数2);
      参数1：错误代码 在 -20001~ -20999‘
      参数2：提示错误信息
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      create or replace trigger emp_insert_before
      before
      insert
      on emp
      declare
       dayStr varchar2(50);
      begin
        select to_char(sysdate,'day') into dayStr from dual;
        if trim(dayStr) = 'friday ' then
            raise_application_error(-20001,'休息时间,工资三薪,我需要加薪');
        end if;
      end;
      insert into emp (ename) values ('阿娇1');
      commit;

  • 不能给员工降薪

    • 在使用:old :new 时候就得加上for each row (行级触发器)
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      create or replace trigger emp_before_update
      before
      update
      on emp
      for each row
      begin
        if :old.sal > :new.sal then
           raise_application_error(-20002,'不能降薪！！！');
        end if;
      end;
      update emp set sal = sal -1000;

  • 触发器应用（通过序列在插入数据的时候，将ID用序列赋值）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    create sequence emp_seq;
    select emp_seq.nextval from dual;
    create or replace trigger emp_insert_before_id
    before
    insert
    on emp
    for each row
    begin
       select emp_seq.nextval into :new.empno from dual;
    end;
    insert into emp (ename) values ('阿娇1');
    commit;
    select * from emp;
张冲 wechat
欢迎扫一扫上面的微信关注我，一起交流！
坚持原创技术分享，您的支持将鼓励我继续创，点击打赏！