我的问题是这样的:在数据库中有两张表:table A,table B,
table A 中有字段_ID,channelID,_createdBy,_status
table B 中有字段_ID,userName,_statusA中的_createdBy是B中_ID,B中的_ID和userName是一一对应的,现在有这样一个问题,就是要在数据库中写一个函数getUserNameByID,通过A中查询的_createdBy来显示对应的userName,例如:A中有数据:
001, English, 12345, ACTIVEB中有数据:
12345, lgccaa, ACTIVE用select channelID, getUserNameByID(_createdBy) from A查询显示的结果为:
English, lgccaa数据库用的是MySQL5.1,我对数据库不怎么熟悉,请大家帮帮忙了。
table A 中有字段_ID,channelID,_createdBy,_status
table B 中有字段_ID,userName,_statusA中的_createdBy是B中_ID,B中的_ID和userName是一一对应的,现在有这样一个问题,就是要在数据库中写一个函数getUserNameByID,通过A中查询的_createdBy来显示对应的userName,例如:A中有数据:
001, English, 12345, ACTIVEB中有数据:
12345, lgccaa, ACTIVE用select channelID, getUserNameByID(_createdBy) from A查询显示的结果为:
English, lgccaa数据库用的是MySQL5.1,我对数据库不怎么熟悉,请大家帮帮忙了。
(
i_id int
)
returns varchar(100)
begin
declare v_name varchar(100);
select userName into v_name from table_B where _ID=i_id;
return v_name;
end;
+-------+----------+---------+
| _ID | userName | _status |
+-------+----------+---------+
| 12345 | lgccaa | ACTIVE |
| 8888 | aaaa | ACTIVE |
+-------+----------+---------+
2 rows in set (0.06 sec)mysql> select getUserNameByID(100);
+----------------------+
| getUserNameByID(100) |
+----------------------+
| NULL |
+----------------------+
1 row in set, 1 warning (0.00 sec)mysql> select getUserNameByID(12345);
+------------------------+
| getUserNameByID(12345) |
+------------------------+
| lgccaa |
+------------------------+
1 row in set (0.02 sec)mysql>
+------+-----------+------------+---------+
| _ID | channelID | _createdBy | _status |
+------+-----------+------------+---------+
| 1 | English | 12345 | ACTIVE |
| 2 | Chinese | 8888 | ACTIVE |
+------+-----------+------------+---------+
2 rows in set (0.00 sec)mysql> select * from table_B;
+-------+----------+---------+
| _ID | userName | _status |
+-------+----------+---------+
| 12345 | lgccaa | ACTIVE |
| 8888 | aaaa | ACTIVE |
+-------+----------+---------+
2 rows in set (0.00 sec)mysql> select channelID, getUserNameByID(_createdBy) from table_A;
+-----------+-----------------------------+
| channelID | getUserNameByID(_createdBy) |
+-----------+-----------------------------+
| English | lgccaa |
| Chinese | aaaa |
+-----------+-----------------------------+
2 rows in set (0.00 sec)mysql>
-------------------------------
不怕,会进行隐性转换的
+-----+-----------+----------+--------+
| id | channelID | createBy | status |
+-----+-----------+----------+--------+
| 001 | English | 123456 | ACTIVE |
+-----+-----------+----------+--------+
1 row in set (0.00 sec)mysql> select * from b;
+--------+----------+--------+
| id | username | status |
+--------+----------+--------+
| 123456 | lgcca | Active |
+--------+----------+--------+
1 row in set (0.00 sec)mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)mysql> select @@log_bin_trust_function_creators;
+-----------------------------------+
| @@log_bin_trust_function_creators |
+-----------------------------------+
| 1 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> delimiter //
mysql> drop function if exists getUserNameByID//
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create function getUserNameByID(createBy int)
-> returns varchar(10)
-> begin
-> declare v_name varchar(10);
-> select username into v_name from b,a where b.id =a.createBy and a.createB
y=createBy;
-> return v_name ;
-> end ;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> select channelId,getUserNameByID('123456') from a //
+-----------+---------------------------+
| channelId | getUserNameByID('123456') |
+-----------+---------------------------+
| English | lgcca |
+-----------+---------------------------+
1 row in set (0.00 sec)create function getUserNameByID(createBy int)
returns varchar(10)
begin
declare v_name varchar(10);
select username into v_name from b,a where b.id =a.createBy and a.createBy=createBy;
return v_name ;
end ;
//
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create function getUserNameByID()
-> returns varchar(10)
-> begin
-> declare v_name varchar(10);
-> select username into v_name from b,a where b.id =a.createBy;
-> return v_name ;
-> end ;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> select getUserNameByID();
-> //
+-------------------+
| getUserNameByID() |
+-------------------+
| lgcca |
+-------------------+
1 row in set (0.00 sec)