SQL> select count(1) from aa; COUNT(1)
----------
7SQL> select 100+(select count(1) from aa) from dual;100+(SELECTCOUNT(1)FROMAA)
--------------------------
107
----------
7SQL> select 100+(select count(1) from aa) from dual;100+(SELECTCOUNT(1)FROMAA)
--------------------------
107
解决方案 »
- 寻找更高效的排序方法
- linux上安装完oracle,为什么找不到liboci.so?
- oracle的表中 date类型插入和查询的数值不一致啊
- 异库表数据同步!!!!!!
- 关于sql2000导入到oracle 大字段问题
- 求触发器统计的问题
- 麻烦各位大哥进来看看,DB LINK连接失效的问题,怎么解决
- 求助,哪位好心的GG帮我把下面的这个SQL Server的视图变成Oracle的?谢谢先!!!!!!!!
- 简单的关于用户问题?
- 谁有不错的oracle的视频教学的东西,或者推荐点好书给我,谢了。
- 如何在linux9下自动关闭启动oracle8.17?急!
- 第一次提问:这个ORACLE的SQL语句如写改成标准的SQL语句呢?主要是(+)联接改为LEFT JOIN的操作!
----------
7SQL> select 100+(select count(1) from aa) from dual;100+(SELECTCOUNT(1)FROMAA)
--------------------------
107
101Elapsed: 00:00:00.15
11:40:09 SQL> select count(*) from tab;
1Elapsed: 00:00:00.01
11:40:27 SQL>
select (select count(data) from zltxsh001 where data>10)/(select count(data) from zltxsh001) from dual;
--类似于计算合格率beckhambobo(beckham) 的做法,似乎仅仅是把Count(*)换成了count(具体字段名),我这儿改成了字段data,但是依然提示表达式错误,会不会因为我的Oracle版本是8.05?
-----------
48 SQL> select count(data) from zltxsh001; COUNT(DATA)
-----------
85 SQL> select (select count(data) from zltxsh001 where data>10)/(select count(data) from zltxsh001) from dual;
select (select count(data) from zltxsh001 where data>10)/(select count(data) from zltxsh001) from du
*
错误位于第1行:
ORA-00936: 缺少表达式
select (select count(data) from zltxsh001 where data>10)/((select count(data) from zltxsh002)+(select count(data) from zltxsh003)) from dual;
---------------------
85SQL> select count(zltxlg0002.lrsj) from zltxlg0002;COUNT(ZLTXLG0002.LRSJ)
----------------------
624SQL> select count(zltxsh001.data)/count(zltxlg0002.data) from zltxsh001,zltxlg0002;COUNT(ZLTXSH001.DATA)/COUNT(ZLTXLG0002.DATA)
--------------------------------------------
1SQL> select count(a.data)/count(b.data) from zltxsh001 a,zltxsh001 b where a.data>10;COUNT(A.DATA)/COUNT(B.DATA)
---------------------------
1
select temp,count(b.data),temp/count(b.data) from zltxsh001 b,
(select count(a.data) temp from zltxsh001 a where a.data>10) tmpTable
group by temp
这样temp/count(b.data)就是合格率了。如果再不行俺就帮不了你了,^-^
2 (select count(a.data) temp1 from zltxsh001 a where a.data>10) tmpTable1,
3 (select count(a.data) temp2 from zltxsh001 a where a.data>20) tmpTable2,
4 (select count(a.data) temp3 from zltxsh001 a ) tmpTable3; TEMP1 TEMP2 TEMP3
--------- --------- ---------
48 7 85SQL> select temp1/temp2,temp2/temp3 from
2 (select count(a.data) temp1 from zltxsh001 a where a.data>10) tmpTable1,
3 (select count(a.data) temp2 from zltxsh001 a where a.data>20) tmpTable2,
4 (select count(a.data) temp3 from zltxsh001 a ) tmpTable3;TEMP1/TEMP2 TEMP2/TEMP3
----------- -----------
6.8571429 .08235294
SQL> create or replace function getCount(tablename in varchar2,n in varchar2)
2 return number
3 is
4 resultvalue NUMBER(8,2);
5 l_str varchar2(1000);
6 cursor_i INTEGER;
7 cursor_ret INTEGER;
8 begin
9 l_str:='select count(*) into resultvalue from '||tablename||' where data>='||n;
10 cursor_i:=dbms_sql.open_cursor;
11 DBMS_SQL.PARSE(cursor_i,l_str,DBMS_SQL.NATIVE);
12 cursor_ret:=dbms_sql.execute(cursor_i);
13 dbms_sql.close_cursor(cursor_i);
14 return resultvalue;
15 end;
16 /函数已创建。SQL> select getCount('zltxsh001','10') from dual;
select getCount('zltxsh001','10') from dual
*
错误位于第1行:
ORA-06571: 函数GETCOUNT不能保证不更新数据库
不知道怎样修改一下才能搞定
以前写了些SQL语句,也没怎么想到底怎么实现的;参考了pegtop和你给出的那个例子,想一下 SQL执行的机理,也颇为有趣呀。
return number
is
resultvalue NUMBER(8,2);
l_str varchar2(1000);
cursor_i INTEGER;
cursor_ret INTEGER;
begin
l_str:='select count(1) from '||tablename ;
cursor_i:=dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_i,l_str,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(cursor_i,1,resultvalue);
cursor_ret:=dbms_sql.execute(cursor_i);
If dbms_sql.fetch_rows(cursor_i) > 0 then
Dbms_sql.column_value(cursor_i,1,resultvalue);
End if;
dbms_sql.close_cursor(cursor_i);
return resultvalue;
exception when others then
dbms_output.put_line('@@'||sqlerrm);
end;----------------------------------------------------------
SQL> select getcount('an_bureau') from dual
2 /GETCOUNT('AN_BUREAU')
---------------------
13
return number
is
resultvalue NUMBER(8,2);
l_str varchar2(1000);
cursor_i INTEGER;
cursor_ret INTEGER;
begin
l_str:='select count(1) from '||tablename ;
cursor_i:=dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_i,l_str,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(cursor_i,1,resultvalue);
cursor_ret:=dbms_sql.execute(cursor_i);
If dbms_sql.fetch_rows(cursor_i) > 0 then
Dbms_sql.column_value(cursor_i,1,resultvalue);
End if;
dbms_sql.close_cursor(cursor_i);
return resultvalue;
exception when others then
dbms_output.put_line('@@'||sqlerrm);
end;----------------------------------------------------------
SQL> select getcount('an_bureau') from dual
2 /GETCOUNT('AN_BUREAU')
---------------------
13
你用的Oracle哪个版本?我用的8.0.5,建立函数成功,执行时依然提示:
SQL> select getCount('zltxsh001') from dual;
select getCount('zltxsh001') from dual
*
错误位于第1行:
ORA-06571: 函数GETCOUNT不能保证不更新数据库到底怎样才能保证函数不更新数据库啊?
不过,刚刚用Oralce对这些东西还是不很熟;尤其找不到比较合适的参考资料;或者都太厚吧,准备另开一帖学习一下关于动态SqL语句,以及函数的问题