[SQLD] SQL기본&활용 : 함수, NULL함수, 그룹함수, 윈도우함수, CASE표현식, 절차형 SQL, 프로시저, 함수, 트리거

https://lotuus.tistory.com/42

[SQLD] 21.11.20 제43회 SQL개발자 후기, 공부방법, 정리본, 43회 출제문제

 

[목차]

💚 함수 : 입력값이 아무리 많아도 출력은 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