Oracle(四)

53.带参数的光标：

set serveroutput on

declare

  cursor cemp(dno number) is select ename from emp where deptno=dno;

  pename emp.ename%type;

begin

  open cemp(20);

  loop

    fetch cemp into pename;

    exit when cemp%notfound;

    dbms_output.put_line(pename);

  end loop;

  close cemp;

end;

54.循环

set serveroutput on

declare

  pnum number := 1;

begin

  LOOP

    exit when pnum > 10;

    dbms_output.put_line(pnum);

    pnum := pnum + 1;

  END LOOP ;

end;

55.自定义异常：

 

set serveroutput on

declare

  cursor cemp is select ename from emp where deptno=70;

  pename emp.ename%type;

  no_emp_found exception;

begin

  open cemp;

  fetch cemp into pename;

  if cemp%notfound then

    raise no_emp_found;

  end if;

  close cemp;

exception

  when no_emp_found then dbms_output.put_line(' 发生异常');

end;

56.引用型变量

set serveroutput on

 declare

  pename emp.ename%type;

  psal   emp.sal%type;

begin

  select ename,sal into pename,psal from emp where empno=7839;

  dbms_output.put_line(pename||'的薪水是'||psal);

end;

57.带out参数的存储过程

create or replace procedure queryEmpInfo(eno in number,

                                         pename out varchar2,

                                         psal   out number,

                                         pjob   out varchar2)

as

 

begin

  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;

 

end;

58.在java中调用存储过程

   public class TestOracle {

       //call Procedure

       // {call <procedure-name>[(<arg1>,<arg2>, ...)]}

       @Test

       public void testProcedure(){

              String sql = "{call queryEmpInfo(?,?,?,?)}";

              Connection conn = null;

              CallableStatement call = null;

              try{

                     conn = JDBCUtils.getConnection();

                     call = conn.prepareCall(sql);

                    

                     //assign value to call

                     call.setInt(1, 7839);

                     //declare out parameter

                     call.registerOutParameter(2, OracleTypes.VARCHAR);

                     call.registerOutParameter(3, OracleTypes.NUMBER);

                     call.registerOutParameter(4, OracleTypes.VARCHAR);

                     //excute

                     call.execute();

                    

                     //get returned values

                     String name = call.getString(2);

                     double sal = call.getDouble(3);

                     String job = call.getString(4);

                    

                     System.out.println(name);

                     System.out.println(sal);

                     System.out.println(job);

              }catch(Exception ex){

                     ex.printStackTrace();

              }finally{

                     JDBCUtils.release(conn, call, null);

              }

       }

      

       //Call Function

       //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

       @Test

       public void testFunction(){

              String sql = "{?=call queryEmpIncome(?)}";

              Connection conn = null;

              CallableStatement call = null;

              try{

                     conn = JDBCUtils.getConnection();

                     call = conn.prepareCall(sql);

                     call.registerOutParameter(1, OracleTypes.NUMBER);

                     call.setInt(2, 7839);

                     call.execute();

                    

                     double income = call.getDouble(1);

                     System.out.println(income);

              }catch(Exception ex){

                     ex.printStackTrace();

              }finally{

                     JDBCUtils.release(conn, call, null);

              }

       }

 

       //call cursor

       @Test

       public void testCursor(){

              String sql = "{call MYPACKAGE.queryDeptEmpList(?,?)}";

             

              Connection conn =null;

              CallableStatement call = null;

              ResultSet rs  = null;

              try{

                     conn = JDBCUtils.getConnection();

                     call = conn.prepareCall(sql);

                     call.setInt(1, 20);

                     call.registerOutParameter(2, OracleTypes.CURSOR);

                     call.execute();

                    

                     rs = ((OracleCallableStatement)call).getCursor(2);

                     while(rs.next()){

                            String ename = rs.getString("ename");

                            double sal = rs.getDouble("sal");

                            String job = rs.getString("empjob");

                           

                            System.out.println(ename);

                            System.out.println(sal);

                            System.out.println(job);

                            System.out.println("-------------------");

                     }

              }catch(Exception ex){

                     ex.printStackTrace();

              }finally{

                     JDBCUtils.release(conn, call, rs);

              }

       }

}

59.触发器

create or replace trigger checkSal

before update on emp

for each row

begin

 

  if :old.sal > :new.sal then

    raise_application_error(-20002,'涨后工资不能少于涨前工资。涨后：'|| :new.sal||'  涨前:'||:old.sal);

  end if;

 

end;

 

60.在归档模式下做数据备份，可以进行联机备份，非归档模式不要调参数才行                  

61．闪回           

 闪回的类型;

1. 闪回表: 将表回退到过去的一个时间上

 2. 闪回删除(drop): Oracle的回收站

 3. 闪回版本查询:

 4. 闪回事务查询:通过查询得到undo_sql

5. 闪回数据库:将数据库回退到过去的一个时间上

 6. 闪回归档日志        

show parameter undo

alter system set undo_retention=900 scope=both;（scope: both momeroy spfile）

授予scott能闪回

grant flashback any table to scott;

记录当前时间

select to_char(sysdate,'yyyy-mm-dd hh24:mi:mm') 时间,timestamp_to_scn(sysdate) SCN from dual;

执行闪回表：alter table flashback_table enable row movement; 先开启行移动功能

flashback table flashback_table to SCN 900171;

            闪回表：把表回退到过去的一个时间上（SCN）

 1. undo参数

 2. 需要flashback any table的权限

3. timestamp和SCN

 4.执行闪回表（开启行移动）

 

执行闪回删除：

drop table test1;删除表到回收站

show recyclebin;查看回收站

drop table testdelete purge;彻底删除表

执行闪回删除

flashback table TESTSEQ to before drop;

   闪回同名表时要改名

flashback table TESTSEQ to before drop rename to aaa;

闪回版本查询:查询所有已经提交了历史记录

                                select  vid,vname,versions_operation,versions_starttime,versions_endtime

                  from versions_table

                  versions between timestamp minvalue and maxvalue

                  order by 1,4;

根据时间可以进行闪回

闪回事务查询：

                                grant select any transaction to scott;

查询语句

  select tid,tname,versions_operation,versions_starttime,versions_endtime,versions_xid

  from transaction_table

  versions between timestamp minvalue and maxvalue

  order by 1,4;

根据事务号查询un_do sql 再根据un_do 闪回

select operation,undo_sql from flashback_transaction_query  where xid='0300120082010000';

将查询到的undo语句执行一次

62. 创建一个名叫heima ，密码password的用户

            create user heima identified by password;

修改密码  alter user heima identified by ****;

给用户分配配额

alter user heima quota unlimited on users;

grant select on departments to scott;

grant select on emp to jeff with grant option;

//创建一个用户要加入这两个角色才具有一个普通用户的功能

grant connect,resource to myuser;

63.分步式数据库

   Net manager 中建立服务

   create database link l2 connect to scott identified by password using 'remote';

 

select ename,dname

   from dept, emp@L2

   where emp.deptno=dept.deptno;

建立同义词，在本地就可以像使用本地表一样

  create synonym remoteemp for emp@L2;