oracle pls-00103错误

[凌音]

create or replace procedure quraur
is
cursor1 CURSOR
is
select to_char ( o.OrderDate,'q') quarter, to_char(o.OrderDate,'yyyy') year,sum(s.Price * s.Num * ( 1-s.Disconut ) ) sale from SaleOrderItem s , Orders o where s.OrderID=o.OrdersID group by to_char(o.OrderDate,'q'), to_char(o.OrderDate,'yyyy');
oq Orders.quarter%type;
ye Orders.year%type;
sa SaleOrderItem.sale%number;
begin
open cursor1;
loop
fetch cursor1 into oq,ye,sa;
exit when cursor1%notfound;
if cursor1%found then
dbms_output.put_line(oq||' '||ye||' '||sa);
end if;
end loop;
close cursor1;
end quraur;

警告: 创建的过程带有编译错误。

SQL> show error
PROCEDURE QURAUR 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PLS-00103: 出现符号 "IS"在需要下列之一时：
:= . ( @ % ;
not null range default character

19/11 PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
pragma

[秋雨飘落]

create or replace procedure quraur
is
cursor1 CURSOR
is
select to_char ( o.OrderDate,'q') quarter, to_char(o.OrderDate,'yyyy') year,sum(s.Price * s.Num * ( 1-s.Disconut ) ) sale from SaleOrderItem s , Orders o where s.OrderID=o.OrdersID group by to_char(o.OrderDate,'q'), to_char(o.OrderDate,'yyyy');
---------这里2个IS，语法不对
create or replace procedure quraur
is
begin
declare
CURSOR cursor1
is

[Ade子夜]

[Quote=引用楼主 youlian010 的回复:]
create or replace procedure quraur
is
cursor1 CURSOR
is
select to_char ( o.OrderDate,'q') quarter, to_char(o.OrderDate,'yyyy') year,sum(s.Price * s.Num * ( 1-s.Disconut ) ) sale from SaleOrderIte……
[/Quote]
这里有误！

[我本是朱]

vv_name varchar2(20);
CURSOR cursor1 is select 。。。。

和其它的不同 不是先变量名 后数据类型

[灰哥]

汗,我就没看出问题来..
你的语法是正确的.我按你的随便写了一个,编译通过,而且调用后正常输出.

sa SaleOrderItem.sale%number;
我估计是你这个地方有问题,你把这个也换成:sa SaleOrderItem.sale%type;
然后在进行编译,还有,你得保证你的cursor 的查询语句正确噢,因为我做的时候是拿我自己的表试的.
祝你好运...

下面是我的代码:

CREATE OR REPLACE PROCEDURE CSDN_TEST
IS
CURSOR CUR_EMP IS SELECT ENAME ,EMPNO ,DEPTNO,sal FROM EMP ;
ANAME EMP.ENAME%TYPE;
ANO EMP.EMPNO%TYPE;
ADNO EMP.DEPTNO%TYPE ;
--asal emp.sal%number;
BEGIN
  OPEN CUR_EMP;
  LOOP
    FETCH CUR_EMP INTO ANAME,ANO,ADNO ,asal;
    EXIT WHEN CUR_EMP%NOTFOUND;
    
    IF CUR_EMP%FOUND THEN
      DBMS_OUTPUT.put_line(ANAME||'--'||ANO||'---'||ADNO);
    END IF ;
  END LOOP;
END;

--调用代码
/**
BEGIN
  CSDN_TEST ;
END;

*/

[凌音]

改过游标的定义后，又有一大堆错，是权限问题吗？我用sys as sysdba 登录的
SQL> create or replace procedure quraur
is
CURSOR cursor1 is
select to_char ( o.OrderDate,'q') as quarter, to_char(o.OrderDate,'yyyy') as year,sum(s.Price * s.Num * ( 1-s.Disconut ) ) as sale from SaleOrderItem s , Orders o where s.OrderID=o.OrdersID group by to_char(o.OrderDate,'q'), to_char(o.OrderDate,'yyyy');
oq Orders.quarter%type;
ye Orders.year%type;
sa SaleOrderItem.sale%number;
begin
open cursor1;
loop
fetch cursor1 into oq,ye,sa;
exit when cursor1%notfound;
if cursor1%found then
dbms_output.put_line(oq||' '||ye||' '||sa);
end if;
end loop;
close cursor1;
end;
/
警告: 创建的过程带有编译错误。

SQL> show error
PROCEDURE QURAUR 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4 PL/SQL: Item ignored
5/11 PLS-00302: 必须声明 'QUARTER' 组件
6/4 PL/SQL: Item ignored
6/11 PLS-00302: 必须声明 'YEAR' 组件
7/4 PL/SQL: Item ignored
7/23 PLS-00208: 标识符 'NUMBER' 不是合法的游标属性
11/5 PL/SQL: SQL Statement ignored
11/24 PLS-00320: 此表达式的类型声明不完整或格式不正确
14/8 PL/SQL: Statement ignored
14/29 PLS-00320: 此表达式的类型声明不完整或格式不正确

[我本是朱]

oq Orders.quarter%type;
ye Orders.year%type;
sa SaleOrderItem.sale%number;
---------------------------------
5/4 PL/SQL: Item ignored
5/11 PLS-00302: 必须声明 'QUARTER' 组件
6/4 PL/SQL: Item ignored
6/11 PLS-00302: 必须声明 'YEAR' 组件
--------------------------
这两个字段在是你sql中的别名，在表orders中根本不存在，应换成orders中对应的字段
number。。前面两个都是%type，后面怎么编程number？你要的时那个字段的类型，还是要定义为number？