[ Oracle DB ] 함수 |내장함수 | 집계함수 | 단일행 함수 | 변환함수 | 사용자 정의

 함수 (function) 

 1. 내장함수(=sql함수) 

 

   1)집계함수(=복수행함수) ->sum,avg,맥시멈,미니멈. ...등..

 

   2)단일행 함수

 

        -문자형(char, varchar2)함수

            :upper,lower,length,concat,substr, lpad, rpad, lterim,rtrim

 

        - 숫자형(number)함수

 

        - 날짜형(date)함수

            :sysdate

 

        -변환함수

                    to_date             to_number
            날짜   <------     문자    -------> 숫자
                     ------->               <------- 
                    to_char               to_char

           * 목적이 되는 쪽에 타입을 적으면 된다.

 

       - null 함수

          : 알려지지 않거나 누락되었을 때 사용한다. 

             -> 데이터에서 널값이 있으면 문제가 발생하기 쉽기 때문에 정확히 작성해야 한다.

 

        -decode함수, case문

          :절차적 x 집합적 사고 , oracle advanced 기능 

 

---

 2.사용자 정의 함수 

 

문자형 함수

show user
desc user_objects
select object_name, object_type
from user_objects;
desc user_tables
select table_name, tablespace_name
from user_tables;
desc user_constraints
select owner, constraint_name, 
constraint_type,table_name,search_condition,r_constraint_name
from user_constraints;

alter table emp --table name
add constraint pk_emp_empno primary key(empno); --프라이머리키를 추가 하는 것
alter table dept --table name
add constraint pk_dept_deptno primary key(deptno); --프라이머리키를 추가 하는 것

alter table emp
add constraint fk_emp_deptno
foreign key(deptno) references dept(deptno); --참조

desc user_tab_columns 
select table_name, column_name, data_type
from user_tab_columns
order by 1 asc,2 desc; --position number
--
desc user_cons_columns
select constraint_name, table_name, column_name
from user_cons_columns;


desc user_objects
select object_name,object_type
from user_objects;
select table_name
from user_tables;

show recyclebin --sql+명령어
purge recyclebin; -- 휴지통:영구적으로 버린다는 것.sql명령어

select owner, constraint_name, 
constraint_type,table_name,search_condition,r_constraint_name
from user_constraints;

select constraint_name, table_name,column_name
from user_cons_columns;

select b.constraint_name, b.table_name, b.column_name,
    a.constraint_type
from user_constraints a join user_cons_columns b --table명 사이사이를 다 join걸어줌.(defult = 내부조인, 아웃터 조인도 할 수 있음)
    on a.constraint_name=b.constraint_name --join 한 뒤 브릿지 되는 애, 즉 컨스트레이트 네임이 주가 되는 것. 테이블 이름을 앞에 써주어야 에러가 안나기 때문에 a,b로 간단히 표시해주는 것.
    
order by 2,3;    

select *
from emp;
desc emp
select * from dept;
select * from bonus;
select * from salgrade;

select upper('abc') from dual; --대문자로 바뀜
select length('abc') from dual;

select concat('abc','defg') from dual;
select 'abc'||'defg' from dual; --concat과 동일한 형태

select substr('abcedf',2,3) from dual;

select lpad('abcdef',10,'-') from dual; --padding같은 느낌(default공백으로 부족한 부분이 채워짐, 3번째 인자로 지정해 줄 수도 있음.)
--결국 오른쪽 정렬임
select rpad('abcdef',10) from dual; --padding같은 느낌(default공백으로 부족한 부분이 채워짐, 3번째 인자로 지정해 줄 수도 있음.)
--결국 왼쪽 정렬임
--줄일 때 즉 짜를 때는 뒤에서부터 없애기 때문에 별로 추천하지 않는 방법.

select length(rtrim('abcd       ')) from dual;
select ltrim('   accc') from dual;


select ename,sal 
from emp 
where upper(ename)='SMITH';

select empno,lpad(ename,10),'$'||rpad(sal,10) from emp 



--변환함수 
select sysdate from dual;
select to_char(sysdate,'mon') from dual; --mm, dd, day하면 월,일,요일 나옴.
--mm = 03나오고, mon = 3월이라고 나옴.

--아래 예제에서 '' 안에 들어있는 날짜는 => 문자형임
select to_char(to_date('80/12/24','rr/mm/dd'),'yyyy') from dual; --rr은 디폴트 , 50년보다 낮으면 2000년대 이상이면 1900년대로 인식
select to_char(to_date('80/12/24','yy/mm/dd'),'yyyy') from dual; --결과 2080,yy는 2000년대로 무조건 인식.


select to_char(to_date('10/3/21','yy/mm/dd'),'yy-mon-dd') from dual;
select to_char(sysdate,'dd-mon-yy') from dual;

select ename,hiredate,to_char(hiredate,'day')as "요일" from emp where to_char(hiredate,'day')='수요일'or  to_char(hiredate,'day')='금요일';
-- as 는 요일이라는 컬럼 명을 만들어 주는 것.
-- where to_char(hiredate,'day') in ('수요일','금요일');

총 예제 코드이니,  한 줄씩 실행해보며 실습해보도록 하자.

---

+ 총 코드

아래는 sys_user.sql이며 바로 위의 긴 문자형 함수 코드가 scott_user.sql 코드이다.

demo_scott.sql 파일은 아래 주소를 참고하면 된다.

2021.03.24 - [언어/Database] - [ Oracle ] scott 스키마 설치 & 제약 조건 설정& 제약조건 스키마 & 제약 조건 확인

select sysdate
from dual;

show user
show parameter nls

alter session set nls_date_format=
'yyyy/mm/dd hh:mi:ss';

desc dba_users
select username,password, account_status
from dba_users 
where username in ('SCOTT','HR');
alter user scott identified by tiger
account unlock;
conn scott/tiger
show user
desc v$instance
select instance_name, host_name, status
from v$instance;