创建一个包 test1


create or replace package  test1 
is
    PROCEDURE t1;
    function t2 return number;
end test1;

创建包的主体 test1，里面有个存储过程 t1，还有一个调用 t1


create or REPLACE package body test1 
is
    PROCEDURE t1 is
    begin
    DBMS_OUTPUT.PUT_LINE(sysdate);
    end;
    
    function t2 return number is
    begin
    return 1;
    end;
    
    begin
    TEST1.T1;
end test1;


执行方法

DECLARE
begin
TEST1.T1;
end;

首次执行，会输出两次




当我们再次执行时，只输出了一次。




当每个连接的新用户，执行存储过程里任意一个存储过程 或者 函数，包里的调用都会执行。

@NamedNativeQueries(
        {
                @NamedNativeQuery(
                        name = "FixedFeeOrderValidCalculate",
                        query = "insert into kh.tb_cust_tdyw" +
                                " (month,cust_no,user_id,broker_account,index_type,index_value,trade_date)" +
                                " select ?1 ,cr.customer_no ,cr.belong_userid ,cr.broker_account,?2,1,cr.rels_begin" +
                                "  from kh.tb_yxkh_cust_rel cr" +
                                " inner join meso.tb_customer cust on cust.customer_no = cr.customer_no" +
                                " where cr.rels_begin between ?3 || '01' and ?4 || '31' " +
                                " and cust.open_date between ?5 || '01' and ?6 || '31'"
                ),
                @NamedNativeQuery(
                        name = "RatioFeeOrderValidCalculate",
                        query = "insert into kh.tb_cust_tdyw" +
                                "      (month,cust_no,user_id,broker_account,index_type,index_value,trade_date)" +
                                "select t.month as month,t.cust_no,t.user_id,t.broker_account,?1," +
                                "  sum(nvl((select case " +
                                "    when not exists (select i.item_value from KH.TB_SDEPT_INDEX i " +
                                "    where i.branch_code = us.dept_id and i.item_id = ?2 and i.version = '2016') then " +
                                "    (select i.item_value from KH.TB_SDEPT_INDEX i " +
                                "    where i.branch_code = '9999' and i.item_id = ?3 and i.version = '2016') " +
                                "  else (select i.item_value from KH.TB_SDEPT_INDEX i" +
                                "   where i.branch_code = us.dept_id and i.item_id = ?4 and i.version = '2016') end" +
                                "  from dual) * nvl(t.index_value, 0), 0)),'' " +
                                " from KH.TB_CUST_TDYW t inner join KH.TB_USER_SNAP us" +
                                " on us.user_id = t.user_id and us.trademonth = ?5" +
                                " where t.month = ?6 and t.index_type = ?7" +
                                " group by t.month, t.cust_no, t.user_id, t.broker_account"
                ),



 @NamedNativeQuery(
                        name = "FixedFeeOrderValidCalculate",
                        query =

 "      select sp.trademonth, " +
                                "             sp.branch_code, " +
                                "             sp.auth_id, " +
                                "             sp.cust_no, " +
                                "             sp.sorder_id, " +
                                "             sp.pay_way, " +
                                "             sp.price_money, " +
                                "             sp.service_rate, " +
                                "             sp.prod_no, " +
                                "             sp.trade_day, " +
                                "             sp.user_id, " +
                                "             sp.begin_date, " +
                                "             sp.end_date, " +
                                "             nvl(decode(sp.charg_range, 0, am.avg_net_ofs_assets, 1, am.total_avg_asset), 0), " +
                                "             '1', " +
                                "             case " +
                                "               when num > 1 then  " +
                                "                round((case " +
                                "                        when tmp1.totsum / 3000 > 3 then " +
                                "                         sp.price_money / tmp1.totsum * 3 " +
                                "                        else " +
                                "                         sp.price_money / 3000 " +
                                "                      end), 2) " +
                                "               else " +
                                "                round((case " +
                                "                        when sp.price_money / 3000 > 3 then " +
                                "                         3 " +
                                "                        else " +
                                "                         sp.price_money / 3000 " +
                                "                      end), 2) " +
                                "             end, " +
                                "             sp.charg_range, " +
                                "             '1', " +
                                "             sysdate, " +
                                "             sysdate, " +
                                "             '1', " +
                                "             null, " +
                                "             'B' " +
                                "        from kh.tb_sproduct_sale sp " +
                                "       inner join (select sp.auth_id auth_id, " +
                                "                          sp.user_id user_id, " +
                                "                          sum(sp.price_money) totsum, " +
                                "                          count(1) as num " +
                                "                     from kh.tb_sproduct_sale sp " +
                                "                    where sp.pay_way = ?1 " +
                                "                      and sp.pay_status = ?2 " +
                                "                      and sp.del_flag = ?3 " +
                                "                      and sp.trademonth = ?4 " +
                                "                    group by sp.auth_id, sp.user_id) tmp1 " +
                                "          on tmp1.auth_id = sp.auth_id " +
                                "         and sp.user_id = tmp1.user_id " +
                                "        left join xc.tb_mt_cust_asset_gather am " +
                                "          on am.customer_no = sp.cust_no " +
                                "         and am.trademonth = ?4 " +
                                "       where sp.pay_way = ?1  " +
                                "         and sp.pay_status = ?2 " +
                                "         and sp.del_flag = ?3 " +
                                "         and sp.trademonth = ?4"
resultSetMapping = "returnorderValidCalc"
），

@SqlResultSetMappings(
        {
                @SqlResultSetMapping(
                        name = "returnorderValidCalc",
                        entities = {},
                        columns = {
                                @ColumnResult(name = "tradeMonth"),
                                @ColumnResult(name = "branchCode"),
                                @ColumnResult(name = "authID"),
                                @ColumnResult(name = "custNO"),
                                @ColumnResult(name = "orderID"),
                                @ColumnResult(name = "signType"),
                                @ColumnResult(name = "tollSum"),
                                @ColumnResult(name = "serviceFarex"),
                                @ColumnResult(name = "prodNO"),
                                @ColumnResult(name = "tradeDate"),
                                @ColumnResult(name = "userID"),
                                @ColumnResult(name = "startDate"),
                                @ColumnResult(name = "endDate"),
                                @ColumnResult(name = "asset"),
                                @ColumnResult(name = "tradeType"),
                                @ColumnResult(name = "validNum"),
                                @ColumnResult(name = "chargRange"),
                                @ColumnResult(name = "createBy"),
                                @ColumnResult(name = "createDT"),
                                @ColumnResult(name = "updateDT"),
                                @ColumnResult(name = "updateBy"),
                                @ColumnResult(name = "deleteBy"),
                                @ColumnResult(name = "actByType"),


                        }
                )
        }
)

    }

)

在entity的实体类的前面用 @NamedNativeQuery注解定义一整条存储过程的sql。

对于select型的sql语句 执行完后，select出的是一个实体表中的某几列。如果没有或者不想定义临时的entity去接收查询结果时，  可以用@SqlResultSetMappings 注解 定义几个列去接收select的查询结果

二者通过returnorderValidCalc 连接。

对用注解定义好的存储过程，用一个EntityManagerTransaction类去执行这些存储过程。

package com.foundersc.crm.brokerage.util;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;
import java.util.List;

/**
 * 获得支持事务操作的entityManager实体
 *
 * Created by zhongxuhui on 16/9/8.
 */
public class EntityManagerTransaction {

    private EntityManager entityManager;
    private EntityManagerFactory entityManagerFactory;

    public EntityManagerTransaction() {
        String config = "classpath*:spring/spring-jpa.xml";
        ApplicationContext ctx = new ClassPathXmlApplicationContext(config);
        entityManagerFactory = ctx.getBean(EntityManagerFactory.class);
        entityManager = entityManagerFactory.createEntityManager();
    }

    public int update (String methodName, String[] params) {
        entityManager.getTransaction().begin();
        Query query = entityManager.createNamedQuery(methodName);
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i + 1, params[i]);
        }
        int result = query.executeUpdate();
        entityManager.getTransaction().commit();
        close();
        return result;
    }

    public List<Object[]> query(String methodName, String[] params) {

        Query query = entityManager.createNamedQuery(methodName);
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i + 1, params[i]);
        }
        List<Object[]> list = query.getResultList();
        entityManager.close();
        return list;
    }

    private void close() {
        entityManager.close();
        entityManagerFactory.close();
    }
}


在  ServiceRepository 里面实例化一个EntityManagerTransaction类 

调用其执行sql的方法：



private static EntityManagerTransaction entityManagerTransaction = new EntityManagerTransaction();


public List getRequestList(String methodName, String[] params) {
        return  entityManagerTransaction.query(methodName,params);
       }

      public int SaveOrUpdateFromSelect(String methodName, String[] params){
          int result= entityManagerTransaction.update(methodName,params);
          if (0==result){
              log.info("no lines had been affected!");
          }
          else {
              log.info(result+" lines has benn affected!");
          }
          return result;
      }

然后在相应的serviceImpl里根据存储过程的名称调用即可：



public void FixedFeeOrderValidCalculate(String tradeMonth){
        String[] params = new String[4];
        params[0] = "1" ;//固定费用客户
        params[1] = "2" ;//已支付的
        params[2] = "0" ;//状态正常
        params[3] = tradeMonth;

        int result=serviceProductOrderValidNumRepository.SaveOrUpdateFromSelect("FixedFeeOrderValidCalculate",params);
 //FixedFeeOrderValidCalculate 是在@NamedNativeQuery注解里定义好的存储过程的名称
    }

public void RatioFeeOrderValidCalculate(String tradeMonth){
        String[] params = new String[4];
        params[0] = "2" ;//固定费用客户
        params[1] = "2" ;//已支付的
        params[2] = "0" ;//状态正常
        params[3] = tradeMonth;

        int result=serviceProductOrderValidNumRepository.SaveOrUpdateFromSelect("RatioFeeOrderValidCalculate",params);

    }//RatioFeeOrderValidCalculate 是entity前的注解里定义好的存储过程的sql

程序中需要直接调用存储过程的场景

第一种：调用带输入输出参数的存储过程
第二种：调用存储过程返回结果集
第三种：第一种+第二种（不讨论）

数据库环境准备

数据库：MySQL
创建表及初始化数据表

CREATE TABLE `order`  (
  `uuid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `number` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`uuid`) USING BTREE
) ；

INSERT INTO `order` VALUES ('d14f8eec-ec1a-11ea-98de-00ffc575', '20200901000001', '单据1', NULL);
INSERT INTO `order` VALUES ('d14f8eec-ec1a-11ea-98de-1234567', '20200901000003', '单据3', NULL);
INSERT INTO `order` VALUES ('d14f8eec-ec1a-11ea-98de-sdfafdd', '20200901000002', '单据2', NULL);


创建存储过程

CREATE PROCEDURE Get_Order_By_Uuid(
IN piUuid varchar(32),
OUT poNumber varchar(32),
OUT poName varchar(255),
OUT poErrMsg varchar(255)
) 
BEGIN
	DECLARE notfound INT DEFAULT 0;    #定义一个辅助变量用于判断
	DECLARE cur1 CURSOR FOR SELECT number,name FROM iponkan_dbcp.order where uuid = piUuid;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET notfound = 1;

	OPEN cur1;
	FETCH cur1 INTO poNumber,poName;

	if notfound = 1 then
		set poErrMsg = 'uuid='+ piUuid +'的单据信息不存在';
  end if;

	CLOSE cur1;
	
END;

CREATE PROCEDURE Get_ALL_Order()
BEGIN
	SELECT number,name FROM iponkan_dbcp.order;
END;

SpringBoot程序调用
情景一：调用带有输入输出参数的存储过程

/**
 * 调用存储过程返回参数-执行器
 * 
 * @author dongtangqiang
 */
@Component
public class ProcedureReturnParametersExecutor {

  @Autowired
  private JdbcTemplate jdbcTemplate;
  @Autowired
  private TransactionTemplate template;

  public boolean save() {
    String insertSql = "INSERT INTO `order`(`uuid`, `number`, `name`, `type`) VALUES ('d14f8eec-ec1a-11ea-98de-1234567', '20200901000003', '单据3', NULL)";
    return jdbcTemplate.update(insertSql) > 0;
  }

  public Order getByUuid(String uuid) {
    ProcedureReturnParametersTransactionCallback callback = new ProcedureReturnParametersTransactionCallback(
        uuid);
    return template.execute(callback);
  }

  class ProcedureReturnParametersTransactionCallback implements TransactionCallback<Order> {
    private String uuid;

    public ProcedureReturnParametersTransactionCallback(String uuid) {
      super();
      this.uuid = uuid;
    }

    @Override
    public Order doInTransaction(TransactionStatus transactionStatus) {
      return jdbcTemplate.execute(new CallableStatementCreator() {

        @Override
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
          String procedure = "{call Get_Order_By_Uuid(?,?,?,?)}";
          CallableStatement cs = con.prepareCall(procedure);
          cs.setString(1, uuid);
          cs.registerOutParameter(2, java.sql.Types.VARCHAR);
          cs.registerOutParameter(3, java.sql.Types.VARCHAR);
          cs.registerOutParameter(4, java.sql.Types.VARCHAR);
          return cs;
        }

      }, new CallableStatementCallback<Order>() {
        @Override
        public Order doInCallableStatement(CallableStatement cs)
            throws SQLException, DataAccessException {
          cs.execute();
          String number = cs.getString(2);
          String name = cs.getString(3);
          String msg = cs.getString(4);
          if (msg != null) {
            throw new RuntimeException(msg);
          }

          Order order = new Order();
          order.setNumber(number);
          order.setName(name);

          return order;
        }
      });
    }
  }

}

情景二：调用返回结果集合的存储过程
/**
 * 调用存储过程返回结果集-执行器
 * 
 * @author dongtangqiang
 */
@Component
public class ProcedureReturnListExecutor {
  @Autowired
  private JdbcTemplate jdbcTemplate;
  @Autowired
  private TransactionTemplate template;

  public List<Order> getAll() {
    ProcedureReturnListTransactionCallback callback = new ProcedureReturnListTransactionCallback();
    return template.execute(callback);
  }

  class ProcedureReturnListTransactionCallback implements TransactionCallback<List<Order>> {

    @Override
    public List<Order> doInTransaction(TransactionStatus transactionStatus) {
      return jdbcTemplate.execute(new CallableStatementCreator() {

        @Override
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
          String procedure = "{call Get_ALL_Order()}";
          CallableStatement cs = con.prepareCall(procedure);
          return cs;
        }

      }, new CallableStatementCallback<List<Order>>() {
        @Override
        public List<Order> doInCallableStatement(CallableStatement cs)
            throws SQLException, DataAccessException {
          ResultSet rs = cs.executeQuery();

          List<Order> list = new ArrayList<>();
          while (rs.next()) {
            String number = rs.getString(1);
            String name = rs.getString(2);

            Order order = new Order();
            order.setNumber(number);
            order.setName(name);
            list.add(order);
          }

          return list;
        }
      });
    }
  }
}


测试

结果：达到预期


/**
 * @author dongtangqiang
 */
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class TestProcedure {

  @Autowired
  private ProcedureReturnParametersExecutor procedure1;
  @Autowired
  private ProcedureReturnListExecutor procedure2;

  @Test
  public void test() {
    procedure1.save();
  }

  @Test
  public void testGetByUuid() {
    Order order = procedure1.getByUuid("d14f8eec-ec1a-11ea-98de-1234567");
    assertNotNull(order);
    assertEquals(order.getNumber(), "20200901000003");
  }

  @Test
  public void testGetAll() {
    List<Order> orderList = procedure2.getAll();
    assertEquals(3, orderList.size());
  }

}


完整代码
Github::iponkan-dbcp

thinkphp貌似不能直接调用存储过程，要用原生的方法。 

代码如下：



$userInfo_proc = "call proc_get_userinfo_details(".$data['userid'].",''); ";//传了个参数
$res = mysql_query($userInfo_proc);//执行存储过程
$res = mysql_fetch_assoc($res);