230804 SQL 강의

SET SERVEROUTPUT ON;
--OUT 매개변수 예제 2
CREATE OR REPLACE PROCEDURE usp_MemberCartTop
    ( p_year       IN VARCHAR2, 
      p_amt        OUT NUMBER ,  
      p_mem_name   OUT member.mem_name%TYPE )               
IS            
    v_year VARCHAR2(5);
BEGIN
     v_year := (p_year || '%');
    SELECT  mem_name, mem_amt  INTO  p_mem_name, p_amt
      FROM (
        SELECT mem_name, SUM(prod_price * cart_qty) mem_amt
          FROM member, cart, prod
         WHERE cart_no LIKE v_year
           AND cart_member = mem_id
           AND cart_prod = prod_id
         GROUP BY mem_name    
         ORDER BY SUM(prod_price * cart_qty)  DESC
      )
      WHERE  ROWNUM <= 1 ;
END; 
/

--실행
VAR send_member VARCHAR2
VAR send_amt NUMBER
EXEC usp_MemberCartTop('2020', :send_amt, :send_member);
PRINT send_member 
PRINT send_amt;
/

---

SELECT cart_no
     , cart_prod
     , cart_member
     , (SELECT mem_name FROM member WHERE mem_id = cart_member)
  FROM cart
 WHERE cart_no = '2020040100001';

--회원 아이디를 받으면 해당 이름을 리턴하는 함수 만들기
CREATE OR REPLACE FUNCTION  fn_memName (p_mem_id   IN   VARCHAR2 ) 
           RETURN VARCHAR2
 IS
         r_name VARCHAR2(30);        
 BEGIN
     SELECT mem_name INTO r_name FROM member 
       WHERE mem_id = p_mem_id;
     RETURN r_name; 
  
 EXCEPTION 
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('예외 발생:' || SQLERRM);
      RETURN null;    
END;
/

--fn_memName 실행 테스트 
VAR m_name VARCHAR2
EXECUTE :m_name := fn_memName('a001') ;
PRINT m_name
/

--실제 함수처럼 SQL구문에서 실행 
SELECT cart_no, cart_prod, cart_member, fn_memName(cart_member)
  FROM cart
 WHERE cart_no = '2020040100001';

---

--년도 및 상품코드를 입력 받으면 해당년도의 평균 판매 횟수를 반환하는 함수
 CREATE OR REPLACE  FUNCTION  fn_prodAvgQty
      ( p_year       IN NUMBER DEFAULT (EXTRACT(YEAR FROM SYSDATE)),
        p_prod_id IN VARCHAR2) 
 RETURN NUMBER
 IS
    r_qty NUMBER(10);
    v_year VARCHAR2(5) := TO_CHAR(p_year) || '%';
 BEGIN
    SELECT NVL(AVG(cart_qty),0) INTO r_qty  FROM cart 
      WHERE cart_prod = p_prod_id AND cart_no like v_year;
    RETURN r_qty; 
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('예외 발생:' || SQLERRM);
      RETURN 0;    
 END;
/

--fn_prodAvgQty 실행 테스트
VAR qty NUMBER
EXEC :qty := fn_prodAvgQty(2004, 'P101000002');
PRINT qty
EXEC :qty := fn_prodAvgQty(2020, 'P101000002');
PRINT qty
/

--실제 함수처럼 SQL구문에서 실행 
SELECT prod_id
     , prod_name
     , fn_prodAvgQty(2004,prod_id) "2004년 평균 판매횟수"
     , fn_prodAvgQty(2020,prod_id) "2020년 평균 판매횟수"
  FROM prod;

---

--상품분류 테이블에 새로운 데이터가 들어오면 ＇상품분류가 추가되었습니다.'란 메시지를 출력되도록 문장 레벨 트리거를 작성하기
CREATE OR REPLACE TRIGGER TG_LPROD_IN
AFTER INSERT 
ON LPROD
BEGIN
DBMS_OUTPUT.PUT_LINE('상품분류가 추가되었습니다');
END;
/

--만들어진 트리거확인
SELECT TRIGGER_NAME FROM USER_TRIGGERS

--상품분류 테이블에 로우를 추가.
SET SERVEROUTPUT ON;

INSERT INTO LPROD(LPROD_ID, LPROD_GU, LPROD_NM) VALUES(
            (SELECT MAX(LPROD_ID) + 1 FROM LPROD),
            (SELECT 'P' || (SUBSTR(MAX(LPROD_GU),2)+1) FROM LPROD),
            '트리거추가값1');

SELECT * FROM LPROD;

--새로운 상품분류가 추가되자 '상품분류가 추가되었습니다'란 메시지가 출력되는 것을 보면 TG_LPROD_IN 트리거가 수행되었음을 확인할 수 있음

---

--새로운 상품분류가 추가되자 '상품분류가 추가되었습니다'란 메시지가 출력되는 것을 보면 TG_LPROD_IN 트리거가 수행되었음을 확인할 수 있음 

--급여 정보를 자동 추가하는 트리거 작성하기
--사원 테이블에 새로운 데이터가 들어오면(즉, 신입 사원이 들어오면) 
--급여 테이블에 새로운 데이터(즉 신입 사원의 급여 정보)를 자동으로 생성하도록 하기 위해서 
--사원 테이블에 트리거를 작성해 봅시다. 
--(신입사원의 급여는 일괄적으로 200으로 합니다.) 

--1. 직원을 저장할 테이블 생성
CREATE TABLE EMP01(
  EMPNO NUMBER(4) PRIMARY KEY,
  EMPNAME VARCHAR2(45),
  EMPJOB  VARCHAR2(60)
);

--급여를 저장할 테이블 생성
CREATE TABLE SAL01(
	SALNO NUMBER(4) PRIMARY KEY,
	SAL   NUMBER(7,2),
	EMPNO NUMBER(4) REFERENCES EMP01(EMPNO)
);

--2. 급여번호를 자동 생성하는 시퀀스를 정의하고 이 시퀀스로부터 일련번호를 얻어 급여번호에 부여합시다. 
CREATE SEQUENCE SAL01_SALNO_SEQ;

--3. 다음과 같이 입력하시오.
CREATE OR REPLACE TRIGGER TRG_02
    AFTER INSERT 
    ON EMP01
    FOR EACH ROW
    BEGIN 
    INSERT INTO SAL01 
         VALUES(SAL01_SALNO_SEQ.NEXTVAL, 100, :NEW.EMPNO); 
END;
/

--4. 사원 테이블에 로우를 추가합니다. 
INSERT INTO EMP01 VALUES(1, '전원지', '화가');
INSERT INTO EMP01 VALUES(2, '전수빈', '프로그래머');

SELECT * FROM EMP01;

SELECT * FROM SAL01;