PLsql学习笔记（四）

分页技术：
有输入和输出的存储过程：
in 代表输入，out代表输出。
实例：
set serveroutput on;
create or replace procedure yzh_mytest
(y_no in number,y_name out varchar2) is
begin
 select username into y_name from users where userno=y_no;
end;
Java调用：
.....
//创建CallableStatement
CallableStatement cs=con.prepareCall("{call yzh_mytest(?,?)}");
//给？赋值
cs.setInt(1,22);
//变量关联，VARCHAR由返回数据的类型决定，可改
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
//取回返回值
String name=cs.getString(2);
System.out.println("22的用户名"+name);
.....
有返回值的存储过程（列表【结果集】）
第一步：创建一个包,定义游标类型
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
第二步：建立存储过程
create or replace procedure yzh_test
(y_no in number,y_cursor out testpackage.test_cursor) is
begin
open y_cursor for select * from users where userno=y_no;
end;
/
第三步：java中调用
.....
//创建CallableStatement
CallableStatement cs=con.prepareCall("{call yzh_test(?,?)}");
//给？赋值
cs.setInt(1,22);
//变量关联，VARCHAR由返回数据的类型决定，可改
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CORSOR);
cs.execute();
//得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next()){
  System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
.....
--------------------------------------------------------------------------------------
编写存储过程，要求输入表名，每页显示记录数、当前页。返回总记录数，总页数和返回的结果集。
分页语句：
select * from
(select t1.* ,rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6;
第一步，开发一个包：
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
第二步，编写分页过程：
create or replace procedure fenye
(tableName in varchar2,
 pageSize in number,
 pageNow in number,
 myRows out number,
 myPageCount out number,
 my_cursor out testpackage.test_cursor--返回的结果集
) is
--定义部分
--定义sql语句 字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pageSize+1;
v_end bumber:=pageNow*pageSize;
begin
--执行部分
v_sql:='select * from (select t1.* ,rownum rn from (select * from '||tableName
||') t1 where rownum<='||v_end||') where rn>='||v_begin||'';
--游标和sql关联
open my_cursor for v_sql;
--计算myrows和myPageCount
v_sql:='select count(*) from'||tableName;
--执行sql,并把返回值赋给myRows
execute immediate v_sql into myRows;
--计算myPageCount
if mod(myRows,pageSize)=0 then
   myPageCount:=myRows/pageSize;
else
   myPageCount:=myRows/pageSize+1;
end if;
--关闭游标
close my_cursor;
end;
第三步、Java中调用
package yzh;
import java.sql.*;
public class test{
  public static void main(String args[]){
    try{
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","system","yzh");
    CallableStatement cs=con.prepareCall("{call fenye(?,?,?,?,?,?)}");
    cs.setString(1,"users");
    cs.setInt(2,5);
    cs.setInt(3,1);
    cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
    cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
    cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
    cs.execute();
    //取出返回值
    int rowNum=cs.getInt(4);//这里的4由该参数的位置决定
    int pageCount=cs.getInt(5);
    ResultSet rs=(ResultSet)cs.getObject(6);
    while(rs.next()){
     System.out.println("编号"+rs.getInt(1)+"名字"+rs.getString(2));
    } 
 }catch(Exception e){
 
  e.printStackTrace();
 }
  }
}
《例外处理》
实例：
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&aa;
dbms_output.put_line('名字：'||v_ename);
exception
  when no_data_found then
  dbms_output.put_line('没有此编号！');
end;
/
什么是预定义例外？
它是由pl/sql所提供的系统例外。当应用程序违反了oracle规定的限制的时候，则会隐含的触发一个内部例外。
常见预定义例外：
case_not_found:when字句中没有包含必须的条件分支，就会触发它。
cursor_already_open:打开一个已经打开的游标就会触发它。
dup_val_on_index:在唯一索引所对应的列上插入重复的值。
invalid_cursor:视图在不合法的游标上执行操作时，会触发该例外。
invalid_number:输入数据有误时，触发它。
no_data_found:
too_many_rows:当执行select into语句时，如果返回超过了一行，则会触发它。
zero_divide:当执行2/0语句时，触发它。
value_error：执行赋值时，如果变量的长度不足以容纳实际数据，则触发它。
login_denide:用户非法登录
not_logged_on:用户没有登录就执行dml操作
storage_error:超出内存空间或是内存被损坏。
timeout_on_resource:超时
自定义例外：
编写块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。
set serveroutput on;

create or replace procedure ex_test(y_no number)
is
myex exception;
begin
update emp set sal=sal+1000 where userno=y_no;
--sql%notfound表示没有update,raise myexception触发异常
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line('没有可更新的用户');
end;
/

exec ex_test(554);
《视图》
视图是一个虚拟表，其内容由查询定义。视图并不在数据库中以存储的数据值形式存在。它在引用视图时动态生成。
视图与表的区别：
1.表需要占用磁盘空间，而视图不需要。
2.视图不能添加索引
3.使用视图可以简化复杂查询
4.视图有利于提高安全性
创建视图
create view myview as select * from emp where sal<2000;
select * from myview;
创建或修改视图
create or replace view 视图名 as select语句 [with read only]
删除视图
drop view 视图名;