오라클(SQL)PL/SQL 펑션

PL/SQL 펑션

테이블 다운로드

 

/*
함수 문제
다음과 같이 출력하시오
상품코드, 삼품명, 대분류코드, 대분류명
함수를 사용. 함수명은 FN_PRODNM
*/
쿼리(메서드, 펑션)

CREATE OR REPLACE FUNCTION FN_PRODNM(P_PROD_LGU IN VARCHAR2) 
RETURN VARCHAR2 
IS 
    P_LPROD_NM VARCHAR2(30); 
BEGIN 
    SELECT LPROD_NM INTO P_LPROD_NM 
    FROM   LPROD 
    WHERE  LPROD_GU = P_PROD_LGU; 
RETURN P_LPROD_NM; 
EXCEPTION 
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('발생' || SQLERRM); 
END; 

쿼리

SELECT  PROD_ID AS 상품코드 
     ,  PROD_NAME AS 삼품명 
     ,  PROD_LGU AS 대분류코드 
     ,  FN_PRODNM(PROD_LGU) AS 대분류명 
FROM    PROD;

함수문제
다음과 같이 출력하시오
장바구니코드, 상품코드, 판매개수, 회원아이디, 회원명
함수를 사용, 함수명은 FN_GET_MEM_NM

CREATE OR REPLACE FUNCTION FN_GET_MEM_NM(P_CART_MEMBER IN VARCHAR2)
RETURN VARCHAR2
IS
    P_LPROD_NAME VARCHAR2(30);
BEGIN
    SELECT MEM_NAME INTO P_LPROD_NAME
    FROM   MEMBER
    WHERE  MEM_ID = P_CART_MEMBER;
RETURN P_LPROD_NAME;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('발생' || SQLERRM);
END;
/

SELECT CART_NO 장바구니코드
     , CART_PROD 상품코드
     , CART_QTY 판매개수
     , CART_MEMBER 회원아이디
     , FN_GET_MEM_NM(CART_MEMBER)회원명
FROM   CART;

함수 문제
다음과 같이 출력 (주소를 받아서 거주지역을 리턴)
회원아이디, 회원명, 거주지역
함수를 사용. 함수명은 FN_GET_CITY

CREATE OR REPLACE FUNCTION FN_GET_CITY(P_MEM_ADD1 IN VARCHAR2)
RETURN VARCHAR2
IS
    P_MEM_CITY VARCHAR2(30);
BEGIN
    SELECT SUBSTR(MEM_ADD1,1,2) INTO P_MEM_CITY
    FROM   MEMBER
    WHERE  P_MEM_ADD1 = MEM_ADD1;
RETURN P_MEM_CITY;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('발생' || SQLERRM);
    RETURN NULL;
END;
/
SELECT MEM_ID AS 회원아이디
     , MEM_NAME AS 회원명
     , FN_GET_CITY(MEM_ADD1) AS 거주지역
FROM   MEMBER;

함수 문제
다음과 같이 출력 (회원아이디를 받아서 거주지역을 리턴)
회원아이디, 회원명, 거주지역
함수를 사용. 함수명은 FN_GET_CITY

CREATE OR REPLACE FUNCTION FN_GET_CITY(P_MEM_ID IN VARCHAR2)
RETURN VARCHAR2
IS
    P_MEM_CYTI VARCHAR2(30);
BEGIN
    SELECT  SUBSTR(MEM_ADD1,1,2) INTO P_MEM_CYTI
    FROM   MEMBER
    WHERE  MEM_ID = P_MEM_ID;
RETURN P_MEM_CYTI;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('발생' || SQLERRM);
END;
/
SELECT MEM_ID AS 회원아이디
     , MEM_NAME AS 회원명
     , FN_GET_CITY(MEM_ID) AS 거주지역
FROM   MEMBER;

함수문제
2005년도 삼품분류 별
총판매금액(SUM(PROD_SALE*CART_QTY))을 출력

CREATE OR REPLACE FUNCTION ALL_SALE_AMT(P_LPROD_GU IN VARCHAR2, P_LPROD_YEAR IN VARCHAR2) 
RETURN NUMBER 
IS 
    V_AMT NUMBER; 
BEGIN 
    SELECT SUM(PROD_SALE * CART_QTY) INTO V_AMT 
    FROM   PROD, CART  
    WHERE  PROD_ID = CART_PROD 
    AND    PROD_LGU = P_LPROD_GU 
    AND    CART_NO LIKE P_LPROD_YEAR || '%' 
    GROUP  BY PROD_LGU; 
     
RETURN V_AMT; 
EXCEPTION 
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('발생' || SQLERRM); 
    RETURN NULL; 
END; 
/ 
SELECT LPROD_GU 상품분류코드 
     , LPROD_NM 상품분류명 
     , NVL(ALL_SALE_AMT(LPROD_GU, 2005),0) 총판매금액 
FROM   LPROD;