postgresql函数

1 若count != 1时,抛出异常并返回

DO
    $$
DECLARE ex_result INTEGER DEFAULT 0 ;
BEGIN
    UPDATE t_order_use_item SET quantity = - 1 WHERE use_item_id = 310598276397286402 ; 
    GET DIAGNOSTICS ex_result := ROW_COUNT ; --更新的影响的记录数
 IF ex_result <> 1 THEN
     RAISE EXCEPTION '更新失败' ;
 END IF ;
END ; $$language plpgsql;

2 循环批量插入或更新数据

DO
    $$
DECLARE record_ref RECORD ; --申请记录
DECLARE destTmpStockId BIGINT ;
DECLARE nowTime DATE ;
BEGIN
    nowTime := now() ; FOR record_ref IN (
        SELECT stock_id,SUM (quantity) quantity FROM t_stock_item_log GROUP BY stock_id
    ) loop 

    destTmpStockId = (SELECT stock_id FROM t_stock WHERE stock_id = record_ref.stock_id) ;
    IF destTmpStockId IS NOT NULL THEN
        --更新库存
        UPDATE t_stock SET quantity = record_ref.quantity,create_time = now_time WHERE stock_id = record_ref.stock_id;
    ELSE
        destTmpStockId = nextval('tmp_seq') ; --插入库存
        INSERT INTO t_stock (stock_id,quantity,create_time) VALUES(destTmpStockId,record_ref.quantity,nowTime) ;
    END IF;
    END loop ;
END ; 
$$language plpgsql;

3 使用主键-1

DO
    $$
DECLARE record_ref RECORD ; --申请记录
DECLARE destTmpStockId BIGINT ;
BEGIN
SELECT MIN (stock_id) INTO destTmpStockId FROM t_stock ; 
    FOR record_ref IN (
            SELECT stock_id,SUM (quantity) quantity FROM t_stock_item_log GROUP BY stock_id
        ) loop 
        destTmpStockId := destTmpStockId - 1 ; 
     INSERT INTO t_stock (stock_id, quantity) VALUES(destTmpStockId,record_ref.quantity);
    END loop ;
END ; 
$$language plpgsql;

4 双层循环

DO
    $$
DECLARE record RECORD ; --汇总记录
DECLARE stockArchiveId BIGINT ;
DECLARE nowTime DATE ;
DECLARE beginTime DATE ;
DECLARE endTime DATE ;
BEGIN
SELECT MIN (stock_archive_id) INTO stockArchiveId FROM t_archive_stock ; 
nowTime := now() ; 
    FOR record IN (
            SELECT * FROM tmp_repair_stock_info WHERE stock_id = 248606209001803006
        ) loop 
        stockArchiveId := stockArchiveId - 1 ; 
        beginTime := record.beginTime ; 
        endTime := record.endTime ; 
        while beginTime <= endTime loop 
            INSERT INTO "public"."tmp_index" ("stock_time") VALUES(beginTime) ; 
            beginTime = beginTime :: TIMESTAMP + INTERVAL '1 day' ;
        END loop ;
    END loop ;
END; 
$$language plpgsql;