select function() from table ;
不要用分组函数试试
不要用分组函数试试
解决方案 »
- 求一个sql语句,条件看里面,谢谢!
- trigger提示无效且未通过重新确认
- 请教一个Oracle数据库查询变慢的问题!!!
- SQL查询语句
- 有谁知道怎么用程序比如VB.Net,或者C#.Net 获取Oracle执行后的返回信息吗?Alter user aa account unlock;返回结果是“用户已修改”。
- 关于包含BLOB类型的存储过程,如何传BLOB这个参数
- 关于Sequence的用法!!
- 一个简单的SQL语句问题,在线等待~
- 才入门,网上那里有oracle的资料阿 ,谢谢各位了
- 请大家进来看看:这个trace文件中的错误是什么意思?会影响数据库吗?
- 一个比较菜的问题!
- 大侠帮我啊
去了group by name 还是这个问题:实际返回的行数超出请求的行
create or replace function get
(count in number)
return number
is
out_count number;
begin
select count(line1) into out_count from test where status='y' group by name;
return (out_count);
end get;
调用是select get(1) from table group by name;
用select count(line1) from test where status='y' group by name; 可以选出多条记录。
请高手指教!谢了!
(count in number)
1)count 改成游标或数组
2)用游标读 select count(line1) from test where status='y' group by name
有没有简单的方法?
/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;
/
create or replace function get
return varchar2
is
cursor t_sor is
select count(line1) num from test where status='y' group by name;
out_count varchar2(50);
n number;=0;
begin
for v_sor in t_sro loop
if n=0 then
out_count:=v_sor.num;
n:=n+1;
else
out_count:=out_count||','||v_sor.num;
end if;
return (out_count);
end get;
/
select * from table(cast(strtab(select get from dual) as mytabletype));
get 的函数出错:LINE/COL ERROR
-------- -------------------------------------------------------------
7/10 PLS-00103: 出现符号 "="在需要下列之一时:
beginfunctionpackagepragma
proceduresubtypetypeuse<an identifier>
<a double-quoted delimited-identifier>formcurrentcursor
get的函数返回的是字符串,我想能不能返回数据集?
create or replace function get
return varchar2
is
cursor t_sor is
select count(line1) num from test where status='y' group by name;
out_count varchar2(50);
n number;=0;
begin
for v_sor in t_sor loop
if n=0 then
out_count:=v_sor.num;
n:=n+1;
else
out_count:=out_count||','||v_sor.num;
end if;
end loop;
return (out_count);
end get;
/
select * from table(cast(strtab(select get from dual) as mytabletype));
--这条sql语句就实现你结果集
select * from table(cast(strtab(select get from dual) as mytabletype));这句出错
create or replace type mytabletype as table of number;
/create or replace function strtab
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
cursor t_sor is
select count(line1) num from test where status='y' group by name;
out_count varchar2(50);
begin
for v_sor in t_sor loop
out_count:=out_count||v_sor.num||',';
end loop;
loop
ln:=instr(out_count,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(out_count,1,ln-1)));
out_count:=substr(out_count,ln+1);
end loop;
return ldata;
end;
/
select * from table(cast(strtab as mytabletype));
create or replace type mytabletype as table of number;
/create or replace function strtab
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
cursor t_sor is
select count(line1) num from test where status='y' group by name;
out_count varchar2(50);
begin
for v_sor in t_sor loop
out_count:=out_count||v_sor.num||',';
end loop;
loop
ln:=instr(out_count,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(out_count,1,ln-1)));
out_count:=substr(out_count,ln+1);
end loop;
return ldata;
end;
/
select * from table(cast(strtab as mytabletype));
return mytabletype
as
ldata mytabletype:=mytabletype();
cursor t_sor is
select count(line1) num from test where status='y' group by name;
begin
for v_sor in t_sor loop
ldata.extend;
ldata(ldata.count):=v_sor.num;
end loop;
return ldata;
end;
/