Thursday, March 23, 2017

oracle inserted row OUT SYS_REFCURSOR by execution procedure

CREATE TABLE "BOROO"."CMS_TEMP"
   (    "ID" VARCHAR2(36 BYTE) DEFAULT SYS_GUID() NOT NULL ENABLE,
    "NAME" NVARCHAR2(150),
    "RCDATE" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL ENABLE
   )
 
procedure

create or replace PROCEDURE INSEL_TEMP (p_name NVARCHAR2, v_cur OUT SYS_REFCURSOR)
AS
v_id NVARCHAR2(36);
BEGIN
INSERT INTO CMS_TEMP (NAME) VALUES (p_name) RETURNING ID INTO v_id;
COMMIT;
OPEN v_cur FOR SELECT ID,NAME,RCDATE FROM CMS_TEMP WHERE ID=v_id;
END;



call






DECLARE
  l_cursor  SYS_REFCURSOR;
  l_id   cms_temp.ID%TYPE;
  l_name   cms_temp.NAME%TYPE;
  l_rcdate  cms_temp.RCDATE%TYPE;
BEGIN
  INSEL_TEMP('name 1', l_cursor);
 
  LOOP FETCH l_cursor
    INTO l_id, l_name, l_rcdate;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_id || ' | ' || l_name || ' | ' || l_rcdate);
  END LOOP;
  CLOSE l_cursor;
END;

No comments: