存储过程实例1：idc_oemp.prws_external_contacts_search

最新推荐文章于 2023-12-11 22:44:31 发布

原创最新推荐文章于 2023-12-11 22:44:31 发布 · 599 阅读

0 ·

CC 4.0 BY-SA版权

技术分析总结专栏收录该内容

132 篇文章

订阅专栏

本文介绍了一个用于检索外部联系人的存储过程实现细节。该过程可根据不同条件进行搜索，并返回相应的记录总数及具体数据。

存储过程的OUT类型的参数是需要返回的，值是可变的，存储过程的输出字段是存储过程中SQL查询的结果集。

参数：

IN in_type TINYINT,
IN in_value VARCHAR(32),
IN in_pageindex INT,
IN in_pagesize INT,
OUT out_totalcount INT,
OUT out_return INT,
OUT out_msg VARCHAR(128)

sp:BEGIN

/************************************
-- Author: QinJinBo
-- Create date: 2016-12-22
-- Description: 检索外部联系人
**********************************
*/
-- 查询类型：1-根据姓名，2-根据公司，3-根据电话，4-根据职责，5-检索全部
DECLARE v_beginpage_index INT;

IF in_pageindex = 0 THEN
SET v_beginpage_index = 0;
ELSEIF in_pageindex > 0 THEN
SET v_beginpage_index = in_pagesize * (in_pageindex - 1);
END IF;

SET in_value = CONCAT('%',in_value,'%');

IF in_type = 1 THEN
SELECT COUNT(*)
INTO out_totalcount
FROM idc_oemp.external_contacts
WHERE `name` LIKE in_value;

if out_totalcount = 0 THEN
SET out_return = - 1,out_msg = '没有相关数据';
leave sp;
end if;

SELECT a.`id`,
a.`name`,
a.`sex`,
a.`companyname`,
a.`post`,
a.`phone`,
a.`email`,
a.`mark`,
a.`createtime`,
aa.dutyname duty
FROM idc_oemp.external_contacts a
LEFT JOIN (SELECT b.externalcontactsid,
GROUP_CONCAT(c.`name`) dutyname
FROM idc_oemp.ext_contacts_duty_map b
LEFT JOIN idc_oemp.duty_config c ON b.dutyid = c.id
GROUP BY b.externalcontactsid
) aa ON a.id = aa.externalcontactsid
WHERE a.`name` LIKE in_value
ORDER BY a.createtime DESC LIMIT v_beginpage_index,in_pagesize;
ELSEIF in_type = 2 THEN
SELECT COUNT(*)
INTO out_totalcount
FROM idc_oemp.external_contacts
WHERE `companyname` LIKE in_value;

if out_totalcount = 0 THEN
SET out_return = - 1,out_msg = '没有相关数据';
leave sp;
end if;

SELECT a.`id`,
a.`name`,
a.`sex`,
a.`companyname`,
a.`post`,
a.`phone`,
a.`email`,
a.`mark`,
a.`createtime`,
aa.dutyname duty
FROM idc_oemp.external_contacts a
LEFT JOIN (SELECT b.externalcontactsid,
GROUP_CONCAT(c.`name`) dutyname
FROM idc_oemp.ext_contacts_duty_map b
LEFT JOIN idc_oemp.duty_config c ON b.dutyid = c.id
GROUP BY b.externalcontactsid
) aa ON a.id = aa.externalcontactsid
WHERE a.`companyname` LIKE in_value
ORDER BY a.createtime DESC LIMIT v_beginpage_index,in_pagesize;
ELSEIF in_type = 3 THEN
SELECT COUNT(*)
INTO out_totalcount
FROM idc_oemp.external_contacts
WHERE `phone` LIKE in_value;

if out_totalcount = 0 THEN
SET out_return = - 1,out_msg = '没有相关数据';
leave sp;
end if;

SELECT a.`id`,
a.`name`,
a.`sex`,
a.`companyname`,
a.`post`,
a.`phone`,
a.`email`,
a.`mark`,
a.`createtime`,
aa.dutyname duty
FROM idc_oemp.external_contacts a LEFT JOIN
(SELECT b.externalcontactsid,
GROUP_CONCAT(c.`name`) dutyname
FROM idc_oemp.ext_contacts_duty_map b
LEFT JOIN idc_oemp.duty_config c ON b.dutyid = c.id
GROUP BY b.externalcontactsid
) aa
on a.id = aa.externalcontactsid where a.`phone` LIKE in_value
ORDER BY a.createtime DESC LIMIT v_beginpage_index,in_pagesize;
ELSEIF in_type = 4 THEN
SELECT COUNT(*)
INTO out_totalcount
FROM idc_oemp.external_contacts a LEFT JOIN
(SELECT b.externalcontactsid,
GROUP_CONCAT(c.`name`) dutyname
FROM idc_oemp.ext_contacts_duty_map b
LEFT JOIN idc_oemp.duty_config c ON b.dutyid = c.id
GROUP BY b.externalcontactsid
) aa
on a.id = aa.externalcontactsid where aa.dutyname LIKE in_value;

if out_totalcount = 0 THEN
SET out_return = - 1,out_msg = '没有相关数据';
leave sp;
end if;

SELECT a.`id`,
a.`name`,
a.`sex`,
a.`companyname`,
a.`post`,
a.`phone`,
a.`email`,
a.`mark`,
a.`createtime`,
aa.dutyname duty
FROM idc_oemp.external_contacts a LEFT JOIN
(SELECT b.externalcontactsid,
GROUP_CONCAT(c.`name`) dutyname
FROM idc_oemp.ext_contacts_duty_map b
LEFT JOIN idc_oemp.duty_config c ON b.dutyid = c.id
GROUP BY b.externalcontactsid
) aa
on a.id = aa.externalcontactsid where aa.dutyname LIKE in_value
ORDER BY a.createtime DESC LIMIT v_beginpage_index,in_pagesize;
ELSEIF in_type = 5 THEN
SELECT COUNT(*)
INTO out_totalcount
FROM idc_oemp.external_contacts;

if out_totalcount = 0 THEN
SET out_return = - 1,out_msg = '没有相关数据';
leave sp;
end if;

SELECT a.`id`,
a.`name`,
a.`sex`,
a.`companyname`,
a.`post`,
a.`phone`,
a.`email`,
a.`mark`,
a.`createtime`,
aa.dutyname duty
FROM idc_oemp.external_contacts a LEFT JOIN
(SELECT b.externalcontactsid,
GROUP_CONCAT(c.`name`) dutyname
FROM idc_oemp.ext_contacts_duty_map b
LEFT JOIN idc_oemp.duty_config c ON b.dutyid = c.id
GROUP BY b.externalcontactsid
) aa
on a.id = aa.externalcontactsid
ORDER BY a.createtime DESC LIMIT v_beginpage_index,in_pagesize;
END IF;

SET out_return = - 1,out_msg = '没有相关数据';
END sp