프로시저 만들기 1)
-------- ***** PL / SQL 구문에서 변수의 사용법 첫번째 ***** ---------------------
create or replace procedure pcd_empInfo -- 이러한 이름의 프로시저가 없으면 만들고, 있으면 엎고 이걸로 만든다.
(p_employee_id IN number) -- IN은 입력모드를 말한다. (출력모드는 out, inout은 입력과 동시출력) / number(5)와 같이 자리수를 넣어주면 오류이다!!
is
v_employee_id number(5); -- 자리수를 사용한다.
v_ename varchar2(50);
v_gender varchar2(15);
v_monthsal varchar2(10);
begin
select employee_id,
first_name || ' ' || last_name,
CASE WHEN SUBSTR(jubun, 7, 1) IN('1', '3') THEN '남' ELSE '여' END,
to_char(NVL( salary + ( salary * commission_pct ), salary ), '9,999,999')
INTO
v_employee_id,
v_ename,
v_gender,
v_monthsal
from employees
where employee_id = p_employee_id; -- 변수가 되어준다
dbms_output.put_line( lpad('-',40,'-') ); --자바의 sysout과 비슷함
dbms_output.put_line( '사원번호 사원명 성별 월급' );
dbms_output.put_line( lpad('-',40,'-') );
dbms_output.put_line(v_employee_id || ' ' ||
v_ename || ' ' ||
v_gender || ' ' ||
v_monthsal);
end pcd_empInfo; --프로시저명
-- Procedure PCD_EMPINFO이(가) 컴파일되었습니다. 가 뜨면 성공! 2) 프로시저 DBMS 출력
/* === SQL Developer 의 메뉴의 보기를 클릭하여 DBMS 출력을 클릭해주어야 한다. ===
=== 이어서 하단부에 나오는 DBMS 출력 부분의 녹색 + 기호를 클릭하여 local_hr 로 연결을 해준다. ===
*/
exec pcd_empInfo(101); -- 사원번호를 넣으면 결과물 출력
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.
/*
<결과물>
----------------------------------------
사원번호 사원명 성별 월급
----------------------------------------
101 Neena Kochhar 남 17,000
*/
exec pcd_empInfo(102); -- PL/SQL 프로시저가 성공적으로 완료되었습니다.
/*
<결과물>
----------------------------------------
사원번호 사원명 성별 월급
----------------------------------------
102 Lex De Haan 여 17,000
*/
3) 프로시저 소스 보기
select text
from user_source
where type = 'PROCEDURE' and name = 'PCD_EMPINFO'; -- 프로시저 소스 분석하기
4) 프로시저 쓰는 이유 ?
select employee_id,
first_name || ' ' || last_name,
CASE WHEN SUBSTR(jubun, 7, 1) IN('1', '3') THEN '남' ELSE '여' END,
to_char(NVL( salary + ( salary * commission_pct ), salary ), '9,999,999')
from employees
where employee_id = 101;
select employee_id,
first_name || ' ' || last_name,
CASE WHEN SUBSTR(jubun, 7, 1) IN('1', '3') THEN '남' ELSE '여' END,
to_char(NVL( salary + ( salary * commission_pct ), salary ), '9,999,999')
from employees
where employee_id = 102;
위의 코드를 보는 것 보다는 프로시저를 사용해서 보는것이 더 빠르다.
-- 프로시저는 문법에 맞을 때만 컴파일 되므로, 보다 확실하게 사용할 수 있다.(만약 위의 방법으로 했을때 틀리면 매번 문법검사하게 됨)
-- 문법에 통과되어진 다음에는 테이블의 유무를 검사-> 테이블이 존재하지 않으면 또 고쳐야하고
-- 모든게 맞으면 그제서야 정보를 다 보여주므로 프로시저보다 속도가 느리다. 프로시저로 만들면 한번만 검사하고 끝낼 수 있기 때문에(재사용) 효율적이다.
프로시저 만들기 2
-------- ***** PL / SQL 구문에서 변수의 사용법 두번째 ***** ---------------------
desc employees;
-- EMPLOYEE_ID NOT NULL NUMBER(6)
create or replace procedure pcd_empInfo -- 이러한 이름의 프로시저가 없으면 만들고, 있으면 엎고 이걸로 만든다.
(p_employee_id IN employees.employee_id%type) -- EMPLOYEE_ID NOT NULL NUMBER(6) 와 같다.
-- p_employee_id 매개변수의 데이터 타입은 employees.employee_id%type이다.
-- 그런데 employees.employee_id%type의 뜻은 employees 테이블의 employee_id 칼럼의 데이터타입을 그대로 사용하겠다는 말이다.
is
--변수의 선언부
v_employee_id employees.employee_id%type; -- p_ 는매개변수, V_는 select되어진 결과물을 갖는 변수
v_ename varchar2(50);
v_gender varchar2(15);
v_monthsal varchar2(10);
begin
-- 실행부
select employee_id,
first_name || ' ' || last_name,
CASE WHEN SUBSTR(jubun, 7, 1) IN('1', '3') THEN '남' ELSE '여' END,
to_char(NVL( salary + ( salary * commission_pct ), salary ), '9,999,999')
INTO -- 변수에 담아주기
v_employee_id,
v_ename,
v_gender,
v_monthsal
from employees
where employee_id = p_employee_id;-- 변수가 되어준다
dbms_output.put_line( lpad('-',40,'-') ); --자바의 sysout과 비슷함
dbms_output.put_line( '사원번호 사원명 성별 월급' );
dbms_output.put_line(lpad('-',40,'-'));
dbms_output.put_line(v_employee_id || ' ' ||
v_ename || ' ' ||
v_gender || ' ' ||
v_monthsal);
end pcd_empInfo; --프로시저명
-- Procedure PCD_EMPINFO이(가) 컴파일되었습니다.
-- 이미 있는 거니 기존 것을 없애버리고 바꿔준다.
-- error 볼때 2/28 하면 2번째줄 28 번째.
select text
from user_source
where type = 'PROCEDURE' and name = 'PCD_EMPINFO';
exec pcd_empInfo(101);
프로시저 만들기 3
-------- ***** PL / SQL 구문에서 변수의 사용법 세번째 ***** ---------------------
create or replace procedure pcd_empInfo
(p_employee_id IN employees.employee_id%type)
is
-- record 타입 생성 ---
type myEmpType is record
(empid employees.employee_id%type -- 밑의 셀렉트문 employee_id 들어옴
,ename varchar2(50) -- 밑의 first_name || ' ' || last_name 들어옴
,gender varchar2(10) -- CASE WHEN SUBSTR(jubun, 7, 1) IN('1', '3') THEN '남' ELSE '여' END...
,monthsal varchar2(15)
,age number(3)
);
-- 변수 생성 --
v_rcd myEmpType;
begin
select employee_id,
first_name || ' ' || last_name,
CASE WHEN SUBSTR(jubun, 7, 1) IN('1', '3') THEN '남' ELSE '여' END,
to_char(NVL( salary + ( salary * commission_pct ), salary ), '9,999,999'),
case when to_date(to_char(sysdate, 'yyyy') || substr(jubun, 3, 4), 'yyyymmdd') - to_date(to_char(sysdate, 'yyyymmdd'), 'yyyymmdd') > 0
then extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end ) - 1
else extract(year from sysdate) - ( to_number(substr(jubun,1,2)) + case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end )
end
INTO
v_rcd
from employees
where employee_id = p_employee_id;
dbms_output.put_line( lpad('-',50,'-') ); --자바의 sysout과 비슷함
dbms_output.put_line( '사원번호 사원명 성별 월급 나이' );
dbms_output.put_line(lpad('-',50,'-'));
dbms_output.put_line(v_rcd.empid || ' ' ||
v_rcd.ename || ' ' ||
v_rcd.gender || ' ' ||
v_rcd.monthsal || ' ' ||
v_rcd.age
);
end pcd_empInfo; --프로시저명
-- Procedure PCD_EMPINFO이(가) 컴파일되었습니다.
프로시저 만들기 4
-------- ***** PL / SQL 구문에서 변수의 사용법 네번째 ***** ---------------------
create or replace procedure pcd_empInfo
(p_employee_id IN employees.employee_id%type)
is
v_all employees%rowtype;
-- v_all 은 변수인데 변수의 타입은 employees 테이블의 모든 컬럼을 받아주는 rowtype(행 타입)이다.
v_result varchar2(1000);
begin
select * INTO v_all -- employees 테이블에 있는 모든 컬럼을 v_all 이라는 변수에 담아준다는 의미이다.
from employees
where employee_id = p_employee_id;
--변수에 값을 대입할때는 := 사용
v_result := v_all.employee_id || ' ' || -- := 어느 변수(v_result)에 값을 대입할 때
v_all.first_name || ' ' || v_all.last_name || ' ' ||
case when substr(v_all.jubun, 7, 1) in('1', '3') then '남' else '여' end || ' ' ||
to_char(nvl(v_all.salary + (v_all.salary * v_all.commission_pct), v_all.salary), '9,999,999') || ' ' ||
case when to_date(to_char(sysdate, 'yyyy') || substr(v_all.jubun, 3, 4), 'yyyymmdd') - to_date(to_char(sysdate, 'yyyymmdd'), 'yyyymmdd') > 0
then extract(year from sysdate) - (to_number(substr(v_all.jubun,1,2)) + case when substr(v_all.jubun,7,1) in('1','2') then 1900 else 2000 end ) - 1
else extract(year from sysdate) - (to_number(substr(v_all.jubun,1,2)) + case when substr(v_all.jubun,7,1) in('1','2') then 1900 else 2000 end )
end || ' ' ||
nvl(to_char(v_all.department_id), '부서없음'); -- department_id 는 number 타입이기 때문에 형변환
dbms_output.put_line(lpad('-', 50, '-'));
dbms_output.put_line('사원번호 사원명 성별 월급 나이 부서번호');
dbms_output.put_line(lpad('-', 50, '-'));
dbms_output.put_line(v_result);
end pcd_empInfo;--프로시저명
프로시저 오류 났을 때
sql developer에서 작성한 코드를 붙여넣고 주석 지우고 sqlplus/nolog로 들어가서 실행함
SQL> create or replace procedure pcd_empInfo
2 (p_employee_id IN number)
3 is
4 --변수의 선언부
5 v_employee_id number(5);
6 v_ename varchar2(50);
7 v_gender varchar2(15);
8 v_monthsal varchar2(10);
9 begin
10 -- 실행부
11 select employee_id,
12 first_name || ' ' || last_name,
13 CASE WHEN SUBSTR(jubun, 7, 1) IN('1', '3') THEN '남' ELSE '여' END,
14 to_char(NVL( salary + ( salary * commission_pct ), salary ), '9,999,999')
15 INTO -- 변수에 담아주기
16 v_employee_id,
17 v_ename,
18 v_gender,
19 v_monthsal
20 from employees
21 where employee_id = p_employee_id;
22
23 dbms_output.put_line( lpad('-',40,'-') );
24 dbms_output.put_line('사원번호 사원명 성별 월급');
25 dbms_output.put_line(lpad('-',40,'-'));
26 dbms_output.put_line(v_employee_id || ' ' ||
27 v_ename || ' ' ||
28 v_gender || ' ' ||
29 v_monthsal);
30 end pcd_empInfo;
31 / <<<<<이거 꼭 넣어야됨!!
경고: 컴파일 오류와 함께 프로시저가 생성되었습니다.
SQL> show errors
PROCEDURE PCD_EMPINFO에 대한 오류:
LINE/COL ERROR
-------- -----------------------------------------------------------------
24/82 PLS-00103: 심볼 ";"를 만났습니다 다음 중 하나가 기대될 때:
. ( ) , * % & = - + < / > at in is mod remainder not rem =>
<지수(**)> <> 또는 != 또는 ~= >= <= <> and or default
like like2 like4 likec as between from using || multiset
member submultiset
심볼이 ")" 계속하기 위하여 ";"로 치환되었습니다
SQL> host cls (화면 청소)
--> 오류 고치고 다시 복붙 해보면 된다. 
개발하는 심심이