단일행 함수

문자 함수

• 시작 위치를 -(마이너스)로 지정할 경우 left <- right(오른쪽에서 왼쪽) 으로 검색해 시작위치지정
• 시작위치와 찾으려는 위치 값은 생략가능하며 생략할 경우자동으로 1로 설정
• ex) 문자열 A-B-C-D에서 오른쪽에서 6번째 문자를 기준으로 2번째'-'가 나오는 위치의 값을 출력하시오.

  SQL> SELECT 'A-B-C-D' "ABCD", INSTR('A-B-C-D','-', -6, 2) "INSTR"
  2  FROM dual;
  
  ABCD         INSTR
  ----------- ---------
  A-B-C-D             0  

• ex2) Student 테이블의 tel 컬럼을 사용하여 1 전공번호(deptno1)가 201번인 학생의 이름과 전화번호, ‘)‘ 가 나오는 위치를 출력하시오.

  SQL> SELECT name, tel, INSTR(tel, ')') "INSTR()"
   2  FROM student
  3  WHERE deptno1 = 201;
  
  NAME                 TEL                INSTR()
  -------------------- --------------- ----------
  
  Demi Moore           02)6255-9875             3
  Macaulay Culkin      02)312-9838              3
  Wesley Snipes        053)736-4981             4    

• ex) student 테이블에서 1 전공이 201 번인 학과 학생들의 id 를 총 10자리로 출력하되 왼쪽 빈 자리는 ‘*’ 기호로 채우세요

  SQL> SELECT name, id, LPAD(id,10,'*') "LPAD()"
  2  FROM student
  3  WHERE deptno1 = 201;
  
  NAME                 ID         LPAD()
  -------------------- ---------- ------------------- 
  Demi Moore           Moore      *****Moore
  Macaulay Culkin      Culkin     ****Culkin
  Wesley Snipes        Snipes     ****Snipes 

• ex) emp 테이블의 ename에서 마지막 글자가 'R'이 있을경우 제거함.

  SQL> SELECT ename, RTRIM(ename, 'R') "RTRIM"
  2  FROM emp
  3  WHERE deptno = 10;
  
  ENAME      RTRIM
  ------- --------
  CLARK      CLARK
  KING       KING
  MILLER     MILLE 

QUIZ1

Student 테이블을 참조해서 아래 화면과 같이 1 전공이(deptno1 컬럼) 201번인 학생의 이름과 전화번호와 지역번호를 출력하세요. 단 지역번호는 숫자만 나와야함. 

SQL> SELECT name, tel, SUBSTR(tel,1,INSTR(tel,')')-1) "Area Code"
  2  FROM student
  3  WHERE deptno1 = 201;

QUIZ2

아래 화면과 같이 emp 테이블에서 deptno 가 10번인 사원들의 이름을 총 9자리로 출력하되 오른쪽 빈자리에는 해당 자리 수에 해당되는 숫자가 출력되도록 하시오 

SQL> SELECT RPAD(ename, 9, SUBSTR('123456789',length(ename)+1,9)) "RPAD"
  2  FROM emp
  3  WHERE deptno = 10; 

QUIZ3

Student 테이블에서 아래와 같이 deptno1 이 101 번인 학과 학생들의 이름과 전화번호와 전화번호에서 지역번호와 국번을 제외한 나머지 번호를 * 로 표시해서 출력하시오.

SQL> SELECT name, tel, REPLACE(tel, SUBSTR(tel,INSTR(tel,'-')+1), '****') "REPLACE"
  2  FROM student
  3  WHERE deptno1 = 101; 

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

오라클 서버 접속

오라클계정

서버접속

ERROR:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

• Enter user-name: sys/oracle as sysdba
• SQL>startup
• SQL> CONN scott/tiger

ERROR:

ORA-28000: the account is locked

• Enter user-name: sys/oracle as sysdba
• SYS> ALTER USER scott IDENTIFED BY tiger ACCOUNT UNLOCK;

데이터 가져오기

SELECT

• SQL>SELECT * FROM emp; 모든 컬럼조회

Column 변경

• 숫자 데이터: SQL> COL 컬럼명 FOR 9999 (자리수만큼 9할당)
• 문자 데이터: SQL>COL 컬럼명 FOR a15 (자리수만큼 숫자할당)

• 한 화면에 출력가능한 가로길이: SQL>SET LINE 200
• 한 화면에 출력가능한 세로길이: SQL>SET PAGES 50

• 컬럼명 이외에 원하는 내용으로 출력할수 있음.
• SELECT 구문 컬럼명 뒤에 '원하는내용'로 묶어서 사용
• '를 출력하려면 ''사용

SQL>SELECT ename||'''s job is '||job " NAME AND JOB"
 2  FROM emp;

 NAME AND JOB
-----------------------------
SMITH's job is CLERK
ALLEN's job is SALESMAN
WARD's job is SALESMAN

DISTINCT

SQL>SELECT DISTINCT job, ename
 2  FROM emp
 3  ORDER BY 1,2;

JOB       ENAME
--------- ----------
ANALYST   FORD
ANALYST   SCOTT
CLERK     JAMES
CLERK     SMITH

WHERE

• 문자의 경우 대소문자 구분 (날짜는 구분안함)
• 날짜의 경우 윈도우와 리눅스 날짜 형식 다름 * 윈도우) yy/mm/dd, 리눅스) dd-MON-yy

• = 같은 조건 검색
• !=, <> 같지 않은 조건 검색
• > (<) 큰(작은) 조건 검색
• >= (<=) 크(작)거나 같은 조건 검색 숫자,문자,날짜 모두 비교가능(날짜의경우 최신날짜 일수록 값이큼)
• BETWEEN a AND b a와 b사이에 있는 범위값 검색 ( a, b값 모두 포함/ 한글은 b포함 안됨) * 속도면에서 위의 비교연산자가 더 빠름
• IN(a,b,c) a거나 b거나 c인 조건 검색 (속도빠름)
• LIKE 특정 패턴 있는 조건 검색, 아래의 두 기호와 함께쓰임. 홑따옴표필수
% : 글자수 제한X(0포함)
_ : 한글자 %나 _를 먼저 쓰면 SQL성능최악임(Index때문). 주의할것.
• IS NILL / IS NOT NULL Null 값/아닌값 검색 =연산 사용못함
• A AND B A,B 모두 만족하는 값 검색 (우선수위: 괄호, AND > OR)
• A OR B A,B 중 하나라도 만족하는 값 검색
• NOT A A가 아닌 모든 조건 검색

ORDER BY

SET OPERATOR (집합 연산자)

• 두 집합의 SELECT 절에 오는 칼럼의개수와 데이터형이 동일해야함.
• UNION & UNION ALL 많이 연결할수록 성능 저하되므로 DECODE, CASE 등 다른 방법 사용해 작성할것.

기타

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저