이응기역

diff -b -w -E 1 2

Alt + 방향키 (↑,...) 

https://code.visualstudio.com/shortcuts/keyboard-shortcuts-windows.pdf

DECLARE ----
        V_RATE				NUMBER(10,5)    := 9.00008;
	

BEGIN


	DBMS_OUTPUT.PUT_LINE('V_RATE = ' || V_RATE);
	DBMS_OUTPUT.PUT_LINE('V_RATE = ' || CEIL(V_RATE));


END;

SELECT A  INTO MY_VAR --  (O)

SELECT A  INTO :MY_VAR  --(X)

https://toolbox.googleapps.com/apps/har_analyzer/

DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT)||' rows affected.');

IF condition1 THEN
   {...statements to execute when condition1 is TRUE...}

ELSIF condition2 THEN
   {...statements to execute when condition1 is FALSE and condition2 is TRUE...}

END IF;

(https://www.techonthenet.com/oracle/loops/if_then.php)

CREATE OR REPLACE 
TYPE US_SLNB.VARCHAR_ARRAY AS TABLE OF VARCHAR(100)
;

/*------------------------------------------------------------------------------
------------------------------------------------------------------------------*/
CREATE OR REPLACE function US_SLNB.TEST_F1 (p_strings VARCHAR_ARRAY)
RETURN  NUMBER
     IS
    PRAGMA AUTONOMOUS_TRANSACTION;

  V_STR         VARCHAR2(100) := '';
  V_WH_CD       VARCHAR2(100) := '';
  V_RESULT_QTY  NUMBER:= 0;

begin

  for n in 1..p_strings.count
  LOOP
    V_WH_CD := p_strings(n);
    V_STR   :=  V_STR ||  V_WH_CD || ',';
    DBMS_OUTPUT.PUT_LINE('V_STR =' || V_STR);

  end loop;
    DBMS_OUTPUT.PUT_LINE('V_STR =' || SUBSTR(V_STR, 1, LENGTH(V_STR)-2));
    V_STR := SUBSTR(V_STR, 1, LENGTH(V_STR)-1) ;
    DBMS_OUTPUT.PUT_LINE('V_STR ====[' || V_STR || ']');

WITH STOC AS (
    SELECT        'a' AS CD, 10 AS QTY FROM DUAL
UNION ALL SELECT  'b' AS CD, 20 AS QTY FROM DUAL
UNION ALL SELECT  'c' AS CD, 40 AS QTY FROM DUAL
)
		SELECT SUM(QTY)
      INTO V_RESULT_QTY
FROM STOC
WHERE CD IN
(select regexp_substr(V_STR,'[^,]+', 1, level) from dual
connect by regexp_substr(V_STR, '[^,]+', 1, level) is not null)
;

 DBMS_OUTPUT.PUT_LINE('V_RESULT_QTY =' || V_RESULT_QTY);
 return V_RESULT_QTY;
end;


/*------------------------------------------------------------------------------
------------------------------------------------------------------------------*/
select TEST_F1(VARCHAR_ARRAY('a', 'c')) from dual;

DECLARE
V1 VARCHAR2(10);
BEGIN
SELECT TO_CHAR(SYSDATE, 'YYYYYMMDD') INTO V1 FROM dual ;
  DBMS_OUTPUT.PUT_LINE('----------------- ' || V1);

END;

콘솔 창 'Console' 이라고 적힌 부분 마우스로 끌어서 고정 

(https://stackoverflow.com/questions/28935839/how-to-attach-the-console-in-eclipse)