Collapse All 
Expand All- Stored Procedures
- Views
- Table Basics (Schema)
The following example is for creating a simple delete stored procedure. You can run it through an explicit call from a host language program or directly from a DBMS query execution shell like dbOrchestra.
CREATE OR REPLACE PROCEDURE SP_STUDENTS_DELETE_BYPK
(
p_STUDENT_ID IN STUDENTS.STUDENT_ID%TYPE
)
AS
BEGIN
DELETE
FROM STUDENTS
WHERE STUDENT_ID = p_STUDENT_ID ;
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001,
p_STUDENT_ID || ':$:' || SQLERRM, TRUE) ;
END SP_STUDENTS_DELETE_BYPK ;
/
To run the stored procedure you need to supply a values to the applicable variables.
P_STUDENT_ID = 25 ------------------------------- P_STUDENT_ID IN NUMBER #OBJECTTYPE=PROCEDURE# #OWNER=COURSEREGISTRATION# #OBJECT=SP_STUDENTS_DELETE_BYPK# #H2DB-END-OF-PROC#
