PLSQL编程_存储过程and存储函数

妖狐艹你老母 2022-06-04 06:45 315阅读 0赞

### 存储过程 ###

> 存储过程相当于是对代码的封装,可提高代码的复用性,已经代码执行效率,

语法 :

create [or replace] procedure 存储过程名称(参数1,参数2,....) is begin end;

demo

使用执行过程输出一句话,
    
    --创建执行过程
    create or replace procedure procedure_name(str varchar2) is begin dbms_output.put_line(str);
    end;
    
    
    
    --使用执行过程 方式一 :
    call procedure_name('学习使我快乐');
    
    --使用执行过程 方式二:
    declare 
    
    begin procedure_name('学习使我快乐');
    end;

输出结果如下:  
![这里写图片描述][SouthEast]

说白了,其实存储过程就相当于是函数,方法,概念都是一样的,

#### 封装存储过程:给指定员工涨薪,并打印涨薪前和涨薪后的工资 ####

Demo

create or replace procedure procedure_name(u_empno in number,addMoney in number) is u_sal number;
    
    begin select sal into u_sal from emp where empno=u_empno;
    
      dbms_output.put_line('加薪前的工资:'||u_sal);
    
      update emp set sal=u_sal+addMoney where empno=u_empno;
      dbms_output.put_line('加薪后的工资:'||(u_sal+addMoney));
    
      --提交事务
      commit;
    end;
    
    //调用存储过程
    
    call procedure_name(7369,100);
    
    
    输出结果如下

![这里写图片描述][SouthEast 1]

#### 封装一个存储过程: 获取指定员工的年薪 ####

需要一个员工编号,返回一个年薪

create or replace procedure proc_getyearsal(vempno in number,vyearsal out number) is begin select sal*12+nvl(comm,0) into vyearsal from emp where empno=vempno;
    end;
    
    
    --使用存储过程
    
    declare 
    yearSal number;
    begin procedure_name(7369,yearSal);
    
      --输出结果 
      dbms_output.put_line(yearSal);
    end;

输出结果如下  
![这里写图片描述][SouthEast 2]

--------------------

### 存储函数 ###

> 存储函数可以提高代码的复用性  
> 增加代码执行效率,  
> 语法

create or replace function function_name(参数1,参数2,...) return 返回值类型
    is
    
    begin
    
    end;

存储函数 跟 存储过程的区别  
1. 存储过程是给jdbc调用的,存储函数是给存储过程和sql语句调用的,  
2. 存储过程没有返回值,但是可以返回数据,(通过out 类型的参数)  
3. 存储过程不能在sql里调用,但是存储函数可以

存储函数 跟 存储过程达到的功能基本一致,,区别很小很小,

#### 封装获取指定编号员工的年薪的存储函数 ####

create or replace function function_name(v_empno in number) return number is vyearSal number;
    begin select sal * 12 + nvl(comm,0) into vyearSal from emp where empno = v_empno ;
      return vyearSal;
    end;
    
    
    在sql语句里使用存储函数 
    select function_name(7369) from dual;

运行结果如下  
![这里写图片描述][SouthEast 3]

在 plsql里使用存储函数的方式如下

declare
    
    begin dbms_output.put_line(function_name(7369));
    end;

运行结果如下  
![这里写图片描述][SouthEast 4]

在java代码里使用存储过程获取数据

首先,创建一个存储过程 
    
    create or replace procedure procedure_name(vmeps out sys_refcursor) is begin --开启游标 open vmeps for select * from emp;
    end;
    
    游标就相当是jdbc 的resultset,我们需要使用游标来获取数据 
    
    注意,需要导入oracle的jar包,还有jdbc提供的
     @Test
        public void test_01() throws Exception {
            //注册驱动
            Class.forName("oracle.jdbc.OracleDriver");
    
            //获取连接,写法基本一致,就数据库ip不同,
            String url = "jdbc:oracle:thin:@192.168.129.100:1521:orcl";
            String username= "scott";
            String password = "tiger";
            Connection conn = DriverManager.getConnection(url, username, password);
    
            //获取oracle的执行对象  {
       call <procedure-name>[(<arg1>,<arg2>, ...)]} 格式 String sql = "call procedure_name(?)";//游标类型用 ? 号代替 
            CallableStatement call = conn.prepareCall(sql);
    
            call.registerOutParameter(1, OracleTypes.CURSOR);
            System.out.println(call.getClass().getName());  //输出查看类型,
    
            conn.close();
        }
    因为我们不知道CallableStatement 的真实类型,所以我们先输出打印看看

![这里写图片描述][SouthEast 5]

我们可以通过输出发现 ,call 其实是这个T4CCallableStatement类型,我们强转成T4CCallableStatement类型 ,但是会报错

![这里写图片描述][SouthEast 6]

我们进入T4CCallableStatement这个类看看,

![这里写图片描述][SouthEast 7]  
观察T4CCallableStatement类我们发现,这个类没有修饰符, 一个类如果没有修饰符,那么默认就是 protect 受保护的,使用范围在当前包下,所以我们放弃转换对象成  
T4CCallableStatement, 我们把目标转换成它的父类

然后我们调用getCursor的方法,拿到resultSet,然后我们就可以获取里面的值啦,  
![这里写图片描述][SouthEast 8]

以上就是plsql中的存储函数,存储过程,

[SouthEast]: /images/20220604/3ad4e0959e7f43fc9c2bdfe968ecb91d.png
[SouthEast 1]: /images/20220604/6e37f06c40fd43a5ab7763d328fa9e1d.png
[SouthEast 2]: /images/20220604/2c61a63e729144a6b05f4101e78bd594.png
[SouthEast 3]: /images/20220604/75b11ba038e04932b5f7691dbeff8b72.png
[SouthEast 4]: /images/20220604/0baf9c85754747d1b553933c7e99160e.png
[SouthEast 5]: /images/20220604/45342fb08c2f459e967d844daad31a3b.png
[SouthEast 6]: /images/20220604/e0bf593208bb480c9114f706807ae095.png
[SouthEast 7]: /images/20220604/463aa66dc28d4c1eb9f4ebe5d96cda72.png
[SouthEast 8]: /images/20220604/2d6f55556902489bab36e006f482e87a.png