存储过程

Question

有两张表，其中一个中间表 student1，一个原始表student2，表结构相同，主键都是id,dept,own
我首先要查出中间表里的数据，然后到原始表去，找出主键与中间表重复的数据，删除他，然后将中间表导入到原始表中,请问各位高手这个如果用存储过程怎么实现，我以前没接触过存储过程，实在不知道该怎么处理

Answer 1

--直接用SQL语句就可以实现
--merge into  不需要过程
merge INTO student2 a
USING (SELECT * FROM student1 ) b
ON (a.id=b.id AND a.dept=b.dept AND a.OWN=b.OWN )
WHEN NOT matched THEN
INSERT VALUES(b.id,b.dept,b.OWN,........) 
WHEN matched THEN 
   UPDATE SET a.其它栏位=b.对应栏位

Answer 2

很容易：SQL> select * from student1;        ID DEPT                                                                             OWNER
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 xxx                                                                              ddd
         2 xxx                                                                              ddd
         3 xxx                                                                              ddd
         4 xxx                                                                              dddSQL> select * from student2;        ID DEPT                                                                             OWNER
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 xxx                                                                              ddd
         2 xxx                                                                              ddd
         3 xxx                                                                              dddSQL> create or replace procedure mergeOne2Two(o_ret out number,
  2                                           o_msg out varchar2) as
  3  begin
  4    for cur in (select * from student1) loop
  5      update student2 b
  6         set (b.dept, b.owner) = (select a.dept, a.owner
  7                                    from student1 a
  8                                   where a.id = cur.id
  9                                     and a.id = b.id) where b.id = cur.id;
 10      if (sql%NOTFOUND) then
 11        insert into student2
 12          select * from student1 a where a.id = cur.id;
 13      end if;
 14    end loop;
 15  exception
 16    when others then
 17      o_ret := sqlcode;
 18      o_msg := sqlerrm(sqlcode);
 19  end;
 20  /Procedure createdSQL> 
SQL> declare
  2    o_ret number;
  3    o_msg varchar2(200);
  4  begin
  5    mergeOne2Two(o_ret, o_msg);
  6  end;
  7  /PL/SQL procedure successfully completedSQL> commit;Commit completeSQL> select * from student2;        ID DEPT                                                                             OWNER
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 xxx                                                                              ddd
         2 xxx                                                                              ddd
         3 xxx                                                                              ddd
         4 xxx                                                                              dddSQL> 

Answer 3

用merge也可以,不过得是存在的时候update,不存在的时候insert

Answer 4

2楼的可以，最简单。我觉得也可以这样：1：删除student2中有，student1也有的数据，（即重复数据）
delete from student2 st2 where exists (
select null from student1 st1 where st2.id = st1.id
and st2.dept = st1.dept
and st2.own = st1.own
)2:将student1的数据插入到student2中
insert into student2  as select * from student1;

Answer 5

  create PROCEDURE Insert_student
  as 
    declare @id varchar(20),@dept varchar(20),@own varchar(20)
    declare cur_moduleds cursor for  
    select id,dept,own from student1 
    open  cur_moduleds
    fetch next from cur_moduleds into  @id,@dept,@own
    while @@fetch_status = 0 
    begin
      delete from  student2 where  id=@id and dept=@dept and own=@own
      fetch next from cur_moduleds into @id,@dept,@own
    end
    close cur_moduleds
    deallocate cur_moduleds
    
    insert  into student2 
    select * from student1

Answer 6

--大体上应该是这个逻辑了
CREATE OR REPLACE function TEST.TEST2
RETURN Number
IS  liRtn            Number(1);                     -- Rtn値  tempD     Student.d%TYPE;     --d
  tempDept  Student.dept%TYPE;  --dept
  tempOwn   Student.own%TYPE;   --own  Cursor CurStudent2 IS
    select * from Student2;  Curstu2 CurStudent2%TYPE;Begin
  --查处student2所有数据
  OPEN CurStudent2;
  LOOP
    --循环
     FETCH CurStudent2 INTO Curstu2;
     EXIT WHEN CurStudent2%NOTFOUND;       BEGIN
         --查出student表中与student2表中主key相同的数据
         SELECT d,dept,own
         INTO  tempD,tempDept,tempOwn
         FROM Student S
         WHERE S.d = Curstu2.d
           AND S.dept = Curstu2.dept
           AND S.own = Curstu2.own        if tempD != null then
           ---------------如果查到，就将student表中的数据删除
            DELETE Student S
         WHERE S.d = Curstu2.d
           AND S.dept = Curstu2.dept
           AND S.own = Curstu2.own           ---------------然后将student2表中的数据插入的student表中
            INSERT INTO Student S 
             VALUES(Curstu2.d,Curstu2.dept,Curstu2.own,...)
        end if
       
       Exception
          When Others Then
            liRtn := 9;
       End;
       Exit when liRtn != 0;      End Loop;
      Close CurStudent2;   return liRtn;End TEST2;
/