EXECUTE IMMEDIATE 'select col1,col2,... from tab_name where '||参数1||'='||参数2 into vCol1,vCol2,....; 只是返回是一条的记录。 如果要返回多条可以用建表的形式。
create or replace package myPackage as type refCursor is ref cursor; end;create or replace procedure p ( column_name varchar2, column_value number, cur out Mypackage.refCursor ) as begin open cur for 'select * from 表 where '||column_name||'='||to_char(column_value); end;
vivianfdlpw() ,我试了,好像不行,
这个语法好像是sql里面的,,oracle里面怎么实现呢,,恳请指点。。
动态sql语句?怎么实现呢?大家帮帮忙阿。。
--创建测试环境 create table A(ID number,NAME varchar2(20));insert into A select 1,'张三' from dual union select 1,'李四' from dual;create or replace package myPackage as type refCursor is ref cursor; end;create or replace procedure p ( column_name varchar2, column_value number, cur out Mypackage.refCursor ) as begin open cur for 'select * from A where '||column_name||'='||to_char(column_value); end;--测试 declare cur Mypackage.refCursor; V_Row A%rowtype; begin P('ID',1,cur); loop fetch cur into V_Row; exit when cur%notfound; dbms_output.put_line(V_Row.NAME); end loop;
close cur; exception when others then close cur; raise; end;--结果 李四 张三--删除测试环境 drop table A cascade constraint; drop procedure P; drop package myPackage;
CREATE OR REPLACE PROCEDURE "P_test" ( 参数1 VARCHAR2, 参数2 VARCHAR2 ) IS strSql varchar2(2000); begin strSql:='select * fron tab_name where ' || 参数1 || '=''' || 参数2 || '''; execute immediate strSql; commit; end
into vCol1,vCol2,....;
只是返回是一条的记录。
如果要返回多条可以用建表的形式。
as
type refCursor is ref cursor;
end;create or replace procedure p
(
column_name varchar2,
column_value number,
cur out Mypackage.refCursor
)
as
begin
open cur for 'select * from 表 where '||column_name||'='||to_char(column_value);
end;
create table A(ID number,NAME varchar2(20));insert into A
select 1,'张三' from dual
union
select 1,'李四' from dual;create or replace package myPackage
as
type refCursor is ref cursor;
end;create or replace procedure p
(
column_name varchar2,
column_value number,
cur out Mypackage.refCursor
)
as
begin
open cur for 'select * from A where '||column_name||'='||to_char(column_value);
end;--测试
declare
cur Mypackage.refCursor;
V_Row A%rowtype;
begin
P('ID',1,cur);
loop
fetch cur into V_Row;
exit when cur%notfound;
dbms_output.put_line(V_Row.NAME);
end loop;
close cur;
exception
when others then
close cur;
raise;
end;--结果
李四
张三--删除测试环境
drop table A cascade constraint;
drop procedure P;
drop package myPackage;
参数1 VARCHAR2,
参数2 VARCHAR2
)
IS
strSql varchar2(2000);
begin
strSql:='select * fron tab_name where ' || 参数1 || '=''' || 参数2 || ''';
execute immediate strSql;
commit;
end
execute immediate strSql