if data_date = '01' then begin execute a1 end if data_date = '02' then begin execute a2
CASE data_date WHEN '1' THEN EXECUTE IMMEDIATE a1; WHEN '2' THEN EXECUTE IMMEDIATE b1; ELSE EXECUTE IMMEDIATE c1; END CASE;
create or replace procedure proc1 (a1 in varchar2, b1 in varchar2, c1 in varchar2, data_date in varchar2 ) is begin if data_date is null then dbms_output.put_line('Error parameter: no value for data_date'); elsif data_date = '01' then execute immediate a1; elsif data_date = '02' then execute immediate b1; elsif data_date = '03' then execute immediate c1; else dbms_output.put_line('Error parameter: value for data_date is not in (''01'',''02'',''03'')'); end if; end;
动态拼接好sql 然后判断 执行
CREATE OR REPLACE PROCEDURE test_proc(v_data_date varchar2) AS sql1 VARCHAR2(200); sql2 VARCHAR2(200); sql3 VARCHAR2(200); BEGIN sql1 := 'SELECT emp, sal, depton FROM emp WHERE deptno=10 '; sql2 := 'SELECT emp, sal, depton FROM emp WHERE deptno=20 '; sql3 := 'SELECT emp, sal, depton FROM emp WHERE deptno=30 '; CASE v_data_date WHEN '01' THEN execute immediate sql1; WHEN '02' THEN execute immediate sql2; WHEN '03' THEN execute immediate sql3; ELSE dbms_output.put_line('输入参数有误,请您重新输入'); END CASE; END; /
CASE data_date WHEN '1' THEN EXECUTE IMMEDIATE a1; WHEN '2' THEN EXECUTE IMMEDIATE b1; ELSE EXECUTE IMMEDIATE c1; END CASE;
create or replace p1(data_date varchar2) as a1 varchar2(1000); b1 varchar2(1000); c1 varchar2(1000); begin a1:='你的sql1'; b1:='你的sql2'; c1:='你的sql3'; if data_date='01' then execute a1; elsif data_date='02' then execute b1; elsif data_date='03' then execute c1; else dbms_output.put_line('传入的参数不正确'); end if; end;
begin
execute a1
end
if data_date = '02' then
begin
execute a2
WHEN '1'
THEN EXECUTE IMMEDIATE a1;
WHEN '2'
THEN EXECUTE IMMEDIATE b1;
ELSE EXECUTE IMMEDIATE c1;
END CASE;
(a1 in varchar2,
b1 in varchar2,
c1 in varchar2,
data_date in varchar2
) is
begin
if data_date is null then
dbms_output.put_line('Error parameter: no value for data_date');
elsif data_date = '01' then
execute immediate a1;
elsif data_date = '02' then
execute immediate b1;
elsif data_date = '03' then
execute immediate c1;
else
dbms_output.put_line('Error parameter: value for data_date is not in (''01'',''02'',''03'')');
end if;
end;
AS
sql1 VARCHAR2(200);
sql2 VARCHAR2(200);
sql3 VARCHAR2(200);
BEGIN
sql1 := 'SELECT emp, sal, depton FROM emp WHERE deptno=10 ';
sql2 := 'SELECT emp, sal, depton FROM emp WHERE deptno=20 ';
sql3 := 'SELECT emp, sal, depton FROM emp WHERE deptno=30 ';
CASE v_data_date
WHEN '01' THEN
execute immediate sql1;
WHEN '02' THEN
execute immediate sql2;
WHEN '03' THEN
execute immediate sql3;
ELSE
dbms_output.put_line('输入参数有误,请您重新输入');
END CASE;
END;
/
WHEN '1'
THEN EXECUTE IMMEDIATE a1;
WHEN '2'
THEN EXECUTE IMMEDIATE b1;
ELSE EXECUTE IMMEDIATE c1;
END CASE;
as
a1 varchar2(1000);
b1 varchar2(1000);
c1 varchar2(1000);
begin
a1:='你的sql1';
b1:='你的sql2';
c1:='你的sql3';
if data_date='01' then
execute a1;
elsif data_date='02' then
execute b1;
elsif data_date='03' then
execute c1;
else
dbms_output.put_line('传入的参数不正确');
end if;
end;