create or replace procedure( in_b in varchar2, o_cur o sys_refcursor) as open o_cur for select * from tb where b like ''%'|| in_b || '%'''; end;
create or replace procedure test_b( in_b in varchar2, o_cur o sys_refcursor) as open o_cur for select * from tb where b like ''%'|| in_b || '%'''; end;
create or replace procedure test_b( in_b in varchar2, o_cur o sys_refcursor) as begin open o_cur for select * from tb where b like ''%'|| in_b || '%'''; end; /
to: crazylaa create or replace procedure test_b( in_b in varchar2, o_cur o sys_refcursor) as begin open o_cur for select * from tb where b like ''%'|| in_b || '%'''; end; / 警告: 创建的过程带有编译错误。
o_cur o sys_refcursor) 改为 o_cur out sys_refcursor) 打快了~~~
create or replace procedure pro(p_str1 in varchar) is p_str2 varchar2(100); begin p_str2 :='create or replace view v1 as select * from tb where b like ' || '''' || '%' || p_str1 || '%' || ''''; execute immediate p_str2; end pro; /
SQL> select * from tb;
A B ---------- -------------------- 1 asdf 2 bac 3 abc 4 wer
SQL> SQL> create or replace procedure pro(p_str1 in varchar) is 2 p_str2 varchar2(100); 3 begin 4 p_str2 :='create or replace view v1 as select * from tb where b like ' || '''' || '%' || p_str1 || '%' || ''''; 5 execute immediate p_str2; 6 end pro; 7 /
Procedure created
SQL> exec pro('a');
PL/SQL procedure successfully completed
SQL> select * from v1;
A B ---------- -------------------- 1 asdf 2 bac 3 abc
o_cur o sys_refcursor)
as
open o_cur for select * from tb where b like ''%'|| in_b || '%''';
end;
o_cur o sys_refcursor)
as
open o_cur for select * from tb where b like ''%'|| in_b || '%''';
end;
o_cur o sys_refcursor)
as
begin
open o_cur for select * from tb where b like ''%'|| in_b || '%''';
end;
/
create or replace procedure test_b( in_b in varchar2,
o_cur o sys_refcursor)
as
begin
open o_cur for select * from tb where b like ''%'|| in_b || '%''';
end;
/
警告: 创建的过程带有编译错误。
改为
o_cur out sys_refcursor)
打快了~~~
p_str2 varchar2(100);
begin
p_str2 :='create or replace view v1 as select * from tb where b like ' || '''' || '%' || p_str1 || '%' || '''';
execute immediate p_str2;
end pro;
/
SQL> select * from tb;
A B
---------- --------------------
1 asdf
2 bac
3 abc
4 wer
SQL>
SQL> create or replace procedure pro(p_str1 in varchar) is
2 p_str2 varchar2(100);
3 begin
4 p_str2 :='create or replace view v1 as select * from tb where b like ' || '''' || '%' || p_str1 || '%' || '''';
5 execute immediate p_str2;
6 end pro;
7 /
Procedure created
SQL> exec pro('a');
PL/SQL procedure successfully completed
SQL> select * from v1;
A B
---------- --------------------
1 asdf
2 bac
3 abc