SQL OFFSET & KEEP & SUBPROGRAM

⏰ 2024.11.04 (D+18)

1. offset

중요 🔖]
값을 가져올 행의 위치.기본값은 1
default는 값이 없을 경우 기본값.기본값은 널

⭐ offset 구문

SELECT e.*
, LAG(empno) OVER(ORDER BY empno) AS prev
, LEAD(empno) OVER(ORDER BY empno) AS next
FROM emp e

SELECT e.*
, LAG(empno) OVER(ORDER BY empno) AS 이전사원번호
, LEAD(empno) OVER(ORDER BY empno) AS 다음사원번호
, LAG(ename) OVER(ORDER BY empno) AS 이전사원명
, LEAD(ename) OVER(ORDER BY empno) AS 다음사원명
FROM emp e

※위 분석 함수들은 오라클에서만 제공되는 함수이다 즉 다른 RDBMS에서는 사용할 수 없다

LAG(컬럼명,[,offset][,default]) OVER([PARTITION BY 칼럼1, 칼럼2...] ORDER BY절)
이전 행의 값을 리턴

LEAD(컬럼명,[,offset][,default]) OVER([PARTITION BY 칼럼1, 칼럼2...] ORDERBY 절)
다음 행의 값을 리턴

LAG, LEAD 함수를 사용하여 사원번호와 이름으로 오름차순1

SELECT e.*
, LAG(empno) OVER(ORDER BY empno) AS prev
, LAG(ENAME) OVER(ORDER BY empno) AS prev_NAME
, LEAD(empno) OVER(ORDER BY empno) AS next
, LEAD(ENAME) OVER(ORDER BY empno) AS next_NAME
FROM emp e;

서브쿼리를 사용하여 사원번호와 이름으로 오름차순 예시2

SELECT e.*
, (SELECT MAX(EMPNO) FROM EMP WHERE EMPNO < E.EMPNO ) prev
, (SELECT MIN(EMPNO) FROM EMP WHERE EMPNO > E.EMPNO ) NEXT
FROM emp e;

2. KEEP()함수

중요 🔖]
- KEEP함수는 특정 쿼리에서 행 그룹을 필터링 하거나 특정 조건을 만족하는 행을 선택하기 위해 사용된다.
- 주로 집계 함수 (SUM, AVG, MAX, MIN)와 함께 사용되며 분석적인 연산이 아니라 데이터 필터링 또는 조건에 따라 데이터를 선택 하는 데 사용된다

부서별 최대급여 출력 예시 📖]

SELECT
deptno,
MAX(sal)
FROM emp
GROUP BY deptno;

서브쿼리를 사용하여 부서별 최대급여 및 이름 출력 예시 📖]

SELECT
    deptno,
    MAX(sal) 최고연봉,
    (SELECT ename FROM emp WHERE sal IN(SELECT MAX(sal) 
FROM emp
WHERE deptno=e.deptno GROUP BY deptno)) 최고연봉자
FROM emp e
GROUP BY deptno;

부서별 최대급여 출력 KEEP함수 사용예시 📖]

SELECT
    deptno,
    MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal DESC) AS 최고연봉
FROM emp
GROUP BY deptno;

부서별 최대급여 및 이름 출력 KEEP함수 사용예시 📖]
SELECT
deptno,
MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal DESC) 최고연봉,
MAX(ename) KEEP (DENSE_RANK LAST ORDER BY SAL) 최고연봉자
FROM emp
GROUP BY deptno;

부서별로 그룹핑 하여 출력 입사한지 가장 오래된 직원의 연봉, 가장 최근에 입사한 직원의 연봉을 출력(KEEP() 함수 사용) 📖]

SELECT
    deptno,
    MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY hiredate) AS "부서 최초 입사자",
    MAX(sal) KEEP (DENSE_RANK LAST ORDER BY hiredate) AS "부서 가장 최근 입사자"
FROM emp
GROUP BY deptno;

3. CURSOR

🔖 정의 ]
SELeCT 문장에 의해 여러행이 RETURN되는 경우 각 행에 접근하기 위한 것

💡 CURSOR 선언

CURSOR 커서명 IS
SELECT문장 ------------------DECLARE부에서 한다
(INTO절이 없는 SELECT문)

⭐ OPEN CURSOR
질의를 수행하라는 의미 즉 OPEN시에 CURSOR 선언시의 SELECT문장이 실행되어 결과셋을 얻게 된다
CURSOR는 그 결과세의 첫번째 행에 바로 위에 위치하게 된다.

OPEN 커서명;

⭐ FETCH ~ INTO ~
결과셋에서 하나의 행을 읽어 들이는 작업 ,
결과 셋에서 인출(FETCH)후에 CURSOR는 다음 행으로 이동

FETCH 커서명 INTO {varaiable1[,variable2,....]};

⭐ CURSOR 닫기

결과 셋의 자원을 반납.
SELECT 문장이 다 처리 된 후 CURSOR를 CLOSE
CLOSE 커서명;

부서 번호를 입력받아 출력하는 예제 📖]

❗스크립트 활성화
SET SERVEROUTPUT ON

➖ 사용자에게 부서코드 입력 받기
ACCEPT DEPTNO PROMPT '부서코드 입력?'

➖ 선언부 CURSOR 정의하기

DECLARE
    ➖ 커서 정의
    CURSOR MYCURSOR IS
    SELECT ENAME, SAL, DNAME, LOC
    FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO
    WHERE E.DEPTNO=20
    ORDER BY SAL;

    ➖ 변수 선언
    이름 EMP.ENAME%TYPE;
    연봉 EMP.SAL%TYPE;
    부서명 DEPT.DNAME%TYPE;
    위치 DEPT.LOC%TYPE;

BEGIN
    ➖ 커서 오픈
    OPEN MYCURSOR;

    ➖ FETCH하기
    FETCH MYCURSOR INTO 이름, 연봉, 부서명, 위치;

    ➖ 타이틀 출력
    DBMS_OUTPUT.PUT_LINE('===========================================');
    DBMS_OUTPUT.PUT_LINE('NAME | SALARY | DNAME | LOC');
    DBMS_OUTPUT.PUT_LINE('===========================================');

    WHILE MYCURSOR%FOUND LOOP
        ➖ 출력
         DBMS_OUTPUT.PUT_LINE(이름 || '    ' ||   연봉   || '    ' ||   부서명   || '    ' || 위치);
        --FETCH하기
         FETCH MYCURSOR INTO 이름, 연봉, 부서명, 위치;
    END LOOP;
        DBMS_OUTPUT.PUT_LINE('===========================================');

        ➖ 커서 닫기
        CLOSE MYCURSOR;
END;
/

4. SUBPROGRAM

🔖 중요 ]
- PL/SQL에는 (스토어드)프로시저 와 함수라는 두가지 유형의 SUBPROGRAM이 있다
- SELECT구문 뿐만 아니라 기타 DML문등을 이용하거나 혹은 프로그래밍적인 요소등을 사용하여
처리하기 복잡한 여러가지 데이터베이스 작업등을 처리 할 수 있도록 만들어진 데이터베이스 객체이다

💡 Function
- 사용자가 PL/SQL구문을 사용하여 직접 오라클 에서 제공하는 내장 함수와 같은 기능을 정의 한 것
- 함수는 IN 파라미터만 사용할 수 있으며, 반드시 반환될 값의 데이터 타입을 RETURN문에 선언해야 하며 단일 값만 반환 된다.

⌨️ 함수 구문

CREATE [OR REPLACE] FUNCTION 함수명
[(매개변수1 IN 자료형, --IN 생략 가능
매개변수2 IN 자료형 ---자료형 정의시 자리수 지정 안함)]
RETURN 자료형-- 자리수 지정 안함
IS
[변수 선언]
BEGIN
함수 내용
RETURN(값);
END;

⌨️ FUNCTION 정의

CREATE OR REPLACE FUNCTION GETSUM(A NUMBER,B IN NUMBER)
RETURN NUMBER
IS
HAP NUMBER;
BEGIN
HAP:=0;
FOR i IN A .. B LOOP
HAP:=HAP+i;
END LOOP;
RETURN HAP;
END;
/

FUNCTION 호출

방법1)
SQL>SELECT GETSUM(1,100) FROM DUAL

방법2)
SQL>VAR HAP CHAR(2);
SQL>EXCUTE :HAP := GETSUM(1,100);
SQL>PRINT HAP;
CREATE OR REPLACE FUNCTION GETGENDER(JUMIN VARCHAR2)
RETURN NCHAR
IS ----함수란(;는 붙이면 안됨)
RTVAL VARCHAR2(4);
BEGIN
IF TO_NUMBER(SUBSTR(TRIM(JUMIN),7,1)) = 1 THEN
RTVAL:='남자';
ELSE
RTVAL:='여자';
END IF;
RETURN RTVAL;
END;
/

⚙️ 다른 계정에 함수 실행 권한 주기
grant execute on 소유계정.함수명 to 부여받는계정;
grant execute on scott.asterisk to hr;

SUBPROGRAM 예제 📖 ]

❗스크립트 활성화
SET SERVEROUTPUT ON

➖ 프로시저 구문

CREATE OR REPLACE FUNCTION GETHAP(NUM1 NUMBER, NUM2 NUMBER)
    RETURN NUMBER
IS
    --변수 선언 및 초기화
    HAP NUMBER :=0;

BEGIN
    FOR I IN NUM1 .. NUM2 LOOP
        HAP := HAP + I;
    END LOOP;

    RETURN HAP;

END;
/

➖ 오류 확인
SHOW ERROR

➖ SELECT 결과 확인
SELECT GETHAP(1,10) FROM DUAL;

➖ 함수로 실행하여 결과 확인
VAR RTVAL NUMBER
EXECUTE :RTVAL := GETHAP(1,10);
PRINT RTVAL

5. 저장 프로시저([Stored] Procedure)]

🔖 정의 ]

프로시저는 RETURN문이 없다 OUT 매개변수로 값을 RETURN한다.

저장 프로시져(STORED PROCEDURE)의 장점 ➕

1) 매우 좋은 성능
2) 보안성을 높일 수 있음.
3) 다양한 처리가 가능
4) 네트웍의 부하를 줄일 수 있음.

저장 프로시저 예시 📖 ]

❗스크립트 활성화
SET SERVEROUTPUT ON

➖ 프로시저 구문

CREATE OR REPLACE PROCEDURE SP_INSERT_EMP
(EMPNO_ IN EMP.EMPNO%TYPE, ENAME_ IN EMP.ENAME%TYPE, SAL_ IN EMP.SAL%TYPE, RTVAL OUT NVARCHAR2)
IS
BEGIN
    INSERT INTO EMP(EMPNO, ENAME, SAL) VALUES (EMPNO_, ENAME_, SAL_);
    COMMIT;
    RTVAL := '입력 성공'; 

EXCEPTION
    WHEN OTHERS THEN 
        ROLLBACK;
        RTVAL := '입력 실패'; 
END;
/

➖ 데이터 삽입여부 조회
SELECT * FROM EMP;

➖ 데이터 삽입
EXEC SP_INSERT_EMP(1324,'가길동',9000,:RT_STR);
EXEC SP_INSERT_EMP(1235,'DSHFDSHJG',9000,:RT_STR);
PRINT RT_STR

저장 프로시져 응용 예시 📖 ]

① INS 프로시저

CREATE TABLE MEMBER (
    USERNAME VARCHAR2(10) PRIMARY KEY,
    PASSWORD VARCHAR2(10) NOT NULL,
    NAME NVARCHAR2(100) NOT NULL,
    REGIDATE DATE DEFAULT SYSDATE
);

CREATE OR REPLACE PROCEDURE SP_INS_MEMBER(
    USERNAME IN MEMBER.USERNAME%TYPE,
    PASSWORD MEMBER.PASSWORD%TYPE,
    NAME MEMBER.NAME%TYPE,
    RTVAL OUT NVARCHAR2)
IS

BEGIN
    INSERT INTO MEMBER VALUES(USERNAME, PASSWORD, NAME, DEFAULT);
    IF SQL%FOUND THEN
        RTVAL := '입력 성공';
        COMMIT;
    END IF;

    EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RTVAL := '입력 실패:아이디가 중복되거나 값이 너무 큽니다';

END;
/

② UDT 프로시저

CREATE OR REPLACE PROCEDURE SP_UDT_MEMBER(
    USERNAME_ IN MEMBER.USERNAME%TYPE,
    PASSWORD_ MEMBER.PASSWORD%TYPE,
    NAME_ MEMBER.NAME%TYPE,
    RTVAL OUT NCHAR
)
IS

BEGIN
    UPDATE MEMBER SET PASSWORD = PASSWORD_, USERNAME = USERNAME_, NAME = NAME_
    WHERE USERNAME = USERNAME_;

    IF SQL%FOUND THEN
        RTVAL := '입력 성공';
        COMMIT;

    ELSE --존재하지 않는 아이디로 수정시
        RTVAL := '존재하지 않은 아이디 입니다'; 
    END IF;

    EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RTVAL := '수정 실패:값이 너무 큽니다';
END;
/

➖ PL/SQL 변수 선언
SELECT * FROM MEMBER;
VAR RTVAL  NVARCHAR2
VAR RTVAL1  NCHAR(100)

➖ UDT문 실행
EXEC SP_UDT_MEMBER('ICT','4321','ICT맨',:RTVAL1);
PRINT RTVAL

③ DEL 프로시저

CREATE TABLE BBS (
    ID NUMBER PRIMARY KEY,
    USERNAME VARCHAR2 (10) REFERENCES MEMBER (USERNAME) NOT NULL,
    TITLE NVARCHAR2 (100) NOT NULL,
    POSTDATE DATE DEFAULT SYSDATE
    );

DROP SEQUENCE SEQ_BBS;

➖ SEQ_BBS 시퀀스 생성
CREATE SEQUENCE SEQ_BBS
NOCACHE
NOCYCLE;

➖ 데이터 생성
INSERT INTO BBS VALUES (SEQ_BBS.NEXTVAL, 'KIM', '제목1', SYSDATE);
INSERT INTO BBS VALUES (SEQ_BBS.NEXTVAL, 'LEE', '제목2', SYSDATE);

➖ 저장
COMMIT;

CREATE OR REPLACE PROCEDURE SP_DEL_MEMBER (
    p_USERNAME IN MEMBER.USERNAME%TYPE,
    RTVAL OUT NUMBER  -- 1: 삭제 성공, 0: 아이디 미존재, -1: 에러 발생
) 
IS
BEGIN
    ➖ MEMBER 테이블에서 USERNAME이 p_USERNAME인 레코드를 삭제
    DELETE FROM MEMBER
    WHERE USERNAME = p_USERNAME;

    ➖ 삭제가 성공한 경우
    IF SQL%ROWCOUNT > 0 THEN
        RTVAL := 1;  -- 삭제 성공
        COMMIT;

    ➖ 존재하지 않는 아이디로 삭제 시도한 경우
    ELSE 
        RTVAL := 0;  -- 아이디 미존재
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RTVAL := -1;  -- 오류 발생 시 -1 반환
END SP_DEL_MEMBER;

프로시저([Stored] Procedure)] : 회원 일치여부 프로시저 예시 📖 ]

❗스크립트 활성화
SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE IS_MEMBER(
    USERNAME_ IN MEMBER.USERNAME%TYPE,
    PASSWORD_ IN MEMBER.PASSWORD%TYPE,
    RTVAL OUT NUMBER
)

IS
    FLAG NUMBER (1);
BEGIN
    SELECT COUNT (*) INTO FLAG FROM MEMBER
    WHERE USERNAME = USERNAME_;
    IF FLAG = 0 THEN
    RTVAL := -1;
    DBMS_OUTPUT.PUT_LINE('아이디가 존재하지 않아요');

    ELSE --아이디 일치
    SELECT COUNT(*) INTO FLAG FROM MEMBER
    WHERE USERNAME=USERNAME_ AND PASSWORD = PASSWORD_;
    IF FLAG=0 THEN --비번 불일치
        RTVAL := 0;
        DBMS_OUTPUT.PUT_LINE('아이디가 일치하나 비번 불일치');
    ELSE
        RTVAL := 1;
        DBMS_OUTPUT.PUT_LINE(USERNAME_ || '님 즐감!');
    END IF;

    END IF;

END;
/

SELECT * FROM MEMBER;
VAR RTVAL NVARCHAR2
VAR RTVAL1 NCHAR (30)
VAR RTVAL2 NUMBER

EXEC IS_MEMBER ('KIM','1234',:RTVAL2);
PRINT RTVAL2

6. SOL 문제

📙 문제 1.

함수 정의
파라미터로 문자열을 받아서
첫글자만 표시하고 나머지는 *로 변환해서 반환하는 함수 정의
예] Smith -> S**, JOHN -> J**
1.DUAL 테이블과 EXEC명령어를 사용해서 결과 확인
2.EMP테이블의 ENAME컬럼에 적용하여라

**➖ 프로시저 구문**
CREATE OR REPLACE FUNCTION TO_ASTERISK(VAL IN NVARCHAR2)
    RETURN NVARCHAR2
  
IS

BEGIN
    RETURN RPAD(SUBSTR(VAL,1,1),LENGTH(VAL),'*');
END;
/

**➖ SELECT 결과 확인**
SELECT TO_ASTERISK('HELLO') FROM DUAL;

**➖ 함수로 실행하여 결과 확인**
VAR RT_STR NVARCHAR2
EXECUTE :RT_STR := TO_ASTERISK('HELLO');
PRINT RT_STR

**➖ SCOTT 계정에서 to_asterisk 컬럼 조회**
SELECT ENAME, to_asterisk(ENAME) FROM EMP;

**➖ system 계정**
--문제1.SCOTT 계정에서 to_asterisk 컬럼을 HR계정으로 조회할 수 있도록 권한 부여
GRANT EXECUTE ON SCOTT.to_asterisk TO HR;

**➖ HR계정**
문제1. SCOTT 계정에서 to_asterisk 컬럼을 HR계정으로 조회하는 방법
SELECT first_name, SCOTT.to_asterisk(first_name) FROM EMPLOYEES;

📙 문제 2.

DATE타입을 파라미터로 받아서 '2019-07-10' 형태의 문자열로 반환하는
함수를 정의하여라(Date->String으로 변환)
그리고
1.DUAL 테이블과 EXEC명령어를 사용해서 결과 확인
2.EMP테이블의 hiredate컬럼에 적용하여라

➖ 프로시저 구문
CREATE OR REPLACE FUNCTION TO_STRING(VAL IN DATE)
RETURN NVARCHAR2

IS

BEGIN
RETURN TO_CHAR(VAL, 'YYYY-MM-DD');
END;
/

➖ SELECT 결과 확인
SELECT TO_STRING(SYSDATE) FROM DUAL;

➖ 함수로 실행하여 결과 확인
VAR RT_STR NVARCHAR2
EXECUTE :RT_STR := TO_STRING(SYSDATE);
PRINT RT_STR
➖ EMP 테이블 HIREDATE에 적용하여 확인
SELECT HIREDATE, TO_STRING(HIREDATE) FROM EMP;

📙 문제 3.

CREATE OR REPLACE FUNCTION EXCEPTFUNCTION(VAL NVARCHAR2)
    RETURN NVARCHAR2
 
IS
    ➖ 변수 선언
    toNumber NUMBER;
BEGIN
    SELECT TO_NUMBER(VAL) INTO toNumber FROM DUAL;

    ➖ 숫자형식 여부 확인
    DBMS_OUTPUT.PUT_LINE(VAL+10);

    RETURN VAL+10;

    ➖ 예외 처리부
    EXCEPTION
    WHEN OTHERS THEN
        RETURN '숫자형식이 아니야';
 
END;
/
 
➖ SELECT 결과 확인
SELECT EXCEPTFUNCTION('100') FROM DUAL;
SELECT EXCEPTFUNCTION('가') FROM DUAL;
 
EXEC :RT_STR := EXCEPTFUNCTION('가');
PRINT RT_STR