错误提示如下:Compilation errors for PROCEDURE HU_CLINIC.P_GET_OPERATESError: 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() isError: 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_USERSError: 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);
BEGINCREATE 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() isError: 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_USERSError: 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);
BEGINCREATE 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;
( CODE VARCHAR(20) NULL;
NAME VARCHAR(50) NULL;
SPM VARCHAR(50) NULL;
}
ON COMMIT ROWS ; 这种是不是要用动态语句写
( CODE VARCHAR(20) NULL;
NAME VARCHAR(50) NULL;
SPM VARCHAR(50) NULL;
}
ON COMMIT ROWS';
这样试试
也要改成 execute immediate 'drop table operate_users';还有这个SELECT * FROM OPERATE_USERS; 是做什么的。
( CODE VARCHAR(20) NULL;
NAME VARCHAR(50) NULL;
SPM VARCHAR(50) NULL;
}
ON COMMIT ROWS ; 修改为:
CREATE global TEMPORARY TABLE OPERATE_USERS
( CODE VARCHAR(20) NULL;
NAME VARCHAR(50) NULL;
SPM VARCHAR(50) NULL;
}
ON COMMIT preserve ROWS ;
Select max(ID) as id From HU_PUB.HVS_USERS
into v_maxid
Where Type = 0 and UserCode = V_CODE;
修改如下:
select id into v_maxid from
(select max(ID) as id From HU_PUB.HVS_USERS
Where Type = 0 and UserCode = V_CODE
);
支持2楼,3楼,用到太语句execute immediate '
CREATE global TEMPORARY TABLE OPERATE_USERS
( CODE VARCHAR(20) NULL;
NAME VARCHAR(50) NULL;
SPM VARCHAR(50) NULL;
}
ON COMMIT preserve ROWS ' ;
v_code varchar2(50);
v_name varchar2(50);
v_srm varchar2(50);
v_maxid varchar2(50);
v_count number(10);
cursor users_cursor is select code,name,srm from hu_pub.hts_workers;BEGIN execute immediate ' CREATE TABLE OPERATE_USERS
( CODE VARCHAR(20) NULL,
NAME VARCHAR(50) NULL,
SPM VARCHAR(50) NULL)'; open users_cursor;
loop
fetch users_cursor into v_code,v_name,v_srm;
exit when users_cursor%notfound;
Select max(ID) into v_maxid From HU_PUB.HVS_USERS Where Type = 0 and UserCode = V_CODE;
if v_maxid is not null THEN
------------------查权限------------------------------------
select 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
execute immediate ' insert into OPERATE_USERS(code,name)
values('||V_CODE||','||V_NAME||','||V_SRM||')';
COMMIT;
END IF; END IF;
end loop;
close users_cursor;
--SELECT * FROM OPERATE_USERS;
execute immediate 'DROP TABLE OPERATE_USERS'; end P_GET_OPERATES;
您好!你帮我写的这个现在语法完全正确,但是我要把临时表的数据集返回,应该怎么办呢?但是你把这句注释掉了
--SELECT * FROM OPERATE_USERS; 这要怎么样才能把数据返回呢
type ref_cur is ref cursor;
procedure P_GET_OPERATES(cur_out out ref_cur);
end pack;
/create or replace package body pack is procedure P_GET_OPERATES(cur_out out ref_cur) is
v_code varchar2(50) := null;
v_name varchar2(50) := null;
v_srm varchar2(50) := null;
v_maxid varchar2(50) := null;
v_count number(10) := null;
v_sql varchar2(2000) := null;
cursor users_cursor is select code,name,srm from hu_pub.hts_workers; BEGIN execute immediate ' CREATE TABLE OPERATE_USERS
( CODE VARCHAR(20) NULL,
NAME VARCHAR(50) NULL,
SPM VARCHAR(50) NULL)'; open users_cursor;
loop
fetch users_cursor into v_code,v_name,v_srm;
exit when users_cursor%notfound;
Select max(ID) into v_maxid From HU_PUB.HVS_USERS Where Type = 0 and UserCode = V_CODE;
if v_maxid is not null THEN
------------------查权限------------------------------------
select 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
execute immediate ' insert into OPERATE_USERS(code,name,spm)
values('||V_CODE||','||V_NAME||','||V_SRM||')';
COMMIT;
END IF; END IF;
end loop;
close users_cursor; v_sql := 'SELECT * FROM OPERATE_USERS';
open cur_out for v_sql;
execute immediate 'DROP TABLE OPERATE_USERS'; end P_GET_OPERATES;
end pack;
请问这个PACKAGE在PLSQL中怎么调用呢,谢谢!
pack.P_GET_OPERATES(cur_test);最后那个表的数据是只查出来看的还是需要对那些数据做进一步处理的?
如果只是查出来看,那没有必要用Package和ref cursor,直接在procedure中打印出来就可以
如果是需要做进一步处理的,那OPERATE_USERS删除的太早了
declare cur_test PACK.ref_cur;
PACK.P_GET_OPERATES(cur_test);
execute immediate ' insert into OPERATE_USERS(code,name)
values('||V_CODE||','||V_NAME||','||V_SRM||')';
后面
dbms_output.put_line(V_CODE||','||V_NAME||','||V_SRM);