PLSQL - creating a procedure with a parameter but only accepting 2 values
Firstly, I am a DBA who does PL / SQL programming. I have some knowledge, but some of it is definitely missing.
CREATE OR REPLACE PROCEDURE TRIGGER_PRC (P_TRGNAME IN VARCHAR2, P_STATUS IN VARCHAR2)
AS ....
I would like to allow the P_STATUS parameter to only allow the "E" or "D" values to enable or disable the trigger. I have done some searching but cannot find a solution for this. Any help is greatly appreciated!
Thank!
Jeremiah
source to share
You can use IF
to check if the values match E or D. If not, raise the error using raise_application_error
:
CREATE OR REPLACE PROCEDURE TRIGGER_PRC (P_TRGNAME IN VARCHAR2, P_STATUS IN VARCHAR2)
AS
begin
if P_STATUS not in ('E', 'D') then
raise_application_error(-20001, 'Invalid P_STATUS value - ' || P_STATUS);
end if;
. . .
. . .
end;
/
source to share
I would rather use the following approach:
CREATE OR REPLACE PROCEDURE ENABLE_TRIGGER_PRC (P_TRGNAME IN VARCHAR2)
AS ....
BEGIN
-- Enable the trigger P_TRGNAME here
END;
CREATE OR REPLACE PROCEDURE DISABLE_TRIGGER_PRC (P_TRGNAME IN VARCHAR2)
AS ....
BEGIN
-- Disable the trigger P_TRGNAME here
END;
You can do the second to call the first, etc., but you must always have an IF statement (as pointed out in the GurV) to test it once inside the procedure. In the future, you can add drop
. I suggest you put them in a package so that they are all combined there, having one procedure to execute the actual statement, for example in execute immediate
, so that all other procedures can reuse the same code.
Greetings
source to share
The solution is to use a boolean too, since you only have two options.
CREATE OR REPLACE PROCEDURE TRIGGER_PRC (P_TRGNAME IN VARCHAR2, P_STATUS IN BOOLEAN)
AS ....
From this interesting article , it is recommended that you create a separate function to validate the input if you want some in PLSQL (externalization is suggested by GurV). ENUM
if
So what do you do with a guy?
- If you want to use an enum in a table, use a validation constraint.
- If you want to use an enum in a stored procedure, write a separate procedure to validate the input.
What you could do if there are more than two values: If you can afford to store global variables in a package, and also advise your developers to look for constants in a specific package (a graphical user like PLSQL Developer, or very easy to use):
CREATE OR REPLACE PACKAGE global_vars IS
P_STATUS_enable CONSTANT varchar2(2) := 'E';
P_STATUS_disable CONSTANT varchar2(2) := 'D';
P_STATUS_drop CONSTANT varchar2(2) := 'Dr';
end global_vars;
/
create or replace procedure TRIGGER_PRC (P_TRGNAME IN VARCHAR2, P_STATUS IN varchar2)
AS
begin
if P_STATUS = global_vars.P_STATUS_enable then
-- do something
dbms_output.put_line('ENABLE');
elsif P_STATUS = global_vars.P_STATUS_disable then
-- p_status = P_STATUS_disable
dbms_output.put_line('DISABLE');
elsif P_STATUS = global_vars.P_STATUS_drop then
-- do other stuff
dbms_output.put_line('DROP?');
end if;
end TRIGGER_PRC;
/
begin
TRIGGER_PRC ('TRIG', global_vars.P_STATUS_enable);
end;
/
source to share