在编译CREATE OR REPLACE PROCEDURE CLOSE_DEPT()
BEGIN
DECLARE distance int;
TRUNCATE TABLE DEPT_CLOSURE;
SET distance = 0;
-- seed closure with self-pairs (distance 0)
INSERT INTO DEPT_CLOSURE (SUDEPTCODE, DEPTCODE, DISTANCE)
SELECT DEPTCODE, DEPTCODE, 0
FROM DEPT; -- for each pair (root, leaf) in the closure,
-- add (root, leaf->child) from the base table
REPEAT
SET distance = distance + 1;
INSERT INTO DEPT_CLOSURE (SUDEPTCODE, DEPTCODE, distance)
SELECT DEPT_CLOSURE.SUDEPTCODE, DEPT.DEPTCODE, distance
FROM DEPT_CLOSURE, DEPT
WHERE DEPT_CLOSURE.DEPTCODE = DEPT.SUDEPTCODE
AND DEPT_CLOSURE.distance = DISTANCE - 1;
UNTIL (ROW_COUNT() == 0))
END REPEAT
END
出现下列错误:
PROCEDURE NYXH.CLOSE_DEPT
On line: 1
PLS-00103: 出现符号 ")"在需要下列之一时:
<an identifier>
<a double-quoted delimited-identifier> current
BEGIN
DECLARE distance int;
TRUNCATE TABLE DEPT_CLOSURE;
SET distance = 0;
-- seed closure with self-pairs (distance 0)
INSERT INTO DEPT_CLOSURE (SUDEPTCODE, DEPTCODE, DISTANCE)
SELECT DEPTCODE, DEPTCODE, 0
FROM DEPT; -- for each pair (root, leaf) in the closure,
-- add (root, leaf->child) from the base table
REPEAT
SET distance = distance + 1;
INSERT INTO DEPT_CLOSURE (SUDEPTCODE, DEPTCODE, distance)
SELECT DEPT_CLOSURE.SUDEPTCODE, DEPT.DEPTCODE, distance
FROM DEPT_CLOSURE, DEPT
WHERE DEPT_CLOSURE.DEPTCODE = DEPT.SUDEPTCODE
AND DEPT_CLOSURE.distance = DISTANCE - 1;
UNTIL (ROW_COUNT() == 0))
END REPEAT
END
出现下列错误:
PROCEDURE NYXH.CLOSE_DEPT
On line: 1
PLS-00103: 出现符号 ")"在需要下列之一时:
<an identifier>
<a double-quoted delimited-identifier> current
distance int;
BEGIN
execute immediate 'TRUNCATE TABLE DEPT_CLOSURE';
distance := 0;
-- seed closure with self-pairs (distance 0)
INSERT INTO DEPT_CLOSURE (SUDEPTCODE, DEPTCODE, DISTANCE)
SELECT DEPTCODE, DEPTCODE, 0
FROM DEPT; -- for each pair (root, leaf) in the closure,
-- add (root, leaf->child) from the base table
loop
distance := distance + 1;
INSERT INTO DEPT_CLOSURE (SUDEPTCODE, DEPTCODE, distance)
SELECT DEPT_CLOSURE.SUDEPTCODE, DEPT.DEPTCODE, distance
FROM DEPT_CLOSURE, DEPT
WHERE DEPT_CLOSURE.DEPTCODE = DEPT.SUDEPTCODE
AND DEPT_CLOSURE.distance = DISTANCE - 1;
exit when sql%rowcount=0;
END loop;
commit;
END;
distance int;
BEGIN
delete from DEPT_CLOSURE;
commit;
distance := 0;
-- seed closure with self-pairs (distance 0)
INSERT INTO DEPT_CLOSURE (SUDEPTCODE, DEPTCODE, DISTANCE)
SELECT DEPTCODE, DEPTCODE, 0
FROM DEPT; -- for each pair (root, leaf) in the closure,
-- add (root, leaf->child) from the base table
loop
distance := distance + 1;
INSERT INTO DEPT_CLOSURE (SUDEPTCODE, DEPTCODE, distance)
SELECT DEPT_CLOSURE.SUDEPTCODE, DEPT.DEPTCODE, distance
FROM DEPT_CLOSURE, DEPT
WHERE DEPT_CLOSURE.DEPTCODE = DEPT.SUDEPTCODE
AND DEPT_CLOSURE.distance = DISTANCE - 1;
exit when sql%rowcount=0;
END loop;
commit;
END CLOSE_DEPT;