📌 [정보처리기사] 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 데이터; : '데이터'에 넣은 변수나 값을 화면에 출력한다.
• 디버깅이 완료되면 출력문을 삭제하고, 주석 기호를 삭제한 후 절차형 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;

 

1. 정수를 저장할 변수 v_sum을 선언한다.
2. 정수를 저장할 변수 v_avg을 선언한다.
3. 결과를 화면에 출력하기 위해 DBMS_OUTPUT 패키지를 사용 가능한 상태로 설정한다.
4. <student> 테이블의 name 속성에서 입력 파라미터 u_name과 같은 값을 가진 튜플을 찾아 kor, math, eng 속성의 값을 더해 변수 v_sum에 저장한다.
5. v_sum의 값을 3으로 나눠 v_avg에 저자한다.
6. v_sum의 값을 화면에 출력한다.
7. v_avg의 값을 화면에 출력한다.
8. u_name, v_sum, v_avg의 값을 <score> 테이블에 추가하는 SQL문 이지만 주석 처리(--)되었으므로 수행되지 않고 종료된다.