[oracle] FUNCTION, TRIGGER(미완성), UPDATE, TRUNC, GROUP BY

*function 만들기 - common table 에서 common name 을 return.

schema Brower 에서 우클릭 -> create function 이용하면 됩니다..

Sample source -- start

CREATE OR REPLACE FUNCTION SCOTT.get_name

( p_param varchar2)

 RETURN varchar2 IS

     v_return varchar2(100) default '';

BEGIN

  

   select to_char( sysdate, 'yyyymmdd' )

   into   v_return

   from dual;

  

   RETURN v_return;

   EXCEPTION

     WHEN NO_DATA_FOUND THEN

       NULL;

     WHEN OTHERS THEN

       -- Consider logging the error and then re-raise

       RAISE;

END get_name;

Sample source -- end

 

*into 는 row 한 개만 받는다

*procedure 눈 배치성 작업, 일괄 작업 등에 많이 쓰이고, 롤백할 때도 유용하다.

 

 

CREATE OR REPLACE FUNCTION common_name_return (l_comm_code in varchar2)

RETURN varchar2

IS

    l_comm_desc varchar2(100);

BEGIN

    SELECT comm_desc

    INTO l_comm_desc

    FROM common

    WHERE comm_code = l_comm_code;

   

    RETURN l_comm_desc;

END common_name_return;

/

 

select common_name_return('1') from dual;

 

 

 

 

 

 

 

*trigger 만들기 - common table 에서 common name 을 return.

Sample source – start

CREATE OR REPLACE TRIGGER NCCJST2011.TR_BUS_LINE_INFO_U_DRVTIME

BEFORE INSERT OR UPDATE

ON NCCJST2011.BUS_LINE_INFO

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

DECLARE

BEGIN

  

   IF :NEW.EXPWAY = 1 THEN     

        :NEW.EXPDRIVETIME  := :OLD.DRIVEDIST;

        --UPDATE BUS_LINE_INFO  SET EXPDRIVETIME = :NEW.DRIVEDIST WHERE LINEID = :NEW.LINEID;

   END IF;

  

END TR_BUS_LINE_INFO_U_DRVTIME;

/

Sample source – end

 

-- editing..

 

*pyong_info 테이블에 평형별로 pyong_price 와 unit_price 구하시오

pyong_price 평형별 가격

unit_price 평당 가격

ho_info의 평형별 평균가를 기준으로 산정하기...

1000원 미만은 절사 하시오..

 

update pyong_info p set (pyong_price, unit_price) = (

    select trunc(avg(h.price), -3), trunc(avg(h.price) , -3)/p.pyong

     from ho_info h

     where p.pyong_code = h.pyong

     group by p.pyong_code

);