Oracle学习笔记(续)

接上文 Oracle学习笔记

PL/SQL 数据分页

Java调用无返回值的存储过程

create table book(      bookId number,      bookName varchar2(50),      publishHouse varchar2(50));

create or replace procedure pro_page(BookId in number,BookName in varchar2,PublishHouse in varchar2)isbegin insert into book values(BookId,BookName,PublishHouse);end;

Java中调用存储过程(无返回值)

CallabelStatement cs = connection.prepareCall("{call pro(?,?,?)}");cs.setInt(1,10);cs.setString(2,"笑傲江湖");cs.setString(3,"人民出版社");cs.execute();

/******************************************************************************/

Java调用有返回值的存储过程

create or replace procedure pro1(no in number,name out varchar2)isbegin select ename into name from SCOTT.Emp where empno =no;end;

Java中调用存储过程(有返回值)

CallabelStatement cs = connection.prepareCall("{call pro1(?,?)}");cs.setInt(1,7788);cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);cs.execute();String name = cs.getString(2);//取出返回值

/******************************************************************************/

返回结果集的存储过程

Step1.创建一个包，在包中定义类型test_cursor

create or replace package testpackage astype test_cursor is refcursor;end testpackage;

Step2.创建过程

create or replace procedure pro(no in number,v_cursor out testpackage.test_cursor)begin open v_cursor forselect * from SCOTT.emp where deptno =no;end;

Step3.在Java中调用

CallabelStatement cs = connection.prepareCall("{call pro(?,?)}");cs.setInt(1,10);cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);cs.execute();ResultSetrs = (ResultSet)cs.getObject(2);while(rs.next()){ }

分页过程

select t1.*,rownum rn from(select*fromSCOTT.Student) t1;--按照编号排序select t1.*,rownum rn from(select*fromSCOTT.Student) t1 where rownum<10;select * from(select t1.*,rownum rn from(select*fromSCOTT.Student) t1 where rownum<=20)where rn >=11;--查询第11-20条数据~~~~模板select * from(select t1.*,rownum rn from(select*fromSCOTT.EMP orderby sal) t1 where rownum<=9)where rn >=5;--按照sal排序

create or replace package testpackage astype test_cursor is refcursor; --创建包，声明游标end testpackage;

create or replace procedure fenye(tablename invarchar2,Pagesize in number,Pagenow in number,myrows out number,--总记录数myPageCount out number,--总页数my_cursor out testpackage.test_cursor--返回的记录集)isv_sql varchar2(1000);v_begin number:=(Pagenow-1)*Pagesize+1;v_end number:=Pagenow*Pagesize;begin  v_sql :='select* from (select t1.*,rownum rn from (select * from '||tablename||'order by sal) t1 where rownum <= '||v_end||')where rn >= '||v_begin;  open my_cursor for v_sql;  v_sql :='selectcount(*) from '|| tablename;  execute immediate v_sql into myrows;  if mod(myrows,Pagesize)=0 then          myPageCount =myrows/Pagesize;  else          myPageCount =myrows/Pagesize+1;  endif;end;

Java调用

CallabelStatement cs = connection.prepareCall("{call fenye(?,?,?,?,?,?)}");//调用存储过程cs.setString(1,"SCOTT.EMP");//设置表名cs.setInt(2,5);//设置Pagesizecs.setInt(3,1);//设置Pagenow cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);//注册总记录数cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);// 注册总页数cs.registerOutParameter(6,oracle.jdbc.OracleTypes.INTEGER);// 注册返回结果集 cs.execute(); introwNum = cs.getInt(4);//intpageCount = cs.getInt(5);ResultSetrs = (ResultSet)cs.getObject(6);//结果集

异常处理

预定义异常no_data_found

declarev_name SCOTT.EMP.ENAME%type;begin select ename intov_name from SCOTT.EMP where empno = &no; dbms_output.put_line('名字：'||v_name); exception   when no_data_found then     dbms_output.put_line('编号没有');end;

预定义异常case_not_found

create or replace procedure pro(no number)is      v_sal SCOTT.EMP.SAL%type;begin select sal intov_sal from SCOTT.EMP where empno =no; case   when v_sal <1000then     update SCOTT.EMP set sal = sal +100 where empno =no;   when v_sal <2000then     update SCOTT.EMP set sal = sal +200 where empno =no;     endcase;   exception     when case_not_found then        dbms_output.put_line('case语句没有与'||v_sal||'相匹配的条件');end;

预定义异常cursor_already_open

declarecursor emp_cursor is select ename,sal from SCOTT.EMP;begin open emp_cursor; for emp_record in emp_cursor   loop   dbms_output.put_line(emp_record.ename);   end loop;   exception     when cursor_already_open then        dbms_output.put_line('游标已经被打开');end;

预定义异常dup_val_on_index

begin insert into SCOTT.DEPT values(10,'公安部','北京'); exception   when dup_val_on_index then     dbms_output.put_line('在deptno列上不能出现重复值');end;

预定义异常invalid_cursor

declarecursor emp_cursor is select ename,sal from SCOTT.EMP;emp_record emp_cursor%rowtype;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;

预定义异常invalid_number

begin update SCOTT.EMP set sal = sal +'lll'; exception   when invalid_number then     dbms_output.put_line('无效数字');end;

预定义异常too_many_rows

declare      v_name SCOTT.EMP.ENAME%type;begin  select ename into v_name from SCOTT.EMP;  exception    when too_many_rows then      dbms_output.put_line('返回了多行');end;

预定义异常zero_divide

被除数为0时触发。

预定义异常value_error

declare     v_name varchar2(2);begin     select ename intov_name from SCOTT.EMP where empno = &no;     dbms_output.put_line(v_name);exception     when value_error then           dbms_output.put_line('变量尺寸不足');end;

预定义异常login_denied

用户非法登录时触发。

预定义异常not_logged_on

如果用户没有登录就执行dml就会触发。

预定义异常storage_error

如果超出了内存空间或是内存被破坏就触发。

预定义异常timeout_on_resorce

如果Oracle在等待资源时，出现了超时就触发。

自定义异常

create or replace procedure pro_exception_test(no number)ismy_exception exception;--自定义异常begin update SCOTT.EMP set sal = sal +100 where empno =no; if sql%not found then   raise my_exception; endif; exception when my_exception then   dbms_output.put_line('没有做任何更新'); end;

视图与表的区别

① 表需要占用磁盘空间，视图不需要

② 视图不能添加索引

③ 使用视图可以简化复杂查询

④ 视图有利于提高安全性

创建视图

create view my_view as select * fromSCOTT.EMP where sal <1000;

可以在最后带上 with read only

删除视图

drop view my_view