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")+"\t"); System.out.print(rs.getString("job")+"\t"); 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")+"\t"); System.out.print(rs.getDouble("sal")+"\t"); System.out.println(rs.getString("job")+"\t"); } 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 //一般用于递归查询
