PL/SQL学习笔记

PL/SQL的学习

第一讲
1. PL/SQL是什么
  在数据库中写过程、函数和触发器（实现复杂的查询功能）
  注意：过程函数触发器是用PL/SQL编写的，
        过程函数触发器是在ORACLE
        过程函数触发器是非常强大的数据库语言，
        过程函数可以在JAVA中调用的

2. 学习的必要性
   ①提高应用程序的运行性能
     在JAVA中的sql语句（优化）尽量少，直接调用可直接执行的
      过程函数（已经编译好了的，尽量多）
   ②模块化的设计思想
       常用的写成过程（如分页过程、订单过程和转账过程等）
   ③减少网络的传输量
        JAVA语句中的sql语句越多 传输量就越大
   ④提高安全性
        表名、字段名和密码等都封装在数据库的过程中，在JAVA程序中不可见
        而Oracle数据库又对库进行保护
  缺点：可移植性差 只能适合Oracle （在DB2 SBS等中不可用 ）

传统方法： 操作数据库通过一个链接 connection
           sql语句写在JAVA程序中通过会话、连接词和GDBC直接连接的方式
           连接传递给数据库，在数据库中编译SQL语句（耗费的时间X庞大的数据
           就是可观的开销）之后执行。

3.用什么编写PL/SQL
   ①sqlplus
   ②PL/SQL developer（是开发pl/sql块的集成开发环境ide，它是一个独立的产品
      而不是一个附件）

4.一个简单的例子
  --创建表
    create table mytest(name varchar2(20),passwd varchar(30));
  --创建过程
    create procedure sp_pro1 
    is
    begin
    insert into mytest('韩顺平','1234'); 
    end；
    注意：如何查看错误信息 通过命令：show error;
          修改insert into mytest values('韩顺平','1234');
     运行：exec sp_pro1;

5.PL/SQL可以做什么
  块编程
  编写过程、函数、触发器和包

6.编写规范（重点）
  ①注释
   单行注释 --
   如：select * from where empno = 7788; --取得员工信息
   多行注释
   /* ....*/
  ②标识符号的命名规范
   Ⅰ. 当定义变量时，建议用v_作为前缀v_sal
   Ⅱ. 当定义常量时，建议用c_作为前缀c_rate
   Ⅲ. 当定义游标时，建议用_cursor作为后缀emp_cursor;
   Ⅳ. 当定义例外时，建议用e_作为前缀e_error

7.块介绍
       块是PL/SQL的基本程序单元，PL/SQL编程实际上就是块编程，
     块可以嵌套实现复杂功能。
   
   块示意图：
     三个部分：定义部分 执行部分 例外部分
    declare:   定义常量、变量、游标和复杂的数据类型（可选）
    begin:     要执行的PL/SQL语句和SQL语句
    exception: 处理运行的各种错误（可选）
    
    注意：可以与JAVA编程结构作比较

第二讲

1. JAVA编程结构与PL/SQL结构的对比

    static void main(String []args)
    {
       int i = 1;//定义
       try{
            a ++;//执行
        }catch(Exception e){
           //捕获异常
         }
     }
   

2.实例1：只包括执行部分的PL/SQL的块

    set serveroutput on; --打开输出选项 
    set serveroutput off;

  --最简单的PL/SQL语句
    begin 
      dbms_ouput.put_line('hello,world!');--类似于JAVA中的system.out.printIn("");
    end; --dbms_output是oracle所提供的包，put_line是包中的一个过程
         --所有函数等都属于一个包

3.实例2：包含定义部分和执行部分的PL/SQL块
   
   declare 
      v_ename varchar2(20);
   begin
      select ename into v_ename from emp where empno = &no;/*相当于把ename的值
        赋给v_ename，如果要同时显示薪水也可以编写查询语句
        select ename,sal into v_ename,v_sal from emp where emno = &no;
        这里ename和sal都只能为一条记录，否则要用ref游标来解决*/  
      dbms_output.put_line('雇员名：'||ename);--拼接符号||
   end；
   相关说明：&表示要接收控制台输入的变量

4.实例3：包括定义部分、执行部分和例外处理部分
    
    Oracle中预定义了一些异常 如no_data_found就是找不到数据的例外
    
    declared
       v_name varchar2(20);
    begin 
       select ename, sal into v_ename, v_sal from emp
      where emno = &no;
       dbms_output.put_line('用户名：'||v_ename||' 工资：'||v_sal);
    end；
    exception
    when 
        no_data_found
    then  
        dbms_output.put_line('编号不存在，请重新输入');
    end；
 

核心部分：  
  （一）过程
   
     
       过程用于执行待定操作，可以指定输出（in）输出（out）参数

     create procedure sp_pro3(spName varchar2,newSal number)
     is
     begin
     update emp set sal = newSal where ename = spName;
     end;

     调用过程有两种方法：
              ①exec sp_pro3('SCOTT',4678);
              ②call sp_pro3('SCOTT',4678);/*意义并不是很大，在数据库中
                                            调用当测试用*/
        exec 和 call 区别
          exec 和 call 执行一个procedure时，    
          exec是sqlplus的命令，只能在sqlplus中控制台和pl/sql的Command控制台中使用。
          call是sql命令，任何工具都可以使用
    
    过程可以在各种语言中调用，只是接口不同而已，在JAVA中调用方法如下：
     
     例如：
          创建一个java project Test
          建立一个包 com_sp
          创建一个类 TestOracle

 

代码：

//演示java程序去调用Oracle的存储过程
package com_sp;
//导入操作数据库的SQL包
import java.sql.*;

public class TestOracle {

 /**
  * @param args
  */
 public static void main(String[] args) {
  // TODO Auto-generated method stub
  
  try{
  //1.加载 驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
  //2.得到链接
   Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "test", "test"); 
  //3.创建CallableStatement
   CallableStatement cs = ct.prepareCall("{call sp_pro1(?,?)}"); 
  //4.给？赋值
   cs.setString(1, "SMITH");
   cs.setInt(2, 1000);
  //5.执行
            cs.execute();
        //6.关闭资源
            cs.close();
            ct.close();
  }catch(Exception e){
   e.printStackTrace();
  }

 }

}

第三讲
（二）函数

            函数用于返回特定数据，函数头部必须包含return字句，函数体内必须包含return语句
         返回的数据。
     

           例：
             
              create function sp_fun1(spName varchar2)
               return number
               is
                  yearSal number(7,2);//yearSal是七位数的变量，其中两位小数      
               begin
                  select sal*12 + nvl(comm,0)*12 into yearSal from emp
                where ename = spName;
               return yearSal;
               end sp_fun1;
   
            var 定义变量的关键词 var abc number；
            call sp_fun1（‘SCOTT’） into：abc；

 

(三) 包

       包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。

      ①使用create package 命令来创建包：

        //创建包规范，包括一个过程和一个函数
         create package sp_package
         is
         procedure updata_sal(name varchar2,newsal number);//声明该包有一个过程
         function annaual_income(name varchar2) return number;//声明该包有个函数
         end sp_package;

         包的规范只包括了过程和函数的说明，但是没有过程和函数的实现代码。

       ②建立包体可以使create package body命令：
    
         create package body sp_package 
         is
         //创建过程
         procedure update_sal(name varchar2,newSal number)
         is
         begin
             update emp set sal = newsal where ename = name;
         end;
         //创建函数
         function annual(name varchar) return number
         is
               annual_salary number;
         begin
               select sal*12 + nvl(comm,0) into annual_salary
                from emp
                where ename = name;
               return annual；
         end annual;
       
          end;
           
       调用包的过程和函数时，须在过程和函数名前带有包名，访问其他方案的包，
       在包前加方案名
       如：call sp_package.update_sal('SCOTT',120);
          

（四）触发器
        

         触发器是隐含执行的存储过程。 一般情况下用户是不会主动调用，主要是
       用户一个操作后引发的事件（联动触发），比如说insert一个记录后引发多个表的
       变化，可以触发器修改其他表，用户登录时，可以用触发器记录用户IP等。
       
         当定义触发器时，必须要指定触发事件和触发操作
         常用的触发操作包括insert update delete
         触发操作实际是一个PL/SQL块
         可以用create trigger 来建立触发器
         触发器可以维护数据库的安全和一致性。

        
       

          
  定义和使用变量：
         变量类型：标量类型（scalar）、复合类型（composite）
                   参照类型（reference）、lob（large object）
    
     ①标量类型
     定义一个变长字符串： v_ename varchar(10);
     定义一个小数(范围-9999.99到9999.99)：       v_sal1   number（6，2）；
     定义一个小数并给初值5.4：                   v_sal2   number(6,2) := 5.4;
     定义一个日期类型的数据：                    v_hiredate date;
     定义一个布尔变量，不能为空，初始值为false： v_valid boolean not null default false;
     使用%type类型： 
        标识符名 表名.列名%type; 
        v_sal1   emp.sal%type;//数据与sal列中所查找到的字段类型匹配
   
     ②复合类型 -- 用于存放多个值的变量
                --（pl/sql记录、pl/sql表，嵌套表、varray动态数组）

     pl/sql记录
        类似于高级语言中的结构体或类。需要注意的是引用pl/sql记录成员时，
    必须要加记录变量作为前缀（记录变量.记录成员）如下：
       
      declare
      --定义一个pl/sql记录类型 emp_record_type
      --类型中包含了三个数据
        type emp_record_type is record (
          name emp.ename%type,
          salary emp.ename.sal%type,
          titel emp.job%type);
          sp_record emp_record_type;
         begin 
        select ename,sal,job into sp_record
        form emp where empno = 7788;
        dems_output.put_line('员工名：'||emp_record.name);
        end;

     pl/sql表
         相当于高级语言中的数组，但是需要注意的是：
         数组下标不能为负数，但pl/sql是可以为负数的，并且表下标没有限制。

         declare
         type sp_table_type is table of emp.ename%type
         index by binary_integer;
         sp_table sp_table_type;
         begin
         select ename into sp_table(0) from emp where empno =7788;
         dbms_output.put_line('员工名：'||sp_table(0));

        当去掉where，会发生溢出。

      ③参照变量
        
        参照变量是指用于存放数值指针的变量
        通过使用参照变量，可以使得应用程序共享相同对象从而降低占用的空间。
        在编写pl/sql程序时，可以使用两种参照变量：
           游标变量（ref cursor）和对象类型变量(ref obj_type)。

        ref cursor 游标变量
        使用游标时，当定义游标时不需要指定相应的select语句，但是当使用
        游标时（open时）需要指定select语句，这样一个游标就与select语句结合了。

        实例如下：
          
         declare
          --定义游标类型 sp_emp_cursor
         type sp_emp_cursor is ref cursor;
         test_cursor sp_emp_cersor;
         v_ename emp.ename%type;
         v_sal emp.sal%type;
         begin
         open test_cursor for select name,sal 
           where deptno = &no;
         --循环取出
         loop
            fetch ename ,sal into v_name,v_sal ;
            exit when test_cursor$notfound;
            dbms_output.put_line('员工名：'||v_name||' 工资：'||v_sal);
     
         end loop;
         close test_cursor;  
         end;
      
        select与游标绑定，游标指向select的结果集。

 

第四讲 
   pl/sql最终是需要被其他语言调用的。
 
   控制结构

1.条件分支语句
   pl/sql中提供了三种分支语句
     if--then   if（）｛｝
     if--then--else  if()｛｝else｛｝
     if--then--elsif--else if(){} else if（）｛｝...else{}

    create or replace procedure sp_pro1(v_ename varchar2)
    is
     v_comm emp.comm%type;
    begin
    select comm into v_comm from emp where ename = v_ename;
    if v_comm <>0 then
    update emp set comm =comm+100 where ename = v_ename;
    else
    update emp set comm = comm +200 where ename = v_ename;
    end if;
   end;

   查询表中字段的类型 desc emp；
  
   
   create or replace procedure sp_pro1(v_no number)
   is
   v_job emp.job%type;
   begin
     select job into v_job where empno = v_no;
     if v_job = 'PRESIDENT' then
       update emp set sal = sal + 1000 where empno = v_no;
     elsif v_job = 'MANAGER' then
       update emp set sal = sal + 500 where empno = v_no;
     else
       update emp set sal = sal + 200 where empno = v_no;
     end if;
   end;

 

2.循环语句
  
   ①loop循环 至少要执行一次
   
   create table users(no number,name varchar2(40)); 

   create or replace procedure sp_pro1(v_name varchar2)
   is
   v_no number := 1;
   begin
     loop
        insert into users values(v_no,v_name);
        v_no = v_no +1;
       exit when v_no = 10;
     end loop;
   end;
  ②while循环

    create or replace procedure sp_pro1(v_name varchar2)
   is
   v_no number := 11;
   begin
     while v_no <=20 loop
        insert into users values(v_no,v_name);
        v_no = v_no +1;
     end loop;
   end;

第五讲

  ③for循环（不建议使用，不符合编程习惯）
    
    基本for循环的基本结构如下
     begin
       for i in reverse 1..10 loop
       insert into users values（i，‘顺平’）；
       end loop；
      end；

3.顺序控制语句 goto ，null
  
  ①goto语句（不建议使用，多层循环可能要使用，但嵌套不超3层） 
   declare 
   i number := 1;
   begin
     loop
       dbms_output.putline('输出i='||i);
       if i = 10 then
         goto end_loop;      
       end if;
          i = i+ 1;
     end loop;
     <<end_loop>> --标号
     dbms_output.put_line('循环结束');
   end;
    
  ②null语句

   declare
     v_ename varchar2 ;
     v_sal  number;
   begin
     select ename,sal into v_name v_sal form emp 
     where empno = &no;
     if v_sal < 3000 then
      update emp set v_sal = v_sal*1.1 where ename = v_name;
     else
        null;-- 空语句，不执行任何操作
        
     end if; 
   end; 
 

实例： 返回结果集

    过程里参数默认为in  
       
    --创建一个包
       create or replace package test_package
       as
       type test_cursor is ref cursor;
       end test_package;
       
    --创建一个过程
        create or replace procedure sp_pro1
      (v_no in number,p_cursor out test_package.test_cursor )
      is
      begin
       open p_cursor for select * from emp where deptno = v_no;
      end;
    
     --在java中调用该过程
         
        
     创建一个java工程 TEST
     创建一个java包  Test
     创建一个类    TestOracle
  
   package Test;
    
   import java.sql*;
 
   public Class TestOracle{

         public static void main(String []args){

      try{

        //加载驱动
     Class.ForName("oracle.jdbc.driver.OracleDriver");

        //建立连接
     connection ct = DriverManager.getConnection
      ("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","test","test");
       //调用过程
      CallableStatement cs = ct.preparaCall("{call sp_pro1(?,?)}");

      //参数赋值
       cs.setInt(1,10);
       cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
     //
        执行语句
       cs.execute();

     //返回结果集
       ResultSet rs = (ResultSet)cs.getObject(2);

      while(rs.next()){
        System.out.printIn(rs.getInt(1)+" " + rs.getString(2));
   }

  }catch(Exception e){

       e.printStackTrace();
   }Finally{
     //关闭资源
       cs.close();
       ct.close();
   }
   }

   }

第六讲
   编写分页过程

 

    -- Oracle的分页

   --视图
   select * from emp;
   --给视图编号
   select t1.*,rownum rn from (select * from emp)t1;
   --查出视图中前十条记录
   selece t1.*,rownum rn from (select * from emp)t1 where rownum <= 10;
   --查出视图中第4到10条记录
   select * from (selece t1.*,rownum rn from
     (select * from emp)t1 where rownum <= 10;) where rn >= 4;

 

   编写分页过程  
      
   --建立游标

     create or replace package test_package as
     type test_cursor is ref cursor;
     end test_package;

   --分页过程
     
     create or replace procedure sp_fenye(
    
    v_tableName varchar2;--表名
    v_pageSize  number;  --每页显示的记录数
    v_pageNow   number;  --需要显示的当前页码
    v_myrows   number;   --总记录数
    v_myPage   number;   --总页数
    p_cursor   package.test_cursor --需要返回的记录集
)is
    v_begin number := v_pageSize*(v_pageNow-1) + 1;
    v_end   number := v_pageSize*v_pageNow;
    v_sql   varchar2;
 begin
    v_sql :='select * from (selece t1.*,rownum rn from
     (select * from '||v_tableName||')t1 where rownum <= '||v_end||';)
      where rn >= '||v_begin;
   open p_cursor for v_sql;
   --计算myRows和 myPage

   v_sql := 'select count(*) from'||v_tableName;
   execute immediate v_sql into v_myRows;

   if mod(v_myRows,v_pageSize) = 0 then
      myPage := v_myRows / v_pageSize;
    else
      myPage := v_myRows / v_pageSize  + 1; 
   end if;
   
    close p_cursor;
 end;

    --用java调用

    创建java工程  FenYeObject
    创建java包 FENYE
    创建java类 FenYe

    package FEBYE
   
   import java.sql*;

   public Class FenYe {

   public static void main(String []agrs){

     try{
       Class.forName("oracle.jdbc.driver.OracleDriver");

      Connection ct = DriverManager.getConnection
    ("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","test","test");

     CallableStatement cs = ct.preparaCall("{call sp_fenye(?,?,?,?,?,?)}");
  
     cs.setString(1,emp);
     cs.setInt(2,5);
     cs.setInt(3,2);

     cs.registerOutParameter(4,"oracle.jdbc.OracleType.INTEGER");
     cs.registerOutParameter(5,"oracle.jdbc.OracleType.INTGEER");

     cs.execute();

     ResuleSet rs = (ResultSet)cs.getObject(6);
    
     while(rs.next()){
       System.out.printIn("总记录数："+rs.getInt(4));
       System.out.printIn("总页数："+rs.getInt(5));
       System.out.printIn(rs.getObject(6));
     }

    }catch(Exception e){
       e.printStackTrace();
    }finally{
     cs.close();
     ct.close();
     }

 }
}

    
  如果按薪水的从低到高先显示，只需修改： 
  v_sql :='select * from (selece t1.*,rownum rn from
     (select * from '||v_tableName||')t1 where rownum <= '||v_end||';)
      where rn >= '||v_begin;
  为：

   v_sql :='select * from (selece t1.*,rownum rn from
     (select * from '||v_tableName||'order by sal)t1 where rownum <= '||v_end||';)
      where rn >= '||v_begin;

 

第七讲

   例外处理
    oracle将例外分为三种：预定义例外、非预定义例外和自定义例外

    ①预定义例外：  用于处理常见的oracle错误
    ②非预定义例外：用于处理预定义不能处理的例外
    ③自定义例外：  自定义例外用于处理与Oracle错误无关的其他情况

   例外传递
     
    declare 
      v_name emp.ename%type;
    begin
      select ename into v_name from emp where empno = &no;
      dbms_output.putline('员工名：'||ename);
    exception
       when no_data_found then
       dbms_output.put_line('您输入的编号不存在！');
    end;

Ⅰ.处理预定义例外
    预定义例外是由PL/SQL所提供的系统例外。
    当PL/SQL应用程序违反了Oracle规定的限制时，则会隐含触发一个内部例外。
    pl/sql为开发人员提供了21个例外。
   
   ①case_no_found  (case when ...then  end case;)
   ②cursor_already_open 游标重复打开
   ③dup_val_on_index    唯一索引所对应的列上插入重复的值
   ④invaild_cursor      试图在不合法的游标上执行操作（从没打开的游标上提取数据
                         或是关闭没有打开的游标）
   ⑤invaild_number      输入数据有误
   ⑥no_data_found       输入的数据表中不存在
   ⑦too_many_rows       当执行select into语句时，如果返回超过一行（比如：select
                         ename into v_ename from emp；缺少where限制）
   ⑧zero_divide         当执行2/0语句时，则会触发该异常
   ⑨value_error         执行赋值语句时，变量长度不足以容纳实际数据

  其他预定义例外
   ① login_denide        当用户非法登录时
   ② not_loggen_on       用户没有登录就执行dml操作
   ③ storage_error       超出内存空间或是内存被损坏
   ④timeout_on_resource  Oracle等待资源出现了超时（比如说资源被锁定）

Ⅱ.非预定义例外

   用于处理与预定义无关的Oracle错误

Ⅲ.处理自定义例外

   与Oracle错误无关，是由开发人员为特定情况所定义的例外

    create or replace procedure ex_pro (v_ename varchar2)
    is
      myEx exception;
    begin

      update emp set sal = sal + 1000 where ename = v_ename;
      if sql%notfound then
         raise myEx;
      end if;
    exception
       when myEx then
         dbms_output.putline('不存在该员工，更新失败！');
    end;
  

   oracle视图

      视图是一张虚拟表，其内容由自定义查询。
      同真实表一样，视图包含一系列带有名称的行和列数据。
      但是，视图并不在数据库中以存储的数据集形式存在。
      行和列数据数据来自来自 由定义视图的查询所用表，并且在引用视图时动态生成。
    
  
   视图与表的区别
     ①表需要占用磁盘空间，视图不需要
     ②视图不能添加索引
     ③使用视图可以简化复杂查询 比如：学生选课系统
     ④视图有利于提高安全性  比如：不同用户查看不同视图

      
     
   创建修改视图：
      ①创建视图
        cerate view 视图名 as select语句 [with read only]

        例1：create view myview as select * from emp where sal < 1000;
             select * from myview;

        例2：create view myview1 as select emp.empno,emp.ename,dept.dname
             from emp,dept
             where emp.deptno = dept.deptno;
             select * from myview2;
           
            视图上可以再次建立视图，从而再次建立复杂度。

      ②创建或修改视图
        create or replace view 视图名 as select语句 [with readonly]
      
      ③删除视图
        drop view 视图名