[Oracle] PL/SQL - 명령프롬프트 정리

＊int → number
＊list→l
＊run→/  (실행)
＊delete→del
＊spool on   - 명령어 실행결과목록 모든것이 저장된다.
＊spool off
＊desc - 특정데이터 타입보는것(=sp_help)
＊date날짜  {yy(rr)/mm/dd}
＊number - 숫자형 데이터 타입(전체자리수,소수이하자리수,기본38자리수를 차지한다.)
         ┌ char(길이)
＊문자형
         └ varchar2(길이)
＊동영상,사운드 : Raw (2G byte)
＊set verify off (구,신 안보이게)
＊set serveroutput on (화면출력)
-------------------------------------------------------------------------------
저장폴더 mysql ==>  create user sky identified by password 패스워드 users;
권한주기 ==>  grant connect , resource to sky;
연결하기 ==>  conn sky/password

(연습1) 
SQL> select ename, job, hiredate from emp
           where job='CLERK';

(연습2) 
SQL> select ename, job, hiredate from emp
          where ename like 'A%';

(연습3) 날짜사이
SQL>  select ename, job, hiredate, sal from emp
            where hiredate between '81/12/03' and '81/12/31' ;

(연습4)  급여가 많은순
SQL> select ename, job, hiredate, sal, comm from emp
           order by 5 desc;

##유형
1) Oracle- select '이름은 ' || ename || ' 입니다' from emp;
2) MySQL- select '이름은' + ename + '입니다' from emp;

(연습5) 
sal int형
Oracle - select '급여는 ' || sal || ' 입니다' from emp;
MySQL - select '급여는' + sal + '입니다' from emp;
  ※ MySQL: 오류 발생됨. (이유)연결연산자가 문자형/숫자형 때문,  그러나 Oracle은 자동변환.

(연습6)  is null과 같다
Oracle - select ename, nvl(comm,0) from emp;
MySQL - select ename, is null(comm,0)from emp;

(연습7) null을 빼고 계산할때
Oracle - select ename, sal, sal+nvl(comm,0) "급여총합" from emp;

(연습8) 나머지구하는 함수
Oracle- select mod(10,3) from dual;                        // mod( 나눠야 되는 정수, 나눌 숫자) ==> 나머지 값만 반환하는 함수
MySQL - select 10%3

 

## 입력값을 받아서 테이블을 보여주는 기능
SQL> select ename, job from emp
            where job='&1' and deptno=&2;

## 상세정보 볼 때
① desc user_constraints
ex> select table_name, constraint_name from user_constraints
       where table_name='SAWON';

1) 활성화

• disable constraint  비활성화
• enable constraint 활성화
  
  

2) 컬럼 추가

• alter table sawon
• add age number(3);

3) 컬럼 삭제

• alter table sawon
  drop column age;

4) 컬럼 변경(사이즈 변경)

• alter table sample
• alter column name char(20)

5) not null설정 방법

• alter table sawon modify varchar2(10) null;
  alter table sawon modify varchar2(10) not null;

SQL> select abs(-7),sin(10),cos(1),tan(10),log(3,2) from dual;
4에 3승
select power(4,3) from dual;
select ceil(10.1),floor(10.7) from dual;
select round(777.7777,2),round(777.7777,-2),round(777.7777,0) from dual;
select trunc(777.7777,2),trunc(777.7777,-2),trunc(777.7777,0) from dual;

-해설: sawon table에서 이름,급여,월급여액(급여/12),세금 출력(단,월급여액 100원 단위출력, 50원 이상 반올림)
세금을 월급여액의 5% 10월 단위로 출력하되,원단위는 절삭

SQL> select saname,sapay,round(sapay/12,-2) "월급여", trunc((sapay/12)*0.05,-1) "세금" from sawon;

대.소문자 상관없이 동일하게 취급 lower
select * from sawon where lower(saname)='sky';

첫글자 대문자
SQL> select initcap('i am a boy')from dual;

두개의 컬럼의 값을 합할경우  두개의 값만 해당
SQL> select concat('korea','programmer')from dual;

좌우 공백
SQL> select '        hitel',ltrim('        hitel')from dual;

원하는 문자열 보여주기
SQL> select substr('computer',3,2) from dual;

SQL> 6번째 위치값  문자열의 시작값
select instr('computer','ter')from dual;
-----------------------------------------------------------------------------------------------------
연속 문자열 보여주기 라인형태로
select saname,substr('saname',1,1), substr('saname',2,2) from sawon;
#####################################################################################################
★  decode(기준,조건1,값1,조건2,값2,...,그외의 값)
select saname,deptno,decode(deptno,10,'총무부',20,'영업부',30,'전산부','관리부') from sawon;
#####################################################################################################
gogek table에 주민번호로 성별을 출력하라
출력:고객명,전화,주민번호,성별
① select goname as "이름",gotel as "전화번호",gojumin as "주민번호",
   decode(substr(gojumin,8,1),1,'남자',2,'여자') as "성별" from gogek;

② select goname as "이름",gotel as "전화번호",gojumin as "주민번호",
   decode(substr(gojumin,8,1),1,'남자',3,'남자','여자') as "성별" from gogek;
-----------------------------------------------------------------------------------------------------
현재 오라클에 설치한 날짜와 시간
select * from nls_session_parameters;
-----------------------------------------------------------------------------------------------------
변환 함수
#####################################################################################################
★ to_char(숫자 || 날짜, 포맷(형식)) : 숫자또는 날짜를 문자타입으로 변환 <문자>

  ▶ to_char(sysdate,'yy-mm-dd')
#####################################################################################################
--to_date(문자,포맷(형식)) : 문자형식을 날짜타입으로 변환   <날짜>

  ▶ to_date('2005-03-30','yyy-mm-dd') 앞에쓴 형식과 뒤의 포멧 형식과 일치해야한다.

--to_number(문자) : <숫자>

  ▶ to_number('123')   숫자 123으로 인식
-----------------------------------------------------------------------------------------------------
select sysdate, to_char(sysdate,'yyyy') from dual;
  

select sysdate, to_char(sysdate,'year') from dual;
                      'year'은 서술형식으로 나온다→ two thousand five

select sysdate, to_char(sysdate,'mon') from dual;
                      'mon'월에 약자

select sysdate, to_char(sysdate,'month') from dual;
                      'month'월에 정상 영문표기

select sysdate, to_char(sysdate,'q') from dual;
                      분기점 1분기,2분기...

select sysdate, to_char(sysdate,'d') from dual;
                      요일의 숫자 월=1 화=2...

select sysdate, to_char(sysdate,'day') from dual;
                      한글 요일   수요일

select sysdate, to_char(sysdate,'dy') from dual;
                      요일 요약  수

select sysdate, to_char(sysdate,'dd') from dual;
                      일

select sysdate, to_char(sysdate,'ddd') from dual;
                      365일중 몇일

select sysdate, to_char(sysdate,'hh') from dual;
                      시간

select sysdate, to_char(sysdate,'hh24') from dual;
                      초

select sysdate, to_char(sysdate,'mi') from dual;
                      분

select sysdate, to_char(sysdate,'miss') from dual;
                      분 시간
-----------------------------------------------------------------------------------------------------
select * from sawon;
insert into sawon values(101,'nsy',20,'사원',5000,to_date('05-03-30 10:10:10','yy-mm-ddhh:mi:ss'),'여자',12);
-----------------------------------------------------------------------------------------------------
입사일에 3개월수를 더한 값
select saname,sahire,add_months(sahire,3) from sawon;
-----------------------------------------------------------------------------------------------------
select floor(months_between(sysdate,sahire)) from sawon;
-----------------------------------------------------------------------------------------------------
현날짜에서 그다음날 월요일의 날짜를 보여준다
select next_day(sahire,'월')from sawon;
-----------------------------------------------------------------------------------------------------
--sawon table에서 이름과 입사일을 출력하되,입사일의 출력 형식을  [xxxx년 xx월 xx일 x요일]
  형태로 출력하라
--sawon table에서 사원들의 근무기간을 출력하라(출력형식 : xx년 xx개월)

select saname,
to_char(sahire, 'yyyy')||'년  '||
to_char(sahire, 'mm')||'월  '||
to_char(sahire, 'dd')||'일  '||
to_char(sahire, 'day')
from sawon;

select saname,sahire, floor(months_between(sysdate,sahire)/12)||'년  '||
                      floor(mod(months_between(sysdate,sahire),12))||'개월 ' as "근무기간"
                      from sawon;

※ col "근무기간" format a12;  컬럼수를 잡아주는것
-----------------------------------------------------------------------------------------------------
select saname, to_char(sapay,'0,000') from sawon;
select saname, to_char(sapay,'fm$9,999') from sawon;  --왼쪽정렬
select saname, to_char(sapay,'9,999') from sawon;  --오른쪽 정렬
'9,999' ?
9→값이 없으면 표시하지 않는다.
0→값이 없어도 표시하겠다.

ex> 50
0,000 → 0,050
9,999 → 50
-----------------------------------------------------------------------------------------------------
  그룹함수  

1. 직책별로 그룹화하여 과장과 대리중에 급여합계,인원수,최대급여,최소급여,최대급여와
   최소급여의 차를 출력하라.
2. 1번 문제에서 인원수가 3명 이상인 직책만 출력하라.
3. 부서코드별로 평균 급여를 구하되, 평균 급여가 2500이상인 부서만 출력하라.

①
select sum(sapay) "합계", round(avg(sapay)) "평균", count(*) "인원수",
       max(sapay) "최대급여", min(sapay) "최소급여" from sawon
group by sajob
having sajob='과장' or sajob='대리';

②
select sajob,count(*) "인원수", sum(sapay) "합계", round(avg(sapay)) "평균",
       max(sapay) "최대급여", min(sapay) "최소급여" from sawon
group by sajob
having count(sajob)>=3;

③
select deptno,sum(sapay) "급여합계", avg(sapay) "평균급여"  from sawon
group by deptno
having avg(sapay)>=2500;
-----------------------------------------------------------------------------------------------------
5명씩 그룹화하여 급여합계와 평균을 출력하라

hint :  ① select ceil(10.1),floor(10.7) from dual;
           rownum이 20개라면 5명씩 묶는다면 4개가 나와야 하며      
  

select rownum/5,sum(sapay) "합계", avg(sapay) "평균",count(*) from sawon
group by rownum/5;
rownum/5--- 0.2, 0.4, 1.0, 2.3 ..... 소수점으로 나오는데 ceil로 구분

select ceil(rownum/5),sum(sapay) "합계", avg(sapay) "평균",count(*) from sawon
group by ceil(rownum/5);
-----------------------------------------------------------------------------------------------------
사원번호,사원명,부서번호,부서명을 출력

select sabun "사원번호",saname " 사원이름",d.deptno "부서번호" ,d.dname" 부서명" from sawon s, dept d
where s.deptno=d.deptno;
-----------------------------------------------------------------------------------------------------
고객명,고객전화번호,담당자이름 출력

select g.goname,g.gotel,g.godam,s.saname from sawon s, gogek g
where g.godam=s.sabun(+); --left out join
where g.godam(+)=s.sabun; --right out join
-----------------------------------------------------------------------------------------------------
①
고객명,고객전화,담당사원명,직책,부서명,부서위치를 출력하라 단,담당자가 없다해도 모든 고객은 출력
②
사원명,직책,관리자명,관리자 직책을 출력하라.(self jion)

①  select g.goname "고객명",g.gotel "고객전화",
           s.saname "담당사원명",s.sajob "직책",
           d.dname "부서명",d.loc "부서위치"
           from sawon s,gogek g,dept d
    where g.godam=s.sabun(+) and d.deptno(+)=s.deptno;
 
②  select s.saname "사원명", s.sajob "직책",
           ss.saname "관사원명", ss.sajob "관직책"
           from sawon s,sawon ss
    where s.samgr=ss.sabun(+);
-----------------------------------------------------------------------------------------------------
① '이순신'과 같은 부서에서 근무하는 사람
② '이순신'과 부서와 직책이 같은 사람 단, 이순신을 제외하고
  select saname from sawon
  where deptno = (select deptno from sawon where saname='이순신');

  select saname,deptno,sajob from sawon
  where (deptno,sajob) = (select deptno,sajob from sawon where saname='이순신')
  and saname<>'이순신';
-----------------------------------------------------------------------------------------------------
사원들 중에서 급여를 가장 많이 받는 사람
회사의 평균 급여보다 많이 받는 사람
10번 부서에서 최대급여와 최소급여를 받는 사람

select saname,sapay from sawon
where sapay = (select max(sapay)from sawon);

select saname,sapay "평균급여" from sawon
where sapay > (select avg(sapay)from sawon);

select saname,sapay from sawon
where sapay in((select max(sapay) from sawon where deptno=10),
              (select min(sapay) from sawon where deptno=10))
and deptno=10;
-----------------------------------------------------------------------------------------------------
union 중복된 결과를 제거후 소트
select saname,sajob,sapay from sawon
where sajob='과장'
union
select saname,sajob,sapay from sawon
where sajob in ('과장','대리');
-----------------------------------------------------------------------------------------------------
intersect 중복된 행만 출력  즉,과장만 출력
select saname,sajob,sapay from sawon
where sajob='과장'
union
intersect
select saname,sajob,sapay from sawon
where sajob in ('과장','대리');
-----------------------------------------------------------------------------------------------------
minus 위에 결과행에서 아래 결과 행을 뺀나머지  즉,대리만 출력
select saname,sajob,sapay from sawon
where sajob in ('과장','대리')
minus
select saname,sajob,sapay from sawon
where sajob='과장';
-----------------------------------------------------------------------------------------------------
조직도 select하기, prior의 위치에 따라 조직도의 순위가 달라진다

이순신을 기준으로 아래 조직
select level,saname,sapay from sawon
connect by prior sabun=samgr
start with saname='이순신';

이순신을 기준으로 위의 조직
select level,saname,sapay from sawon
connect by sabun=prior samgr
start with saname='이순신';

이순신을 제외한 조직보기
select level,saname,sapay from sawon
where saname<>'이순신'
connect by sabun=prior samgr
start with saname='이순신';
-----------------------------------------------------------------------------------------------------
부서 이름별로 합계구하기=부서명 별로 합계구하기
select d.dname,sum(sapay) "합계" from sawon s,dept d
where d.deptno=s.deptno
group by d.dname;
-----------------------------------------------------------------------------------------------------
--테이블 정의된 구조와 데이터(행)를 복사할 수 있다. 단, 제약조건은 복사되지 않는다! 
※ sql 에서는 select ~ into : 테이블 복사

create table sawon_ex as select * from sawon;

create table s1
as
select * from sawon
where deptno=20;

create table s2
as
select sum(sapay) "합계", round(avg(sapay)) "평균", count(*) "인원수",
       max(sapay) "최대급여", min(sapay) "최소급여" from sawon
where sajob='과장' or sajob='대리'
group by sajob;

                    ▼

create table s2(직책,급여합계,인원수,최대급여,최소급여,급여차)
as
select sajob,sum(sapay) "합계", round(avg(sapay)) "평균", count(*) "인원수",
       max(sapay) "최대급여", min(sapay) "최소급여" from sawon
where sajob='과장' or sajob='대리'
group by sajob;

 

create table s3
as
select g.goname,g.gotel,g.godam,s.saname from sawon s, gogek g
where g.godam=s.sabun(+);
-----------------------------------------------------------------------------------------------------
DML(insert/update/delete)
--insert문
1.전체값을 가진 삽입
--insert into table_name values(value,value,.....)
2.선택적 값을 가진 삽입
--insert into table_name(column,column,...) values(value, values,...)
--insert into table_name default values --전체를 기본값으로 삽입
3.대량 값을 가진 삽입
--insert into table_name select~
select * from dept
-----------------------------------------------------------------------------------------------------
--테이블 구조만 복사하는 경우(행은 제외)
즉,대량 insert
create table dept_ex
as
select * from dept where 1=2  (부정만해주면됨)
-----------------------------------------------------------------------------------------------------
--update
update table
set~
where~

update dept_ex
set loc=null
where deptno=40;

update dept_ex
set loc='서울'
where loc is null;
-----------------------------------------------------------------------------------------------------
--Delete:행단위의 삭제(데이터 삭제)
delete table
where ~

delete from sawon_ex where sapay = (select max(sapay)from sawon_ex);
-----------------------------------------------------------------------------------------------------
--전체 행을 한 번에 삭제(DDL)
truncate table dept_ex;
-----------------------------------------------------------------------------------------------------
--20,30번 부서 사원들의 급여를 20% 인상하라
--사번이 17번인 사원과 같은 직책을 가진 사원을 부서코드 17에 사원의 부서코드로 변경하라

select sabun,deptno,saname,sapay,round(sapay/12,-1) "월급여" from sawon_ex
where deptno in(20,30);

update sawon_ex set deptno=(select deptno from sawon_ex where sabun=17)
 where sajob=(select sajob from sawon_ex where sabun=17);

select * from sawon_ex
 where sajob=(select sajob from sawon_ex where sabun=17);
-----------------------------------------------------------------------------------------------------
-- 흐름제어문(TCL)
-- commit, savepoint, rollback

select * from dept;
insert into dept values(99,'생산부','인천');
rollback to savepoint a;
-----------------------------------------------------------------------------------------------------
-- VIEW
create view view_1
 as
select * from sawon where deptno=20;

select * from view_1;

create view v3(부서명,급여합계)
 as
select d.dname, sum(s.sapay)
  from dept d, sawon s
 where d.deptno=s.deptno
 group by d.dname;

select view_name,text from user_views where view_name='V3';

update v1 set sapay=3200 where sabun=2;

create view v1
as
select * from sawon where deptno=20
with read only;

create or replace view v3
as
select * from sawon where deptno=20
with check option constraint v3_dept_20;

ttitle
btitle
column saname new_value aaa;
-----------------------------------------------------------------------------------------------------
                        <출력형식>

          직책:과장

성별   이름 급여 입사일
---- --------- ------- --------
남자   11111
       22222
여자   33333
       44444

          직책:대리

성별   이름 급여 입사일
---- --------- ------- --------
남자   11111
       22222
여자   33333
       44444

--<명령문>
select '직책:'|| sajob "직책",sasex 성별,saname 이름,sapay 급여,sahire 입사일
from sawon
order by sajob,sasex;

--<속성>
col "직책" new_value va
title col18 va
col "직책" noprint
break on "직책" page on "성별"
set pause on;

--<ttitle 컬럼 변수설정>
column 직책 new_value aaa;

--<ttitle 변수 지정>
ttitle col12 aaa;
--ttitle center aaa;

--<숨김>
column 직책 noprint;

--<직책 페이지 break, 성별 break>
break on 직책 page on 성별;
set pause on

--<해제>
ttitle off;
pause off;
clear break;
-----------------------------------------------------------------------------------------------------
★  decode(기준,조건1,값1,조건2,값2,...,그외의 값)
select saname,deptno,decode(deptno,10,'총무부',20,'영업부',30,'전산부','관리부') from sawon;

각 부서별로 합계를 구하라.

select sajob,
sum(decode(deptno,10,sapay,0)) 총무부,
sum(decode(deptno,20,sapay,0)) 영업부,
sum(decode(deptno,30,sapay,0)) 전산부,
sum(decode(deptno,40,sapay,0)) 관리부,
sum(sapay) 합계 from sawon
group by sajob;

SAJOB          총무부     영업부     전산부     관리부       합계
---------- ---------- ---------- ---------- ---------- ----------
과장             4500       7400       4003          0      15903
대리             1800          0       4206          0       6006
부장             3000       3000       2803          0       8803
사원             3100       6800       2706          0      12606
회장             5000          0          0          0       5000

-----------------------------------------------------------------------------------------------------
break on report
compute sum label "부서별합계" of "총무부" "영업부" "전산부" "관리부" "급여합계" on sajob report;
--compute 함수 [label 제목] of 컬럼 on [그룹화]컬럼 report

 

SAJOB          총무부     영업부     전산부     관리부       합계
---------- ---------- ---------- ---------- ---------- ----------
과장             4500       7400       4003          0      15903
대리             1800          0       4206          0       6006
부장             3000       3000       2803          0       8803
사원             3100       6800       2706          0      12606
회장             5000          0          0          0       5000
           ---------- ---------- ---------- ---------- ----------
부서별합계      17400      17200      13718          0      48318

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

   객체(시퀀스)         │      속성(identity)
────────────┼──────────────
여러 table 사용         │    하나의 테이블 컬럼 적용
insert (스퀀스.nextval) │         insert시 제외

--주문 번호 생성  (ms-sql identity와 비슷하다)
  하지만, 하나의 객체로서 새테이블을 생성해야한다.
  다른 곳에서 이어서 insert 할수 있다
  하지만 identity는 하나의 테이블에서만 가능.

--스퀀스 시작
create sequence dno_seq             
increment by 1
start with 11
maxvalue 99;

--dno_seq.nextval  추가
insert into dept values(dno_seq.nextval,'생산부','서울');
insert into dept values(dno_seq.nextval,'생산2부','서울');

--현재 카운드 값 보기
select dno_seq.currval from dual;
-----------------------------------------------------------------------------------------------------
권한(privilege,role)

select dno_
select grantee, privilege from dba_sys_privs
where grantee='CONNECT';

grant select on sawon to scott;
select * from sky.sawon;

①롤 생성
②롤 권한부여
③사용자에게 롤부여

--시스템에서 계정부여
conn /as sysdba
grant create user, create database link to sky;

--role생성
grant create role to sky; 

--role 권한부여
grant select,insert,update on sawon to admin_sawon;  --롤에게 권한을 부여

--- scott에게 권한을 부여
grant select,insert,update on sawon to scott;

--admin_sawon만 삭제하면 scott의 권한이 자동으로 사라진다.
--admin_sawon의 권한을 scott에게
grant admin_sawon to scott;

drop role admin_sawon;
-----------------------------------------------------------------------------------------------------
--현재 계정 확인
show user
--sys계정연결
conn /as sysdba
--
create user media identified by password default tablespace users;
--권한부여
grant connect, resource to media;

conn media/password
--sys에서 sky에게
grant create user to sky with admin option;
--sky에서
create user test1 identified by test1;
--media 에게 권한을 줌
grant create user to media;
--sky에서
grant create user to media;
--media에서
grnat select on sky.dept to scott;
--회수뺏기
revoke select on sky.dept to scott;
-----------------------------------------------------------------------------------------------------
● 동의어
즉, sawon = s 사원이 s 로 정의됨.
create synonym s for sawon;
--sky에서 media에게 sawon을 볼수 있는 권한주기
grant select on sky.sawon to media;
--관리자 계정에서 만들기  public으로 지정 모든 권한이 있는 사람은 ss로 통일.
create public synonym ss for sky.sawon;
select * from ss;
-----------------------------------------------------------------------------------------------------
____________________________________________________________________________________________________
──────────────────────────────────────────────────

▣ PL/SQL

declare
i number :=20;
begin
dbms_output.put_line('i의 값은?' || i);
end;
/

set serveroutput on

화면에 출력(package)dbms_output.put_line
화면에 실제 출력   set serveroutput on

※ 대입 연산자는 『 := 123,'ABC' 』 이다!

 

package?

◆ 패키지(package)는 오라클 데이터베이스에 저장되어 있는 서로 관련있는 PL/SQL
   프로시져와 함수들의 집합이다
◆ 패키지는 선언부와 본문 두 부분으로 나누어 집니다.

패키지 선언부
- 선언절은 패키지에 포함될 PL/SQL 프로시저나, 함수, 커서, 변수, 예외절을 선언 합니다.
- 패키지 선언부에서 선언한 모든 요소들은 패키지 전체에 적용됩니다.
- 즉, 선언부에서 선언한 변수는 PUBLIC 변수로 사용 됩니다

패키지 본문
- 패키지 본문은 패키지에서 선언된 부분의 실행을 정의 합니다.
- 즉, 실제 프로시져나 함수의 내용에 해당하는 부분이 옵니다.
-----------------------------------------------------------------------------------------------------
● IF문
--if 조건 then
--   명령 ;
--   [elsif 조건 then
--          명령;
--    elsif 명령;]
--end if;

declare
i number :=10;
begin
if i >=20 then
dbms_output.put_line('i의 값은?' || i);    // 자동으로 writeline 된다.
end if;
end;
/
set serveroutput on
/
-----------------------------------------------------------------------------------------------------
● 반복문(loop ~ end loop;)
--for 변수 in 초기값... 최대값 loop
--    명령;
--end loop;

declare
 i number :=0;
begin
 for i in 0..50 loop
 dbms_output.put_line('i의 값은?' || i || '입니다');
 end loop;
end;
/
-----------------------------------------------------------------------------------------------------
● while문
--while 조건 loop
--      명령;
--end loop;

declare
 i number :=0;
begin
 while i<=50 loop
 dbms_output.put_line('i의 값은?' || i || '입니다');
 i:=i+1;
 end loop;
end;
/
-----------------------------------------------------------------------------------------------------
● loop문 무한반복
declare
 i number :=0;
begin
   loop
     if i > 50 then
     exit;    
     end if;
     dbms_output.put_line('i의 값은?' || i || '입니다');
     i:=i+1;
   end loop;
end;
/
-----------------------------------------------------------------------------------------------------
● 변수 선언
--v_hiredate date;
--v_deptno   number(2) not null :=0;
--v_loc      varchar2(10) :='KOREA';
--v_comm     constant number :=1200;  //변경되는 기준의 값이 꼭 있어야됨.

변수명과 컬럼명과 같을수가 없다
select ~ into

ex>
select deptno from sawon (x)

select deptno into dno from sawon (o)
￣￣￣        ￣￣

형식 : accept 변수 prompt '입력문자열'
입력받은 값에 &를 붙여줘야한다.
-----------------------------------------------------------------------------------------------------
--사원번호를 입력받아,사원명과 급여를 출력하는 프로그램
--accept 변수 prompt '입력문자열'
--accept sno prompt '사번을 입력하세여-->'

declare
name sawon.saname%type;
pay sawon.sapay%type;

select saname,sapay into name,pay from sawon
                         ￣￣￣￣                                  
begin
where sabun=&sno;
dbms_output.put_line('사원명:' || name || '급여:' || pay);
end;
/

name,pay를 선언해줘야 한다.
하지만, 타입이 다를수 있으므로 오라클에서만 타입을 복사할수있다
ex>
name sawon.saname%type;
pay sawon.sapay%type;
-----------------------------------------------------------------------------------------------------
--숫자를 입력받아, 홀/짝수를 판단하여 여부를 출력하는 프로그램

<scott/emp테이블>
--사원번호를 입력받아 커미션 계산을 하여 커미션을 수정하는 프로그램
--커미션 계산조건
--급여가 1000미만      →  급여의 15％(0.15)
--급여가 1000 ∼ 1500  →  급여의 20％(0.02)
--급여가 1500초과      →  급여의 25％(0.25)
-----------------------------------------------------------------------------------------------------
accept snumner prompt '숫자를 입력하세요 : '
declare
  sno number := &snumner;
begin
  if (MOD(sno,2) = 0) then
     dbms_output.put_line(sno || '는 짝수 입니다.');
  else
     dbms_output.put_line(sno || '는 홀수 입니다.');
  end if;
end;
/
-----------------------------------------------------------------------------------------------------
accept snumner prompt '사번으로 입력하시오 : '

declare
  name emp.ename%type;
  pay emp.sal%type;
  com emp.comm%type;
begin
  select ename, sal, comm into name, pay, com from emp where empno = &snumner;
  if pay < 1000 then
     com := pay * 0.15;
  elsif pay <= 1500 then
     com := pay * 0.2;
  else
     com := pay * 0.25;
  end if;
  update emp
  set comm = com
  where empno = &snumner;
  dbms_output.put_line('사원명:' || name || '급여:' || pay ||'커미션:' || com);
end;
/
-----------------------------------------------------------------------------------------------------
● Cursor : 일괄반복작업

행단위, 로우작업
                        ┌───────────┐
                        │  (다시 되돌아감)     │
                        ↓                      │
기본테이블 - open - fetch(행 가져오기) - 처리 - empty - close

--커서
 declare
    cursor 커서명 is select~;                  ---커서정의
    변수;

 begin
    (열려있다면 커서 닫기;)
    open 커서명;                               ---커서열기
    loop
      fetch 커서명 into 변수,변수,...;         ---행 가져오기
      (가져올 행이 없다면 종료하기;)       
       명령;                                   ---처리
       명령;
 
   end loop;
  [close 커서명;]                              ---커서 닫기

end;
/
--커서 내에서 사용되는 속성
%isopen : 커서가 열려있다면
%notfound : 가져올 행이 없다면
%found : 가져올 행이 있다면
%rowcount : 읽어들인 행의 수
-----------------------------------------------------------------------------------------------------
--사원의 이름과 급여를 출력
declare
   cursor cur_1 is select saname, sapay from sawon;
   v_name sawon.saname%type;
   v_pay  sawon.sapay%type;
begin
   if cur_1%isopen then
      close cur_1;
   end if;
   open cur_1;
   loop
      fetch cur_1 into v_name, v_pay;
--      if cur_1%notfound then
--      exit;
--      end if;

 < 3개의 문장을 하나로 >
--   exit(whem cur_1%notfound);
dbms_output.put_line('이름은 '||v_name||'이고, 급여는 '||v_pay);

  end loop;
--close cur_1;
end;
/
-----------------------------------------------------------------------------------------------------
--sawon table에서 이름, 입사일, 급여를 읽어들여,
--급여순위, 근무기간을 구하여, 테이블에 넣어라
--create table sawon_r
--(이름 varchar2(10), 입사일 date, 급여 number(10),
-- 급여석차 number(3), 근무기간 varchar2(30));
--단, 근무기간은 [xx년 xx개월]로 출력할 것!

create table sawon_r
(이름 varchar2(10), 입사일 date, 급여 number(10),급여석차 number(3), 근무기간 varchar2(30));

_____________________________________________________________________________________________________
declare
   cursor cur_2 is select saname, sahire, sapay from sawon;
   v_name  sawon.saname%type;
   v_pay   sawon.sapay%type;
   v_hire  sawon.sahire%type;
   gigan   varchar2(30);
   r       number(3);
begin
   if cur_2%isopen then
      close cur_2;
   end if;
   open cur_2;
loop
       fetch cur_2 into v_name, v_hire, v_pay;
       exit  when(cur_2%notfound);
--급여순위구하기      
       r:=0;
       select count(*) into r from sawon where sapay>v_pay;
       r:=r+1;
--근무기간 구하기
       gigan :=floor(months_between(sysdate,v_hire)/12)||'년 '||
               floor(mod(months_between(sysdate,v_hire),12))||'개월';
--테이블에 넣기
       insert into sawon_r values(v_name, v_hire, v_pay, r, gigan);
end loop;
end;
/
_____________________________________________________________________________________________

● for 문
for 변수 in 커서명 loop
    ┬─    ┬──      
    │      └→ (변수,컬럼명)
    └→{카운트 변수(자료형은×)}

end loop;

 

● for 문  <결과가 똑같고 변수명등.. 코드가 줄어든다.>

declare
   cursor cur_2 is select saname, sahire, sapay from sawon;
   gigan     varchar2(30);
   r            number(3);
begin
     for s in cur_2 loop
--급여순위구하기      
       r:=0;
       select count(*) into r from sawon where sapay>s.sapay;
       r:=r+1;
--근무기간 구하기
       gigan :=floor(months_between(sysdate,s.sahire)/12)||'년 '||
               floor(mod(months_between(sysdate,s.sahire),12))||'개월';
--테이블에 넣기
       insert into sawon_r values(s.saname, s.sahire, s.sapay, r, gigan);
  end loop;
end;
/
_____________________________________________________________________________________________

SQL> select * from sawon_r;

이름       입사일         급여   급여석차 근무기간
---------- -------- ---------- ---------- ------------------
홍길동     80/01/01       5000          1 25년 3개월
한국남     88/11/01       3000          6 16년 5개월
이순신     85/03/01       3500          4 20년 1개월
놀기만     96/06/01       2300         10 8년 10개월
류별나     89/12/01       1600         15 15년 4개월
채시라     93/10/01       3400          5 11년 6개월
이성계     84/05/01       2803          8 20년 11개월
무궁화     96/11/01       3000          6 8년 5개월
이순라     90/05/01       1200         17 14년 11개월
임꺽정     88/04/01       2200         11 17년 0개월
이미라     83/04/01       2503          9 22년 0개월

이름       입사일         급여   급여석차 근무기간
---------- -------- ---------- ---------- ------------------
깨똥이     90/05/01       4500          2 14년 11개월
공부만     95/05/01       4003          3 9년 11개월
채송화     92/06/01       1703         14 12년 10개월
공부해     88/11/01       1303         16 16년 5개월
류명한     90/10/01       1800         13 14년 6개월
김유신     81/04/01        400         20 24년 0개월
강감찬     86/07/01       1003         19 18년 9개월
최신실     91/04/01       2000         12 14년 0개월
무궁화     84/08/01       1100         18 20년 8개월
-----------------------------------------------------------------------------------------------------
declare
  cursor dept_cur is
       select deptno, count(*) CNT, sum(sapay) TOT, avg(sapay) AVR from sawon
       group by deptno;
  begin
     for st in dept_cur loop
        dbms_output.put_line('부서번호:'||st.deptno);
        dbms_output.put_line('인원수:'||st.cnt);
        dbms_output.put_line('급여합계:'||st.tot);
        dbms_output.put_line('급여평균:'||st.avr);
     end loop;
end;
/
-----------------------------------------------------------------------------------------------------
create table sung
(name varchar2(10), kor number(3), eng number(3), mat number(3));

insert into sung values('홍길동',90, 85, 70);
insert into sung values('이지영',70, 80, 100);
insert into sung values('이은혜',65, 80, 75);
insert into sung values('유정현',100, 85, 90);
insert into sung values('김승민',95, 70, 60);

create table resultsung
(name varchar2(10), kor number(3), eng number(3), mat number(3),
 total number(4), average number(6,2), hakjum varchar2(10),
 pyungga varchar2(10), sukcha number(2));

//--- 커서(1)
declare
   cursor c_sung is select name, kor, eng, mat from sung;
   tot number(5);
   ave number(10,2);
   gra varchar2(10);
   pyu varchar2(10);  -///
begin
   for s in c_sung loop
      tot := s.kor + s.eng + s.mat;
      ave := tot / 3;
      if ave >= 90 then
         gra := '수';
      elsif ave >= 80 then
         gra := '우';
      elsif ave >= 70 then
         gra := '미';
      elsif ave >= 60 then
         gra := '양';
      else
         gra := '가';
      end if;
      if ave >= 70 then
         pyu := '합격';
      else
         pyu := '불합격';
      end if;
      insert into resultsung values(s.name,s.kor,s.eng,s.mat,tot,ave,gra,pyu,null);
  end loop;
//--- 다중커서(2)
declare  
     cursor d_sung is select name, total from resultsung;
     z number(5);
begin
     for sd in d_sung loop
        z := 0;
        select count(*) into z from resultsung where total > sd.total;
        z := z + 1;
        update resultsung set sukcha = z where name = sd.name;
     end loop;
  end;
end;
/
-----------------------------------------------------------------------------------------------------
● 프로시져(Procedure)

＊ 자주실행되는 SQL&PL/SQL을 미리 컴파일 해서 DB에 저장하는 것,응용 프로그램에서 많이 사용한다.
                                                               ￣￣￣￣￣￣￣￣￣￣￣ ￣￣
＊ 테이블이 아닌 프로시져 단위의 사용권한을 부여할 수 있으므로 보안 기능을 강화할 수 있다.

--유일한 권한 : exec
--미리 컴파일된 SQL문
--서버 캐시에 저장되어 있다(서버에 생성)
--성능이 빨라진다(한 번의 컴파일이면 실행(호출)만 해서 사용한다)
--삽입, 수정, 조회가 가능하다
--개발자가 많이 사용한다

프로시져 생성 → 컴파일 → 호출(실행 : sql*plus or APP)

호출 : 별도의 파싱 작업이 없다!         ⇒ 속도가 빠르다
       여러 사용자가 동일한 메모리 공유 ⇒ 메모리 절약

생성 파라미터 mode -(in)/out.input
                   -길이 지정 안함!

   <형식>
create or replace procedure pro_name
(변수 (in) type, .....)
is
[local 변수]
begin
명령;
end;

create or replace procedure pro_1
(one number,two char)
is
begin
insert into test values(one,two);
end;
/

create table test (no number(3),name char(10));

exec pro_1(101,'nsy')

//프로시져,함수,트리거가 모두 포함된다.
desc user_object
//
select * from user_object
where object_type='PROCEDURE';
//에러보는거
desc user_errors
//간단하게 에러보는거
show errors procedure pro_1
//소스
desc user_source
//소스 보는거
select name,text from user_source
//소스 수정
col text format a30
col name format a10
-----------------------------------------------------------------------------------------------------
--호출되는 사번을 가진 사원을 삭제하는 프로시져
create or replace procedure pro_2
(no number)
is
begin
delete sawon_ex where
sabun=no;
end;
/

exec pro_2(13)
-----------------------------------------------------------------------------------------------------
--해당 사번을 가진 사용자의 급여 수정하는 프로시져
create or replace procedure pro_3
(bun number,pay number)
is
begin
update sawon_ex
set sapay=pay
where sabun=bun;
end;
/

//update 는 직접호출은 안되고 간접호출은 된다
accept bun prompt '사번을 입력 →'
accept pay prompt '변경금액을 입력 →'
begin
pro_3(&no,&sal);
end;
/
-----------------------------------------------------------------------------------------------------
--직접호출문 : exec pro_3(17,1000) -17번 사원을 급여를 1000으로 변경
--emp table
--emp table 에 신입 사원에 데이터를 삽입하는 프로시져
--job이 'salesman'이면 comm→0,그외의 job이면 comm→null
--또한 empno는 시퀀스 사용
     create sequence emp_id
     increment by 1
     start with 10
     maxvalue 99;

--호출
--호출문 : exec emp_in('이시영','clerk',7902,2000,30)
                        ￣￣￣   ￣￣   ￣￣ ￣￣ ￣
--                       이름  ,  job,매니저번호,급여,부서번호

create or replace procedure emp_in
   (e_name varchar2,             //인수
    e_job varchar2,
    e_mgr number,
    e_sal number,
    e_deptno number)
as
    e_comm emp.comm%type;        //변수
begin
    if lower(e_job) = 'salesman' then
      e_comm := 0;
    else
      e_comm := null;
  end if;
          
  insert into emp(empno,ename,job,mgr,sal,deptno,comm,hiredate)
  values(emp_id.nextval,e_name,e_job,e_mgr,e_sal,e_deptno,e_comm,sysdate);

end;
/

exec emp_in('이시영','clerk',7902,2000,30)
-----------------------------------------------------------------------------------------------------
--사원번호로 사원의 이름 출력하기  --outmod--
create or replace procedure select_name
(v_bun in number, v_name out varchar2)
is
begin
select saname into v_name from sawon
where sabun=v_bun;
end;
/

--호출
variable names varchar2(10)  //variable
exec select_name(13,:name)   //out모드는 : 를 붙여서 표현
print name
-----------------------------------------------------------------------------------------------------
--숫자 2개 곱한 결과
--두 수를 곱하여 내보내는 프로시져
create or replace procedure su_mul
  (no1 number,
   no2 number,
   s    out number)
is
begin
  s:= no1*no2;
end;
/
──────────────────

--호출
--variable tot number
--exec su_mul(15,13,:tot)
--print tot

-----------------------------------------------------------------------------------------------------
● 함수(function) : 리턴값이 반드시 필요한 경우에 적용된다.

--독립적으로 쓰인다
--문장의 일부처럼 사용되기 때문에 함수 사용이 가능한 모든 곳에서 호출가능
--반드시 리턴값이 있으므로 리턴되는 데이타 타입 정의가 필요
  ￣￣￣￣￣￣￣
--목적 자체가 복잡하거나 어려운 계산식에 사용 가능
--함수 내에서 DML명령 사용 불가(insert제외)
--파라미터 모드는 in만 사용 가능하다
                  ￣￣￣￣￣￣￣
--데이터를 조작하기 위한 목적이다(데이터 변경 또는 생성이 목적이 아니다)
--최소 하나의 리턴문(여러개를 사용해도 실제 하나의 값만 리턴)

--이름만 출력하는 함수
create or replace function name_f
  (v_name varchar2)
  return varchar2
is
  name varchar2(5);
begin
  name := substr(v_name,2,2);
  return name;
end;
/

select saname,name_f(saname) "이름" from sawon
/

col 이름 format 25
/

--주민번호로 성별을 출력하는 함수
create or replace function sex_f
  (jumin varchar2)
  return varchar2
is
  sex varchar2(4);
begin
  if substr(jumin,8,1)=1 or substr(jumin,8,1)=3 then
    sex := '남자';
  else
    sex := '여자';
  end if;

  return sex;
end;
/

--select goname, gotel, sex_f(gojumin) from gogek;

--sawon table에 입력할때 dept table에 해당 부서번호 있는지 체크하는 함수
※ boolean (true 와 false을 비교판단)

create or replace function isdept
  (v_dno dept.deptno%type)
  return boolean
is
  v_count number;
  v_return boolean;
begin
  select count(*) into v_count from dept
  where deptno=v_dno;
 if v_count=0 then
    v_retun := false;
 else
    v_return := true;
 end if;
 return v_return;
end;
/

#####################################################################################################
--if isdept(v_dno) then    -응용편-
--insert
#####################################################################################################
create or replace procedure emp_in
   (e_name varchar2,             //인수
    e_job varchar2,
    e_mgr number,
    e_sal number,
    e_deptno number)
as
    e_comm emp.comm%type;        //변수
begin
    if lower(e_job) = 'salesman' then
      e_comm := 0;
    else
      e_comm := null;
  end if;
#####################################################################################################
 if isdept(v_dno) then     -응용편-
#####################################################################################################
  insert into emp(empno,ename,job,mgr,sal,deptno,comm,hiredate)
  values(emp_id.nextval,e_name,e_job,e_mgr,e_sal,e_deptno,e_comm,sysdate);
  commit;
 endif;

end;
/
-----------------------------------------------------------------------------------------------------
● Trigger(트리거)
--데이터 무결성 강화(constraint와 부딪치면 constraint가 먼저 발생)
--DML문을 사용해 데이터를 수정할 때 실행된다(반사작용, 연쇄작용..예-국민연금, 세금..)
--복잡한 업무를 단순화
--일종의 trans-sql문이다
--호출문이 없다(생성과 동시에 동작)
--자료의 무결선을 유지   //오라클부분
--변경된자료,변경한 유저를 기록하여 테이블의 변경정보 감시
--자동호출(데이터 제어흐름문 사용 안됨)
--데이터 제어 흐름문이 안된다(commit,rollback)
--지정한 이벤트가 발생하면 자동으로 실행되는 PL/SQL블럭
--데이터베이스에 저장

create table t1(name char(10),age number(3));
create table t1_r(name char(10),age number(3));
create table t2(memo varchar2(30));

create or replace trigger tr1
 after insert on t1
begin
 insert into t2 values('행삽입');
end;
/

insert into t1 values('nsy',20);
insert into t1 values('opp',20);
insert into t1 values('bsy',20);

 

insert into t1 select * from t1;  //위의 행이 세번발생 했지만... 이벤트는 한번이기 때문에
                                  //한번만 실행된다.
select * from t2;

          
          ▼

※  for each row //하나하나의 행을 구분!!
:old.컬럼명 : 지금막 삭제된 데이터의 컬럽값
:new.컬럼명 : 지금막 삽입된 데이터의 컬럽값
:new, :old 는 for each row와 함께 사용한다.

create or replace trigger tr1
 after insert on t1
 for each row
begin
 insert into t2 values('행삽입');
end;
/

insert into t1 select * from t1;   //이벤트와 상관없이 실제 실행된 행이 실행된다

select * from t2;

truncate table t1;
insert into t1 values('홍길동',30);
insert into t1 values('박길동',34);

create table t1_r(name char(10),age number(3));
create or replace trigger tr1
 after insert on t1
 for each row
begin
 insert into t1_r values(:new.name, :new.age);   //new.  지금막 삽입된 데이터의 컬럽값
end;
/

select * from t1_r;
select * from t1;

             ▼

create or replace trigger tr2
 after delete on t1
 for each row
begin
 insert into t1_r values(:old.name, :old.age);   //old.  지금막 삭제된 데이터의 컬럽값
end;
/

create or replace trigger tr2
 after delete on t1
 for each row
begin
 delete t1
 where name =: old.name;   //old.  지금막 지워진값을 의미
end;
/

delete t1
where name='홍길동';   //t1 과 t1_r과 같이 삭제된다

-----------------------------------------------------------------------------------------------------
///외래키의 cascade///

--dept테이블의 deptno가 변경되면,
---부서번호도 함수와 함께 변경되는 트리거를 생성하라.
create or replace trigger dept_cascade
  after update on dept
  for each row
begin
  update sawon
  set deptno=:new.deptno
  where deptno=:old.deptno;
end;
/        --부서번호도 함수와 함께 변경

create or replace trigger dept_cascade
  after update of deptno on dept
  for each row
begin
  update sawon
  set deptno=:new.deptno
  where deptno=:old.deptno;
end;
/        --부서번호만 참조하는것은 제외

select * from sawon;

update dept
set deptno=20
where deptno=22;
-----------------------------------------------------------------------------------------------------
● 사용자 정의 에러(raiserror)

: 변경할 데이터를 변경못하게 설정해주면서 에러메세지를 띄어준다.

//오라클 : ①사용자에러띄어주기, ②black_list  둘중에 하나만 된다.
//MS-SQL : ①사용자에러띄어주기, ②black_list  둘다 된다.

--raise_application_erro(-20001,'메세지');
--rollback 작업 포함
--급여의 변경되면 변경할 수 없도록 메세지 출력(급여는 변경할 수 없다)
--시도하는 계정과 날짜/시간을 기록(black_list table)

create or replace trigger pay_trigger
  after update of sapay on sawon
  for each row
begin
  raise_application_error(-20001,'급여는 변경할 수 없습니다!');
end;
/

**변경
update sawon
set sapay=2000
where sabun=16;   //에러메시지 나옴.

--create table black_list(name varchar2(10), time varchar2(30));

create or replace trigger pay_trigger
  after update of sapay on sawon
  for each row
begin
--  raise_application_error(-20001,'급여는 변경할 수 없습니다!');
  insert into black_list values(user, to_char(sysdate, 'yyyy-mm-dd hh:mi:ss am'));
end;
/

 

update sawon
set sapay=2000
where sabun=12;

select * from black_list;

-----------------------------------------------------------------------------------------------------
--sawon table의 급여를 변경하는 경우, 기존 급여보다 적은 금액으로 변경되거나,
--기존 급여보다 10%초과하여 변경되는 경우, 에러를 발생시키는 트리거!

--drop trigger pay_trigger;

--create or replace trigger ck_pay
    after update of sapay on sawon
    for each row
  begin
    if (:new.sapay < :old.sapay) or (:new.sapay > :old.sapay*1.1) then
       raise_application_error(-20001,'범위 안의 값이 아닙니다!!');
    end if;
  end;
  /

--update sawon
  set sapay=2000
  where sabun=12;
-----------------------------------------------------------------------------------------------------
--sawon table의 급여를 변경하는 경우, 기존 급여보다 적은 금액으로 변경되거나,
--기존 급여보다 10%초과하여 변경되는 경우, 에러를 발생시키는 트리거!
--단,직책이 회장인 사람의 급여는 제외

--drop trigger pay_trigger;

--create or replace trigger ck_pay
    after update of sapay on sawon
    for each row
    when (new.sajob<>'회장') //when절은 new앞에『:』를 안붙인다
  begin
    if (:new.sapay < :old.sapay) or (:new.sapay > :old.sapay*1.1) then
       raise_application_error(-20001,'범위 안의 값이 아닙니다!!');
    end if;
  end;
  /

--update sawon
  set sapay=7000
  where sabun=1;
-----------------------------------------------------------------------------------------------------
//트리거 정보보기
desc user_triggers

alter trigger ck_pay
disable;

alter trigger ck_pay
enable;

alter table sawon disable all triggers;  //테이블의 모든 트리거는 disable이 된다.
alter table sawon enable all triggers;   //테이블의 모든 트리거는 disable이 된다.

saname,sahire,근무기간
col saname format a10
col sahire format a10
col '근무기간' format a0
-----------------------------------------------------------------------------------------------------
● RPAD,LPAD
＊RPAD - 오른쪽 공백부터 "*"를 채워나간다.
＊LPAD - 왼쪽부터 공백으로...
-----------------------------------------------------------------------------------------------------
--backup으로 가장많이 사용하는 툴
● export
● import
conn /as sysdba  //계정
alter user hr identified by password;
alter user hr account unlock;
conn hr/password
host- 잠깐 도스 명령어 쓸때
exit- 세션은 유지

c:\oracle\ora92\bin\EXP.EXE
//sky_1.DUMP 만들기

export
sky/password
sky_1  // 엑소프트 파일
....엑스포트 처리
....엑스포트 처리
....엑스포트 처리

export
sky/password
sky_2 //엑소프트 파일
t //테이블

전체 import 할때
IMP.EXE
hr/password
sky_2

일부분 import 할때
IMP.EXE
media/password
sawon  //일부분일 경우 소유자를 물어본다
dept
.  //끝날경우
-----------------------------------------------------------------------------------------------------
● conn /as sysdba
select username, sid, serial#, status from v$session;
  //세션 죽일때
  //media 번호입력 MEDIA INACTIVE→ MEDIA KILL
    alter system kill session '10,101';

● sky/password
//현재 자기자신의 롤 정보
select * from session_roles;

//객체권한 정보를 뷰
desc user_tab_privs
select grantee,table_name,privilege from user_tab_privs;

//시스템 권한 정보를 뷰
desc user_sys_privs
select USERNAME,PRIVILEGE,ADMIN_OPTION from user_sys_privs;

//privs 검색해서 뷰정보를 볼때
desc dictionary
select table_name from dictionary
where table_name like '%PRIVS%';        '%PRIVS%' → 여기만 수정해주면 검색가능!
-----------------------------------------------------------------------------------------------------
   <<Startup and Shutdown>>

shutdown immediate
 데이터베이스가 닫혔습니다.
 데이터베이스가 마운트 해제되었습니다
 ORACLE 인스턴스가 종료되었습니다.
        ↓
conn /as sysdba
휴지 인스턴스에 접속되었습니다.  <서비스가 접속이 안된상태를 말한다>
        ↓
sqlplus /nolog <로그온이 안될경우 다시..로그온>
        ↓
show user
        ↓
conn /as sysdba
 휴지 인스턴스에 접속되었습니다.
        ↓
//nomount 상태 
startup nomount
 ORACLE 인스턴스가 시작되었습니다.
  Total System Global Area  135338868 bytes
  Fixed Size                   453492 bytes
  Variable Size             109051904 bytes
  Database Buffers           25165824 bytes
  Redo Buffers                 667648 bytes
        ↓
//nomount open된 상태가 아니다
alter database mount;
데이타베이스가 변경되었습니다.
        ↓
//nomount open
alter database open;
데이타베이스가 변경되었습니다.
        ↓
//내리기
shutdown immediate
        ↓
//한번에 open
startup
ORACLE 인스턴스가 시작되었습니다.
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

    <<파라미터>>

//sp파일을 볼때
desc v$spparameter;
//
select name,open_mode from v$database;
//
select instance_name form v$instance;
        ↓
//데이파일 경로 보기
select status,name,enabled,bytes from v$datafile;
        ↓
//Temp 보기
select status,name,enabled,bytes from v$tempfile;
        ↓
//물리적인 파일보기
select * from v$logfile;
select * from v$log
        ↓
//물리적인 파일보기 control 파일보기
select * from v$controlfile;
        ↓
//패스워드 파일에 있는사람이 들어올수 있는 경우의 사람을 보는 경우
//패스워드,파라미터 상황을 볼수있다.
select * from v$pwfile_users;
        ↓
//instance 정보보기
select * from v$sgastat;
        ↓
//백그라운드 프로세스 정보보기
select * from v$bgprocess where paddr > '00';
select * from v$bgprocess where name like'%DB%';
        ↓
//프로세스 보기
select * from v$process;
        ↓
//spfilesid.ora (오라클상에서 수정,확인) → initsid.ora (메모장에서 수정,확인) →init.ora
//초기파라미터 만드는 방법!!
create pfile from spfile;

//
C:\>orapwd

//
shutdown immediate;
        ↓
//일반유저는 conn못한다.  제한적
//즉, 제한적 startup
startup open restrict;
        ↓
//startup 세션변경
alter system disable restricted session;
        ↓

//계정죽이기........
SQL> conn sky/password
연결되었습니다.
SQL> conn /as sysdba
연결되었습니다.
SQL> select sid, serial#, username, status from v$session;
     → sky, scott 연결이 되어 있어야지 username이 보인다.

       SID    SERIAL# USERNAME                       STATUS
---------- ---------- ------------------------------ --------
         1          1                                ACTIVE
         2          1                                ACTIVE
         3          1                                ACTIVE
         4          1                                ACTIVE
         5          1                                ACTIVE
         6          1                                ACTIVE
         7          1                                ACTIVE
         8          1                                ACTIVE
         9          7 SYS                            ACTIVE
        11         20 SKY                            INACTIVE
        12          6 SCOTT                          INACTIVE

11 개의 행이 선택되었습니다.

SQL> alter system kill session '11,20'; 
     → sid,serial# 의 인자값으로 변경
     → sky계정을 죽임 INACTIVE → KILLED 변경!!

 시스템이 변경되었습니다.

SQL> select sid, serial#, username, status from v$session;

       SID    SERIAL# USERNAME                       STATUS
---------- ---------- ------------------------------ --------
         1          1                                ACTIVE
         2          1                                ACTIVE
         3          1                                ACTIVE
         4          1                                ACTIVE
         5          1                                ACTIVE
         6          1                                ACTIVE
         7          1                                ACTIVE
         8          1                                ACTIVE
         9          7 SYS                            ACTIVE
        11         20 SKY                            KILLED
        12          6 SCOTT                          INACTIVE

//
select * from dict where upper(commits) like upper('%USER%');

//데이타사전의 컬럼정보 보기
select * from dict_colums;
select * from dict_colums where table_name='DBA_SYS_PRIVS';

//동적뷰
select * from v$fixed_table;

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