Tuesday, November 27, 2012

PL/SQL After almost a decade ...

PL/SQL After almost a decade :)


create or replace
PROCEDURE PRC_UPDATE_SEC_ASSET_MGR
IS
  l_single_quote CHAR(1) := '''';
  CURSOR cur_fetch_core_mem(p_invno sec_assetmanager.INV_NO%type)
  IS
    SELECT cores,memory FROM SEC_ASSETMANAGER WHERE INV_NO=p_invno;
  v_core_mem cur_fetch_core_mem%rowtype;
  CURSOR cur_invno
  IS
    SELECT INV_NO FROM SEC_ASSETMANAGER;
  v_inv_no cur_invno%rowtype;
BEGIN
  FOR v_inv_no IN cur_invno
  LOOP
    FOR v_core_mem IN cur_fetch_core_mem(v_inv_no.inv_no)
    LOOP
      -- dbms_output.put_line(v_inv_no.inv_no ||','||v_core_mem.cores||','||v_core_mem.memory);
      dbms_output.put_line ( 'update SEC_ASSETMANAGER set total_cores='||v_core_mem.cores||','|| 'total_memory='|| v_core_mem.memory ||' where  inv_no='||l_single_quote||v_inv_no.inv_no||l_single_quote||';');
    END LOOP;
  END LOOP;
END;

Output:

SQL> set pagesize 999
SQL> set linesize 999
SQL> exec PRC_UPDATE_SEC_ASSET_MGR


update SEC_ASSETMANAGER set total_cores=6,total_memory=56 where  inv_no='X-SSC-C12-Xj-002-OW-39';
update SEC_ASSETMANAGER set total_cores=20,total_memory=20 where  inv_no='P-SSC-C12-Pi-1-OW-1';
update SEC_ASSETMANAGER set total_cores=20,total_memory=20 where  inv_no='P-SSC-C12-Pi-001-OW-2';
update SEC_ASSETMANAGER set total_cores=20,total_memory=20 where  inv_no='P-SSC-C12-Pi-001-OW-3';
update SEC_ASSETMANAGER set total_cores=20,total_memory=20 where  inv_no='P-SSC-C12-Pi-001-OW-4';
update SEC_ASSETMANAGER set total_cores=10,total_memory=10 where  inv_no='P-SSC-C12-Pi-001-OW-5';
update SEC_ASSETMANAGER set total_cores=20,total_memory=20 where  inv_no='P-SSC-C12-Pi-001-OW-6';
update SEC_ASSETMANAGER set total_cores=20,total_memory=20 where  inv_no='P-SSC-C12-Pi-001-OW-7';
update SEC_ASSETMANAGER set total_cores=20,total_memory=20 where  inv_no='P-SSC-C12-Pi-001-OW-8';
update SEC_ASSETMANAGER set total_cores=25,total_memory=25 where  inv_no='P-SSC-C12-Pi-001-OW-9';

No comments:

Post a Comment