create or replace procedure ChangeInfo(tnum in number) as
SqlStr varchar(250);
begin
case
when tnum= 1 then
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner=''v''';
when tnum= 2 then
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner=''a''';
when tnum= 3 then
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner=''b''';
else
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner not in (''v'',''a'',''b'')';
end;
dbms_output.put_line(SqlStr);
end ;
Compilation errors for PROCEDURE CASIC.CHANGEINFOError: PLS-00103: 出现符号 ";"在需要下列之一时:
case
符号 "case" 被替换为 ";" 后继续。
Line: 13
Text: end;
SqlStr varchar(250);
begin
case
when tnum= 1 then
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner=''v''';
when tnum= 2 then
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner=''a''';
when tnum= 3 then
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner=''b''';
else
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner not in (''v'',''a'',''b'')';
end;
dbms_output.put_line(SqlStr);
end ;
Compilation errors for PROCEDURE CASIC.CHANGEINFOError: PLS-00103: 出现符号 ";"在需要下列之一时:
case
符号 "case" 被替换为 ";" 后继续。
Line: 13
Text: end;
CREATE OR REPLACE PROCEDURE ChangeInfo(tnum IN NUMBER) AS
SqlStr VARCHAR(250);
BEGIN
CASE
WHEN tnum = 1 THEN
SqlStr := 'select tt.owner,tt.object_name from test_table tt where tt.owner=''v''';
WHEN tnum = 2 THEN
SqlStr := 'select tt.owner,tt.object_name from test_table tt where tt.owner=''a''';
WHEN tnum = 3 THEN
SqlStr := 'select tt.owner,tt.object_name from test_table tt where tt.owner=''b''';
ELSE
SqlStr := 'select tt.owner,tt.object_name from test_table tt where tt.owner not in (''v'',''a'',''b'')';
END CASE; dbms_output.put_line(SqlStr);
END;
SqlStr varchar(250);
begin
case tnum
when 1 then
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner=''v''';
when 2 then
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner=''a''';
when 3 then
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner=''b''';
else
SqlStr:='select tt.owner,tt.object_name from test_table tt where tt.owner not in (''v'',''a'',''b'')';
end case;
dbms_output.put_line(SqlStr);
end ;
--还不如用if-else-end if好处理:
SQL> create or replace procedure pro_statement(n_in number)
2 as
3 sqlstr varchar2(250);
4 begin
5 if n_in=1 then
6 sqlstr:='select * from emp';
7 elsif n_in=2 then
8 sqlstr:='select * from dept';
9 else
10 sqlstr:='select e.empno,d.deptno,e.job,e.sal from emp e,dept d where e.deptno=d.deptno';
11 end if;
12 dbms_output.put_line(sqlstr);
13 end pro_statement;
14 /
Procedure created
SQL> set serveroutput on;
SQL> exec pro_statement(2);
select * from dept
PL/SQL procedure successfully completed
SQL> exec pro_statement(10);
select e.empno,d.deptno,e.job,e.sal from emp e,dept d where e.deptno=d.deptno
PL/SQL procedure successfully completed
SQL> exec pro_statement(1);
select * from emp
PL/SQL procedure successfully completed