create table sample_data (
id integer generated by default as identity,
info varchar(100),
created_by integer not null,
created_date timestamp(6) not null,
modified_by integer not null,
modified_date timestamp(6) not null,
ver integer not null,
primary key (id)
);
CREATE OR REPLACE PROCEDURE create_sample_data(in v_info VARCHAR)
LANGUAGE 'plpgsql'
AS $$
DECLARE
--v_user_id INTEGER := 9;
v_user_id sample_data.created_by%TYPE := 3;
BEGIN
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO sample_data (
info, created_by, created_date, modified_by, modified_date, ver
) VALUES (
v_info || ':' || i, v_user_id, CURRENT_TIMESTAMP, v_user_id, CURRENT_TIMESTAMP, 0
);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
ROLLBACK;
RAISE;--呼出し元へのエラー伝達
END;
COMMIT;
END;
$$;
例外ハンドラを伴うブロック内ではトランザクションを終了できない
cannot commit while a subtransaction is active
参考サイト
