Oracle Database ：玩转Oracle学习笔记之（9）：案例

declare    --define a variable;    v_ename varchar2(5);    v_sal   number(7,2);begin    --exec part;    select ename , sal into v_ename , v_sal from emp where empno=&aa;    --display messgae in console;    dbms_output.put_line('User Name is : '||v_ename|| 'And Sal is '||v_sal);    exception    --Exception handling;    when no_data_found then        dbms_output.put_line('Input error , reinput : ');end;create procedure sp_pro3(spName varchar2 , newSal number) isbegin    --execute part，根据用户名曲修改工资；    update emp set sal=newSal where ename=spName;end;--函数案例；--输入雇员的姓名，返回该雇员的年薪；create or replace function myfun1(spName varchar2)    return number is yearSal number(7,2);begin    --函数执行部分    select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;    return yearSal;end;var income numbercall myfun1('SCOTT') into:income;print income;--包的创建案例；--创建一个包myPackage；--声明该包有一个过程和函数；create or replace package mypackage is--begin//在创建包的时候不能用begin，否则或出现编译错误；    procedure update_sal(name varchar2 , newSal number);    function annual_income(name varchar2) return number;end;--创建包体实例；--创建一个包体mypbody；create or replace package body mypackage is--begin在创建包的时候不能用begin，否则或出现编译错误；    procedure update_sal(name varchar2 , newSal number)    is    begin        update emp set sal=newSal where ename=name;    end;        function annual_income(name varchar2)    return number is        annual_sal number;    begin        select sal*12+nvl(comm,0)*12 into annual_sal from emp where ename=name;        return annual_sal;    end;end;--下面以输入员工号，显示员工姓名等信息；declare    c_tax_rate number(3,2):=0.03;    --用户名；    v_name emp.ename%type;    v_sal  emp.sal%type;    v_tax_sal number(6,2);begin    --执行    select ename ,sal into v_name , v_sal from emp where empno=&no;    --计算所得税‘    v_tax_sal:=v_sal*c_tax_rate;    --输出；    dbms_output.put_line('Name is : ' || v_name || ' sal is '||v_sal||' tax is  '||v_tax_sal);end;--pl/sql记录实例；declare    --定义复合（pl/sql记录）类型；emp_record_type    type emp_record_type is record(        name emp.ename%type,        salary emp.sal%type,        tittle emp.job%type    );    --复合类型定义结束；    --定义一个复合类型的变量；    sp_record emp_record_type;begin    select ename,sal,job into sp_record from emp where empno=&no;    dbms_output.put_line('Name : '||sp_record.name);end;

--pl/sql表实例；declare    --定义了一个pl/sql表类型：sp_table_type ，    --该类型是用于存放emp.ename%type类型的数组；    --index by binary_integer：代表下标是按照整数来排序的；    type sp_table_type is table of emp.ename%type index by binary_integer;    --定义了一个sp_table_type类型的变量：sp_table；    sp_table sp_table_type;begin    select ename into sp_table(0) from emp where empno=7788;    dbms_output.put_line('Name : '||sp_table(0));end;--说明：--sp_table_type：pl/sql表类型；--emp.ename%type 指定了表的元素类型和长度；--sp_table：为pl/sql表变量；--sp_table(0)表示下表为0的元素；

--请用pl/sql编写一个块：可以输入部门号，并显示该部门所有员工姓名和工资；declare    --定义游标类型：sp_emp_cursor；    type sp_emp_cursor is ref cursor;    --定义一个游标变量；    test_cursor sp_emp_cursor;    --定义变量：    v_ename emp.ename%type;    v_sal emp.sal%type;begin--执行    --把test_cursor和一个select结合；    --相当于将test_cursor指向有select查询返回的结果集；    open test_cursor for select ename , sal from emp where deptno=&no;    --这时候我们可以循环取出结果集中的结果了；    --开始循环体；    loop    --取值；        fetch test_cursor into v_ename ,v_sal;    --判断是否test_cursor为空了，如果不判断就会出现死循环的效果；        exit when test_cursor%notfound;        dbms_output.put_line('Name is '||v_ename ||' Sal is '||v_sal);    --结束循环体；    end loop;end;--编写一个过程，可以输入一个员工名，--如果该员工工资低于2000，就给该员工增加10%；create or replace procedure mypro(spName varchar2) is    --定义；    v_sal emp.sal%type;begin    --执行    select sal into v_sal from emp where ename=spName;    --判断；    if v_sal<2000 then        update emp set sal=sal*1.1 where ename=spName;    end if;end;--编写一个过程，可以输入一个雇员名，--如果该雇员名的补助不是0，就在原有的基础上增加100，--如果没有补助，就把补助设置为200；create or replace procedure mypro(spName varchar2) is    --define    v_comm emp.comm%type;begin    --执行；    select comm into v_comm from emp where ename=spName;    --判断；    if v_comm<>0 then        update emp set comm=comm+100 where ename=spName;    else        update emp set comm=comm+200 where ename=spName;    end if;end;/--编写一个过程，可以输入雇员号，如果该雇员的职位是PRESIDENT，--借给他增加1000工资，如果是manager，就增加500，--如果是其他就增加200；create or replace procedure mypro(spNo number) is    --define    v_job emp.job%type;begin    --执行；    select job into v_job from emp where empno=spNo;    --判断；    if v_job='PRESIDENT' then        update emp set sal=sal+1000 where empno=spNo;    elsif v_job='MANAGER' then        update emp set sal=sal+500  where empno=spNo;    else        update emp set sal=sal+200  where empno=spNo;    end if;end;--现在有一张表 用户id，用户名；--编写一个过程，可以输入用户名，--并添加10个用户到表中，用户编号从1开始；--创建用户表；create  table users(id number(3) , name varchar2(5));--创建过程；create or replace procedure mypro(spName varchar2) is    --定义部分；    --定义循环的次数；    v_num number :=1;begin    --执行部分；    --循环；    loop        insert into users values(v_num , spName);        --判断是否要推出循环；        --注意，判断是否相等用=，而赋值的时候用:=;    exit when v_num=10;        --自增；        --注意，判断是否相等用=，而赋值的时候用:=;        v_num:=v_num+1;    end loop;end;--编写一个过程，可以输入用户名，--并添加10个用户到表中，用户编号从11开始；--创建过程；create or replace procedure mypro(spName varchar2) is    --定义部分；    --定义循环的次数；    v_num number :=11;begin    --执行部分；    --循环；    while v_num<=20 loop        insert into users values(v_num , spName);        --自增；        --注意，判断是否相等用=，而赋值的时候用:=;        v_num:=v_num+1;    end loop;end;declare    i int:=1;begin    loop        dbms_output.put_line('OUt put : '||i);        if i=10 then            goto end_loop;        end if;        i:=i+1;    end loop;    <<end_loop>>    dbms_output.put_line('Loop END!');end;create or replace procedure mypro is    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<3000 then        update emp set comm=sal*0.1 where ename=v_ename;    else        null;    end if;end;

--现有异常表book：书号，书名，出版社；--编写一个过程，可以向表中添加书籍，--通过Java调用该过程 ；--建表book；create table book(    bookId number ,    bookName varchar2(50),    publishHous varchar2(50));--编写存储过程；--in代表的是，往存储过程输入的一个变量参数，--如果不写in，则默认就是一个in；--后面还会有out，表是输出参数；create or replace procedure mypro(    spBookId in number ,    spBookName in varchar2 ,    spPublishHouse varchar2) isbegin    insert into book values(spBookId , spBookName , spPublishHouse);end;--编写一个过程；--可以输入雇员编号，--返回该雇员的姓名；--有输入和输出的存储过程；--out代表的是，从存储过程中输出的变量参数；--如果不写out，则默认就是一个in；create or replace  procedure mypro(    spNo in number,    spName out varchar2) isbegin    select ename into spName from emp where empno=spno;end;

 //创建CallableStatment;CallableStatment cs = connection.prepareCall("{ call mypro(?,?,?,?)}");//给第一个问号赋值；给输入参数；cs.setInt(1,7788);//这里要注意：在关联注册输出参数的时候，每个输出参数都要关联，否则会出错；//给第二个问号赋值：输出参数；cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);//给第三个问号赋值：输出参数；cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);//给第四个问号赋值：输出参数；cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);//执行；cs.execute();//取出返回值；要注意问号的顺序；String name = cs.getString(2);String job = cs.getString(4);System.out.println("7788 的名字是： “ + name +" 工作岗位是 ： " + job);

--编写一个过程；--可以输入雇员编号，--扩展，返回多个值：工资，岗位，姓名；--有输入和输出的存储过程；--out代表的是，从存储过程中输出的变量参数；--如果不写out，则默认就是一个in；create or replace  procedure mypro(    spNo in number,    spName out varchar2 ,    spSal out number ,    spJob out varchar2) isbegin    select ename ,sal , job into spName ,spSal , spJob from emp where empno=spno;end;

--案例：编写一个过程，输入部门号，
--返回该部门所有雇员的信息；

--分析：由于Oracle存储过程没有返回值，
--所以他的返回值都是通过out参数来替换的，
--列表同样也不例外，但是由于是集合，
--所以不能用一般的参数，
--必须要用package了；
--所以要分为两部分了；

①、建立一个包：如下：

create or replace package testpackage as    TYPE test_cursor is ref cursor;end testpackage;

②、建立存储过程：

create or replace procedure mypro(    spNo in number ,    p_cursor out testpackage.test_cursor) isbegin    open p_cursor for select * from emp where deptno=spNo;end mypro;

③、如何在Java中调用

//创建CallableStatment;CallableStatment cs = connection.prepareCall("{ call mypro(?,?)}");//给第一个问号赋值；给输入参数；cs.setInt(1,10);//这里要注意：在关联注册输出参数的时候，每个输出参数都要关联，否则会出错；//给第二个问号赋值：输出参数；cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);//执行；cs.execute();//取出返回值；ResultSet rs = (ReslutSet)cs.getObject(2);while(rs.next()){    System.out.println(rs.getInt(1)+ "  "+rs.getString(2));}

--编写一个存储过程，
--输入表名称，每页显示的记录数，当前页；
--返回总记录数，总页数，返回的结果集；

--oracle分页：select t1.* ,rownum rn from (select * from emp) t1select t1.* , rownum  rn from ( (select * from emp) t1 )where rownum<=10;--在分页的时候，可以吧下面的sql语句当初模板使用；select * from (select t1.* ,rownum rn from ((select * from emp) t1) where rownum<=10) where rn>=3;--开始编写分页的过程；--编写游标包；create or replace package mypackage as    TYPE my_cursor is ref cursor;end mypackage;--创建存储过程；create or replace procedure mypro(    tableName in varchar2 ,    --一页显示的记录数；    pageSize in number,    --当前的页；    pageNow in number,    --总记录数；    myRows out number ,    --总的页数；    myPageCount out number,    --结果集游标；    p_cursor out mypackage.my_cursor) is    --定义部分；    --定义sql语句，字符串；    v_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 ||') t1) where rownum<='|| v_end ||') where rn>='|| v_begin;    --打开游标；    open p_cursor for v_sql;    --计算myRows和myPageCount；    --组织了一个sql语句；    v_sql:='select count(*) from '||tablename;    --执行了一个sql语句，并不返回的值赋值给myRows；    execute immediate v_sql into myRows;    if mod(myRows , pageSize)=0 then        myPageCount:= myRows/pageSize;    else        myPageCount:=1+myRows/pageSize;    end if;    --关闭游标；    --close p_cursor;end mypro;

使用Java测试分页：

//创建CallableStatment;CallableStatment cs = connection.prepareCall("{ call mypro(?,?,?,?,?,?)}");//给第问号赋值；给输入参数；cs.setString(1,"EMP");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 totalNum = cs.getInt(4);//取出总的页数；int totalPage = cs.getInt(5);ResultSet rs = (ReslutSet)cs.getObject(6);while(rs.next()){    System.out.println(rs.getInt(1)+ "  "+rs.getString(2));}

--例外案例；declare    v_ename emp.ename%type;begin    select ename into v_ename from emp where empno=&no;    dbms_output.put_line('Name : '||v_ename);    exception    when no_data_found then        dbms_output.put_line('编号不存在');end;--case_not_found;create or replace procedure mypro(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        update emp set sal=sal+100 where empno=spNo;    when v_sal<2000 then        update emp set sal=sal+200 where empno=spNo;    end case;    exception    when case_not_found then        dbms_output.put_line('case语句中没有与'||v_sal||'想匹配的条件');end mypro;--invalid_cursordeclarecursor emp_cursor is select ename , sal from 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；--编写一个pl/sql块，接收一个雇员的编号，并给该雇员的工资增加1000员，如果雇员不存在，情提示；--自定义例外；create or replace procedure mypro(spNo number) is    --定义一个例外；    myex exception;    begin    --更新用户sal；    update emp set sal=sal+1000 where empno=spNo;    --sql%notfound表示sql没有更新成功；    if sql%notfound then        --raise myex: 触发myex;        raise myex;    end if;    exception    when myex then        dbms_output.put_line('没有更新任何数据');end;--用system给scott用户授权；grant create view to scott;--创建视图：把emp表的sal<1000的雇员映射到一张视图中；conn scott/S123create view myview as select * from emp where sal < 1000;--为了简化操作，用一个视图解决显示雇员编号，姓名，部门名称；create or replace view myview as select empno , ename,dname from emp , dept where emp.deptno=dept.deptno;