2021.02.08(Mon) 📌 TIL

-----------------------------[오전]--------------------------------

https://www.ibm.com/support/knowledgecenter/ko/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.plsql.doc/doc/c0053892.html
코드참고
https://programmers.co.kr/learn/challenges?tab=sql_practice_kit
코딩테스트연습

 

---DBA에 롤 부여
SQL> GRANT DBA TO ora_user;
SQL> commit;
SQL> comm ora_user/hong
SQL> show user

 

--교재 39페이지 임포트명령어 한줄로 기술
윈도우시작버튼 cmd
C:\WINDOWS\system32> cd
C:>cd backup
C:\backup> dir
imp ora_user/hong file=expall.dmp log=empall.log ignore=y grants=y indexes=y full=y
imp ora_user/hong file=expcust.dmp log=empcust.log ignore=y grants=y indexes=y full=y
imp system/1234 file=expall.dmp log=empall.log ignore=y grants=y indexes=y full=y
imp system/1234 file=expcust.dmp log=empcust.log ignore=y grants=y indexes=y full=y

 

 

C:\backup> imp
     system/1234 file=expall.dmp
     log=empall.log ignore=y grants=y indexes=y full=y
C:\backup> imp
     system/1234 file=expcust.dmp log=empcust.log ignore=y grants=y indexes=y full=y

 

 

SQL>
drop table sales ;
create table sales (
     prod_id number(6,0) not null,
     cust_id number(6,0) not null,
     channel_id number(6,0) not null,
     employee_id number(6,0) not null,
     sales_date date default sysdate not null,
     sales_month varchar2(6 ),
     quantity_sold number(10,2),
     amount_sold number(10,2),
     create_date date default sysdate,
     update_date date default sysdate
);

---

PL/SQL/ = PSQL 설명
데이터베이스에서 함수 = 메소드 = 함수기능
PL프로시저언어 = Procedure Language = 처리 =함수기능

 

 

set serverputput on --설정해야 결과내용이나 메세지 출력
선언
begin
end;
/

 

============================================================

 

📃[ 문제1] table 연결안하고 반복문으로 while loop end loop구구단

set serveroutput on

 

declare

  dan number := 5 ;
  i number := 1 ;
begin
  while i < 10
    loop
      dbms_output.put_line(dan || '_' || i || '=' || dan_i ) ;
      i := i+1;
    end loop ;
end;
/

 

 

📃[문제2] lotto테이블 간단한 저장

--lotto테이블 생성

drop table lotto ;
create table lotto(
  title varchar2(10), content varchar2(10),
  writer varchar2(10), regdate date, viewcnt number(3)
);
commit ;

 

col name for a10
col content for a10
col writer for a10
select * from lotto;
desc lotto;

 

delete from lotto;
commit;

 

---로또 저장 프로시저 만들기

SQL> declare
  2   i number :=0 ;
  3   begin
  4   while i <3
  5   loop
  6   i := i+1;
  7   insert into lotto
  8   values( concat('월요', i), concat('내용', i), concat('kim',i), sysdate, 2) ;
  9   end loop ;
  10  end;
  11 /

 

ㅣ- 단점을 프로시저 이름 주어서 명명 => 실행할때 반드시 이름으로 실행! 💡
    create or replace procedure lotto_insert0208

  is

 i number :=0 ;
begin
  while i <3
    loop
       i := i+1;
       insert into lotto
       values( concat('엘사', i), concat('평강', i), concat('lee',i), sysdate, 7) ;
       commit;
  end loop ;
end;
/

 

 

SQL> select object_name from user_procedures;
SQL> exec lotto_insert0208 -- ( )여부 상관없이 둘다 성공
SQL> exec lotto_insert0208( ) -- ( )여부 상관없이 둘다 성공, ()안에는 입력값

---

SQL> select table_name from user_tables ;
SQL> select * from tab ;
SQL> select object_name from user_procedures;

 

 

 

📃[문제3] guest테이블 while반복문대신 for반복문 데이터 전체조회 (묵시적 커서명령)

 

create or replace procedure guest_sp_select
   is
begin
   for g in
       ( select * from guest order by sabun )
    loop
       dbms_output.put_line( g.sabun || ' ' || g.name || ' ' || g.sabun || g.wdate ) ;
    end loop ;
end;
/

 

SQL> exec guest_sp_select ;
SQL> select object_name from user_procedures;

 

 

📃[문제4] guest테이블 for반복문 데이터 전체조회 cursor (명시적 커서명령)

               ㅣ- 교재 344, 345페이지

커서선언 cursor is 쿼리
커서열기 open 커서면
커서패치 fetch 커서명
커서닫기 close
     ㅣ- 우리는 커서명령sp=pl명령어 몰라도 페에징+검색+댓글 잘할수있지만
          면접때 물어볼수 있으니 실습해두면 도움 💡

 

 

create or replace procedure guest_cursor_select
is
    vsabun guest.sabun%TYPE;
    vname guest.name%TYPE;
    vtitle guest.title%TYPE;
    vwdate guest.wdate%TYPE;
    vpay guest.pay%TYPE;
    vhit guest.hit%TYPE;
    vemail guest.email%TYPE;

 

    cursor gcs --커서선언 cursor 커서이름 is 쿼리
        is
        select * from guest ;
begin
    open gcs ;
        loop
          fetch gcs into vsabun,vname,vtitle,vwdate,vpay, vhit, vemail ;
           exit when gcs%notfound;
           dbms_output.put_line(vsabun || ' ' || vname || ' ' ||vtitle || ' ' || vwdate || ' ' || vemail );
        end loop ;
    close gcs;
end;
/

 

-----------------------------[오후]--------------------------------

 

📃[345페이지 1시45분까지 기술]

set serveroutput on

 

DECLARE
    --사원명을 받아오기 위한 변수 선언
    vs_emp_name employees.emp_name%TYPE;

 

    --커서 선언, 매개변수로 부서코드를 받기
    CURSOR cur_emp_dep(cp_department_id employees.department_id%TYPE)
    IS
    SELECT emp_name
    FROM employees
    WHERE department_id = cp_department_id;

 

BEGIN
    --커서 오픈(매개변수로 90번 부서를 전달)
    OPEN cur_emp_dep(90);

 

    --반복문을 통한 커서 패치 작업
    LOOP

    --커서 결과로 나온 로우를 패치함(사원명을 변수에 할당)
    FETCH cur_emp_dep INTO vs_emp_name;

 

    --패치된 참조 로우가 더 없으면 LOOP 탈출
    EXIT WHEN cur_emp_dep%NOTFOUND;

 

    --사원명을 출력
    DBMS_OUTPUT.PUT_LINE(vs_emp_name);

 

END LOOP;

CLOSE cur_emp_dep;
END;
/

 

 

📃[354페이지 기술]

select ( select department_name from departments d
       where e.department_id = d.department_id ) as dep_name ,
    e.emp_name
    from employees e
where e.department_id = 90 ;

 

exec guest_sp_select

 

////////////////////////////////////////////////////////////
guest테이블 한건수정
guest테이블 한건 등록

~~~.java 문서에 연결

~~~.jsp 문서에 연결

 

 

📃[문제5] guest테이블 수정커서 명령 사용안함

create or replace procedure guest_sp_update
(
    vsabun in number,
    vname in varchar2,
    vtitle in varchar2,
    vwdate in date,
    vpay in number,
    vhit in number,
    vemail in varchar2
)
is

 

begin
    update guest set name=vname, title = vtitle,
    wdate=sysdate, pay=vpay , hit=vhit , email=vemail
    where sabun = vsabun ; 
      dbms_output.put_line( vsabun || '수정 성공했습니다 ' );
end;
/

 

 

SQL> commit;
SQL> select * from guest;
SQL> exec guest_sp_update(7789, 'sunday','monday', 123, 27, 'sm@tis.com') ;
SQL> select * from guest ;
SQL> 에러 exec guest_sp_update('길동','둘리', 987, 29, 'kt@tis.com', 7789) ;
SQL> 정답 exec guest_sp_update(7789, 'LAs, 'LBs', 987, 29, 'sm@tis.com') ;
SQL> select * from guest ;

 

 

📃[문제6] guest테이블 신규커서 명령 사용안함

create or replace procedure guest_sp_insert
(

    vsabun in number,
    vname in varchar2,
    vtitle in varchar2,
   --vwdate in date,
   vpay in number,
   --vhit in number,
   vemail in varchar2
)
 is
begin
insert into guest(sabun, name, title, wdate, pay, hit, email)
values( vsabun, vname, vtitle, sysdate, vpay, 0, vemail) ;
dbms_output.put_line( vsabun || '수정 성공했습니다 ' );
end;
/

 

SQL> commit;
SQL> select * from guest;
SQL> exec guest_sp_insert(9997, 'gugu', 'dan', 81, 'gu@daum' ) ;
SQL> exec guest_sp_insert(9984, 'you', 'we', 71, 'we@daum') ;
SQL> commit;
SQL> select * from guest;

---

💻자바기초 이클립스
Day1116 =>오.버 => Project Properties클릭 => java build path
  ㅣ- JRE System Library (원래 있던 폴더)
  ㅣ- net.tis.day16
        ㅣ- TestGuestDB.java 문서 -> prepareStatment
        class TestGuestDB { TestGuestDB(){ } dbInsert(){ } 메인함수(String[]){ } }
        ㅣ- DB.java 문서 복사붙여넣기 새이름저장 -> TestDB.java
        ㅣ- Global.java 문서 복사붙여넣기 새이름저장 -> TestGuestGlobal.java
  ㅣ- Referenced Librarie (새로 생성됨, ojdbc.jar 파일 포함)
        |-ojdbc6.jar

 

 

*Project Properties
플젝속성창 왼쪽항목
Java Build Path클릭
|-3번째탭 Libraries클릭 2번째 버튼 Add External JARS...클릭 ojdbc6.jar선택
참고] C:\Mtest\ojdbc6.jar파일