¡á¿¹ 5_4_1 || FUNCTION »ý¼º ¿¹Á¦
CREATE OR REPLACE FUNCTION LEEOK.GET_CMM_CODE_NAME (
V_CLASS_CODE IN CMM_CODE_DETAIL.CLASS_CODE%TYPE,
V_CODE_NO IN CMM_CODE_DETAIL.CODE_NO%TYPE)
RETURN VARCHAR2
IS
V_CMM_CODE_NAME CMM_CODE_DETAIL.CODE_NAME%TYPE;
BEGIN
____SELECT CODE_NAME
INTO V_CMM_CODE_NAME
FROM CMM_CODE_DETAIL
WHERE CODE_NO = V_CODE_NO
AND CLASS_CODE = V_CLASS_CODE;
RETURN V_CMM_CODE_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ÀÔ·ÂÇÑ ÄÚµå´Â ¾ø½À´Ï´Ù.');
RETURN V_CMM_CODE_NAME;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('ÀÚ·á°¡ 2°Ç ÀÌ»óÀÔ´Ï´Ù.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('±âŸ ¿¡·¯ÀÔ´Ï´Ù.');
END;
/
¡á¿¹ 5_4_2 || FUNCTION »ç¿ë ¿¹
SELECT GET_CMM_CODE_NAME('H01','A06') AS ÀÀ½Ã°á°ú FROM DUAL;
¡á¿¹ 5_4_3 || EXCEPTION ó¸®
SELECT GET_CMM_CODE_NAME('H01','A09') AS ÀÀ½Ã°á°ú FROM DUAL;
¡á¿¹ 5_4_4 || FUNCTIONÀÇ »ç¿ë ¿¹
CREATE OR REPLACE FUNCTION SAWON_COUNT
(v_GBN_CODE INSA.JOIN_GBN_CODE%TYPE)
RETURN VARCHAR2
IS
v_m NUMBER;
v_f NUMBER;
v_t NUMBER;
v_ALL VARCHAR2(30);
BEGIN
v_m :=0;
v_f :=0;
v_t :=0;
SELECT COUNT(DECODE(SEX,'M',1)) AS MEN,
COUNT(DECODE(SEX,'F',1)) AS WOMEN,
COUNT(SEX) AS TOT
INTO v_m, v_f, v_t
FROM INSA
WHERE JOIN_GBN_CODE=v_GBN_CODE;
DBMS_OUTPUT.PUT_LINE('³² : '||v_m ||', ¿© : '|| v_f ||', ÅäÅ» : '|| v_t);
v_ALL := '³² : '||v_m ||' ¿© : '|| v_f ||' ÃÑ¿ø : '|| v_t;
DBMS_OUTPUT.PUT_LINE('v_ALL ¿¡ ´ã°ÜÀÖ´Â °ªÀº :'|| v_ALL);
RETURN v_ALL;
END;
/
¡á¿¹ 5_4_5 || SAWON_COUNT ÇÔ¼ö ½ÇÇà
SELECT SAWON_COUNT('RGL') AS Àοø±¸¼º FROM DUAL;
¡á¿¹ 5_4_6 || ABS ÇÔ¼ö »ç¿ë ¿¹
SELECT ABS(-27) FROM DUAL;
¡á¿¹ 5_4_7 || SIGN ÇÔ¼ö »ç¿ë ¿¹
SELECT SIGN(-10), SIGN(44), SIGN(0) FROM DUAL;
¡á¿¹ 5_4_8 || ROUND ÇÔ¼ö »ç¿ë ¿¹
SELECT ROUND(125.198 , 2) FROM DUAL;
¡á¿¹ 5_4_9 || TRUNC ÇÔ¼ö »ç¿ë ¿¹
SELECT TRUNC(125.198 , 2) FROM DUAL;
¡á¿¹ 5_4_10 || MOD ÇÔ¼ö »ç¿ë ¿¹
SELECT MOD(15,4) FROM DUAL;
¡á¿¹ 5_4_11 || POWER ÇÔ¼ö »ç¿ë ¿¹
SELECT POWER(4, 2) FROM DUAL;
¡á¿¹ 5_4_12 || SQRT ÇÔ¼ö »ç¿ë ¿¹
SELECT SQRT(9) FROM DUAL;
¡á¿¹ 5_4_13 || CHR ÇÔ¼ö »ç¿ë ¿¹
SELECT CHR(88) FROM DUAL;
¡á¿¹ 5_4_14 || TO_NUMBER ÇÔ¼ö »ç¿ë ¿¹
SELECT TO_NUMBER('508') FROM DUAL;
¡á¿¹ 5_4_15 || LOWER ÇÔ¼ö »ç¿ë ¿¹
SELECT LOWER('KING') FROM____ DUAL;