CREATE PROCEDURE UNIFY_METRO_DATA (
UNIFY_DATE DATE DEFAULT '2016-03-03'
)
BEGIN
VAR_METRO_RAW_TABLE =
SELECT
CASE WHEN ALIASCARDNO LIKE '0000%' THEN SUBSTRING(ALIASCARDNO, 5) ELSE ALIASCARDNO END AS CARD_NO,
CARDCLASS AS CARD_TYPE
FROM
"CC_METRO_EXT"
WHERE
TO_DATE(GETOFFDATETIME) = :UNIFY_DATE
AND
ONSTATIONID != STATIONID;
VAR_METRO_RESULT =
SELECT
CARD_NO,
COUNT(CARD_NO) AS METRO_COUNT,
MAX(CARD_TYPE) AS CARD_TYPE
FROM :VAR_METRO_RAW_TABLE
GROUP BY CARD_NO;
VAR_FINAL_RESULT =
SELECT
T1.CARD_NO,
T1.METRO_COUNT,
T1.CARD_TYPE,
T2.IDCARDNO
FROM :VAR_METRO_RESULT AS T1
LEFT JOIN "CC_MAP_LITE_EXT" AS T2
ON
T1.CARD_NO = T2.ALIASCARDNO
AND
T2.CHOOSEFLAG = 1;
DELETE FROM "CC_EVERYDAY_METRO_RECORDS"
WHERE COLLECT_DATE = :UNIFY_DATE;
INSERT INTO "CC_EVERYDAY_METRO_RECORDS"
SELECT
CARD_NO AS ALIASCARDNO,
CARD_TYPE AS CARD_CLASS,
IDCARDNO,
METRO_COUNT AS SUBWAY_TIMES,
TO_DATE(:UNIFY_DATE) AS COLLECT_DATE
FROM :VAR_FINAL_RESULT;
END;
uj5u.com熱心網友回復：
你這個根本不是存盤程序，存盤程序里面想把結婚列賦值給變數，應該是用select col into 變數 from！記得單個變數，查出來的結婚列應該是一行哦uj5u.com熱心網友回復：
這個存盤程序是我從hana資料庫中匯出來的，現在要放到oracle資料庫中使用，看著好像就幾條sql但不知道怎么改
uj5u.com熱心網友回復：
sap嗎？你百度下select into看看
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/21236.html
標籤：開發
上一篇：硬碟損壞，服務器使用xfsdump冷備份了資料庫安裝目錄檔案系統可以直接掛載新硬碟恢復到原目錄嗎？
下一篇：ERROR 1356 (HY000): View 'information_schema.COLUMNS' references invalid