오라클 SQL 15~16강

--15강
--DCL
CREATE USER TST IDENTIFIED BY 1234;

 

--16강

--PL/SQL (PROCEDURAL LANGUAGE EXTENSION TO SQL)

--DECLARE(선언부) , BEGIN(실행부) , EXCEPTION(예외 처리부)

SET SERVEROUTPUT ON;

BEGIN

DBMS_OUTPUT.PUT_LINE('WELCOME TO WORLD~!');

END;

/

 

--DECLARE/BEGIN/END

DECLARE

V_EMPNO NUMBER(4) :=7788;

V_ENAME VARCHAR2(10);

BEGIN

V_ENAME := 'SCOTT';

DBMS_OUTPUT.PUT_LINE('V_EMPNO: ' || V_EMPNO);

DBMS_OUTPUT.PUT_LINE('V_ENAME: ' || V_ENAME);

END;

/

 

set serveroutput on;

declare

v_deptno DEPT.DEPTNO%TYPE := 50;

BEGIN

DBMS_OUTPUT.PUT_LINE('V_DEPTNO : ' || V_DEPTNO);

END;

/

 

set serveroutput on;

DECLARE

V_DEPT_ROW DEPT%ROWTYPE;

BEGIN

SELECT DEPTNO,DNAME,LOC INTO V_DEPT_ROW

FROM DEPT

WHERE DEPTNO = 40;

DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO);

DBMS_OUTPUT.PUT_LINE('DNAME : ' || V_DEPT_ROW.DNAME);

DBMS_OUTPUT.PUT_LINE('LOC : ' || V_DEPT_ROW.LOC);

END;

/

 

SET SERVEROUTPUT ON;

DECLARE

V_CONDITION NUMBER := 2;

BEGIN

IF V_CONDITION < 1 THEN

DBMS_OUTPUT.PUT_LINE('대한');

ELSIF V_CONDITION = 1 THEN

DBMS_OUTPUT.PUT_LINE('민국');

ELSE

DBMS_OUTPUT.PUT_LINE('만세');

END IF;

END;

/

 

--반복제어문

--LOOP-END/FOR-LOOP/WHILE-LOOP

--LOOP-END : 반복블록을 반복수행하다가 어떤 조건에 맞으면 중단하게된다.

SET SERVEROUTPUT ON;

DECLARE

V_CNT NUMBER :=0;

V_SUM NUMBER :=0;

BEGIN

LOOP

V_CNT := V_CNT+1;

V_SUM := V_SUM+V_CNT;

DBMS_OUTPUT.PUT_LINE(V_CNT || ' ' || V_SUM);

EXIT WHEN V_CNT = 10;

END LOOP;

END;

/

 

--FOR-LOOP : 반복의 횟수를 알고있을때 사용

SET SERVEROUTPUT ON;

DECLARE

V_CNT NUMBER ;

V_SUM NUMBER :=0;

BEGIN

FOR V_CNT IN 1..10 LOOP

V_SUM := V_SUM+V_CNT;

DBMS_OUTPUT.PUT_LINE(V_CNT || ' ' || V_SUM);

END LOOP;

END;

/

 

--WHILE-LOOP : 조건이 만족되는 동안 반복수행하다가 조건이 거짓이면 종료함.

SET SERVEROUTPUT ON;

DECLARE

V_CNT NUMBER :=0;

V_SUM NUMBER :=0;

BEGIN

WHILE V_CNT < 10 LOOP

V_CNT := V_CNT+1;

V_SUM := V_SUM+V_CNT;

DBMS_OUTPUT.PUT_LINE(V_CNT||' '||V_SUM);

END LOOP;

END;

/