https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/03_types.htm#10546
컴퓨터에게 0101이라는 값을 주면?
-> 못알아듣는다.
-> 항상 타입을 지정해 주어야 한다.
-- 6가지 유형의 SELECT 문장과 적절한 변수 설정
-- 6가지 유형의 SELECT 문장과 적절한 변수 설정
-- [1]
create or replace procedure p1 (
p_employee_id in employees.employee_id%type,
p_salary out employees.salary%type
)
is
v_salary employees.salary %type;
begin
select salary into v_salary
from employees
where employee_id = p_employee_id;
p_salary := v_salary;
end;
/
show errors
desc p1
set serveroutput on
declare
v_id number := 100;
v_ret number;
begin
p1(v_id, v_ret);
dbms_output.put_line(v_id||'의 급여는 '||v_ret);
end;
/
---
-- [2] 행 하나를 리턴하는 SELECT 문
create or replace procedure p1 (
p_employee_id in employees.employee_id%type,
p_last_name out employees.last_name%type,
p_salary out employees.salary%type,
p_job_id out employees.job_id%type
)
is
employee_row employees%rowtype;
begin
select * into employee_row
from employees
where employee_id = p_employee_id;
p_last_name := employee_row.last_name;
p_salary := employee_row.salary;
p_job_id := employee_row.job_id;
end;
/
declare
v_employee_id employees.employee_id%type := 100;
v_last_name employees.last_name%type;
v_salary employees.salary%type;
v_job_id employees.job_id%type;
begin
p1(v_employee_id, v_last_name, v_salary, v_job_id);
dbms_output.put_line(v_employee_id);
dbms_output.put_line(v_last_name);
dbms_output.put_line(v_salary);
dbms_output.put_line(v_job_id);
end;
/
create or replace procedure p1 (
p_employee_id in employees.employee_id%type,
p_employee_row out employees%rowtype
)
is
begin
select * into p_employee_row
from employees
where employee_id = p_employee_id;
end;
/
declare
v_employee_id employees.employee_id%type := 100;
employee_row employees%rowtype ;
begin
p1(v_employee_id, employee_row);
dbms_output.put_line(v_employee_id);
dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/
-- [3] 행 하나의 몇몇 컬럼을 리턴하는 SELECT 문
create or replace procedure p1(
p_employee_id in employees.employee_id%type
)
is
TYPE employee_record_type IS RECORD (
last_name employees.last_name%type,
salary employees.salary%type,
job_id employees.job_id%type);
employee_row employee_record_type;
begin
select last_name, salary, job_id into employee_row
from employees
where employee_id = p_employee_id;
dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/
exec p1(100)
exec p1(101)
↓↓
create or replace package pack_datatypes
is
TYPE employee_record_type IS RECORD (
last_name employees.last_name%type,
salary employees.salary%type,
job_id employees.job_id%type);
end;
/
create or replace procedure p1(
p_employee_id in employees.employee_id%type
)
is
employee_row pack_datatypes.employee_record_type;
begin
select last_name, salary, job_id into employee_row
from employees
where employee_id = p_employee_id;
dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/
exec p1(100)
exec p1(101)
↓↓
create or replace procedure p1 (
p_employee_id in employees.employee_id%type,
p_employee_row out pack_datatypes.employee_record_type
)
is
begin
select last_name, salary, job_id into p_employee_row
from employees
where employee_id = p_employee_id;
end;
/
declare
v_employee_id employees.employee_id%type := 100;
employee_row pack_datatypes.employee_record_type;
begin
p1(v_employee_id, employee_row);
dbms_output.put_line(v_employee_id);
dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/
↓↓
create or replace view view_for_row_type
as
select last_name, salary, job_id
from employees;
create or replace procedure p1 (
p_employee_id in employees.employee_id%type,
p_employee_row out view_for_row_type%rowtype
)
is
employee_row view_for_row_type%rowtype;
begin
select last_name, salary, job_id into p_employee_row
from employees
where employee_id = p_employee_id;
end;
/
declare
v_employee_id employees.employee_id%type := 100;
employee_row view_for_row_type%rowtype;
begin
p1(v_employee_id, employee_row);
dbms_output.put_line(v_employee_id);
dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/
-- [4] 같은 유형의 값 여러 개를 리턴하는 SELECT 문
create or replace procedure p1 (
p_department_id in employees.department_id%type
)
is
TYPE employees_salary_tab_type IS TABLE OF employees.salary%type
INDEX BY pls_integer;
emp_sal_tab employees_salary_tab_type;
begin
select salary BULK COLLECT INTO emp_sal_tab
from employees
where department_id = p_department_id;
for i in emp_sal_tab.first .. emp_sal_tab.last loop
dbms_output.put_line(emp_sal_tab(i));
end loop;
end;
/
exec p1(20)
exec p1(50)
↓↓
create or replace package pack_datatypes
is
TYPE employee_record_type IS RECORD (
last_name employees.last_name%type,
salary employees.salary%type,
job_id employees.job_id%type);
TYPE employees_salary_tab_type IS TABLE OF employees.salary%type
INDEX BY pls_integer;
end;
/
create or replace procedure p1 (
p_department_id in employees.department_id%type,
p_emp_sal_tab out pack_datatypes.employees_salary_tab_type
)
is
begin
select salary BULK COLLECT INTO p_emp_sal_tab
from employees
where department_id = p_department_id;
end;
/
create or replace procedure p1_print (
p_department_id in employees.department_id%type
)
is
emp_sal_tab pack_datatypes.employees_salary_tab_type;
begin
p1(p_department_id, emp_sal_tab);
for i in emp_sal_tab.first .. emp_sal_tab.last loop
dbms_output.put_line(emp_sal_tab(i));
end loop;
end;
/
exec p1_print(20)
exec p1_print(50)
-- [5] 행 여러 개를 리턴하는 SELECT 문
create or replace procedure p1(
p_department_id in employees.department_id%type
)
is
TYPE employees_table_type IS TABLE OF employees%rowtype
INDEX BY pls_integer;
employees_tab employees_table_type;
begin
select * BULK COLLECT INTO employees_tab
from employees
where department_id = p_department_id;
for i in employees_tab.first .. employees_tab.last loop
dbms_output.put_line(employees_tab(i).last_name||', '||employees_tab(i).salary);
end loop;
end;
/
exec p1(20)
exec p1(50)
[6] 몇몇 컬럼을 포함하는 행 여러 개를 리턴하는 SELECT 문
create or replace procedure p1(
p_department_id in employees.department_id%type
)
is
TYPE employee_record_type IS RECORD (
last_name employees.last_name%type,
salary employees.salary%type,
job_id employees.job_id%type);
TYPE employees_table_type IS TABLE OF employee_record_type
INDEX BY pls_integer;
employees_tab employees_table_type;
begin
select last_name, salary, job_id BULK COLLECT INTO employees_tab
from employees
where department_id = p_department_id;
for i in employees_tab.first .. employees_tab.last loop
dbms_output.put_line(employees_tab(i).last_name||', '||employees_tab(i).salary);
end loop;
end;
/
exec p1(20)
exec p1(50)
https://www.oraclechennai.com/Calling-Stored-Functions-from-SQL-Expressions.html
To be callable from SQL expressions, a user-defined PL/SQL function must meet the following basic requirements:
/* SQL에 포함할 수 있는 함수 예제 하나 */
create or replace function number_of_days_worked(
p_employee_id in employees.employee_id%type
)
return number
is
v_days number;
begin
select ceil(sysdate - hire_date) into v_days
from employees
where employee_id = p_employee_id;
return v_days;
end;
/
select employee_id, number_of_days_worked(employee_id) "근속일수"
from employees;
exec dbms_output.put_line(number_of_days_worked(101))
/* SQL에 포함할 수 없는 함수 예제 하나 */
create or replace function findEmployeeById_func(
p_employee_id in employees.employee_id%type
)
return employees%rowtype
is
emp_row employees%rowtype;
begin
select * into emp_row
from employees
where employee_id = p_employee_id;
return emp_row;
end;
/
-- 에러임
select findEmployeeById_func(100)
from dual;
set serveroutput on
-- 성공함
declare
ret employees%rowtype;
begin
ret := findEmployeeById_func(100);
dbms_output.put_line(ret.last_name);
dbms_output.put_line(ret.salary);
end;
/
/* SQL에 포함할 수 없는 함수 예제 하나 더 */
create or replace function employees_salary_func(
p_employee_id in employees.employee_id%type,
p_salary out employees.salary%type
)
return employees.salary%type
is
begin
select salary into p_salary
from employees
where employee_id = p_employee_id;
return 0;
end;
/
-- 변수명에 변수를 넣을 수 있는가??
select employee_id, employees_salary_func(employee_id, 변수명)
from employees;
-- 성공함
declare
v_ret employees.salary%type;
v_sal employees.salary%type;
begin
v_ret := employees_salary_func(100, v_sal);
dbms_output.put_line(v_sal);
end;
/
# DML Triggers
/* 문장 트리거 */
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT or UPDATE or DELETE ON emp
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI') BETWEEN '08:00' AND '12:00') THEN
RAISE_APPLICATION_ERROR(-20500, 'You may insert into EMP table only during business hours.');
END IF;
END;
/
UPDATE emp
SET sal = sal + 100
WHERE rownum = 1;
DROP TRIGGER secure_emp;
------
/* 행 트리거 */
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
IF NOT (:NEW.job IN ('PRESIDENT', 'MANAGER')) AND :NEW.sal > 5000 THEN
RAISE_APPLICATION_ERROR (-20202, 'Employee cannot earn more than $5,000.');
END IF;
END;
/
UPDATE emp
SET sal = sal + 4500
WHERE deptno = 10;
set long 1000
select trigger_name, trigger_body
from user_triggers;
# DDL Triggers
CREATE OR REPLACE TRIGGER grant_connect
AFTER CREATE ON DATABASE
WHEN (dictionary_obj_type = 'USER')
begin
execute immediate 'grant connect,resource to '||dictionary_obj_name;
end;
/
CREATE OR REPLACE TRIGGER grant_select
AFTER CREATE ON schema
WHEN (sys.dictionary_obj_type = 'TABLE')
begin
execute immediate 'grant select on '|| sys.dictionary_obj_name || ' to joo' ;
end;
/
# Database Event Triggers
create table log_trig_table
(user_id varchar2(30),
log_date date,
action varchar2(30));
CREATE OR REPLACE TRIGGER logon_trig
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO log_trig_table(user_id,log_date,action)
VALUES (USER, SYSDATE, 'Logging on');
END;
/
------
CREATE TABLE up_time
(action_date DATE,
action VARCHAR2(20));
CREATE OR REPLACE TRIGGER db_start
AFTER STARTUP ON DATABASE
BEGIN
insert into system.up_time values(sysdate,'STARTUP');
END;
/
# 트리거 구현 예제들
- 보안 제어
GRANT SELECT, INSERT, UPDATE, DELETE
ON employees
TO clerk; -- database role
GRANT clerk TO scott;
------
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
DECLARE
dummy PLS_INTEGER;
BEGIN
IF (TO_CHAR (SYSDATE, 'DY') IN ('SAT','SUN')) THEN
RAISE_APPLICATION_ERROR(-20506,'You may only change data during normal business hours.');
END IF;
SELECT COUNT(*) INTO dummy FROM holiday
WHERE holiday_date = TRUNC (SYSDATE);
IF dummy > 0 THEN
RAISE_APPLICATION_ERROR(-20507, 'You may not change data on a holiday.');
END IF;
END;
/
- 데이터 무결성
ALTER TABLE employees ADD
CONSTRAINT ck_salary CHECK (salary >= 500);
------
CREATE OR REPLACE TRIGGER check_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary < OLD.salary)
BEGIN
RAISE_APPLICATION_ERROR (-20508, 'Do not decrease salary.');
END;
/
- 참조 무결성
ALTER TABLE employees
ADD CONSTRAINT emp_deptno_fk
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE CASCADE;
------
CREATE OR REPLACE TRIGGER cascade_updates
AFTER UPDATE OF department_id ON departments
FOR EACH ROW
BEGIN
UPDATE employees
SET employees.department_id=:NEW.department_id
WHERE employees.department_id=:OLD.department_id;
UPDATE job_history
SET department_id=:NEW.department_id
WHERE department_id=:OLD.department_id;
END;
/
- 테이블 복제(replication)
CREATE MATERIALIZED VIEW emp_copy
NEXT sysdate + 7
AS SELECT * FROM employees@ny;
------
CREATE OR REPLACE TRIGGER emp_replica
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
IF :NEW.flag IS NULL THEN
INSERT INTO employees@sf
VALUES(:new.employee_id,...,'B');
:NEW.flag := 'A';
END IF;
ELSE /* Updating. */
IF :NEW.flag = :OLD.flag THEN
UPDATE employees@sf
SET ename=:NEW.last_name,...,flag=:NEW.flag
WHERE employee_id = :NEW.employee_id;
END IF;
IF :OLD.flag = 'A' THEN
:NEW.flag := 'B';
ELSE
:NEW.flag := 'A';
END IF;
END IF;
END;
/
- 파생된 데이터 계산
UPDATE departments d
SET total_sal=(SELECT SUM(salary)
FROM employees d
WHERE e.department_id = d.department_id);
------
CREATE PROCEDURE increment_salary
(id NUMBER, new_sal NUMBER)
IS
BEGIN
UPDATE departments
SET total_sal = NVL (total_sal, 0)+ new_sal
WHERE department_id = id;
END increment_salary;
/
CREATE OR REPLACE TRIGGER compute_salary
AFTER INSERT OR UPDATE OF salary OR DELETE ON employees
FOR EACH ROW
BEGIN
IF DELETING THEN
increment_salary(:OLD.department_id,(-1*:OLD.salary));
ELSIF UPDATING THEN
increment_salary(:NEW.department_id,(:NEW.salary-:OLD.salary));
ELSE
increment_salary(:NEW.department_id,:NEW.salary); --INSERT
END IF;
END;
/
- 이벤트 로깅
CREATE OR REPLACE TRIGGER notify_reorder_rep
BEFORE UPDATE OF quantity_on_hand, reorder_point
ON inventories FOR EACH ROW
DECLARE
dsc product_descriptions.product_description%TYPE;
msg_text VARCHAR2(2000);
BEGIN
IF :NEW.quantity_on_hand <= :NEW.reorder_point THEN
SELECT product_description INTO dsc
FROM product_descriptions
WHERE product_id = :NEW.product_id;
msg_text := 'ALERT: INVENTORY LOW ORDER:'||'Yours,' ||CHR(10) ||user || '.'|| CHR(10);
ELSIF :OLD.quantity_on_hand >= :NEW.quantity_on_hand THEN
msg_text := 'Product #'||... CHR(10);
END IF;
UTL_MAIL.SEND('inv@oracle.com','ord@oracle.com', message=>msg_text, subject=>'Inventory Notice');
END;
/
Error -> Logic Error
-> Syntax Error
-> Runtime Error -> Oracle-defined Exception - Predefined excetion -> [1] when name then
(Exception) - Non-predefined excetion -> [2] put the name to exception then handling
-> [3] when others then
-> User-defined Exception -> [4] declare, raise, handling
-> [5] raise_application_error procedure
Predefined excetion
C:\Users\COM> cd C:\app\user\product\21c\dbhomeXE\rdbms\admin
C:\Users\COM> dir cat /W
C:\Users\COM> dir utl /W
C:\Users\COM> dir dbms /W
C:\Users\COM> dir prvt /W
C:\Users\COM> notepad stdspec.sql
but exceptions must be handled by name.
-> An exception raised in a declaration propagates immediately to the enclosing block.
-> An exception raised inside a handler propagates immediately to the enclosing block.
# Exception 처리하지 않을 경우 vs Exception 처리할 경우
(1) Exception 처리하지 않을 경우
drop table t1 purge;
create table t1 (no number);
begin
insert into t1 values (1000);
dbms_output.put_line(100/0);
insert into t1 values (2000);
end;
/
select * from t1;
(2) Exception 처리할 경우
begin
insert into t1 values (1000);
dbms_output.put_line(100/0);
insert into t1 values (2000);
exception
when zero_divide then
null;
end;
/
select * from t1;
rollback;
(3) Exception 발생과 상관없이 두번째 insert 문장 무조건 수행해야 한다면?
begin
begin
insert into t1 values (1000);
dbms_output.put_line(100/0);
exception
when zero_divide then
null;
end;
insert into t1 values (2000);
end;
/
select * from t1;
rollback;
# Exception 처리 방법 패턴 5가지
[1] when name then
create or replace procedure p1(a number, b number)
is
begin
dbms_output.put_line(a/b);
exception
when zero_divide then
dbms_output.put_line('0으로 나눌 수 없습니다!');
end;
/
exec p1(100, 2) -- 성공
exec p1(100, 0) -- 실패
[2] put the name to exception then handling
drop table t1 purge;
create table t1 (no number not null);
create or replace procedure insert_t1(a number)
is
begin
insert into t1 values(a);
end;
/
exec insert_t1(1000);
exec insert_t1(Null);
select * from t1;
↓↓
create or replace procedure insert_t1(a number)
is
e_null exception;
pragma exception_init(e_null, -1400);
begin
insert into t1 values(a);
exception
when e_null then
dbms_output.put_line('Null 값을 입력할 수 없습니다!');
end;
/
exec insert_t1(1000);
exec insert_t1(Null);
select * from t1;
↓↓
create or replace package pack_exceptions
is
e_null exception;
pragma exception_init(e_null, -1400);
end;
/
create or replace procedure insert_t1(a number)
is
begin
insert into t1 values(a);
exception
when pack_exceptions.e_null then
dbms_output.put_line('Null 값을 입력할 수 없습니다!');
end;
/
exec insert_t1(1000);
exec insert_t1(Null);
select * from t1;
[3] when others then
drop table t1 purge;
create table t1 (no number not null);
create or replace procedure insert_t1(a number)
is
begin
insert into t1 values(a);
exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
end;
/
exec insert_t1(1000);
exec insert_t1(Null);
select * from t1;
↓↓
drop table t_errors purge;
create table t_errors
(error_date date,
subprogram_name varchar2(30),
error_code varchar2(60),
error_message varchar2(60));
create or replace procedure insert_t1(a number)
is
v_error_code varchar2(60);
v_error_message varchar2(60);
begin
insert into t1 values(a);
exception
when others then
v_error_code := substr(sqlcode, 1, 30);
v_error_message := substr(sqlerrm, 1, 30);
insert into t_errors
values(sysdate, 'insert_t1', v_error_code, v_error_message );
end;
/
exec insert_t1(1000);
exec insert_t1(Null);
select * from t1;
select * from t_errors;
↓↓
drop table t_errors purge;
create table t_errors
(error_date date,
subprogram_name varchar2(30),
error_code varchar2(60),
error_message varchar2(60));
create or replace procedure insert_error_messages
(error_date in date,
subprogram_name in varchar2,
error_code in varchar2,
error_message in varchar2)
is
v_error_code varchar2(60);
v_error_message varchar2(60);
begin
v_error_code := substr(error_code, 1, 30);
v_error_message := substr(error_message, 1, 30);
insert into t_errors
values(sysdate, subprogram_name, v_error_code, v_error_message );
end;
/
create or replace procedure insert_t1(a number)
is
begin
insert into t1 values(a);
exception
when others then
insert_error_messages(sysdate, 'insert_t1', sqlcode, sqlerrm);
end;
/
exec insert_t1(1000);
exec insert_t1(Null);
select * from t1;
select * from t_errors;
[4] declare, raise, handling
create or replace procedure p1(a number)
is
v_sal employees.salary%type;
e_too_low exception;
begin
select salary into v_sal
from employees
where employee_id = a;
if v_sal < 10000 then
raise e_too_low;
end if;
dbms_output.put_line(a||' 사원의 급여는 '||v_sal||'입니다.');
exception
when e_too_low then
dbms_output.put_line(a||' 사원의 급여가 최저 급여미만입니다. 확인해보세요!');
end;
/
exec p1(100)
exec p1(141)
[5] raise_application_error procedure
- 사용자 정의 에러를 마치 Oracle의 Non-predefined excetion처럼 사용할 수 있게 하는 프로시져
- https://docs.oracle.com/database/121/LNPLS/errors.htm#GUID-48F88C61-8CE9-4821-91CB-48A8F1BC09E1
create or replace procedure p1(a number)
is
v_sal employees.salary%type;
begin
select salary into v_sal
from employees
where employee_id = a;
if v_sal < 10000 then
RAISE_APPLICATION_ERROR(-20111, a||' 사원의 급여가 최저 급여미만입니다. 확인해보세요!');
end if;
dbms_output.put_line(a||' 사원의 급여는 '||v_sal||'입니다.');
end;
/
exec p1(100)
exec p1(141)
↓↓
create or replace procedure p1(a number)
is
v_sal emp.sal%type;
e_too_null exception;
pragma exception_init(e_too_null, -20111);
begin
select sal into v_sal
from emp
where empno = a;
if v_sal < 1000 then
RAISE_APPLICATION_ERROR(-20111, a||' 사원의 급여가 최저 급여미만입니다. 확인해보세요!');
end if;
dbms_output.put_line(a||' 사원의 급여는 '||v_sal||'입니다.');
exception
when e_too_null then
dbms_output.put_line('예외 발생');
end;
/
exec p1(7369)
cf.트리거(Trigger)를 이용해서 특정 IP 접속 제한하기
http://orapybubu.blog.me/40025984303
C:\Users\COM> sqlplus system/oracle@localhost:1521/xepdb1
SQL> CREATE OR REPLACE TRIGGER LOGON_TRI
after LOGON ON ACE.SCHEMA
BEGIN
if SUBSTR(sys_context('USERENV', 'IP_ADDRESS'), 1, 7) in ('127.0.0', '219.241') then
RAISE_APPLICATION_ERROR ( -20002, 'IP '||ORA_CLIENT_IP_ADDRESS
|| ' is not allowed to connect database as ACE!');
END IF;
END;
/
SQL> exit
C:\Users\COM> sqlplus ace/me@localhost:1521/xepdb1
ORA-04088: 트리거 'SYSTEM.LOGON_TRI'의 수행시 오류
ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다
ORA-20002: IP 127.0.0.1 is not allowed to connect database as ACE!
C:\Users\COM> sqlplus system/oracle@localhost:1521/xepdb1
SQL> drop trigger LOGON_TRI;
SQL> exit
C:\Users\COM> sqlplus ace/me@localhost:1521/xepdb1
Cursor
A cursor is a name or handle to a specific private SQL area.
Oracle Memory Architecture
Cursor의 종류
-> implicit cursor : 한 건 리턴 select, insert, update, delete, merge
-> explicit cursor : 두 건 이상 리턴 select
create or replace procedure p1(a number)
is
v_salary number;
begin
select salary into v_salary
from employees
where department_id = a;
end;
/
exec p1(10) <- 에러 : ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다
exec p1(60) <- 에러 : ORA-01403: 데이터를 찾을 수 없습니다.
create or replace procedure p1(a number)
is
CURSOR nemam
IS
select salary
from employees
where department_id = a;
begin
open nemam;
end;
/
exec p1(10)
exec p1(60)
Cursor 속성
-> implicit cursor
sql%isopen
sql%found
sql%notfoud
sql%rowcount
-> explicit cursor
커서명%isopen
커서명%found
커서명%notfoud
커서명%rowcount
# implicit cursor 예제
예제> 암시적 커서 속성 사용 예제
drop table sawon purge;
create table sawon
as
select * from employees;
begin
update sawon
set salary = salary * 1.3
where department_id = 30;
if sql%rowcount = 0 then
p.p('수정된 행이 없습니다');
else
p.p(sql%rowcount||'행이 수정되었습니다');
end if;
delete from sawon
where department_id = 20;
if sql%rowcount = 0 then
p.p('삭제된 행이 없습니다');
else
p.p(sql%rowcount||'행이 삭제되었습니다');
end if;
end;
/
# Explicit cursor 예제 : declare -> open -> fetch -> close
create or replace procedure p1(a emp.department_id%type)
is
CURSOR emp_cursor
IS
select empno, ename, salary, job, hiredate
from employees
where department_id = a
order by salary desc;
r emp_cursor%rowtype;
begin
if not(emp_cursor%isopen) then
OPEN emp_cursor; /* 서버 내부에 active set이 생성 */
end if;
loop
FETCH emp_cursor INTO r;
exit when emp_cursor%notfound;
p.p(r.empno||' '||r.salary||' '||r.job);
end loop;
CLOSE emp_cursor;
end;
/
exec p1(10)
exec p1(30)
↓↓
/* Cursor for loop */
create or replace procedure p1(a emp.department_id%type)
is
CURSOR emp_cursor
IS
select empno, ename, salary, job, hiredate
from employees
where department_id = a
order by salary desc;
begin
for r in emp_cursor loop /* open, fetch */
p.p(r.empno||' '||r.salary||' '||r.job);
end loop; /* close */
end;
/
exec p1(10)
exec p1(30)
↓↓
/* 서브쿼리를 이용한 Cursor for loop */
create or replace procedure p1(a emp.department_id%type)
is
begin
for r in (select empno, ename, salary, job, hiredate
from employees
where department_id = a
order by salary desc) loop
p.p(r.empno||' '||r.salary||' '||r.job);
end loop;
end;
/
exec p1(10)
exec p1(30)
# Cursor for loop 예제 하나 더
create or replace procedure p1
is
begin
for d in (select * from dept order by department_id) loop
p.p(d.department_id);
for e in (select * from employees where department_id = d.department_id order by salary desc) loop
p.p(e.empno||', '||e.ename);
end loop;
p.p('-----------');
end loop;
end;
/
exec p1
# Cursor variable
- Cursor Variables
https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/static-sql.html#GUID-4A6E054A-4002-418D-A1CA-DE849CD7E6D5
(1) 강한 타입의 커서 변수는 반환형만 일치하면 어떤 SELECT문도 OPEN 할 수 있다
(2) 약한 타입의 커서 변수는 반환형이 서로 다른 쿼리에 대해서도 사용할 수 있다
(3) 커서 변수는 서브프로그램의 매개변수로 사용할 수 있다
- Enhancing the Application: Advanced JDBC Features
https://docs.oracle.com/database/121/TDPJD/addfunc.htm#TDPJD206
REM 강한 타입의 커서 변수는 반환되는 칼럼의 개수와 타입만 일치하면
REM 어떤 SELECT 문에 대해서도 OPEN 가능하다.
create or replace procedure sp_strong_type_cursor_variable
is
type emp_record_type is record
(empno emp.empno%type,
ename emp.ename%type,
salary emp.salary %type);
type emp_cursor_type is ref cursor
return emp_record_type;
v_empcur emp_cursor_type; /* 강한 타입의 커서 변수 */
v_emprec emp_record_type;
begin
/* 첫번째 SQL문에 대해 커서 변수를 OPEN */
open v_empcur
for
select empno, ename, salary
from employees
where department_id = 10;
loop
fetch v_empcur into v_emprec ;
exit when v_empcur%notfound ;
dbms_output.put_line('EMPNO='||v_emprec.empno||', ENAME='||v_emprec.ename||', salary='||v_emprec.salary);
end loop ;
close v_empcur ;
dbms_output.put_line('--------------') ;
/* 두번째 SQL문에 대해 커서 변수를 OPEN */
open v_empcur
for
select empno, ename, salary+nvl(comm,0)
from employees
where department_id = 20;
loop
fetch v_empcur into v_emprec ;
exit when v_empcur%notfound ;
dbms_output.put_line('EMPNO='||v_emprec.empno||', ENAME='||v_emprec.ename||', salary='||v_emprec.salary);
end loop ;
close v_empcur ;
end;
/
exec sp_strong_type_cursor_variable
REM 커서 변수는 서브 프로그램의 매개 변수로 사용할 수 있다.
create or replace procedure sp_cusor_variable_parameter
is
type emp_rec is record
(empno emp.empno%type,
ename emp.ename%type);
type emp_cursor_type is ref cursor
return emp_rec;
v_empcur emp_cursor_type; /* 강한 타입의 커서 변수 */
procedure print_emp /* 로컬 서브프로그램 */
(a_empcur in emp_cursor_type)
is
v_emprec emp_rec;
begin
loop
fetch a_empcur into v_emprec ;
exit when a_empcur%notfound;
dbms_output.put_line('EMPNO=' ||v_emprec.empno||', ENAME=' || v_emprec.ename);
end loop;
end;
begin
open v_empcur
for
select empno, ename
from employees;
print_emp(v_empcur);
close v_empcur;
end;
/
exec sp_cusor_variable_parameter
REM 약한 타입의 커서 변수는 반환형이 서로 다른 쿼리에 대해서도 사용할 수 있다
create or replace procedure sp_weak_type_cursor_variable
is
v_cursor sys_refcursor; /* 약한 타입의 커서 변수 */
v_selector char;
v_department_id number;
procedure open_cursor
(a_cursor in out sys_refcursor,
a_selector in char,
a_department_id in number)
is
begin
if a_selector = 'E' then
open a_cursor for select * from employees where department_id = a_department_id;
else
open a_cursor for select * from dept where department_id = a_department_id;
end if ;
end;
procedure print_cursor
(a_cursor in out sys_refcursor,
a_selector in char)
is
v_emprec emp%rowtype;
v_deptrec dept%rowtype;
begin
if a_selector = 'E' then
loop
fetch a_cursor into v_emprec; -- emp 테이블의 모든 칼럼을 레코드에 담음
exit when a_cursor%notfound;
dbms_output.put_line('EMPNO='||v_emprec.empno||', ENAME='||v_emprec.ename||', JOB='||v_emprec.job ||', salary=' ||v_emprec.salary);
end loop;
else
loop
fetch a_cursor into v_deptrec; -- dept 테이블의 세 칼럼을 레코드에 담음
exit when a_cursor%notfound;
dbms_output.put_line('department_id='||v_deptrec.department_id||', DNAME='||v_deptrec.dname||', LOC=' ||v_deptrec.loc);
end loop;
end if;
end;
begin
-- DEPT 테이블 출력
v_selector := 'D';
v_department_id := 10;
open_cursor (v_cursor, v_selector, v_department_id); -- 커서 OPEN
print_cursor(v_cursor, v_selector); -- 커서 출력
close v_cursor;
dbms_output.put_line('----');
-- EMP 테이블 출력
v_selector := 'E';
v_department_id := 10;
open_cursor (v_cursor, v_selector, v_department_id); -- 커서 OPEN
print_cursor(v_cursor, v_selector); -- 커서 출력
close v_cursor;
end;
/
exec sp_weak_type_cursor_variable