Oracle存储过程和自定义函数详细讲解_oracle存储函数和自定义函数的区别

创建和使用存储过程

用 create procedure 命令建立存储过程和存储函数。

create [or replace] procedure 过程名(参数列表)
PLSQL子程序体;

注意事项：

存储过程或者存储函数，只能创建或者替换；
参数可以带也可以不带；
as 相当于PLSQL语句中的 declare ，用来声明变量，游标等，但是不可以省略。

不带参数的存储过程：不用带括号

--第一个存储过程：打印HelloWorld
调用存储过程的两种方式：
1、exec sayHelloWorld();
2、begin
       sayHelloWorld();
       sayHelloWorld();
create or replace procedure sayHelloWorld
--说明部分
begin
   dbms_output.put_line('HelloWorld');
end;

在PL/SQL Developer中执行存储过程：
这里写图片描述

--如果想要在命令窗口显示（即打印出来），要先
SQL> set serveroutput on
--第一种调用方式
SQL> exec sayHelloWorld()
HelloWorld
PL/SQL procedure successfully completed
--第二种调用方式
SQL> begin
  2      sayHelloWorld();
  3      sayHelloWorld();
  4  end;
HelloWorld
HelloWorld
PL/SQL procedure successfully completed

带参数的存储过程

给指定的员工涨100元工资，并且打印涨前和涨后的薪水。

使用的表和数据：

create table EMP
  empno VARCHAR2(16) not null,
  ename VARCHAR2(16) not null,
  sal   NUMBER,
  comm  NUMBER,
  job   VARCHAR2(16),
  deptno NUMBER
insert into EMP (empno, ename, sal, comm, job, deptno) values ('7839', 'xiaoming', 1200, 500,  'Actor', 12);
insert into EMP (empno, ename, sal, job, deptno) values ('7566', 'xiaowang', 800, 'Lawyer', 12);
commit;

创建存储过程：

--创建一个带参数的存储过程：
--给指定的员工涨100块钱的工资，并且打印涨前和涨后的薪水
如何调用：
begin
    addSal(7839);
    addSal(7566);
create or replace procedure addSal(pempno in emp.empno%type)
  --定义变量
  pename emp.ename%type;
  beforesal emp.sal%type;
  aftersal emp.sal%type;
begin
  --得到员工涨前的薪水
  select ename,sal into pename,beforesal from emp where empno=pempno;
  --给该员工涨100
  aftersal:=beforesal+100;
  update emp set sal=aftersal where empno=pempno; 
  --需不需要commit？
  --注意：一般不在存储过程或者存储函数中，commit和rollback
  dbms_output.put_line('姓名: '||pename||' 涨前工资:'||beforesal||'涨后工资:'||aftersal);
end;
执行结果： 
SQL> set serveroutput on
SQL> begin
  2      addSal(7839);
  3      addSal(7566);
  4  end;
姓名: xiaoming 涨前工资:1200 涨后工资:1300
姓名: xiaowang 涨前工资:800 涨后工资:900
PL/SQL procedure successfully completed 
要说明，参数是输入参数(in)还是输出参数(out)；
为保证调用多个存储过程中处在同一个事务中，所以一般不在存储过程或者存储函数中,commit或rollback； 
函数(Function)为一命名的存储程序，可带参数，并返回一计算值；
函数和过程的结构类似，但必须有一个return子句，用于返回函数值。 
创建存储函数的语法 
create [or replace] function 函数名(参数列表)
return 函数值类型
PLSQL子程序体; 
注意事项： 
与存储过程注意事项类似，不同的是，必须有个返回值；
参数列表可以有，也可以没有.当没有时，函数名后面不要带括号。 
例子：查询某个员工的年收入。 
--存储函数：查询某个员工的年收入
create or replace function queryempannal(pempno in number)
return number
  --定义变量保存员工的薪水和奖金
  psal emp.sal%type;
  pcomm emp.comm%type;
begin
  --得到该员工的月薪和奖金
  select sal,comm into psal,pcomm from emp where empno=pempno;
  --直接返回年收入
  return psal*12+nvl(pcomm,0);
end; 
in和out参数 
一般来讲，存储过程和存储函数的区别在于存储函数可以有一个返回值，而存储过程没有返回值；
过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值； 
 
  存储过程和存储函数都可以有out参数；
存储过程和存储函数都可以有多个out参数；
存储过程可以通过out参数来实现返回值。
什么时候用存储过程/存储函数? 
 
  原则：如果只有一个返回值，用存储函数；否则，就用存储过程。 
--out参数：查询某个员工姓名、月薪和职位
1、查询某个员工的所有信息---->out参数太多？
2、查询某个部门中所有员工的所有信息----->out中返回集合？
create or replace procedure queryempinform(eno in number,
                                           pename out varchar2,
                                           psal out number,
                                           pjob out varchar2 )
begin
  --得到该员工的姓名、月薪和职位
  select ename,sal,job into pename,psal,pjob from emp where empno=eno;
end; 
在应用程序中访问存储过程和存储函数 
先看一下数据库工具类： 
package test.oracle.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
    private static String driver = "oracle.jdbc.OracleDriver";
    private static String url = "jdbc:oracle:thin:@20.26.26.250:1521:ZJDQ_TEST";
    private static String user = "DDZX";
    private static String password = "ddzx_1Q#";
    // 注册数据库的驱动
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
    // 获取数据库连接
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        return null;
    // 释放数据库资源
    public static void release(Connection conn, Statement sta, ResultSet rst) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                conn = null;
        if (sta != null) {
            try {
                sta.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                sta = null;
        if (rst != null) {
            try {
                rst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                rst = null;
访问存储过程 
package test.oracle.demo;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.Test;
import oracle.jdbc.OracleTypes;
import test.oracle.utils.JDBCUtils;
public class TestProcedure {
    create or replace procedure queryempinform(eno in number,
                                        pename out varchar2,
                                        psal out number,
                                        pjob out varchar2 )
         begin
           select ename,sal ,job into pename, psal,pjob from emp where empno=eno ;
    @Test
    public void testProcedure() {
         * {call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{call queryempinform(?,?,?,?)}";
        Connection conn = null;
        CallableStatement sta = null;
        try {
            // 获取连接
            conn = JDBCUtils.getConnection();
            // 通过连接创建statement
            sta = conn.prepareCall(sql);
            // 对于in参数,赋值
            sta.setInt(1, 7839);
            // 对于out参数,申明
            sta.registerOutParameter(2, OracleTypes.VARCHAR);
            sta.registerOutParameter(3, OracleTypes.NUMBER);
            sta.registerOutParameter(4, OracleTypes.VARCHAR);
            // 执行调用
            sta.execute();
            // 取出结果
            String name = sta.getString(2);
            double sal = sta.getDouble(3);
            String job = sta.getString(4);
            System.out.println(name + "\t" + sal + "\t" + job);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, sta, null);
访问存储函数 
package test.oracle.demo;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.Test;
import oracle.jdbc.OracleTypes;
import test.oracle.utils.JDBCUtils;
public class TestFunction {
    create or replace function queryempannal(pempno in number)
         return number
           psal emp.sal%type;
           pcomm emp.comm%type;
         begin
           select sal,comm into psal, pcomm from emp where empno=pempno ;
           return psal*12+nvl (pcomm,0);
    @Test
    public void testFunction() {
        // {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{?= call queryempannal(?)}";
        Connection conn = null;
        CallableStatement call = null;
        try {
            // 获取数据库连接
            conn = JDBCUtils.getConnection();
            // 通过连接获取statement对象
            call = conn.prepareCall(sql);
            // 对于out参数 声明
            call.registerOutParameter(1, OracleTypes.NUMBER);
            // 对于in参数 赋值
            call.setInt(2, 7839);
            // 执行调用
            call.execute();
            double income = call.getDouble(1);
            System.out.println("该员工的年收入为:" + income);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.release(conn, call, null);
在out参数中使用光标 
申明包结构 , 包头 ,包体。 
案例：查询某个部门中所有员工的所有信息。 
create or replace package mypackage01 as
     --使用type关键字,自定义一个光标类型
     type empcursor is ref cursor;
     procedure queryEmpList(dno in number,empList out empcursor);
end mypackage01; 
create or replace package body mypackage01 as
     procedure queryEmpList(dno in number,empList out empcursor) as
     begin
          --打开光标
          open empList for select * from emp where deptno=dno;
     end queryEmpList;
end mypackage01; 
在控制台，可以使用desc查看程序包的结构 
SQL> desc mypackage01
Element      Type      
------------ --------- 
EMPCURSOR    TYPE      
QUERYEMPLIST PROCEDURE  
在应用中访问包中的存储过程 
注意：需要带上包名。 
package test.oracle.demo;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import test.oracle.utils.JDBCUtils;
public class TestCursor {
    @Test
    public void testCursor() {
        // {call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{call MYPACKAGE01.queryEmpList(?,?)}";
        Connection conn = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            // 获取数据库连接
            conn = JDBCUtils.getConnection();
            // 通过连接获取statement
            call = conn.prepareCall(sql);
            // 对于in 参数 赋值
            call.setInt(1, 12);
            // 对于out参数 申明
            call.registerOutParameter(2, OracleTypes.CURSOR);
            // 执行调用
            call.execute();
            // 获取到指定部门的所有的员工的所有信息
            rs = ((OracleCallableStatement) call).getCursor(2);
            // 遍历结果集
            while (rs.next()) {
                // 获取员工的编号 姓名 薪水 职位
                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                double sal = rs.getDouble("sal");
                String job = rs.getString("job");
                System.out.println(empno + "\t" + ename + "\t" + sal + "\t" + job);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(conn, call, rs);
PL/SQL中的过程和函数（通常称为子程序）是PL/SQL块的一种特殊的类型，这种类型的子程序可以以编译的形式存放在数据库中，并为后续的程序块调用。
相同点： 完成特定功能的程序 
不同点：是否用return语句返回值。
举个例子：
create or replace procedure PrintStudents(p_staffName in xgj_test.username%type) as
 cursor c_testData is
 select t.sal, t.comm from xgj_test t where t.username = p_staffName;
				[code]
--[5]// Oracle Procedure and Function
-------------------------------------------------------------------------------------//
--过程(PROCEDURE)-------------------------------------------------...
				概述PL/SQL中的过程和函数（通常称为子程序）是PL/SQL块的一种特殊的类型，这种类型的子程序可以以编译的形式存放在数据库中，并为后续的程序块调用。相同点： 完成特定功能的程序 
不同点：是否用return语句返回值。举个例子：create or replace procedure PrintStudents(p_staffName in xgj_test.username%type) as
指存储在数据库中供所有用户程序调用的子程序叫存储过程/存储函数。
存储过程和存储函数的相同点：完成特定功能的程序
存储过程和存储函数的区别：是否用return语句返回值
二、创建和使用存储过程
用CREATE PROCEDURE命令建立存储过程和存储函数。
   create [or replace] PROCEDURE 过程名（参数列表） 
   PLSQL子程序体；
1、第一个存储过程
打印：Hello World
--第一个存储过程:打印Hello Wor
				文章目录一、存储过程二、存储函数三、in 和 out 类型参数四、存储过程和存储函数的区别
一、存储过程
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经
编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来
存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端
可以直接被调用，这一段pl/sql
				异常:This application has no explicit mapping for /error, so you are seeing this as a fallback.
					72310