以mysql举例
存储过程:
/** 存储过程1*/
drop procedure if exists clearVehicleData1 //
CREATE PROCEDURE clearVehicleData1(in flag varchar(20),in p1 varchar(20),in p2 varchar(20))begin
set @sql=concat("update newvehicle
set ",p1," =(select ",p1," from ",flag," where VIN = '",p2,"' AND ",p1,"!='' LIMIT 0, 1)
where VIN = '",p2,"'");
select @sql;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
end;
自定义函数:DROP FUNCTION IF EXISTS funIsNum //
CREATE FUNCTION funIsNum (Minfo VARCHAR(25)) RETURNS INT
BEGIN
DECLARE iResult INT DEFAULT 0;
SELECT Minfo REGEXP '^[A-Za-z0-9]+$' INTO iResult;
IF iResult = 1 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END //我想知道这两个有什么区别,分别哪种情况下使用,
自定函数里可以写sql语句吗mysql 存储过程 自定义函数数据库SQL
存储过程:
/** 存储过程1*/
drop procedure if exists clearVehicleData1 //
CREATE PROCEDURE clearVehicleData1(in flag varchar(20),in p1 varchar(20),in p2 varchar(20))begin
set @sql=concat("update newvehicle
set ",p1," =(select ",p1," from ",flag," where VIN = '",p2,"' AND ",p1,"!='' LIMIT 0, 1)
where VIN = '",p2,"'");
select @sql;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
end;
自定义函数:DROP FUNCTION IF EXISTS funIsNum //
CREATE FUNCTION funIsNum (Minfo VARCHAR(25)) RETURNS INT
BEGIN
DECLARE iResult INT DEFAULT 0;
SELECT Minfo REGEXP '^[A-Za-z0-9]+$' INTO iResult;
IF iResult = 1 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END //我想知道这两个有什么区别,分别哪种情况下使用,
自定函数里可以写sql语句吗mysql 存储过程 自定义函数数据库SQL
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货