· 6 years ago · Jun 30, 2019, 01:38 AM
1# req
2
3
4+--------------------------------------------------+
5| req |
6+--------------------------------------------------+
7| req_id | order_placed | contact_id | seq_records |
8+--------+--------------+------------+-------------+
9| 1 | null | 1000 | null |
10+--------+--------------+------------+-------------+
11| 2 | null | 1002 | null |
12+--------+--------------+------------+-------------+
13| 3 | null | 1003 | null |
14+--------+--------------+------------+-------------+
15
16
17
18+--------------------------------------------------------------------+
19| contact |
20+--------------------------------------------------------------------+
21| contact_id | first_name | order_placed | company_name | company_id |
22+------------+------------+--------------+--------------+------------+
23| 1000 | dirt | null | Asus | 12 |
24+------------+------------+--------------+--------------+------------+
25| 1002 | dammy | null | Asus | 12 |
26+------------+------------+--------------+--------------+------------+
27| 1003 | samii | null | Asus | 12 |
28+------------+------------+--------------+--------------+------------+
29| 1004 | xenon | null | Lenova | 1 |
30+------------+------------+--------------+--------------+------------+
31
32
33CREATE TABLE `req` (
34 `req_id` bigint(20) NOT NULL,
35 `order_placed` char(1) COLLATE utf8_bin DEFAULT NULL,
36 `contact_id` bigint(20) DEFAULT NULL,
37 `seq_records` bigint(2) DEFAULT NULL,
38 PRIMARY KEY (`req_id`),
39 KEY `contact_id` (`contact_id`),
40 CONSTRAINT `req_ibfk_10` FOREIGN KEY (`contact_id`) REFERENCES
41 `contact` (`contact_id`)
42)
43/*!40101 SET character_set_client = @saved_cs_client */;
44
45# contact
46
47CREATE TABLE contact (
48 contact_id bigint(20) NOT NULL,
49 `first_name` varchar(100) COLLATE utf8_bin NOT NULL,
50 `company_name` varchar(100) COLLATE utf8_bin DEFAULT NULL,
51 `company_id` varchar(100) COLLATE utf8_bin DEFAULT NULL,
52 `order_placed` char(1) COLLATE utf8_bin DEFAULT NULL,
53 PRIMARY KEY (`contact_id`),
54 KEY `index_name` (`contact_id`),
55)
56
57DELIMITER $$
58DROP procedure IF EXISTS `recordsequence` $$
59CREATE procedure `recordsequence` ()
60BEGIN
61
62declare companyname varchar(250);
63declare recordcount integer default 0;
64declare duplcount integer default 0;
65DECLARE vfinished INTEGER DEFAULT 0;
66declare icount int default 0;
67DEClARE records_cursor CURSOR FOR
68select c.company_name,count(c.company_name),r.opr_id from contact c, request r where c.contact_id=r.contact_id and r.order_placed is null group by c.company_name;
69-- declare NOT FOUND handler
70DECLARE CONTINUE HANDLER
71FOR NOT FOUND SET vfinished = 1;
72OPEN records_cursor;
73transfer_records: LOOP
74FETCH records_cursor INTO companyname,duplcount;
75IF vfinished = 1 THEN
76LEAVE transfer_records;
77END IF;
78
79begin
80set recordcount := duplcount;
81set icount := 1;
82DEClARE records_cursor1 CURSOR FOR
83select c.contact_id,c.company_name from contact c, request r where c.company_name = companyname and c.contact_id=r.contact_id and r.order_placed is null group by c.company_name;
84-- declare NOT FOUND handler
85DECLARE CONTINUE HANDLER
86FOR NOT FOUND SET vfinished = 1;
87OPEN records_cursor1;
88transfer_records1: LOOP
89FETCH records_cursor INTO contactid,companyname;
90IF vfinished = 1 THEN
91LEAVE transfer_records1;
92END IF;
93
94begin
95
96UPDATE contact set reorder_sequence = icount where contact_id = contactid;
97set icount := icount + 1;
98end;
99
100END LOOP transfer_records1;
101
102CLOSE records_cursor1;
103
104if(recordcount == icount) THEN
105
106select concat('company_name Updated successfully', companyname);
107
108else
109select concat('company_name count mismatches please check', companyname);
110end if
111
112end
113
114END LOOP transfer_records;
115
116CLOSE records_cursor;
117
118End$$
119DELIMITER ;
120
121Eg: contact table
122
123+--------------------------------------------------------+
124| contact |
125+--------------------------------------------------------+
126| order_placed | contact_id | company_name | seq_records |
127+--------------+------------+--------------+-------------+
128| null | 1002 | Asus | 1 |
129+--------------+------------+--------------+-------------+
130| null | 1003 | Asus | 2 |
131+--------------+------------+--------------+-------------+
132| null | 1005 | Asus | 3 |
133+--------------+------------+--------------+-------------+
134| null | 1006 | Lenova | 1 |
135+--------------+------------+--------------+-------------+