存储过程的常见结构

Oracle:

create or replace procedure get_needQuery_Balance(
v_num in number,
v_result out number
) 
is
--declare veriables
--declare cursor
--begin 
begin
--transition 
--exception
end

 

create or replace procedure get_needQuery_Balance(
v_num    IN  VARCHAR2,   --流水号最后一位除5，所得余数
v_result out VARCHAR2,
v_QueryPhone         OUT   VARCHAR2,
v_count  out VARCHAR2,
v_time  out VARCHAR2
) 
is
--定义变量
--l_count NUMBER;
phoneNo_Temp mobile_balanceQuery.Phonenum%TYPE;
type phoneNo is record ( phonenum  mobile_balanceQuery.Phonenum%TYPE);
type phoneNoTable is table of phoneNo;
ret_phoneNums phoneNoTable;
--定义游标
CURSOR v_gettedMbileNo is
       select t.phonenum from mobile_balanceQuery t
       where t.querycount = 0 
       AND MOD(TO_NUMBER(substr(t.phonenum,LENGTH(t.phonenum),1)),5) = v_num
       and rownum<=10;
--begin开启事务处理
begin
  --获取等待时间
  v_time := 100;
  select t.key into v_time from pt_table t
  where t.value1 = 'QueryNumTime';
  
  --打开游标
  open v_gettedMbileNo;
  fetch v_gettedMbileNo BULK COLLECT INTO ret_phoneNums;
  close v_gettedMbileNo;
  --判断取出的数量
  IF ret_phoneNums.count <= 0 THEN
        v_count := 0;
        RETURN;
  END IF;
  --组手机号
  v_count := 0;
  for i in 1..ret_phoneNums.count loop
      phoneNo_Temp:=ret_phoneNums(i).phonenum;
      --更新查询次数
      --select t.querycount into l_count from mobile_balanceQuery t 
             --where t.phonenum = phoneNo_Temp for update wait 2;
      --if(l_count = 0) then
        update mobile_balanceQuery
               set mobile_balanceQuery.Querycount = mobile_balanceQuery.Querycount + 1,
               mobile_balanceQuery.Start_Querytime = sysdate
               where mobile_balanceQuery.Phonenum = phoneNo_Temp;
     -- end if;
      v_QueryPhone := phoneNo_Temp     || ',' || v_QueryPhone ;
      v_count := v_count + 1;
  end loop;
  commit;
  v_result := '0';
  --异常捕获
  --结束
  EXCEPTION WHEN OTHERS THEN
    v_result := '-1';
    ROLLBACK;
end;

 MySQL

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_release`.`test11`$$

CREATE DEFINER=`encysys48`@`%` PROCEDURE `test11`(
IN    hf_serialid           VARCHAR(30),   #
OUT   v_ChargeMoney         VARCHAR(300) #
)
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
    begin
    DECLARE l_chargemoney           int(10);
    SELECT t.charge_money
                INTO l_chargemoney
                FROM Jp_Fullnote t
               WHERE t.hf_serialid = hf_serialid;
    SET v_ChargeMoney = CONCAT(IFNULL(l_chargemoney, ''), ',', v_ChargeMoney);
    insert into w_help(char_content) values (l_chargemoney);
    commit;
    end;
    END$$

DELIMITER ;

一般来说，

begin

定义变量

定义游标

定义异常

处理任务

end