2022.09.01 SQL

sofia·2022년 9월 4일
sql
0

SQL

목록 보기
13/13
post-thumbnail
  1. 평가 시험 풀이
    viewEmp.jsp
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
    
<%!
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String user = "scott";
	String password = "tiger";
	String selectQuery = "SELECT * FROM EMP";
%>      
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width="800" border="1">
		<tr>
			<td>사원번호</td>
			<td>사원명</td>
			<td>직급</td>
			<td>상관번호</td>
			<td>입사일자</td>
			<td>급여</td>
			<td>커미션</td>
			<td>부서번호</td>
		</tr>
	<%
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection(url, user, password);
				stmt = conn.createStatement();
				rs = stmt.executeQuery(selectQuery); //쿼리 결과를 rs로 받음
				
				while(rs.next()) {
		%>
		<tr>
			<td><%= rs.getString("empno") %></td>
			<td><%= rs.getString("ename") %></td>
			<td><%= rs.getString("job") %></td>
			<td><%= rs.getString("mgr") %></td>
			<td><%= rs.getString("hiredate") %></td>
			<td><%= rs.getString("sal") %></td>
			<td><%= rs.getString("comm") %></td>
			<td><%= rs.getString("deptno") %></td>
			
		</tr>
		<%
				}
			} catch(SQLException ex) {
				ex.getMessage();
			} finally {
				try {
					if(rs != null) rs.close();
					if(stmt != null) stmt.close();
					if(conn != null) conn.close();
				} catch(SQLException ex) {
					ex.getMessage();
				}
			}
		%>
	</table>
	
</body>
</html>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%!
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String user = "scott";
	String password = "tiger";
	StringBuffer selectQuery = new StringBuffer();//객체생성
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
	<table width="400" border="1">
		<tr>
			<td>컬럼명</td>
			<td>컬렴형식</td>
			<td>컬럼길이</td>
			<td>널값여부</td>
		</tr>
		<%
			try {
				String tname = request.getParameter("table");
				out.print("##$#$# tname => "+tname);
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection(url, user, password);
				stmt = conn.createStatement();
				
				selectQuery.append("SELECT COLUMN_NAME");
				selectQuery.append(" , DATA_TYPE");
				selectQuery.append(" , DATA_LENGTH");
				selectQuery.append(" , NULLABLE");
				selectQuery.append(" FROM USER_TAB_COLUMNS");
				selectQuery.append(" WHERE TABLE_NAME = '"+tname+"'");
				rs = stmt.executeQuery(selectQuery.toString()); //쿼리 결과를 rs로 받음
				selectQuery.setLength(0);
				//가끔 어떤 테이블은 실행이 안되는 경우가 있는데 그럴때 .setLength(0)을 이용하면 됨
				//이전에 입력되었던 값의 초기화 역할(실행전으로 돌아가서 구동되도록 함.)
				
				while(rs.next()) {
		%>
		<tr>
			<td><%= rs.getString("COLUMN_NAME") %></td>
			<td><%= rs.getString("DATA_TYPE") %></td>
			<td><%= rs.getString("DATA_LENGTH") %></td>
			<td><%= rs.getString("NULLABLE") %></td>
		</tr>
		<%
				}
			} catch(SQLException ex) {
				ex.getMessage();
			} finally {
				try {
					if(rs != null) rs.close();
					if(stmt != null) stmt.close();
					if(conn != null) conn.close();
				} catch(SQLException ex) {
					ex.getMessage();
				}
			}
		%>
	</table>
</body>
</html>

복습 문제1

SELECT NAME
     , POSITION
     , TO_CHAR(PAY,'$999,999,999') "SALARY"
  FROM EMP2
 WHERE (POSITION,PAY) IN (SELECT POSITION
                               , MAX(PAY)
                            FROM EMP2
                           GROUP BY POSITION)
 ORDER BY 3;

스칼라 서브쿼리(Scalar Subquery)

  • SELECT실행문 안에 SELECT절 삽입
  • 속도가 빠름

SELECT * FROM EMP2; 와 SELECT * FROM DEPT2;를 이용해서 

SELECT E.NAME
     , D.DNAME 
FROM EMP2 E, DEPT2 D
WHERE E.DEPTNO = D.DCODE;

출력
서브쿼리를 이용해서 동일한 결과를 얻을 수있다. 아래 형태를 스칼라 서브쿼리형태라고 함

-- 스칼라 서브쿼리
-- 속도가 빠르게 적용됨
SELECT E.NAME
     , (SELECT D.DNAME 
        FROM DEPT2 D
        WHERE E.DEPTNO = D.DCODE) "DNAME"
FROM EMP2 E;

결과 동일함

-- 데이터를 비교해서 보고싶다. 그럼 .*을 사용하면 편함
SELECT E.DEPTNO, E.*
    FROM EMP E
ORDER BY JOB;

SELECT E.DEPTNO, E.JOB , E.SAL, E.*
    FROM EMP E
ORDER BY E.JOB;

SELECT E.DEPTNO, E.JOB , E.SAL, E.*
    FROM EMP E
ORDER BY E.DEPTNO, E.JOB;

예제 1

SELECT DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
                                    FROM EMP GROUP BY DEPTNO, JOB
UNION ALL
SELECT DEPTNO, NULL JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
                                    FROM EMP GROUP BY DEPTNO
UNION ALL
SELECT NULL DEPTNO, NULL JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
                                    FROM EMP
                                    ORDER BY DEPTNO, JOB;

집계 함수

ROLL UP

-- ROLLUP (집계함수)                                    
SELECT DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
   FROM EMP 
GROUP BY ROLLUP (DEPTNO, JOB);

CUBE

SELECT DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
                                    FROM EMP GROUP BY DEPTNO, JOB
UNION ALL
SELECT DEPTNO, NULL JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
                                    FROM EMP GROUP BY DEPTNO
UNION ALL
SELECT NULL DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
                                    FROM EMP
                                    GROUP BY JOB 
UNION ALL
SELECT NULL DEPTNO,  NULL JOB , ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
   FROM EMP 
ORDER BY DEPTNO, JOB;
  • CUBE 사용
-- CUBE (집계함수)                                    
SELECT DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
   FROM EMP 
GROUP BY CUBE (DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

GROUPING SETS

⭐내 방법

SELECT DEPTNO, NULL JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
   FROM EMP group by DEPTNO, NULL 
UNION ALL
SELECT NULL DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
   FROM EMP group by NULL, JOB 
   ORDER BY DEPTNO,JOB;
  • GROUPING SETS

⭐강사님 방법

SELECT DEPTNO, NULL JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
   FROM EMP group by DEPTNO
UNION ALL
SELECT NULL DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
   FROM EMP group by NULL, JOB 
   ORDER BY DEPTNO,JOB;
SELECT DEPTNO, NULL JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP 
   FROM EMP 
   GROUP BY GROUPING SETS(DEPTNO, JOB)
   ORDER BY DEPTNO,JOB;

SELECT GRADE,
       NULL DEPTNO1,
       COUNT(*),
       SUM(HEIGHT)"TOTAL_HEIGHT",
       SUM(WEIGHT)"TOTAL_WEIGHT"
   FROM STUDENT group by GRADE
UNION ALL
SELECT NULL GRADE,
       DEPTNO1,
       COUNT(*),
       SUM(HEIGHT)"TOTAL_HEIGHT",
       SUM(WEIGHT)"TOTAL_WEIGHT"
   FROM STUDENT group by DEPTNO1
   ORDER BY GRADE,DEPTNO1;
  • GROUPING SETS
SELECT GRADE,
       DEPTNO1,
       COUNT(*),
       SUM(HEIGHT)"TOTAL_HEIGHT",
       SUM(WEIGHT)"TOTAL_WEIGHT"
   FROM STUDENT  
   GROUP BY GROUPING SETS(GRADE, DEPTNO1)
   ORDER BY GRADE,DEPTNO1;

PROCEDURE & Function (프로시저& 함수) (PPT 12장)

  • 자주 사용하는 SQL을 프로시저로 만들어 저장한 후, 필요할때마다 사용
  • 데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템에 저장한 것

  • 장점
    네트워크 소요 시간을 줄여 성능을 개선
    기능 변경항떄 편함

  • 단점
    유지보수가 어려움

1. PROCEDURE 생성


CREATE OR REPLACE PROCEDURE 프로시져이름 (파라미터1,파라미터2...);

IS
변수

BEGIN

쿼리문

END 프로시져 이름;

예시

  • 사원 급여 조정 프로시저(수정함)

(+) 생성한 후 변경하는 경우도 많아서 CREATE OR REPLACE 를 많이 사용함


CREATE OR REPLACE PROCEDURE adjust_sal --PROCEDURE명
    (v_flag VARCHAR2  --괄호안에 변수 및 데이터타입 작성 
   , v_empno NUMBER
   , v_pct NUMBER) AS 
BEGIN  --시작 로직
    IF v_flag = 'INCREASE' THEN
        UPDATE EMP SET SAL = SAL + (SAL*(v_pct/100))
         WHERE EMPNO = v_empno; --해당되는 사원의 v_flag가 인상되었을 경우의 SAL 변경
    ELSE -- 해당되는 사원의 v_flag가 인상되지 않았을때
         UPDATE EMP SET SAL = SAL - (SAL*(v_pct/100))
         WHERE EMPNO = v_empno;
         --해당되는 사원의 v_flag가 인상되지 않았을 경우의 SAL변경 
    END IF; -- 끝 로직

실행하면 Procedure ADJUST_SAL이(가) 컴파일되었습니다. 라고 적음

2. PROCEDURE 실행

EXEC adjust_sal('INCREASE',7369,10);
EXEC adjust_sal('',7369,10);

3. 함수(Funtion) 생성

  • 예제 사원 연봉 구하는 함수
--  
CREATE OR REPLACE FUNCTION get_annual_sal (v_empno NUMBER)
--  함수 명 및 변수와 데이터 타입 설정
--  RETURN NUMBER IS v_sal NUMBER : = 0; 
--  0은 초기값을 주는것 
--  NUMBER타입의 v_sal를 넘기겠다.
    RETURN NUMBER IS v_sal NUMBER;
BEGIN
    SELECT (SAL + NVL(COMM,0)) * 12 INTO v_sal -- 구하는 함수이므로 SELECT문 사용
      FROM EMP WHERE EMPNO = v_empno;  --조건문: 사원 번호가 v_empno인 사원의
      RETURN v_sal;
END;
  • 예제 사원 퇴직급여 구하는 함수
-- 사원 퇴직급여 구하는 함수
CREATE OR REPLACE FUNCTION get_retire_sal (v_empno NUMBER)
    RETURN NUMBER IS v_sal NUMBER;
BEGIN
    SELECT ROUND((SAL + NVL(COMM,0)) * ROUND((MONTHS_BETWEEN(SYSDATE, HIREDATE)),0) / 12, 0)
    INTO v_sal
    FROM EMP WHERE EMPNO = v_empno;
    RETURN v_sal;
END;

3. 함수(Funtion) 실행

SELECT get_hiredate(7369,'YYYY-MM-DD') FROM dual;

SELECT EMPNO "사번"
     , ENAME "성명"
     , get_annual_sal(EMPNO) "연봉"
     , get_retire_sal(EMPNO) "퇴직금"
     , get_hiredate(EMPNO,'YYYY-MM-DD') "입사일"
     FROM EMP;

사용하면 깔끔하게 결과를 구할 수 있다.

HELP 함수 (도움말)

-- HELP 함수
CREATE OR REPLACE FUNCTION help (v_module VARCHAR2)--모듈을 함수나 프로시저가 됨
    RETURN VARCHAR2 IS v_usage VARCHAR2(100); --리턴하면서 데이터 크기 지정 가능
BEGIN
    v_usage := v_module || '는(은) 등록되지 않은 모듈입니다.'; -- 초기값 설정
    --조건문
    IF UPPER(v_module) = 'ADJUST_SAL' THEN
        v_usage:='종류: PROC, 사용법 : EXEC adjust_sal(INCREASE|DECREASE, 사번, 증감율)';
    ELSIF UPPER(v_module) = 'GET_ANNUAL_SAL' THEN
        v_usage:='종류: FUNC, 사용법 : get_annual_sal(사번)';   
    END IF;
    RETURN v_usage;
END;
SELECT HELP('adjust_sal') FROM DUAL;

SELECT HELP('adjust_sal2') FROM DUAL;

함수와 프로시저의 차이

PACKAGE

  • 관련된 PROCEDURE와 Function 그룹지어 생성
    헤더와 바디가 존재함

헤더 부분

create or replace PACKAGE emp_mgmt AS 
   PROCEDURE adjust_sal (v_flag VARCHAR2 , v_empno NUMBER, v_pct NUMBER);
   FUNCTION get_annual_sal (v_empno NUMBER) RETURN NUMBER;
   FUNCTION get_retire_sal (v_empno NUMBER) RETURN NUMBER;
   PROCEDURE remove_emp (v_empno NUMBER);
   FUNCTION get_hiredate (v_empno NUMBER, v_fmt VARCHAR2) RETURN VARCHAR2;
END emp_mgmt;

BODY

바디 부분

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS 
    --사원급여 조정 프로시저
    PROCEDURE adjust_sal
        (v_flag VARCHAR2, v_empno NUMBER, v_pct NUMBER) AS
    BEGIN
        IF v_flag='INCREASE' THEN 
            UPDATE EMP SET SAL = SAL + (SAL*(v_pct/100))
             WHERE EMPNO = v_empno;
        ELSE
            UPDATE EMP SET SAL = SAL - (SAL*(v_pct/100))
             WHERE EMPNO = v_empno;
        END IF;
    END;
    
    -- 사원연봉 구하는 함수
    FUNCTION get_annual_sal (v_empno NUMBER)
        RETURN NUMBER IS v_sal NUMBER;
    BEGIN
        SELECT (SAL + NVL(COMM,0)) * 12 INTO v_sal
          FROM EMP WHERE EMPNO = v_empno;
          RETURN v_sal;
    END;
    
    --사원 퇴직급여 구하는 함수
    FUNCTION get_retire_sal (v_empno NUMBER)
        RETURN NUMBER IS v_sal NUMBER;
    BEGIN
        SELECT ROUND((SAL + NVL(COMM,0)) * ROUND((MONTHS_BETWEEN(SYSDATE, HIREDATE)),0) / 12, 0)
        INTO v_sal
        FROM EMP WHERE EMPNO = v_empno;
        RETURN v_sal;
    END;
    
    -- 사원 정보 삭제 프로시저
    PROCEDURE remove_emp
        (v_empno NUMBER) AS 
    BEGIN  
        DELETE FROM EMP 
             WHERE EMPNO = v_empno;
    END;
    
    -- 사원 입사 일자 구하는 함수
    FUNCTION get_hiredate (v_empno NUMBER, v_fmt VARCHAR2)
        RETURN VARCHAR2 IS v_hiredate VARCHAR2(20);
    BEGIN
        SELECT TO_CHAR(hiredate, v_fmt)
            INTO v_hiredate
            FROM EMP WHERE EMPNO = v_empno;
            RETURN v_hiredate; 
    END;
END;
  • 실행시
EXEC emp_mgmt.adjust_sal('INCREASE',7369,10);

SELECT emp_mgmt.get_hiredate(7369,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

백업

계정 전체 백업

시스템계정으로 접속 scott 계정의 모든 파일들을 백업

exp userid=system/1234@xe owner=scott file=d:\backup\scott01.dmp

cmd 창가서

imp userid=system/1234 fromuser=scott touser=scott file=d:\backup\scott01.dmp tables=(dept4)

입력

테이블 선택해서 백업

  1. 교수테이블의 익스포트 로 이동


생성됨을 확인 할수있음

레퍼런스

profile
sofia
이전 포스트

2022.08.30 SQL

0개의 댓글