자기 개발하기

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE//프로시저명

IS exVar varchar2(100) := 'test111'; //변수명
BEGIN 

//실행할 query
     select cnt(*) from test1
COMMIT;   

END TEST_PROCEDURE;

SELECT * FROM

TabS

ORDER BY (CASE WHEN 맨위컬럼 = '1' THEN 1 ELSE 2 END), 등록일자 desc;

맨위컬럼이갈 구분값이 1일때 맨처음으로 등록하고나머지는 등록일자 desc순으로 정렬합니다11098765432

이런식으로 정렬됩니다.

- 오라클 JOB 등록하기

DECLARE X NUMBER; 
BEGIN SYS.DBMS_JOB.SUBMIT 
( 
JOB => X,  //자동입력됨 JOB넘버
WHAT => 'USER_ID.PROCEDURE_ID;' ,  //오라클 프로시져 이름
NEXT_DATE => SYSDATE + 1/24/60,  //1분뒤에 JOB실행 시작
INTERVAL => 'SYSDATE + 1/24',   //1시간마다 JOB 실행
NO_PARSE => FALSE 

); 
END;
COMMIT;

- JOB 목록 조회

select * from user_jobs;

insert 할 table A

select 할 table A_TEMP

insert into A

select * from A_TEMP

컬럼이 같은 테이블두개일시

모든데이터가 insert 됩니다. 

조건필요할시

where절로 아래 추가하면됩니다.

이것은 회원가입을 했을때는 del_flag 가 null 값이지만 회원 탈퇴를 한경우에는 del_flag가 1이 되는데

회원 탈퇴를 한 사람의 아이디를 다른사람이 사용하는것을 막게 하기 위해 사용한것이다.

회원 가입시 기존에 가입한 회원이 있는가를 주민번호로 보통 검색을 하게 되는데

이때 del_flag 의 조건을 걸어주어야 된다.

del_flag가 null 값인 것 = 회원가입되어 있음.

del_flag가  null 인 값 구하기

SELECT  *
FROM h_member
WHERE jumin1 = '811118'
   and del_flag is null

이렇게 하면  del_flag 가 null 인 값만을 구하게 된다.

del_flag가 null 이 아닌값을 구하려면  is null 이 아닌 is not null 을 사용하면 된다.

SELECT  *
FROM h_member
WHERE jumin1 = '811118'
   and del_flag is not null

출처:

https://m.blog.naver.com/PostView.nhn?blogId=hack1s&logNo=40070954005&proxyReferer=https%3A%2F%2Fwww.google.com%2F

◆ 숫자, 영문, 한글, NULL 순서

ORDERY BY 컬럼명

◆ 숫자, 한글, 영문 순서

ORDER BY CONVERT(컬럼명, 'VN8VN3')

◆ 한글, 영문, 숫자 순서

ORDER BY 컬럼명 DESC

ORDER BY CONVERT(컬럼명, 'US8ICL')

◆ 한글, 숫자, 영문 순서

ORDER BY CONVERT(컬럼명, 'ISO2022-KR')

◆ 영문, 한글, 숫자 순서

ORDER BY CONVERT(컬럼명, 'UTFE')

 ◆ 정렬 순서 정하기
ORDER BY DECODE(컬럼명,2,1,컬럼명), 컬럼명 desc

출처: https://dvlp.tistory.com/109 [DVLP]

 -- ORDER BY : 정렬  

  -- ASC : 오름차순(생략하면 오름차순)

  -- DESC : 내림차순

-- 오름차순 정렬

SELECT name, basicpay FROM insa ORDER BY basicpay;

SELECT name, basicpay FROM insa ORDER BY basicpay ASC;

-- 내림차순 정렬

SELECT name, basicpay FROM insa ORDER BY basicpay DESC;

--부서별 오름차순으로 정렬하고 부서가 같으면 직위 오름차순 정렬( ㄱ 부터 A 부터 1부터)

SELECT name, buseo,jikwi FROM insa ORDER BY  buseo, jikwi;

--부서별 내림차순으로 정렬하고 부서가 같으면 직위 오름차순 정렬(ㅎ 부터 Z 부터 9부터)

 SELECT name, buseo,jikwi FROM insa ORDER BY buseo DESC, jikwi;

 SELECT name, buseo,jikwi FROM insa ORDER BY buseo DESC, jikwi ASC;

 -- 다음은 오류가 발생한다  이유가 무엇이고 오류가 없도록 수정 할 것.

 SELECT name, basicpay,sudang,(basicpay + sudang) pay

 FROM insa

 WHERE pay >=2500000  -- WHERE절에는 pay라는 컬럼이 없다.

 ORDER BY pay DESC;

 => 컴파일 순서에 따라서 오류가난다

  SELECT name, basicpay,sudang,(basicpay + sudang) pay

 FROM insa

 WHERE (basicpay + sudang) >=2500000  -- WHERE절에는 pay라는 컬럼이 없다.

 ORDER BY pay DESC; --ORDER BY는 가장 마지막에 컴파일 되므로  pay나 (basicpay + sudang) 둘중 아무거나 써도 상관없다.

 -- city가 서울 사람중(basicpay + sudang) 내림차순으로 정렬하여 출력

   -- 출력 컬럼 : name, city, basicpay + sudang pay, buseo

   SELECT name, city,(basicpay+sudang) pay, buseo

   FROM insa

   WHERE city='서울'

   ORDER BY pay DESC;

 -- 여자 (ssn이 이용) 중 부서 오름차순으로 정렬하고 부서가 같으면 기본급 내림차순으로 정렬

    -- 출력 컬럼 : name, ssn,buseo, basicpay

    SELECT name, ssn, buseo, basicpay

    FROM insa

    WHERE SUBSTR(ssn, 8, 1) IN (2,4,6)

    ORDER BY buseo ASC, basicpay DESC

출처: https://tibang.tistory.com/entry/Oracle-ORDER-BY [T없이맑은날]

-- 테이블 생성

CREATE TABLE MY_DUAL AS(

    SELECT 1 ID, 2000 SALARY FROM DUAL UNION ALL

    SELECT 2 ID, 2200 SALARY FROM DUAL UNION ALL

    SELECT 3 ID, 2400 SALARY FROM DUAL UNION ALL

    SELECT 4 ID, 2600 SALARY FROM DUAL UNION ALL

    SELECT 5 ID, 2800 SALARY FROM DUAL UNION ALL

    SELECT 6 ID, 2600 SALARY FROM DUAL UNION ALL

    SELECT 7 ID, 3000 SALARY FROM DUAL UNION ALL

    SELECT 8 ID, 2600 SALARY FROM DUAL

);

-- 기존 테이블 컬럼만 복사해 새 테이블 생성

CREATE TABLE COPY_NEW_TABLE AS(

    SELECT * FROM EXIST_TABLE_NAME

    WHERE 1=2 -- FALSE

);

2. 컬럼 ADD, MODIFY, RENAME, DROP 사용하기 - 1

-- 1. 컬럼 ADD 

-- ALTER TABLE 테이블명 ADD 새 컬럼명 이름  데이터타입;

ALTER TABLE MY_DUAL ADD ANNUAL_INCOME VARCHAR2(1000);

UPDATE MY_DUAL SET ANNUAL_INCOME = SALARY;

-- 2. 컬럼 MODIFY 

-- ALTER TABLE 테이블 명 MODIFY 컬럼명 데이터타입;

ALTER TABLE MY_DUAL MODIFY SALARY VARCHAR2(1000);

-- 3. 컬럼 RENAME

-- ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명;

ALTER TABLE MY_DUAL RENAME COLUMN ANNUAL_INCOME TO SALARY;

-- 4. 컬럼 DROP

-- ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명;

ALTER TABLE MY_DUAL DROP COLUMN SALARY;

3. 컬럼에 데이터가 있을때 데이터 유형 바꾸는 방법 - 2

-- 컬럼에 데이터가 있을때 데이터 유형 바꾸는 방법

-- 1. 새 이름으로 새 컬럼을 하나 생성 (ADD)

ALTER TABLE MY_DUAL ADD ANNUAL_INCOME VARCHAR2(1000);

-- 2. 새 컬럼에 기존 데이터를 다시 입력 (SET)

UPDATE MY_DUAL SET ANNUAL_INCOME = SALARY;

-- 3. 기존 컬럼 삭제 (DROP)

ALTER TABLE MY_DUAL DROP COLUMN SALARY;

-- 4. 새 컬럼의 이름을 기존 컬럼 이름으로 바꿈 (RENAME)

ALTER TABLE MY_DUAL RENAME COLUMN ANNUAL_INCOME TO SALARY;

현재 SALARY 컬럼의 데이터 유형은 NUMBER 

SALARY 컬럼은 데이터를 갖고 있다.

데이터를 가지고있는 컬럼에 대한 데이터유형 변경은 허용하지 않는다.

그러므로 새 이름의 필요한 데이터유형의 새로운 컬럼을 하나 생성한 뒤 

기존 컬럼이 가지고 있는 데이터를 새로운 데이터유형의 컬럼에 넣어주고(이동)

기존 컬럼을 삭제(DROP) 하고 새 이름의 새컬럼 이름을 기존 컬럼의 이름으로 바꿔주면 된다.

출처 : https://shlee0882.tistory.com/54

LAG 함수

 - LAG 함수를 이용해 파티션별 윈도우에서 이전 몇번째 행의 값을 가져올 수 있다. SQL SERVER에서는 지원하지 않는다.

- EX) 직원들의 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력한다. 

 SELECT ENAME

           ,HIREDATE

           ,SAL

           ,LAG(SAL) OVER(ORDER BY HIREDATE) AS PREV_SAL

   FROM EMP

  WHERE JOB = 'SALESMAN'

    ENAME HIREDATE   SAL      PREV_SAL

1 ALLEN 1981-02-20 1600.00

2 WARD 1981-02-22 1250.00 1600

3 TURNER 1981-09-08 1500.00 1250

4 MARTIN 1981-09-28 1250.00 1500

    ENAME HIREDATE NEXT_SAL

1 SMITH 1980-12-17 1981-02-20

2 ALLEN 1981-02-20 1981-02-22

3 WARD 1981-02-22 1981-04-02

4 JONES 1981-04-02 1981-05-01

5 BLAKE 1981-05-01 1981-06-09

6 CLARK 1981-06-09 1981-09-08

7 TURNER 1981-09-08 1981-09-28

8 MARTIN 1981-09-28 1981-11-17

9 KING         1981-11-17 1981-12-03

10 JAMES 1981-12-03 1981-12-03

11 FORD         1981-12-03 1982-01-23

12 MILLER 1982-01-23 1987-07-13  

13 SCOTT 1987-07-13 1987-07-13

14 ADAMS 1987-07-13 9999-01-31

출처: https://crosstheline.tistory.com/106?category=693092 [이거 알아영???ㅎㅎㅎ]

oracle 정렬 순서 정하기 order by 순서정하기

◆ 숫자, 영문, 한글, NULL 순서

ORDERY BY 컬럼명

◆ 숫자, 한글, 영문 순서

ORDER BY CONVERT(컬럼명, 'VN8VN3')

◆ 한글, 영문, 숫자 순서

ORDER BY 컬럼명 DESC

ORDER BY CONVERT(컬럼명, 'US8ICL')

◆ 한글, 숫자, 영문 순서

ORDER BY CONVERT(컬럼명, 'ISO2022-KR')

◆ 영문, 한글, 숫자 순서

ORDER BY CONVERT(컬럼명, 'UTFE')

 ◆ 정렬 순서 정하기
ORDER BY DECODE(컬럼명,2,1,컬럼명), 컬럼명 desc
 

출처: http://dvlp.tistory.com/109 [DVLP]