Oracle实战笔记(第七天)之PL/SQL进阶

一、控制结构

  控制结构包括:判断语句(条件分支语句)、循环语句、顺序控制语句三种。

  1、条件分支语句

  • if--then:简单条件判断
    --编写一个过程,可以输入一个雇员名,如果该雇员名的工资低于2000,就给该雇员工资增加10%
    create or replace procedure pro_addSal(v_ename varchar2) is
    --定义变量
      v_sal emp.sal%type;
      begin
        select sal into v_sal from emp where ename=v_ename;
      --判断
      if v_sal<2000 then
        update emp set sal=sal+sal*0.1 where ename=v_ename;
      end if;
     end;
    /
  • if--then--else:二重条件分支
    --编写一个过程,可以输入一个雇员名,如果该雇员名的工资低于2000,就给该雇员工资增加10%,否则减少10%
    create or replace procedure pro_addSal(v_ename varchar2) is
    --定义变量
      v_sal emp.sal%type;
      begin
        select sal into v_sal from emp where ename=v_ename;
      --判断
      if v_sal<2000 then
        update emp set sal=sal+sal*0.1 where ename=v_ename;
      else
        update emp set sal=sal-sal*0.1 where ename=v_ename;
      end if;
     end;
    /
  • if--then--elsif--else:多重条件分支
    create or replace procedure pro_addSal(eNo number) is  
      v_job emp.job%type;  
    begin  
        select job into v_job from emp where empno=eNo;
      if v_job='PRESIDENT' then
         update emp set sal=sal+1000 where empno=eNo;
      elsif v_job='MANAGER' then
        update emp set sal=sal+500 where empno=eNo;
      else
        update emp set sal=sal+200 where empno=eNo;
      end if;
    end;
    /

  2、循环语句

  • loop循环:pl/sql中最简单的循环语句,以loop开头,以exit()作为结束判断语句,以end loop结尾。(至少循环一次)
    --编写一个过程,输入用户名,并循环添加10个用户到users表中
    create table users(userId number(5),userName varchar(20));--为了后面操作先创建一个users表
    create or replace procedure pro_addUser(eName varchar2) is
    --定义变量
      v_num number:=1;
    begin
      loop
        insert into users values(v_num,eName);
        exit when v_num=10;--判断退出条件
        v_num:=v_num+1;--自增
      end loop;
    end;
    /
  • while循环:其实就是使用while语句来代替loop循环的exit语句。
    --编写一个过程,删除users表中的编号1—10的个用户
    --用户编号从1开始增加。
    create or replace procedure pro_delUser is
    --定义变量
      v_num number:=1;
    begin
      while v_num<=10 loop
         delete from users where userId=v_num;
         v_num:=v_num+1;--自增
        end loop;
    end;
    /
  • for循环:自带变量和循环退出条件
    create or replace procedure pro_addUser is
    begin  
      for i in 1..10 loop  
        insert into users values(i,'lucy');
      end loop;  
    end;
    /

  3、顺序控制语句

  • goto语句:用于跳转到特定标号去执行语句。注:由于使用gogo语句会增加程序的复杂性,并使得应用程序可读性变差,因此建议不要使用goto语句。
    语法:goto lable,其中lable是已经定义好的标号名,如<<标记名>>,<<>>是标记符号,常用来跳出循环。
    --循环输出i=1..10,最后跳出循环后打印“循环结束”
    declare
      i int:=1;
    begin
      loop
      dbms_output.put_line('i='||i);
      if i=10 then
        goto end_loop;
      end if;
        i:=i+1;
      end loop;
      <<end_loop>>
      dbms_output.put_line('循环结束');
    end;
    /
  • null语句:null语句不会执行任何操作,并且会直接将控制传递到下一条语句。(类似Java中的continue的用法)
    declare
      v_sal emp.sal%type;
      v_ename emp.ename%type;
    begin
      select ename,sal into v_ename,v_sal from emp where empno=&no;
      if v_sal<3000 then
        update emp set comm=sal*0.1 where ename=v_ename;
      else
        null;
      end if;
    end;
    /

二、使用Java程序调用存储过程

  1、无返回值的存储过程

  创建一个表book,表结构如下:

   

  • create table book(bId number(4) primary key,bName varchar(30) not null,publisher varchar(30));

  编写一个过程,向book表添加书籍信息,要求可以通过java程序调用该过程:

  • 使用命令行创建:
    create or replace procedure pro_addBook(bookId number,bookName varchar2,pub varchar2) is
    begin
      insert into book values(bookId,bookName,pub);
    end;
    /
  • 使用Java调用无返回值的过程:
     1 package test;
     2 import java.sql.CallableStatement;
     3 import java.sql.Connection;
     4 import java.sql.DriverManager;
     5 import java.sql.SQLException;
     6 
     7 import org.junit.Test;
     8 
     9 /**
    10  * 使用java调用Oracle创建的过程pro_addBook
    11  */
    12 public class callPro_addBook {
    13     @Test
    14     public void test(){
    15         Connection conn = null;
    16         CallableStatement cs = null;
    17         try{
    18             //连接数据库
    19             Class.forName("oracle.jdbc.driver.OracleDriver");
    20             conn = DriverManager.getConnection(
    21                     "jdbc:oracle:thin:@192.168.183.1:1521:orcl","scott","tiger");
    22             //获得执行对象
    23             cs = conn.prepareCall("{call pro_addBook(?,?,?)}");
    24             //传参
    25             cs.setInt(1, 1001);
    26             cs.setString(2, "五年模拟三年高考");
    27             cs.setString(3, "教育出版社");
    28             //执行
    29             cs.execute();
    30         }catch(Exception e){
    31             e.printStackTrace();
    32         }finally{
    33             try {
    34                 cs.close();
    35                 conn.close();
    36             } catch (SQLException e) {
    37                 e.printStackTrace();
    38             }
    39         }
    40     }
    41 }
    callPro_addBook

  2、有返回值的存储过程(返回若干值)

  编写一个过程,要求输入book表的书号就返回书籍信息:书名和出版社

  • 使用命令行创建过程:
    create or replace procedure pro_showBook
      (bookId in number,bookName out varchar2,pub out varchar2) is
    begin
      select bName,publisher into bookName,pub from book where bId=bookId;
    end;
    /
  • 使用Java调用返回值是若干数据的过程
     1 package test;
     2 import java.sql.CallableStatement;
     3 import java.sql.Connection;
     4 import java.sql.DriverManager;
     5 import java.sql.SQLException;
     6 
     7 import org.junit.Test;
     8 
     9 /**
    10  * 使用java调用Oracle创建的过程pro_addBook
    11  */
    12 public class callPro_showBook {
    13     @Test
    14     public void test(){
    15         Connection conn = null;
    16         CallableStatement cs = null;
    17         try{
    18             //连接数据库
    19             Class.forName("oracle.jdbc.driver.OracleDriver");
    20             conn = DriverManager.getConnection(
    21                     "jdbc:oracle:thin:@192.168.183.1:1521:orcl","scott","tiger");
    22             //获得执行对象
    23             cs = conn.prepareCall("{call pro_showBook(?,?,?)}");
    24             //传入参数
    25             cs.setInt(1, 1001);
    26             cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR );  
    27             cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR );
    28             //执行
    29             cs.execute();
    30             //获取out参数
    31             String bookName = cs.getString(2);
    32             String pub = cs.getString(3);
    33             System.out.println("书名:"+bookName);
    34             System.out.println("出版社:"+pub);            
    35         }catch(Exception e){
    36             e.printStackTrace();
    37         }finally{
    38             try {
    39                 cs.close();
    40                 conn.close();
    41             } catch (SQLException e) {
    42                 e.printStackTrace();
    43             }
    44         }
    45     }
    46 }
    callPro_showBook

  3、有返回值的存储过程(返回一个列表)

  为了方便说明,我们再往book表中添加几条数据:

  

  现在的需求是:创建一个过程,要求返回指定出版社如“知乎周刊”出版的书籍信息。

    如表所示,返回结果是三本书,而这种查询结果集我们一般放在一个list即列表中,而在oracle在接受返回值时需要使用包package,并用游标来进行参数输出:

  • --建立包,在该包中,定义一个游标类型test_cursor
    create or replace package testpackage as
      type test_cursor is ref cursor;
      end;
    /
  • 使用命令行创建过程:
    create or replace procedure pro_showPubBook
      (pub in varchar2,my_cursor out testpackage.test_cursor) is
    begin
      open my_cursor for select * from book where publisher=pub;
    end;
    /
  • 使用Java调用返回值是列表的过程:
     1 package test;
     2 import java.sql.CallableStatement;
     3 import java.sql.Connection;
     4 import java.sql.DriverManager;
     5 import java.sql.ResultSet;
     6 import java.sql.SQLException;
     7 
     8 import org.junit.Test;
     9 
    10 /**
    11  * 使用java调用Oracle创建的过程pro_addBook
    12  */
    13 public class callPro_showPubBook {
    14     @Test
    15     public void test(){
    16         Connection conn = null;
    17         CallableStatement cs = null;
    18         try{
    19             //连接数据库
    20             Class.forName("oracle.jdbc.driver.OracleDriver");
    21             conn = DriverManager.getConnection(
    22                     "jdbc:oracle:thin:@192.168.183.1:1521:orcl","scott","tiger");
    23             //获得执行对象
    24             cs = conn.prepareCall("{call pro_showPubBook(?,?)}");
    25             //传入参数
    26             cs.setString(1, "知乎周刊");
    27             cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR );//游标类型
    28             //执行
    29             cs.execute();
    30             //获得结果集
    31             ResultSet rs = (ResultSet) cs.getObject(2);
    32             System.out.println("知乎周刊出版社书籍:");
    33             if(rs!=null)
    34                 while(rs.next()){
    35                     System.out.println("书号:"+rs.getInt(1)+"  "+"书名:《"+rs.getString(2)+"》");
    36                 }
    37             else
    38                 System.out.println("暂无书籍");
    39         }catch(Exception e){
    40             e.printStackTrace();
    41         }finally{
    42             try {
    43                 cs.close();
    44                 conn.close();
    45             } catch (SQLException e) {
    46                 e.printStackTrace();
    47             }
    48         }
    49     }
    50 }
    callPro_showPubBook

三、分页编程

  案例:编写一个存储过程,要求可以输入表名、每页显示记录数、当前页,返回总记录数、总页数和返回的结果集。

  1、使用rownum分页查询

  • select * from emp;
    select t1.*,rownum rn from (select * from emp) t1;
    select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
    select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rownum>=6;

  2、编写分页的存储过程

  • --编写分页的存储过程
    create or replace procedure fenye
      (tableName in varchar2,--in表名
      myPageSize in number,--in记录数
      pageNow in number,--in当前页
      myRows out number,--out总记录数
      myPageCount out number,--out总页数
      p_cursor out testpackage.test_cursor--out结果集
      )is
    
      v_sql varchar2(500);--定义sql语句
      v_begin number:=(pageNow-1)*myPageSize+1;--定义起始页
      v_end number:=pageNow*myPageSize;--定义尾页
      
    begin
      --执行分页查询语句
      v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||
     ') t1 where rownum<='||v_end||') where rn>='||v_begin; --把游标和sql语句关联 open p_cursor for v_sql; --计算myRows v_sql:='select count(*) from '||tableName; execute immediate v_sql into myRows; --计算myPageCount if mod(myRows,myPageSize)=0 then myPageCount:=myRows/myPageSize; else myPageCount:=myRows/myPageSize+1; end if;end; /

  3、使用Java调用分页过程

  •  1 import java.sql.CallableStatement;
     2 import java.sql.Connection;
     3 import java.sql.DriverManager;
     4 import java.sql.ResultSet;
     5 
     6 public class Test {
     7       public static void main(String[] args) {
     8              // TODO Auto-generated method stub
     9             Connection ct = null;
    10             CallableStatement cs = null;
    11              try {
    12                   Class. forName("oracle.jdbc.driver.OracleDriver");
    13                    ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" );
    14 
    15                    cs = ct.prepareCall( "{call fenye(?,?,?,?,?,?)}");
    16 
    17                    // 赋值
    18                    cs.setString(1, "emp");
    19                    cs.setInt(2, 5);
    20                    cs.setInt(3, 1);
    21 
    22                    // 注册总记录数
    23                    cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER );
    24                    // 注册总页数
    25                    cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER );
    26                    // 注册返回的结果集
    27                    cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR );
    28 
    29                    cs.execute();
    30                    // 取出总记录数
    31                    int rowNum = cs.getInt(4);
    32                    // 取出总页数
    33                    int pageCount = cs.getInt(5);
    34                   ResultSet rs = (ResultSet) cs.getObject(6);
    35 
    36                    // 显示
    37                   System. out.println( "rowNum=" + rowNum);
    38                   System. out.println( "pageCount=" + pageCount);
    39 
    40                    while ( rs.next()) {
    41                         System. out.println( "编号:" + rs .getInt(1) + ",姓名:" + rs .getString(2));
    42                   }
    43             } catch (Exception e) {
    44                    // TODO Auto-generated catch block
    45                    e.printStackTrace();
    46             } finally {
    47                    try {
    48                          // 关闭资源
    49                          cs.close();
    50                          ct.close();
    51                   } catch (Exception e1) {
    52                          // TODO Auto-generated catch block
    53                          e1.printStackTrace();
    54                   }
    55             }
    56      }
    57 }

  

四、例外处理

  1、分类

  • 预定义例外:用于处理常见的oracle错误。
  • 非预定义例外:用于处理与预定义例外不能处理的例外。
  • 自定义例外:用于处理与oracle错误无关的其他情况。

  2、一个简单的例外处理

  编写一个过程,可接收雇员的编号,并显示该雇员的姓名。如果输入的雇员编号不存在,如何处理?

  • --例外
    declare
      v_ename emp.ename%type;
    begin
      select ename into v_ename from emp where empno=&no;
      dbms_output.put_line('名字:'||v_ename);
    exception when no_data_found then
      dbms_output.put_line('编号不存在,请重新输入!');
    end;
    /

  3、预定义例外

  由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时,则会隐含触发一个内部例外。pl/sql为开发人员提供了20多个预定义例外。

  •  case_not_found:when子句中没有包含必须的条件分支,就会触发case_not_found的例外。
    --case_not_found
    create or replace procedure sp_pro11(spno number) is
      v_sal emp.sal%type;
    begin
      select sal into v_sal from emp where empno=spno;
    case
      when v_sal<1000 then
        update emp set sal=sal+100 where empno=spno;
      when v_sal<2000 then
        update emp set sal=sal+200 where empno=spno;
    end case;
    exception
      when case_not_found then
        dbms_output.put_line('case语句没有与'||v_sal||'相匹配的条件');
    end;
    /
  • cursor_already_open当重新打开已经打开的游标时,会隐含触发例外cursor_already_open。
    --cursor_already_open
    declare
      cursor emp_cursor is select ename,sal from emp;
    begin
      open emp_cursor;
      for emp_reacord1 in emp_cursor loop
        dbms_output.put_line(emp_record1.ename);
      end loop;
      exception
        when cursor_already_open then
        dbms_output.put_line('游标已经打开');
    end;
    /
  • dup_val_on_index:在唯一索引所对应的列上插入重复的值时,会隐含触发例外dup_val_on_index。
  • invalid_cursor:当试图在不合法的有表上执行操作时,会触发该例外。例如:试图从没有打开的游标提取数据,或是关闭没有打开的游标,则会触发该例外。
  • invalid_number当输入的数据有误时,会触发该例外。数字100写成了1oo就会触发该例外。
  • no_data_found当执行select into没有返回值时,就会触发该例外。
    --no_data_found
    declare
    v_sal emp.sal%type;
    begin
      select sal into v_sal from emp where ename= '&name';
      exception
        when no_data_found then
          dbms_output.put_line( '不存在该员工' );
    end;
  • too_many_rows执行select into语句时,如果返回超过了一行,则会触发该例外。
    --too_many_rows
    declare
      v_ename emp.ename%type;
    begin
      select ename into v_ename from emp;
    exception when too_many_rows then
      dbms_output.put_line('返回了多行');
    end;
    /
  • zero_divide当执行除法运算时,如果分母为0,则会触发该例外。
  • value_error在执行赋值操作时,如果变量的长度不足以容纳实际数据,则会触发该例外value_error。
  • login_denide:用户非法登录。
  • not_logged_on:用于未登录就执行dml操作。
  • storage_error:超出了内存空间或是内存被损坏。
  • timeout_on_resource:oracle在等待资源时,出现超时。

  4、非预定义例外

  非预定义例外:用于处理与与定义例外无关的oracle错误。预定义例外只可以处理21个oracle错误,而当使用pl/sql开发应用程序时,可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时,违反了约束规定等。在这样的情况下,也可以处理oracle的各种例外。

  5、自定义例外

  自定义例外与oracle错误没有任何关联,是由开发人员为特定情况所定义的例外。编写一个pl/sql块,接收一个雇员的编号,并给该雇员工资增加1000元,如果该雇员不存在,请提示。

--自定义例外
create or replace procedure ex_test(spNo number) is
--定义一个例外
  myex exception;
begin
  update emp set sal=sal+100 where empno=spNo;
  if sql%notfound then
    raise myex;--触发例外myex
  end if;
exception
  when myex then
  dbms_output.put_line('没有更新任何例外');
end;
/
--说明:sql%notfound返回的数据类型是一个布尔值。布尔值与前一条sql语句相关。当最近的一条sql语句没有操作任何行的时候,返回true。否则返回false。

五、Oracle视图View

  1、概念

  视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。视图并不在数据库中以存储的数据值集形式存在。航和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。

  2、视图与表的区别

  • 表需要占用磁盘空间,视图不占用。
  • 视图不能添加索引。
  • 使用视图可以简化复杂查询:比如学生选课系统。
  • 视图有利于提高安全性:比如不同用户查看不同视图。

  3、创建视图

  create or replace view 视图名 as select语句 [with read only]
  • --创建视图,把emp表的sal<1000的雇员映射到该视图
    create view myview as select * from emp where sal<1000;

  4、删除视图

  drop view 视图名;

  5、简单地使用视图

  比如说有表图书book(id,name,prise....)读者reader(id.....)借阅关系 borrow( bookid,readerid,date)。

  如果要查询读者借阅情况,我们需要多表查询比较麻烦,但是我们可以建立个视图,view1:

  • select * from book,reader,borrow where book.id=bookid and reader.id=readerid
    这样只要查询select * from view1 就可以看到谁借了什么书了,包括所有的详细内容。

 

【推广】 免费学中医,健康全家人
原文地址:https://www.cnblogs.com/fzz9/p/8393879.html