set serveroutput on
declare
begin
for i in 1..1000 loop
insert into tab1 values(1);
commit;
end loop;
exception
when DUP_VAL_ON_INDEX then
dbms_output.put_line('一意性の制約違反発生');
when others then
dbms_output.put_line(SQLCODE);
dbms_output.put_line(SQLERRM);
end;
/
https://dev.mysql.com/doc/refman/5.6/ja/declare-handler.html
https://www.bnote.net/mysql/appendix/exception.shtml
CREATE TABLE tab22 (col1 INT, PRIMARY KEY (col1));
delimiter //
CREATE PROCEDURE proc22 ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO tab22 VALUES (1);
SET @x = 2;
INSERT INTO tab22 VALUES (1);
SET @x = 3;
END;
//
delimiter ;
CALL proc22();
SELECT @x;
SET client_min_messages TO notice;
DO $$
DECLARE
x numeric;
y varchar;
BEGIN
FOR i IN -5..5 LOOP
x := 1 / i::numeric;
RAISE NOTICE 'i=%,x=%', i,x;
EXECUTE 'SELECT pg_sleep(1)';
END LOOP;
y := '12345678';
insert into tab1 values(1);
insert into tab1 values(1);
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
WHEN others THEN
RAISE NOTICE 'SQLERRM= %,SQLSTATE=%',SQLERRM,SQLSTATE;
END
$$
;
https://www.dbsheetclient.jp/blog/?p=658
create table tab1
(col1 varchar(100),col2 varchar(10),col3 varchar(1))
create procedure dbo.proc1
as
begin try
select 'step 1'
insert into dbo.tab1
values('test', 'test', 'test');
select 'step 2'
end try
begin catch
select
error_number() as エラー番号
, error_severity() as エラー重大度
, error_message() as エラーメッセージ
, error_line() as エラー行
end catch
go
exec test.dbo.proc1;
go
