D:\query\LEE

--LEE

SELECT * FROM TAB;

SELECT * FROM CUSTOM;

SELECT * FROM COMPANY;

SELECT * FROM POINT;

SELECT * FROM PRODUCT;

SELECT * FROM RESEARCH; 

SELECT * FROM SALES;

SELECT * FROM CUSTOM ORDER BY USERNAME; --영어가 먼저 정렬

SELECT * FROM CUSTOM ORDER BY AGE;

SELECT PRODUCTID,PRODUCTNAME,PRICE,PRICE*0.8 AS 할인가 ,PRICE*0.2 AS 할인금액 FROM PRODUCT; --할인금액을 바코드로 가져와서 영수증에 출력

SELECT * FROM CUSTOM;

SELECT ADDR1 || ' ' || ADDR2 || ' ' || ADDR3 AS ADDR FROM CUSTOM;

SELECT * FROM CUSTOM WHERE AGE <= 20;

SELECT * FROM CUSTOM WHERE JOB='학생';

SELECT * FROM CUSTOM WHERE SCHOL='기타';

SELECT * FROM CUSTOM WHERE ADDR1='서울특별시';

SELECT * FROM CUSTOM WHERE REGDATE>='2000-07-01';

SELECT * FROM CUSTOM WHERE REGDATE>='2000-07-01' ORDER BY REGDATE;

SELECT * FROM CUSTOM WHERE REGDATE>='2000-07-01' ORDER BY AGE; --나이순으로 정렬

SELECT * FROM CUSTOM WHERE ADDR1 LIKE '서울%';

SELECT * FROM CUSTOM WHERE ADDR1 LIKE '서울%' AND USERNAME LIKE '김%';

SELECT * FROM CUSTOM WHERE AGE LIKE '3%';

SELECT * FROM CUSTOM WHERE ADDR3 LIKE '%호%'; --중간에 무조건 호가 들어가있는거 

SELECT * FROM CUSTOM WHERE ADDR3 LIKE '%트'; --끝에가 무조건 트로 끝나는거

SELECT USERNAME,SUBSTR(USERNAME,1,1) 성, SUBSTR(USERNAME,2,2) 이름 FROM CUSTOM; 

CREATE TABLE post (

seq  NUMBER  NOT NULL

,zipcode  VARCHAR2(10)

,sido  VARCHAR2(50)

,gugun  VARCHAR2(100)

,dong  VARCHAR2(100)

,bunji  VARCHAR2(50)

        ,PRIMARY KEY (seq)

);

SELECT * FROM tab;

SELECT * FROM POST; --컬럼만 생성

SELECT * FROM POST WHERE DONG LIKE '역삼%';

SELECT SIDO || ' ' || GUGUN || ' ' || DONG 검색주소 FROM POST WHERE DONG LIKE '수택%';

DROP TABLE POST PURGE; --POST TABLE 삭제!

SELECT USERID,USERNAME,RPAD(SUBSTR(JUMIN,1,8),14,'*') JUMIN2 FROM CUSTOM;

SELECT RPAD(SUBSTR(JUMIN,1,8),14,'*') JUMIN FROM CUSTOM; --중첩

DESC CUSTOM;

SELECT * FROM COMPANY;

SELECT SUM(PAY) FROM COMPANY WHERE POSIT='대리';

SELECT SUM(PAY) FROM COMPANY WHERE POSIT='과장';

--MAX/MIN

SELECT MIN(PAY) FROM COMPANY;

SELECT MAX(PAY) FROM COMPANY;

SELECT * FROM CUSTOM;

SELECT DISTINCT SCHOL,COUNT(*) OVER(PARTITION BY SCHOL) 인원수

FROM CUSTOM;

SELECT * FROM COMPANY;

--직책별 평균PAY

SELECT DISTINCT POSIT ,ROUND(AVG(PAY) OVER(PARTITION BY POSIT)) 평균,

ROUND(SUM(PAY) OVER (PARTITION BY POSIT)) 합계,

ROUND(COUNT(PAY) OVER(PARTITION BY POSIT)) 인원수

FROM COMPANY;

SELECT * FROM CUSTOM;

SELECT AGE,COUNT(*),SUM(POINT) FROM CUSTOM 

GROUP BY AGE;

SELECT ADDR1,COUNT(*),SUM(POINT) FROM CUSTOM 

GROUP BY ADDR1;

SELECT ADDR1,COUNT(*) 인원수 FROM CUSTOM 

GROUP BY ADDR1;

SELECT 

CASE SEX 

WHEN '1' THEN '남자'

WHEN '0' THEN '여자'

END 성별 

,COUNT(*),SUM(POINT) FROM CUSTOM 

GROUP BY SEX;

SELECT JOB,COUNT(*),SUM(POINT) FROM CUSTOM 

GROUP BY JOB;

SELECT * FROM COMPANY;

SELECT POSIT,ROUND(AVG(PAY)) 평균 ,SUM(PAY) 총액 FROM COMPANY

GROUP BY POSIT;

SELECT AGE,COUNT(*),SUM(POINT) FROM CUSTOM

GROUP BY AGE

HAVING AGE>30

ORDER BY AGE;

SELECT ADDR1,COUNT(*) 인원수 FROM CUSTOM

GROUP BY ADDR1

HAVING ADDR1='제주도';

--HAVING COUNT(*) >= 40;

SELECT * FROM COMPANY;

SELECT POSIT 직책, ROUND(AVG(PAY)) 평균, SUM(PAY) 총액 FROM COMPANY

GROUP BY POSIT

HAVING ROUND(AVG(PAY)) < 1500000;

--JOIN문

--2개이상의 테이블에서 데이터검색

--INNER JOIN(EQUI JOIN) --제일 많이 씀!

--NON-EQUI JOIN(오라클만)  --나머지는 표준

--OUTER JOIN

--CROSS JOIN

--SELF JOIN

SELECT * FROM CUSTOM ORDER BY USERNAME;            --953075

SELECT * FROM COMPANY WHERE USERID='953075';     --953075

--공통분모로 가져오고 싶은 데이터를 가져온다

SELECT 

A.USERID,USERNAME,AGE,JOB, --해당테이블이 다른테이블이 없을경우 CUSTOM 생략가능 

B.USERID,DEPT,POSIT,PAY --BUT, USERID는 두곳에 존재하기 때문에 생략X

FROM CUSTOM A,COMPANY B

--TABLE 의 별칭은 A 짧게... --분자 --별칭을 만들면 무조건 위에 별칭으로 써줘야함 안쓰면 안됨!!!!

WHERE A.USERID = B.USERID; --공통분모

--EQUI JOIN --쉼표가 쉬우니까 더 많이씀//공통분모가 있어야함!!!!!!

SELECT

A.USERID,USERNAME,AGE,JOB,

B.USERID,DEPT,POSIT,PAY

FROM CUSTOM A, COMPANY B

WHERE A.USERID=B.USERID --AND A.USERID='953075'; --USERID는 공통분모 

--INNERJOIN(INNER JOIN의 조건은 ON)

SELECT

A.USERID,USERNAME,AGE,JOB,

B.USERID,DEPT,POSIT,PAY

FROM CUSTOM A INNER JOIN COMPANY B

ON A.USERID=B.USERID --AND A.USERID='953075';

WHERE A.USERID='953075'; --AND로 적어줘도 되고 WHERE문으로 줘도됨!

SELECT

A.USERID,USERNAME,AGE,JOB,

B.USERID,DEPT,POSIT,PAY

FROM CUSTOM A JOIN COMPANY B --INNER는 생략가능 

ON A.USERID=B.USERID;

--CUSTOM 테이블과 POINT 테이블에서 데이터 출력

SELECT * FROM CUSTOM;

SELECT * FROM POINT;

SELECT A.USERID,USERNAME,AGE,PRODUCT,LOGIN

FROM CUSTOM A, POINT B

WHERE A.USERID=B.USERID AND LOGIN>20;

SELECT A.USERID,USERNAME,AGE,PRODUCT,LOGIN

FROM CUSTOM A INNER JOIN POINT B

ON A.USERID=B.USERID --AND LOGIN>20;

WHERE LOGIN>20;

--외부조인(OUTER JOIN) 

-- 하나의 테이블을 고정시키고 다른테이블을 비교하여 확인작업하는 방법

SELECT COUNT(*) FROM CUSTOM; --494

SELECT COUNT(*) FROM COMPANY; --490

SELECT A.USERID,USERNAME,ADDR1,COMPANY,DEPT

FROM CUSTOM A,COMPANY B

WHERE A.USERID(+)= B.USERID; --COMPANY가 기준 

SELECT A.USERID,USERNAME,ADDR1,COMPANY,DEPT

FROM CUSTOM A,COMPANY B

WHERE A.USERID= B.USERID(+) AND COMPANY IS NULL; --COSTOM A 가 기준 

--표준

SELECT A.USERID,USERNAME,ADDR1,COMPANY,DEPT

FROM CUSTOM A RIGHT OUTER JOIN COMPANY B

ON A.USERID= B.USERID; --490

SELECT A.USERID,USERNAME,ADDR1,COMPANY,DEPT

FROM CUSTOM A LEFT OUTER JOIN COMPANY B

ON A.USERID= B.USERID; --494

SELECT * FROM CUSTOM; --494

SELECT * FROM COMPANY; --490

--CROSS JOIN(상호조인)

--많은 양의 데이터가 필요할때 

SELECT

A.USERID,USERNAME,AGE,JOB,

B.USERID,DEPT,POSIT,PAY

FROM CUSTOM A , COMPANY B;

--WHERE A.USERID=B.USERID --CUSTOM(494) * COMPANY(490) 곱한값이 출력됨

--496개가 USERID,USERNAME,AGE,JOB/490개의 뒷부분이 매치가 됨 

SELECT

A.USERID,USERNAME,AGE,JOB,

B.USERID,DEPT,POSIT,PAY

FROM CUSTOM A CROSS JOIN COMPANY B

WHERE A.USERID=B.USERID; --CROSS JOIN을 쓰고 WHERE로 쓰면 INNERJOIN으로 바뀜 

--SELF JOIN

--자기자신과의 조인

--동명이인을 검색

SELECT * FROM CUSTOM ORDER BY USERNAME;

SELECT DISTINCT A.*    --A.* A의 모든것을 가져오는거 --DISTINCT 중복되는거는 빼버리기!!!!!

FROM CUSTOM A, CUSTOM B    --CUSTOM 을 두개를 메모리상에 올려놔서 A B로 나누어서 동명이인찾음 

WHERE A.USERNAME = B.USERNAME 

AND A.USERID <> B.USERID --동명이인의 LIST만 출력 

ORDER BY A.USERNAME;

--공식임 --같은 컬럼에서 중복되는 데이터가 있는지 검색하는 쿼리 

--CUSTOM  테이블에서 제주도에 살고있는 사람중에 동명이인을 검색

SELECT * FROM CUSTOM;

SELECT A.*

FROM CUSTOM A,CUSTOM B 

WHERE A.USERID<>B.USERID

AND A.USERNAME=B.USERNAME

AND A.ADDR1='제주도'; AND B.ADDR1='제주도';

--3개 이상의 테이블 조인

SELECT A.USERID , USERNAME, COMPANY,DEPT,PRODUCT,LOGIN

FROM CUSTOM A,COMPANY B,POINT C

WHERE A.USERID = B.USERID AND A.USERID=C.USERID;

SELECT A.USERID , USERNAME, COMPANY,DEPT,PRODUCT,LOGIN

FROM CUSTOM A INNER JOIN COMPANY B

ON A.USERID = B.USERID INNER JOIN POINT C

ON A.USERID = C.USERID

AND A.USERID=C.USERID;

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

--파생테이블과 조인

SELECT * FROM SALES ORDER BY USERID;

SELECT A.USERID,USERNAME,AGE,ADDR1,NCOUNT,HAP

FROM CUSTOM A,

(SELECT USERID,COUNT(*) NCOUNT, SUM(PRICE) HAP

FROM SALES 

GROUP BY USERID --USERID가 몇번샀고 합계는 HAP!

HAVING SUM(PRICE)>2000000) B --여기까지가 테이블 B  

WHERE A.USERID = B.USERID; --파생컬럼인 B에도 USERID를.. 

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

--UNION ALL

SELECT * FROM TAB;

SELECT * FROM CUSTOM;

CREATE TABLE SEOUL

AS

SELECT * FROM CUSTOM  WHERE ADDR1='서울특별시';

SELECT * FROM SEOUL;

CREATE TABLE JEJU

AS

SELECT * FROM CUSTOM  WHERE ADDR1='제주도';

SELECT * FROM JEJU;

CREATE TABLE KKI

AS

SELECT * FROM CUSTOM  WHERE ADDR1='경기도';

SELECT * FROM KANGWON;

SELECT * FROM SEOUL;

SELECT * FROM JEJU;

SELECT * FROM KKI;

SELECT * FROM SEOUL

UNION

SELECT * FROM JEJU

UNION

SELECT * FROM KKI;

-------------------------------------------------------------------20190111

--SUBQUERY (하위쿼리)

SELECT USERID,COMPANY,DEPT,PAY,AVG(PAY) FROM COMPANY;

--다중과 AVG단일값이기 때문에 에러남 

GROUP BY USERID; --USERID는 묶어도 COMPANY ,DEPT,PAY는 단일값이기 때문에 

SELECT USERID,COMPANY,DEPT,PAY,1778763 평균 FROM COMPANY;

--내가 얻고자하는 데이터를 위해 1번이상의 쿼리로 실행하면 안됨, 무조건 한번!!!!!!

SELECT USERID,COMPANY,DEPT,PAY,

(SELECT ROUND(AVG(PAY)) FROM COMPANY) 평균 

FROM COMPANY; --쿼리안에 괄호안에 SELECT 문이 하나 더 들어가있는게 바로 쿼리 !!!

SELECT USERID,COMPANY,DEPT,PAY,

(SELECT ROUND(AVG(PAY)) FROM COMPANY) 평균 ,

(PAY-(SELECT ROUND(AVG(PAY)) FROM COMPANY)) 차액

FROM COMPANY;

--평균 나이보다 나이가 많은 사원의 개인정보 

SELECT AVG(AGE) FROM CUSTOM;

SELECT * FROM CUSTOM;

SELECT USERNAME,USERID,AGE, ADDR1 FROM CUSTOM 

WHERE AGE>(SELECT AVG(AGE) FROM CUSTOM); --하위쿼리 

--평균 월급보다 적은 월급을 받는 사원의 정보 

SELECT * FROM COMPANY;

SELECT * FROM COMPANY

WHERE PAY<(SELECT AVG(PAY) FROM COMPANY);

--현대자동차에 근무하는 사원의 고객정보를 검색

SELECT * FROM COMPANY;

SELECT USERID FROM COMPANY

WHERE COMPANY LIKE '현대자동차%';

SELECT * FROM CUSTOM 

WHERE USERID IN(

SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%');

--JOIN

SELECT * FROM COMPANY;

SELECT * FROM CUSTOM ;

SELECT A.*

FROM CUSTOM A, COMPANY B WHERE A.USERID=B.USERID

AND COMPANY LIKE '현대자동차%';

SELECT A.*

FROM CUSTOM A INNER JOIN COMPANY B 

ON A.USERID=B.USERID

WHERE COMPANY LIKE '현대자동차%';

--4회이상 물건을 구입한 고객의 정보

SELECT USERID,COUNT(*) NCOUNT FROM SALES

GROUP BY USERID

HAVING COUNT(*)>4;

SELECT * FROM CUSTOM 

WHERE USERID IN (

SELECT USERID NCOUNT FROM SALES

GROUP BY USERID

HAVING COUNT(*)>4);

SELECT * FROM CUSTOM 

WHERE USERID NOT IN (

SELECT USERID NCOUNT FROM SALES

GROUP BY USERID

HAVING COUNT(*)>4); --4회이상이 아닌 

SELECT A.* FROM CUSTOM A,

(SELECT USERID,COUNT(*) FROM SALES B 

GROUP BY USERID

HAVING COUNT(*)>=4) B

WHERE A.USERID=B.USERID;

--INLINE-VIEW

SELECT A.* ,NCOUNT

FROM CUSTOM A,

(SELECT USERID,COUNT(*) NCOUNT FROM SALES B 

GROUP BY USERID

HAVING COUNT(*)>=4) B

WHERE A.USERID=B.USERID;

--ANY, ALL

SELECT POINT FROM CUSTOM WHERE ADDR1='제주도' ORDER BY POINT; 

SELECT MIN(POINT) FROM CUSTOM WHERE ADDR1='제주도' ORDER BY POINT; 

SELECT MAX(POINT) FROM CUSTOM WHERE ADDR1='제주도' ORDER BY POINT; 

-->ANY 최소값보다 큰 데이터 

SELECT USERID, USERNAME, POINT FROM CUSTOM 

WHERE POINT > ANY (SELECT POINT FROM CUSTOM WHERE ADDR1='제주도'); --132보다 큰애들(SELECT MIN(POINT) FROM CUSTOM WHERE ADDR1='제주도' ORDER BY POINT; )

SELECT USERID, USERNAME, POINT FROM CUSTOM 

WHERE POINT > (SELECT MIN(POINT) FROM CUSTOM WHERE ADDR1='제주도');

--<ANY 최대값보다 작은 데이터

SELECT USERID,USERNAME,POINT FROM CUSTOM 

WHERE POINT < ANY(SELECT POINT FROM CUSTOM WHERE ADDR1='제주도'); --269보다 작은애들(SELECT MAX(POINT) FROM CUSTOM WHERE ADDR1='제주도' ORDER BY POINT;)

SELECT USERID,USERNAME,POINT FROM CUSTOM 

WHERE POINT < (SELECT MAX(POINT) FROM CUSTOM WHERE ADDR1='제주도'); --최댓값보다 작으니까 MAX로 쓸수있음 

-->ALL 최대값보다 큰 데이터

SELECT USERID,USERNAME,POINT FROM CUSTOM 

WHERE POINT > ALL(SELECT POINT FROM CUSTOM WHERE ADDR1='제주도');

SELECT USERID,USERNAME,POINT FROM CUSTOM 

WHERE POINT > (SELECT MAX(POINT) FROM CUSTOM WHERE ADDR1='제주도');

--<ALL 작은

SELECT USERID,USERNAME,POINT FROM CUSTOM 

WHERE POINT < ALL(SELECT POINT FROM CUSTOM WHERE ADDR1='제주도');

--ALL보다 작으면 안나옴 WHY? 최소값보다 작은경우 (최소값보다 작은데이터가 없으니까 안나옴)

 

SELECT USERID,USERNAME,POINT FROM CUSTOM 

WHERE POINT < (SELECT MIN(POINT) FROM CUSTOM WHERE ADDR1='제주도');

--MAX,MIN 보다 ANY,ALL이 속도가 훨씬 빨라서 자주 씀 

INSERT INTO CUSTOM (USERID,POINT) VALUES ('AAAA',90);

SELECT * FROM CUSTOM WHERE POINT=90;

DELETE CUSTOM WHERE POINT ='90';

COMMIT; --실제 DB에 반영 된거임 

--ANY 는 무조건 범위값이 포함되어있어야함 / ALL은 범위 값이 포함되어있으면 안됨

--ALL은 범위외부의 값을 출력함

--<ALL은 범위가 포함되지 않아서 최소값보다 작을수 밖에 없음 

         --소        대--

--ANY는 범위값 내부의 값을 출력함   -----||-->  <--||------

--ALL은 범위값 외부의 값을 출력함     <--||--------||-->

SELECT POINT FROM CUSTOM WHERE AGE>=70;

--하위쿼리의 결과가 없으면 전체 쿼리의 결과도 없다 

SELECT * FROM CUSTOM 

WHERE POINT > ANY (SELECT POINT FROM CUSTOM WHERE AGE >=70) ; --최소값보다 큰

--ALL은 하위쿼리의 결과가 없으면 모든 데이터를 출력한다 

SELECT * FROM CUSTOM 

WHERE POINT > ALL (SELECT POINT FROM CUSTOM WHERE AGE >=70) ;

SELECT * FROM CUSTOM 

WHERE USERID IN 

(SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%'); --현대 자동차에 근무하는 USERID만 검색 

SELECT * FROM CUSTOM 

WHERE USERID =ANY

(SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%'); --IN연산자와 똑같은게 =ANY ,ANY가 빠름

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

--NOT IN과 <>ALL은 같은 결과를 출력

SELECT * FROM CUSTOM 

WHERE USERID NOT IN

(SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%'); --479

SELECT * FROM CUSTOM 

WHERE USERID <> ANY

(SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%'); --494

SELECT * FROM CUSTOM 

WHERE USERID <> ALL

(SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%'); --479

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

--하위쿼리

--괄호안에 코딩을 실행하면 결과값이나옴

SELECT * FROM CUSTOM 

WHERE USERID IN 

(SELECT USERID FROM COMPANY WHERE COMPANY LIKE '현대자동차%'); --O

--상관쿼리

--괄호안에 코딩실행하면 결과값이 안나옴 

--WHY? CUSTOM.USERID 를 못찾기 때문에 

SELECT * FROM CUSTOM 

WHERE EXISTS (SELECT * FROM COMPANY 

WHERE USERID=CUSTOM.USERID AND COMPANY LIKE '현대자동차%'); --X

--NOT EXISTS

SELECT * FROM CUSTOM 

WHERE NOT EXISTS (SELECT * FROM COMPANY 

WHERE USERID=CUSTOM.USERID AND COMPANY LIKE '현대자동차%');

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

SELECT * FROM (SELECT * FROM CUSTOM WHERE ADDR1='경기도')K;

SELECT * FROM (SELECT * FROM CUSTOM WHERE ADDR1='경기도')K

WHERE AGE <= 20;

SELECT * FROM CUSTOM 

WHERE ADDR1='경기도' AND AGE<=20; --FROM 뒤에 하위쿼리를 쓰는경우가 의외로 많음

--ROWNUM(일렬번호)/무조건 시작값은 1 

SELECT ROWNUM,USERID,USERNAME FROM CUSTOM

WHERE ROWNUM >= 3 AND ROWNUM <=7; --X안나옴

SELECT * FROM(

SELECT ROWNUM RNUM,DATA.* FROM 

(SELECT USERID,USERNAME FROM CUSTOM) DATA) --묶어줘야됨//(SELECT~) 에서 내가 원하는 결과를 만들어내고 그결과로 또 SELECT해서 결과값을 만들어줌

WHERE RNUM>=3 AND RNUM < 7;  

--평균월급보다 많은 월급을 받는 사람들의 고객정보를 검색하시오

SELECT * FROM CUSTOM

WHERE USERID IN(SELECT USERID FROM COMPANY

WHERE PAY > (SELECT AVG(PAY) FROM COMPANY));

--SUBQUERY UPDATE

CREATE TABLE EXCUSTOM 

AS 

SELECT * FROM CUSTOM;

CREATE TABLE EXCOMPANY 

AS 

SELECT * FROM COMPANY;

SELECT * FROM EXCOMPANY;

--주소가 제주도인 고객의 월급을 500원 인상 

SELECT * FROM EXCOMPANY 

WHERE USERID IN

(SELECT USERID FROM EXCUSTOM WHERE ADDR1='제주도');

SELECT A.* 

FROM EXCOMPANY A, EXCUSTOM B 

WHERE A.USERID=B.USERID AND ADDR1='제주도';      --하위쿼리를 조인문으로 만든것 

UPDATE EXCOMPANY SET PAY=PAY+500

WHERE USERID IN

(SELECT USERID FROM EXCUSTOM WHERE ADDR1='제주도');

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

SELECT A.* 

FROM EXCOMPANY A, EXCUSTOM B 

WHERE A.USERID=B.USERID AND ADDR1='제주도'; --FROM 부터 조건문 

--MSSQL가능 (ORACLE안됨)

UPDATE EXCOMPANY SET PAY=PAY+500

FROM EXCOMPANY A, EXCUSTOM B

WHERE A.USERID=B.USERID AND ADDR1='제주도'; --MSSQL만 JOIN문으로 UPDATE가능 

--SUBQUERY 삭제 

CREATE TABLE EXSALES

AS 

SELECT * FROM SALES;

--EXSALES에서 판매 내역이 없는 고객정보를 삭제 

SELECT * FROM EXSALES;

SELECT * FROM CUSTOM;

SELECT * FROM EXCUSTOM 

WHERE USERID NOT IN(SELECT USERID FROM EXSALES);

DELETE EXCUSTOM 

WHERE USERID NOT IN (SELECT USERID FROM EXSALES);

ROLLBACK;

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

SELECT A.* 

FROM EXCOMPANY A,EXCUSTOM B 

WHERE A.USERID=B.USERID AND  ADDR1='제주도'; --제주도에 사는사람의 정보 

--MS-SQL만 실행

SELECT EXCOMPANY

FROM EXCUSTOM A,EXCUSTOM B 

WHERE A.USERID=B.USERID AND  ADDR1='제주도'; 

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

SELECT * FROM TAB;

SELECT * FROM SALES;

SELECT * FROM CUSTOM;

SELECT * FROM 사원;

----------------------------------------------------------------------------20190118

--PL/SQL

create table 사원

(사원번호 number(10),

사원명 varchar2(10),

부서번호 number(2),

직급 varchar2(10),

우편번호 char(7),

주소 varchar2(50),

전화번호 char(15),

급여 number(5),

커미션 number(5),

입사일 date,

성별 char(4),

사수번호 number(10)

);

insert into 사원 values(2001,'이순신',10,'부장','125-365','서울 용산구','02-985-1254',3500,100,'1980-12-01','남자',null);

insert into 사원 values(2002,'홍길동',10,'대리','354-865','서울 강남구','02-865-1254',4000,'','2000-01-25','남자',2004);

insert into 사원 values(2003,'성유리',20,'사원','587-456','부산 해운대구','051-256-9874',2500,100,'2002-05-24','여자',2002);

insert into 사원 values(2004,'옥주현',30,'과장','987-452','서울 강남구','02-33-6589',5000,'','1997-03-22','여자',2001);

insert into 사원 values(2005,'길건',10,'대리','123-322','서울 성동구','02-888-9564',3000,100,'1999-07-15','여자',2004);

insert into 사원 values(2006,'한지혜',20,'사원','154-762','서울 송파구','02-3369-9874',2000,'','2003-05-22','여자',2005);

insert into 사원 values(2007,'박솔미',30,'대리','367-985','서울 영등포구','02-451-2563',3000,100,'2006-01-25','여자',2004);

insert into 사원 values(2008,'이효리',40,'사원','552-126','서울 중구','02-447-3256',2000,'','2001-02-02','여자',2007);

COMMIT;

SELECT * FROM 사원;

DESC 사원;

SELECT * FROM CUSTOM;

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

DECLARE 

TYPE FIRSTTYPE IS RECORD --JAVA에 RECORD와같음 =FIRSTTYPE 

(A 사원.사원명%TYPE,B 사원.직급%TYPE,C 사원.급여%TYPE); --데이터타입을 자동을 가져올수있음 

CUS FIRSTTYPE; --CUS[A][B][C]

BEGIN 

SELECT 사원명,직급,급여 INTO CUS FROM 사원 WHERE 사원번호=2001; -->CUS에 집어넣을거

DBMS_OUTPUT.PUT_LINE('사원명  직급  급여');

DBMS_OUTPUT.PUT_LINE('--------------------');

DBMS_OUTPUT.PUT_LINE(CUS.A||'  '||CUS.B||'  '||TO_CHAR(CUS.C));

DBMS_OUTPUT.PUT_LINE('현재 질의한 사용자는 '||USER||' 입니다');

DBMS_OUTPUT.PUT_LINE('질의한 시각은 '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS'));

END;

--PROCEDURE

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

DECLARE 

SAWON_REC 사원%ROWTYPE; --사원번호~사수번호 까지의 행TYPE을 모두 읽어옴 / 12개가 자동으로 생성

BEGIN

SELECT * INTO SAWON_REC FROM 사원 WHERE 사원번호=2001;

DBMS_OUTPUT.PUT_LINE('사원번호: '||SAWON_REC.사원번호);

DBMS_OUTPUT.PUT_LINE('사원명: '||SAWON_REC.사원명 );

DBMS_OUTPUT.PUT_LINE('직급: '||SAWON_REC.직급);

DBMS_OUTPUT.PUT_LINE('급여: '||SAWON_REC.급여);

END;

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

SELECT * FROM 사원;

DECLARE 

TYPE SANAME_TABLE_TYPE IS TABLE OF 사원.사원명%TYPE

INDEX BY BINARY_INTEGER;

TYPE JIK_TABLE_TYPE IS TABLE OF 사원.직급%TYPE

INDEX BY BINARY_INTEGER;

SANAME_COL SANAME_TABLE_TYPE;

JIK_COL JIK_TABLE_TYPE;

I BINARY_INTEGER := 0;

BEGIN 

--대입연산자 :=

FOR K IN(SELECT 사원명,직급 FROM 사원) LOOP --()안을 실행하고 제일먼저 나온 값을 K에 넣고 작업처리...

I:=I+1;

SANAME_COL (I) := K.사원명 ;

JIK_COL(I) := K.직급;

END LOOP;

DBMS_OUTPUT.PUT_LINE('사원명   직급');

DBMS_OUTPUT.PUT_LINE('----------------');

FOR J IN 1..I LOOP --1부터 시작해서 I까지 

DBMS_OUTPUT.PUT_LINE(RPAD(SANAME_COL(J),12)||RPAD(JIK_COL(J),9)); --SANAME1번째꺼내와서 12자리 오른쪽 정렬/

END LOOP;

END; 

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

--저장 PROCEDURE

SELECT * FROM 사원;

DESC 사원;

CREATE OR REPLACE PROCEDURE CHANG_PAY

(V_SANO IN NUMBER, V_NEW_PAY NUMBER) --V_NEW_PAY 뒤 IN은 생략가능 

IS

--윗부분OR아랫부분에 변수 지정할수있음 /위에는 사용자가 선언하는 CHANG_PAY를 호출하는 매개변수 

--아랫부분은 내부에서 사용하는 BEGIN~END / 

BEGIN

UPDATE 사원 SET 급여 = V_NEW_PAY WHERE 사원번호=V_SANO;

--사용자가 CHANG_PAY 를 호출하면서 V_SANO 2001에 , V_NEW_PAY 3500을 업데이트 시키는거!!

--UPDATE면 트랜잭션! COMMIT!

COMMIT;

END CHANG_PAY; --CHANGE_PAY 생략가능 

SELECT * FROM 사원 WHERE 사원번호='2005';

EXECUTE CHANG_PAY(2005,3500);

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

--함수(내가 값을 주면 반드시 값을 되돌려줘야함 )

CREATE OR REPLACE FUNCTION F_TAX

(V_SANO NUMBER) --IN생략가능

RETURN NUMBER --;없고 / RETURN값 줘야함 

IS 

V_TAX NUMBER;

BEGIN 

SELECT ((급여*12)+NVL(커미션,0))*0.05 INTO V_TAX 

FROM 사원 WHERE 사원번호=V_SANO;

RETURN V_TAX;

END;

SELECT 사원번호,사원명,직급,급여,커미션,F_TAX(2001) 세금 FROM 사원

WHERE 사원번호='2001';

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

--PROCEDURE는 리턴값이 원래 없음 

--PROCEDURE는 주로 값을 받아서 출력만해줌/ 그래서 반환값이 있는것은 함수로 만들어줌 

--BUT 만들어줄수있음!

CREATE OR REPLACE PROCEDURE P_OUTEX

(V_SANO IN 사원.사원번호%TYPE, --IN은 외부에서 받을 매개변수

V_SANAME OUT 사원.사원명%TYPE, --OUT은 내보낼 값 / 하나에 값을 받으면 사원명 급여 커미션을 내보내는거

V_PAY OUT 사원.급여%TYPE,

V_COMM OUT 사원.커미션%TYPE)

IS 

BEGIN 

SELECT 사원명,급여,커미션 INTO V_SANAME,V_PAY,V_COMM

FROM 사원 WHERE 사원번호 =V_SANO;

END;

EXECUTE P_OUTEX(2001,:NAME,:PAY,:COMM);

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

CREATE OR REPLACE PROCEDURE ADD_ONE --V_TEL이 ADD_ONE의 값을 받아서 OUT을 통해서 나감 

(V_TEL IN OUT VARCHAR2)

IS

BEGIN 

V_TEL := SUBSTR(V_TEL,1,3)||'-'||SUBSTR(V_TEL,4);

END;

VARIABLE TEL VARCHAR2(15) 

EXEC :TEL :='2349876';

EXEC ADD_ONE(:TEL) --:TEL값을 받아서ADD_ONE을 처리하고 다시 :TEL로 넣어줘서 PRINT 

PRINT TEL;

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

SELECT * FROM CUSTOM;

--941010-2123458

CREATE OR REPLACE PROCEDURE P_JUMIN

(V_JUMIN IN OUT VARCHAR2)

IS

BEGIN

V_JUMIN := SUBSTR(V_JUMIN,1,2)||'년'||SUBSTR(V_JUMIN,3,2)||'월'||SUBSTR(V_JUMIN,5,2)||'일';

END;

VARIABLE BIRTH VARCHAR2(14) --'---...'갯수 14개 나옴 

EXEC :BIRTH :='941010-2123458';

EXEC P_JUMIN(:BIRTH)

PRINT BIRTH 생년월일;

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

--SCRIPT 

SET SERVEROUTPUT ON --그리드 결과값을 보여줌

SET VERIFY OFF --변수의 값이 이것으로 바꼈따는것을 안보여줌

ACCEPT ID PROMPT '검색할 아이디를 입력하세요: ';

DECLARE 

TYPE GOGAK IS RECORD 

(A CUSTOM.USERID%TYPE,

B CUSTOM.USERNAME%TYPE,

C NUMBER(12,2),

D NUMBER(5)); 

CUS GOGAK; --A,B,C,D의 값을 CUS에 넣어줌

BEGIN 

SELECT A.USERID,USERNAME,HAP,NCOUNT INTO CUS

FROM CUSTOM A, 

(SELECT USERID,SUM(PRICE) HAP,COUNT(*) NCOUNT

FROM SALES

GROUP BY USERID) B

WHERE A.USERID=B.USERID AND A.USERID='&ID';

DBMS_OUTPUT.PUT_LINE('사용자 아이디: '||CUS.A);

DBMS_OUTPUT.PUT_LINE('사용자 이  름: '||CUS.B);

DBMS_OUTPUT.PUT_LINE('전체 판매액: '||CUS.C);

DBMS_OUTPUT.PUT_LINE('판매 횟수: '||CUS.D);

END;

/

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

--1.고객정보를 입력받아 해당 고객이 지금까지 주문한 총 횟수 구하기

CREATE OR REPLACE PROCEDURE P_GOJUTOT

(V_GNO IN VARCHAR2)

IS

TOT NUMBER;

BEGIN 

SELECT COUNT(*) INTO TOT FROM SALES 

WHERE USERID=V_GNO ;

DBMS_OUTPUT.PUT_LINE(V_GNO||'번 고객님의 총 주문횟수는 '||TO_CHAR(TOT)||'번 입니다');

END;

EXECUTE P_GOJUTOT('og6675');

--결과: A001번 고객님의 총 주문횟수는 3번입니다 

--2.직책을 입력받아 그 직책의 급여의 총액,평균월급,인원수를 검색하기

CREATE OR REPLACE PROCEDURE SEARCHJIK 

(JIK IN VARCHAR2)

IS

A NUMBER:=0;

B NUMBER(12,2):=0;

C NUMBER := 0;

BEGIN

SELECT SUM(PAY),AVG(PAY),COUNT(*) INTO A,B,C 

FROM COMPANY WHERE POSIT=JIK;

DBMS_OUTPUT.PUT_LINE(JIK||'의 급여 총액: '||A||'원');

DBMS_OUTPUT.PUT_LINE(JIK||'의 평균 월급: '||B||'원');

DBMS_OUTPUT.PUT_LINE(JIK||'의 인원수 : '||C||'명');

END;

EXECUTE SEARCHJIK('과장');

--결과:

--과장의 급여총액: XXXX

--과장의 평균 월급: XXXX

--과장의 인원수 : XX명

--------------------------------------------------------------------------------20190121

--DML PROCEDURE(INSERT,DELETE,UPDATE)를 많이 사용함

SELECT * FROM TAB;

CREATE TABLE TEST 

(A NUMBER(10),

B NUMBER(10),

C DATE DEFAULT SYSDATE);

SELECT * FROM TEST;

DESC TEST;

CREATE OR REPLACE PROCEDURE P_EX

(FIRST IN NUMBER, SECOND IN NUMBER)

IS

BEGIN

INSERT INTO TEST(A,B) VALUES (FIRST,SECOND);

COMMIT;

END; --INSERT PROCEDURE 

EXECUTE P_EX(10,20);

SELECT * FROM TEST;

EXECUTE P_EX(20,30);

--만들어놓으면 간단한 명령어로 데이터를 입력할수있음/ 이게 INSERT PROCEDURE 

--CUSTOM 테이블에 INSERT 시키는 PROCEDURE (CUS_IN)

SELECT * FROM CUSTOM;

DESC CUSTOM;

SELECT * FROM CUSTOM WHERE USERID='AAA';

CREATE OR REPLACE PROCEDURE CUS_IN

(A  VARCHAR2,B  VARCHAR2,

C  VARCHAR2,D NUMBER,

E VARCHAR2,F VARCHAR2,

G VARCHAR2,H VARCHAR2,

I VARCHAR2,J VARCHAR2,

K VARCHAR2,L VARCHAR2,

M NUMBER)

IS BEGIN 

INSERT INTO CUSTOM

VALUES (A,B,C,D,E,F,G,H,I,J,K,L,M,SYSDATE); --날짜는 내가 등록하는게 아니기 때문에 SYSDATE 

COMMIT;

END;

EXECUTE CUS_IN('AAA','KIM',123,12,1,'123-456','서울','강남구','AAA','123-456','자영업','무직',123);

--CUSTOM 테이블에 UPDATE 시키는 PROCEDURE (CUS_UP)

SELECT * FROM CUSTOM;

DESC CUSTOM;

CREATE OR REPLACE PROCEDURE CUS_UP

(A  VARCHAR2,B  VARCHAR2,

C  VARCHAR2,D NUMBER,

E VARCHAR2,F VARCHAR2,

G VARCHAR2,H VARCHAR2,

I VARCHAR2,J VARCHAR2,

K VARCHAR2,L VARCHAR2,

M NUMBER)

IS BEGIN 

UPDATE CUSTOM 

SET USERNAME=B,JUMIN=C,AGE=D,SEX=E,ZIP=F,ADDR1=G,ADDR2=H,ADDR3=I,TEL=J,JOB=K,SCHOL=L,POINT=M

WHERE USERID=A;

COMMIT;

END;

EXECUTE CUS_UP('AAA','KIM1','123',12,1,'123-4561','서울1','강남구1','AAA1','123-456','자영업1','무직1',123);

--CUSTOM 테이블에 DELTE시키는 PROCEDURE (CUS_DEL)

SELECT * FROM CUSTOM;

DESC CUSTOM;

SELECT * FROM CUSTOM WHERE USERID='AAA';

CREATE OR REPLACE PROCEDURE CUS_DEL

(A  VARCHAR2)

IS BEGIN 

DELETE 

FROM CUSTOM 

WHERE USERID=A;

END;

EXECUTE CUS_DEL('AAA');

SELECT * FROM CUSTOM WHERE USERID='AAA';

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

--함수

--부피를 구하는 함수

CREATE OR REPLACE FUNCTION F_CUBVOL

(GILI IN NUMBER,POK IN NUMBER,NOPI IN NUMBER)

RETURN NUMBER

IS

BUPI NUMBER;

BEGIN

BUPI :=GILI*POK*NOPI;

RETURN BUPI;

END F_CUBVOL;

SELECT F_CUBVOL(4,7,8) FROM DUAL;

--입력받은 날짜의 말일 구하기

CREATE OR REPLACE FUNCTION F_MONLAST

(V_DATE DATE)

RETURN DATE 

IS

LASTDATE DATE;

BEGIN 

LASTDATE := (ADD_MONTHS(V_DATE,1)-TO_CHAR(V_DATE,'DD'));  --입력받은 날짜에 한달을 더하고 입력받은날짜에 일을 뺌

RETURN LASTDATE;

END;

SELECT F_MONLAST('2019-01-10') FROM DUAL; 

SELECT * FROM CUSTOM;

SELECT REGDATE , F_MONLAST(REGDATE) FROM CUSTOM; 

--이름을 입력받아 성을 뺀 이름만 반환

CREATE OR REPLACE FUNCTION F_NAME 

(USERNAME VARCHAR2) 

RETURN VARCHAR2 

IS 

NAME VARCHAR2(4) ;

BEGIN

NAME := SUBSTR(USERNAME,-2);

RETURN NAME;

END;

SELECT USERNAME,F_NAME(USERNAME) FROM CUSTOM;

SELECT F_NAME('ㅎㅎㅎㅎㅎㅎ') FROM DUAL;

--입사일을 입력받아 사원명,입사일,근무기간(6년2개월)을 출력

SELECT * FROM CUSTOM;

SELECT

FLOOR(MONTHS_BETWEEN (SYSDATE,'2002-06-01')/12) || '년'  ||  --199.66017211768219832735961768219832736

FLOOR(MOD(MONTHS_BETWEEN(SYSDATE,'2002-06-01'),12)) ||'개월' 근무기간 --12로 나눈 나머지 값을 구함

FROM DUAL;

SELECT * FROM CUSTOM;

CREATE OR REPLACE FUNCTION F_IPSAIL

(IPSAIL DATE )

RETURN VARCHAR2 --년,개월로 꺼내기 때문에 VARCHAR2!!!!!!!!!!

IS 

GUNDATE VARCHAR2(20);

BEGIN 

GUNDATE := (FLOOR(MONTHS_BETWEEN (SYSDATE,IPSAIL)/12) || '년'  || 

FLOOR(MOD(MONTHS_BETWEEN(SYSDATE,IPSAIL),12)) ||'개월' );

RETURN GUNDATE;

END;

SELECT  USERNAME,REGDATE,F_IPSAIL(REGDATE) FROM CUSTOM WHERE ADDR1 LIKE '제주%';

--주민번호를 입력했을때 성별을 반환

CREATE OR REPLACE FUNCTION F_GENDER 

(V_JUMIN IN VARCHAR2)

RETURN VARCHAR2

IS 

GENDER VARCHAR2(2);

BEGIN 

GENDER:=SUBSTR(V_JUMIN,8,1);

IF GENDER IN('1','3') THEN

GENDER:='남';

ELSE 

GENDER:='여';

END IF;

RETURN GENDER;

END;

SELECT USERNAME,JUMIN,F_GENDER(JUMIN) GENDER FROM CUSTOM;

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

--날짜에 년,월,일 을 더해서 계약 만료일 구하기

CREATE OR REPLACE FUNCTION F_GAEYAK

(V_DATE IN DATE ,Y IN NUMBER, M IN NUMBER,D IN NUMBER)

RETURN DATE 

IS

NALZA DATE;

BEGIN 

NALZA:=ADD_MONTHS(V_DATE,Y*12);

NALZA:=ADD_MONTHS(NALZA,M);

NALZA:=NALZA+D;

RETURN NALZA;

END;

SELECT USERNAME,REGDATE,F_GAEYAK(REGDATE,1,11,29) GAEYAK FROM CUSTOM;

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

CREATE OR REPLACE FUNCTION F_PAYGRADE

(V_PAY IN NUMBER)

RETURN VARCHAR2

IS 

RESULT VARCHAR2(20);

BEGIN

IF V_PAY>2500000 THEN 

RESULT :='A';

ELSIF V_PAY>2000000 THEN

RESULT :='B';

ELSIF V_PAY>1500000 THEN

RESULT :='C';

ELSIF V_PAY>1000000 THEN

RESULT :='D';

ELSE

RESULT := 'E';

END IF;

RETURN RESULT;

END;

SELECT USERID,POSIT,PAY,F_PAYGRADE(PAY)||'등급' PAYGRADE

FROM COMPANY WHERE USERID='XA9776';

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

--반복문(LOOP) 조건이 밑에 있으니까 DO ~WHILE이랑 똑같 

CREATE TABLE LOOP1

(NO NUMBER,

NAME VARCHAR2(6) DEFAULT '홍길동');

DECLARE

V_COUNT NUMBER(2):=1;

BEGIN 

LOOP

INSERT INTO LOOP1(NO) VALUES(V_COUNT);

V_COUNT:=V_COUNT+1;

EXIT WHEN V_COUNT>10;

END LOOP;

DBMS_OUTPUT.PUT_LINE('데이터 입력완료');

END;

SELECT * FROM LOOP1;

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

--반복문(FOR)

CREATE OR REPLACE PROCEDURE P_FOR

IS

BEGIN 

FOR I IN 21..30 LOOP

INSERT INTO LOOP1(NO) VALUES(I);

END LOOP;

END;

EXECUTE P_FOR;

SELECT * FROM LOOP1; --10번다음에 21번부터 30번까지 들어감 

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

CREATE OR REPLACE PROCEDURE BANBOK1

(V_LOWER NUMBER,V_UPPER NUMBER) --매개변수

IS 

V_COUNT NUMBER(10):=0;

V_OUTPUT NUMBER(10):=0; --초기화

BEGIN

FOR I IN V_LOWER..V_UPPER LOOP

V_COUNT := V_COUNT+1;

V_OUTPUT := I;

END LOOP;

DBMS_OUTPUT.PUT_LINE('최종값은'||TO_CHAR(V_OUTPUT)||'이고 총 반복 횟수는'||TO_CHAR(V_COUNT)||'이다');

END;

EXECUTE BANBOK1(1,10); --최종값은10이고 총 반복 횟수는10이다

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

--WHILE

CREATE OR REPLACE PROCEDURE BANBOK2

(V_LOWER NUMBER,V_UPPER NUMBER) --매개변수

IS 

V_COUNT NUMBER(10):=0;

V_OUTPUT NUMBER(10):=0; --초기화

BEGIN

V_OUTPUT := V_LOWER;

WHILE V_OUTPUT<V_UPPER LOOP

V_COUNT := V_COUNT+1;

V_OUTPUT := V_OUTPUT+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE('최종값은'||TO_CHAR(V_OUTPUT)||'이고 총 반복 횟수는'||TO_CHAR(V_COUNT)||'이다');

END;

EXECUTE BANBOK2(1,10); --최종값은10이고 총 반복 횟수는9이다

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

--사용자가 입력하는 수의 작은값에서 큰값까지의 합계

CREATE OR REPLACE PROCEDURE P_CAL

(V_START NUMBER , V_END NUMBER)

IS 

CNT NUMBER:=V_START;

TOT NUMBER:=0;

BEGIN 

--LOOP

-- LOOP

-- TOT:=TOT+CNT;

-- CNT:=CNT+1;

-- EXIT WHEN CNT>V_END;

-- END LOOP;

--FOR

-- FOR I IN V_START..V_END LOOP

-- TOT:=TOT+I;

-- END LOOP;

--WHILE

WHILE V_START<=V_END LOOP

TOT:=TOT+CNT;

CNT:=CNT+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_START)||'부터 '||TO_CHAR(V_END)||'까지의 합은 '||TO_CHAR(TOT)||'입니다');

END;

EXECUTE P_CAL(1,10); --1부터 100까지의 합은 5050입니다

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

--예외처리

SELECT * FROM 사원;

CREATE OR REPLACE PROCEDURE EXE_TEST

IS

SW_REC 사원%ROWTYPE;

BEGIN

SELECT * INTO SW_REC FROM 사원 WHERE 사원번호=2001;

DBMS_OUTPUT.PUT_LINE('데이터 검색 성공!!');

EXCEPTION 

WHEN TOO_MANY_ROWS THEN 

ROLLBACK; --에러 나면 다 취소~

DBMS_OUTPUT.PUT_LINE('데이터가 너무 많습니다!!');

WHEN NO_DATA_FOUND THEN 

ROLLBACK; 

DBMS_OUTPUT.PUT_LINE('데이터가 없습니다!!');

WHEN OTHERS THEN 

ROLLBACK; 

DBMS_OUTPUT.PUT_LINE('기타 에러입니다!!');

END;

EXECUTE EXE_TEST;

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

CREATE TABLE EMP2

(EMPNO NUMBER(4) PRIMARY KEY,

ENAME VARCHAR2(10),

DEPTNO NUMBER(2));

INSERT INTO EMP2 VALUES(1001,'배수지',10);

INSERT INTO EMP2 VALUES(1002,'김수지',20);

SELECT * FROM EMP2;

CREATE OR REPLACE PROCEDURE IN_EMP2

(EMPNO NUMBER,DEPTNO NUMBER)

IS

NAME VARCHAR2(10):='미지정';

BEGIN 

INSERT INTO EMP2 VALUES(EMPNO,NAME,DEPTNO);

COMMIT;

EXCEPTION 

WHEN DUP_VAL_ON_INDEX THEN

DBMS_OUTPUT.PUT_LINE('데이터중복!!');

END;

SELECT * FROM EMP2;

EXECUTE IN_EMP2(1003,20);--한번더 실행하면 데이터중복!

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

--사용자 정의 에러 

CREATE OR REPLACE PROCEDURE PAY_VALIDATE

(V_SANO 사원.사원번호%TYPE)

IS

NOT_ENOUGH_PAY EXCEPTION;

SW_REC 사원%ROWTYPE;

BEGIN

SELECT * INTO SW_REC  FROM 사원 WHERE 사원번호=V_SANO;

IF SW_REC.급여>=2000 THEN 

DBMS_OUTPUT.PUT_LINE('급여가 3000 많음!!');

ELSIF SW_REC.급여<2000 THEN 

RAISE NOT_ENOUGH_PAY;

ELSE 

DBMS_OUTPUT.PUT_LINE('급여가 적당함!!');

END IF;

EXCEPTION 

WHEN NOT_ENOUGH_PAY THEN 

RAISE_APPLICATION_ERROR(-20001,'급여가 충분하지 못함!!');

END;

SELECT * FROM 사원;

EXECUTE PAY_VALIDATE(2003);

EXECUTE PAY_VALIDATE(2002);--O

EXECUTE PAY_VALIDATE(2001);--O

--ERROR

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

SELECT 부서번호,사원명,직급,급여 FROM 사원

WHERE 부서번호=10;

--선언->OPEN->FETCH->CLOSE

--CURSOR

CREATE OR REPLACE PROCEDURE P_DEPTNO

(I_DEPTNO 사원.부서번호%TYPE) --DEPTNO는 WHERE 부서번호에 들어간다

IS

CURSOR CUR_DEPTNO

IS

SELECT 부서번호,사원명,직급,급여 FROM 사원

WHERE 부서번호 = I_DEPTNO; --SELECT 의 결과값이 CUR_DEPTNO 커서에 들어간다 

--WHERE 부서번호 = DEPTNO; //DEPTNO는 어떤 부서번호가 들어갈지 모른다

--출력하기용도

V_DEPTNO 사원.부서번호%TYPE;

V_SANAME 사원.사원명%TYPE;

--VARCHAR2(10); =사원.사원명%TYPE;

V_JIK 사원.직급%TYPE;

V_SAL 사원.급여%TYPE;

--SELECT 문이 결과가 4개라 4개작성

BEGIN

OPEN CUR_DEPTNO; --커서를 열어준다

DBMS_OUTPUT.PUT_LINE('부서번호  사원명  직급   급여');

DBMS_OUTPUT.PUT_LINE('--------------------------------');

--반목문실행

LOOP

FETCH CUR_DEPTNO INTO V_DEPTNO,V_SANAME,V_JIK,V_SAL;  --꺼내오는 것은 FETCH

EXIT WHEN CUR_DEPTNO%NOTFOUND;  --NOTFOUND 시스템변수 (없을 때) --커서에 데이터가 없을때

DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DEPTNO) || '  ' ||V_SANAME || '  '||V_JIK||'  '||TO_CHAR(V_SAL));

END LOOP;

DBMS_OUTPUT.PUT_LINE('--------------------------------');

DBMS_OUTPUT.PUT_LINE('전체 데이터 갯수:' || TO_CHAR(CUR_DEPTNO%ROWCOUNT)); --ROWCOUNT 시스템변수 30개데이터이면 --ROWCOUNT 30이 들어간다

CLOSE CUR_DEPTNO;

END;

EXECUTE P_DEPTNO(20);

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

SELECT * FROM 사원;

CREATE OR REPLACE PROCEDURE P_DEPTNO1

(DEPTNO 사원.부서번호%TYPE) --DEPTNO는 WHERE 부서번호에 들어간다

IS

CURSOR CUR_DEPTNO

IS

SELECT 부서번호,사원명,직급,급여 FROM 사원

WHERE 부서번호 =DEPTNO; --SELECT 의 결과값이 CUR_DEPTNO 커서에 들어간다 

--WHERE 부서번호 = DEPTNO; //DEPTNO는 어떤 부서번호가 들어갈지 모른다

V_CNT NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('부서번호  사원명  직급   급여');

DBMS_OUTPUT.PUT_LINE('--------------------------------');

--반목문실행(FOR문 / OPEN,FETCH,CLOSE)

FOR CURDEPT IN CUR_DEPTNO LOOP  

--FOR문은 커서 OPEN할필요없음 / FOR문은 OPEN도하면서 FETCH도함 / 

--반복문이 끝나면 알아서 CLOSE 도 시켜줌

DBMS_OUTPUT.PUT_LINE(TO_CHAR(CURDEPT.부서번호) || '  ' ||CURDEPT.사원명 || '  '||CURDEPT.직급||'  '||TO_CHAR(CURDEPT.급여));

V_CNT:=CUR_DEPTNO%ROWCOUNT ; --FOR문을 벗어나면 시스템변수를 사용할수 없기때문에 미리 .. 

END LOOP;

DBMS_OUTPUT.PUT_LINE('--------------------------------');

DBMS_OUTPUT.PUT_LINE('전체 데이터 갯수:' || TO_CHAR(V_CNT));

--ROWCOUNT 시스템변수 30개데이터이면 --ROWCOUNT 30이 들어간다

END;

EXECUTE P_DEPTNO1(10);

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

--사원번호 (USERID)를 입력시 아이디,이름 구입한 제품명을 출력

SELECT * FROM CUSTOM;

DESC CUSTOM;

SELECT * FROM SALES;

CREATE OR REPLACE PROCEDURE P_JEPUM

(NAME CUSTOM.USERID%TYPE)IS

     CURSOR CUR_USERID 

     IS 

     SELECT A.USERID,USERNAME,PRODUCTNAME FROM CUSTOM A,SALES B 

     WHERE A.USERID=B.USERID AND A.USERID=NAME;

V_CNT NUMBER;

BEGIN 

DBMS_OUTPUT.PUT_LINE('아이디   이름    제품명');

DBMS_OUTPUT.PUT_LINE('--------------------------');

FOR CURNO IN CUR_USERID LOOP

DBMS_OUTPUT.PUT_LINE(TO_CHAR(CURNO.USERID)||'  '||CURNO.USERNAME||'  '||CURNO.PRODUCTNAME);

V_CNT:=CUR_USERID%ROWCOUNT;

END LOOP;

DBMS_OUTPUT.PUT_LINE('--------------------------------');

DBMS_OUTPUT.PUT_LINE('전체 데이터의 갯수: '||TO_CHAR(V_CNT));

END;

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

EXECUTE P_JEPUM(841883);

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

--사원번호를 입력시 사수번호,사수명,사원번호,사원명을 출력

SELECT * FROM 사원;

CREATE OR REPLACE PROCEDURE P_SANO

(USERID IN NUMBER)IS 

CURSOR CUR_ID

IS

SELECT A.사수번호,B.사원명 사수명,A.사원번호,A.사원명 FROM 사원 A, 사원 B

WHERE A.사수번호=B.사원번호 AND USERID=A.사수번호 ;

BEGIN

DBMS_OUTPUT.PUT_LINE('사수번호   사수명   사원번호   사원명');

DBMS_OUTPUT.PUT_LINE('-------------------------------------------');

FOR CUR2 IN CUR_ID LOOP

DBMS_OUTPUT.PUT_LINE(CUR2.사수번호||'       '||CUR2.사수명||'       '||CUR2.사원번호||'       '||CUR2.사원명);

END LOOP;

DBMS_OUTPUT.PUT_LINE('-------------------------------------------');

END;

EXECUTE P_SANO(2004);

----------------------------------------------------------------------------20190122

--PACKAGE

--프로시져의 묶음

--PACKAGE + BODY 

CREATE OR REPLACE PACKAGE SAWON_PACK

IS

PROCEDURE SANO_OUT;

PROCEDURE SANAME_OUT;

PROCEDURE BUNO_OUT;

PROCEDURE JIK_OUT;

PROCEDURE PAY_OUT;

END;

CREATE OR REPLACE PACKAGE BODY SAWON_PACK

IS

CURSOR SA_CUR

IS

SELECT * FROM 사원;

PROCEDURE SANO_OUT 

IS 

BEGIN 

DBMS_OUTPUT.PUT_LINE('사원번호');

DBMS_OUTPUT.PUT_LINE('---------');

     FOR SW_REC IN SA_CUR LOOP 

DBMS_OUTPUT.PUT_LINE(SW_REC.사원번호);

END LOOP;

END;    --기본구조

PROCEDURE SANAME_OUT

IS 

BEGIN 

DBMS_OUTPUT.PUT_LINE('사원명');

DBMS_OUTPUT.PUT_LINE('---------');

     FOR SW_REC IN SA_CUR LOOP 

DBMS_OUTPUT.PUT_LINE(SW_REC.사원명);

END LOOP;

END; 

PROCEDURE BUNO_OUT

IS 

BEGIN 

DBMS_OUTPUT.PUT_LINE('부서번호');

DBMS_OUTPUT.PUT_LINE('---------');

     FOR SW_REC IN SA_CUR LOOP 

DBMS_OUTPUT.PUT_LINE(SW_REC.부서번호);

END LOOP;

END;  

PROCEDURE JIK_OUT

IS 

BEGIN 

DBMS_OUTPUT.PUT_LINE('직급');

DBMS_OUTPUT.PUT_LINE('---------');

     FOR SW_REC IN SA_CUR LOOP 

DBMS_OUTPUT.PUT_LINE(SW_REC.직급);

END LOOP;

END;  

PROCEDURE PAY_OUT

IS 

BEGIN 

DBMS_OUTPUT.PUT_LINE('급여');

DBMS_OUTPUT.PUT_LINE('---------');

     FOR SW_REC IN SA_CUR LOOP 

DBMS_OUTPUT.PUT_LINE(SW_REC.급여);

END LOOP;

END;  

END SAWON_PACK;

EXECUTE SAWON_PACK.SANO_OUT;

EXECUTE SAWON_PACK.SANAME_OUT;

EXECUTE SAWON_PACK.BUNO_OUT;

EXECUTE SAWON_PACK.JIK_OUT;

EXECUTE SAWON_PACK.PAY_OUT;

--TRIGGER : 연쇄 작용 

--AFTER TRIGGER 

--BEFORE GRIGGER 

/*

INSERT ,UPDATE,DELETE 에 한해서 발동

사용자가 I,U,D 실행한뒤에 TRIGGER하는게 AFTER TRIGGER 

사용자가 데이터를 넣기전에 STOP 시키고 어떠한 작업을 실행시킨다음에 넣을려는 데이터가 조건에 맞는지 확인후 넣는것이 BEFORE TRIGGER

*/

CREATE TABLE HAKSANG 

(HAKBUN VARCHAR2(10),

NAME VARCHAR2(10));

CREATE TABLE MEMO 

(NO NUMBER,

BIGO VARCHAR2(200));

CREATE SEQUENCE SE99

START WITH 1

INCREMENT BY 1

NOMAXVALUE 

NOCYCLE 

NOCACHE;

CREATE OR REPLACE TRIGGER TRI_HAKSANG_IN

AFTER INSERT 

ON LEE.HAKSANG                --소유권을 명시하는 TABLE을 써줘야함 //학생TABLE에 INSERT하면 TRIGGER는 자동실행

FOR EACH ROW

BEGIN 

INSERT INTO LEE.MEMO VALUES (LEE.SE99.NEXTVAL,:NEW.HAKBUN||'행이 INSERT 되었습니다');  --:NEW.NAME 을 실행할려면 FOR EACH ROW 꼭 써줘야함

END;

SELECT * FROM HAKSANG; 

SELECT * FROM MEMO;  

INSERT INTO HAKSANG VALUES(111,'SUZI');  --INSERT가 되면 / MEMO에 행이 INSERT되었습니다 라고 출력됨

INSERT INTO HAKSANG VALUES(222,'SHIN');  --실패해서 MEMO가 4 .. 

INSERT INTO HAKSANG VALUES(333,'SHIN5'); --MEMO 5

INSERT INTO HAKSANG VALUES(555,'SHIN7');

CREATE TABLE COPY_HAKSANG 

(HAKBUN VARCHAR2(10),

NAME VARCHAR2(10));

CREATE OR REPLACE TRIGGER TRI_COPY_HAKSANG_IN

AFTER INSERT 

ON LEE.HAKSANG                

FOR EACH ROW

BEGIN 

INSERT INTO LEE.COPY_HAKSANG  VALUES (:NEW.HAKBUN,:NEW.NAME); 

END;

SELECT * FROM HAKSANG;

SELECT * FROM MEMO;

SELECT * FROM COPY_HAKSANG;

INSERT INTO HAKSANG VALUES (888,'LOO');

--BACKUP할수도 있음 / COPY_HAKSANG TABLE에 복사가됨

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

CREATE OR REPLACE TRIGGER TRI_HAKSANG_UP

AFTER UPDATE OF NAME --이름이라는 컬럼이 UPDATE가 되었을때 작업할거임!

ON LEE.HAKSANG                

FOR EACH ROW

BEGIN 

INSERT INTO LEE.MEMO  VALUES (LEE.SE99.NEXTVAL,:OLD.NAME||'행이 UPDATE 되었습니다'); 

--EX)이름이 SUZI라는애가 있는데 KIM으로 UPDATE를 할려면 KIM은 NEW , SUZI는 OLD 

END;

UPDATE HAKSANG SET NAME='AAA' WHERE HAKBUN=111;

SELECT * FROM HAKSANG;

SELECT * FROM MEMO;

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

CREATE OR REPLACE TRIGGER TRI_HAKSANG_DEL

AFTER DELETE 

ON LEE.HAKSANG                

FOR EACH ROW

BEGIN 

INSERT INTO LEE.MEMO  VALUES (LEE.SE99.NEXTVAL,:OLD.NAME||'행이 DELETE 되었습니다');

INSERT INTO LEE.COPY_HAKSANG  VALUES (:OLD.HAKBUN,:OLD.NAME);  

END;

SELECT * FROM HAKSANG;

SELECT * FROM MEMO;

SELECT * FROM COPY_HAKSANG;    --삭제된 데이터가 그대로 들어가있음 

DELETE HAKSANG WHERE HAKBUN='111';

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

--BEFORE TRIGGER

CREATE TABLE INSA

(EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JIK VARCHAR2(10),

SAL NUMBER,

CONSTRAINT PK_INSA_EMPNO PRIMARY KEY(EMPNO));

CREATE OR REPLACE TRIGGER TRI_INSA 

BEFORE INSERT OR DELETE OR UPDATE      

ON LEE.INSA

DECLARE 

IMPOSSIBLE_TIME EXCEPTION; --사용자정의 에러 

BEGIN 

IF TO_CHAR(SYSDATE,'DAY') IN ('화요일','토요일','일요일') OR --해당요일엔 데이터를 입력 못하게

TO_CHAR(SYSDATE,'HH24') <12 OR TO_CHAR(SYSDATE,'HH24') >17 THEN 

RAISE IMPOSSIBLE_TIME; --에러발생시킬거

END IF;

EXCEPTION

WHEN IMPOSSIBLE_TIME THEN 

RAISE_APPLICATION_ERROR(-20007,'월,수,목,금요일중 정요 12:00부터 오후 5시까지만 DML문장이 가능합니다');

END;

INSERT INTO INSA VALUES(1,'배수지','과장',5000); 

-- 월,수,목,금요일중 정요 12:00부터 오후 5시까지만 DML문장이 가능합니다 //현재 화요일 오전 11:19분

--같은 작업을하는 트리거가 2(A,B)개 있으면 A가 항상먼저 실행

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

CREATE TABLE MEMBER

(ID NUMBER(3) PRIMARY KEY,

NAME VARCHAR2(10),

JUBUN VARCHAR2(13));

SELECT * FROM MEMBER;

CREATE OR REPLACE TRIGGER TRI_MEMBER_JUBUN

BEFORE INSERT OR UPDATE OF JUBUN   --데이터가 입력될때나,JUBUN컬럼을 업데이트 시킬때 체크

ON LEE.MEMBER 

FOR EACH ROW 

DECLARE

A NUMBER;B NUMBER;C NUMBER;D NUMBER;E NUMBER;

F NUMBER;G NUMBER;H NUMBER;I NUMBER;J NUMBER;

K NUMBER;L NUMBER;M NUMBER;

CK NUMBER;

JUBUNFAIL EXCEPTION;

BEGIN

A:=TO_CHAR(SUBSTR(:NEW.JUBUN,1,1));

B:=TO_CHAR(SUBSTR(:NEW.JUBUN,2,1));

C:=TO_CHAR(SUBSTR(:NEW.JUBUN,3,1));

D:=TO_CHAR(SUBSTR(:NEW.JUBUN,4,1));

E:=TO_CHAR(SUBSTR(:NEW.JUBUN,5,1));

F:=TO_CHAR(SUBSTR(:NEW.JUBUN,6,1));

G:=TO_CHAR(SUBSTR(:NEW.JUBUN,7,1));

H:=TO_CHAR(SUBSTR(:NEW.JUBUN,8,1));

I:=TO_CHAR(SUBSTR(:NEW.JUBUN,9,1));

J:=TO_CHAR(SUBSTR(:NEW.JUBUN,10,1));

K:=TO_CHAR(SUBSTR(:NEW.JUBUN,11,1));

L:=TO_CHAR(SUBSTR(:NEW.JUBUN,12,1));

M:=TO_CHAR(SUBSTR(:NEW.JUBUN,13,1));

CK:=(A*2)+(B*3)+(C*4)+(D*5)+(E*6)+(F*7)+(G*8)+(H*9)+(I*2)+(J*3)+(K*4)+(L*5);  --주민번호에 숨겨져있는 비트를 곱한거 

CK:=MOD(CK,11); --CK를 11로 나눈 나머지

CK:=11-CK;

CK:=MOD(CK,10);

IF M!=CK THEN 

RAISE JUBUNFAIL;

END IF; 

EXCEPTION 

WHEN JUBUNFAIL THEN 

RAISE_APPLICATION_ERROR(-20001,'주민번호가 틀립니다');

END;

SELECT * FROM MEMBER;

DESC MEMBER;

INSERT INTO MEMBER VALUES(111,'SUZI','1111111111118');

INSERT INTO MEMBER VALUES(111,'SUZI','1111112345678'); --ORA-20001: 주민번호가 틀립니다

SELECT * FROM CUSTOM;