查詢以使用PROCEDURE驗證IMEI大小

如何在 PROCEDURE 中使用 SQL 查詢驗證 IMEI，以便每次呼叫它時 - 它首先驗證 IMEI，然后驗證其他步驟。查詢構建到 .csv 檔案第 1 步 - 驗證 IMEI 大小（應該是 15 個數字） - 如果 IMEI 大小為 15 - 然后進入下一步第 2 步 - 它會在表中創建一個新條目 - 如果沒有現有表中的 IMEI 相同

INSERT INTO "master_packs".list("serialNumber","assetName") 
VALUES(("IMEI" :: bigint) , "battery_pack_number") 
ON CONFLICT DO NOTHING;

（我用過這個查詢） STEP 3 - 如果 IMEI 已經在現有表中，它應該檢查資產名稱并更新與 IMEI 對應的資產名稱。

update "master_packs".list 
  SET "assetName" = battery_pack_number 
where "serialNumber" = "IMEI";

第 4 步 - 如果與 IMEI 對應的資產名稱不是他們的，那么它應該 - 將 IMEI 復制到資產名稱。

update "master_packs".list 
  SET "assetName" = "IMEI" 
where battery_pack_number = "assetName";

我需要一個程式中的所有步驟 - 請指導解決這個問題的正確方法。

CREATE or REPLACE PROCEDURE "master_packs".procCheckUpdateAdd1("IMEI" text, "battery_pack_number" text) 
language 'plpgsql' AS
$BODY$
declare
retval text;
begin

    --luhn_verify("IMEI"::text) is true and length("serialNumber"::int8) = 15;
    
    if length("IMEI") 
--- on conflict for imei 
--- update or new or put imei assetname - if then else
    --"master_packs".list("IMEI"::int8) is true and length("IMEI"::text) = 15;
    -- INSERT INTO "master_packs".list("serialNumber","assetName") VALUES(("IMEI" :: bigint) , "battery_pack_number")
     --INSERT INTO "master_packs".list("serialNumber","assetName","packType","barcode","ownopsNumber") VALUES(CAST ("IMEI" AS bigint) ,"battery_pack_number") 
    --ON CONFLICT DO NOTHING;
    -- update "master_packs".list SET "assetName" = battery_pack_number where "serialNumber" = "IMEI";
    -- update list SET "assetName" = "IMEI" where battery_pack_number = "assetName";
end;
$BODY$;

SELECT * 
FROM "master_packs".list 
ORDER BY "serialNumber" ASC; 

uj5u.com熱心網友回復：

創建或替換程式 "master_packs".procCheckUpdateAdd1("IMEI" text, "battery_pack_number" text) 語言 'plpgsql' AS $BODY$ 宣告 retval 文本；開始

--luhn_verify("IMEI"::text) is true and length("serialNumber"::int8) = 15;

if (length ("master_packs".list(IMEI) = 15)) 
then 
update list SET "assetName" = "IMEI" where battery_pack_number = "assetName"; 

elsif ("master_packs".list(IMEI) != "serialNumber")
then 
INSERT INTO "master_packs".list("serialNumber","assetName") VALUES(("IMEI" :: bigint) , "battery_pack_number");

elsif ("master_packs".list(IMEI) = "serialNumber" && "master_packs".list(battery_pack_number) != "assetName" )
then
update "master_packs".list SET "assetName" = battery_pack_number where "serialNumber" = "IMEI";

elsif ("master_packs".list(IMEI) = "serialNumber" && "master_packs".list("assetName") = "battery_pack_number" )
then 
update "master_packs".list SET "assetName" = "IMEI" where "battery_pack_number" = "assetName";

else
ON CONFLICT DO NOTHING;

end if;
 end;
$BODY$;

我一直在研究解決方案 - 但附近幾乎沒有錯誤 - 不等于操作員。請指導。

uj5u.com熱心網友回復：

我無法確定錯誤。我現在已經添加了評論。

創建或替換程式 "master_packs".procCheckUpdateAdd1("IMEI" text, "battery_pack_number" text) 語言 'plpgsql' AS $BODY$ 宣告 retval 文本；開始

if length ("master_packs".list(IMEI) = 15) then             --check IMEI size
        if ("master_packs".list(IMEI) != "serialNumber")  --if ime size is 15 - create new imei (NEW)
        then 
        INSERT INTO "master_packs".list("serialNumber","assetName") VALUES(("IMEI" :: bigint) , "battery_pack_number");

        elsif ("master_packs".list(IMEI) = "serialNumber" AND "master_packs".list(battery_pack_number) != "assetName" )   --update assetname - if imei is already there
        then
        update "master_packs".list SET "assetName" = battery_pack_number where "serialNumber" = "IMEI";

        elsif ("master_packs".list(IMEI) = "serialNumber" AND "master_packs".list("assetName") = "battery_pack_number" ) -- if assetname = battery pack number (or no assetname) =imei 
        then 
        update "master_packs".list SET "assetName" = "IMEI" where "battery_pack_number" = "assetName";  
        end if;
else
    update "master_pack".list(IMEI) = concat('IMEI','ERROR'); -- concatinate with ERROR to show that IMEI = 15 numbers
        
end if;
 end;
$BODY$;

我現在更新了評論。

標籤：sql PostgreSQL plpgsql

上一篇：在嵌套回圈中呼叫sql中的函式

下一篇：如何將我的AuroraSQL資料庫配置為可使用pgAdmin訪問并修復超時過期錯誤？