| [문제] 사원들 중에 job_id가 'SA_REP' 사원들의 이름, 부서 이름을 출력하고 부서 배치를 받지 않는 사원에 대해서는 "부서 배치를 못 받았습니다." 출력해야 합니다. 또한 출력할때 카운터 수를 출력해주세요.(조인은 이용하지 마세요) <출력화면> 1 사원이름 : Tucker, 부서이름 : Sales 2 사원이름 : Bernstein, 부서이름 : Sales ... 28 사원이름 : Livingston, 부서이름 : Sales 29 사원이름 : Grant, 부서이름 : 부서 배치를 못 받았습니다. 30 사원이름 : Johnson, 부서이름 : Sales |
| DECLARE CURSOR emp_cur IS SELECT last_name, department_id FROM hr.employees WHERE job_id = 'SA_REP'; dept_name hr.departments.department_name%type; cnt number := 1; BEGIN FOR r_vec IN emp_cur LOOP -- r_vec : FOR문과 함께 선언한거라 FOR문 안에서만 가능 BEGIN SELECT department_name INTO dept_name FROM hr.departments WHERE department_id = r_vec.department_id; DBMS_OUTPUT.PUT_LINE(cnt||' '||'사원이름 : '||r_vec.last_name||', 부서이름 : '||dept_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(cnt||' '||'사원이름 : '||r_vec.last_name||', 부서이름 : 부서 배치를 못 받았습니다.'); END; cnt := cnt + 1; END LOOP; END; / |
| [문제] 사원번호를 입력값으로 받아서 그 사원의 정보를 출력하는 프로그램 |
| var b_id number var b_sal number execute :b_id := 100 print :b_id DECLARE v_id number := :b_id; v_rec hr.employees%rowtype; BEGIN SELECT * INTO v_rec FROM hr.employees WHERE employee_id = v_id; DBMS_OUTPUT.PUT_LINE(v_rec.employee_id||' '||v_rec.last_name); :b_sal := v_rec.salary; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(v_id||' 사원은 존재하지 않습니다.'); END; / |
| 익명 블록 | 서브프로그램(프로시저, 함수, 패키지), 객체 프로그램 |
| 이름이 있는 PLSQL블록 | 이름이 없는 PLSQL 블록 |
| 매번 컴파일해야한다. | 한번만 컴파일 한다. |
| DB에 저장되지 않습니다. | DB에 저장된다. |
| 다른 응용프로그램에서 호출할 수 없다. | 다른 응용프로그램에서 호출할 수 있다. |
| 파라미터를 사용할 수 없다. | 파라미터를 사용할 수 있다. |
SELECT * FROM session_privs;
>> CREATE PROCEDURE 시스템권한 : 프로시저, 함수, 패키지 객체를 생성할 수 있는 권한
| /* 프로시저 생성 */ CREATE OR REPLACE PROCEDURE emp_proc (v_id number) IS v_rec hr.employees%rowtype; BEGIN SELECT * INTO v_rec FROM hr.employees WHERE employee_id = v_id; DBMS_OUTPUT.PUT_LINE(v_rec.employee_id||' '||v_rec.last_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(v_id||' 사원은 존재하지 않습니다.'); END; / |
| /* 프로시저 조회 */ SELECT * FROM user_source WHERE name = 'EMP_PROC' -- 대문자 ORDER BY line; |
| /* 호출 */ execute emp_proc(100) execute emp_proc(300) exec emp_proc(100) -- exec : execute 약어 exec emp_proc(300) BEGIN emp_proc(100); END; / |
■ FORMAL PARAMETER (형식매개변수)
- 형식매개변수를 선언시에 SIZE를 명시하면 안된다.
- 형식매개변수는 한번 입력받은 값만 사용해야 한다.
# PARAMETER MODE
IN 파라미터는 상수값을 호출 환경에서 프로시저로 전달한다. 입력값 처리. 기본값
IN 모드로 선언된 형식매개변수는 프로그램안에서는 상수로 동작한다.(고정값)
OUT 파라미터는 프로시저 안에서 어떤 값을 호출환경으로 전달한다. 값리턴 처리.
OUT 모드로 선언된 형식매개변수는 프로그램안에서 변수로 동작한다.
IN OUT 파라미터는 값을 호출환경에서 프로시저로 전달하고 동일한 매개변수에 대해서 다른값으로 변경한 값을 호출환경으로 전달한다.
| CREATE OR REPLACE PROCEDURE emp_proc(v_id number) --> (v_id IN number) IS -- IS : DECLARE 대신 사용 필수 v_rec hr.employees%rowtype; BEGIN SELECT * INTO v_rec FROM hr.employees WHERE employee_id = v_id; DBMS_OUTPUT.PUT_LINE(v_rec.employee_id||' '||v_rec.last_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(v_id||' 사원은 존재하지 않습니다.'); END; / |
| SELECT * FROM user_source WHERE name = 'EMP_PROC' -- 조회는 대문자 ORDER BY line; |
| /* 호출 */ execute emp_proc(100) --> Actual Parameter (실제매개변수) execute emp_proc(300) execute emp_proc() -- PLS-00306: wrong number or types of arguments in call to 'EMP_PROC' exec emp_proc(100) exec emp_proc(300) BEGIN emp_proc(100); END; / |
# 컴파일 실패 시에 오류 확인하는 방법
show error
| CREATE OR REPLACE PROCEDURE emp_proc(p_id in number, p_name out varchar2, p_sal out number) IS BEGIN SELECT last_name, salary INTO p_name, p_sal -- OUT FROM hr.employees WHERE employee_id = p_id; -- IN DBMS_OUTPUT.PUT_LINE(p_id||' '||upper(p_name)||' '||to_char(p_sal,'L999,999')); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(p_id||' 사원은 존재하지 않습니다.'); END; / |
| SELECT * FROM user_source WHERE name = 'EMP_PROC' -- 조회는 대문자 ORDER BY line; |
| var b_name varchar2(30) var b_sal number execute emp_proc(100, :b_name, :b_sal) -- 호출 print :b_name :b_sal DECLARE v_name varchar2(30); v_sal number; BEGIN emp_proc(100, v_name, v_sal); -- 호출 DBMS_OUTPUT.PUT_LINE(v_name||' '||v_sal); END; / |
# 프로시저 형식매개변수 구조 확인
desc emp_proc
| CREATE OR REPLACE PROCEDURE format_phone(p_phone_no IN OUT varchar2) IS BEGIN p_phone_no := substr(p_phone_no,1,3)||'-'||substr(p_phone_no,4,4)||'-'||substr(p_phone_no,8); END; / |
| var b_phone varchar2(20) execute :b_phone := '01012345678' execute format_phone(:b_phone) -- IN & OUT print :b_phone DECLARE v_phone varchar2(20) := '01012345678'; BEGIN format_phone(v_phone); -- IN & OUT DBMS_OUTPUT.PUT_LINE(v_phone); END; / |
| CREATE OR REPLACE PROCEDURE sp_comm (p_id in hr.employees.employee_id%type, p_name out hr.employees.last_name%type, p_sal out hr.employees.salary%type, p_comm in out hr.employees.commission_pct%type ) IS v_comm hr.employees.commission_pct%type; BEGIN SELECT last_name, salary, nvl(commission_pct,0) INTO p_name, p_sal, p_comm -- OUT FROM hr.employees WHERE employee_id = p_id; -- IN p_comm := p_comm + v_comm; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000,sqlerrm); WHEN others THEN RAISE_APPLICATION_ERROR(-20001,sqlerrm); END; / >> 호출 : sp_comm(100, 변수, 변수, 초기값이있는변수) |
| SHOW ERROR -- 프로시저 에러 확인 desc sp_comm -- 타입 확인 SELECT * FROM user_source WHERE name = 'SP_COMM' ORDER BY line; |
| DECLARE v_id number := 160; v_name varchar2(30); v_sal number; v_comm number(3,2) := 0.1; BEGIN sp_comm(v_id, v_name, v_sal, v_comm); -- 호출 -- sp_comm(300, v_name, v_sal, v_comm); -- ORA-20000: ORA-01403: no data found DBMS_OUTPUT.PUT_LINE(v_name||' '||v_sal||' '||v_comm); END; / |
| CREATE TABLE sawon (id number, name varchar2(30), day date, deptno number); |
| CREATE OR REPLACE PROCEDURE sawon_in_proc (p_id in number, p_name in varchar2, p_day in date default sysdate, p_deptno in number := 0) IS BEGIN INSERT INTO hr.sawon(id, name, day, deptno) VALUES(p_id, p_name, p_day, p_deptno); -- IN END sawon_in_proc; / |
| desc sawon_in_proc -- 타입 체크 |
| # 위치지정방식 : 형식매개변수에 실제값이 위치적으로 대응해서 수행하는 방법. execute sawon_in_proc(1, '홍길동', to_date('2023-01-01','yyyy-mm-dd'), 10) SELECT * FROM hr.sawon; |
| # 이름지정방식 : 형식매개변수에 실제값을 이름지정방식(=>)으로 수행하는 방법 execute sawon_in_proc(p_id => 1, p_name => '홍길동', p_deptno => 10) SELECT * FROM hr.sawon; -- 날짜는 디폴트값으로 입력 됨 |
| # 위치지정방식과 이름지정방식 혼합, 위치지정방식 뒤에 이름지정방식 순서는 문제없이 사용가능하다. execute sawon_in_proc(2, p_name => '손흥민', p_deptno => 10) SELECT * FROM hr.sawon; |
| # 주의! 이름지정방식 뒤에는 위치지정방식을 사용할 수 없다. execute sawon_in_proc(p_id => 3, '박찬호', p_deptno => 30) SELECT * FROM hr.sawon; -- PLS-00312: a positional parameter association may not follow a named association |
| rollback; SELECT * FROM hr.sawon; |
/* 프로시저 생성 */
CREATE PROCEDURE 프로시저명(변수명A 타입, 변수명A 타입)
IS
CREATE OR REPLACE PROCEDURE 프로시저명(변수명A 타입, 변수명A 타입) -- 기존에 사용하던 명칭이 있다면 삭제 후 생성
IS
(변수명 IN 타입) -- 기본값 : 상수로 입력받음
(변수명 OUT 타입) -- 호출환경으로 값 리턴
(변수명 IN OUT 타입) -- 값을 입력받아 작업 후 다시 리턴
# 위치지정방식 : 형식매개변수에 실제값이 위치적으로 대응해서 수행하는 방법.
execute 프로시저명(값, 값)
# 이름지정방식 : 형식매개변수에 실제값을 이름지정방식(=>)으로 수행하는 방법, 순서무관
execute 프로시저명(변수명 => 값, 변수명 => 값)
# 주의! 이름지정방식 뒤에는 위치지정방식을 사용할 수 없다.
/* 프로시저 조회 */
SELECT *
FROM user_source
WHERE name = '프로시저명' -- 대문자
ORDER BY line;
# 프로시저 형식매개변수 구조 확인
desc emp_proc
# 컴파일 실패 시에 오류 확인하는 방법
show error
'Oracle SQL' 카테고리의 다른 글
| 21 PL/SQL PACKAGE (1) | 2023.11.03 |
|---|---|
| 20 PL/SQL PROCEDURE, FUNCTION (0) | 2023.11.02 |
| 18 PL/SQL EXCEPTION (1) | 2023.10.31 |
| 17 PL/SQL Nested Table, VARRAY, Explicit Cursor (0) | 2023.10.30 |
| 16 PL/SQL RECORD, Array (0) | 2023.10.27 |