Java基础——Oracle（八）

一、流程控制语句

1) 循环语句

== loop ..  end loop

简单的循环,至少被执行一次

create table userinfo (id number, name  varchar2(30)) ; //创建一个表
create or replace procedure sp_04 (p_name varchar2) is  //创建一个存储过程,循环往表里添10条数据
v_num number :=1;
begin
loop
insert into userinfo values (v_num,p_name);
exit when v_num=10;
v_num :=v_num+1;
end loop;
end;
                             
exec  sp_04('xxxx') ;

== while ... loop   end loop     

create or replace procedure sp_05 (p_name varchar2) is
v_num number :=11;
                  
begin
while v_num <=20 loop
insert into userinfo values (v_num,p_name);
v_num :=v_num+1;
end loop;
end;

== for

create or replace procedure sp_06 (p_name varchar2) is
v_num number :=21;
begin
for i in 21..30 loop   //可以 写成 for i  reverse  in 21..30 loop ,  // reverse表示反转,按相反的顺序添加
insert into userinfo values (v_num,p_name);
v_num :=v_num+1;
end loop;    
end; 

2) 控制语句

== goto

用于跳转到指定的标号去执行,不建议使用

语法: goto 标号名

set serveroutput on;
declare
i number:=1;  
begin
loop
dbms_output.put_line('i的值是'||i);
if i=10 then
goto end_loopAAA;
end if;
i:=i+1;
                          
end loop;
<<end_loopAAA>>   --//这是在声明一个标号
dbms_output.put_line('程序执行结束');
end;

== null 语句 

null语句不执行任何操作,主要是为了让程序好读

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<8000 then
update emp set sal=99999 where ename=v_ename;
else
null;  
end if;
end;

二、存储过程练习

//练习一 向 book 表添一本书,写一个存储过程,向表中添加一条数据,在java程序中调用

create table book (
bookid number,
bookname varchar2(50),
pubhouse varchar2(50)
)
                
create or replace procedure sp_addbook(sp_bookid in number,sp_bookname in varchar2,pubhouse in varchar2) is    --//in 表示是输入参数
begin
insert into book (bookid,bookname,pubhouse)  values(sp_bookid,sp_bookname,pubhouse);
end;

在java中调用

public static void test3(){
Connection conn=null;
CallableStatement stm=null;
try{
     conn=DBUtil.getConn();
     stm=conn.prepareCall("{call sp_addbook(?,?,?)}");  
                    
     stm.setInt(1, 50);
     stm.setString(2, "红岩");
     stm.setString(3, "清华大学出版社");
                    
      stm.execute();    
      }
      catch(Exception ex){
      ex.printStackTrace();
      }
      finally{
      DBUtil.close(null,stm,conn);
      }
}

//练习二 输入一个书的编号,返回书名 (有输入和输出的存储过程)

create or replace procedure sp_getbookname (sp_bookid in number ,sp_bookname out varchar2) is    --//out 表示这个参数是输出参数
begin
select bookname into sp_bookname from book where bookid= sp_bookid;
end; 

在java中调用

//根据书的id查询书名( 即有输入,也有输出的存储过程)
public static void test4(){
Connection conn=null;
CallableStatement stm=null;
     try{
         conn=DBUtil.getConn();
         stm=conn.prepareCall("{call sp_getbookname(?,?)}");  
                        
          stm.setInt(1, 50); //传一个输入参数 (书的id)
          stm.registerOutParameter(2, OracleTypes.VARCHAR); //指定输入参数所对应的类型
                        
         stm.execute();    
                        
         String bookName=stm.getString(2);  //取存储过程输出参数返回的值 2,代表是第2个问号
         System.out.println(bookName);    
         }
         ...
}

//练习三 一个存储过程同时有多个输出参数  

create or replace procedure sp_getempinfo (sp_empno number  , sp_totalsal out number, sp_job out varchar2, sp_name out varchar2) is
begin
select sal*12+nvl(comm,0)*12, job,  ename into sp_totalsal,sp_job,sp_name  from  emp where empno =sp_empno;
end;
            
public static void test5(){
          Connection conn=null;
          CallableStatement stm=null;
          try{
          conn=DBUtil.getConn();
          stm=conn.prepareCall("{call sp_getempinfo(?,?,?,?)}");  
                    
           stm.setInt(1, 7788); //传一个输入参数 ,员工id
           stm.registerOutParameter(2, OracleTypes.NUMBER); //sp_totalsal
           stm.registerOutParameter(3, OracleTypes.VARCHAR); //sp_job
           stm.registerOutParameter(4, OracleTypes.VARCHAR); //sp_name
                    
           stm.execute();    
                    
           System.out.println("姓名是"+stm.getString(4));
           System.out.println("岗位是"+stm.getString(3));
           System.out.println("年薪是"+stm.getString(2));
                 
                    
           }catch(Exception ex){
           ex.printStackTrace();
           }finally{
           DBUtil.close(null,stm,conn);
                }
            }

 //例四 返回列表(结果集)

编写一个过程,输入部门号,查询出该部门所有员的工信息

Oracle 中的存储过程没有返回值 ,所以它返回内容的时候,都是用 out 参数,对于返回列表也不例外,但由于返回的是列表,不能用一般的参数,所以要用 package

1) 建一个包

create or replace package testpackage as
TYPE  test_cursor is ref cursor ;  --//声明了一个游标类型
end testpackage;

2) 建一个存储过程

create or replace procedure sp_testquery (sp_deptno in  number, p_cursor out testpackage.test_cursor ) is
begin
open p_cursor for  select * from emp where deptno=sp_deptno;   --// 把游标和查询关联起来
end;

3) java程序

public static void getEmpList(){
     Connection conn=null;
     CallableStatement stm=null;
        try{
            conn=DBUtil.getConn();
            stm=conn.prepareCall("{call sp_testquery(?,?)}"); //deptno, p_cursor
            stm.setInt(1, 20);  //给部门编号传值
            stm.registerOutParameter(2, OracleTypes.CURSOR);  //给第二个参数(存储过程的输出参数) 指定类型
                        
            stm.execute();
                        
            ResultSet rs= (ResultSet)stm.getObject(2);
            while(rs.next()){
                    System.out.print(rs.getString("ename")+"	");
                    System.out.print(rs.getString("job")+"	");
                    System.out.println(rs.getString("sal"));
                    }
             }catch(Exception ex){
               ex.printStackTrace();
             }finally{
              DBUtil.close(null,stm,conn);
             }
     }
}

三、分页存储过程

1) 分页语句 : 

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

2) 建一个包

create or replace package pagePackage as
type p_cursor is ref cursor ;    --//声明一个游标类型
end pagePackage;

3) 存储过程

create or replace procedure sp_fenye
(
   tableName in varchar2 , --表名
   pageSize in number,    --每页多少条记录
   pageIndex in number,    --当前是第几页
               
   sp_cursor out pagePackage.p_cursor,  --用于返回记录集的游标
   myrowCount out number, -- 返回记录总数
   mypageCount out number -- 返回总页数
               
   )  is
        
         v_sql varchar2 (2000);
         v_begin number :=(pageIndex-1)* pageSize +1;
         v_end number :=pageIndex*pageSize;
        
         begin
           v_sql:=' select * from (  select t1.*,rownum rn from 
          ( select * from '||tableName||') t1 where rownum <='||v_end||' ) where rn >= '||v_begin ;
           open sp_cursor for v_sql;
           
           v_sql:='select count(*) from '||tableName;
           execute immediate v_sql into myrowCount; --//查询记录总数,立即查询
           
              /*计算mypageCount
                 if mod(myrowCount,pageSize) ==0
                 then
                   mypageCount:=myrowCount/pageSize;
                 else
                  mypageCount:=myrowCount/pageSize+1;
                 end if;
             */
            
             mypageCount :=ceil (myrowCount/pageSize); --//这句可以替代上面的逻辑
            
         end;

4) java程序

public static void getListByPage(int pageSize, int pageIndex,String tableName){
       Connection conn=null;
       CallableStatement stm=null;
       ResultSet rs=null;
          try{
          conn=DBUtil.getConn();
          stm=conn.prepareCall("{call sp_fenye(?,?,?,?,?,?)}");
                    
          stm.setString(1,tableName);  //tableName
          stm.setInt(2, pageSize); //pageSize 设为每页5条
          stm.setInt(3, pageIndex); //pageIndex ,设当前页是第二页
                    
          stm.registerOutParameter(4, OracleTypes.CURSOR);  //指定第四个参数的返回类型为游标类型
          stm.registerOutParameter(5, OracleTypes.NUMBER);  //接收总记录数
          stm.registerOutParameter(6, OracleTypes.NUMBER);  //接收总页数
                    
          stm.execute();
                    
          rs=(ResultSet)stm.getObject(4) ; //打开返回的游标
          while(rs.next()){
               System.out.print(rs.getString("ename")+"	");
               System.out.print(rs.getDouble("sal")+"	");
               System.out.println(rs.getString("job")+"	");
                    }
                    
               System.out.println("总行数:"+stm.getInt(5));
               System.out.println("总页数:"+stm.getInt(6));
                        
               }catch(Exception ex){
                    ex.printStackTrace();
                }finally{
                    DBUtil.close(rs,stm,conn);
                }
}

四、异常处理

//例外

预定义例外,非预定义例外 和 自定义例外

预定义例外用于处理常见的Oracle 错误

非预定义例外用于处理预定义例外不能处理的例外

自定义例外,用于处理与Oracle错误无关的其他情况

//例子        
set serveroutput on;
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;

预定义例外是由 pl/sql提供的系统例外

1) case_not_found

在编写 case 语句的时候同, 如果when 子句没有包含必须的 case 分支

create or replace procedure sp_g(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
          dbms_output.put_line('少于1000');
          when v_sal<2000 then
          dbms_output.put_line('少于2000');
          end case;
          exception
          when case_not_found then
          dbms_output.put_line('case 语句没有得到匹配的条件');
         end;

2)  cursor_already_open

当重新打开已打开的游标时,会触发
             

declare
cursor emp_cursor is select ename,sal from emp;
begin
open emp_cursor;
for emp_record1 in emp_cursor loop  -- 这里又打开了一次游标
dbms_output.put_line(emp_record1.ename);
end loop;
                            
exception
when cursor_already_open then
dbms_output.put_line('游标已经打开');
end;

3)  dup_val_on_index

在唯一索引所对应的例上插入重复的值时触发

begin
insert into dept values(10,'公案安部','北京');
   exception
   when dup_val_on_index then
   dbms_output.put_line('插入重复列了');
end;

创建索引 create index 索引名 on 表名(列名)

4) invaild_cursor

试图在不合法的游标上操作时

例如: 试图从没有打开的游标提取数据,或是关闭没有打开的游标        

declare
cursor emp_cursor is select ename,sal from emp;
emp_record emp_cursor%rowtype;      -- //emp_record是这个游标变量的名字,
begin 

open emp_cursor;//打开游标

fetch emp_cursor into emp_record;
dbms_output.put_line(emp_record.ename);
close emp_cursor;
exception
when invalid_cursor then
dbms_output.put_line('请检测游标是否打开');
end;

五、批量生成测试数据

create  table TestKKK (idAAA number,dateAAA varchar2(50),randomAAA number , nameAAA varchar2(50));
          insert  into TestKKK (idAAA ,dateAAA ,randomAAA,nameAAA)
          select rownum ,
           to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss'),
           trunc(dbms_random.value(0, 100)),
           dbms_random.string('x', 20)
           from dual  connect by level <= 100000   //一般用于递归查询