Oracle Stored Procedure - DELETE - Example
Source code to create and add sql delete stored procedure to catalog
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 ; /
Executing the sql delete stored procedure
Execute sql delete stored procedure
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#