[SQLD] 21.11.20 제43회 SQL개발자 후기, 공부방법, 정리본, 43회 출제문제
2021년 11월 20일 토요일 제43회 SQL개발자 자격증 시험 후기 오늘 보고왔다! 내 공부방법과 정리본, 이번에 시험에 출제된 문제들을 기억나는대로 적어보겠다 개인적으로 기출이나 복원된 문제가
lotuus.tistory.com
[목차]
💚 함수 : 입력값이 아무리 많아도 출력은 1가지만 나온다.
💚 단일행함수 : 입력값 단일행
문자형함수
숫자형함수
날짜, 반환, NULL함수
다중조건함수 : CASE 표현식
💚 다중행함수 : 입력값 다중행
집계함수, 그룹함수
윈도우함수
윈도우함수구문
WINDOWING절 (SQL Server 지원 X)
💚 절차형 SQL
PROCEDURE 프로시저
USER DEFINED FUNCTION 사용자 정의 함수
TRIGGER 트리거
💚 함수 : 입력값이 아무리 많아도 출력은 1가지만 나온다.
💚 단일행함수 : 입력값 단일행
문자형함수
숫자형함수
날짜, 반환, NULL함수
📌 '201501' = TO_CHAR(서비스종료일시, 'YYYYMM') : 서비스종료일시의 년과 월이 2015년 01월과 같은 것들을 찾아낸다. 01월 01일 ~ 01월 31일이 해당된다.
📌 'TO_DATE('201501', 'YYYYMM') = 서비스종료일시 : 지정되지 않은 DD는 01로 지정되어 2015년 01월 01일이 서비스종료일시와 같은 것들을 찾아낸다.
다중조건함수 : CASE 표현식
💚 다중행함수 : 입력값 다중행
집계함수, 그룹함수
윈도우함수
관계형 데이터베이스는 칼럼과 칼럼의 연산은 쉬운반면 행과 행의 연산은 매우 어려웠다.
PL/SQL, T-SQL 등의 절차형 프로그램을 작성하거나 INLINE VIEW로 해결해야했던 것을 함수로 쉽게 해결할 수 있다.
윈도우함수구문
SELECT 윈도우함수명(매개변수) OVER (
[PARTITION BY 칼럼명] //GROUP BY 역할
[ORDER BY절] //ORDER BY 역할
[WINDOWING절] //WHERE 역할, SQL Server 지원 X
)
FROM 테이블명;
WINDOWING절 (SQL Server 지원 X)
| 종류 | 의미 | 예제 |
| ROWS | 행 | |
| CURRENT ROW | 현재 행 | |
| RANGE | 칼럼값 | |
| BETWEEN A AND B | A와 B사이 값 | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 칼럼값을 제한없이 이하값으로 현재행의 |
| PRECEDING | 이전 행, 빼는 값 | ROWS 2 PRECEDING : 행의 2행 전 RANGE 2 PRECEDING : 칼럼값의 -2 |
| FOLLOWING | 다음 행, 더하는 값 | ROWS 2 FOLLOWING : 행의 2행 후 RANGE 2 FOLLOWING : 칼럼값의 +2 |
| UNBOUNDED | 제한없음 | ROWS UNBOUNDED PRECEDING : 첫행~현재행까지 RANGE UNBOUNDED PRECEDING : 현재 칼럼값 이하 전부 |
📌 다음 설명 중 적절한 것은?
1) PARTITION과 GROUP BY구문은 의미적으로 완전히 다르다 //유사하다
2) SUM, MAX, MIN 등과 같은 집계 WINDOW FUNCTION을 사용할 때 WINDOW 절과 함께 사용하면 집계의 대상이 되는 레코드 범위를 지정할 수 있다.
3) WINDOW FUNCTION 처리로 인해 결과 건수가 줄어들 수 있다 //결과건수는 상관없다.
4) GROUP BY 구문과 WINDOW FUNCTION은 병행하여 사용할 수 있다. //병행 불가하다
💚 절차형 SQL
SQL도 절차 지향적인 프로그램이 가능하도록 절차형 SQL을 제공하고 있다.
연속적인 실행이나 조건에 따른 분기처리를 이용하여 모듈을 생성할 수 있다.
저장모듈로 PROCEDURE, USER DEFINED FUNCTION, TRIGGER를 제공한다.
| 구분 | PROCEDURE 프로시저 | FUNCTION 함수 | TRIGGER 트리거 |
| 설명 | 특정 로직을 처리하는 프로그램 | 특정 기능을 처리하는 프로그램 | INSERT, UPDATE, DELETE 작업에 자동으로 실행되는 프로그램 |
| Oracle 구문 |
CREATE [or REPLACE] Procedure 프로시저명 ( 매개변수명 [MODE] 데이터타입, .... ) IS [AS] 변수선언 BEGIN 실행내용 EXCEPTION 에러 발생 시 내용 END; / |
CREATE Function 함수명(매개변수명 데이터타입) RETURN 데이터타입 IS[AS] 변수선언 BEGIN 실행내용 RETURN 반환할 값 EXCEPTION 예외시 내용 END / |
CREATE Trigger 트리거명 BEFORE or AFTER INSERT or UPDATE or DELETE ON 테이블명 [FOR EACH ROW] [WHEN 조건식] 변수선언 BEGIN 실행내용 END / |
| SQL Server 구문 | CREATE Procedure 프로시저명 @매개변수명 데이터타입 [MODE], .... AS 변수선언 BEGIN 실행내용 ERROR 에러 발생 시 내용 END; |
CREATE Function 함수명(매개변수명 데이터타입) RETURNS 데이터타입 AS 변수선언 BEGIN 실행내용 RETURN 반환할 값 END |
CREATE Trigger 트리거명 ON 테이블명 FOR or AFTER or INSTEAD OF INSERT or UPDATE or DELETE AS 변수선언 BEGIN 실행내용 END; |
| 실행방법 | EXECUTE 프로시저명( 매개변수 ) | 함수명( 매개변수 ) | DBMS에 의해 자동실행 |
| RETURN | RETURN 필수 X | RETURN 필수 O | 사용안함 |
| TCL | BEGIN~END에 COMMIT, ROLLBACK 사용 O |
BEGIN~END에 COMMIT, ROLLBACK 사용 X |
PROCEDURE 프로시저
| Oracle | SQL Server | |
| 절차형 SQL | PL(Procedural Language)/SQL BLOCK 구조로 되어있어 각 기능별로 모듈화가 가능하다. |
T-SQL |
| Stored Procedure 생성방법 |
CREATE [or REPLACE] Procedure 프로시저명 ( 매개변수명 [MODE] 데이터타입, .... ) IS [AS] 변수선언 BEGIN 실행할 내용 EXCEPTION 에러 발생 시 내용 END; / |
CREATE Procedure 프로시저명 @매개변수명 데이터타입 [MODE], .... AS 변수선언 BEGIN 실행할 내용 ERROR 에러 발생 시 내용 END; |
| 매개변수 MODE - IN : 외부에서 매개변수 받을 때 사용 - OUT : 프로시저 결과 반환할 때 사용 - INOUT : IN과 OUT의 내용 동시에 수행 / : 프로시저를 컴파일하도록 명령하는 것. |
매개변수 MODE - VARING : 결과 집합이 출력 매개변수로 사용되도록 - DEFAULT : 매개변수 값이 없으면 기본값으로 처리 - OUT/OUTPUT : 프로시저 결과 반환 - READONLY : 매개변수를 수정할 수 없다 |
|
| Stored Procedure 수정방법 |
REPLACE Procedure 프로시저명... 을 작성하면 새로운 내용으로 덮어쓰게된다. |
ALTER Procedure .... 로 변경한다. |
| Stored Procedure 삭제방법 |
DROP Procedure 프로시저명; | DROP Procedure 프로시저명; |
| Stored Procedure 실행방법 |
EXECUTE 프로시저명(매개변수, ...); | EXECUTE 프로시저명 매개변수, ...; |
| 예제 | CREATE Procedure p_DEPT_insert ( v_DEPTNO in number, v_dname in varchar2, v_loc in varchar2, v_result out varchar2 ) IS cnt number := 0; --SCALAR 변수 cnt : 임시데이터 1개 저장가능 --대입연산자 := BEGIN SELECT COUNT(*) INTO CNT FROM DEPT --입력받은 부서코드가 존재하는지 확인 --SELECT문 결과가 반드시 있어야한다. WHERE DEPTNO = v_DEPTNO AND ROWNUM = 1; if cnt >0 then --부서코드가 존재하면 v_result := '이미 등록된 부서번호이다'; else --부서코드가 존재하지 않으면 INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (v_DEPTNO, v_dname, v_loc); -- 레코드 입력 COMMIT; --커밋 v_result := '입력 완료!!'; end if; EXCEPTION --에러발생 시 WHEN OTHERS THEN ROLLBACK; v_result := 'ERROR 발생'; END; / |
CREATE Procedure dbo.p_DEPT_insert @v_DEPTNO int, @v_dname varchar(30), @v_loc varchar(30), @v_result varchar(100) OUTPUT AS DECLARE @cnt int -- 변수 선언 SET @cnt = 0 --SCALAR 변수 cnt : 임시데이터 1개 저장가능 --대입연산자 = BEGIN SELECT @cnt=COUNT(*) FROM DEPT --입력받은 부서코드가 존재하는지 확인 --SELECT문 결과가 없어도 된다. WHERE DEPTNO = @v_DEPTNO IF @cnt >0 --부서코드가 존재하면 BEGIN SET @v_result = '이미 등록된 부서번호이다' RETURN END ELSE --부서코드가 존재하지 않으면 BEGIN BEGIN TRAN INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (@v_DEPTNO, @v_dname, @v_loc) -- 레코드 입력 IF @@ERROR<>0 BEGIN ROLLBACK --에러발생 시 SET @v_result = 'ERROR 발생' RETURN END ELSE BEGIN COMMIT --커밋 SET @v_result = '입력 완료!!' RETURN END END END |
FUNCTION 함수
함수는 프로시저와 달리, RETURN을 이용해서 값을 반드시 반환해준다.
| Oracle | SQL Server | |
| ABS 구현 예제 |
CREATE Function UTIL_ABS (v_input in number) return NUMBER IS v_return number := 0; BEGIN if v_input <0 then v_return := v_input * -1; else v_return := v_input; end if; RETURN v_return; END; / |
CREATE Function dbo.UTIL_ABS (@v_input int) RETURNS int AS BEGIN DECLARE @v_return int SET @v_return=0 IF @v_input <0 SET @v_return = @v_input * -1 ELSE SET @v_return = @v_input RETURN @v_return; END |
TRIGGER 트리거
트리거는 특정 테이블에 INSERT, UPDATE, DELETE 등이 수행되었을 때
데이터베이스에서 자동으로 동작하게하는 프로그램이다.
트리거는 트랜잭션 안에서 일어나는 작업으로 볼수도있다.
따라서 ROLLBACK을 하면 트리거 작업내역도 모두 취소된다!
| Oracle | SQL Server | |
| 예제 | CREATE Trigger SUMMARY_SALES AFTER INSERT --레코드가 입력된 후 트리거실행 ON ORDER_LIST -- 테이블에 트리거 설정 FOR EACH ROW --각 행마다 트리거 실행 DECLARE o_date ORDER_LIST.order_date%TYPE; o_prod ORDER_LIST.product%TYPE; BEGIN o_date := :NEW.order_date; --NEW 신규행의 정보 o_prod := :NEW.product; UPDATE SALES_PER_DATE SET qty = qty + :NEW.qty, amount = amount + :NEW.amount WHERE sale_date = o_date AND product = o_prod; if SQL%NOTFOUND then INSERT INTO SALES_PER_DATE VALUES(o_date, o_prod, :NEW.qty, :NEW.amount); end if; END; / |
CREATE Trigger dbo.SUMMARY_SALES ON ORDER_LIST -- 테이블에 트리거 설정 AFTER INSERT --레코드가 입력된 후 트리거실행 AS DECLARE @o_date DATETIME,@o_prod INT, @qty int, @amount int BEGIN SELECT @o_date=order_date, @o_prod=product, @qty=qty, @amount=amount FROM inserted -- inserted 신규행의 정보 UPDATE SALES_PER_DATE SET qty = qty + @qty, amount = amount + @amount WHERE sale_date = @o_date AND product = @o_prod; IF @@ROWCOUNT=0 INSERT INTO SALES_PER_DATE VALUES(@o_date, @o_prod, @qty, @amount) END |
