[Oracle] 저장 프로시저와 함수

test2

create or replace procedure test2
(v_stu_no in student.stu_no%type,
v_stu_grade in student.stu_grade%type)
is
begin
 update student
 	set stu_grade = v_stu_grade
    where stu_no = v_stu_no ; 
end test2; 
/

 

test3

create or replace procedure test3
(v_stu_no in student.stu_no%type,
v_stu_name out student.stu_name%type)
is 
begin
	select stu_name
    	into v_stu_name
        from student
        where stu_no = v_stu_no ; 
end test3; 
/

variable d_stu_name varchar2(12);
exec test3(20153075, :d_stu_name);
print d_stu_name;

 

test4

create or replace procedure test4
(v_sub_no in enrol.sub_no%type,
v_stu_no in enrol.stu_no%type, 
v_enr_grade in out enrol.enr_grade%type)
is 
begin
	update enrol
    	set enr_grade = enr_grade + v_enr_grade
        where stu_no = v_stu_no and sub_no = v_sub_no ; 
    select enr_grade
    	into v_enr_grade
        from enrol
        where stu_no = v_stu_no and sub_no = v_sub_no ; 
end test4;
/

variable d_enr_grade number
begin :d_enr_grade :=10; end;
/

execute test4(101, 20131001, :d_enr_grade);

print d_enr_grade;

 

test5

create sequence seq1
increment by 1
start with 201
maxvalue 999;

create procedure test5
(v_sub_name in subject.sub_name%type,
v_sub_prof in subject.sub_prof%type, 
v_sub_grade in subject.sub_grade%type, 
v_sub_dept in subject.sub_dept%type)
is
begin
	insert
    	into subject
        values (seq1.nextval, v_sub_name, v_sub_prof, v_sub_grade, v_sub_dept) ; 
    commit ; 
end test5 ; 
/

 

cursor 정의 -> cursor open -> cursor fetch

커서 : 프로시저의 단위

        select문의 결과가 복수 행일 경우 사용

 

count(컬럼) <== null 은 제외됨 

 

4. 예외처리 

create or replace procedure test10
	(v_stu_no in student.stu_no%type)
is
	v_stu_name	student.stu_name%type;
begin
	select stu_name
    	into v_stu_name
        from student
        where stu_no = v_stu_no ;
        dbms_output.put_line(v_stu_name);
    exception 
    	when no_data_found then
        	dbms_output.put_line('해당 데이터가 없습니다.');
end test10;
/

create or replace procedure test11
	(v_sub_no in enrol.sub_no%type)
is 
	v_cnt	number;
    cnt_error	exception;
begin
	select count(stu_no)
    	into v_cnt
        from enrol
        where sub_no = v_sub_no ;
        dbms_output.put_line(v_sub_no||' 과목 수강자는 '||v_cnt||'명 입니다.');
    if v_cnt = 0 then
    	raise cnt_error;
    end if;
    exception
    	when cnt_error then
        	dbms_output.put_line('수강자가 없습니다.');
end test11;
/

적절한 execute명령을 실행했을 때, 결과가 보이지 않는 경우, 

set serveroutput on; 

을 해주면 된다.