1026 (28일차) -sql 8일

--1026
--일반 객체 / pl,sql객체
--====================================
--DATABASE OBJECT 1
--====================================
--db를 효율적으로 운영하기 위해 미리 생성해둔 개체(구조) ex테이블,사용자,뷰..등등..

select object_type from all_objects;

------------------------------------------------------------
--DATA DICTIONARY
------------------------------------------------------------
--DB 모든 객체에 대한 메타정보를 가진 뷰
--관리자 소유 이지만, 읽기 권한을 부여 받아 사용
--사용자는 DD에 대해 쓰기 할 수 없음.
--DDL 등을 통해 객체의 정보를 변경하면 DD에는 자동으로 변경된 사항이 반영됨.;

--3가지 구분
--1)user_xxx 자신이 생성한 객체에 대한 dd
--2)all_xxx   1번을 포함해서 사용권한을 부여받은 객체에 대한 dd
--3)dba_xxx  관리자만 열람 가능한 dd(db의 모든 정보를 열람 가능)
select * from sh.employee;

--모든 dd 조회
select * from dict;

--소유한 테이블 조회
select * from user_tables;
select * from tabs; --synonym user_tables에 대한 별칭.
select * from tab; --간단ver

--권한/롤 확인
select * from user_sys_privs;
select * from user_role_privs; 

select * from role_sys_privs; --롤,권한 모두 보기

--제약 조건 조회
select * from user_constraints;  --BIN$ : DROP한 테이블

--뷰(안만들면 안보임)
select * from user_views;
--인덱스(pk,uq 자동생성)
select * from user_indexes;
--시퀀스(안만들면 안보임)
select * from user_sequences;

--all_xxx
--자신이 소유한 객체와 사용권한을 부여받은 객체
select * from all_tables;
--dual:가상테이블, 관리자가 일반사용자에게도 사용 허가한 테이블

--view
--data dictionary는 모두 sys의 소유이며 사용권한을 부여받아 읽기 가능
select * from all_views where owner ='SYS' and view_name like 'USER_%';

--dba_xxx
--sh계정으로는 볼 수가 없고, system(관리자) 계정으로만 접근 가능.
select * from dba_tables;

select * from dba_tables where owner = 'SH';

--사용자별 권한 확인
select * from dba_sys_privs where grantee ='SH'; --create table, create session
select * from dba_role_privs where grantee ='SH'; --resource,connect

--테이블 관련 권한 확인
select * from dba_tab_privs where owner ='SH'; --여기서 확인, 권한은 자동으로 갱신되어서 select만해도 확인가능
grant select,insert on sh.tbl_coffee to qwerty; --권한을 주고

-------------------------------------------------------------------------
--STORED VIEW
-------------------------------------------------------------------------
--하나 이상의 테이블에서 원하는 데이터를 선별해 새로운 가상테이블을 생성
--실제 데이터를 가지고 있지 않고, 실제 테이블에 대한 창구 역할.
--view 용량은 매우 작다. (실데이터가 없어서)
--inline view는 1회성 인데 비해, stored view는 view 객체를 저장해서 재사용.(테이블처럼..)
--create view는 resource롤에 포함이 되어있지 않으므로 별도로 권한부여를 해야함.
--or replace 옵션: 없으면 생성, 있으면 대체
--system계정:create view 권한 부여하기.. grant create view to sh;
grant create view to sh;
create or replace view view_emp --ORA-01031: 권한이 불충분합니다 - View VIEW_EMP이(가) 생성되었습니다.
as
select
    emp_id,
    emp_name,
    email, --email 추가시.. View VIEW_EMP이(가) 생성되었습니다.
    decode(substr(emp_no,8,1),'1','남','3','남','여') gender, 
    extract(year from sysdate)- (decode(substr(emp_no,8,1),'1',1900,'2',1900,2000)+substr(emp_no,1,2)) 나이,
    (select job_name from job where job_code = e.job_code) job_name,
    (select dept_title from department where dept_id = e.dept_code) dept_title
from
    employee e;

select * from view_emp;

--dd에서 확인
select * from user_views; --view_emp가 나온다.
--text에 보면 실제 데이터가 아닌 위에 작성한 쿼리문을 가지고 있음. 작동방식은 인라인뷰와 같음.

grant select on sh.view_emp to qwerty; --권한을 주고
--cmd에서 다른 계정의 테이블을 사용할땐 사용자명.테이블명으로 작성하기.

--view를 통해서 특정컬럼만 뽑아내면 다른 사용자들은 view에서 제공되는것만 볼 수 있음.
--원본 table에는 주민번호,주소 이런게 있지만 view로 가공할때 제외할 수 있음.
--가상컬럼,서브쿼리가 추가된 것도 view에 적용가능.


--employee_ex - D5부서원의 사번 사원명 직급명 급여 입사일 view_emp_d5 생성

create or replace view view_emp_d5  --ORA-01031: 권한이 불충분합니다 - View VIEW_EMP이(가) 생성되었습니다.
as
select
    emp_id ,
    emp_name ,
    dept_code,
    (select job_name from job where job_code = e.job_code) job_name,
    salary,
    hire_date
from
    employee_ex e
where
    dept_code = 'D5'
with check option ; --where절의 dept_code 변경방지
select * from view_emp_d5;
grant select on sh.view_emp_d5 to qwerty; --권한을 준당

--view를 통해서 dml 처리하기
--가상컬럼은 변경할 수 없다..--이거 뭔가..별칭을 붙이면 인식을 못하는듯..?지우니까 적용됨..
select salary from view_emp_d5;
update view_emp_d5 set salary = salary + 500000;

--view option
--1)or replace : 존재하면 대체

--2)with check option : 뷰 지정시에 사용한 where 절 컬럼값 변경 방지
update 
    view_emp_d5
set
    dept_code = 'D6'
where
    emp_id='206';
--ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

--3)with read only : 읽기전용

--------------------------------------------------------------------
--SEQUENCE
--------------------------------------------------------------------
-- 정수값을 순차적으로 발행하는 객체. 채번기
-- 테이블의 PK컬럼의 식별값으로 주로 사용.

/*
    create sequence 시퀀스명
    [start with 시작값] 기본값 1
    [increment by 증감값] 기본값 1
    [maxvalue 최대값 | nomaxvalue] 기본값 nomaxvalue
    [minvalue 최소값 | nominvalue] 기본값 nominvalue
    [cycle | nocycle] 기본값 nocycle
    [cache 메모리캐싱개수 | nocache] 기본값 20
    캐싱이란? : 재참조하지않고 조회한 결과를 저장해두는것

*/
create table mac_order(
    no number primary key
    );
insert into mac_order values(1); --이런식으로 값을 넣지 않고.
insert into mac_order values(시퀀스명.nextval);

--시퀀스 생성
create SEQUENCE seq_mac_order
start with 1
increment by 1
nominvalue
nomaxvalue
nocycle
cache 20;
--Sequence SEQ_MAC_ORDER이(가) 생성되었습니다.
insert into mac_order values(seq_mac_order.nextval); --2 번 실행

select * from mac_order; --실행한 만큼 추가되는걸 확인할 수있음. 절대 중복값없음

select seq_mac_order.currval from dual; --마지막 발급번호.(currval)

--dd에서 시퀀스 확인
select * from user_sequences;
--last_number 다음번호
--시퀀스번호 20까지는 ln도 21이고
--21인 순간 41로 변경됨.
--메모리가 휘발되는 경우가 있지만 pk는 고유성(중복x) 중요하기 때문에 문제는없음.
--insert가 빈번한 경우 캐싱사이즈를 늘린다.


--어제(25일) 했던 주문테이블 복붙 , 테이블명 tb_order로 변경
create table tb_order(
    no number, --new pk
    prod_no number, 
    user_id varchar2(50), 
    order_date date default sysdate, 
    cnt number default 1, --주문수량
    constraints pk_tb_order_no primary key(no),
    constraints uk_tb_order unique(prod_no, user_id, order_date)
);
create sequence seq_tb_order_no;

insert into     
    tb_order 
values(
        seq_tb_order_no.nextval,
        123,
        'jin',
        default,
        33
        );

select * from tb_order;

--pk 복합문자열 처리
--kh-20231026-0123
create table tb_order2(
    id varchar2(30), --pk
    prod_no number, 
    user_id varchar2(50), 
    order_date date default sysdate, 
    cnt number default 1, --주문수량
    constraints pk_tb_order2_id primary key(id), --제약조건 이름 겹치지않기
    constraints uk_tb_order2 unique(prod_no, user_id, order_date)
);

insert into
    tb_order2
values(
    'kh-'|| to_char(sysdate,'yyyymmdd')||'-'||to_char(seq_tb_order_no.nextval,'fm0000'),
    777,
    'sinsa',
    default,
    10
);    

select * from tb_order2;

--시퀀스 수정
--increment by 값만 변경 가능..[증감값]
--start with값은 변경 불가..[중복값이 나올 수 있어서그럼, 시퀀스객체를 삭제후 새로 생성하는 수밖에]
alter sequence seq_tb_order_no increment by 10;


-------------------------------------------------------------------------
--INDEX
-------------------------------------------------------------------------
--색인.
--테이블 데이터의 처리속도를 향상을 위해 컬럼에 대해 생성하는 색인 객체
--KEY-VALUE값으로 저장 /KEY=컬럼값 VALUE=레코드주소(ROWID)
--검색속도 향상,시스템부하 저하,전체적인 성능 향상 
--추가적인 공간이 필요하다..(<->VIEW)
--DML 작업시 매번 함께 수정된다..

--어떤 컬럼을 대상으로 인덱스를 생성해야 하는가??
--1.선택도(중복값 없는 정도)가 좋은 컬럼
    -- 선택도 좋은 컬럼 : EMP_ID , EMAIL, EMP_NO , EMP_NAME  
    -- 중간: DEPT_CODE ,JOB_CODE
    -- 선택도 나쁜 컬럼 : QUIT_YN, GENDER, 
    
--2.조인시 기준컬럼, WHERE절에 자주 사용되는 컬럼
--3.데이터가 수백만 이상인 경우..필수적으로 인덱스 사용해야한다.

--인덱스 조회
--pk,uq 제약조건 생성시 해당 컬럼에 대한 인덱스가 자동생성.=>선택도가 좋은 컬럼이라 기본적으로 생성됨..
select * from user_indexes;
select * from user_ind_columns; --컬럼확인

--한 행을 조회
select * from employee where job_code = 'J1';
select * from employee where emp_id = '201';

--사원명
select * from employee where emp_name = '송종기';

--인덱스 생성
create index idx_employee_emp_name on employee(emp_name);

--인덱스 사용시 유의 사항
--1.인덱스 컬럼(emp_no)에 변형이 가해지는 경우 인덱스 타지 않음
--ex substr(emp_no,8,1)
--2.인덱스 컬럼에 null 비교
--ex emp_name is null
--3.not 비교하는 경우
--4.인덱스컬럼의 자료형과 비교하는 값의 자료형이 다른경우★
--5.db옵티마이저 실행계획에 따라선택(힌트)

select * from employee where emp_no='631126-1548654'; --idnex unique scan
select * from employee where substr(emp_no,1,6)='631126'; --인덱스 타지 않음. table full scan

select * from employee where emp_name is null; -- table full scan

select * from employee where emp_id!='201'; --table full scan
select * from employee where emp_id=201; --table full scan(자료형 매칭x)

--index 삭제
--drop index 인덱스명
--단 pk,uq로 만들어진 인덱스는 직접 삭제 불가, 제약조건을 제거하면 인덱스도 제거됨.


--================================================
--PL/SQL
--================================================
--Procedural Language extension to SQL
--절차적 언어 확장판

--pl/sql 유형
--1.익명 블럭 1회용 실행블럭
--2.db 객체 (procedure , function , trigger...)

--콘솔 출력 set serveroutput on (세션마다/사용자바꿀때마다)
set serveroutput on
/*
    익명 블럭의 구조
    declare
        변수 선언부(선택)
    begin
        실행부 (필수)
    exception  
        예외처리(선택)
    end;
    /
...
최소로 사용한다면
begin ~
end;/ 로 해야함

*/

--예제하나 hellowolrd 출력
--PL/SQL 프로시저가 성공적으로 완료되었습니다.
begin 
    --콘솔출력
    dbms_output.put_line('Hello PL/SQL~');
    dbms_output.put_line('Hello PL/SQL~');
    dbms_output.put_line('Hello PL/SQL~');
end;
/

--employee 사번으로 사원명 조회
declare
    v_emp_id char(3) := '&사번';
    v_emp_name varchar2(20);
begin 
    --v_emp_id :='201';
    --v_emp_id := '&사번';
    
    
    --1행 조회
    select 
        emp_name
    into -- 조회된결과를 v_emp_name에.
        v_emp_name
    from
        employee
    where 
        emp_id = v_emp_id;
        
        dbms_output.put_line('사원명:'||v_emp_name);
exception
    when no_data_found then dbms_output.put_line('조회된 사원이 없습니다.');
end;
/


--declare 절 변수 선언
--변수명 [constant] 자료형 [not null] [:=초기값];
--constant 상수(대입된 값 변경불가)
--not null 필수입력

--자료형
--pl/sql자료형 사용
--char,varchar2 ,date, number 모두 사용가능.sql자료형과 이름이 같지 자료형의 크기가 다르다..
--https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-data-types.html#GUID-C3B938C9-7B0B-4AAC-8323-FEB2ED0225D0
--1.기본자료형
    --문자형 varchar2 ,char
    --숫자형 number, binary integer, pls_integer
    --날짜형 date, timestamp
    --논리형 boolean
    
--2.복합자료형
    --record
    --cursor
    --collection(배열,리스트,맵)

declare
    v_num number;
    v_num2 constant number:=77; --초기화필수
    v_bool boolean;
    v_today date := sysdate;
begin
    v_num :=100;
   -- v_num2 :=99; --'V_NUM2' 식은 피할당자로 사용될 수 없습니다
    dbms_output.put_line(v_num);
    
    v_bool := (2=2); --true
    if v_bool then
        dbms_output.put_line('참');
    end if;
    dbms_output.put_line(v_today);
end;
/

--참조변수 : 다른 테이블의 컬럼타입을 참조해서 선언
-- 1. %type
-- 2. %rowtype
-- 3. record 자료형

declare
    --테이블.컬럼%타입
    --v_emp_id char(3)
    v_emp_id employee.emp_id%type :='&사번';
    v_emp_name employee.emp_name%type;
    v_phone employee.phone%type;
begin
    select
        emp_name, phone
    into
        v_emp_name,v_phone
    from
        employee
    where
        emp_id = v_emp_id;
        
        dbms_output.put_line('사원명 : ' || v_emp_name);
        dbms_output.put_line('전화번호 : ' || v_phone);
end;
/

declare
    erow employee%rowtype;
begin
    select * --rowtype사용시 전체를 넣는다
    into erow
    from
        employee
    where 
        emp_id = '&사번';
    dbms_output.put_line('사원명 : '||erow.emp_name);
    dbms_output.put_line('이메일 : '||erow.email);
end;
/



--ex)사원명으로 전화번호,이메일,직급명,부서명 출력
declare
    --테이블.컬럼%타입
    --v_emp_id char(3)
    v_emp_name employee.emp_name%type :='&사원명';
    
    v_phone employee.phone%type; --전화번호
    v_email employee.email%type; --이메일
    
    v_job_name job.job_name%type;--직급명
    v_dept_title department.dept_title%type;--부서명
    
begin
    select
        phone,
        email,
        (select job_name from job where job_code = e.job_code) job_name,
        (select dept_title from department where dept_id = e.dept_code )dept_title
    into
        v_phone,v_email,v_job_name,v_dept_title
    from
        employee e
    where
        emp_name= v_emp_name;
        
        dbms_output.put_line('전화번호 : ' || v_phone);
        dbms_output.put_line('이메일 : ' || v_email);
        dbms_output.put_line('직급명 : ' || v_job_name);
        dbms_output.put_line('부서명 : ' || v_dept_title);
end;
/





-------------record 선언 후 사용,..-----------------------------------
--이 예제는 여러 테이블을 사용해야해서 rowtype을 사용하지 못했는데,
--record를 사용하면 가능하다..
--여러 컬럼을 조합해서 자료형 생성.

declare
    v_emp_name employee.emp_name%type := '&사원명' ;

    type my_type is record(
     v_phone employee.phone%type,
     v_email employee.email%type, 
     v_job_name job.job_name%type,
     v_dept_title department.dept_title%type
    );
    
    myrow my_type;
    
begin
    select
        phone,
        email,
        (select job_name from job where job_code = e.job_code) job_name,
        (select dept_title from department where dept_id = e.dept_code )dept_title
    into
        myrow
    from
        employee e
    where
        emp_name= v_emp_name;
        
        dbms_output.put_line('전화번호 : ' || myrow.v_phone);
        dbms_output.put_line('이메일 : ' || myrow.v_email);
        dbms_output.put_line('직급명 : ' || myrow.v_job_name);
        dbms_output.put_line('부서명 : ' || myrow.v_dept_title);
end;
/

--pl/sql에서도 dml 실행
--commit처리도 반드시 함께한다.

create table member(
    id number,
    name varchar2(50),
    create_at date default sysdate
);

select * from member;


--member 테이블에 데이터추가--
begin
    insert into
        member
        values(1,'홍길동',default);
        commit;
end;
/

--member 테이블에 데이터 변경--
begin
    update
        member
    set
        name = '홍길순'
    where
    id = 1;
    commit;
end;
/

--member 테이블에 데이터 삭제--
begin
    delete
        from member
        where id=1;
        commit;
end;
/


--ex)employee_ex 테이블에 신입사원 정보 추가..
--사번: 마지막 번호 +1
--이름,주민번호,전화번호,직급코드,급여등급은 사용자 입력값으로 처리한다.


declare
    v_emp_id employee_ex.emp_id%type;
    v_emp_name employee_ex.emp_name%type := '&사원명';
    v_emp_no employee_ex.emp_no%type :='&주민번호';
    v_phone employee_ex.phone%type :='&전화번호';
    v_job_code employee_ex.job_code%type :='&직급코드';
    v_sal_level employee_ex.sal_level%type :='&급여등급';
begin
    --마지막 사번 구하기
    select 
        max(emp_id)+1
    into
        v_emp_id
    from 
        employee_ex;
 
    insert into
        employee_ex (emp_id, emp_name, emp_no, phone, job_code, sal_level)
        values(
                v_emp_id , 
                v_emp_name, 
                v_emp_no,
                v_phone,
                v_job_code,
                v_sal_level
        );
        commit;
end;
/

select * from employee_ex;
--employee_ex 마지막 번호 확인
select 
    emp_id
from
    employee_ex
where
    rownum=1
order by
    emp_id desc;
--------------------------
insert into 
    employee_ex 
value 
    ((to_char(to_number(emp_id,'999')+1),
    '농담곰',
    '111111-1111111',
    '010-1234-5678',
    'J1',
    'S1')
select 
    emp_id 
from 
    employee_ex
where 
    rownum=1
order by
    emp_id desc;
    
--where
--    rownum=1
--order by
--    emp_id desc);




begin
    insert into
        member
        values(1,'홍길동',default);
        commit;
end;
/