存储过程实例1：idc_oemp.prws_external_contacts_search

来源：互联网发布：极品五笔2013优化版编辑：程序博客网时间：2024/05/01 17:40

存储过程的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

0 0