为何分几次写呢,一次就可完成.
insert into a
select name,code from cc@db_1;以下是循环写法:
CREATE OR REPLACE PROCEDURE asdf
as
cursor t_sor is
select lh from 表A;
begin
for v_sor in t_sor loop
insert into a
select name,code from cc@db_1 where lh=v_sor.lh;
end loop;
commit;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
end;
/
insert into a
select name,code from cc@db_1;以下是循环写法:
CREATE OR REPLACE PROCEDURE asdf
as
cursor t_sor is
select lh from 表A;
begin
for v_sor in t_sor loop
insert into a
select name,code from cc@db_1 where lh=v_sor.lh;
end loop;
commit;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
end;
/
select name,code from cc@db_1 tab2 where tab2.lh in ( select tab3.lh from a tab3);不就行了吗?
报错信息如下:
Compilation errors for PROCEDURE DBAUSERZON100.JD_TESTError: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
:= . ( @ % ; not null range default character
Line: 4
Text: cursot t_sor isError: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin function package pragma procedure form
Line: 23
RETURN INTEGER
IS
T_NAME VARCHAR2(30);
T_CONS_NAME VARCHAR2(30);
C_SQL NUMBER;
S_SQL VARCHAR2(500);
RET NUMBER;
P_ERROR INTEGER;
CURSOR C_CONS IS SELECT A.TABLE_NAME,A.CONSTRAINT_NAME FROM USER_CONSTRAINTS A
WHERE A.R_CONSTRAINT_NAME = (
SELECT B.CONSTRAINT_NAME FROM USER_CONSTRAINTS B
WHERE B.TABLE_NAME=UPPER(I_NAME) AND B.CONSTRAINT_TYPE='P');BEGIN
P_ERROR := 0;
C_SQL := DBMS_SQL.OPEN_CURSOR;
OPEN C_CONS;
LOOP
BEGIN
FETCH C_CONS INTO T_NAME,T_CONS_NAME;
EXIT WHEN C_CONS%NOTFOUND;
IF I_BZ = 0 THEN --把所有与 I_NAME 有关的 CONSTRAINT DISABLE
S_SQL :='ALTER TABLE '||T_NAME||' DISABLE CONSTRAINT '||T_CONS_NAME;
ELSE --恢复与 I_NAME 有关的 CONSTRAINT ENABLE
S_SQL :='ALTER TABLE '||T_NAME||' ENABLE CONSTRAINT '||T_CONS_NAME;
END IF;
DBMS_SQL.PARSE( C_SQL, S_SQL, DBMS_SQL.v7 ) ;
ret := DBMS_SQL.EXECUTE( C_SQL ) ;
EXCEPTION
WHEN OTHERS THEN
P_ERROR := P_ERROR + 1;
END;
END LOOP;
CLOSE C_CONS;
DBMS_SQL.CLOSE_CURSOR(C_SQL);
RETURN P_ERROR;
END ALTER_CONS;
/
SHOW ERROR这是我写的一给函数,你参考下