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 character19/11 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
pragma
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 character19/11 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
pragma
解决方案 »
- 数据库awr报告分析求教!
- win7 64 位oracle 11g 报 no -listener
- 做个job定时执行一条sql语句,请问该怎么写?
- PLSQL修改Oracle数据库表的内容问题
- ORACLE中如何建立表不记日志
- 又一个很简单的oracle 小问题!!
- between and 的问题
- 在线等!100分求plsql函数例子,这个 函数根据java端传过来的参数,执行查询,然后将查询结果集返回给java端。
- 密码问题 大家近来看看
- 装载数据库时发生areasQueries错误
- 分组查询将多行字段合并到一行的SQL写法,大家看看该怎么写~~~
- 请教:PLSQL中判断一个%ROWTYPE结果集的记录条数最好怎么实现?急等
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
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;*/
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: 此表达式的类型声明不完整或格式不正确
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?