CREATE OR REPLACE PROCEDURE Query1
AS
CNT_PART NUMBER(10) := 0;
SUM_PART NUMBER(10) := 0;
AVG_PART NUMBER(10) := 0;BEGIN
SELECT COUNT(*),SUM(p_retailprice),AVG(p_retailprice) INTO CNT_PART,SUM_PART,AVG_PART
FROM part
WHERE PS_PARTKEY IN
(SELECT P_PARTKEY
FROM PART
WHERE P_NAME LIKE 'B%' ) AND PS_AVAILQTY > (SELECT 0.5 * SUM(L_QUANTITY)
FROM LINEITEM
WHERE L_PARTKEY = PS_PARTKEY
AND L_SUPPKEY = PS_SUPPKEY
AND L_SHIPDATE <= '31-DEC-2003'
AND L_SHIPDATE > '12-MAY-2002')
group by p_retailprice;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END Query1;
/
AS
CNT_PART NUMBER(10) := 0;
SUM_PART NUMBER(10) := 0;
AVG_PART NUMBER(10) := 0;BEGIN
SELECT COUNT(*),SUM(p_retailprice),AVG(p_retailprice) INTO CNT_PART,SUM_PART,AVG_PART
FROM part
WHERE PS_PARTKEY IN
(SELECT P_PARTKEY
FROM PART
WHERE P_NAME LIKE 'B%' ) AND PS_AVAILQTY > (SELECT 0.5 * SUM(L_QUANTITY)
FROM LINEITEM
WHERE L_PARTKEY = PS_PARTKEY
AND L_SUPPKEY = PS_SUPPKEY
AND L_SHIPDATE <= '31-DEC-2003'
AND L_SHIPDATE > '12-MAY-2002')
group by p_retailprice;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END Query1;
/
解决方案 »
- 请问该如何修改Oracle的会话时间长度?谢谢。【高分】
- oracle11g 远程访问EM数据库管理平台无法连接(本机是LINUX系统)
- 按照官方文档装 一路正常。启动sql*plus确出错(ORA-01034: ORACLE not available)
- 关于AWR里面的度量。。
- Oracle几个小问题,能者来拿分~
- 安装oracle9i application server 9.0.3时报 generalQueries读写错误 安装终止
- 急救,如何让一个表中的一个字段的值等于另一个字段中的内容呢???我的表中有数万条记录!!
- 关于备份oracle时字符集的问题
- 几个简单的问题
- 外网通过隔离装置防火墙访问内网数据库报错
- orcale 触发器问题 请帮忙看看
- ,我有个 test.txt 文件,现在我想把里面的数据导入到Oracle数据库中的一张表 table1 中, 具体怎么实现 ?
group by p_retailprice 不用分组吧
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
4/66 PL/SQL: ORA-00907: missing right parenthesis
4/67 PLS-00103: Encountered the symbol ")" when expecting one of the
following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
楼主你的书写习惯看着很难受。
plsql developer-->edit->pl/sql beautifier
把你的过程中的改成下面的
SELECT COUNT(1),SUM(p_retailprice),AVG(p_retailprice) INTO CNT_PART,SUM_PART,AVG_PART
FROM part
WHERE PS_PARTKEY IN
(SELECT P_PARTKEY FROM PART WHERE P_NAME LIKE 'B%' )
AND PS_AVAILQTY > (SELECT (0.5 * SUM(L_QUANTITY)) FROM LINEITEM WHERE L_PARTKEY = PS_PARTKEY AND L_SUPPKEY = PS_SUPPKEY
AND L_SHIPDATE >('2003-05-12','yyyy-mm-dd') and to_date(L_SHIPDATE) <=to_date('2003-12-31','yyyy-mm-dd') )
group by p_retailprice;
没有什么意义
对于聚合函数以外的字段必须放置到group by子句中
FROM part
WHERE PS_PARTKEY IN
(SELECT P_PARTKEY FROM PART WHERE P_NAME LIKE 'B%' )
AND PS_AVAILQTY > (SELECT (0.5 * SUM(L_QUANTITY)) FROM LINEITEM WHERE L_PARTKEY = PS_PARTKEY AND L_SUPPKEY = PS_SUPPKEY
AND to_date(L_SHIPDATE) >to_date('2003-05-12','yyyy-mm-dd') and to_date(L_SHIPDATE) <=to_date('2003-12-31','yyyy-mm-dd') )
group by p_retailprice;