CREATE DEFINER =`root` @`localhost` PROCEDURE`Proc_BarcodeScanDetail`（IN scancode TEXT）
BEGIN


IF（（从location_barcode中选择条形码，其中Barcode = scancode limit 1）= scancode）
THEN

SELECT distinct hg.SKU_code，SKU_NAME，COMPRESSION_FACTOR，REF_BATCH_CODE，EXPIRY_DATETIME，
hg.MRP，SOH / COMPRESSION_FACTOR为'SOH'，BARCODE
来自主sm内连接stock_master hg
ON sm.code = hg.code
内连接location_barcode lb
on hg.SKU_LOC_STOCK_NO = lb. sku_Loc_Stock_No
WHERE lb.Barcode = scancode
和SOH / COMPRESSION_FACTOR> 0 limit 1;

ELSEIF（（从ean_sku_link选择EAN_code，其中EAN_code =扫描码限制1）=扫描码）
THEN

SELECT hg.SKU_code，SKU_NAME，COMPRESSION_FACTOR，REF_BATCH_CODE，EXPIRY_DATETIME ，
hg.MRP，SOH / COMPRESSION_FACTOR为'SOH'，EAN_code
FROM Master sm inner join stock_master hg
ON sm.code = hg.code
inner join ean_sku ean
on hg.SKU_CODE = ean.SKU_CODE
WHERE ean.EAN_code = scancode
和SOH / COMPRESSION_FACTOR> 0 limit 1;
结束如果;
结束

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_BarcodeScanDetail`(IN scancode TEXT) BEGIN IF ((select Barcode from location_barcode where Barcode = scancode limit 1) = scancode) THEN SELECT distinct hg.SKU_code,SKU_NAME,COMPRESSION_FACTOR,REF_BATCH_CODE,EXPIRY_DATETIME, hg.MRP,SOH/COMPRESSION_FACTOR as 'SOH',BARCODE FROM Master sm inner join stock_master hg ON sm.code = hg.code inner join location_barcode lb on hg.SKU_LOC_STOCK_NO = lb.sku_Loc_Stock_No WHERE lb.Barcode = scancode and SOH/COMPRESSION_FACTOR >0 limit 1; ELSEIF ((select EAN_code from ean_sku_link where EAN_code = scancode limit 1)= scancode) THEN SELECT hg.SKU_code,SKU_NAME,COMPRESSION_FACTOR,REF_BATCH_CODE,EXPIRY_DATETIME, hg.MRP,SOH/COMPRESSION_FACTOR as 'SOH',EAN_code FROM Master sm inner join stock_master hg ON sm.code = hg.code inner join ean_sku ean on hg.SKU_CODE = ean.SKU_CODE WHERE ean.EAN_code = scancode and SOH/COMPRESSION_FACTOR >0 limit 1; end if; END

在此程序中; IF和ELSE语句中的子查询发现扫描的条形码是全局的（公司生成的）或本地的（个人生成的）。此外，如果扫描的条形码是本地的，则执行第一主查询，如果扫描的条形码是全局的，则执行第二主查询。我使用内部联接为Master，stock_master，location_barcode/ean_sku。

执行子查询需要5-8秒，主查询需要10-12秒，因此整个过程需要15-20秒。



table_name行数

location_barcode 378150

master 76573

stock_master 280001

ean_sku 18233

In this procedure; the subqueries in IF and ELSE statement finds that scanned barcode is global(company generated) or local(personally generated). Also if scanned barcode is local then executed 1st main query and if scanned barcode is global then execute 2nd main query. I used inner join for "Master" , "stock_master" ,"location_barcode"/"ean_sku".
It takes 5-8 seconds for executing subqueries and 10-12 seconds for main queries, hence 15-20 seconds for whole procedure.

table_name rows count
location_barcode 378150
master 76573
stock_master 280001
ean_sku 18233