str是一个变量,不能直接用在SQL语句中,
如果需要用在SQL语句中,可以使用DBMS_SQL包来实现动态SQL
如果需要用在SQL语句中,可以使用DBMS_SQL包来实现动态SQL
解决方案 »
- 关闭数据库据如何用sqlplus启动?
- 给linux oracle程序员一个交流的家
- 关于存储过程和select ...for update nowait的问题
- 出现错误提示:ORA-04031: 无法分配 8192 字节的共享内存 ("large pool","unknown object","cursor work he","get new buffer")
- pl/sql中执行SQL的问题
- 急-------------在线等----开贴给分50-----关于一条sql问题
- Oracle全文检索中遇到特殊字符的问题
- oracle8.1.7的字符设置冲突,100分相赠,请高手支招
- 如何在两个数据库中进行备份?
- 想编写一个类似SQL SERVER的表值函数,不知ORACLE如何实现,请高手帮忙
- 谁敢说select简单——来试试看400分
- 高分求助:B/S结构系统中往Oracle数据库的Clob字段中添加信息时经常出现乱字符问题
create or replace procedure single_Row_insert
(c1 number, c2 number, r out number) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'insert into tab values (:bnd1, :bnd2) ' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_variable(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r); -- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/
create or replace type mytabletype as table of number;
/create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/SQL> select * from table(cast(strtab('11,12,13') as mytabletype));COLUMN_VALUE
------------
11
12
13SQL> create table bb(id varchar2(2),name varchar2(10));Table createdSQL> insert into bb values('11','张三');1 row insertedSQL> insert into bb values('12','李四');1 row insertedSQL> insert into bb values('13','王五');1 row insertedSQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));ID NAME
-- ----------
11 张三
12 李四
13 王五
/create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/declare
str varchar(500):='1071,1120,1119,1212,1220,1007,1036,1087';
num number :=1;
begin
select sum(dv.quantity) into num from vv_om112_delivery_2_v dv
where
dv.delivery_id in (select * from table(cast(strtab(str) as mytabletype)));
dbms_output.put_line(num);
dbms_output.put_line(str);
end;
/
函数是什么意思?
create or replace type mytabletype as table of number;
/create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
我怎么执行楼上写的才能得到结果亚,我在pl/sql中运行了,没错误,也没有结果亚,谢谢