Question

请高手解决个oracle存储过程的问题，源码如下

CREATE OR REPLACE PROCEDURE GET_INHERITED_APPLICATIONS(VAR_APP_ID nvarchar2,VAR_INHERITED_STATE INT)
AS
ROOT_LEVEL nvarchar2(32);
n int;
APP_TEMP APPLICATIONS%rowtype;
begin
n:=1;
SELECT RESOURCE_LEVEL into ROOT_LEVEL FROM APPLICATIONS WHERE ID = VAR_APP_ID;
SELECT * INTO APP_TEMP
FROM APPLICATIONS
WHERE (RESOURCE_LEVEL LIKE ROOT_LEVEL+'%'
AND bitand(INHERITED_STATE,VAR_INHERITED_STATE) = VAR_INHERITED_STATE)
OR ID = VAR_APP_ID
ORDER BY RESOURCE_LEVEL;
loop
exit when n = 0;
DELETE FROM APP_TEMP WHERE substr(RESOURCE_LEVEL,1,LENGTH(RESOURCE_LEVEL)-3) NOT IN (SELECT RESOURCE_LEVEL FROM APP_TEMP) AND RESOURCE_LEVEL <> ROOT_LEVEL;

n:=sql%rowcount;
end loop;
SELECT * FROM APP_TEMP
WHERE ID <> VAR_APP_ID;
end;

我想创建临时表，并对其做增删改查的操作，然后返回这个临时表，但是在DELETE那里总是报错说表或视图不存在，请高手指教啊！

Answer 1

APP_TEMP这个只是个变量,又不是表.你做DELETE当然会错 ,
你应该先考虑用动态SQL建一张临时表.
比如:
execute immediate 'create table xxx as
SELECT *
FROM APPLICATIONS
WHERE (RESOURCE_LEVEL LIKE ROOT_LEVEL+''%''
AND bitand(INHERITED_STATE,VAR_INHERITED_STATE) = VAR_INHERITED_STATE)
OR ID = VAR_APP_ID
ORDER BY RESOURCE_LEVEL';
然后在对这个表进行DELETE操作

追问

哦哦 我试试 那最后那句select怎么写呢？oracle里不能直接写查询语句，那我怎么得到查询结果呢？

追答

你直接在PL/SQL上面查这个表就行了社
存储过程里面是不行的

追问

闹心啊，领导非让把sqlserver改成oracle ，oracle真是一窍不通啊，各种错啊。。。亲麻烦能给写个直接能执行通过的么。。。你给的这句话我应该加到哪啊。。。

追答

CREATE OR REPLACE PROCEDURE GET_INHERITED_APPLICATIONS(VAR_APP_ID VARCHAR2,
VAR_INHERITED_STATE INTEGER) AS
ROOT_LEVEL VARCHAR2(32);
N INTEGER;
--APP_TEMP APPLICATIONS%ROWTYPE;
BEGIN
N := 1;
SELECT RESOURCE_LEVEL
INTO ROOT_LEVEL
FROM APPLICATIONS
WHERE ID = VAR_APP_ID;

EXECUTE IMMEDIATE
'CREATE TABLE APP_TEMP AS
SELECT *
FROM APPLICATIONS
WHERE (RESOURCE_LEVEL LIKE '''||ROOT_LEVEL||'%''
AND BITAND(INHERITED_STATE, VAR_INHERITED_STATE) ='||
VAR_INHERITED_STATE||')
OR ID='||VAR_APP_ID||'
ORDER BY RESOURCE_LEVEL';

EXECUTE IMMEDIATE
'DELETE FROM APP_TEMP
WHERE SUBSTR(RESOURCE_LEVEL, 1, LENGTH(RESOURCE_LEVEL) - 3) NOT IN
(SELECT RESOURCE_LEVEL FROM APP_TEMP)
AND RESOURCE_LEVEL '||ROOT_LEVEL;
COMMIT;

END;

Answer 2

SELECT * INTO APP_TEMP

要保证，插入到APP_TEMP是一个值，不能多个值呢。你这个明显不对啊，你把APP_TEMP当成一个表来用了，它只是一个变量啊，只能存一行值呢。
要保存多个值，你得学学Oracle的游标了。