[PL/SQL] PL/SQL기초

PL/SQL

Oracle’s Procedural Language extension to SQL의 약자가 PL/SQL이다.

PL/SQL은 오라클 데이터베이스에서 프로그래밍을 위해 사용되는 절차적인 언어다.

보통 SQL에서는 if문 또는 for(반복문)을 사용할수 없는데 PL/SQL에서는 가능하다.

절차적 프로그래밍

PL/SQL은 절차적 프로그래밍 언어다.

절차적 언어의 제어구조를 사용한 프로그램이 가능하다.

• 변수, 상수 등을 선언하여 사용한다.

• if문을 사용하여 조건에 따라 문들을 분기한다.

• loop문을 사용하여 일련의 문을 반복적으로 실행한다.

프로시저, 함수, 피키지, 등의 서브루틴을 만들고 호출하여 코드를 재사용할 수 있다.

-- PL/SQL은 함수와 메서드가 공존하고 있다. 
set serveroutput on;

declare
-- 스칼라 변수
    mesage varchar2(30) := '안녕 PL/SQL';  -- 변수명  타입(길이) 할당한다  '안녕 PL/SQL'이 문자열을 

-- 코드블록{}
begin
    dbms_output.put_line('안녕');  -- 객체가 됨 put_line 이건 자바에서 System.out.print() 같은 존재
    dbms_output.put_line(mesage);
end;


begin -- 코드블록을 시작하낟.
    dbms_output.put_line('안녕 PL/SQL'); --프로시저를 호출하고 () 안의 내용을 화면에 출력한다.
end;

--anonymous 블록으로 변수를 추기화하여 출력한다.
declare
    message varchar2(30) := 'PL/SQL'; -- 문자변수를 선언하고 초기화 한다.
begin
    dbms_output.put_line(message); -- 프로시저를 호출하고 퍼렌씨시스 안의 내용을 화면에 출력한다.
end;

변수의 선언

• 숫자 : number, integer, float

• 문자 : char, varchar2

• 날짜 : date

• 논리 : boolean(true, false)

스칼라 변수

단일 값을 가지는 변수의 데이터 타입을 직접 설정해주는 변수

래퍼런스 변수

래퍼런스 변수는 변수가 저장된 메모리 주소를 가리키는 변수

(변수의 값을 직접 복사하지 않고 변수가 저장된 메모리 주소를 참조하여 값을 사용할 수 있다.)

• %type : 개별컬럼에 맞춰 변수 선언
• %rowtype : 테이블의 모든 컬럼을 행으로 선언하기 위해 사용
  

declare
    minno number(4); -- 숫자변수를 선언한다.
    minname varchar2(12); -- 문자변수선언
begin
    minno := 1001; -- 숫자변수값 할당
    minname :='홍길동'; -- 문자벼숫값 할당
    dbms_output.put_line('사번  이름');-- 프로시저를 호출하고 ()안에 내용을 화면에 출력한다.
    dbms_output.put_line('-----  -----');-- 프로시저를 호출하고()내용을 화면에 출력
 dbms_output.put_line(''|| minno ||'   '|| minname);
end;

질의문

select…into from where문은 정보를 추출할 필요가 있을 때 SQL을 사용한다. (반드시 하나의 데이터 행만 추출 해야한다.) 다수의 행을 추출할때는 커서(cursor)를 사용해야한다.

선택문(if)

단일 if…then…end if 문

-- 사원테이블에서 특정 사원의 사원번호와 이름을 조회한다.(단일 if...then...end if 문)
declare
    minno emp.empno%type;
    minname emp.ename%type; --레퍼런스 변수를 선언한다.
begin
select empno, ename into minno, minname from emp -- select  into  from 절로 조회할 컬럼과 저장할 변수를 설정한다.
where ename = 'SMITH';-- 조건확인
    dbms_output.put_line('사번   이름');
    dbms_output.put_line('----  ----');
    dbms_output.put_line(''|| minno ||'  '|| minname);
end;

이중 if…then…else…if 문

--사원테이블에서 SMITH 사원의 연봉을 조회한다.
declare
    minemp emp%rowtype;
    minsal number(7,2);
    begin
        select * into minemp from emp
        where name ='SMITH';
        if minemp.comm is null then
            minsal :=minemp.sal*12;
        else
        minsal := minemp.sal**12+minemp.comm;
        end if;
        dbms_output.put_line('  사원   이름   연봉');
        dbms_output.put_line('-----   -----  -----');
        dbms_output.put_line(''||minemp.empno||'  '||minsal);
    end;

반복문(loop)

--1oop문 1부터 10까지 반복하여 호출하고 출력한다.
declare 
    num number := 1;
begin
    loop
        dbms_output.put_line(num);
        num := num +1;
     if num > 10 then
        exit;
    end if;
    end loop;
end;

바인드변수

외부에 출력하는 변수.

내장되어 있는걸 내가 특별한 변수 모양을 만들어서 동작할 수 있도록 해주는 것이 변수라고 한다.

XML에서 사용한 변수를 자바에서 사용하면 그 변수를 바인드변수라고한다( = 다른 개발환경에서 사용하는 변수를 같이 쓸수 있도록 한 것이 바인드변수이다.)

커서

전체조회할때는 커서(corsor),

개별조회할때는 in out사용한다.

다중행을 출력할수 있게하는걸 커서라고 한다. 인 모드, 아웃모드, 커서모드를 대신한다라고 수업때 말할거야 그러니 잘 알아라!

프로시저

프로시저: 쿼리를 하나의 함수처럼 실행하기 위한 쿼리 집합

파라미터 모드라고 하는데

인 모드, 아웃모드, 이럴 때

파라미터는 파라미터이지만 전달하는 역할을 말하는거구나 라고 알면 됨

ORM

오브젝트, 릴레이션, 매핑 (객체, 관계, 매핑)

객체와 데이터베이스의 관계를 매핑(연결)해주는 도구 직접테이블과 연결하겠다 = 구조가 PL/SQL구조와 같다.

스프링에서 연동하는 방식이 오알엠이다,

create table empcopy
as
select * from emp;


create or replace procedure del_all -- 크리에이트 프로시저 절로 del_all프로시저를 선언한다.
is
begin
    delete from empcopy; -- delete from 문으로 empcopy테이블에 저장된 모든 데이터를 삭제한다.
    commit;-- 트랜잭션 작업을 commit명령어로 완료한다.
end;

execute del_all; --execute 명령어로 del_all프로시저를 실행하여 empcopy테이블에 저장된 모든 데이터를 삭제한다.

select*from empcopy;


desc user_source; -- desc 명령어로 user_source 시스템 테이블의 구조를 조회한다.


select name, type, line, text from user_source; -- select .. from 문으로 user_source 시스템 테이블의 모든 컬럼을 조회한다.


create table emprecopy -- 복사테이블 설정하여 이름은 emprecopy로 할거야
as
select * from emp; -- emp, 테이블에서 *를 사용하여 전체를 조회한다.

select * from emprecopy;

--저장프로시저 생성
create or replace procedure del_ename(minename emprecopy.ename%type) -- create procedure 절로 del_ename 프로시저를 선언한다.
is 
begin
    delete from emprecopy -- 삭제할 emprecopy테이블을 delete..from절로 설정한다.
    where ename like minename; -- where 절로 삭제할 조건을 확인한다.
    commit; 
end;

execute del_ename('S%'); -- S로 시작하는 ename컬럼 값에 해달하는 값을 삭제한다.

select *from emprecopy;


-- 부서정보 조회
create or replace procedure dept_select ( -- create or replace procedure 절로 파라미터를 가진 dept_select 프로시저를 선언한다.
vdeptno in dept.deptno%type, -- in 모드로 dept 테이블의 deptno컬럼에 입력한 값을 vdeptno파라미터에 전달한다. 
vdname out dept.dname%type,-- out 모드로 dept테이블의 dname컬럼값을 받아와서 vdname 파라미터에서 호출한다.
vloc out dept.loc%type -- out 모드로 dept테이블의 loc 컬럼값을 받아와서 vloc파라미터에서 호출한다.
)
is
begin
    select dname, loc into vdname, vloc from dept
    where deptno = vdeptno;
end;


variable vdname varchar2(30);
variable vloc varchar2(20);
execute pt_select(40, :vdname, :vloc); -- 값이 안나왔는데 테이블 이름을 잘못적어서dept ->pt 
print vdname; -- 근데 답은 안나오고 컬럼 명이 나오는이유는 이것때문 프로시저가 실행된거가 아니라!
print vloc;


create or replace procedure dept_insert(
vdeptno in dept.deptno%type,
vdname in dept.dname%type,
vloc in dept.loc%type
)
is
begin
    insert into dept(deptno, dname, loc)
    values(vdeptno,vdname, vloc);
end;

execute dept_insert(50, '기획실', '서울');