📌 [정보처리기사] SQL 활용 - SQL 테스트
데이터베이스 구축 - SQL 활용
목차
SQL 테스트의 개요
📌 SQL 테스트는 SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정이다.
- 단문 SQL은 SQL 코드를 직접 실행한 후 결과를 확인하는 것으로 간단히 테스트가 가능하다.
- 절차형 SQL은 테스트 전에 생성을 통해 구문 오류(Syntax Error)나 참조 오류의 존재 여부를 확인한다.
- 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적으로 확인한다.
💡 구문 오류(Syntax Error) : 구문 오류는 잘못된 문법으로 작성된 SQL문을 실행하면 출력되는 오류를 의미한다.
💡 디버깅(Debugging) : 디버깅은 오류를 잡기 위해 소스 코드를 한 줄 한 줄 추적해 가며 변수 값의 변화를 검사하는 작업이다.
단문 SQL 테스트
📌 단문 SQL 테스트는 DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 테스트하는 것으로, 직접 실행하여 결과물을 확인한다.
- 실행 시 오류나 경고가 발생할 경우 메시지를 참조하여 문제를 해결한다.
- DESCRIBE 명령어를 이용하면 DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 바로 확인할 수 있다.
- DESC [개체명];
- DML로 변경한 데이터는 SELECT문으로 데이터의 정상적인 변경 여부를 확인할 수 있다.
- DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 SELECT로 조회하거나, SHOW 명령어로 확인할 수 있다.
- Oracle : SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 사용자;
- MySQL : SHOW GRANTS FOR 사용자@호스트;
💡 DESCRIBE, DESC : DESCRIBE는 개체의 정보를 확인할 때 사용하는 명령어이고, DESC는 DESORIBE의 약어로 DESC를 사용해도 동일한 기능을 수행한다.
💡 TCL(Transaction Control Language)은 트랜잭션을 제어하는 COMMIT, ROLLBACK SAVEPOINT 명령을 가리키는 용어이다.
🔔 예제1) MySQL에서 <학생> 테이블의 정보를 확인하는 SQL문을 작성하시오.
DESC 학생;
<결과>
| Field | Type | Null | Key | Default | Extra |
| 이름 | varchar(8) | No | noname | ||
| 학번 | Int(11) | No | PRI | ||
| 전공 | char(8) | Yes | |||
| 주민번호 | char(13) | No | UNI |
💡 해설 : Field는 속성명, Type은 자료형, Null은 Null 허용 여부, Key는 기본키 (Primary Key)와 유니크(Unique) 옵션의 여부, Default는 기본값, Extra는 그 외의 기타 옵션을 의미한다.
🔔 예제2) Oracle에서 'KIM' 사용자의 시스템 권한을 확인하는 SQL문을 작성하시오.
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'KIM';
<결과>
| GRANTEE | PRIVILEGE | ADMIN_OPTION |
| KIM | CREATE TABLE | NO |
| KIM | CREATE VIEW | NO |
💡 해설 : 'KIM'이 테이블과 뷰를 만들 권한(PRIVILEGE)을 갖고 있지만, 해당 권한을 다른 유저에게 부여할 수 있는 권한(ADMIN_OPTION)은 없다는 의미이다.
🔔 예제3) MySQL에서 locallhost 서버의 'KIM' 사용자에 대한 권한을 확인하는 SQL문을 작성하시오.
SHOW GRANTS FOR 'KIM'@'localhost';
<결과>
| Grants for KIM@localhost |
| GRANT SELECT ON *.* TO 'KIM'@'localhost' |
💡 해설 : localhost 서버에서 'KIM' 사용자는 모든 스키마와 테이블에 대해 조회(SELECT) 권한이 있다는 의미이다.
절차형 SQL 테스트
📌 프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 기능의 적합성 여부를 검증하고, 실행을 통해 결과를 확인하는 테스트를 수행한다.
- 많은 코드로 구성된 절차형 SQL의 특성상 오류 및 경고 메시지가 상세히 출력되지 않으므로 SHOW 명령어를 통해 오류 내용을 확인하고 문제를 수정한다.
- 형식 : SHOW ERRORS;
- 데이터베이스에 변화를 줄 수 있는 SQL문은 주석으로 처리하고, 출력문을 이용하여 화면에 출력하여 확인한다.
- Oracle 출력 형식
- DBMS_OUTPUT.ENABLE; : 화면에 출력하기 위해 DBMS_OUTPUT 패키지를 불러온다.
- DBMS_OUTPUT.PUT_LINE(데이터); : '데이터'에 넣은 변수나 값을 화면에 출력한다.
- MySQL 출력 형식
- SELECT 데이터; : '데이터'에 넣은 변수나 값을 화면에 출력한다.
- Oracle 출력 형식
- 디버깅이 완료되면 출력문을 삭제하고, 주석 기호를 삭제한 후 절차형 SQL을 실행하여 결과를 검토한다.
🔔 예제1) Oracle에서 변수 RESULT를 출력하는 SQL문을 작성하시오.
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE(RESULT);
💡 해설 : Oracle에서 화면에 변수나 텍스트 등을 출력하기 위해서는 DBMS_OUTPUT 패키지의 PUT_LINE 함수를 사용해야 한다. 첫 번째 행은 DBMS_OUTPUT 패키지를 사용하겠다는 의미이고, 두 번째 행은 실제 출력을 위해 PUT_LINE 함수를 사용한 것이다. 실행하면 RESULT에 기억된 값이 화면에 표시된다.
🔔 예제2) MySQL에서 변수 RESULT를 출력하는 SQL문을 작성하시오.
SELECT RESULT;
💡 해설 : MySQL에서는 SELECT만으로 간편하게 화면에 변수나 텍스트 등을 출력할 수 있다. Oracle의 명령문을 사용하면 오류가 발생한다.
💡 Oracle 오류 확인 예
오류가 발생한 장소와 오류 코드 오류 내용이 표시된다.
LINE/COL : 7/1 ERROR : PLS-00363 : 'N' 식은 피 할당자로 사용될 수 없습니다.
PLS는 오라클에서 프로시저나 함수를 호출할 때 파라미터에 오류가 있을 때 발생한다.
💡 주석(Comment) : 설명을 위해 입력한 부분을 주석이라고 한다. 즉 주석은 사람만 알아볼 수 있으며, 컴파일 되지도 않는다. 때문에 잡시 사용하지 않을 SQL 코드를 주석으로 처리해 두면 지우지 않고도 해당 코드를 무시하고 SQL문을 수행할 수 있다.
🔔 DBMS의 종류에 따라 다음과 같이 다른 주석 기호를 사용한다.
1. # : MySQL 에서 사용하는 주석 기호로 # 다음에 오는 한 줄을 주석으로 처리한다.
2. -- : Oracle에서 사용하는 주석 기호로 -- 다음에 오는 한 줄을 주석으로 처리한다.
3. /* */ : DBMS에 관계없이 '/*'과 '*/' 사이에 있는 문장을 모두 주석으로 처리한다.
💡 Oracle은 DBMS_OUTPUT 패키지로 화면에 결과를 출력하려면 절차형 SQL을 실행하기 전에 'SET SERVEROUTPUT ON' 명령어로 환경변수 를 설정해야 한다.
- Oracle 프로시저 디버깅 예
- 다음은 사용자에게 이름을 입력 받아 <strudent> 테이블에서 같은 이름을 가진 학생의 국어, 수학, 영어 점수를 합하여 <score> 테이블에 삽입하는 프로시저를 디버깅하기 위해 수정한 것이다. 진하게 강조한 코드와 주석 기호가 디버깅을 위해 추가한 코드이다.
CREATE OR REPLACE PROCEDURE u_sum_avg(u_name IN CHAR)
IS
1. v_sum INT;
2. v_avg INT;
BEGIN
3. DBMS_OUTPUT.ENABLE;
4. SELECT kor + math + eng INTO v_sum FROM student
WHERE name = u_name;
5. v_avg := v_sum/3;
6. DBMS_OUTPUT.PUT_LINE(v_sum);
7. DBMS_OUTPUT.PUT_LINE(v_avg);
8. -- INSERT INTO score VALUES(u_name, v_sum, v_avg);
END;
- 정수를 저장할 변수 v_sum을 선언한다.
- 정수를 저장할 변수 v_avg을 선언한다.
- 결과를 화면에 출력하기 위해 DBMS_OUTPUT 패키지를 사용 가능한 상태로 설정한다.
- <student> 테이블의 name 속성에서 입력 파라미터 u_name과 같은 값을 가진 튜플을 찾아 kor, math, eng 속성의 값을 더해 변수 v_sum에 저장한다.
- v_sum의 값을 3으로 나눠 v_avg에 저자한다.
- v_sum의 값을 화면에 출력한다.
- v_avg의 값을 화면에 출력한다.
- u_name, v_sum, v_avg의 값을 <score> 테이블에 추가하는 SQL문 이지만 주석 처리(--)되었으므로 수행되지 않고 종료된다.