PL/SQL
프로그래밍이란 : 해야 할 일을 미리 기술해 놓은 것이다. (프로그래밍이라느건 컴퓨터만 있는게 아니다.)
인간의 언어 -> 프로그래밍 언어 -> 기계어
간단히 말하면 : 인간의 언어를 수학적 기호가 많이 포함된 프로그래밍 언어로 바꾸는 것이라고 생각하면된다.
우리가 만든 자바코드를 기계어로 바꿔주는 건 컴파일러or 인터프리터 가 해주는 것이다.
마찬 가지로
우리가 만든 sql문을 기계어로 바꿔주는게 DBMS이다.
(우리가 짠 sql문은 자바코드에서 그냥 스트링으로 읽혀진다. 자바코드에서 그걸 DBMS를 호출해서 읽히게 하는거다.)
PL/SQL 정의
PL/SQL은 SQL 문장에서 변수 정의, 조건처리(IF), 반복처리(LOOP,WHILE,FOR) 등을 지원하며, 오라클 자체에 내장 되어 있는 절차적 언어로서 SQL의 단점을 보완해 준다.
- 변수, 상수 등을 선언하여 SQL과 절차형 언어에서 사용한다.
- IF 문등을 사용하여 조건에 따라 문장들을 분기한다.
- LOOP 문을 사용하여 일련의 문장을 반복적으로 실행한다.
- 커서를 사용하셔 여러행을 검색한다.
PL/SQL 구조
PL/SQL은 블록(BLOCK) 구조의 언어로서, 크게 선언부 , 실행부, 예외처리부 3가지 부분으로 나눌 수 있다.
- DECLARE SECTION(선언부)
- EXECUTABLE SECTION(선언부)
- EXCEPTION SECTION(선언부)
- DECLARE(선언부)
PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분으로서 DECLARE로 시작한다.
- BEGIN(실행부)
절차적 형식으로 SQL 문을 실행할 수 있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할 수 있는 부분이며 BEGIN으로 시작한다.
- EXCEPTION(예외처리부)
PL/SQL 문이 실행되는 중에 에러가 발생할 수 있는데 이를 예외 사항이라고 한다. 예외 사항이 발생했을 때 이를 해결하기 위한 문장을 기술할 수 있는 부분이며 EXCEPTION으로 시작한다.
PL/SQL이 SQL을 읽는 원리
자바에서 만약 sql을 섞어서 코딩을 한다면
자바 코드 돌다가 sql문을 만나면 SQL 엔진으로 던져진다. DB로 가서 sql엔진에 의해 결과 받고 다시 서버온다음에 또 돌다가 또 sql만나면 다시 DB 가서 SQL엔진으로 결과 돌리고 하는거다. 왔다 갔다 하는거다.
하지만,
PL/SQL을 사용하면 DB로 한번 갔다가 거기 내에서 PL/SQL Enging이 받는다.
-> 내부에 SQL 문이 있으면 -> 그때 SQL 엔진으로 던져진다.
그리고 END 를 만나기 전에 DB내에서 수행 된다.
=> 최종적으로 로직을 다 처리한 후에 한 번만 서버로 결과를 반환한다.
PL/SQL의 계통도
ALGOL -> Pascal -> Ada -> PL/SQL
알골, 파스칼과 에이다를 참고해서 Oracle사에서 만든것이 PL/SQL
SQL의 한계를 극복하기 위해 만든것이 PL/SQL이다.
PL/SQL은
파스칼을 참고했기 때문에 할당 연산자와 비교연산자가 C,Java와 다르다.
C, JAVA는 코드 블록 구분을 { } 로 한다.
PL/SQL은 Begin과 END로 블록을 구분한다.
PL/SQL은 블록 구조 언어이다.
begin이 여러개라도 예외처리 부분은 하나이다.
(예외 처리 부분을 몰아서 해놓는다.)
PL/SQL 변수 선언과 대입문
PL/SQL의 선언부에서는 실행부에서 사용할 변수를 선언한다.
VEMPNO NUMBER(4);
VEMPNO VARCHAR2(10);
선언부에서 선언한 변수를 실행부에서 사용하는것이다.
선언한 변수에 값을 넣어주기 위해서는 :=을 사용한다.
DECLARE
VEMPNO NUMBER(4);
VENAME VARCHAR2(10);
BEGIN
VEMPNO := 10
VENAME := "KANG"
DBMS_OUTPUT.PUT_LINE(VEMPNO);
DMBS_OUTPUT.PUT_LINE(VENAME);
END;
/
스칼라변수/ 레퍼런스 변수
변수를 선언하기 위해 사용할 수 있는 자료형은 크게 스칼라와 레퍼런스로 나눌 수 있다.
스칼라변수 선언
VEMPNO NUMBER(4);
VENAME VARCHAR2(10);
레퍼런스 변수는 이전에 선언된 다른 변수 또는 DB 컬럼에 맞추어 변수를 선언하기 위해 %TYPE 속성을 사용할 수 있다.
레퍼런스 변수 선언하기(COLUMN 단위)
오라클에서 %ROWTYPE , %TYPE 는 PL/SQL에서 변수의 데이터타입, 크기를 지정해줄 때 사용하는데 사용 시에는 변수의 데이터타입, 크기가 선택한 테이블 컬럼의 타입, 크기와 동일하게 된다.
변수명 테이블명.칼럼명%TYPE;
VEMPNO EMP.EMPNO%TYPE;
VENAME EMP.ENAME%TYPE;PL/SQL에서 SELECT 문
PL/SQL의 SELECT문은 INTO 절이 필요한데, INTO 절에는 데이터를 저장할 변수를 기술한다.
DECLARE
VEMPNO emp.empno%type;
VEMPNAME emp.ename%type;
BEGIN
select empno, ename into VEMPNO, VEMPNAME
from emp
where ename='kang';
END;
/
레퍼런스 변수 선언하기(ROW 단위)
변수명 테이블명%ROWTYPE;
VEMP EMP%ROWTYPE;create or replace function uf_emp_dept(e emp%rowtype) return dept%rowtype
is
dept_rec dept%rowtype;
begin
select * into dept_rec
from dept
where deptno = e.deptno;
return dept_rec;
end;
/create or replace procedure up_emp_dept
(e in emp%rowtype,
d out dept%rowtype)
is
begin
select * into d
from dept
where deptno = e.deptno;
end;
/위의 2개가 같은거임
위에는 함수로 선언한거고 밑에는 프로시저로 선언한 것이다.
oracle 선택문
DECODE 함수
- decode(a,b,x,y) : a=b 이면 x를 출력 a!=b이면 y를 출력
- decode(a,b,x,c,y,z) : a=b이면 x출력, a=c이면 y 출력, a!=b이고 a!=c이면 z출력
CASE WHEN 함수
case when (조건) then (리턴값)
else (리턴값)
end
예시)
case when deptno=10 then '10번 부서'
else '10번 부서가 아니다.'
end
case표현식 에서 then 뒤에는 값만 와야 한다.
v_grade3 := decode(
);
decode 함수는 sql문으로 쓸 수 있지만 PLSQL에서는 쓸 수가 없다.
case then 함수를 사용해야 한다.
oracle 반복문
- 조건 없이 반복 작업을 제공하기 위한 BASIC LOOP문
- COUNT를 기본으로 작업의 반복 제어를 제공하는 FOR LOOP문
- 조건을 기본으로 작업의 반복 제어를 제공하기 위한 WHILE LOOP문
- LOOP를 종료하기 위한 EXIT문
BASIC LOOP문
declare
n number:=1;
begin
loop
dbms_output.put_line(n);
n:=n+1;
exit when n=6;
end loop;
end;
/
FOR LOOP문
declare
begin
for n in 1..5 loop
dbms_output.put_line(n);
end loop;
end;
/
WHILE LOOP문
declare
n number := 1;
begin
while n!=6 loop
dbms_output.put_line(n);
n:= n+1;
end loop;
end;
/
PL/SQL 수행
begin
end;
/PL/SQL 문으로 뭔가를 할때는 ' / ' 를 꼭 마지막에 적어줘야한다. 이것을 실행한다는 의미이다.
클라이언트 창에서 execute PL/SQL 이름적는 것이 요청을 하는거다.
(DBMS에 컴파일 해서 결과 보여달라 !!!! 라고 요청하는거다. )
show erros 를 하면 프로시저랑 등등 PL/SQL 생성시 오류를 알 수 있다.
예시
직원의 사번과, 증가할 급여를 매개변수로 입력할 때 emp테이블을 UPDATE 하는 Procedure를 만들어보자.
1단계) uf_select_t1_sal 함수를 만든다.
create or replace function uf_select_t1_sal
(p_empno t1.empno%type)
return t1.sal%type
is
v_sal t1.sal%type;
begin
select sal into v_sal
from t1
where empno = p_empno;
return v_sal;
end;
/
함수에서는 매개변수로 emp_no를 받아서
결과=> emp_no에 해당하는 sal 의 값을 받아온다.
그림을 통해 function이 작동하는 원리를 알아보자
선언부에 선언한 변수명에 로직을 돌린 결과 값을 할당하고 그거를 리턴해(반환) 주는 것이다.
2단계) 값을 UPDATE 하는 up_update_t1_sal프로시저를 만든다.
create or replace procedure up_update_t1_sal
(p_empno t1.empno%type,
p_sal_increase number)
is
begin
update t1
set sal = sal + p_sal_increase
where empno = p_empno;
end;
/
첫번째 프로시저(up_update_t1_sal)에서는
emp_no와 변경할 sal값을 매개변수로 받은 후
결과 => t1테이블에 emp_no에 해당하는 sal값을 업데이트 시킨다.
3단계) 변경전과 후의 값을 볼 수 있도록 출력을 하는 p1 프로시저를 만든다.
create or replace procedure p1
(p_empno t1.empno%type,
p_sal_increase number)
is
begin
dbms_output.put_line(p_empno||' 사원의 변경전 급여 : '||uf_select_t1_sal(p_empno));
up_update_t1_sal(p_empno, p_sal_increase);
dbms_output.put_line(p_empno||' 사원의 변경후 급여 : '||uf_select_t1_sal(p_empno));
end;
/마지막 프로시저(p1) 에서는 emp_no 증가시키고 싶은 p_sal_increase를 매개변수로 받아와서
프로시저 1을 실행시켜서 emp_no의 sal 값을 변경시켜준다.
콘솔창에 변경전 급여 변경 후 급여를 syso로 출력하고싶다.
결과 => 콘솔창에 사원의 변경전 급여와 사원의 변경후 급여가 콘솔창에 출력된다.
#매개변수 두 개 사용하는 프로시져
pl/sql 의 독특함
프로시저가 2개
함수가 1개
pl/sql 오버로딩을 쓰려면
우선 패키지에 넣어줘야한다.!!!
패키지 안에서 매개변수 를 달리 주면서
오버로딩 시키는거다.
앞에 있는건 패키지명. 뒤에있는건 sub
헤더 부분(is 앞에까지)을 spec 안에 넣어 줌으로써
private 으로 선언된걸 public 으로 변환시켜 준다.
(그래야 plsql 오버로딩이 가능하다. public으로 변환을 안해주면 같은 패키지 안에서 밖에 못쓰므로 오버로딩이 안된다.)
선언부에서 not NULL 키워드를 쓰면 무조건 초기화를 시켜줘야 한다.
v_deptno NUMBER(2) NOT NULL :=10;
또는 CONSTANT 키워드를 사용하더라고 무조건 초기화를 시켜줘야 한다.
오늘의 tip
PL/SQL 짤때 이름 있게 없게 만들 수 있다.
이름 없이 만들때는 begin부터 시작하고
이름 있이 만들때는 create or replace 이런식으로 시작한다.
char 40글자.....
만들어 놓은 프로시저를 사용하는 방법
2가지
exec 프로시저명
또는
begin
프로시저명
end;
프로젝트 진행할 때
테이블당 패키지를 만들고 dao 에서는 패키지 안에 있는 써브 프로그램을 호출하는 코드만 있으면 된다.
body부분을 wrepping 시켜보자!
패키지화 해서 소스 코드로 만든다음에 배포한다?????
SQL> host where wrap //wrap의 위치를 물어보는것이다.
SQL> host wrap iname=pack_books_body.sql //실질적으로 wrapping 하는 부분
SQL> host dir pack* //
SQL> host more pack_books_body.plb //more로 보여준다.
SQL> @pack_books.sql
SQL> @pack_books_body.plb
dir more where wrap 등등 os 명령어다.
dir은 sqlplus에서 실행이 안되는 명령어 이다.
where도 마찬가지이다.
이걸 sqlplus위에서 사용하고 싶은거다...아니면 자꾸 들어갔다 나왔다가 해야 하니까.......귀찮잖아?
=> 그럴때 host 명령어를 사용하는것이다!!!!
sqlplus에서 os명령어를 쓰고 싶을때 host 라는 키워드를 사용해서 쓰면된다.
오늘의 최종 실습
books 테이블에 책 제목과 저자를 추가해 주는 프로시저를 만들어 보자.
pack_books 패키지를 만들고 오버로딩을 시켜보자.
오늘의 TIP
오라클에서 제공해 주는 프로시저를 사용하여 출력해 주는 내용을 화면에 보여주려면 아래와 같은 코딩을 해야한다.
SET SERVEROUTPUT ON
'Oracle' 카테고리의 다른 글
| [Oracle]저장 프로시저 / 저장함수 _ 1107 (0) | 2022.11.12 |
|---|---|
| [Oracle]View와 JDBC 드라이버 _ 1108 (1) | 2022.11.09 |
| [Oracle]그룹함수_1104 (0) | 2022.11.04 |
| [Oracle]Oracle 주요함수_1103 (0) | 2022.11.03 |
| [Oracle]정렬, 데이터 연결_1102 (0) | 2022.11.03 |