We have created a awesome themeFar far away,behind the word mountains, far from the countries
INFO ½ÇÀü DB¸ðµ¨¸µ°ú SQL°úÁ¤
¡á¿¹ 5_2_1 || ÇÁ·Î½ÃÀú »ý¼º ¿¹Á¦CREATE OR REPLACE PROCEDURE salary_inc (v_sabun IN VARCHAR2)ISBEGINUPDATE INSASET SALARY=SALARY*1.03WHERE SABUN=v_sabun;COMMIT;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('register is failed!'); ROLLBACK;END;/
¡á¿¹ 5_2_2 || ÇÁ·Î½ÃÀú »ý¼º ¿¹Á¦CREATE OR REPLACE PROCEDURE in_sa(v_NAME IN VARCHAR2, v_RGL IN VARCHAR2)ISv_SABUN INSA.SABUN %TYPE;BEGININSERT INTO INSA(SABUN, NAME, JOIN_GBN_CODE, JOIN_DAY)VALUES((____SELECT /*+ INDEX_DESC(INSA INSA_PK) */ DECODE(SUBSTR(SABUN,1,8) ,TO_CHAR(SYSDATE,'YYYYMMDD'),SABUN+1 ,TO_CHAR(SYSDATE,'YYYYMMDD')||'01') FROM INSA WHERE SABUN < TO_CHAR(SYSDATE,'YYYYMMDD')||'99' AND ROWNUM=1), v_NAME, v_RGL, TO_CHAR(SYSDATE,'YYYYMMDD'));
COMMIT;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('FAIL!'); ROLLBACK;END;/
¡á¿¹ 5_2_3 || ÇÁ·Î½ÃÀú ½ÇÇàEXEC in_sa ('±è¹Î±â', 'RGL')
SELECT SABUN, NAME, JOIN_GBN_CODE, JOIN_DAY FROM INSAORDER BY SABUN DESC;
¡á¿¹ 5_2_4 || OUT º¯¼ö¸¦ ÀÌ¿ëÇÏ´Â ÇÁ·Î½ÃÀú »ý¼ºCREATE OR REPLACE PROCEDURE workPm(v_SABUN IN INSA.SABUN %TYPE, v_name OUT INSA.ENG_NAME%TYPE, v_cdname OUT CMM_CODE_DETAIL.CODE_NAME%TYPE, v_m OUT INSA.JOIN_DAY%TYPE)ISBEGINSELECT T1.ENG_NAME, T2.CODE_NAME, ROUND(MONTHS_BETWEEN(SYSDATE, TO_DATE(JOIN_DAY,'YYYYMMDD')))AS MON_WORK INTO v_name, v_cdname, v_m FROM INSA T1, CMM_CODE_DETAIL T2 WHERE T1.POS_GBN_CODE = T2.CODE_NO AND T1.SABUN=v_SABUN; DBMS_OUTPUT.PUT_LINE(v_name||'--'||v_cdname||'--'||v_m);
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Á¶È¸ÇÏ·Á´Â »ç¹øÀÌ Á¸ÀçÇÏÁö ¾ÊÀ½!');
END workPm;/
¡á¿¹ 5_2_5 || ÀÍ¸í ºí·Ï PL/SQL »ý¼ºDECLAREv_name INSA.ENG_NAME%TYPE;v_cdname CMM_CODE_DETAIL.CODE_NAME%TYPE;v_m INSA.JOIN_DAY%TYPE;BEGIN
workPm('2012010119', v_name, v_cdname, v_m);DBMS_OUTPUT.PUT_LINE('À̸§ :'||v_name||' Á÷±Þ :'||v_cdname||' ±Ù¹«¿ù¼ö :'||v_m||'°³¿ù');
END;/
¡á¿¹ 5_2_6 || ÇÁ·Î½ÃÀú È®ÀÎSELECT * FROM USER_PROCEDURES;
¡á¿¹ 5_2_7 || »ý¼ºµÈ PL/SQL °´Ã¼ ³»¿ë Á¶È¸SELECT * FROM____ USER_SOURCE;