MySQL存储过程例子

CREATE DEFINER = 'root'@'%' PROCEDURE `PROC_ROLE_CREATE`(IN i_opr_id SMALLINT, IN i_passport VARCHAR(32), IN i_rolename VARCHAR(32), IN i_sex VARCHAR(1), IN i_ip BIGINT, IN i_recommend INTEGER(11), IN i_type VARCHAR(1), IN i_hair INTEGER(11), IN i_face INTEGER(11), IN i_clothes INTEGER(11), OUT o_state INTEGER(11), OUT o_id INTEGER(11))
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
quit:
BEGIN
	SET o_state = 1;
    IF(i_opr_id IS NULL OR i_passport IS NULL OR i_rolename IS NULL 
    OR i_sex IS NULL OR i_type IS NULL OR i_hair IS NULL
    OR i_face IS NULL OR i_clothes IS NULL)THEN
    	SET o_state = 1502;
        LEAVE quit;
    END IF;
    SET @dn = NOW();
    SET @likeName  = CONCAT('0',i_rolename);
    ##init role
    SET @uniqueState=0;	
    BEGIN
        DECLARE CONTINUE HANDLER FOR SQLSTATE'23000' SET @uniqueState=1;
    	INSERT INTO player_roles(s_rolename,s_likename,n_opr_id,s_passport,s_sex,s_type,s_state,d_create,n_ip,n_recommend)
    	VALUES(i_rolename,@likeName,i_opr_id,i_passport,i_sex,i_type,'1',@dn,i_ip,i_recommend);
        IF(@uniqueState=1) THEN
    		SET o_state = 6004;
    		LEAVE quit;
        ELSEIF(ROW_COUNT()<>1)THEN
    		SET o_state = 1504;
            LEAVE quit;
    	END IF;
        SELECT LAST_INSERT_ID() INTO  @oid;
    END;
    IF(@oid IS NULL) THEN
    	SET o_state = 1501;
        LEAVE quit;
    END IF;
    #init property
    INSERT INTO player_property(n_roleid,s_sex,s_vocation,n_rank,n_vcurrency,n_grade,n_merit,n_wisdom,n_wisdom_exp,n_agile,n_agile_exp,n_comm,n_comm_exp,n_health,n_health_exp,
    n_voc_level,n_voc_exp,n_blood,n_energy,n_luck,n_bad,n_body_state,n_online_time,d_last_online,s_online,n_luck_reset)
	VALUES(@oid,i_sex,'0',0,1000,0,0,0,0,0,0,0,0,0,0,0,0,100,100,0,0,0,0,@dn,'1',1);
    IF(ROW_COUNT()<>1)THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
    ##init dress
	INSERT INTO dress_role_current(n_roleid,n_doid,n_cid)	
	VALUES(@oid,i_hair,0),(@oid,i_face,0),(@oid,i_clothes,0);
    IF(ROW_COUNT()<>3)THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
    ##history dress
    ###init role dress
    INSERT INTO dress_history(n_roleid,s_type,d_time)
	VALUES(@oid,'0',@dn);
    IF(ROW_COUNT()<>1) THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
    SELECT LAST_INSERT_ID() INTO  @opid;
    INSERT INTO dress_history_detail(n_pid,n_doid)
	VALUES(@opid,i_hair),(@opid,i_face),(@opid,i_clothes);
    IF(ROW_COUNT()<>3)THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
    INSERT INTO fight_player_cards(n_roleid,n_card)
    VALUES(@oid,31),(@oid,32),(@oid,33);
    IF(ROW_COUNT()<>3)THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
	INSERT player_config(n_roleid,n_pack_cap,n_trade_cap,s_task,n_buy_cap,n_card_cap)
    VALUES(@oid,40,2,'0',2,2);
    IF(ROW_COUNT()<>1)THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
	INSERT INTO fight_info(n_roleid,n_points,n_total,n_succ,n_fail,n_cont,n_fight)
    VALUES(@oid,0,0,0,0,0,0);
    IF(ROW_COUNT()<>1)THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
    ##add 
    INSERT INTO  servant_info(n_roleid,s_master,n_enslaveds,n_tenslaves,n_enslaves)
	VALUES(@oid,'0',0,0,0);
    IF(ROW_COUNT()<>1)THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
    INSERT INTO npc_favorability(n_roleid,n_npcid,n_favor) 
    (SELECT @oid,n_npcid,0 FROM npc_define);
    IF(ROW_COUNT()<1)THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
    ##
    INSERT INTO letter_info(n_send_roleid,n_recieve_roleid,s_type,s_type2,s_extend,s_affix,d_send) 
    VALUES(NULL,@oid,'1','97',NULL,'0',@dn);
    IF(ROW_COUNT()<>1) THEN
    	SET o_state = 1504;
    	LEAVE quit;
    END IF;
    ##tmp 
    INSERT INTO acc_balance(n_roleid,n_balance,n_token,n_total_imprest,n_total_present,d_create) 
    VALUES(@oid,1000,0,1000,0,@dn);
   	IF(ROW_COUNT()<>1)THEN
    	SET o_state = 1504;
        LEAVE quit;
    END IF;
    SET o_state = 0;
    SET o_id =@oid;
END;