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:
Post a Comment