想写个存储过程 把两个表中查到的数据插入到另一个表中 ,可怎么也不好使 。存储过程本身貌似没有错误 请大家指教!CREATE OR REPLACE PROCEDURE MIS_ADDEMPRECORD_PROCEDURE AS
CURSOR MIS_ADDEMPRECORD_CURSOR
IS
SELECT MM.ID,MM.NAME,MP.ID,MP.NAME,MP.PRODUCTLINE
FROM MIS_MEMBER MM, MIS_PROJECT MP,MIS_PROJECT_MEMBER MPM
WHERE MM.ID=MPM.MEMBER_ID AND MP.ID=MPM.PROJECT_ID;
MEMBER_IDD VARCHAR2(32);
MEMBER_NAMEE VARCHAR2(20);
PROJECT_IDD VARCHAR2(32);
PROJECT_NAMEE VARCHAR2(20);
PRODUCTLINEE VARCHAR2(20);
WEEKSS VARCHAR2(10);
WEEKSTARTT DATE;
WEEKENDD DATE;
BEGIN
OPEN MIS_ADDEMPRECORD_CURSOR;
LOOP
FETCH MIS_ADDEMPRECORD_CURSOR INTO MEMBER_IDD,MEMBER_NAMEE,PROJECT_IDD,PROJECT_NAMEE,PRODUCTLINEE;
SELECT TO_CHAR(SYSDATE,'IW') INTO WEEKSS FROM DUAL;
SELECT SYSDATE-4 INTO WEEKSTARTT FROM DUAL;
SELECT SYSDATE+2 INTO WEEKENDD FROM DUAL;
EXIT WHEN MIS_ADDEMPRECORD_CURSOR%NOTFOUND;
INSERT INTO MIS_EMPLOYEEWORKRECORD(ID,MEMBER_ID, MEMBER_NAME, WEEKS, WEEKSTART, WEEKEND, PROJECT_ID, PROJECT_NAME, PRODUCTLINE)
VALUES(MIS_SEQ.NEXTVAL,MEMBER_IDD,MEMBER_NAMEE,WEEKSS,WEEKSTARTT,WEEKENDD,PROJECT_IDD,PROJECT_NAMEE,PRODUCTLINEE);
END LOOP;
CLOSE MIS_ADDEMPRECORD_CURSOR;END MIS_ADDEMPRECORD_PROCEDURE;oraclesql存储存储过程游标
CURSOR MIS_ADDEMPRECORD_CURSOR
IS
SELECT MM.ID,MM.NAME,MP.ID,MP.NAME,MP.PRODUCTLINE
FROM MIS_MEMBER MM, MIS_PROJECT MP,MIS_PROJECT_MEMBER MPM
WHERE MM.ID=MPM.MEMBER_ID AND MP.ID=MPM.PROJECT_ID;
MEMBER_IDD VARCHAR2(32);
MEMBER_NAMEE VARCHAR2(20);
PROJECT_IDD VARCHAR2(32);
PROJECT_NAMEE VARCHAR2(20);
PRODUCTLINEE VARCHAR2(20);
WEEKSS VARCHAR2(10);
WEEKSTARTT DATE;
WEEKENDD DATE;
BEGIN
OPEN MIS_ADDEMPRECORD_CURSOR;
LOOP
FETCH MIS_ADDEMPRECORD_CURSOR INTO MEMBER_IDD,MEMBER_NAMEE,PROJECT_IDD,PROJECT_NAMEE,PRODUCTLINEE;
SELECT TO_CHAR(SYSDATE,'IW') INTO WEEKSS FROM DUAL;
SELECT SYSDATE-4 INTO WEEKSTARTT FROM DUAL;
SELECT SYSDATE+2 INTO WEEKENDD FROM DUAL;
EXIT WHEN MIS_ADDEMPRECORD_CURSOR%NOTFOUND;
INSERT INTO MIS_EMPLOYEEWORKRECORD(ID,MEMBER_ID, MEMBER_NAME, WEEKS, WEEKSTART, WEEKEND, PROJECT_ID, PROJECT_NAME, PRODUCTLINE)
VALUES(MIS_SEQ.NEXTVAL,MEMBER_IDD,MEMBER_NAMEE,WEEKSS,WEEKSTARTT,WEEKENDD,PROJECT_IDD,PROJECT_NAMEE,PRODUCTLINEE);
END LOOP;
CLOSE MIS_ADDEMPRECORD_CURSOR;END MIS_ADDEMPRECORD_PROCEDURE;oraclesql存储存储过程游标
Merge into table_a
using (select xxx from a,b where a.xxx=b.xxx) table_b
on(table_a.xxx=table_b.xxx)
when matched then
insert into a values (table_b.xxx,table_b.xxx);