FOR ROW IN ( SELECT * FROM pm_work_registration WHERE work_registration_code = 'GFDJ202103310115' )
loop
INSERT INTO pm_registration_member ( id, main_id, work_registration_code, peoplecode, ismainhand, isapprentice, issectionchief, createdate, ext1 )
sys_guid ( ) id,
t.id main_id,
t.work_registration_code,
t.registration_user_code peoplecode,
t2.ismainhand,
t2.isapprentice,
t2.issectionchief,
SYSDATE createdate,
'初始化数据' ext1
FROM
(
SELECT
id,
work_registration_code,
regexp_substr( registration_user_code, '[^,]+', 1, ROWNUM ) registration_user_code,
regexp_substr( registration_user, '[^,]+', 1, ROWNUM ) registration_user
FROM
( SELECT * FROM pm_work_registration WHERE work_registration_code = ROW.work_registration_code ) registration_user_code CONNECT BY ROWNUM <= to_number( length( regexp_replace( registration_user_code, '[^,]+' ) ) + 1 )
) t
INNER JOIN sys_people t2 ON t.registration_user_code = t2.peoplecode
WHERE
t.work_registration_code NOT IN ( SELECT work_registration_code FROM pm_registration_member );
END loop;
END;
DECLARE BEGIN
FOR ROW IN ( SELECT * FROM QUA_CHECKLIST )
loop
dbms_output.put_line(row.ID || '----' || row.NAME );
FOR itemRow IN (SELECT rownum,t.* FROM (
SELECT qi.ITEMNAME,qc.* FROM ALMES.QUA_CHECKLISTITEM qc
LEFT JOIN ALMES.QUA_ITEM qi ON qc.ITEMID = qi.ID
WHERE qc.CHECKLISTID = ROW.ID
--'5dd23207-f8d9-4e59-ab1a-e89962bdd7b9'
ORDER BY qi.ITEMNAME ASC
)t)
LOOP
--SELECT t.SORTNO FROM QUA_CHECKLISTITEM t WHERE t.CHECKLISTID = '5dd23207-f8d9-4e59-ab1a-e89962bdd7b9';
UPDATE QUA_CHECKLISTITEM t SET t.SORTNO = itemRow.rownum WHERE t.ID = itemRow.ID;
--dbms_output.put_line(itemRow.ID || '-----' || itemRow.rownum || '----' || itemRow.ITEMNAME );
END LOOP;
END loop;
END;
DECLARE
myTabelName NVARCHAR2(200):='';
myTableRowComment NVARCHAR2(200):='';
result VARCHAR2(2000):='';
CURSOR myCursor IS SELECT * FROM 结果集(表名/视图名/查询结果) ORDER BY (结果集中的列名);
myRecord myCursor%ROWTYPE;
BEGIN
OPEN myCursor;
---------------------游标打开成功
FETCH myCursor into myRecord;
BEGIN
myTabelName:='TEC_PARTTEC';
myTableRowComment:=myRecord.ID||' '||myRecord.CREATEDATE||' '
||myRecord.CREATEUSER||' '||myRecord.ORGANIZATIONID||' '
||myRecord.PARTID||' '||myRecord.PROCESSTYPE||' '
||myRecord.VERSION||' '||myRecord.REMARK||' '
||myRecord.STATE;
result:='表名 '||myTabelName||' 列值 '||myTableRowComment;
result:=myTabelName||' '||myTableRowComment;
DBMS_OUTPUT.PUT_LINE(result);
END;
ELSE
EXIT;
ELSE
DBMS_OUTPUT.PUT_LINE('游标没有打开');