一、控制结构

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

　　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 isbegin    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;  <
         
          >  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) isbegin  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_addBook11  */12 public class callPro_addBook {13     @Test14     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 }
  

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

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

• 使用命令行创建过程：
  create or replace procedure pro_showBook  (bookId in number,bookName out varchar2,pub out varchar2) isbegin  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_addBook11  */12 public class callPro_showBook {13     @Test14     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 }
  

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

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

　　

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

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

• --建立包，在该包中，定义一个游标类型test_cursorcreate 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) isbegin  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_addBook12  */13 public class callPro_showPubBook {14     @Test15     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             else38                 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 }

  

三、分页编程

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

　　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 block45                    e.printStackTrace();46             } finally {47                    try {48                          // 关闭资源49                          cs.close();50                          ct.close();51                   } catch (Exception e1) {52                          // TODO Auto-generated catch block53                          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_foundcreate 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_opendeclare  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_founddeclarev_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_rowsdeclare  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 就可以看到谁借了什么书了，包括所有的详细内容。