7-SQL 기본문법 정리 (용어/프로시저/문자열함수)

1) 데이터베이스 용어

1. 자료 와 정보

: 자료를 가공하면 정보가 된다

 

2. 오라클에서 스키마란?

: 스키마=데이터베잉스=사용자 이름(=저장소)

 

3. 제약조건 5가지 한번 적어보세요

: primary key, foreign key, unique, check, default, not null 

(check는 사용가능한 곳도 있고 없는 곳도 있다)

 

4. view 의 특징

: 논리적으로 생성된 가상의 테이블이다. 보안성이 좋고 편리하다

 

5. 트랜젝션이란 ?

: 작업 처리의 단위 

 

6. DBA 는 어떤일을 하는 사람일까요?

: 데이터베이스 관리자 (계정관리, 장치관리, 백업 등을 한다. 프로그래밍에 약할 수 있음)

 

7. 트리거 는 어떤 일을 하는것일까요?

: 어떤 일을 실행할 때 자동으로 실행되는 것

 

8. F.K 에 대하여 설명하시오

: 다른 테이블의 컬럼을 참조하는 것

 

9. char(3) 과 varchar2(3) 의 차이를 설명하시오.

: char는 고정길이고 varchar는 가변길이다

 

10. null 이란 ?

: 0도 공백도 아닌 모르는 값, 연산불가

 

2) 스토어드 프로시저 

: CRUD 가능  (R:select)

 

1. Select 구문

 

--select 경우의 수 2가지

1. 리턴이 1개 : put_line

2. 리턴이 여러개 : pkg

 

--insert,update,delete는 리턴 안함

 

set SERVEROUTPUT ON; --결과가 출력되기 위한 설정

 

1)하나의 레코드값 출력하기

create or replace procedure sp_select1
as
var1 varchar2(10);
var2 varchar2(10);
var3 varchar2(10);
begin 
 select sno, sname, dept
 into var1, var2, var3
 from student
 where sno='100'; 
 DBMS_OUTPUT.put_line(var1||var2||var3); --한 라인만 출력 가능, 콘솔에 출력하겠다
end; 

execute sp_select1;

--출력: 100민수컴퓨터

 

2)조건을 받아 하나의 레코드값 출력하기

create or replace procedure sp_select2
(
strSno in varchar2 --입력변수
)
as
var1 varchar2(10); --출력변수
var2 varchar2(10);
var3 varchar2(10);
begin 
 select sno, sname, dept
 into var1, var2, var3
 from student
 where sno=strSno; --변수니까 sno='strSno'하면 안됨
 DBMS_OUTPUT.put_line(var1||var2||var3); 

 DBMS_OUTPUT.put_line('하나의 라인만 출력됩니다);
 DBMS_OUTPUT.put_line("출력성공!!!");
end; 

execute sp_select2('300');

--출력: 300정기태컴퓨터

          하나의 라인만 출력됩니다

          출력성공!!!

 

3)

create or replace procedure sp_select3
(
ret out pkg.t_ref,
strSno in varchar2 --입력변수
)
is
begin 
 open ret for
 select sno, sname, dept
 from student
 where sno=strSno; 
end; 

execute sp_select3('300');

--출력: 300정기태컴퓨터

 

2)랑 3)이랑 같음 

 

2. 패키지 만들기 ( 여러개의 레코드 실행 )

 

create or replace package pkg is
  type t_ref is ref cursor;
end;

 

여러개의 레코드 결과값을 보는 경우 실행 명령은 의미가 없다.

Execute() <--- 실행해도 의미가 없다. !!

​

4)

create or replace procedure sp_select4
(
ret out pkg.t_ref,  --아웃풋
strDept in varchar2 --입력변수, 인풋
)
is
begin 
 open ret for
 select sno, sname, dept
 from student
 where dept like strDept; 
end; 

--프로시저 실행 in값에 %컴%

 where dept like '%'||strDept||'%';    -- 파이프라인 오라클에서만 가능
end; 

--프로시저 실행 in값에 컴

--출력: 컴퓨터과 다 나옴

 

3. insert 구문

create or replace procedure sp_insert
(
 i_sno varchar2,
 i_sname varchar2,
 i_dept varchar2,
 i_year varchar2
)
is
begin 
 insert into student (sno, sname, dept, year)
values (i_sno, i_sname, i_dept, i_year);
end;

execute sp_insert('911','가나다','전산','4'); --값 넣고 끝, 리턴 안함

select * from student;

 

4. update 구문

 

create or replace procedure sp_update
(
 i_sno varchar2,
 i_sname varchar2
)
is
begin 
 update student  set sname=i_sname
 where sno=i_sno;
end;

execute sp_update('100','민수');

select * from student;

 

5. delete 구문

create or replace procedure sp_delete
(
 i_sno varchar2
)
is
begin 
 delete from student 
 where sno=i_sno;
end;

execute sp_delete('911');  --형 맞춰보기

select * from student;

 

 

3) 문자열 함수

 

1.조건 컬럼 추가

한글성별을 추가하시오
   select emp_no,
   substr(BIRTH_DATE,1,2)||'년'||substr(BIRTH_DATE,4,2)||'월'||substr(BIRTH_DATE,7,2)||'일' as "생년 월일",
   first_name,
   last_name,
   gender,
   case 
    when gender='F' then '여성'
    when gender='M' then '남성'
    else '기타'
   end as "성별",
   hire_date
   from test002;

 

2.translate

  select 성별, translate(count(성별),'025','공이오') 인원수 from v_bigdata1
   group by 성별;

  --1:1로 매칭해서 변환해줌

 

3. 길이

   select first_name 이름 ,length(first_name) 길이 from v_bigdata1;

 

4. 대문자, 글자열 맞추기
   select Rpad(upper(first_name),20,'#') 이름 ,length(first_name) 길이 from v_bigdata1
   order by 길이 desc; --Rpad:#을 오른쪽에 작성, Lpad

 

5. trim(공백 지우기)
    select Ltrim('  1234  ') 값1, trim(  '1234'  ) 값2, Rtrim('  1234  ') 값3 from dual;

 

6. 올림 버림 반올림
   
   select ceil (4.1), ceil(4.5), ceil(4.0)
   from dual; --5 5 4 / 4보다 크면 무조건 올림
    
   select floor (4.1), floor(4.5), floor(4.0)
   from dual; --4 4 4
   
   select round(4.7), round(4.5), round(4.1)
   from dual;  --5 5 4