存储过程和函数详解

--01.什么是PL/SQL?

procedure language /SQL 过程化语言，oracle数据中提供的编程语言

通过plsql语言可以实现复杂业务逻辑（条件分支，循环判断等）

为什么要学习pl/sql?

   传统项目中，一般复杂的业务逻辑都是在业务逻辑处理层解决。

   如果使用plsql语言（存储过程、函数），那就可以替代java代码中复杂的业务逻辑。

 

 

 

分析传统项目和plsql 存储过程编程优缺点：

    传统项目中：代码维护相对比较方便，对开发人员要求仅仅是只要会java语言

plsql存储过程编程：必须要掌握plsql语言，维护不方便

--02.PL/SQL基本语法

语法：

declare

          --声明变量 （普通变量、常量、引用型变量、记录型）

          --游标变量（光标变量）

          --异常变量（例外变量）

begin

          --逻辑 DML语句

          --异常处理

end;

--03.普通变量和常量使用

--普通变量

--private String name = "小王";

--System.out.pritln(name);

declare

          变量名称 数据类型(长度) := '初始值';

begin

         dbms_output.put_line(变量名称); //打印的语句

end;

 

--例题1

 

 

declare 
name1 varchar(10):='lanpo1';
begin 
  dbms_output.put_line(name1);
end;

 

 --例题2

 

   

     declare
     v_name varchar2(10):='小王';
     begin  --这里结果是ename 不是小王
       select ename into v_name from emp e where e.empno=7788;
       dbms_output.put_line(v_name);
       end;
      

 

 

  -- 常量

 

 -- private final String name='小王'
  declare
    v_name constant varchar2(10):='小王' ;--constant常量
    begin
    --select ename into v_name from emp e where e.empno=7788;
    --此时插入报错
    dbms_output.put_line(v_name);
    end;

 

 

 

--04.引用型变量 （重点）

为什么用引型变量？

因为上面的例题2中，ename的类型你可能不知道是varchar2 ，此外就算你知道数据类型，长度也可能不知道，

ename如果长度是30能用v_name接收？所有这样是有风险的。

 

引用表中某个字段的变量数据类型

语法：

变量名称 表名.字段名称%type;

--emp.ename%type (推荐使用此方法定义变量数据类型)

--例题 1

  

 declare
   v_name emp.ename%type;--emp.ename%type (推荐使用此方法定义变量数据类型)
   begin
     select e.ename into v_name from emp e where e.empno=7788;
     --into的关键字是将查询到的结果保存到变量v_name中
     dbms_output.put_line(v_name);
     end;

 

 --例题 2

 

   declare
   v_name emp.ename%type;--没有这一行会报错
   v_sal emp.sal%type;
   begin
     select e.ename,e.sal into v_name,v_sal from emp e where e.empno=7788;
     dbms_output.put_line('姓名：'||v_name||'薪资：'||v_sal);
     end;

    

注意：|| 相当于java中字符的+号

 

 --05.记录型变量

记录一条数据

语法：

变量名称 表名%rowtype;

declare
   v_row emp%rowtype;--声明一条数据变量  v_row：相当于java实体对象 保存了很多的数据
   begin
     select * into v_row  from emp e where e.empno=7788;   --into 关键字就将结果存放到变量中
     dbms_output.put_line(v_row.ename||' : '||v_row.sal);
     end;

 

   --总结

普通变量以及常量中的数据类型推荐使用引用型变量声明方式

如果是为了存单个字段，推荐使用引用型变量

如果是为了存一条数据，推荐使用记录型变量

 

--06.条件分支

语法1：

if 条件 then

  --逻辑处理

end if;

语法2：

if 条件 then

  --逻辑处理

else

  --逻辑处理

end if;

语法3：

if 条件 then

  --逻辑处理

elsif 条件 then

  --逻辑处理

else

  --逻辑处理

end if;

 

--07.根据数据判断如果小于18输出未成年人，18-60成年人，60以上老年人

declare
    v_age varchar2(10):=&age; --&age替代具体的值有弹框输入的效果
   
    begin
      if v_age<18 then
        dbms_output.put_line('未成年人') ;
      elsif v_age<60 then
          dbms_output.put_line('成年人') ;
          else
            dbms_output.put_line('老年人') ;
       end if ; --本身的结束
      
       end; --

 

 注意：案例中少分号（；）会报错

 

 

--08.loop循环

语法1：无条件循环，有条件退出

loop

  --逻辑

  exit when 条件;

end loop;

 

语法2：有条件循环

while 条件

loop

 

end loop;

语法3：for循环

for 变量名称 in 起始值..结束值

  loop

   

  end loop;

--09.输出1到100的个数

 

语法一
declare
   v_number varchar2(10):=1;
   begin  
  loop
   dbms_output.put_line(v_number);
   v_number := v_number+1; --这里一定要冒号
  exit when v_number>100;
   end loop;
   end;
语法二
declare
      v_number varchar2(10):=1;
 
  begin
 
      while v_number<=100
 
      loop
       dbms_output.put_line(v_number);
        v_number := v_number+1; --这里一定要冒号
      end loop;
   end;
语法三
for 变量名称 in 起始值..结束值
  loop
   
  end loop;
 
  --
    declare
    begin 
    for v_number in 1..100
     loop
       dbms_output.put_line(v_number);
     end loop;
      end;

 

--10.游标 cursor

就是用来存多行数据的

语法：

--声明游标变量

cursor 游标名称 is 查询的sql；

--使用游标变量

open 游标名称;

  loop

    fetch 游标名称 into 记录型变量;   --fetch 游标 取出一条数据 将取出的结果放入记录型变量中

          --输出结果

          --退出游标循环

          exit when  游标名称%notfound;

  end loop;

close 关闭游标;

--11.通过游标输出emp表中所有员工的信息

declare
  cursor c_emp is select * from emp;--声明游标 存放emp表数据
  v_row emp%rowtype;--声明记录型变量
begin
  --使用游标
  open c_emp;
  loop
    fetch c_emp into v_row;--结束加分号
    --退出游标循环
     exit when c_emp%notfound;
     --注意点：要放在输出员工信息之前 退出游标循环
     dbms_output.put_line('编号'||v_row.empno||'薪资'||v_row.sal||'姓名 '||v_row.ename);
    end loop;
end;

 

 

--12.通过游标输出指定部门的员工信息

语法：（有条件）

--声明游标变量

cursor 游标名称(变量名称 数据类型) is 查询的sql  where 条件=变量名称；

--使用游标变量

open 游标名称(参数);--打开游标的时候，是真正查询表的时候

  loop

    fetch 游标名称 into 记录型变量   --fetch 游标 取出一条数据 将取出的结果放入记录型变量中

          --输出结果

          --退出游标循环

          exit when  游标名称%notfound;

  end loop;

close 关闭游标

例题：

declare
     cursor v_emp (v_deptno number) is select * from emp e where  e.deptno=v_deptno;
     v_row emp%rowtype; 
     begin
       open v_emp(&mag);
       loop
         fetch v_emp into v_row;
         exit when v_emp%notfound;
           dbms_output.put_line('编号'||v_row.empno||'薪资'||v_row.sal||'姓名 '||v_row.ename);
         end loop;
         close v_emp;
     end;

 

 

 

--13.异常

什么是异常？

程序中有可能出现的错误，通过提前预定义程序来捕获，达到程序健壮性和安全性

--预定义异常

--除以0的异常plsql程序

 

declare
       v_num number(10);
begin
       v_num := 10/0;
      exception when ZERO_DIVIDE then
          dbms_output.put_line('除数不能为0');
end;

 

--一个变量容量不够时异常plsql程序

declare
    v_num number(1);
    begin
      v_num:= 10;
      exception when VALUE_ERROR   then
         dbms_output.put_line('将一个变量赋给另一个不能容纳该变量的变量时引发');
         end;

--根据数字判断，如果年龄数字大于150，抛异常

--------------------------存储过程-------------------------------------

--14.什么是存储过程？

  存储过程是由一组sql集合，通过这一组sql集合进行复杂业务处理。

存储过程没有返回值，存储过程有名称

--15.如何使用存储过程？

语法：

--in关键字可以省略 但 out不可以省略

create [or replace] procedure   存储过程名称(参数 in|out 数据类型)

is|as（或是as）

  --声明变量

begin

  --逻辑处理

end; 

 --17.使用存储过程,输出指定员工的年薪 

create  or  replace  procedure p_yeansal(v_empno number)  //此处省略了in
is
    v_row emp%rowtype;
   
    begin
      select *  into v_row from emp e where e.empno=v_empno;
       dbms_output.put_line('编号'||v_row.empno||'姓名 '||v_row.ename||'月薪'||v_row.sal);
    end;

 

--方式一：测试 选中上面程序正确运行后，选中下方call关键字语句执行得出结果

    call p_yeansal(7369);

   

 

 

  --方式二

   

    begin

       p_yeansal(7788);

    end;

 --注意上面语句写错是不弹框报错的，需要查看

 

 

 --18.指定员工的年薪，用out参数返回年薪

 创建存储过程

create or replace procedure p_yeansal(v_empno number, o_sal out emp.sal%type)
is  --v开头的变量是输入参数  o开头的是输出参数
begin
   select sal into o_sal from emp e where e.empno=v_empno;
end;

 调用存储过程

declare
  --定义一个参数接收上面的输出参数 就是o_sal
  v_sal number(10);
 
begin--调用上面的程序
  p_yeansal(7788,v_sal);
  --打印
   dbms_output.put_line(v_sal);
end;

 

 

 

--通过plsql客户端工具测试(了解)

--19.什么是函数？

自定义函数

存储过程 以及 函数区别

1.存储过程没有返回值，但有输入输出参数

2.函数必须有返回值，有输入输出参数，但一般不用输出参数

3.语法区别

4.通过java代码调用存储过程 还是函数？

  java代码一般都是调用存储过程，函数一般提供存储过程调用的

--20.如何使用函数？

函数语法：

create or replace function 函数名称(参数 in|out 数据类型)

return 返回数据的类型

is|as

begin

    return 结果;

end;

 --21.计算某个员工年薪并返回

 

create or replace function f_sal(v_empno in number )
   return number
   as
   v_sal number(10);
   begin
     select sal into v_sal from emp e where e.empno=v_empno;
     return v_sal;
   end;

 

 调用函数

--测试
   declare
   v_sal number(10);
   begin
     --有返回值需要接收
     v_sal:=f_sal(7788);
     dbms_output.put_line(v_sal);
   end;

 

 --模仿存储过程调用函数

 

--1
   select f_sal(7788)  from dual;
   --2
   create or replace procedure p_yeansal2(v_empno number, o_sal out emp.sal%type)
   is 
   begin
   select f_sal(v_empno) into  o_sal from emp e where e.empno=v_empno;
end;

 

 

--22.通过java代码测试jdbc连接

-强调一定要新建一个工作空间

1）

2）

package com.itcast.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BaseDao {
    static{
        try {
            Class.forName("oracle.jdbc.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    public static Connection getConn() throws SQLException{
        String url="jdbc:oracle:thin:@192.168.244.10:1521:orcl";//192.168.244.10 oracle安装的电脑ip
        String user="scott";
        String password="123";
        return DriverManager.getConnection(url, user, password);
    }
    
    public static void closeAll(ResultSet rs,Statement stmt,Connection conn){
        if(rs!=null){//关闭资源
            try {
                rs.close();
            } catch (SQLException e) { 
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 

 

3）

package com.itcast.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleTypes;
/**
 * 函数测试
 * @author admin
 *
 */
public class FunctionDao {
    
    public static void main(String[] args) {
        getEmp(7788l);//7788 数据类型是Long
    }
    
    /**
     * 调用f_sal(v_empno in number)测试
     * @param empno
     */
    public static void getEmp(Long empno){
        Connection conn = null;
        CallableStatement prepareCall = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConn();
            /**
             * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
               {call <procedure-name>[(<arg1>,<arg2>, ...)]}

             */
            prepareCall = conn.prepareCall("{?= call f_sal(?)}");
            prepareCall.registerOutParameter(1, OracleTypes.NUMBER); //设置返回参数的类型
            prepareCall.setLong(2, empno);
            prepareCall.execute();
            
           System.out.println(prepareCall.getLong(1));
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
     
}

 

 

 到此完成调用函数的过程

--24.jdbc调用存储过程 out参数
通过调用有out参数的存储过程，返回月薪（某一个值）

 

package com.itcast.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;

/**
 * 存储过程测试
 * @author admin
 *
 */
public class ProcedureDao {
    
    public static void main(String[] args) {
        getsal(7369l);
       
    }
    
    /**
     * 通过调用存储过程 返回普通数据类型
     *  //p_yearsal(v_empno number,o_sal out emp.sal%type)
     * @param empno
     */
    public static void getsal(Long empno){
        Connection conn = null;
        CallableStatement prepareCall = null;
        try {
            conn = BaseDao.getConn();
            /**
             * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
               {call <procedure-name>[(<arg1>,<arg2>, ...)]}

             */
            prepareCall = conn.prepareCall("{call p_yearsal(?,?)}"); //第二个参数是输出参数
            prepareCall.setLong(1, empno);
            
            prepareCall.registerOutParameter(2, OracleTypes.NUMBER); //设置返回参数的类型
           
            prepareCall.execute();
            
           System.out.println(prepareCall.getLong(2));// 输出的参数是2
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    
    
}

 

--25.jdbc调用out参数是游标的存储过程（表数据查询出来）
--out游标接收指定部门员工的所有信息

--创建存储过程返回参数是游标

create or replace procedure p_cur_yearsal(v_deptno number,o_cur_emp out sys_refcursor)
is
 
begin
  open o_cur_emp for select * from emp where deptno = v_deptno;
end;

 ---代码调用

/**
 * 返回参数是游标
 *  p_cur_yearsal(v_deptno number,o_cur_emp out sys_refcursor)
 * @param deptno
 */
public static void getCurSal(Long deptno){
  Connection conn = null;
  CallableStatement prepareCall = null;
  try {
    conn = BaseDao.getConn();
    /**
     * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
       {call <procedure-name>[(<arg1>,<arg2>, ...)]}

     */
      prepareCall = conn.prepareCall("{call p_cur_yearsal(?,?)}");
      prepareCall.setLong(1, deptno);
      prepareCall.registerOutParameter(2, OracleTypes.CURSOR); //设置返回参数的类型
      prepareCall.execute();
      OracleCallableStatement ocs = (OracleCallableStatement)prepareCall;
      ResultSet rs = ocs.getCursor(2);
      while (rs.next()) {
      System.out.println(rs.getObject(1)+"=="+rs.getObject(2));
    }
  } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }
}

 

 

 

视图/存储过程/函数/游标

1.视图（在某一个表创建一个视图）

2.函数（提供存储过程调用）

3.游标

--通过plsql程序调用out参数为游标的存储过程

 

declare
v_cur sys_refcursor;--定义游标变量接收数据
  v_row emp%rowtype; --定义记录型变量
begin
  p_cur_yearsal(10,v_cur);--调用存储过程
 
  --循环游标
  loop
    fetch v_cur into v_row;--取出游标中每一行数据放入记录型变量中
    --退出
    exit when v_cur%notfound;
    dbms_output.put_line(v_row.empno);
  end loop;
end;