是否可以在过程中声明带有约束的常量?
类似于:
declare
procedure proc
(param1 IN VARCHAR2,
param2 IN VARCHAR2,
param3 IN NUMBER)
is
BEGIN
my_const CONSTANT VARCHAR2(50) := param1 -- Notice the constraint where my_const is VARCHAR2(50)
other_c CONSTANT VARCHAR2(50) := 'My other constant!'
-- Do something with my_const and other_c
END;
有一个约束的想法是,如果有人提供的值太长,我想引起一个错误。
无法在参数处定义约束。我做不到(至少我试过了(:
declare
procedure proc
(param1 IN VARCHAR2(50),
param2 IN VARCHAR2(50),
param3 IN NUMBER)
is
BEGIN
other_c CONSTANT VARCHAR2(50) := 'My other constant!'
-- Do something with other_c
END;
我知道我可以用if
检查长度,如果太长就会发出异常,但我正在寻找一种更简单的方法。
您可以将参数直接传递到常量的值中,但如果不适合,则会得到VALUE_ERROR
。您需要在异常处理中捕获该错误,或者如果希望引发该错误,则删除该异常处理。
DECLARE
PROCEDURE proc (param1 IN VARCHAR2, param2 IN VARCHAR2, param3 IN NUMBER)
IS
my_const VARCHAR2 (5) := param1; -- Notice the constraint where my_const is VARCHAR2(5)
other_c VARCHAR2 (5) := 'other';
BEGIN
-- Do something with my_const and other_c
NULL;
END;
BEGIN
proc ('long_val', 'test', 0);
EXCEPTION
WHEN VALUE_ERROR
THEN
DBMS_OUTPUT.put_line ('Something doesn''t fit');
END;
/
您可以尝试这样的操作-内部代码块声明常量并分配输入参数;您还可以在过程中包含异常块;出于演示的目的,我将这个块添加到调用您的proc的块中。
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE proc (
param1 IN VARCHAR2,
param2 IN VARCHAR2,
param3 IN NUMBER
) IS
BEGIN
-- Do something with other_c
DECLARE
my_const CONSTANT VARCHAR2(50) := param1;
other_c CONSTANT VARCHAR2(50) := 'My other constant!';
BEGIN
dbms_output.put_line('other_c'
|| chr(10)
|| other_c);
dbms_output.put_line('my_const'
|| chr(10)
|| my_const);
END;
END;
/
下面是一个示例方法调用:
BEGIN
proc(param1 => 'sdfasdfsadfsdfadsfasfasdfsadfa32234fdasdfsadfsdfsadfaf', param2 => 'something', param3 => 1);
EXCEPTION
WHEN value_error THEN
dbms_output.put_line('Invalid input param!');
END;
/