错误提示如下: Compilation errors for PROCEDURE HU_CLINIC.P_GET_OPERATES Error: PLS-00103: Encountered the symbol ")" when expecting one of the following:
<an identifier> <a double-quoted delimited-identifier>
current
Line: 1
Text: create or replace procedure P_GET_OPERATES() is Error: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> < <
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 9
Text: CREATE TEMPORARY TABLE OPERATE_USERS Error: Hint: Comparison with NULL in 'P_GET_OPERATES'
Line: 28
Text: if (v_maxid!='')
写了一个游标,创建一个临时表,返回数据集,
存储过程如下
create or replace procedure P_GET_OPERATES() is
v_code varchar2(50);
v_name varchar2(50);
v_srm varchar2(50);
v_maxid varchar2(50);
v_count number(10);
BEGIN CREATE TEMPORARY TABLE OPERATE_USERS
( CODE VARCHAR(20) NULL;
NAME VARCHAR(50) NULL;
SPM VARCHAR(50) NULL;
}
ON COMMIT ROWS ;
DECLARE cursor users_cursor is
select code,name,srm
from hu_pub. hts_workers
BEGIN
open users_cursor;
loop
fetch users_cursor into v_code,v_name,v_srm;
Select max(ID) as id From HU_PUB.HVS_USERS
into v_maxid
Where Type = 0 and UserCode = V_CODE;
if (v_maxid!='')
THEN
------------------查权限------------------------------------
select count(*) as count
into v_count
from HU_PUB.HVS_USERRIGHTS A
inner join (
select *
from HU_PUB.HVS_USERS
start with ID = v_maxid
connect by prior PARENTID = ID) B ON (A.ID=B.ID)
where rightscode in (8001,8002,8003)
----如果具有权限,将用户名加到结果列表----------
IF (v_count>0)
THEN
insert into OPERATE_USERS(code,name) values(V_CODE,V_NAME,V_SRM);
COMMIT;
END IF;
--------------------------------------------------
END IF;
END;
SELECT
exit when users_cursor%notfound;
end loop;
END;
close users_cursor;
SELECT * FROM OPERATE_USERS;
DROP TABLE OPERATE_USERS;
end P_GET_OPERATES;
<an identifier> <a double-quoted delimited-identifier>
current
Line: 1
Text: create or replace procedure P_GET_OPERATES() is Error: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> < <
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 9
Text: CREATE TEMPORARY TABLE OPERATE_USERS Error: Hint: Comparison with NULL in 'P_GET_OPERATES'
Line: 28
Text: if (v_maxid!='')
写了一个游标,创建一个临时表,返回数据集,
存储过程如下
create or replace procedure P_GET_OPERATES() is
v_code varchar2(50);
v_name varchar2(50);
v_srm varchar2(50);
v_maxid varchar2(50);
v_count number(10);
BEGIN CREATE TEMPORARY TABLE OPERATE_USERS
( CODE VARCHAR(20) NULL;
NAME VARCHAR(50) NULL;
SPM VARCHAR(50) NULL;
}
ON COMMIT ROWS ;
DECLARE cursor users_cursor is
select code,name,srm
from hu_pub. hts_workers
BEGIN
open users_cursor;
loop
fetch users_cursor into v_code,v_name,v_srm;
Select max(ID) as id From HU_PUB.HVS_USERS
into v_maxid
Where Type = 0 and UserCode = V_CODE;
if (v_maxid!='')
THEN
------------------查权限------------------------------------
select count(*) as count
into v_count
from HU_PUB.HVS_USERRIGHTS A
inner join (
select *
from HU_PUB.HVS_USERS
start with ID = v_maxid
connect by prior PARENTID = ID) B ON (A.ID=B.ID)
where rightscode in (8001,8002,8003)
----如果具有权限,将用户名加到结果列表----------
IF (v_count>0)
THEN
insert into OPERATE_USERS(code,name) values(V_CODE,V_NAME,V_SRM);
COMMIT;
END IF;
--------------------------------------------------
END IF;
END;
SELECT
exit when users_cursor%notfound;
end loop;
END;
close users_cursor;
SELECT * FROM OPERATE_USERS;
DROP TABLE OPERATE_USERS;
end P_GET_OPERATES;
解决方案 »
- 有关sql 更新的一条语句
- 关于oracle创建自定义的函数
- 求一sql关于调用递归树的 要求性能最优
- 高分求Oracle 7 for windows安装程序,找了好久
- sql求助
- 调用存储过程报错:PLS-00306和ORA-06550
- 第一次使用SQL*PLUS,怎么登陆不进去?
- 如何解决Oracle 8i 的SQLPlus Worksheet中简体中文显示为乱码的问题?
- 如何在水晶报表中设置数据源为Oracle的存储过程?
- oracle insert into
- 【请教高高手】如何在PL/SQL环境里直接使用已经创建的PRC、PKG、FUN等,请老师们指教???
- 急 帮我看看这个存储过程!
2.cursor申明应该在begin外;
3.判断是否为空不能用!='',应该是is not null;
4.select一定要有Into承接结果和我不一致的地方就是你要改的
DROP TABLE OPERATE_USERS;
这两句都不能直接放在过程里
你想如何返回数据集呢?
执行完过程,再SELECT * FROM OPERATE_USERS;
不就好了
或者在过程里用dbms_output.put_line()一行一行打印出来
还有临时表不能这么删除
execute immediate ' CREATE TEMPORARY TABLE OPERATE_USERS
( CODE VARCHAR(20) NULL;
NAME VARCHAR(50) NULL;
SPM VARCHAR(50) NULL;
}
ON COMMIT ROWS ';