SQL PL/SQL 프로시저 1_ 익명프로시저 실행문 / 변수, 컬럼타입

PL/SQL 

pl/sql은 오라클 데이터베이스에서 사용되는 프로시저형 프로그래밍언어로  SQL문 + 프로그래밍 언어 요소를 결합하여 
데이터베이스 내에서 데이터를 처리하거나 비즈니스 로직 실행, 트리거, 함수, 저장 프로시저, 패키지 등을 작성하는데 사용된다.

 

*비즈니스로직이란?

조직이나 기업이 자신들의 목적을 달성하기 위해서 수행하는 일련의 절차, 정책, 규칙, 계획 등을 말한다.

 

[비즈니스 로직 예시]
 주문 처리 로직

   -고객이 상품을 주문→ 접수 → 주문 검증 → 재고 확인 → 가격 계산 → 결제 처리

회원 가입 로직

  -새로운 회원의 가입요청 → 회원 정보 검증 → 중복 가입 방지 → 이메일 인증 → 가입 환영 메일 발송

 

이러한 절차들을 담은 비즈니스 로직들은 프로그래밍 언어로 구현되며, pl/sql과 같은 데이터베이스 저장 프로시저로 구현이 가능하다.

 

 

기본 PL/SQL 블록 구조

declare     <-- (선언부) 옵션(생략 가능)
begin       <-- (실행부) 필수(생략 불가능)
exception   <-- (예외 처리부) 옵션(생략 가능)
  . PL/SQL문이 실행되는 중에 예외가 발생하면 해결하기 위한 실행문을 기술한다.
end;        <-- (실행문 종료) 필수(생략 불가능)
/           <-- 필수(생략 불가능) 

 

PL/SQL 화면 출력하기

--PL/SQL--

--화면 출력--
set serveroutput on;
show serveroutput; --serveroutput 상태확인--

show serveroutput;

pl/sql의 결과를 스크립트 창에서 출력하려면 set serveroutput on;이라는 명령어를 통해 serveroutput의 상태를 on으로 바꿔주어야한다. serveroutput이 켜져있는지 꺼져있는지의 상태여부는 show serveroutput;의 명령어를 통해 알 수 있다.

 

 

PL/SQL 익명블록 사용하기

--익명블록 사용하기--
begin
dbms_output.put_line('Hello Oracle PL/SQL');
end;
/

begin
dbms_output.put_line('안녕 오라클!');
end;
/

익명 프로시저란 임시적인 블록으로 1회성으로 실행하는 블록 로직이다. 따로 이름으로 저장해두지 않았기때문에 출력하는 당시에만 사용이 가능하며 따로 저장할 수 없다. 실행문인 begin절에 출력하고자 하는 문구를 적어, 간단한 형태로 만드는 것이 특징이다.

 

 

PL/SQL 익명블록에 변수사용하기

--익명블록에 변수 사용--
declare --변수 선언절--
v_message varchar(30) := 'Hello Oracle PL/SQL';
begin
dbms_output.put_line(v_message);
end;
/

--실행문(begin)절에서 변수 값 할당--
declare
v_no number(4);
begin
v_no := 100;--declare절에서 변수 정의만하고 값은 실행문에서 넣어도 됨--
dbms_output.put_line('v_no: '||v_no); --||문자 연결연산자--
end;
/

변수는 변동이 가능한 값을 저장할 수 있는 공간으로, 프로시저에서 변수 선언절인 declare절에 변수를 선언한다. 필수는 아니지만 프로시저에서는 변수명 네이밍을 할때 주로 v_로 시작하는 변수병을 사용하며, 변수명 형식 :=값의 형태로 적어야한다.

declare절에서 변수의 값까지 함께 정의해도 되지만, 실행문인 begin절에서 변수의값을 할당해도 상관없다.

첫번째 쿼리문의 경우 변수안에 문장을 저장하고 출력문에서 그 변수자체를 출력한것이기때문에 변수안에 있는 문장이 출력된 것이고, 두번째 쿼리문의 경우 변수에 100이라는 숫자를 저장한 후, 변수를 출력하여 변수안에 저장된 100이라는 문자가 출력되도록 한것이다.

 

 

PL/SQL 익명블록에 변수사용하기_실습

--프로시저 실습--
declare
v_empno number(4);
v_ename varchar2(10);
begin
v_empno :=7369;
v_ename :='SMITH';
dbms_output.put_line('v_empno: '||v_empno);
--문자 연결함수 ||--
dbms_output.put_line('v_ename: '||v_ename);
end;
/

--변수값 변경가능--
declare
v_empno number(4) := 2024;
v_ename varchar2(10) := '홍길동';
--변수선언절인 declare절에서 값만 변경해주면 됨--
begin
dbms_output.put_line(v_empno);
dbms_output.put_line(v_ename);
end;
/

 

 

 

%type형식 사용하기

--%type형식 사용--
declare
v_emp dept.dname%type;
begin
select dname into v_emp from dept where deptno=30;
dbms_output.put_line('v_emp : '|| v_emp);
end;
/

변수를 선언할때 다른 테이블의 컬럼을 참조하는 경우에 사용하는 타입지정자 형식이다. %type의 경우 변수명 참조테이블명.컬럼명%type의 형태로 적으며, %type을 사용하는경우에는 실행문에 반드시 select into절을 사용하여 테이블의 값을 선언한 변수에 할당해야한다.

위의 쿼리문의 경우 dept테이블에서 부서번호가 30번인 부서의 이름을 v_emp변수에 할당하고 출력문을 통해 그 값을 출력하는 프로시저의 쿼리문이다.

 

 

%rowtype형식 사용하기

--%rowtype형식 사용--
declare
v_dept_row dept%rowtype;
begin
select deptno, dname, loc into v_dept_row from dept where deptno =20;
dbms_output.put_line(v_dept_row.deptno);
dbms_output.put_line(v_dept_row.dname);
dbms_output.put_line(v_dept_row.loc);
end;
/

select * from dept;

%rowtype은 여러행을 참조할때사용되는 타입형식지정자이다. %rowtype의 경우 변수명 테이블명%rowtype의 형태로 적으며,

select into절을 통해 값을 할당하며, 출력문에서 할당한 값을 표시하기 위해서는 변수명.컬럼명의 형태로 적는다.

위의 쿼리문의 경우 dept테이블에 있는 전체 행을 참조하는 %rowtype이다.

 

 

%type 과 %rowtype형식 같이 사용하기

--%type, %rowtype같이 사용하는 경우--
declare
v_deptno dept.deptno%type;
v_dept_row dept%rowtype;
begin
select deptno,dname,loc into v_dept_row from dept where deptno=10;
v_deptno := v_dept_row.deptno;
dbms_output.put_line(v_deptno);
dbms_output.put_line(v_dept_row.dname);
dbms_output.put_line(v_dept_row.loc);
end;
/

%type과 %rowtype을 함께 사용할 수도 있는데, 이 경우 declare절에서 똑같이 변수를 선언해주는데 %type의 값은 %rowtype에서 참조하여 가져온값을 %type을 사용한 변수의 값으로 재할당을 시켜주는 방식으로 사용한다.

 

 

컬럼 선택해서 가져오기

--컬럼 선택해서 가져오기--
declare
v_emp_row emp%rowtype;
begin
select empno, ename, deptno 
into v_emp_row.empno, v_emp_row.ename, v_emp_row.deptno 
--참조하는 테이블의 전체컬럼을  다 사용하지 않는다면--
--사용할 컬럼들을 따로 into절에 개수를 맞춰 적어주어야함--
from emp where empno=7788;
dbms_output.put_line('사원번호: '||v_emp_row.empno);
dbms_output.put_line('사원명: '||v_emp_row.ename);
dbms_output.put_line('부서번호 :'||v_emp_row.deptno);
end;
/

%rowtype을 사용하여 행을 참조하는데 전체 컬럼을 다 가져오는것이 아니라, 그중에서도 컬럼을 몇개만 골라오고싶다면 %rowtype으로 행을 참조하여 준다음에 select into절에 참조하고싶은 컬럼을 따로 정의해주어야한다. 이때 형식은 변수명.컬럼명의 형태로 지정하며, into절에서 정의한 컬럼만 사용이 가능하다.

 

---

 

학습일기

와..드디어 프로시저까지 왔다....역시 개어렵다. 프로시저...ㅠㅠ 쿼리문 적는데 오류가 몇번이나 난건지 셀수도 없지만..ㅋㅋㅋ

오류가 많이나는만큼 나중에는 그 실수를 반복하지 않도록 복습할 수 있으니 긍정적이게 생각해야겠다...🥲

내용이 어려운것도 있지만 %rowtype과 %type의 차이점도 헷갈려서 어려웠고 출력문 문장 자체에서 오타 때문에 오류가 가장많이 발생했다. 빨리 손에 익어야하는데...ㅎㅎ 그만큼 연습을 더해야겠지..ㅠㅠ 프로시저를 다른 sql구문처럼 사용하는 그날이 오기까지..ㅠㅠ!!