1CREATE [OR REPLACE] PROCEDURE procedure_name
2 [ (parameter [,parameter]) ]
3
4IS
5 [declaration_section]
6
7BEGIN
8 executable_section
9
10[EXCEPTION
11 exception_section]
12
13END [procedure_name];
1CREATE OR REPLACE Procedure UpdateCourse
2 ( name_in IN varchar2 )
3
4IS
5 cnumber number;
6
7 cursor c1 is
8 SELECT course_number
9 FROM courses_tbl
10 WHERE course_name = name_in;
11
12BEGIN
13
14 open c1;
15 fetch c1 into cnumber;
16
17 if c1%notfound then
18 cnumber := 9999;
19 end if;
20
21 INSERT INTO student_courses
22 ( course_name,
23 course_number )
24 VALUES
25 ( name_in,
26 cnumber );
27
28 commit;
29
30 close c1;
31
32EXCEPTION
33WHEN OTHERS THEN
34 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
35END;
1
2
3
4
5 CREATE OR REPLACE PROCEDURE print_contact(
6 in_customer_id NUMBER
7)
8IS
9 r_contact contacts%ROWTYPE;
10BEGIN
11 -- get contact based on customer id
12 SELECT *
13 INTO r_contact
14 FROM contacts
15 WHERE customer_id = p_customer_id;
16
17 -- print out contact's information
18 dbms_output.put_line( r_contact.first_name || ' ' ||
19 r_contact.last_name || '<' || r_contact.email ||'>' );
20
21EXCEPTION
22 WHEN OTHERS THEN
23 dbms_output.put_line( SQLERRM );
24END;
25