Oracle实战笔记（第七天）之PL/SQL进阶

一、控制结构

　　控制结构包括：判断语句（条件分支语句）、循环语句、顺序控制语句三种。

　　1、条件分支语句

if--then：简单条件判断

--编写一个过程，可以输入一个雇员名，如果该雇员名的工资低于2000，就给该雇员工资增加10%
create or replace procedure pro_addSal(v_ename varchar2) is
--定义变量
  v_sal emp.sal%type;
  begin
    select sal into v_sal from emp where ename=v_ename;
  --判断
  if v_sal<2000 then
    update emp set sal=sal+sal*0.1 where ename=v_ename;
  end if;
 end;
/

if--then--else：二重条件分支

--编写一个过程，可以输入一个雇员名，如果该雇员名的工资低于2000，就给该雇员工资增加10%，否则减少10%
create or replace procedure pro_addSal(v_ename varchar2) is
--定义变量
  v_sal emp.sal%type;
  begin
    select sal into v_sal from emp where ename=v_ename;
  --判断
  if v_sal<2000 then
    update emp set sal=sal+sal*0.1 where ename=v_ename;
  else
    update emp set sal=sal-sal*0.1 where ename=v_ename;
  end if;
 end;
/

if--then--elsif--else：多重条件分支

create or replace procedure pro_addSal(eNo number) is  
  v_job emp.job%type;  
begin  
    select job into v_job from emp where empno=eNo;
  if v_job='PRESIDENT' then
     update emp set sal=sal+1000 where empno=eNo;
  elsif v_job='MANAGER' then
    update emp set sal=sal+500 where empno=eNo;
  else
    update emp set sal=sal+200 where empno=eNo;
  end if;
end;
/

　　2、循环语句

loop循环：pl/sql中最简单的循环语句，以loop开头，以exit()作为结束判断语句，以end loop结尾。（至少循环一次）

--编写一个过程，输入用户名，并循环添加10个用户到users表中
create table users(userId number(5),userName varchar(20));--为了后面操作先创建一个users表
create or replace procedure pro_addUser(eName varchar2) is
--定义变量
  v_num number:=1;
begin
  loop
    insert into users values(v_num,eName);
    exit when v_num=10;--判断退出条件
    v_num:=v_num+1;--自增
  end loop;
end;
/

while循环：其实就是使用while语句来代替loop循环的exit语句。

--编写一个过程，删除users表中的编号1—10的个用户
--用户编号从1开始增加。
create or replace procedure pro_delUser is
--定义变量
  v_num number:=1;
begin
  while v_num<=10 loop
     delete from users where userId=v_num;
     v_num:=v_num+1;--自增
    end loop;
end;
/

for循环：自带变量和循环退出条件

create or replace procedure pro_addUser is
begin  
  for i in 1..10 loop  
    insert into users values(i,'lucy');
  end loop;  
end;
/

　　3、顺序控制语句

goto语句：用于跳转到特定标号去执行语句。注：由于使用gogo语句会增加程序的复杂性，并使得应用程序可读性变差，因此建议不要使用goto语句。
语法：goto lable，其中lable是已经定义好的标号名，如<<标记名>>，<<>>是标记符号，常用来跳出循环。
```
--循环输出i=1..10，最后跳出循环后打印“循环结束”
declare
  i int:=1;
begin
  loop
  dbms_output.put_line('i='||i);
  if i=10 then
    goto end_loop;
  end if;
    i:=i+1;
  end loop;
  <<end_loop>>
  dbms_output.put_line('循环结束');
end;
/
```

null语句：null语句不会执行任何操作，并且会直接将控制传递到下一条语句。（类似Java中的continue的用法）

declare
  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;
/

二、使用Java程序调用存储过程

　　1、无返回值的存储过程

　　创建一个表book，表结构如下：

create table book(bId number(4) primary key,bName varchar(30) not null,publisher varchar(30));

　　编写一个过程，向book表添加书籍信息，要求可以通过java程序调用该过程：

使用命令行创建：

create or replace procedure pro_addBook(bookId number,bookName varchar2,pub varchar2) is
begin
  insert into book values(bookId,bookName,pub);
end;
/

使用Java调用无返回值的过程：

 1 package test;
 2 import java.sql.CallableStatement;
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.SQLException;
 6 
 7 import org.junit.Test;
 8 
 9 /**
10  * 使用java调用Oracle创建的过程pro_addBook
11  */
12 public class callPro_addBook {
13     @Test
14     public void test(){
15         Connection conn = null;
16         CallableStatement cs = null;
17         try{
18             //连接数据库
19             Class.forName("oracle.jdbc.driver.OracleDriver");
20             conn = DriverManager.getConnection(
21                     "jdbc:oracle:thin:@192.168.183.1:1521:orcl","scott","tiger");
22             //获得执行对象
23             cs = conn.prepareCall("{call pro_addBook(?,?,?)}");
24             //传参
25             cs.setInt(1, 1001);
26             cs.setString(2, "五年模拟三年高考");
27             cs.setString(3, "教育出版社");
28             //执行
29             cs.execute();
30         }catch(Exception e){
31             e.printStackTrace();
32         }finally{
33             try {
34                 cs.close();
35                 conn.close();
36             } catch (SQLException e) {
37                 e.printStackTrace();
38             }
39         }
40     }
41 }

callPro_addBook

　　2、有返回值的存储过程（返回若干值）

　　编写一个过程，要求输入book表的书号就返回书籍信息：书名和出版社

使用命令行创建过程：

create or replace procedure pro_showBook
  (bookId in number,bookName out varchar2,pub out varchar2) is
begin
  select bName,publisher into bookName,pub from book where bId=bookId;
end;
/

使用Java调用返回值是若干数据的过程

 1 package test;
 2 import java.sql.CallableStatement;
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.SQLException;
 6 
 7 import org.junit.Test;
 8 
 9 /**
10  * 使用java调用Oracle创建的过程pro_addBook
11  */
12 public class callPro_showBook {
13     @Test
14     public void test(){
15         Connection conn = null;
16         CallableStatement cs = null;
17         try{
18             //连接数据库
19             Class.forName("oracle.jdbc.driver.OracleDriver");
20             conn = DriverManager.getConnection(
21                     "jdbc:oracle:thin:@192.168.183.1:1521:orcl","scott","tiger");
22             //获得执行对象
23             cs = conn.prepareCall("{call pro_showBook(?,?,?)}");
24             //传入参数
25             cs.setInt(1, 1001);
26             cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR );  
27             cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR );
28             //执行
29             cs.execute();
30             //获取out参数
31             String bookName = cs.getString(2);
32             String pub = cs.getString(3);
33             System.out.println("书名："+bookName);
34             System.out.println("出版社："+pub);            
35         }catch(Exception e){
36             e.printStackTrace();
37         }finally{
38             try {
39                 cs.close();
40                 conn.close();
41             } catch (SQLException e) {
42                 e.printStackTrace();
43             }
44         }
45     }
46 }

callPro_showBook

　　3、有返回值的存储过程（返回一个列表）

　　为了方便说明，我们再往book表中添加几条数据：

　　现在的需求是：创建一个过程，要求返回指定出版社如“知乎周刊”出版的书籍信息。

　如表所示，返回结果是三本书，而这种查询结果集我们一般放在一个list即列表中，而在oracle在接受返回值时需要使用包package，并用游标来进行参数输出：

--建立包，在该包中，定义一个游标类型test_cursor
create or replace package testpackage as
  type test_cursor is ref cursor;
  end;
/

使用命令行创建过程：

create or replace procedure pro_showPubBook
  (pub in varchar2,my_cursor out testpackage.test_cursor) is
begin
  open my_cursor for select * from book where publisher=pub;
end;
/

使用Java调用返回值是列表的过程：

 1 package test;
 2 import java.sql.CallableStatement;
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 
 8 import org.junit.Test;
 9 
10 /**
11  * 使用java调用Oracle创建的过程pro_addBook
12  */
13 public class callPro_showPubBook {
14     @Test
15     public void test(){
16         Connection conn = null;
17         CallableStatement cs = null;
18         try{
19             //连接数据库
20             Class.forName("oracle.jdbc.driver.OracleDriver");
21             conn = DriverManager.getConnection(
22                     "jdbc:oracle:thin:@192.168.183.1:1521:orcl","scott","tiger");
23             //获得执行对象
24             cs = conn.prepareCall("{call pro_showPubBook(?,?)}");
25             //传入参数
26             cs.setString(1, "知乎周刊");
27             cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR );//游标类型
28             //执行
29             cs.execute();
30             //获得结果集
31             ResultSet rs = (ResultSet) cs.getObject(2);
32             System.out.println("知乎周刊出版社书籍：");
33             if(rs!=null)
34                 while(rs.next()){
35                     System.out.println("书号："+rs.getInt(1)+"  "+"书名：《"+rs.getString(2)+"》");
36                 }
37             else
38                 System.out.println("暂无书籍");
39         }catch(Exception e){
40             e.printStackTrace();
41         }finally{
42             try {
43                 cs.close();
44                 conn.close();
45             } catch (SQLException e) {
46                 e.printStackTrace();
47             }
48         }
49     }
50 }

callPro_showPubBook

三、分页编程

　　案例：编写一个存储过程，要求可以输入表名、每页显示记录数、当前页，返回总记录数、总页数和返回的结果集。

　　1、使用rownum分页查询

select * from emp;
select t1.*,rownum rn from (select * from emp) t1;
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rownum>=6;

　　2、编写分页的存储过程

--编写分页的存储过程
create or replace procedure fenye
  (tableName in varchar2,--in表名
  myPageSize in number,--in记录数
  pageNow in number,--in当前页
  myRows out number,--out总记录数
  myPageCount out number,--out总页数
  p_cursor out testpackage.test_cursor--out结果集
  )is

  v_sql varchar2(500);--定义sql语句
  v_begin number:=(pageNow-1)*myPageSize+1;--定义起始页
  v_end number:=pageNow*myPageSize;--定义尾页
  
begin
  --执行分页查询语句
  v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||
　') t1 where rownum<='||v_end||') where rn>='||v_begin;
  --把游标和sql语句关联
  open p_cursor for v_sql;
  --计算myRows
  v_sql:='select count(*) from '||tableName;
  execute immediate v_sql into myRows;
  --计算myPageCount
  if mod(myRows,myPageSize)=0 then
    myPageCount:=myRows/myPageSize;
  else
    myPageCount:=myRows/myPageSize+1;
  end if;end;
/

　　3、使用Java调用分页过程

 1 import java.sql.CallableStatement;
 2 import java.sql.Connection;
 3 import java.sql.DriverManager;
 4 import java.sql.ResultSet;
 5 
 6 public class Test {
 7       public static void main(String[] args) {
 8              // TODO Auto-generated method stub
 9             Connection ct = null;
10             CallableStatement cs = null;
11              try {
12                   Class. forName("oracle.jdbc.driver.OracleDriver");
13                    ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" );
14 
15                    cs = ct.prepareCall( "{call fenye(?,?,?,?,?,?)}");
16 
17                    // 赋值
18                    cs.setString(1, "emp");
19                    cs.setInt(2, 5);
20                    cs.setInt(3, 1);
21 
22                    // 注册总记录数
23                    cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER );
24                    // 注册总页数
25                    cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER );
26                    // 注册返回的结果集
27                    cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR );
28 
29                    cs.execute();
30                    // 取出总记录数
31                    int rowNum = cs.getInt(4);
32                    // 取出总页数
33                    int pageCount = cs.getInt(5);
34                   ResultSet rs = (ResultSet) cs.getObject(6);
35 
36                    // 显示
37                   System. out.println( "rowNum=" + rowNum);
38                   System. out.println( "pageCount=" + pageCount);
39 
40                    while ( rs.next()) {
41                         System. out.println( "编号：" + rs .getInt(1) + ",姓名：" + rs .getString(2));
42                   }
43             } catch (Exception e) {
44                    // TODO Auto-generated catch block
45                    e.printStackTrace();
46             } finally {
47                    try {
48                          // 关闭资源
49                          cs.close();
50                          ct.close();
51                   } catch (Exception e1) {
52                          // TODO Auto-generated catch block
53                          e1.printStackTrace();
54                   }
55             }
56      }
57 }

四、例外处理

　　1、分类

预定义例外：用于处理常见的oracle错误。

非预定义例外：用于处理与预定义例外不能处理的例外。

自定义例外：用于处理与oracle错误无关的其他情况。

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

　　3、预定义例外

　　由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时，则会隐含触发一个内部例外。pl/sql为开发人员提供了20多个预定义例外。

case_not_found：when子句中没有包含必须的条件分支，就会触发case_not_found的例外。

--case_not_found
create or replace procedure sp_pro11(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;
/

cursor_already_open：当重新打开已经打开的游标时，会隐含触发例外cursor_already_open。

--cursor_already_open
declare
  cursor emp_cursor is select ename,sal from emp;
begin
  open emp_cursor;
  for emp_reacord1 in emp_cursor loop
    dbms_output.put_line(emp_record1.ename);
  end loop;
  exception
    when cursor_already_open then
    dbms_output.put_line('游标已经打开');
end;
/

dup_val_on_index：在唯一索引所对应的列上插入重复的值时，会隐含触发例外dup_val_on_index。
invalid_cursor：当试图在不合法的有表上执行操作时，会触发该例外。例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标，则会触发该例外。
invalid_number：当输入的数据有误时，会触发该例外。数字100写成了1oo就会触发该例外。

no_data_found：当执行select into没有返回值时，就会触发该例外。

--no_data_found
declare
v_sal emp.sal%type;
begin
  select sal into v_sal from emp where ename= '&name';
  exception
    when no_data_found then
      dbms_output.put_line( '不存在该员工' );
end;

too_many_rows：执行select into语句时，如果返回超过了一行，则会触发该例外。

--too_many_rows
declare
  v_ename emp.ename%type;
begin
  select ename into v_ename from emp;
exception when too_many_rows then
  dbms_output.put_line('返回了多行');
end;
/

zero_divide：当执行除法运算时，如果分母为0，则会触发该例外。
value_error：在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error。
login_denide：用户非法登录。
not_logged_on：用于未登录就执行dml操作。
storage_error：超出了内存空间或是内存被损坏。
timeout_on_resource：oracle在等待资源时，出现超时。

　　4、非预定义例外

　　非预定义例外：用于处理与与定义例外无关的oracle错误。预定义例外只可以处理21个oracle错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等。在这样的情况下，也可以处理oracle的各种例外。

　　5、自定义例外

　　自定义例外与oracle错误没有任何关联，是由开发人员为特定情况所定义的例外。编写一个pl/sql块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。

--自定义例外
create or replace procedure ex_test(spNo number) is
--定义一个例外
  myex exception;
begin
  update emp set sal=sal+100 where empno=spNo;
  if sql%notfound then
    raise myex;--触发例外myex
  end if;
exception
  when myex then
  dbms_output.put_line('没有更新任何例外');
end;
/
--说明：sql%notfound返回的数据类型是一个布尔值。布尔值与前一条sql语句相关。当最近的一条sql语句没有操作任何行的时候，返回true。否则返回false。

五、Oracle视图View

　　1、概念

　　视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图并不在数据库中以存储的数据值集形式存在。航和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

　　2、视图与表的区别

表需要占用磁盘空间，视图不占用。
视图不能添加索引。
使用视图可以简化复杂查询：比如学生选课系统。
视图有利于提高安全性：比如不同用户查看不同视图。

　　3、创建视图

　　create or replace view 视图名 as select语句 [with read only]

--创建视图，把emp表的sal<1000的雇员映射到该视图
create view myview as select * from emp where sal<1000;

　　4、删除视图

　　drop view 视图名;

　　5、简单地使用视图

　　比如说有表图书book（id,name,prise....）读者reader(id.....)借阅关系 borrow( bookid,readerid,date)。

　　如果要查询读者借阅情况，我们需要多表查询比较麻烦，但是我们可以建立个视图，view1:

```
select * from book,reader,borrow where book.id=bookid and reader.id=readerid
```
这样只要查询select * from view1 就可以看到谁借了什么书了，包括所有的详细内容。