我有一个函数：
// Search user who have zero city but not zero country
list($user_id, $country_id) = $site_db->query("
select user_id, country_id
from users
WHERE city_id < 1 AND country_id > 1
LIMIT 1");
// If user found, get random city from user country
if ($user_id > 0)
{
$random_city_query = $site_db->query("
select id
from cities
WHERE country_id = $country_id
order by RAND()
limit 1");
// Update user city
$update_city = $site_db->query("
update users
set city_id = $random_city_query
WHERE user_id = $user_id");
}
我尝试在这个函数的基础上创建MYSQL存储过程。
我已经尝试了一些变体，但它不起作用：（
BEGIN
update users u
set u.city_id = (select ci.id from cities ci WHERE ci.country_id = u.country_id order by RAND() limit 1)
WHERE user_id = (select u.user_id WHERE u.city_id < 1 AND u.country_id > 1 limit 1)
END
有人能帮我吗？
所以，没有人帮我：(我又花了2个小时，自己创建程序：）
BEGIN
SELECT @user_id := user_id from users WHERE city_id < 1 AND country_id > 1 LIMIT 1;
IF NOT @user_id IS NULL
THEN
SELECT @user_country_id := country_id from users WHERE user_id = @user_id;
SELECT @city_id := id from cities WHERE country_id = @user_country_id order by RAND() limit 1;
update users set city_id = @city_id WHERE user_id = @user_id;
END IF;
END
创建存储过程的方法：
Create Procedure sp_ADD_RESPONSABLE_EXTRANET_CLIENT
(
@ParLngId int output
)
as
Begin
if not exists (Select ParLngId from T_Param where ParStrIndex = 'RES' and ParStrP2 = 'Web')
Begin
INSERT INTO T_Param values('RES','¤ExtranetClient', 'ECli', 'Web', 1, 1, Null, Null, 'non', 'ExtranetClient', 'ExtranetClient', 25032, Null, 'informatique.interne@septeo.fr', 'Extranet-Client', Null, 27, Null, Null, Null, Null, Null, Null, Null, Null, 1, Null, Null, 0 )
SET @ParLngId = @@IDENTITY
End
Else
Begin
SET @ParLngId = (Select top 1 ParLngId from T_Param where ParStrNom = 'Extranet Client')
Return @ParLngId
End
End