Question

Oracle存储过程的更新插入问题。

一个存储过程，要实现的是数据同步。要求对比两张表A和B，id相同A有做修改的B也要更新，A中新增的B也要跟着更新。附代码
create or replace procedure SQL2ORA is
v_id number;
v_names varchar(50);
cursor c_test is
select id,name from test@sqllink;
c_row c_test%rowtype;
begin
select t.id ,t.name into v_id,v_names FROM testsql t;
FOR c_row IN c_test LOOP
if(c_row.id=v_id) then
update testsql t set t.name=c_row.name;
commit;
else
insert into testsql(id,name) values ( c_row.id,c_row.name);
commit;
end if;
END LOOP;
end SQL2ORA;

Answer 1

--向 TESTSQL 插入 TESTSQL 不存在的数据
INSERT INTO TESTSQL
  (ID, NAME)
  SELECT ID, NAME
    FROM TEST@SQLLINK AA
   WHERE NOT EXISTS (SELECT ID FROM TESTSQL BB WHERE BB.ID = AA.ID);
--修改 TESTSQL 
UPDATE TESTSQL AA
   SET AA.NAME =
       (SELECT BB.NAME FROM TEST@SQLLINK BB WHERE BB.ID = AA.ID);

我觉得有点没必要写存储过程，上面两条sql完全可以实现你的需求，如果非要写成存储过程，按照上面两条sql改写就可以了

追问

所给是能实现，由于需要定时更新要用到JOB调度所以写了存储过程（上述是测试表）新手求罩啊，存储过程很少写到不太熟

追答

CREATE OR REPLACE PROCEDURE SQL2ORA IS
BEGIN
--修改 TESTSQL 
UPDATE TESTSQL AA
   SET AA.NAME =
       (SELECT BB.NAME FROM TEST@SQLLINK BB WHERE BB.ID = AA.ID);
COMMIT;
  --向 TESTSQL 插入 TESTSQL 不存在的数据
INSERT INTO TESTSQL
  (ID, NAME)
  SELECT ID, NAME
    FROM TEST@SQLLINK AA
   WHERE NOT EXISTS (SELECT ID FROM TESTSQL BB WHERE BB.ID = AA.ID);
COMMIT;
END SQL2ORA;

最简单的方法，就是这样了

追问

数据太大了都用select效率有点低，有改进的吗（游标啥的）万分感谢哈

追答

使用游标效率会更慢，建议分段执行吧，比如说时间段等等

CREATE OR REPLACE PROCEDURE SQL2ORA IS
BEGIN
  --修改 TESTSQL 
  UPDATE TESTSQL AA
     SET AA.NAME =
         (SELECT BB.NAME FROM TEST@SQLLINK BB WHERE BB.ID = AA.ID)
   WHERE NOT EXISTS (SELECT CC.ID --判断是否相等，不相等则更新
            FROM TEST@SQLLINK CC
           WHERE CC.ID = AA.ID
             AND CC.NAME = AA.NAME);
  COMMIT;
  --向 TESTSQL 插入 TESTSQL 不存在的数据
  INSERT INTO TESTSQL
    (ID, NAME)
    SELECT ID, NAME
      FROM TEST@SQLLINK AA
     WHERE NOT EXISTS (SELECT ID FROM TESTSQL BB WHERE BB.ID = AA.ID);
  COMMIT;
END SQL2ORA;

Answer 2

问题是什么?编译出错?执行有问题?

从你贴出来的代码里可以看到比较多错误:

1. commit实际上可以放到if...else外面，这样简洁点。

2. 每次都commit效率会很慢，建议是每200-1000commit一次。用一个计数器就行了。

3. 这个感觉上是批量处理，应该对每一条记录捕获下异常，异常信息应该输出或者记录到另外一个表中，不然出了错就停下来，又不知道停在哪里...

4. Cursor打开了没有关闭。

5. 逻辑好像也不太对啊，第一条select语句应该放在FOR END LOOP里面，然后应该加上where t.id = c_row.id

6. 每次都select一次效率不高，可以用两个游标来处理的。两个游标各自按照相同的顺序从A、B表中select出来数据，接下来就是挨个往下比就行了。

追问

执行提示ORA-01422实际返回的行数超出请求的行数;

能帮我改改代码吗，这个存储过程实际上是实现从SQL Server取过来的数据同步到Oracle，sql中新增的Oracle也要新增，sql有修改的Oracle也要跟着修改。。

追答

就是你那个select的语句没有where条件，select into 要求查询结果只能有唯一一条，否则就会报这个错

追问

没有报错了，但是功能无法实现。帮改改啊（另，select语句放loop里面会报错提取违反顺序）

追答

select应该放在LOOP里面的第一句话，应该是不会有问题的。你要不把你改完的代码贴一下。

追问

就按你写的改，select加where条件，comitt放后面。没报错但功能不起作用，代码太长贴不出来，select放第一句会报错
FOR c_row IN c_test LOOP
if(c_row.id=v_id) then
if(c_row.name !=v_names)then
update testsql t set t.name=c_row.name;