오라클 기초#12 ( Cursor, 사용자 계정관리, 계층형쿼리)

[ 커서(Cursor) ]

- 실행한 SQL문의 단위

1) 커서의 단계

- 커서선언하기
- 커서오픈하기
- 커서에서 추출한 결과 저장하기
- 커서닫기

 

예)

DECLARE
    VEMPNO NUMBER(4);
    VENAME VARCHAR2(20);
    VSAL NUMBER(7,2);
    
    CURSOR C1
    IS
    SELECT EMPNO,ENAME,SAL
    FROM EMP
    WHERE DEPTNO=30;
BEGIN
    OPEN C1; --커서오픈하기
    LOOP
        FETCH C1 
        INTO VEMPNO,VENAME,VSAL;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(VEMPNO||' '||VENAME||' '||VSAL);
    END LOOP;
    CLOSE C1; --커서닫기
END;
/

DECLARE
    CURSOR EMP_CURSOR
    IS
    SELECT ENAME,SAL
    FROM EMP
    ORDER BY SAL DESC;
    STAR VARCHAR2(100);
    CNT NUMBER:=0;
BEGIN
    DBMS_OUTPUT.PUT_LINE(' =======  사원별 급여 현황  =======');
    FOR CURVAL IN EMP_CURSOR LOOP
        STAR:=NULL;
        CNT:=TRUNC(CURVAL.SAL/100);
        FOR I IN 1..CNT LOOP
            STAR:=STAR||'*';
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(CURVAL.ENAME);
        DBMS_OUTPUT.PUT_LINE(STAR||' '||CURVAL.SAL);
    END LOOP;
END;
/

[ 사용자 계정관리 ]

1) 계정생성

CREATE USER 아이디 IDENTIFIED BY 비밀번호;

 

예)

CREATE USER TEST IDENTIFIED BY TEST1234;

-- 사용자 생성권한이 있는 시스템계정으로 로그인

SQL> conn / as sysdba
Connected.
 
SQL> create user test identified by test1234;
User created.

-- test계정에 접속과 리소스사용권한부여하기

SQL> grant connect,resource to test;
Grant succeeded.
SQL> conn test/test1234

2) 비밀번호 변경하기

ALTER USER 아이디 IDENTIFIED BY 수정할 비밀번호;

SQL> conn / as sysdba
Connected.
 

SQL> alter user test identified by test0000;

User altered

 

3) 계정삭제하기

DROP USER 아이디;

 

예)

drop user test;
 
-- 데이터가 하나라도 존재하면 계정삭제가 안됨.이때 CASCADE옵션 사용
SQL> drop user test CASCADE;

[ 계층형 쿼리 ]

- 계층적구조로 값을 출력

형식)

SELECT 컬럼명,..
  FROM 테이블명
START WITH 시작조건     -- 루트설정
CONNECT BY PRIOR 참조조건; -- 참조관계설정

 

( 자식쪽에 prior )

 

예1)

SELECT *
FROM EMP
START WITH ENAME='KING'     -- 시작조건(루트설정)
CONNECT BY PRIOR EMPNO=MGR; -- 참조관계설정 조건
 
 

예2)

SELECT LPAD(' ',(LEVEL-1)*2)||EMPNO,ENAME,MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO=MGR;