23.3.6

 

  [오라클 계정 생성]   
   
   --계정 생성시 세션 변경해야 함
   alter session set "_ORACLE_SCRIPT"=true;
   
   --사용자 계정 목록
   select username from all_users;
   
   
   --테이블스페이스(테이블의 저장 공간) 생성
   
   형식) create tablespace 테이블스페이스이름
      datafile '데이터파일경로' size 초기사이즈
      autoextend on
      next 자동증가사이즈
      maxsize 최대사이즈;

연습)
create tablespace java202301
datafile 'I:\java202301\database\java202301.dbf' size 50m
autoextend on 
next 10m
maxsize unlimited;

--테이블스페이스 종류 확인
형식) select tablespace_name, contents from dba_tablespaces;
 /////////////////////////////////////////////////////////////////  
   
   
  --사용자 계정 생성
  형식) create user 아이디 identified by 비번
      default tablespace 테이블스페이스이름;
  
   연습)
   create user itwill identified by 1234
   default tablespace java202301;
   
   
   --사용자 계정 목록
   select username from all_users;
   
   --모든 권한 부여
   grant connect, resource, dba to itwill
   
   commit
   
   
   
   --명령프롬프트에서 권한 부여된 itwill 로그인하기
   >sqlplus itwill/1234
   ////////////////////////////////
   
   --사용자 계정 삭제
   drop user itwill cascade;
   commit
   
   
   --테이블스페이스 삭제
   형식) drop tablespace 테이블스페이스명
      including contents and datafiles
      cascade constraints;
   
   연습) drop tablespace java202301
        including contents and datafiles
        cascade constraints
   
   commit

 

////////////////////////////////////////////////////////////////////////

   [뷰 view]
   --뷰생성(두번째부터는 수정)
   --형식) create or replace view 뷰이름 as 실제로 실행할 sql문
   
   --테이즐, 뷰 목록 확인
   select * from tab --모든 객체 종류 확인
   select * from tab where tabtype ='TABLE' --테이블 목록
   select * from tab where tabtype = 'VIEW' --뷰 목록
   
   --sungjuk 테이블 목록 확인
   select * from sungjuk 
   
   --주소가 서울, 제주 지역인 학생의 이름, 국,영, 수, 주소 찾기
   select uname, kor, mat, eng, addr
   from sungjuk
   where addr in ('Seoul', 'Jeju')
   
   
   create or replace view test1_view
   as
   select uname, kor, mat, eng, addr 
   from sungjuk
   where addr in ('Seoul', 'Jeju')
   
   --생성된 뷰는 테이블처럼 사용 가능
 select * from test1_view
 
 
 --데이터사전(user_views테이블)에서 뷰의 세부 정보 확인
 select * from user_views
 select text from user_views where View_name = 'TEST1_VIEW'


--뷰 삭제
drop view test1_view
   
   ////////////////////
   
   create or replace view test2_view
   as 
select uname as 이름, kor as 국어, eng as 영어, mat as 수학, addr as 주소
from sungjuk
where addr in ('Seoul', 'Jeju');

select * from test2_view;

/////////////////////////////////////////////////////

문) 수강신청한 학생들의 학번, 총학점을 뷰로 생성하시오(test3_view)
     g1001  14학점
     g1002  8학점
     g1005  12학점
     g1006  3학점
   
   
   --1) 수강신청과목의 학점을 가져와 학번별로 총학점 구하기
   select su.hakno, sum(gw.ghakjum) || '학점' as 총학점
   from tb_sugang su join tb_gwamok gw
   on su.gcode = gw.gcode
   group by su.hakno
   
   
   --2) 위의 결과를 test3_view 뷰 생성
   create or replace view test3_view
   as
       select su.hakno as 학번, sum(gw.ghakjum) || '학점' as 총학점
       from tb_sugang su join tb_gwamok gw
       on su.gcode = gw.gcode
       group by su.hakno
       
       
       select * from test3_view
   
   
   --3) 위에서 생성한 뷰와 학생테이블 조인해 학번, 이름, 총학점 출력
   select tb_student.uname, test3_view.*
   from tb_student join test3_view
   on tb_student.hakno = test3_view.학번
   order by tb_student.hakno

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

  [PL/SQL (Procedural Language) 프로시저] (t-sql문)
   - 절차적인 데이터베이스 프로그래밍 언어
   - 변수, 조건문, 반복문
   --접두 'sp_'는 메이커측에서 제공하는 프로시저에 붙는 것이므로 자체제작 프로시저에는 붙이지 말 것
   --프로시저를 통한 호출이 속도가 빠름
   
   --콘솔창 출력하기 위한 사전 준비작업
    set serveroutput on;
    
    [PL/SQL 기본 문법]
    
1) 변수선언
declare --여기에 declare 대신 create+이름 넣으면 자료 저장 가능
    --변수선언, 대입연산자 ':='
    a number := 3;
    b number := 5;
    
begin 
    --콘솔창 출력    
    dbms_output.put_line('* 실행 결과 *');
    dbms_output.put_line('a');
    dbms_output.put_line('b');
    dbms_output.put_line('a+b');
    --결합연산자 ||
    dbms_output.put_line( a || '+' || b || '=' || (a+b) );    
end;


    2) 조건문
    declare
    --성적 프로그램
    uname   varchar2 (50) := '무궁화';
    kor     number := 100;
    eng     number := 95;
    mat     number := 80;
    aver     number := ( kor + eng + mat ) / 3;
    grade   varchar2(10) := NULL;
   
    begin
        if aver > 90 then grade := 'A';
        elsif aver > 80 then grade := 'B';
        elsif aver > 70 then grade := 'C';
        elsif aver > 60 then grade := 'D';
        else grade := 'F';
        end if;
        
        --출력
        dbms_output.put_line('* 실행 결과 *');
        dbms_output.put_line('이름 : ' || uname);
        dbms_output.put_line('국어 : ' || kor);
        dbms_output.put_line('수학 : ' || mat);
        dbms_output.put_line('영어 : ' || eng);
        dbms_output.put_line('평균 : ' || round( aver, 2));
        dbms_output.put_line('학점 : ' || grade );       
    end;
    
    
    
    3) 반복문 
   declare 
   --구구단 출력
   dan number := 4;
   i   number default 0;
   
   begin
   while i < 10 loop
   i := i+1;
   exit when i = 10;
   dbms_output.put_line(dan || '*' || i || '=' || (dan*i) ); 
   end loop;
end;
   
   
   /////////////////////////////////////////////////////////////
   연습) 프로시저를 이용해서 sungjuk테이블에서 sno=64행 조회
   select * from sungjuk where sno = 64;
   
   declare 
        v_sno     number;
        v_uname   varchar2(50);
        v_kor     number;
        v_eng     number;
        v_mat     number;
        v_addr    varchar2(30);
        v_wdate   date;
        v_juso    varchar2(30);  --한글주소
   begin
        --SQL문 작성
        select SNO, UNAME, KOR, ENG, MAT, ADDR, WDATE 
        into v_sno, V_uname, v_kor, v_eng, v_mat, v_addr, v_wdate
        from sungjuk 
        where sno = 64;
        
        if     v_addr='Seoul' then v_juso :='서울';
        elsif  v_addr='Jeju' then v_juso :='제주';
        elsif  v_addr='Suwon' then v_juso :='수원';
        elsif  v_addr='Busan' then v_juso :='부산';
        end if;
        
        --출력        
        dbms_output.put_line('* 실행 결과 *');
        dbms_output.put_line('번호 : ' || v_sno);
        dbms_output.put_line('이름 : ' || v_uname);
        dbms_output.put_line('국어 : ' || v_kor);
        dbms_output.put_line('영어 : ' || v_eng);
        dbms_output.put_line('수학 : ' || v_mat);
        dbms_output.put_line('주소 : ' || v_addr);
        dbms_output.put_line('주소 : ' || v_juso);
        dbms_output.put_line('작성일 : ' || v_wdate);
   end;
   ////////////////////////////////////////////////////////////
   ● [프로시저 생성]
형식) create or replace  procedure 프로시저명


● [프로시저 삭제]
형식) drop  procedure 프로시저명


● [프로시저 호출]
형식) execute 프로시저명

//////////////////////////////////////////////////////////

연습) test_proc 프로시저 생성
--프로시저 생성
create or replace procedure test_proc
is
begin
    dbms_output.put_line('sp_test 프로시저 호출');
end;
   

--test_proc 프로시저 호출
execute test_proc;

--test_proc 프로시저 삭제
drop procedure test_proc;

 

////////////////////////////

[성적 프로시저]
   참조 http://pretyimo.cafe24.com/lectureRead.do?lectureno=192
    sungjuk테이블에서 Create/Read/Update/Delete 관련 프로시저


1) 행추가 프로시저(Create)
    create or replace procedure sungjukInsert
    (
        --매개변수(parameter) 선언
        --in 입력매개변수
        --sungjuk테이블의 uname칼럼과 동일한 타입
        v_uname  in  sungjuk.uname%type
        ,v_kor   in  sungjuk.kor%type
        ,v_eng    in  sungjuk.eng%type
        ,v_mat    in  sungjuk.mat%type
        ,v_addr   in  sungjuk.addr%type
    )
    is
    begin
        insert into sungjuk(sno, uname, kor, eng, mat, tot, aver, addr)
        values(sungjuk_seq.nextval, v_uname, v_kor, v_eng, v_mat
        , (v_kor + v_eng + v_mat ), (v_kor + v_eng + v_mat )/3, v_addr);
        commit;
    end;
    
    
--sungjukInsert 프로시저 실행
execute sungjukInsert('오필승', 100, 99, 95, 'Seoul');

--sungjukInsert 결과확인
select * from sungjuk order by sno desc

//////////////////////////////////////////////////

2) 행수정 프로시저(Update)
--문) sno=61 행을 아래 값으로 이름, 국, 영, 수 , 주소, 총점, 평균을 수정하는 프로시저 생성
execute sungjukUpdate('코리아', 50, 60, 90,'Jeju', 62);

   create or replace procedure sungjukUpdate
    (
        v_uname  in  sungjuk.uname%type
        ,v_kor   in  sungjuk.kor%type
        ,v_eng    in  sungjuk.eng%type
        ,v_mat    in  sungjuk.mat%type
        ,v_addr   in  sungjuk.addr%type
        ,v_sno    in sungjuk.sno%type
    )
    is
    begin
        update sungjuk
        set uname = v_uname, kor = v_kor, eng = v_eng, mat = v_mat, aver = (v_kor + v_eng + v_mat )/3, addr = v_addr
        where sno = v_sno;
   
    end;

     commit
    
    ////////////////////////////////////////////////////// 
    
3) 행삭제 프로시저(Delete)
--문) sno = 61행 삭제
 execute sungjukUpdate(62);
 create or replace procedure sungjukUpdate
  (
        v_sno    in sungjuk.sno%type
    )
    is
    begin
    delete from sungjuk where sno = v_sno;
    
    commit;
    
    end;
    ///////////////////////////////////////////////////
    

    
4) 상세보기(Read)

    create or replace procedure sungjukRead
    (
     v_cursor out sys_refcursor    -- out 출력매개변수
    ,v_sno sungjuk.sno%type
    )
    is
    begin
        open v_cursor for 
        select * from sungjuk where sno=v_sno;
        -- close v_cursor; 커서반납
    end;

-------------------------

5) 전체목록(Read)
   create or replace procedure sungjukList
   (
    v_cursor out sys_refcursor
    )
is
begin
    open v_cursor for 
    select * from sungjuk order by sno desc;
    loop
        fetch v_cursor into rec;
        exit when v_cursor%NOTFOUND;
    end loop;
    -- close v_cursor; 커서반납
end;

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 [index 색인]
 
   ● [인덱스 생성]   
- 형식) create index 인덱스명 on 테이블명(칼럼명)
  
● [인덱스 삭제]  
- 형식) drop index 인덱스명
   
   /////////////////////////////////////////////////////////////////////////
   
   [연습1] -pk가 없는 테이블
   --테이블생성
create table emp2(
  id        number(5)    
  ,name     varchar2(25)
  ,salary   number(7, 2)
  ,phone    varchar2(15)
  ,dept_id  number(7)
);

--행추가
insert into emp2(id,name) values (10,'kim');
insert into emp2(id,name) values (20,'park');
insert into emp2(id,name) values (30,'hong');

commit;

--인덱스 생성 전
select * from emp2 where name = 'hong'; --커서 여기에 놓고 f10, cost 3

--인덱스 생성 후 (색인페이지가 별도로 생성됨)
create index emp2_name_idx on emp2(name);

--인덱스 삭제
drop index emp2_name_idx

select * from emp2 where name = 'kim'; --커서 여기에 놓고 f10, cost 2
--f10 계획설명 보기 -> full scan cost2


   
   
   
   ////////////////////////////////////////////////////////////////////
   
   [연습2] - pk가 있는 테이블
   
   --pk는 인덱스가 자동으로 생성되며 정렬된다
create table emp3 (
   no    number       primary key --해당 칼럼은 인덱스 자동생성
  ,name varchar2(10)
  ,sal  number
);
   
   select * from emp3 where no = 3;
   --f10 계획설명 보기 -> unique scan, cost 1
   
   
   //////////////////////////
   
   
   --연습3) 100만건의 행 대상으로 cost 비교
create table emp4(
   no     number
  ,name   varchar2(10)
  ,sal    number
);
   
   
  프로시저를 이용해 100만행 추가하기######################################## 
   
   declare  --선언문
    --변수선언
    i     number      := 1;      --i변수에 1 대입 := 연산자
    name  varchar(20) := 'kim';
    sal   number      := 0;
begin
while i <= 1000000 loop
    if i mod 2 = 0 then
            name := 'kim'  || to_char(i);
            sal  := 300;
        elsif i mod 3 = 0 then
            name := 'park' || to_char(i);
            sal  := 400;
        elsif i mod 5 = 0 then
            name := 'hong' || to_char(i);
            sal  := 500;
        else
            name := 'shin' || to_char(i);
            sal  := 250;
        end if;

insert into emp4(no, name, sal) values (i, name, sal); --행추가
        i := i+1;
    end loop;
end;
   
   
   #########################################################################
   
   select count(*) from emp4
   commit
   select * from emp4;
   
   --1) 인덱스를 사용하지 않은 경우
   select * from emp4 where name = 'kim466'; --f10 계획설명
                                             --full scan, cost 900
   
   
   select * from emp4 where no = 466;        --full scan, cost 898
   
   select * from emp4 where sal > 300;       --full scan, cost 901
   
   
   --2) 인덱스를 사용한 경우
   --name칼럼 기준 인덱스 생성
   create index emp4_name_idx on emp4(name);
   
   select * from emp4 where name = 'kim466'; --range scan, cost 3
   
   
   --name칼럼과 sal칼럼을 기준으로 인덱스 생성
   create index emp4_name_sal_idx on emp4(name, sal);
   
   select * from emp4 where name = 'kim466' and sal > 200;--range scan, cost 3
   
   
   --인덱스 데이터 사전
   select * from user_indexes;
   select * from user_indexes where index_name like 'EMP4%';

 

///////////////////////////////////////////////////////////////////

   트리거 Trigger
   
   --콘솔창 출력 dbms_output.put_line을 사용
   set serveroutput on;
   
///////////////////////////////////////////////

-- 연습)
--sungjuk 테이블에서 국어점수 평균 구하기

select avg(kor)--70.30
from sungjuk

--국어점수가 추가, 수정, 삭제되면 전체 국어평균도 달라진다.
--이때 바뀐 국어평균 값을 출력

   create or replace trigger kor_trigger
   after
        insert or update or delete on sungjuk
   declare 
        avg_kor number;
   begin
        select avg(kor) into avg_kor from sungjuk;
        dbms_output.put_line('국어평균: ' || avg_kor);
   end;
   
   
   select * from sungjuk order by sno desc;
   
   
   --행수정
   update sungjuk set kor = 0 where sno = 72; --국어평균 64.615 뜸
 
   --행삭제
   delete from sungjuk where sno = 71;
   
   --행추가
   insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
   values(sungjuk_seq.nextval, '이순신', 100, 100, 100, 'Seoul', sysdate);
  

 

///////////////////////////////////////////////////////////////////////////////////////

fuction 함수
   
select * from sungjuk order by sno desc
   
   --연습) sno = 81의 국어점수를 5점 더하고 수정된 국어점수 반환하는 함수 생성
   
   create or replace function fn_update(v_sno number)--매개변수는 sno
   return number --반환되는 자료형
   is 
    v_kor number;
   begin
    update sungjuk set kor = kor +5 where sno= v_sno;
    select kor into v_kor from sungjuk where sno = v_sno;
    return v_kor; --인상된 국어점수
   end;
   
   --위의 fn_update 함수는 명령프롬프트에서 테스트
   sqlplus system/1234
   SQL> var korea numberl
   SQL> execute :korea := fn_update(72);
   SQL> print korea;

     KOREA
----------
         5