22.12.01 PL-SQL 사용 문법, JAVA-DB연동/더미데이터생성, 시퀀스 활용

board1201.sql

0.00MB

day3.sql

0.00MB

query.sql

0.00MB

query.txt

0.00MB

제목 없음.sql

0.04MB

select (sysdate)
from dual;

--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--오늘날짜 조회
select sysdate, systimestamp
from dual;

select add_months(sysdate, -6), add_months(sysdate, 18)
from dual;

select months_between(sysdate, add_months(sysdate, -6))
from dual;

select last_day(sysdate)
from dual;

--입사한 달의 근무일수(토, 일, 공휴일에도 근무가정)
--날짜 -날짜의 데이터 타입은 숫자
select ename, hiredate, last_day(hiredate), last_day(hiredate)-hiredate
from emp
where deptno=10
order by hiredate asc ;

--비교 : 날짜데이터대상의 trunc(날짜데이터, FMT), round(날짜데이터, FMT)
--trunc함수는 FMT에 지정된 단위로 절삭
--round함수는 FMT가 년도라면 년도단위로 반온림
--FMT가 생략되면 날짜를 가장 가까운 날짜로 반올림
--FMT: YEAR,MONTH,DD 등=>P141 날짜변환형식 참고)
select trunc(123.456), trunc(456.78) from dual;
select round(123.456, -2), round(123.456, -1),
round(123.456, 0), round(123.456, 1)
from dual;

--trunc 와 lastday는 반대 개념
select sysdate, trunc(sysdate, 'month'), trunc(sysdate, 'day'),
trunc(systimestamp, 'month'), trunc(systimestamp, 'day'),
to_char(trunc(systimestamp, 'month'), 'YYYY.MM.DD / HH24:MI:SS'), to_char(trunc(systimestamp, 'day'), 'YYYY.MM.DD / HH24:MI:SS')
from dual;

select sysdate, round(sysdate, 'month'), round(sysdate, 'day'),
round(systimestamp, 'month'), round(systimestamp, 'day'),
to_char(round(systimestamp, 'month'), 'YYYY.MM.DD / HH24:MI:SS'), to_char(round(systimestamp, 'day'), 'YYYY.MM.DD / HH24:MI:SS')
from dual;

---

-- * TABLE삭제 => auto commit되므로 주의.
문법> Drop table 테이블명;
--scott의 bonus, emp, dept, salgrade 제외하고 전부 삭제
Drop table ctest;
Drop table dtest;
Drop table emp1;
Drop table emp10;
Drop table emp2;
Drop table emp20;
Drop table empetc;
Drop table istest;
Drop table ntest;
Drop table test;

p176
--join

select 컬럼명 [AS] 별칭, 컬럼명 AS 별칭
from 테이블명 별칭, 테이블명 별칭
[where 조건];

--사원번호, 사원명, 업무, 부서번호 조회 (부서 번호로 오름차순 정렬)
select empno, ename, job, deptno
from emp
order by deptno asc;

--부서번호, 부서명 조회 (부서 번호로 오름차순 정렬)
select deptno, dname
from dept
order by deptno asc;

--deptno 양쪽 테이블 모두에 있어서 애매한 상황
-- 1 select empno, ename, job, deptno, dname
-- 2* from emp, dept
--SQL> /
--select empno, ename, job, deptno, dname;
-- *
/*
ERROR at line 1:
ORA-00918: column ambiguously defined
*/

--deptno를 지워버리면 결과는 나온다.그런데 ... 정확하지 않다...크로스조인 되어 데이터가 이상하다.
--CROSS JOIN, CATASIAN PRODUCT(p184)
--join조건을 명시하지 않는 조인
select empno, ename, job, dname
from emp, dept; --사원 .12개행*부서.5개의행 => 60개의 행 이것이 크로스 조인

--equi join(동등조인 p176)
--where절에 =(동등기호)를 이용하여 join 조건을 명시
--where 사원 테이블의 부서번호 부서테이블의 부서번호 컬럼의 값이 같다.
--이 구절을 써줘야 제대로 출력된다.
select empno, ename, job, dname
from emp, dept
where emp.deptno = dept.deptno; --12개의 행이 리턴.

--equi조인은 조인조건에 사용되는 값들이 서로 일치=하는 데이터만 select
--즉 여기에서는 사원테이블에 부서번호가 null인 사원은 select 되지 않는다.
--즉 소속부서가 확정되지 않은 사원은 제회 => 소속부서가 명확한 사원만 select
select empno, ename, job, dname
from emp, dept
where emp.deptno = dept.deptno
and
emp.deptno is not null;

---

--사원번호가 9000이상인사원 삭제후 commit;
delete from emp
where empno like '9%';
--where empno>=9000;

commit;

--부서테이블에서 부서번호가 91이상인 부서삭제후 commit;
delete from dept
where deptno>=91;
commit;

---

/*
select 테이블명.empno, 테이블명.ename, 테이블명.job,
테이블명.부서번호, 테이블명.dname
from emp, dept
where emp.deptno = dept.deptno;
*/

--에러 : column ambiguously defined 발생
--해결방법 : 조회하는 컬럼의 소속테이블명을 명시하면 된다.
select emp.empno, emp.ename, emp.job,
dept.deptno, dept.dname
from emp, dept;
where emp.deptno = dept.deptno;

--테이블에 별칭을 부여하여 쿼리는 간단, 소속분명, 이해도 향상
select e.empno, e.ename, e.job,
d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno;

*p198 인라인뷰(inline view)
-->오랫동안 또는 영구적으로 사용할거면 뷰를 생성하면 되고 일시적으로 처리 할 거면 인라인 뷰형태인 서브쿼리로 쓰면 된다.

--from절에 서브쿼리(subquery) 가 들어가면 inline view
--select e.empno, e.ename, e.job, 테이블명.컬럼명, 별칭.컬럼명
-- d.deptno, d.dname
--from emp e, dept d (select 컬럼명, 컬럼명 from 테이블명) 별칭
--where e.deptno = d.deptno;

-- 10번 또는 20번 부서에 근무하는 사원번호, 사우너명, 급여, 부서번호, 부서명을
-- 부서명 오름차순 정렬하여 조회
select e.empno, e.ename, e.sal, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno and d.deptno in (10, 20) --조인문 조건 써줘야 한다 ! 무조건!! 카타시안 프로덕트 및 크로스조인 방지!
order by d.dname asc;
--where (e.deptno=10 or e.deptno=20) and e.deptno = d.deptno --> 이렇게 써도 무관. 문법은 무궁무진

/*
급여가 1600이상인 사원번호, 사원명, 급여,부서번호, 부서명을
급여를 많이 받는 사원부터 출력 -->내림차순
이때, 급여가 확정된 사원에 한함
*/

select e.empno, e.ename, e.sal, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno and e.sal>=1600 and e.sal is not null
order by e.sal desc;

--사번, 사원명, 부서번호, 부서명을 부서번호, 오름차순

select e.empno, e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
order by d.deptno asc;

--natural join
--문법> from 테이블명1 natural join 테이블명2
--주의> natural join에 사용된 컬럼의 소속테이블은 명시하면 안된다.
--해결방법> 컬럼명만 명시하자
--예시> d.deptno가 아닌 deptno;
--예시> order by d.deptno가 아닌 order by deptno;
--공통된 컬럼에 대해서 소속을 넣으면 안된다.
select e.empno, e.ename, deptno, d.dname
from emp e natural join dept d
order by deptno;

--join using()
--문법> from 테이블명1 join 테이블명2 using(join사용컬럼)
--column part of USING clause cannot have qualifier
--주의> join에 사용된 컬럼의 소속테이블은 명시하면 안된다.
--해결방법> 컬럼명만 명시하자
--예시> d.deptno가 아닌 deptno;
--예시> order by d.deptno가 아닌 order by deptno;
--이 것 또한 사용된 컬럼의 소속테이블은 명시하면 안된다.
select e.empno, e.ename, deptno, d.dname
from emp e join dept d using(deptno)
order by deptno;

--join on
--column ambiguously defined
-->얘는 사용 컬럼 명시를 따로 안하기 때문에 소속을 적어줘야하는듯?e.d.다 된다.
-- order by 절은 소속 명시 안해도 된다.
-- 주의. 테이블명(소속) 명시하자!
select e.empno, e.ename, d.deptno, d.dname
from emp e join dept d on e.deptno = d.deptno
order by d.deptno;

--ANSI 조인(p184)
--join on(p184)
--문법>테이블명 [INNER] JOIN 테이블명 ON 조인조건
--column ambiguously defined
-- 주의. 테이블명(소속) 명시하자!
select e.empno, e.ename, d.deptno, d.dname
from emp e INNER join dept d on e.deptno = d.deptno
order by d.deptno;

--9000, 이순신, DBA, 오늘입사, 급여 7000, 부서 미확정
insert into emp(empno, ename, job, hiredate, sal)
values(9000, '이순신', 'DBA', sysdate, 7000);

commit;

--where절 추가!!(+)왼쪽
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno; -->부서쪽이 다 출력된다.부서번호가 다 나와야 하니까 나머지 컬럼에 널을 추가해가면서까지 출력된다.

--안시외부조인
--where e.deptno(+) = d.deptno; 과 동일한 결과 (외부조인!!OUTER 생략가능)
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e RIGHT OUTER join dept d on e.deptno = d.deptno; --> 오른쪽에 있는 테이블이 다 출력 (위와 동일)

--where절 추가!!(+)오른쪽
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno(+); -->사원쪽이 다 출력된다.사원쪽정보가 다 나와야 하니까 부서쪽 나머지 컬럼에 널을 추가해가면서까지 출력된다

--안시외부조인
--where e.deptno = d.deptno(+); 과 동일한 결과 (외부조인!!OUTER 생략가능)
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e LEFT OUTER join dept d on e.deptno = d.deptno; --> 왼쪽쪽에 있는 테이블이 다 출력 (위와 동일).

--에러발생
--a predicate may reference only one outer-joined table
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno(+); -->+는 아우터 조인인데 이것은 오직 한쪽에만 조인 가능(+)쓰는 문법은 무조건 이렇게.

--안시 아우터 조인만 가능.
select e.empno, e.ename, e.job, d.deptno, d.dname
from emp e FULL OUTER join dept d on e.deptno = d.deptno; --> 양쪽 다 + 하는것은 FULL로 명시해야한다.여전히 OUTER 생략가능

-- NON-EQUI JOIN 조인
--사번, 사원명, 급여, 급여등급 조회
select e.empno, e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal BETWEEN s.losal AND s.hisal;
--where e.sal >= s.losal AND e.sal <= s.hisal; --같은결과

--self join(p180)
--동일한 테이블을 사용해 조인하는 방법
select e1.empno, e1.ename, e1.mgr, e2.empno, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;
--이것도 된다.
--select e1.empno, e1.ename, e1.mgr, e2.empno, e2.ename
--from emp e1 join emp e2
--on e1.mgr = e2.empno;

---

---

---

/*
subquery(서브쿼리 p191)
:서브쿼리란 SQL문장안에서 보조로 사용되는 또 다른 select문을 의미
*/
/
select 컬럼명1,...컬럼명N [AS alias]
from 테이블명 별칭, 뷰명,

~

[where 조건]
[group by 그룹기준]
[having 그룹기준 조건]
[order by 정렬기준 방법]
*/

--20번 부서에 근무하는 사번, 사원명, 급여, 부서번호 조회
select empno, ename, sal, deptno
from emp
where deptno = 20;

--SMITH가 근무하는 부서번호는?
select ename, deptno
from emp
where ename = 'SMITH';

--SMITH와 동일부서에 근무하는 부서번호, 사번, 사원조회(서브쿼리 사용)
select deptno, empno, ename
from emp
--where ename = 'SMITH' or deptno = 20; -->이건 결과는 같은데 ... 서브쿼리 사용 의도에 맞지 않는 구문
where deptno = (select deptno from emp where ename = 'SMITH');

--FORD와 같은 일을 하는 사번, 사원명, 업무 조회-> 1명밖에 없다.
select empno, ename, job
from emp
where job = (select job from emp where ename = 'FORD');

--TURNER와 같은 일을 하는 사번, 사원명, 업무 조회
select empno, ename, job
from emp
where job = (select job from emp where ename = 'TURNER');

--TURNER가 받는 급여보다 많이 받는 사번, 사원명, 급여 조회
--> 해석하면 1500보다 많이 받는 사람 조회
select empno, ename, sal
from emp
where sal > (select sal from emp where ename = 'TURNER')
order by sal asc;

--전체사원들의 평균급여 이상을 받는 사번, 사원명, 업무 조회
--사번, 사원명, 업무조회

--1. 전체사원들의 평균급여를 조회
select avg(sal)
from emp;

select empno, ename, job, sal
from emp
where sal >= (select avg(sal) from emp);

--TURNER와 같은 일을 하는 사원들의 평균급여 이상을 받는 사번, 사원명, 업무 조회

--TURNER가 하는일 조회
--사원명이 TURNER인 업무 조회

select job
from emp
where ename='TURNER';

--TURNER와 같은일을 하는 사원들의 평균급여
-- =>job이 salesman 사원들의 평균급여
select avg(sal)
from emp
where job = 'SALESMAN'; --1400

--사원들의 평균급여 조회
select empno, ename, job, sal
from emp
where 급여가 (평균급여)이상
where 급여가 (1400) 이상
where sal>=(select AVG(sal)
from emp
where job ='SALESMAN');

select empno, ename, job, sal
from emp
where sal >= (select avg(sal) from emp where job = (select job from emp where ename = 'TURNER'))
order by sal asc;

--위의 결과에 부서명을 추가하여 출력하시오
select e.empno, e.ename, e.job, e.sal, d.dname
from emp e, dept d
where sal >= (select avg(sal) from emp where job = (select job from emp where ename = 'TURNER')) and e.deptno = d.deptno
order by sal asc;

select e.empno, e.ename, e.job, e.sal, d.dname
from emp e, dept d
where sal >= (select avg(sal) from emp e1 where e1.job = (select e2.job from emp e2 where e2.ename = 'TURNER')) and e.deptno = d.deptno
order by sal asc;

select e.empno, e.ename, e.job, e.sal, d.dname
from emp e, dept d
where sal >= (select avg(sal) from emp where job = (select job from emp where ename = 'TURNER')) and e.deptno = d.deptno(+)
order by sal asc;

select e.empno, e.ename, e.job, e.sal, d.dname
from emp e LEFT OUTER join dept d on e.deptno = d.deptno
where sal >= (select avg(sal) from emp where job = (select job from emp where ename = 'TURNER'))
order by sal asc;

-- new york에 근무하는 부서번호, 부서명, 소재지, 사번, 사원명,급여 조회
-- 급여를 많이 받는 사원부터 출력
--INNER 생략 가능

--INNER join on
select d.deptno, d.dname, d.loc, e.empno, e.ename, e.sal
from dept d INNER join emp e on d.deptno = e.deptno
where loc = 'NEW YORK'
order by sal desc;

--INNER join using
--deptno 소속 명시 금지
select deptno, d.dname, d.loc, e.empno, e.ename, e.sal
from dept d INNER join emp e using(deptno)
where loc = 'NEW YORK'
order by sal desc;

--natural join
--deptno 소속 명시 금지
select deptno, d.dname, d.loc, e.empno, e.ename, e.sal
from dept d natural join emp e
where loc = 'NEW YORK'
order by sal desc;

--일반 조인!!
select d.deptno, d.dname, d.loc, e.empno, e.ename, e.sal
from dept d, emp e
--where loc = 'NEW YORK' AND d.deptno = e.deptno
where loc = UPPER('new york') AND d.deptno = e.deptno
order by sal desc;

---

--allen이 근무하는 부서의 인원수보다 많은 사원들이 근무하는 부서명, 근무하는 사원수를 구하시오.

select d.dname
from dept d;

--not a single-group group function --->count 함수 쓰려면 그룹바이 절을 써야한다.그리고 조건절 왼쪽에 카운트나 avg같은 함수는 그룹바이 다음 having절에 써야한다.
select d.dname, count(e1.deptno)
from emp e1, dept d
where e1.deptno = (select deptno from emp e where e.ename='ALLEN');

--ssj
select d.dname, count(e.deptno)
from emp e join dept d on d.deptno = e.deptno
group by d.dname
having count(e.deptno) > (select count(deptno)
from emp
where deptno =
(select deptno from emp where ename = 'ALLEN'));

--jjb
select e.deptno, d.dname, count(e.deptno)
from emp e join dept d on e.deptno=d.deptno
where e.deptno is not null
group by e.deptno, d.dname
having count(e.deptno)>=(select count(deptno)
from emp
where deptno=(select deptno
from emp
where ename=upper('allen')))
order by deptno;

---

--p177~180, p194
*다중행 서브쿼리 => 서브쿼리의 결과로 리턴받은 rows수가 여러개일때
연산자(서브쿼리) : IN, ANY, ALL, EXISTS
sal=2450 or sal=5000 or sal=1300 => sal in(2450, 5000, 1300) OR

select empno, ename, sal, deptno
from emp
where sal in(select sal from emp where deptno=10) --or연산 이지만 부등호를 쓸 수 없다.
order by sal desc;

select empno, ename, sal, deptno
from emp
where sal >=any(select sal from emp where deptno=10) --or연산
order by sal desc;

select empno, ename, sal, deptno
from emp
where sal >=all(select sal from emp where deptno=10) --and연산
order by sal desc;

where 컬럼명 >=(서브쿼리);
where sal>=all(2450, 5000, 1300); AND sal>=5000
where sal>=any(2450, 5000, 1300); OR sal>=1300
where sal>=(2450, 5000, 1300);
where sal>=(select sal from emp where deptno=10);

*EXISTS (p194)
--=>서브쿼리의 실행결과가 존재하면 메인쿼리 실행
--=>서브쿼리의 실행결과가 존재하지않으면 메인쿼리 실행결과 없다.
select empno, ename
from emp
where exists(select deptno from dept where dname='SALES'); -->where문 무시~ 있는 값이면 다 보여준다.

select empno, ename
from emp
where exists(select deptno from dept where dname='SALES123'); --> where문 무시~ 없는 값이면 아예 안보여준다.

---

/*
view 객체(p.73)
view : 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체이다.

*View 생성
CREATE [OR REPLACE] 객체타입 객체명
CREATE [OR REPLACE] View View명
AS
Subquery;

*View 삭제
DROP View View명
*/

--사번, 사원명, 부서명 조회 --> 권한 없다고 하면 system 계정으로 접속해서 grant create view to scott; commit;
CREATE View emp_dept
AS
select e.empno, e.ename, d.dname
from emp e join dept d
on e.deptno = d.deptno;

CREATE or REPLACE View emp_dept
AS
select e.empno, e.ename, e.sal, d.dname
from emp e join dept d
on e.deptno = d.deptno
where e.sal>=2450;

drop View emp_dept;

select * from emp_dept;

--수정
--7698사원의 급여를 3000으로 수정 (2850 -> 3000)
/*
UPDATE 뷰명
set 컬럼명=새값
where 테이블명
*/

UPDATE emp_dept set sal = 3000 where empno=7698;

select * from emp_dept where empno=7698;
select * from emp where empno=7698; --> 뷰를 수정해도 원본 테이블 데이터도 수정된다.
--원본 EMP 테이블의 7698사원의 급여를 다시 2850으로 수정 (3000->2850)-->원본이 수정되면 자동으로 뷰도 수정된다.
update emp set sal = 2850 where empno = 7698;

--뷰에서 7698사원의 급여를 100으로 수정
update emp_dept set sal=100 where empno=7698;

--원본 emp에서 확인
select * from emp where empno=7698; -->100으로 수정하면 원본 테이블 데이터 수정 된다.

--뷰 emp_dept에서 확인 --> 얘는 100으로 수정된 것이 안보인다애초에 뷰를 생성할때 서브쿼리 조건이 2450이상이기 때문에 안보이는것.
select * from emp_dept;

--생성된 뷰 구조 확인
desc user_views;

select view_name, text, read_only
from user_views;

--삭제
--delete from emp_dept where empno=7698;

---

--system 계정 접속
conn system/asdf123

--계정정보 및 PW 및 계정잠금상태 변경
문법>ALTER USER 유저명
IDENTIFIED BY 비번
ACCOUNT UNLOCK | LOCK ;

--hr계정 pw hr, 잠금 풀기
ALTER USER hr
IDENTIFIED BY hr
ACCOUNT UNLOCK;

*p198 인라인뷰(inline view)
-->오랫동안 또는 영구적으로 사용할거면 뷰를 생성하면 되고 일시적으로 처리 할 거면 인라인 뷰형태인 서브쿼리로 쓰면 된다.
--from절에 서브쿼리(subquery) 가 들어가면 inline view

--부서별, 부서명, 평균급여, 총급여를 부서번호 오름차순 정렬하여 출력
--이때 평균급여는 소수점 첫째자리에서 반올림

--내가 한 것.
select d.deptno, d.dname, sum(e.sal), round(avg(e.sal),0) -->이경우 e.없어도 실행된다.
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname
order by deptno asc;

--답안-->인라인뷰 이용한것이 맞음.인라인 뷰테이블 자체를e로 지정!
select d.dname, e.avg_sal, e.sum_sal
from dept d,
(select deptno, round(avg(sal), 0) avg_sal, sum(sal) sum_sal
from emp
where deptno is not null
group by deptno) e
where e.deptno = d.deptno
order by d.deptno asc;

--join on으로 변경 결과는 위와 같다.
select d.dname, e.avg_sal, e.sum_sal
from dept d join
(select deptno, round(avg(sal), 0) avg_sal, sum(sal) sum_sal
from emp
where deptno is not null
group by deptno) e on e.deptno = d.deptno
order by d.deptno asc;

---

--*SEQUENCE(시퀀스)

/*
SEQUENCE(시퀀스)(p.83) : 자동순번을 반환하는 데이터베이스 객체
사용 예) 사원번호, 부서번호, 회원번호, 주문번호, 대여번호, 예약번호, 글번호
/
--시퀀스 조회
select *
from user_sequences;

/*

• 생성
  CREATE SEQUENCE 시퀀스명
  [START WITH 시작번호] --시작번호(수정불가)
  [INCREMENT BY 값] --증감규칙
  [MINVALUE 최저값]
  [MAXVALUE 최고값]
  [CYCLE | NOCYCLE]
  [CACHE | NOCACHE];

*수정
ALTER SEQUENCE [스키마명.] 시퀀스명
increment by 값 --증값규칙
minvalue 값 --시작번호
maxvalue 값 --끝번호

--*수정
ALTER SEQUENCE 스키마명.시퀀스명
--[START WITH 시작번호] --시작번호(수정불가)
[INCREMENT BY 값] --증감규칙
[MINVALUE 최저값]
[MAXVALUE 최고값]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE];

• 삭제
  문법>DROP SEQUENCE 시퀀스명;
  DROP SEQUENCE dept_deptno_seq;
  SELECT * FROM USER_SEQUENCES;
• /
• /

--부서dept테이블의 deptno컬럼의 값으로 사용하고자하는 시퀀스생성
--71부터 81까지 1씩 증가
CREATE SEQUENCE scott.dept_deptno_seq
START WITH 71 --시작번호 (수정불가
increment by 1 --증감규칙
minvalue 71 --시작번호
maxvalue 81; --끝번호

insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept1'); --71
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept2'); --72
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept3'); --73

select * from dept;

--사원번호(시퀀스 이용), 'emp1' 입력
insert into emp(empno, ename)
values(dept_deptno_seq.nextval,'emp1'); --74가 들어간다. 위에 73까지 들어갔으니...
insert into emp(empno, ename)
values(dept_deptno_seq.nextval,'emp2'); --75
select * from emp;

--부서의 부서번호 INSERT ? 몇 ?
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept4'); --76
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept5'); --77
select * from dept;
select dept_deptno_seq.nextval from dual; --78
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept6'); --79

rollback;

select * from dept; -- 71, 72, 73, 76, 77, 79
select * from emp; -- 74, 75,
--롤백 후에도 그 다음 숫자부터 이어서 입력 된다.
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept7'); --80
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept8'); --81
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept9'); --오류발생
--sequence DEPT_DEPTNO_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

--시퀀스를 다시 만들어서 새로 써도 되지만... 수정하는 방식으로 !!
--71부터 89까지 2씩 증가!

--cannot alter starting sequence number
--시작번호는 수정불가하다
ALTER SEQUENCE scott.dept_deptno_seq
--START WITH 71--시작번호 (수정불가)
increment by 2 --증감규칙
minvalue 1 --시작번호
maxvalue 89; --끝번호

--81에서 2씩 증가

insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'AFTER--'); --83
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept10'); --85
insert into dept(deptno, dname)
values(dept_deptno_seq.nextval,'dept11'); --87

--*삭제
DROP SEQUENCE scott.dept_deptno_seq;

select * from USER_SEQUENCES;

delete from dept where loc is null;
delete from dept where not in(10, 20, 30, 40, 90);

select * from emp;
select * from dept;

commit;

--emp의 식별키 empno에 사용될 시퀀스 생성
CREATE SEQUENCE scott.emp_empno_seq
START WITH 9100
INCREMENT BY 1 ;
select * from user_sequences;

--시퀀스명.NEXTVAL
--시퀀스명.CURRVAL
--위에서 생성한 시퀀스를 이용하여 emp에 데이터 입력
insert into emp(empno, ename, job, hiredate, sal, comm)
values(emp_empno_seq.nextval, '조규성', '개발', sysdate, 5000, 2500);

select * from emp
order by empno desc;

rollback;
--자바로 넣은 데이터는 오토커밋된다. 롤백해도 그대로

---

--테이블 변경, 삭제, 추가 등의 작업은 상당히 주의해야한다.
=> 모델링을 잘 해야한다. 모델링의 중요!!!
--복사생성
create table 테이블명
AS
SUBQUERY;(select문)

--*생성
create table 테이블명(
컬럼명 데이터타입(크기),
컬럼명 데이터타입(크기) default 기본값,
컬럼명 데이터타입(크기) [constraint 제약조건명] 제약조건
);

--*수정
alter table 테이블명
-추가 ADD
-변경 MODIFY
-삭제 DROP

--*삭제
drop table 테이블명;

--*제약조건 조회
select *
from user_constraints;

--*제약조건 추가

--*제약조건 삭제

---

--@emp table 복사생성
--부서번호 10번인 사원들의 사원번호, 사우너명, 업무, 입사일, 부서번호 출력(입사일 기준으로 오름차순)
--데이터 타입, 크기, 데이터복사
create table emp10
as
select empno, ename, job, hiredate, deptno
from emp
where deptno = 10
order by hiredate asc;

--*수정
--emp10테이블에 etc 숫자(10) 컬럼 추가
--추가 ADD
문법>alter table 테이블명
ADD 컬럼명 데이터 타입(크기);
--컬럼추가
alter table emp10
ADD etc number(10);

select * from emp10;

--변경 MODIFY (p.68)
문법>alter table 테이블명
MODIFY 컬럼명 데이터 타입(크기);
--앞에서 추가한 etc컬럼의 데이터타입과 크기를
--varchar2(100)으로 변경
--주의.컬럼의 데이터타입, 크기변경시에는 데이터가 있는지 확인해야한다.
--이미 데이터가 들어가 있는데 타입을 변경하면 안된다.
--예) 날짜데이터가 들어가 있는데 숫자타입으로 바꾸면 안된다는 뜻.
alter table emp10
modify etc varchar2(100);

select * from emp10;

--삭제 DROP
문법>alter table 테이블명
DROP column 컬럼명; or DROP constraint 제약조건명;
--앞에서 추가한 etc컬럼삭제
alter table emp10
drop column etc;

select * from emp10;

--테이블 복사 생성
create table empsales
as
select empno no, ename name, job
from emp
where job='SALESMAN';

desc empsales; -->컬럼명 alias로 출력 된다.

--empsales삭제
drop table empsales;

--테이블 복사 생성시 조건이 false 이면 데이터 복사X 구조만 가져온다.(조건문 이상할때)
create table empsales
as
select empno no, ename name, job
from emp
where 1=2;

select * from empsales;

---

--*emp테이블을 복사하여 emp2생성(테이블 구조만 복사, 데이터복사 X)

--사원번호의 컬럼명은 eno
--ename
--job
--mgr컬럼명은 mgrid
--hiredate
--sal

create table emp2
as
select empno eno, ename, job, mgr mgrid, hiredate, sal
from emp
where 1=2;

desc emp2;
select * from emp2;

---

--*제약조건(constraint)
-- 제약조회
SELECT constraint_name, constraint_type, table_name
FROM USER_CONSTRAINTS;

-- 제약조건 종류
1)not null : null허용x=>값을 넣어라. 필수입력
2)unique : 유일한 값=>중복허용x
3)check : 조건만족하는지 체크. check(조건)
4)Primary key:pk.식별키. nn+unique이면서 테이블대표, 각 레코드를 구분
-- 주의 : 컬럼타입(문자&날짜는 '')
컬럼크기(숫자&영문 1글자는 1byte,
여기에서는 한글1글자는 3byte(한글은 encoding설정에 따라 달라짐))
5)Foreign key : FK.외래키
PK(혹은 UK)컬럼의 값을 참조하는 키(references key)

--*emp테이블을 복사하여 emp2생성(테이블 구조만 복사, 데이터복사 X)

--사원번호의 컬럼명은 eno => pk
--ename ==> nn
--job
--mgr컬럼명은 mgrid => uk
--hiredate
--sal => check sal between 700 and 9999

참고문법>
alter table 테이블명 ADD 컬럼명 데이터타입(크기);--컬럼추가
*수정
--테이블의 제약조건 추가(변경)-p71
--문법>
ALTER table 테이블명
ADD [constraint 제약조건명] 제약조건(컬럼명);

--emp2테이블의 eno에 PK 제약조건 추가(변경)-p71
--문법>
ALTER table emp2
ADD constraint pk_emp2_eno primary key(eno);

--emp2테이블의 mgrid에 UK 제약조건 추가
--문법>
ALTER table emp2
ADD constraint uk_emp2_eno unique(mgrid);

--emp2테이블의 sal에 check 제약조건 추가
--check sal between 700 and 9999
--문법>
ALTER table emp2
ADD constraint ck_emp2_sal check(sal between 700 and 9999);

--emp2테이블의 ename에 nn 제약조건 변경
--문법> add가 아니라 modify로 해야한다.--컬럼명이 앞에 들어간다.modify문법이라서 이미 있는 컬럼이라서 데이터타입과 값은 입력하지 않는다.
ALTER table emp2
MODIFY ename constraint nn_emp2_ename not null;

--emp2테이블의 hiredate에 기본값 오늘
--테이블 특정컬럼에 기본값 적용(아래에는 이형식 사용)
--문법>
ALTER table 테이블명
MODIFY 컬럼명 default 값;
--문법> add가 아니라 modify로 해야한다
ALTER table emp2
MODIFY hiredate default SYSDATE;

---

--제약조건 있는 테이블에 DML
select * from emp2;

--*****입력
1, NULL, NULL, NULL, NULL, NULL
insert into emp2
--values(1, NULL, NULL, NULL, NULL, NULL);
--cannot insert NULL into ("SCOTT"."EMP2"."ENAME") ename이 not null이기 때문에 에러발생
values(1, '홍길동', NULL, NULL, NULL, NULL);

select * from emp2;

--eno컬럼의 값을 null입력
insert into emp2
--values(null, '홍길동', NULL, NULL, NULL, NULL); --PK는 NN이므로 오류발생
--cannot insert NULL into ("SCOTT"."EMP2"."ENO")
values(1, '홍길동', NULL, NULL, NULL, NULL); --PK는 유일한 값이어야 하는데
--unique constraint (SCOTT.PK_EMP2_ENO) violated

select * from emp2;

-- 2, 이순신, mgrid는 1입력 이순신이 상사
insert into emp2(eno, ename, mgrid)
values(2, '이순신', 1);
-- 기본값은 묵시적 NULL입력시 들어간다. hiredate는 오늘날짜가 입력된다.
select * from emp2;

delete from emp2 where ename='이순신';
delete from emp2 where ename='강감찬'; -->그냥 내가 필요해서 넣은것

--3, '강감찬', 1입력
insert into emp2(eno, ename, mgrid)
values(3, '강감찬', 1); --MGROD는 unique한 값을 넣어야 하므로 오류발생
--unique constraint (SCOTT.UK_EMP2_ENO) violated

--기본값을 명시적으로 NULL해보자
--3, '강감찬', 2입력
insert into emp2
values(3, '강감찬', null, 2, null, null);
select * from emp2;

--4, 김구, 3, 입사일 기본값 적용, 급여는 700
insert into emp2(eno, ename, mgrid, sal)
values(4, '김구', 3, 700);
select * from emp2;

--*****수정
update 테이블명
set 컬럼명=새값, 컬럼명=새값
where 조건

---강감찬의 급여를 7770으로 수정
update emp2
set sal=7770
where ename = '강감찬';
select * from emp2;

---이순신의 급여를 9999으로 수정
update emp2
set sal=9999
where ename = '이순신';
select * from emp2;

---홍길동의 급여를 119999으로 수정
UPDATE emp2
SET sal = 119999
where ename = '홍길동'; --급여는 700~9999사이어야만 한다. 그래서 오류가 난다.
--value larger than specified precision allowed for this column
select * from emp2;

--emp2테이블에 deptno number(2)컬럼을 추가
--foreign key
--fk인 컬럼이 참조한다. 부모테이블의( PK인) 컬럼을
--(fk)인 컬럼이 참조한다. 부모테이블의( PK인)컬럼명
--(fk)인 컬럼명 references 부모테이블의( PK인)컬럼명

--emp2테이블에 deptno number(2)컬럼을 추가
alter table emp2
add deptno number(2);
desc emp2;
select * from emp2;

--기존테이블에 fk제약조건 추가
--문법>
alter table 테이블명
add constraint 제약조건명
제약조건(컬럼명) references 부모테이블의( PK인)컬럼;

--emp2에 fk제약조건 추가
alter table emp2
add constraint fk_emp2_deptno
foreign key(deptno) references dept(deptno);

select * from emp2;
--현재기준 dept.deptno(부모)컬럼의 값에는 10, 20, 30, 40, 90 존재
--fk는 null허용. 중복허용
--2번, 이순신의 부서번호를 90으로 변경
update emp2
set deptno=90
where eno=2 and ename='이순신';
--3번, 강감찬의 부서번호를 90으로 변경
update emp2
set deptno=90
where eno=3 and ename='강감찬'; -->중복허용!!

--4번, 김구의 부서번호를 99으로 변경
update emp2
set deptno=99
where eno=4 and ename='김구'; -->99부서는 존재하지 않는 부서이기 때문에 안된다!!!--참조하는 컬럼에 존재하는 값만 가능!!
--integrity constraint (SCOTT.FK_EMP2_DEPTNO) violated - parent key not found -->무결성 제약에 위반!!

select * from emp2;

--부모와 자식관계 pk와 fk인 컬럼에서 입력, 수정, 삭제

              부서(dept.deptno)                                                       사원(emp2.deptno)

---

입력 [※항상가능※] [※부분가능※]
부모 테이블인 dept에서 입력을 하는것은 언제든지 가능!!! 존재하는 부모키에 한해 가능
(부모한테 없는값은 업데이트 불가)(단, null가능)

수정 [※부분가능※] [※부분가능※]
참조하는 자식레코드가 없으면 언제든 가능!!! 존재하는 부모키에 한해 가능
(부모한테 없는값은 업데이트 불가)(단, null가능)

삭제 [※부분가능※] [※항상가능※]
참조하는 자식레코드가 없으면 언제든 가능!!! 자식 테이블인 emp2에서 삭제를 하는것은 언제든지 가능!!!

---

--dept테이블에 부서입력(91, dept91)
insert into dept(deptno, dname)
values(91, 'dept91');

--dept테이블의 91번 부서번호를 99로 수정
update dept set deptno = 99 where deptno=91;

--dept테이블의 90번 부서번호를 1로 수정
update dept set deptno = 1 where deptno=90; --무결성 제약조건 위반
--integrity constraint (SCOTT.FK_EMP2_DEPTNO) violated - child record found
--업데이트 하고자 하는 데이터는 emp2에서 참조하고 있으므로 수정 불가

select * from dept;

--dept테이블에서 deptno=99 삭제
delete from dept where deptno=99;

--dept테이블에서 deptno=90 삭제
delete from dept where deptno=90;--무결성 제약조건 위반
--integrity constraint (SCOTT.FK_EMP2_DEPTNO) violated - child record found
--삭제 하고자 하는 데이터는 emp2에서 참조하고 있으므로 삭제 불가
select * from dept;

-- emp2테이블에 사원입사 (사번은 5번, 나머지는 임의내용)
insert into emp2(eno, ename, deptno)
values(5, '이이', null);

insert into emp2(eno, ename, deptno)
values(6, '이도', 99); --dept.deptno 컬럼에 99 값은 없어서 오류!!
--integrity constraint (SCOTT.FK_EMP2_DEPTNO) violated - parent key not found

insert into emp2(eno, ename, deptno)
values(6, '이도', 90);

select * from emp2;

-- 5, 이이 삭제
delete emp2 where eno=5;

-- 6, 이도 삭제
delete emp2 where eno=6;

select * from emp2;
/*
★★입력은 부모먼저하고 자식!★★
*테이블생성 : 부모테이블 ->자식테이블 순으로 생성
*부모테이블의 pk컬럼의 데이터입력 ->-> 자식테이블의 fk컬럼의 데이터입력 순
*/

/*
★★삭제는 자식먼저하고 부모!★★
*테이블삭제 : 자식테이블 ->부모테이블 순으로 삭제
*참조하는 child record가 존재해도 부모테이블의 데이터를 삭제하고 싶다면
방법1. fk제약조건삭제 후 부모테이블의 데이터 삭제
방법2. 참조하는 child record도 같이 삭제
*/

*fk제약조건 설정시 on delete cascade
constraint 제약조건명
foreign key(컬럼명) references 참조테이블명(컬럼명)
[on delete cascade];

외래키 쪽 제약조건 생성시 on delete cascade 조건 적용하면 참조테이블 삭제시 관련되는 child record 데이터도 같이 삭제된다.!
원래 참조되는 child record가 있으면 에러뜨면서 삭제가 안되지만 해당 조건을 명시해서 외래키를 생성해놓으면 참조테이블삭제시 같이 삭제 된다.

---

*제약조건이 있는 테이블 생성
--형태1
문법>CREATE TABLE 테이블명(
컬럼명 데이터타입(크기) [DEFAULT 기본값] [CONSTRAINT 제약조건명 제약조건],
컬럼명 데이터타입(크기) CONSTRAINT 제약조건명 PRIMARY KEY,
컬럼명 데이터타입(크기) CONSTRAINT 제약조건명 UNIQUE,
컬럼명 데이터타입(크기) CONSTRAINT 제약조건명 NOT NULL,
컬럼명 데이터타입(크기) CONSTRAINT 제약조건명 REFERENCES 부모테이블(컬럼명)
);
--형태2
문법> create table 테이블명(
컬럼명 데이터타입(크기) [default 기본값],
컬럼명 데이터타입(크기) [default 기본값] CONSTRAINT 제약조건명 NOT NULL,
컬럼명 데이터타입(크기) [default 기본값],
constraint 제약조건명 primary key(컬럼명)
constraint 제약조건명 UNIQUE(컬럼명)
constraint 제약조건명 CHECK(조건)
constraint 제약조건명 FOREIGN KEY(컬럼명)
references 부모테이블명(컬럼명)
);
--명시 안하면 기본값이 NULL인데 밑에 NOT NULL선언하는것이 문법상 맞지 않아 NOT NULL만 기본 컬럼명 구문 뒤에 바로 제약조건을 써준다.

---

--*****삭제
*테이블 삭제
문법>DROP table 테이블명 [PURGE]

--EMP10삭제
drop table EMP10;

*삭제한 테이블 복원
문법>flashback table "테이블명" to before drop;

flashback table "CTEST" to before drop;
flashback table "EMP10" to before drop;

*완전하게 테이블제거(flashback 불가)
문법>drop table 테이블명 purge;
--CTEST테이블 제거
drop table CTEST purge;

*휴지통 비우기
문법>PURGE RECYCLEBIN;
--휴지통비우기
PURGE RECYCLEBIN;

---

SELECT constraint_name, constraint_type, table_name
FROM USER_CONSTRAINTS;

---

트랜잭션 p.328

PL/SQL p.260

---

/*
DECLARE
--변수선언 java에서는

데이터타입 변수명[=초기값];
--변수명 데이터타입 변수명[:=초기값]; --초기값 생략시 null
[IS(AS)]
--선언부:변수, CURSOR등
BEGIN
--실행부
DBMS_OUTPUT.PUT_LINE('hello

안녕');
END;
/
*/

--IF조건문
/*
IF형태1
IF 조건 THEN
조건 만족시 실행코드
END IF;

---

---

IF형태2
IF 조건 THEN
조건만족시실행코드
ELSE
조건만족x실행코드
END IF;

---

---

IF형태3
IF 조건1 THEN
조건1 만족시 실행코드
ELSIF 조건2 THEN
조건2 만족시 실행코드
ELSE
조건만족X 실행코드
END IF;
*/

---

---

--데이터를 가진 2개의 변수의 크기를 비교하여 출력
DECLARE
num1 NUMBER:=200;
num2 NUMBER:=-200;
BEGIN
-- DBMS_OUTPUT.PUT_LINE('hello~안녕!');
DBMS_OUTPUT.PUT_LINE('변수 num1의 값 :' || num1);
DBMS_OUTPUT.PUT_LINE('변수 num2의 값 :' || num2);

IF num1>num2 THEN
    DBMS_OUTPUT.PUT_LINE('변수 num1의 값은 num2 보다 크다');
ELSIF num1=num2 THEN
    DBMS_OUTPUT.PUT_LINE('변수 num1의 값과 num2 값은 같다');
ELSE
    DBMS_OUTPUT.PUT_LINE('변수 num1의 값은 num2 보다 작다');
END IF;

END;
/

---

--DBMS_OUTPUT.PUT_LINE('hello~안녕'); 실행결과를 화면에 출력시키기 위한 설정
SET SERVEROUTPUT ON;

DECLARE

BEGIN
DBMS_OUTPUT.PUT_LINE('hello~안녕');
END;
/

---

--CASE 문(p277)
/*유형1
CASE 표현식
WHEN 결과1 THEN
처리문1;
WHEN 결과2 THEN
처리문2;
…
ELSE
기타 처리문;
END CASE;

유형2
CASE WHEN 표현식1 THEN
처리문1;
WHEN 표현식2 THEN
처리문2;
…
ELSE
기타 처리문;
END CASE;
● 첫 번째보다는 두 번째 유형을 많이 사용*/

DECLARE
num1 NUMBER:=200;
num2 NUMBER:=100;
BEGIN
-- DBMS_OUTPUT.PUT_LINE('hello~안녕!');
DBMS_OUTPUT.PUT_LINE('변수 num1의 값 :' || num1);
DBMS_OUTPUT.PUT_LINE('변수 num2의 값 :' || num2);

CASE WHEN num1>num2 THEN
    DBMS_OUTPUT.PUT_LINE('변수 num1의 값은 num2 보다 크다');
WHEN num1=num2 THEN
    DBMS_OUTPUT.PUT_LINE('변수 num1의 값과 num2 값은 같다');
ELSE
    DBMS_OUTPUT.PUT_LINE('변수 num1의 값은 num2 보다 작다');
END CASE;

END;
/

---

--LOOP문(p278)
/*
● 루프를 돌며 로직을 처리하는 반복문?
● 구문?
LOOP?
처리문;?
EXIT [WHEN 조건];?
END LOOP;?
● 루프를 돌다가 EXIT WHEN 조건을 만나면 루프 종료 */

-- 1 2 3.....9 출력
DECLARE
num1 number:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('num1:'||num1);
num1:=num1+1; --java에서의 num1++;
EXIT WHEN num1>9; --num1의 값이 9보다 커지면 loop빠져나가라
END LOOP;
DBMS_OUTPUT.PUT_LINE('LOOP밖');
END;
/

---

--31=3부터 3*9=27까지 출력
DECLARE
dan number:=3;
num1 number:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(dan||''||num1||'='||dan*num1);
num1:=num1+1; --java에서의 num1++;
EXIT WHEN num1>9; --num1의 값이 9보다 커지면 loop빠져나가라
END LOOP;
DBMS_OUTPUT.PUT_LINE('LOOP밖');
END;
/

---

/*
WHILE문(p.279)
●구문
WHILE 조건
LOOP
처리문;
END LOOP;
●WHILE 다음 조건이 TRUE인 경우 루프를 돌고, FALSE인 경우 루프를 빠져나간다
*/

-- 31=3부터 3*9=27까지 출력
DECLARE
dan number:=3;
num1 number:=1;
BEGIN
WHILE num1<=9 --num1의 값이 9보다 작으면 반복해라
LOOP
DBMS_OUTPUT.PUT_LINE(dan||''||num1||'='|| dan*num1);
num1:=num1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('LOOP밖');
END;
/

---

?/*
for 문(p.281)
● 구문?
FOR 인덱스 IN [REVERSE] 초기값..최종값?
LOOP?
처리문;?
END LOOP;?
● IN 다음 조건이 만족할 경우에 루프를 돈다.
/
-- 31=3부터 39=27까지 출력
DECLARE
dan number:=3;
BEGIN
for num1 in 1..9 --num1 변수의 값이 1부터 9까지 1씩 증가
LOOP
DBMS_OUTPUT.PUT_LINE(dan||''||num1||'='|| dan*num1);
END LOOP;
DBMS_OUTPUT.PUT_LINE('LOOP밖');
END;
/

-- 39=27 부터 3*1=3 까지 출력
DECLARE
dan number:=3;
BEGIN
for num1 in reverse 1..9 --num1 변수의 값이 9부터 1까지 1씩 감소
LOOP
DBMS_OUTPUT.PUT_LINE(dan||''||num1||'='|| dan*num1);
END LOOP;
DBMS_OUTPUT.PUT_LINE('LOOP밖');
END;
/

---

??/*
contunue 문(p.282)
● 반복문 내에서 특정 조건에 부합할 때 처리로직을 건너뛰고 상단의 루프 조건으로 건너가 ?
루프를 계속 수행할 때 사용?
*/

-- 31=3부터 39=27까지 출력(단, 35=15는 제외)
DECLARE
dan number:=3;
BEGIN
for num1 in 1..9 --num1 변수의 값이 1부터 9까지 1씩 증가
LOOP
--continue when num1 in(5,6,7); --> 이건 35 36 37 결과 제외
continue when num1=5; --> 이건 35 36 37 결과 제외
--num1의 값이 5이면 아래반복실행 하지말고 다음반복문을 위한 반복조건으로 이동
DBMS_OUTPUT.PUT_LINE(dan||''||num1||'='|| dan*num1);
END LOOP;
DBMS_OUTPUT.PUT_LINE('LOOP밖');
END;
/

---

??/*
NULL 문(p.284)
● 아무것도 처리하지 않는 문장
/
/ 사용 예
CASE WHEN 표현식1 THEN
처리문1;
WHEN 표현식2 THEN
처리문2;
…
ELSE NULL;
기타 처리문;
END CASE;

---

EX)if문
IF 조건1 THEN
조건1 만족시 실행코드
ELSIF 조건2 THEN
조건2 만족시 실행코드
ELSE NULL;
조건만족X 실행코드
END IF;

*/
DECLARE
num1 NUMBER:=50;
BEGIN
-- DBMS_OUTPUT.PUT_LINE('hello~안녕!');
DBMS_OUTPUT.PUT_LINE('변수 num1의 값 :' || num1);

IF num1>100 THEN
    DBMS_OUTPUT.PUT_LINE('변수 num1의 값은 100 보다 크다');
ELSIF num1=0 THEN
    DBMS_OUTPUT.PUT_LINE('변수 num1의 값은 0이다');
ELSE --그 외는 0보다 작으다면 아무것도 안한다.
    NULL;  --> 안쓰면 에러!
END IF;

END;
/

---

--지정한 사번인 사원의 기존의 급여의 500%를 인상
--사번 9106
select empno, sal from emp where empno=9106;
update emp
set sal=sal*5
where empno='9106';

--사원번호로 사원상세정보 조회
--사번, 사원명, 업무, 급여, 커미션, 부서명 (입사일)
--커미션이 미확정된 사원은 1230으로 처리

select e.empno, e.ename, e.job, e.sal, nvl(e.comm, 1230) as bonus, d.dname
from emp e join dept d on e.deptno=d.deptno
where e.empno=7902;

commit;

select * from emp;

 

 

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

 

--시퀀스 삭제
DROP SEQUENCE notice_seq;


--시퀀스 생성
CREATE SEQUENCE notice_seq
START WITH 1
INCREMENT BY 1
NOCYCLE;


--테이블 삭제
DROP TABLE notice;

--테이블 생성
CREATE TABLE notice(
nbno number(4) constraint pk_notice_nbno primary key, --글번호
title varchar2(1000) constraint nn_notice_title NOT NULL, --제목
centent VARCHAR2(2000) constraint nn_notice_content NOT NULL,  --내용
cre_date date default SYSDATE, --작성일
writer varchar2(30) constraint nn_notice_writer NOT NULL, --작성자
rcnt NUMBER(5) default 0 constraint ck_notice_rcnt check(rcnt>=0), --조회수
empno number(30)
constraint fk_notice_empno references emp(empno) on delete cascade  --작성자 아이디
);

--더미데이터
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목1', '내용1', SYSDATE-10,'관리자',10,7900);
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목2', '내용2', SYSDATE-10,'관리자',10,7900);
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목3', '내용3', SYSDATE-10,'관리자',10,7900);
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목4', '내용4', SYSDATE-10,'관리자',10,7900);
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목5', '내용5', SYSDATE-10,'관리자',10,7900);







-------------------------------------------------------------------------------------------
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목1', '내용1', SYSDATE-10,'관리자',100,7900);
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목2', '내용2', SYSDATE-9,'관리자',90,7900);
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목3', '내용3', SYSDATE-8,'관리자',88,7900);
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목4', '내용4', SYSDATE-7,'관리자',77,7900);
insert into notice(nbno, title, centent, cre_date, writer, rcnt, empno)
values(notice_seq.nextval, '제목5', '내용5', SYSDATE-6,'관리자',66,7900);





commit;

insert into 테이블명(컬럼명,...컬럼명) values(값, 값, ....값);