CREATE OR REPLACE PROCEDURE Query1
AS
CNT_PART NUMBER(10) := 0;
SUM_PART NUMBER(10) := 0;
AVG_PART NUMBER(10) := 0;
BEGIN
with A as (SELECT COUNT(*),SUM(p_retailprice),AVG(p_retailprice) INTO CNT_PART,SUM_PART,AVG_PART
FROM part GROUP BY p_retailpric;),
B as ( SELECT S_NAME,S_ADDRESS FROM SUPPLIER,NATION
WHERE S_SUPPKEY IN
(SELECT PS_SUPPKEY
FROM PARTSUPP
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'
)
) AND N_NAME = 'AUSTRALIA' ORDER BY S_NAME;)
select * from A, B
EXCEPTION WHEN OTHERS THEN ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END Query1;
/
--------------
错误提示:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3 PL/SQL: SQL Statement ignored
8/38 PL/SQL: ORA-00907: missing right parenthesis
8/39 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
AS
CNT_PART NUMBER(10) := 0;
SUM_PART NUMBER(10) := 0;
AVG_PART NUMBER(10) := 0;
BEGIN
with A as (SELECT COUNT(*),SUM(p_retailprice),AVG(p_retailprice) INTO CNT_PART,SUM_PART,AVG_PART
FROM part GROUP BY p_retailpric;),
B as ( SELECT S_NAME,S_ADDRESS FROM SUPPLIER,NATION
WHERE S_SUPPKEY IN
(SELECT PS_SUPPKEY
FROM PARTSUPP
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'
)
) AND N_NAME = 'AUSTRALIA' ORDER BY S_NAME;)
select * from A, B
EXCEPTION WHEN OTHERS THEN ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END Query1;
/
--------------
错误提示:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3 PL/SQL: SQL Statement ignored
8/38 PL/SQL: ORA-00907: missing right parenthesis
8/39 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
解决方案 »
- 11g R2 RAC安装,推荐使用asm磁盘组,具体怎么使用能有人介绍下吗
- 全文索引建立的JOB可以成功执行但却不起作用?
- 两个看似一样的语句,执行时间为何相差几十分钟?!
- 请教
- [求助] 求一个效率高的SQL语句
- windows xp下安装了最新的oracle 11,每次启动总是有个oracle.exe的进程占用97%-99%的cpu,这是怎么回事?
- 在VB下调用ORACLE数据库进行密码验证怎么写?
- 请大家一定帮忙!谁能提供一个完整的从Ctree数据库迁移数据到oracle数据库的方案,给建议也给分,特急!
- oracle列转行
- 安装ORALCE 11G 这样的电脑配置会有什么后果
- oracle-session合理配置
- 查询音同字不同的SQL文咋写
CREATE OR REPLACE PROCEDURE Query2
AS
MIN_PART NUMBER(10) := 0;
MAX_PART NUMBER(10) := 0;
BEGIN
SELECT MIN(p_retailprice), MAX(p_retailprice) INTO MIN_PART, MAX_PART From part
Group by p_retailprice;
JOIN ORDERS ON CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY;
WHERE C_CUSTKEY NOT IN(SELECT O_CUSTKEY
FROM ORDERS
WHERE O_ORDERDATE > '31-DEC-2003')
Join LINEITEM
WHERE L_QUANTITY > 200 AND (L_EXTENDEDPRICE < 20 OR L_DISCOUNT < 10);EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);END Query2;
/
如过程里面不能直接写纯粹的select语句的
还有你写join,left join 还是inner join,还是right join,不知道你的意思啊