1、新建不带入参的存储过程,并调用存储过程
use testDataBases; -- 选择数据库
SELECT
'create procedure prod_test' ;
DROP PROCEDURE IF EXISTS prod_test;
DELIMITER //
CREATE PROCEDURE prod_test()
BEGIN
DECLARE variable VARCHAR(5);
SELECT VALUE INTO variable FROM tableName WHERE NAME = 'name';
IF (variable = '1')
THEN
******; -- 一系列sql,或者写别的存储过程也可以,注意数据库变化
END IF;
COMMIT;
END;
//
DELIMITER ;
call prod_test(); -- 调用新建的存储过程
DROP PROCEDURE IF EXISTS prod_test;
2、新建带入参的存储过程,并调用存储过程
use testDataBases; -- 选择数据库
SELECT
'create procedure prod_test_param' ;
DROP PROCEDURE IF EXISTS prod_test_param;
DELIMITER //
CREATE PROCEDURE prod_test_param
(
IN param1 VARCHAR(128);
IN param2 VARCHAR(128)
)
BEGIN
DECLARE variable1 VARCHAR(5);
DECLARE variable2 VARCHAR(5);
DECLARE variable3 VARCHAR(5);
DECLARE variable4 VARCHAR(5);
-- sql语句示例
SELECT VALUE INTO variable1 FROM tableName1 WHERE NAME = 'name1';
SELECT table.column1,table.column2 INTO variable1,variable2 FROM tableName2 table
where table.column1 = variable1 and table.column2 = param2
SET variable4=variable2;
IF(variable1='****')
THEN
SET variable4=variable3;
END IF;
IF (variable1 = '*****')
THEN
UPDATE tableName3 SET VALUE=variable4 WHERE NAME = 'name3';
END IF;
COMMIT;
END;
//
DELIMITER ;
call prod_test_param(param1,param2);
DROP PROCEDURE IF EXISTS prod_test_param;