oracle笔记——第七天：oracle视图、触发器，编写分页存储过程

1.控制结构(条件语句，循环语句，顺序语句)

1.1、条件分支

if-then,if-then-else,if-then-elseif-else
--编写过程，可以输入一个雇员，如果该雇员的工资低于2000，就给该雇员的工资增加10%；
create or replace procedure sp_pro6(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，如果补助为0，就在原来的基础之上加200

create or replace procedure sp_pro6(spName varchar2) is
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 sp_pro6(spNo number) is
--定义
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;
elseif 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;

1.2、循环语句

--loop 一loop开始  一end loop 结尾 至少执行一次
--编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号是从0开始

create or replace procedure sp_pro6(spName varchar2) is
v_num number:=1;--赋值
begin
loop
      insert into user values(v_num,spName);
      --判断退出条件
      exit when v_num=10;
      --自增
      v_num:=v_num+1;
end loop;
end;
--while循环（以while开始 while loop 结尾）
create or replace procedure sp_pro6(spName varchar2) is
v_num number:=1;--赋值
begin
while v_num<=20 loop
--执行
       insert into user values(v_num,spName);
       v_num:=v_num+1;
end if;
end;
--for循环
create or replace procedure sp_pro6 is
begin
       for i in reverse 1..10 loop
            insert into users values(i,'顺平');
       end loop;  
end;

 

1.3、顺序语句(goto语句)(null,使用null提高pl/sql语句的可读性)

---

 

declare
i int:=1;
begin
loop
dbms_output.put_line('输出i='||i);
if i=100 then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>--标号，指定跳出的位置
dbms_output.put_line('循环结束');
end;

---------------pl/sql 进阶，分页---------------------------------

--book 表
create table boook
(bookId number,bookName varchar2(50),publishHouse varchar2(50))
--编写过程
--in：输入参数
--out：输出参数
create or replace procedure sp_pro7
(spBookId in number,spbookName in varchar2,sppublishHousein varchar2) is
begin
   insert into book values(spBookId,spbookName,sppublishHousein);
end;

--在java中调用

import java.sql.*;
public static void main(String[] args){
       try{
              Class.forName("oracle.jdbc,driver.OralceDriver");
              Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
              //创建classableStatment
              ClassableStatement cs=ct.prepareCall("{call sp_pro7(?,?,?)}");//调用存储过程，有几个参数写几个？号
              //给？赋值
              cs.setInt(1,10);
              cs.setString(2,"笑傲江湖");
              cs.setString(3,"人民出版社");
              //执行
              cs.ececute();
       }catch(Exception e){
                        
       }finally{
          //关闭各种资源
       }
}

--有输入和输出的存储过程
create or replace procedure sp_pro8(spno in number,spName out varchar2) is
begin
       select ename into spName from emp where empno=spno;
end;
--java 调用有返回值的
ClassableStatement cs=ct.prepareCall("{call sp_pro8(?,?)}");//调用存储过程，有几个参数写几个？号
//给第一个赋值
cs.setInt(1,7788);
//给第er个赋值
cs.registerOutParameter(2,oracle.jdbc.OralceType.VARCHAR);
//执行
cs.execute();
//去除返回值
String name=cs.getString(2);
System.out.println("7788的名是："+name);
--案例扩展
--返回工资，岗位，部门号。。。
--返回结果集
--案例：(返回结果集)编写一个过程，输入部门号，返回该部门的所有雇员的信息，
--1.建立一个包
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
--2.创建过程
create or replace procedure sp_pro9
(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select *from emp where deptno=spNo;
end;
--在java中调用
ClassableStatement cs=ct.prepareCall("{call sp_pro9(?,?)}");//调用存储过程，有几个参数写几个？号
//赋值
cs.setInt(1,10);
cs.registerOutParameter(2,oralce.jdbc.OracleType.CURSOR);
//执行
cs.execute();
//得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next){
   system.out.println(rs.getInt(1)+"  "+rs.getString(2));
}   
------------------pl/sql进阶  分页存储函数-----------------------
--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>=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,--总页数
 p_cursor out testpackage.test_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 ||' order by sal)t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
--打开游标sql关联
open p_cursor for v_sql;
--计算myrows、myPageCount
--组织一个sql语句
v_sql:='select count(*) from '||tableName;
--执行一个sql语句，并把返回值，给myrows
execute immediate v_sql into myrows;
--计算mymyPageCount
if mod(myrows,PageSize)=0 then
myPageCount:=myrows/PageSize;
else
myPageCount:=myrows/PageSize+1;
end if;
--关闭游标
close p_cursor;
end;
----------使用java程序验证
import java.sql.*;
public static void main(String[] args){
       try{
              Class.forName("oracle.jdbc,driver.OralceDriver");
              Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
              //创建classableStatment
              ClassableStatement cs=ct.prepareCall("{call fenye(?,?,?,?,?,?)}");//调用存储过程，有几个参数写几个？号
              //给？赋值
              cs.setString(1,"emp");
              cs.setInt(2,5);
              cs.setInt(3,1);
              //注册总记录数
              cs.registerOutParameter(4,oralce.jdbc.OracleType.INTEGER);
              //注册总页数
              cs.registerOutParameter(5,oralce.jdbc.OracleType.INTEGER);
              //注册返回的结果集
              cs.registerOutParameter(6,oralce.jdbc.OracleType.CURSOR);
              //执行
              cs.execute();
              //取出总记录数    
              //注意:getInt(4),是由该参数的位置决定的
              int rowNum=cs.getInt(4);
              int pageCount=cs.getInt(5);
              ResultSet rs=(ResultSet)cs.getObject(6);
              //显示看看对不对一下结果，
              system.out.println("rowNum="+rowNum);
              system.out.println("pageCount="+pageCount);
              //显示结果集
              while(rs.next()){
                   system.out.println("姓名："+rs.getString(2)+" 工资："+re.getString(5));
              }
       }catch(Exception e){
                        
       }finally{
          //关闭各种资源
       }
}
--新的需求  ，按照薪水从高到低排序，然后取出6-10；
v_sql:='select * from (select t1.*,rownum rn from (select *from '|| tableName ||' order by sal)t1 where rownum<='|| v_end ||') where rn>='|| v_begin;



----例外处理(1 预定义例外、2 非预定义例外，3 自定义例外)
预定义例外：处理常见的oracle错误
非预定义例外：处理预定义例外不能处理的例外
自定义例外：用于处理与oracle错误无关的其他情况

--案例一：输入雇员编号，显示员工信息；如果输入的编号不存在，怎么处理
--1
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line('名字:'||v_ename);
end;
--2
declare
v_ename emp.ename%type;
begin
        select ename into v_ename from emp where empno=&no;
        dbms_output.put_line('名字:'||v_ename);
exception
        when no_data_found then
        dbms_output.put_line('你输入的编号不存在');
end;

---预定义例外
1.1、case_not_fount:如果when子句没有包含必须的分支条件，就会触发case_not_fount;
1.2、cursor_already_open:重复打开游标;
1.3、dup_val_on_index:添加主键，重复数据出现
1.4、invalid_cursor:没有打开游标，就要去取数据;关闭一个没有打开的游标
1.5、invalid_number:输入的数据有问题（输入了字母）
1.6、not_data_fount:未找到数据;
1.7、too_many_rows:返回的行数超过一条;
1.8、zero_divide:分母为0;
1.9、value_error:如果变量的长度不足以容纳实际的数据;
---其它预定义例外
2.1、login_denide:非法用户登录
2.2、not_logged_on:如果用户没有登录，就执行dml语句
2.3、storage_error:如果超出内存空间或者内存被破坏
2.4、timeout_on_resource:oracle等待资源，出现了超时现象;
---处理自定义例外(开发人员自己定义)
--案例：编写一个pl/sql块，接收一个雇员的编号，并给该雇员的工资加上1000，如果该雇员不存在，请提示;

create or replace procedure ex_test(spNo number)
is
--定义一个例外
myex exception;
begin
       update emp set sal=sal+1000 where empno=spNo;
       --例外
       --sql%notfound  表示没有更新成功
       if sql%notfound then
       --raise myex触发myex
       raise myex;
       end if;
       exception
       when myex then
       dbms_output.put_line('数据没有更新成功');
end;

----------------oracle视图(视图是一个虚表)
1.表需要占用磁盘空间，试图不需要
2.视图不能添加索引
3.视图可以简化复杂查询
4.视图的使用提高安全性
create view 视图名 as select 语句。。。。