英文:
DB2 FUNCTION EXCEPTION
问题
在异常部分遇到错误。我在互联网上搜索，我得到了DECLARE type HANDLER FOR SQLEXCEPTION，他们提供了这个语法，并在执行部分之前提到了需要声明这个。我不确定我需要在type的位置使用什么词。有人可以帮助我找到exception when others的DB2等效语法吗？
英文:
I'm converting Oracle plsql function into DB2 plsql function.Below I mentioned the structure of Oracle and DB2 query.
CREATE FUNCTION FUNCTION_NAME (IN PARAMETER1 DATATYPE)
Return varchar(4000) IS
N_COLUMN1 varchar(1);
N_COLUMN2 INT;
N_COLUMN3 VARCHAR(2000);
BEGIN
SELECT A.COLUMN1,A.COLUMN2 INTO N_COLUMN1,N_COLUMN2 FROM TABLE A WHERE A.COLUMN1=PARAMETER1;
IF N_COLUMN1 = 'A' then N_COLUMN3:= 'NEW_A';
ELSEIF N_COLUMN1 = 'B' then N_COLUMN3:= 'NEW_B';
END IF;
exception when others then N_COLUMN3:= 'OTHERS'
RETURN N_COLUMN3;
END;
/
DB2 query:
CREATE FUNCTION FUNCTION_NAME (IN PARAMETER1 DATATYPE)
Returns varchar(4000)
BEGIN
declare N_COLUMN1 varchar(1);
declare N_COLUMN2 INT;
declare N_COLUMN3 VARCHAR(2000);
SELECT A.COLUMN1,A.COLUMN2 INTO N_COLUMN1,N_COLUMN2 FROM TABLE A WHERE A.COLUMN1=PARAMETER1;
IF N_COLUMN1 = 'A' then set N_COLUMN3= 'NEW_A';
ELSEIF N_COLUMN1 = 'B' then set N_COLUMN3= 'NEW_B';
END IF;
exception when others then set N_COLUMN3= 'OTHERS'
RETURN N_COLUMN3;
END;
I'm getting error on Exception part.I searched over internet and i'm getting DECLARE type HANDLER FOR SQLEXCEPTION they provide this syntax and mentioned before execution part need to declare this one.i'm not sure what word i need to use in place of type.can anyone help me what is the equaivalent DB2 syntax for exception when others
答案1
得分: 0
这取决于在处理程序调用后，您希望在代码中传递控制的位置。查看handler-declaration的描述。以下是函数代码的一个可能示例。
CREATE FUNCTION FUNCTION_NAME
(
IN PARAMETER1 VARCHAR (1)
)
Returns varchar(4000)
BEGIN
declare N_COLUMN1 varchar(1);
declare N_COLUMN2 INT;
declare N_COLUMN3 VARCHAR(2000);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 处理程序触发时在函数中执行的最后一个语句
-- 由于处理程序在主BEGIN END函数块中声明，
-- 控制流将传递出函数体
RETURN 'OTHERS';
END;
SELECT A.COLUMN1,A.COLUMN2
INTO N_COLUMN1,N_COLUMN2
FROM
--表A
(
VALUES
('A', 0)
, ('A', 0)
, ('B', 0)
) A (COLUMN1, COLUMN2)
WHERE A.COLUMN1=PARAMETER1;
IF N_COLUMN1 = 'A' then
set N_COLUMN3 = 'NEW_A';
ELSEIF N_COLUMN1 = 'B' then
set N_COLUMN3 = 'NEW_B';
END IF;
-- 异常处理部分（当其他情况发生时），则设置N_COLUMN3= 'OTHERS'
RETURN N_COLUMN3;
END
SELECT P, FUNCTION_NAME (P) AS F
FROM (VALUES 'A', 'B') T (P)
| P | F |
|---|---|
| A | OTHERS |
| B | NEW_B |
英文:
This depends on where you want to pass the control in your code after the handler invocation.
Look at the handler-declaration description.
Below is a possible example of the function code.
CREATE FUNCTION FUNCTION_NAME
(
IN PARAMETER1 VARCHAR (1)
)
Returns varchar(4000)
BEGIN
declare N_COLUMN1 varchar(1);
declare N_COLUMN2 INT;
declare N_COLUMN3 VARCHAR(2000);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- The last statement executed in the function
-- when the handler fires.
-- The control is passed out of the function body
-- since the handler is declared
-- in the main BEGIN END function block.
RETURN 'OTHERS';
END;
SELECT A.COLUMN1,A.COLUMN2
INTO N_COLUMN1,N_COLUMN2
FROM
--TABLE A
(
VALUES
('A', 0)
, ('A', 0)
, ('B', 0)
) A (COLUMN1, COLUMN2)
WHERE A.COLUMN1=PARAMETER1;
IF N_COLUMN1 = 'A' then
set N_COLUMN3 = 'NEW_A';
ELSEIF N_COLUMN1 = 'B' then
set N_COLUMN3 = 'NEW_B';
END IF;
--exception when others then set N_COLUMN3= 'OTHERS'
RETURN N_COLUMN3;
END
SELECT P, FUNCTION_NAME (P) AS F
FROM (VALUES 'A', 'B') T (P)
| P | F |
|---|---|
| A | OTHERS |
| B | NEW_B |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论