si0

예전 카페에서 폐지 공지가 와서 자료를 옮겨 놓습니다.

1. 테이블 설명 조회

column comments format a50
select table_name, comments from user_tab_comments where table_name like 'SOME%';

2. 컬럼설명 조회

column comments format a50
select table_name, column_name, comments from user_col_comments where table_name like 'SOME%';

3. 전체 테이블/컬럼 설명 조회

SELECT A.TABLE_NAME
, A.COMMENTS AS TB_DESC
, B.COLUMN_NAME
, B.COMMENTS AS COL_DESC
FROM USER_TAB_COMMENTS A 
, USER_COL_COMMENTS B

예전 카페에서 폐지 공지가 와서 자료를 옮겨 놓습니다.

CREATE OR REPLACE PROCEDURE test_proc

IS

--exception_name EXCEPTION;

chkyear_err EXCEPTION;

BEGIN

-- 예외 발생

-- RAISE exception_name;

IF(1==0) THEN

RAISE chkyear_err;

END IF;

DBMS_OUTPUT.PUT_LINE('정상처리 실행');

EXCEPTION

--예외처리

--WHEN exception_name THEN

--    statement;

WHEN chkyear_err THEN

DBMS_OUTPUT.PUT_LINE('예외처리 실행');

END test_proc

1. 시퀀스 작성 쿼리

CREATE SEQUENCE SEQUENCE_NAME

[    INCREMENT BY n  ]

[   START WITH  ]

[ { MAXVALUE n | NOMAXVALUE } ]

[ { MINVALUE n | NOMINVALUE } ]

[ { CYCLE | NOCYCLE } ]

[ { CACHE n | NOCACHE } ]

1. 집합 함수 (Aggregate Functions)

1) avg 함수

select avg(salary) from employees;

select employee_id, department_id, salary, 

RAOUND(AVG(salary) OVER (PARTITION BY department_id),0) "AvgByDeptid"

from employees

where department_id IN (10,20,30)

2) rank 함수

select rank(3000) whthin group(order by salary desc) "rank" from employees;

select emplyee_id, salary, rank() over (order by salary) "rank" from employees;

3) sum, min, max, count 함수

 2. 분석함수

1) first_value 함수

select employee_id, salary,

FIRST_VALUE(salary)

OVER(PARTITION BY department_id ORDER BY salary DESC)

"Highsal_Departmentid"

from employees;

2)count 함수

3) sum 함수