Mysql中新建和调用存储过程

DROP PROCEDURE IF EXISTS update_address_byid;

CREATE PROCEDURE update_address_byid
(
    id_param INT,
    username_param VARCHAR(50),
    name_param VARCHAR(50),
    sex_param VARCHAR(10),
    mobile_param VARCHAR(20),
    email_param VARCHAR(50),
    qq_param VARCHAR(20),
    company_param VARCHAR(100),
    address_param VARCHAR(100),
    postcode_param VARCHAR(10)
)
BEGIN
    DECLARE sql_error TINYINT DEFAULT FALSE;

    DECLARE CONTINUE HANDLER for SQLEXCEPTION
        set sql_error=TRUE;

    START TRANSACTION;

    UPDATE address
    SET
        username = username_param,
        `name` = name_param,
        sex = sex_param,
        mobile = mobile_param,
        email = email_param,
        qq = qq_param,
        company = company_param,
        address = address_param,
        postcode = postcode_param
    where id = id_param;

    if sql_error = FALSE THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END;

调用：

call update_address_byid(9, 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test');

 

 

 

转载于:https://www.cnblogs.com/davidgu/archive/2013/02/28/2936871.html