用法一:
DECLARE
CURSOR cu_1 IS
WITH wip_entities_v AS (SELECT * FROM wip_Entities WHERE 1 = 1 AND wip_Entity_name = '911101230207' AND organization_id = 7057)
SELECT *
FROM wip_entities_v;
BEGIN
FOR lr_1 IN cu_1 LOOP
DBMS_OUTPUT.PUT_LINE(lr_1.wip_entity_id);
END LOOP;
END;
用法二:
WITH transfer_scraps_v AS (SELECT wip_entity_id,
dept_id,
SUM (pcb_qty) pcb_qty,
organization_id
FROM (SELECT d.wip_entity_id,
DECODE (:to_sub_code, 'CB', 486, wdo.dept_id) dept_id,
d.pcb_qty,
h.organization_id
FROM apps.ecs_wip_p_move_transactions_l d,
apps.wip_p_move_transactions h,
apps.wip_dept_opcode wdo
WHERE 1 = 1
AND h.transaction_id = d.transaction_id
AND h.organization_id = d.organization_id
AND h.organization_id = wdo.organization_id
AND h.fm_operation_code = wdo.op_code
AND h.to_operation_code = 9999
AND h.status = 'P'
AND h.organization_id = :org_id
AND d.wip_entity_id = :wip_entity_id
UNION ALL
SELECT TO_NUMBER (d.attribute14) wip_entity_id,
DECODE (:to_sub_code, 'CB', 486, TO_NUMBER (d.attribute1)) dept_id,
d.pcb_qty * (-1) pcb_qty,
h.organization_id
FROM apps.wip_p_move_transactions_l d,
apps.wip_p_move_transactions h
WHERE 1 = 1
AND h.transaction_id = d.transaction_id
AND h.organization_id = d.organization_id
AND h.fm_operation_code = 9999
AND h.organization_id = :org_id
AND d.wip_entity_id = :wip_entity_id
UNION ALL
SELECT d.wip_entity_id,
DECODE (:to_sub_code, 'CB', 486, d.res_dept) dept_id,
d.scrap_qty * (-1) pcb_qty,
d.organization_id
FROM apps.transfer_scraps_r d,apps.transfer_scraps_header h
WHERE 1 = 1
AND h.header_id = d.header_id
AND h.organization_id = d.organization_id
AND h.fm_op_id = 9999
AND h.organization_id = :org_id
AND d.wip_entity_id = :wip_entity_id
)
GROUP BY wip_entity_id, dept_id, organization_id)
SELECT ok.dept_id, ewdo.dept_name
FROM transfer_scraps_v ok,
(SELECT DISTINCT dept_id, dept_name, organization_id
FROM apps.wip_dept_opcode) ewdo
WHERE 1 = 1
AND ok.dept_id = ewdo.dept_id
AND ok.organization_id = ewdo.organization_id
AND ok.pcb_qty > 0
AND ok.organization_id = :org_id
AND ok.wip_entity_id = :wip_entity_id
DECLARE
CURSOR cu_1 IS
WITH wip_entities_v AS (SELECT * FROM wip_Entities WHERE 1 = 1 AND wip_Entity_name = '911101230207' AND organization_id = 7057)
SELECT *
FROM wip_entities_v;
BEGIN
FOR lr_1 IN cu_1 LOOP
DBMS_OUTPUT.PUT_LINE(lr_1.wip_entity_id);
END LOOP;
END;
用法二:
WITH transfer_scraps_v AS (SELECT wip_entity_id,
dept_id,
SUM (pcb_qty) pcb_qty,
organization_id
FROM (SELECT d.wip_entity_id,
DECODE (:to_sub_code, 'CB', 486, wdo.dept_id) dept_id,
d.pcb_qty,
h.organization_id
FROM apps.ecs_wip_p_move_transactions_l d,
apps.wip_p_move_transactions h,
apps.wip_dept_opcode wdo
WHERE 1 = 1
AND h.transaction_id = d.transaction_id
AND h.organization_id = d.organization_id
AND h.organization_id = wdo.organization_id
AND h.fm_operation_code = wdo.op_code
AND h.to_operation_code = 9999
AND h.status = 'P'
AND h.organization_id = :org_id
AND d.wip_entity_id = :wip_entity_id
UNION ALL
SELECT TO_NUMBER (d.attribute14) wip_entity_id,
DECODE (:to_sub_code, 'CB', 486, TO_NUMBER (d.attribute1)) dept_id,
d.pcb_qty * (-1) pcb_qty,
h.organization_id
FROM apps.wip_p_move_transactions_l d,
apps.wip_p_move_transactions h
WHERE 1 = 1
AND h.transaction_id = d.transaction_id
AND h.organization_id = d.organization_id
AND h.fm_operation_code = 9999
AND h.organization_id = :org_id
AND d.wip_entity_id = :wip_entity_id
UNION ALL
SELECT d.wip_entity_id,
DECODE (:to_sub_code, 'CB', 486, d.res_dept) dept_id,
d.scrap_qty * (-1) pcb_qty,
d.organization_id
FROM apps.transfer_scraps_r d,apps.transfer_scraps_header h
WHERE 1 = 1
AND h.header_id = d.header_id
AND h.organization_id = d.organization_id
AND h.fm_op_id = 9999
AND h.organization_id = :org_id
AND d.wip_entity_id = :wip_entity_id
)
GROUP BY wip_entity_id, dept_id, organization_id)
SELECT ok.dept_id, ewdo.dept_name
FROM transfer_scraps_v ok,
(SELECT DISTINCT dept_id, dept_name, organization_id
FROM apps.wip_dept_opcode) ewdo
WHERE 1 = 1
AND ok.dept_id = ewdo.dept_id
AND ok.organization_id = ewdo.organization_id
AND ok.pcb_qty > 0
AND ok.organization_id = :org_id
AND ok.wip_entity_id = :wip_entity_id
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23233349/viewspace-1134440/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23233349/viewspace-1134440/