delimiter //
create function getadminname(user varchar(32))
RETURNS CHAR(50)
BEGIN
declare type int(11) default 0;
declare countryid int(11) default 0;
set type =(select type from db1.user_account where user='user');
if exists (select name from db2.country_info where id=(select id from dbpushmail.id_info where user=user)
set countryid =(select channel from db1.id_info where user=user);
return (select Name from db1.name_info where type = type and countryid = countryid );
END
//这个语法,我Google了一下,没有找到比较全面的语法规则。
试着写了一下,发现在if exists 下面一行提示错误。请问具体要怎么改呢?
create function getadminname(user varchar(32))
RETURNS CHAR(50)
BEGIN
declare type int(11) default 0;
declare countryid int(11) default 0;
set type =(select type from db1.user_account where user='user');
if exists (select name from db2.country_info where id=(select id from dbpushmail.id_info where user=user)
set countryid =(select channel from db1.id_info where user=user);
return (select Name from db1.name_info where type = type and countryid = countryid );
END
//这个语法,我Google了一下,没有找到比较全面的语法规则。
试着写了一下,发现在if exists 下面一行提示错误。请问具体要怎么改呢?
把输入参数user改成var_user
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
create function getadminname(user varchar(32))
RETURNS CHAR(50)
BEGIN
declare type int(11) default 0;
declare b int default 0;
declare countryid int(11) default 0;
set type =(select type from db1.user_account where user='user');select count(*) into b from db2.country_info where id=(select id from dbpushmail.id_info where user=user)
if b=1 then
set countryid =(select channel from db1.id_info where user=user);
return (select Name from db1.name_info where type = type and countryid = countryid );
END
//
CREATE FUNCTION getadminname(USER VARCHAR(32))
RETURNS CHAR(50)
BEGIN
DECLARE TYPE INT(11) DEFAULT 0;
DECLARE countryid INT(11) DEFAULT 0;
SET TYPE =(SELECT TYPE FROM db1.user_account WHERE USER='user');
SELECT COUNT(*) INTO @AA FROM db2.country_info WHERE id=(SELECT id FROM dbpushmail.id_info WHERE USER=USER);
IF @AA>=1 THEN
SET countryid =(SELECT channel FROM db1.id_info WHERE USER=USER);
END IF;
RETURN (SELECT NAME FROM db1.name_info WHERE TYPE = TYPE AND countryid = countryid );
END
//建议将USER VARCHAR(32)中USER更名,与字段名重名?
a.`user`=b.`user`