高手帮忙写个db2 存储过程

Question

有两张表
表1如：
CREATE TABLE man(
id INT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) ,
name VARCHAR(40) NOT NULL ,
age INT NOT NULL ,
status CHAR(6) NOT NULL ,
PRIMARY KEY (id)
）;
表2：
CREATE TABLE P_score
（
id INT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) ,
person_id INT NOT NULL,
score INT,
PRIMARY KEY (id) ,
FOREIGN KEY (person_id ) REFERENCES man(id)
）;

请高手帮帮忙些个存储过程 要求实现两张表的插入操作，当插入失败时实现回滚，实现数据的完整。

Answer 1

CREATE PROCEDURE SP_TEST()
SPECIFIC SP_TEST
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS

BEGIN ATOMIC
--全局错误处理变量
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

--异常错误处理变量
DECLARE V_SQLCODE INTEGER DEFAULT 0;
DECLARE V_SQLSTATE CHAR (5) DEFAULT '00000';
DECLARE V_BREAKPOINT CHAR (13) DEFAULT '001';
DECLARE V_ERRMSG VARCHAR (255);
DECLARE V_NOTFOUND SMALLINT DEFAULT 0;

--SQL异常处理程序体
DECLARE EXIT HANDLER FOR sqlexception
BEGIN
DECLARE CONTINUE HANDLER FOR sqlexception
BEGIN
SET MESSAGE_TEXT = V_ERRMSG;
END ;
SELECT SQLCODE,SQLSTATE INTO V_SQLCODE ,V_SQLSTATE FROM SYSIBM.SYSDUMMY1 ;
RESIGNAL;
END;

--SQL NOT FOUND 处理程序体
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET V_NOTFOUND = 1;
END ;

SET V_BREAKPOINT = '001';
INSERT INTO man (name ,age ,status) VALUES ('name',1,0);
insert into P_score(person_id ,score) values (select max(id) from man,10);

END;
DB2的存储过程自己会启事务,失败会自动回滚的