어제 문제
--1. 아래 뷰를 사용하여 DESC EMP 수행결과와
--동일하게 출력을 하는 SELECT문 작성(COMMENT 컬럼 제외)
-- 테이블 컬럼 정보 조회 뷰
select table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
from dba_tab_columns
where table_name = 'EMP';
desc emp; -- column_name, nullable, type
select COLUMN_NAME as "COLUMN",
decode(NULLABLE,'N','NOT NULL') as NULLABLE,
decode(DATA_TYPE, 'VARCHAR2',DATA_TYPE||'('||DATA_LENGTH||')',
'NUMBER',decode(DATA_SCALE, '0',DATA_TYPE||'('||DATA_PRECISION||')',
DATA_TYPE||'('||DATA_PRECISION||','||DATA_SCALE||')')
, DATA_TYPE) as "TYPE"
from dba_tab_columns
where table_name = 'EMP';
-- 강사님 방법 - char일 경우도 생각해야 되니깐 조건문 처리 순서가 바뀌어야함
select COLUMN_NAME as "Column",
decode(NULLABLE,'N','NOT NULL') as "Nullable",
decode(DATA_TYPE, 'NUMBER',decode(DATA_SCALE, '0',DATA_TYPE||'('||DATA_PRECISION||')',
DATA_TYPE||'('||DATA_PRECISION||','||DATA_SCALE||')'),
'DATE',DATA_TYPE,
DATA_TYPE||'('||DATA_LENGTH||')') as "Type"
from dba_tab_columns
where table_name = 'EMP';
-- 참고 : 테이블 및 컬럼 comment 확인 및 설정
select *
from dba_views
where view_name like 'DBA_%COMMENT%';
select *
from dba_tab_comments
where comments is not null;
select *
from dba_col_comments
where comments is not null;
comment on table SCOTT.emp is '직원정보 테이블';
COMMENT ON COLUMN SCOTT.EMP.EMPNO IS '사원번호';
COMMENT ON COLUMN SCOTT.EMP.ENAME IS '사원이름';
COMMENT ON COLUMN SCOTT.EMP.MGR IS '상위관리자 번호';
desc emp;--2. 권한부여
--[ 연습문제 ]
--1) easyup 계정 생성
create user easyup identified by oracle;
grant create session to easyup;
alter user easyup quota unlimited on users; -- quota 수정하는 법
--2) scott_sel, scott_cud, hr_sel, hr_cud 롤 생성
-- 롤은 동시 생성 안됨
create role scott_sel;
create role scott_cud;
create role hr_sel;
create role hr_cud;
--3) 각 롤에 다음 권한 담기
-- - scott_sel : scott 소유의 모든 테이블의 조회 권한
-- - hr_sel : hr 소유의 모든 테이블의 조회 권한
-- - scott_cud : scott 소유의 모든 테이블의 insert, update, delete 권한
-- - hr_cud: hr 소유의 모든 테이블의 insert, update, delete 권한
-- 스크립트로 해결.
select 'grant select on SCOTT.'||TABLE_NAME||' to scott_sel;'
from dba_tables
where owner='SCOTT';
select 'grant select on HR.'||TABLE_NAME||' to hr_sel;'
from dba_tables
where owner='HR';
select 'grant insert,update,delete on SCOTT.'||TABLE_NAME||' to scott_cud;'
from dba_tables
where owner='SCOTT';
select 'grant insert,update,delete on HR.'||TABLE_NAME||' to hr_cud;'
from dba_tables
where owner='HR';
-- 강사님 방법 - 한 쿼리로 모든 쿼리 출력
select 'grant select on '||owner||'.'||table_name||' to '||owner||'_sel;'
from dba_tables
where owner in ('SCOTT','HR')
union all
select 'grant insert,update,delete on '||owner||'.'||table_name||' to '||owner||'_cud;'
from dba_tables
where owner in ('SCOTT','HR');
--4) 위 롤 모두 easyup 유저에 부여
grant SCOTT_sel,hr_sel,SCOTT_cud,hr_cud to easyup;
--5) 조회 테스트
select * from SCOTT.DEPARTMENT;
select * from hr.EMPLOYEES;수업 내용
— [정규식표현(regexp)]
— 문자열 패턴을 간소화하여 공통 패턴을 가진 문자열을 추출, 삭제, 조회하기 위한 표현식
— 1) 문자/숫자/특수기호 표현
— \d : 숫자
— \w : 글자(숫자,문자) + _
— \s : 공백
— \t : 탭
— \n : 엔터
— [[:digit:]] : 숫자
— [[:alpha:]] : 문자
— [[:alnum:]] : 숫자+문자
— [[:punct:]] : 특수기호
— [[:blank:]] : 공백
— 2) 횟수 표현
— * : 0회 이상 (포함되어있지 않거나 1회이상 연속적으로 포함된 경우. 숫자나오다 문자나오면 그 문자 전까지 짤림)
— + : 1회 이상. 반복류 표현은 가능한한 길게 뽑는다.
— ? : 0회 아니면 1회
— {n} : n회
— {n,} : n회 이상
— {n,m} : n회 이상 m회 이하
— 3) 그룹
— [0-9] : 숫자 한 개
— [a-z] : 소문자 한 개
— [A-Z] : 대문자 한 개
— [a-zA-Z], [A-z] : 영문 한 개
— [가-힣] : 한글 한 글자
— 4) 나머지 기호
— ^ : 시작
— $ : 끝
— | : 또는
— . : 엔터를 제외한 모든 글자 한 개
— \ : escape character. 뒤에 오는 특수기호를 문자로 취급시킴
— 5) 기타
— () : 연산 우선 순위, 서브그룹 지정
— [^ab] : a 또는 b를 제외
— [정규식 표현 함수]
— 정규식 표현을 사용한 함수
— 1. regexp_substr
— 정규식 표현을 사용한 문자열 추출
regexp_substr(대상,패턴[,검색시작위치][,발견횟수][,옵션][,서브그룹]);
— 검색시작위치, 발견횟수, 서브그룹 생략 시 default 1
— 옵션 : c(대소구분), i(대소구분X), m(다중라인패턴선언)
-- 예)
select id,
regexp_substr(id,'\d+'), -- 숫자의 반복 추출
regexp_substr(id,'[a-z]+') -- 소문자의 반복 추출
from professor;
select regexp_substr('abc1234 abc10', 'abc\d+'), -- abc1234
regexp_substr('abc1234 abc10', 'abc\d{2}'), -- abc12
regexp_substr('abc1234 abc10', 'abc\d{2}',1,2), -- abc10
regexp_substr('abc1234 abc10', 'abc\d*') -- abc1234
from dual;-- 예제)
create table regexp1(name varchar2(50));
insert into regexp1 values('abcd');
insert into regexp1 values('bcd');
insert into regexp1 values('cdb');
insert into regexp1 values('cd-a');
insert into regexp1 values('d100b');
commit;
select regexp_substr(name, '^a|b[a-z]+')
from regexp1;-- 예제) 이메일 아이디 추출
select email,
substr(email,1,instr(email,'@')-1) as email_id1,
regexp_substr(email,'[A-z0-9-]+') as email_id2,
regexp_substr(email,'(.+)@',1,1,null,1) as email_id3 -- 서브그룹 활용.
from professor;-- 예제) 전화번호에서 지역번호, 국번을 각각 추출
select tel,
regexp_substr(tel,'\d+') as 지역번호,
regexp_substr(tel,'\d+',1,2) as 국번,
regexp_substr(tel,'.+).+-.+') as 전화번호
from student;-- 예제) 문자열에서 일부 문자열 추출(학원명, 주소, 전화번호, 시작일, 종료일)
select regexp_substr('아이티윌 ( 서울 강남구 ☎ 02-6255-8002 )
훈련기관정보보기 훈련기간 : 2018-10-12 ~ 2019-03-27',
'([가-힝]+) \(',1,1,null,1) as 학원명,
regexp_substr('아이티윌 ( 서울 강남구 ☎ 02-6255-8002 )
훈련기관정보보기 훈련기간 : 2018-10-12 ~ 2019-03-27',
'\( ([가-힝 ]+) ☎',1,1,null,1) as 주소,
regexp_substr('아이티윌 ( 서울 강남구 ☎ 02-6255-8002 )
훈련기관정보보기 훈련기간 : 2018-10-12 ~ 2019-03-27',
'☎ (.+) \)',1,1,null,1) as 전화번호,
regexp_substr('아이티윌 ( 서울 강남구 ☎ 02-6255-8002 )
훈련기관정보보기 훈련기간 : 2018-10-12 ~ 2019-03-27',
'\: (.+) ~',1,1,null,1) as 시작일,
regexp_substr('아이티윌 ( 서울 강남구 ☎ 02-6255-8002 )
훈련기관정보보기 훈련기간 : 2018-10-12 ~ 2019-03-27',
'~ (.+)',1,1,null,1) as 종료일,
regexp_substr('아이티윌 ( 서울 강남구 ☎ 02-6255-8002 )
훈련기관정보보기 훈련기간 : 2018-10-12 ~ 2019-03-27',
'(.+) ( (.+) ☎ (.+) ) .+ : (.+) ~ (.+)',1,1,null,1) as 전체 -- 출력안되네ㅠ
from dual;-- 문제 1)
SELECT
REGEXP_SUBSTR('http://www.example.com/products',
'http://([[:alnum:]]+.?){3,4}/?') "REGEXP_SUBSTR"
FROM DUAL;
--1) http://www.example.com/products
--2) http://www.example.com/
--3) http://www.example.com
--4) null
-- 정답2번-- 문제 2)
SELECT
REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR"
FROM DUAL;
--1) 500 Oracle Parkway, Redwood Shores, CA
--2) , Redwood Shores,
--3) Redwood Shores
--4) 500 Oracle Parkway,
-- 정답2번-- 문제 2-1)
SELECT
REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+',1,3)
FROM DUAL;
--1) 500
--2) Oracle
--3) Parkway
--4) Parkway,
-- 정답4번— 2. regexp_replace
regexp_replace(대상,찾을문자열패턴[,바꿀문자열][,검색위치][,발견횟수][,옵션]);
— 바꿀문자열 생략 시 문자열 삭제
— 검색위치 생략 시 1
— 발견횟수 생략 시 0(전체)
-- 예) 이름 마스킹
select regexp_replace('김나나','나','X',1,1)
from dual;-- 예) PROFESSOR ID에서 숫자 삭제
select id,
regexp_replace(id, '\d'), -- 모든 숫자 삭제
regexp_replace(id, '\d{2,}'), -- 2개 이상의 숫자 삭제
regexp_replace(id, '[[:digit:]]'),-- 모든 숫자 삭제
regexp_replace(id, '[0-9]') -- 모든 숫자 삭제
from professor;-- 예제) professor id에서 특수기호 삭제
update professor set id = 'captain!!' where id = 'captain';
update professor set id = 'simson_1' where id = 'simson';
commit;
select id,
regexp_replace(id,'[[:punct:]]'), -- 모든 특수기호 삭제
regexp_replace(id,'\W'), -- _ 만 남기고 삭제
regexp_replace(id,'\W|_') -- 모든 특수기호 삭제
from professor;-- 예제) professor id에서 only-u, silver-her 값을 전체 삭제(null로 리턴)
select id,
regexp_replace(id,'.[-].')
from professor;-- 예제) 다음 테이블 생성 후(price.csv) 상품명에 괄호 안에 있는 모든 글자를 괄호포함 삭제
create table price
(
상품명 varchar2(1000),
조사일 varchar2(1000),
판매가격 number,
판매업소 varchar2(1000),
제조사 varchar2(1000),
세일여부 varchar2(5),
원플러스원 varchar2(5)
);
select distinct 상품명,
regexp_replace(상품명, '(.+)'),
regexp_replace(상품명, '(([A-z0-9가-힝 ]|[[:punct:]])+)')
from price;-- 예제) 아래 테이블 생성 후(oracle_alert_testdb.log) 코드, 에러내용 으로 나눠 출력.
create table alertlog
(
text varchar2(4000)
);
select text,
regexp_replace(text,':.+') as code,
regexp_replace(text,'.\d{4,5}:') as error_description1, regexp_replace(text,'ORA-\d:') as error_description2,
regexp_replace(text,'.+:') as error_description3
from alertlog
where text like '%ORA-%';
-- 강사님 방법
select text,
regexp_substr(text,'ORA-(\d+):? (.+)',1,1,null,1) as code,
regexp_substr(text,'ORA-(\d+):? (.+)',1,1,null,2) as error
from alertlog
where text like '%ORA-%';