你安照ms sql 的写个类似的oracle 动态的看看,行不行 --> 测试数据: #TB if object_id('tempdb.dbo.#TB') is not null drop table #TB GO create table #TB (N1 int,N2 varchar(4),N3 int,N4 varchar(4)) insert into #TB select 12,'茶碗',4,'李四' union all select 15,'水缸',4,'李四' union all select 8,'锅',4,'李四' union all select 53,'锅2',5,'王1' DECLARE @S VARCHAR(50) SET @S='''水缸'',''锅''' set @s='select * from #TB where n2 in ('+@s+')' --print @s exec(@s)N1 N2 N3 N4 ----------- ---- ----------- ---- 15 水缸 4 李四 8 锅 4 李四(2 行受影响)
我的Oracle代码如下: 测试结果,放在PLSQL里面好像还是不行CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));INSERT INTO TB SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL SELECT 15,'水缸',4,'李四' FROM dual UNION ALL SELECT 8,'锅',4,'李四' FROM dual UNION ALL SELECT 53,'锅2',5,'王1' FROM dual; SET serveroutput ON;DECLARE v_in VARCHAR2(20); v_out varchar2(10); BEGIN v_in :='''水缸'',''锅'''; SELECT MAX(n4) INTO v_out FROM tb WHERE n2 IN (v_in); dbms_output.put_line('out is : ' || v_out); end;SELECT MAX(n4) FROM tb WHERE n2 IN ('水缸','锅');
drop table tb; CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));INSERT INTO TB SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL SELECT 15,'水缸',4,'李四' FROM dual UNION ALL SELECT 8,'锅',4,'李四' FROM dual UNION ALL SELECT 53,'锅2',5,'王1' FROM dual; SET serveroutput ON;DECLARE v_in VARCHAR2(20); v_out varchar2(10); v_sql varchar2(500); BEGIN v_in :='''水缸'',''锅'''; --SELECT MAX(n1) INTO v_out FROM tb WHERE n2 IN (v_in); v_sql :='SELECT MAX(n4) from tb where n2 in('||v_in||')'; --把上面这句换成动态的 execute immediate v_sql into v_out; dbms_output.put_line('out is : ' || v_out); end; out is : 李四 PL/SQL 过程已成功完成。
你好,谢谢,我用你的方法测试通过了,而且我注意到v_sql里面不能带分号。不过关于动态SQL,我还有个问题想请教你,ORACLE的文档提到了execute v_sql into *** using ** 的語法,见下面的例子,但是我自己没有测试成功,请问你是怎样解决的,再次谢谢。 CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));INSERT INTO TB SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL SELECT 15,'水缸',4,'李四' FROM dual UNION ALL SELECT 8,'锅',4,'李四' FROM dual UNION ALL SELECT 53,'锅2',5,'王1' FROM dual;SET serveroutput ON;DECLARE v_in VARCHAR2(20); v_out varchar2(10); v_sql varchar2(500); BEGIN v_in :='''水缸'',''锅'''; --SELECT MAX(n1) INTO v_out FROM tb WHERE n2 IN (v_in); v_sql :='SELECT MAX(n4) from tb where n2 in(:v_para)'; --把上面这句换成动态的 dbms_output.put_line(v_sql); execute immediate v_sql into v_out using v_in; dbms_output.put_line('out is : ' || v_out); end;
v_sql :='SELECT MAX(n4) from tb where n2 in(:v_para)'; 你这个:v_para 全放引号里,就不是变量,是字符了哦
不过ORACLE的文档里是这样用的啊,http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDGJEGD见例子7-3附上一个ORACLE文档里面的例子 DECLARE plsql_block VARCHAR2(500); new_deptid NUMBER(4); new_dname VARCHAR2(30) := 'Advertising'; new_mgrid NUMBER(6) := 200; new_locid NUMBER(4) := 1700; BEGIN plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;'; EXECUTE IMMEDIATE plsql_block USING IN OUT new_deptid, new_dname, new_mgrid, new_locid; END; /
--> 测试数据: #TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
GO
create table #TB (N1 int,N2 varchar(4),N3 int,N4 varchar(4))
insert into #TB
select 12,'茶碗',4,'李四' union all
select 15,'水缸',4,'李四' union all
select 8,'锅',4,'李四' union all
select 53,'锅2',5,'王1'
DECLARE @S VARCHAR(50)
SET @S='''水缸'',''锅''' set @s='select * from #TB where n2 in ('+@s+')'
--print @s exec(@s)N1 N2 N3 N4
----------- ---- ----------- ----
15 水缸 4 李四
8 锅 4 李四(2 行受影响)
测试结果,放在PLSQL里面好像还是不行CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));INSERT INTO TB
SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL
SELECT 15,'水缸',4,'李四' FROM dual UNION ALL
SELECT 8,'锅',4,'李四' FROM dual UNION ALL
SELECT 53,'锅2',5,'王1' FROM dual;
SET serveroutput ON;DECLARE
v_in VARCHAR2(20);
v_out varchar2(10);
BEGIN
v_in :='''水缸'',''锅''';
SELECT MAX(n4) INTO v_out FROM tb WHERE n2 IN (v_in);
dbms_output.put_line('out is : ' || v_out);
end;SELECT MAX(n4) FROM tb WHERE n2 IN ('水缸','锅');
CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));INSERT INTO TB
SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL
SELECT 15,'水缸',4,'李四' FROM dual UNION ALL
SELECT 8,'锅',4,'李四' FROM dual UNION ALL
SELECT 53,'锅2',5,'王1' FROM dual;
SET serveroutput ON;DECLARE
v_in VARCHAR2(20);
v_out varchar2(10);
v_sql varchar2(500);
BEGIN
v_in :='''水缸'',''锅''';
--SELECT MAX(n1) INTO v_out FROM tb WHERE n2 IN (v_in);
v_sql :='SELECT MAX(n4) from tb where n2 in('||v_in||')'; --把上面这句换成动态的
execute immediate v_sql into v_out;
dbms_output.put_line('out is : ' || v_out);
end;
out is : 李四
PL/SQL 过程已成功完成。
你好,谢谢,我用你的方法测试通过了,而且我注意到v_sql里面不能带分号。不过关于动态SQL,我还有个问题想请教你,ORACLE的文档提到了execute v_sql into *** using ** 的語法,见下面的例子,但是我自己没有测试成功,请问你是怎样解决的,再次谢谢。
CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));INSERT INTO TB
SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL
SELECT 15,'水缸',4,'李四' FROM dual UNION ALL
SELECT 8,'锅',4,'李四' FROM dual UNION ALL
SELECT 53,'锅2',5,'王1' FROM dual;SET serveroutput ON;DECLARE
v_in VARCHAR2(20);
v_out varchar2(10);
v_sql varchar2(500);
BEGIN
v_in :='''水缸'',''锅''';
--SELECT MAX(n1) INTO v_out FROM tb WHERE n2 IN (v_in);
v_sql :='SELECT MAX(n4) from tb where n2 in(:v_para)'; --把上面这句换成动态的
dbms_output.put_line(v_sql);
execute immediate v_sql into v_out using v_in;
dbms_output.put_line('out is : ' || v_out);
end;
不过ORACLE的文档里是这样用的啊,http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDGJEGD见例子7-3附上一个ORACLE文档里面的例子
DECLARE
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/