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
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,不知道你的意思啊