MySQL Information and Resources |
Custom Search
|
The following example is for creating a simple select 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_SELECT_BYPK
(
p_STUDENT_ID IN STUDENTS.STUDENT_ID ,
p_ACTIVE_FLG OUT STUDENTS.ACTIVE_FLG ,
p_LASTNAME OUT STUDENTS.LASTNAME ,
p_FIRSTNAME OUT STUDENTS.FIRSTNAME ,
p_MARITAL_STATUS_CODE OUT STUDENTS.MARITAL_STATUS_CODE
)
AS
BEGIN
SELECT
ACTIVE_FLG ,
LASTNAME ,
FIRSTNAME ,
MARITAL_STATUS_CODE
INTO
p_ACTIVE_FLG ,
p_LASTNAME ,
p_FIRSTNAME ,
p_MARITAL_STATUS_CODE
FROM STUDENTS
WHERE STUDENT_ID = p_STUDENT_ID ;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001,
p_STUDENT_ID || ':$:' || SQLERRM, TRUE) ;
END SP_STUDENTS_SELECT_BYPK ;
/
You will note that just like in a SQL SELECT or a SQL VIEW you do not have to use all of the columns available when creating a stored procedure. Additionally, for this stored procedure I used the primary key of the table as the means of access (the WHERE clause). You can write a stored procedure that uses an alternate key as a means of access.
To run the stored procedure you need to supply a value to the student_id variable. The "WHERE" clause in the code above maps this variable to the student_id column in the table.
P_STUDENT_ID = 23 ------------------------------- P_MARITAL_STATUS_CODE OUT VARCHAR2 P_FIRSTNAME OUT VARCHAR2 P_LASTNAME OUT VARCHAR2 P_ACTIVE_FLG OUT NUMBER P_STUDENT_ID IN NUMBER #OBJECTTYPE=PROCEDURE# #OWNER=COURSEREGISTRATION# #OBJECT=SP_STUDENTS_SELECT_BYPK# #H2DB-END-OF-PROC#
=============================== P_MARITAL_STATUS_CODE = null =============================== P_FIRSTNAME = Harley =============================== P_LASTNAME = Sazuki =============================== P_ACTIVE_FLG = 1
Link to schema for Students table