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) is
begin
    --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 number
call 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
) is
begin
    insert into book values(spBookId , spBookName , spPublishHouse);
end;





--编写一个过程；
--可以输入雇员编号，
--返回该雇员的姓名；
--有输入和输出的存储过程；
--out代表的是，从存储过程中输出的变量参数；
--如果不写out，则默认就是一个in；
create or replace  procedure mypro(
    spNo in number,
    spName out varchar2
) is
begin
    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
) is
begin
    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
) is
begin
    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) t1

select 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_cursor

declare
cursor 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/S123

create 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;