Another cursor example

1
Another cursor example

• Question 4 (page 572)

SQL create table custlist 2 (Cname
varchar2(100)) Table created.
1 declare 2 name varchar2(100) 3
cursor get_names is 4 select first '
'MI' 'last from customer 5 begin 6
open get_names 7 loop 8 fetch
get_names into name 9 exit when
get_namesNOTFOUND 10 insert into
custlist values(name) 11
dbms_output.put_line('The name inserted is '
name) 12 end loop 13 close get_names
14 end 15 /
2
Run program
The name inserted is Paula E Jones The name
inserted is Mitch M Edwards The name inserted
is Betty H Sorenson The name inserted is Lee
Miller The name inserted is Alissa R
White PL/SQL procedure successfully
completed. SQL commit Commit complete. SQL
select from custlist CNAME -------------------
--------------------- Mitch M Edwards Betty H
Sorenson Lee Miller Alissa R White Paula E Jones
3
Error handling

• Exception a method of processing errors and
  informing users
• General format
• EXCEPTION
• WHEN exception_name THEN
• set of statements 1
• WHEN exception_name THEN
• set of statements 2
• WHEN OTHERS THEN
• set of statements 3
• END

4
1 declare 2 e_too_many_students
exception 3 v_csecid number(8) 4
v_currenrl number(4) 5 v_maxenrl
number(4) 6 cursor get_info is 7
select csecid, currenrl,maxenrl from
course_section 8 begin 9 open
get_info 10 loop 11 fetch
get_info into v_csecid,v_currenrl,v_maxEnrl 12
exit when get_infoNOTFOUND 13
if v_currenrl v_maxenrl then 14
raise e_too_many_students 15 end if
16 end loop 17 close
get_info 18 exception 19
when e_too_many_students then 20
insert into log_table(info) 21
values ('Course 'v_csecid' has
'v_currenrl 22 ' students. It
exceeds the limit ' v_maxenrl) 23
when others then 24
dbms_output.put_line('Error occurred. '
SQLERRM) 25 end SQL /
5
Functions and procedures

• Subprograms
• Functions must return 1 and only 1 value
• Procedures return 0 or more than 1 values

1 create or replace procedure add_new_student
( 2 first student.sfnameTYPE, 3 mi
student.smiTYPE, 4 last student.slnameTYPE,
5 pin student.spinTYPE) as 6 begin 7
insert into student(sid,sfname,smi,slname,spin)
8 values(sid_sequence.nextval,first,mi,last,pi
n) 9 commit 10 end SQL / Procedure
created. SQL exec add_new_student('John', 'O',
'Doe', 1000)
6
1 create or replace function classinfo ( 2
p_csecid course_section.csecidTYPE) 3 return
varchar2 is 4 v_currenrl course_section.currenr
lTYPE 5 v_maxenrl course_section.maxenrlTYPE
6 v_percent_full number 7 begin 8
select currenrl,maxenrl 9 into
v_currenrl,v_maxenrl 10 from course_section
11 where csecidp_csecid 12
v_percent_full v_currenrl/v_maxenrl 100 13
if v_percent_full 100 then 14
return ('Over Full') 15 elsif
v_percent_full 100 then 16 return
('Full') 17 elsif v_percent_full 80 then
18 return ('Some room') 19
elsif v_percent_full 60 then 20
return ('More room') 21 elsif
v_percent_full 0 then 22 return
('Lots of room') 23 else return ('Empty')
24 end if 25 end SQL / Function created.
SQL set serveroutput on SQL declare status
varchar2(25) 2 begin 3 status
classinfo(1000) 4 dbms_output.put_line('The
status of 1000 is ' status) 5 end 6
/ The status of 1000 is Over Full