Collapse All 
Expand All- Stored Procedures
- Views
- Table Basics (Schema)
The following example is for creating a simple insert 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_INSERT_BYPK
(
p_STUDENT_ID IN STUDENTS.STUDENT_ID ,
p_PASSWORD IN STUDENTS.PASSWORD ,
p_ACTIVE_FLG IN STUDENTS.ACTIVE_FLG ,
p_LASTNAME IN STUDENTS.LASTNAME ,
p_BIRTH_DTTM IN STUDENTS.BIRTH_DTTM ,
p_GPA IN STUDENTS.GPA ,
p_IS_ON_STAFF IN STUDENTS.IS_ON_STAFF
)
AS
BEGIN
INSERT INTO STUDENTS
(
STUDENT_ID ,
PASSWORD ,
LASTNAME ,
ACTIVE_FLG ,
BIRTH_DTTM ,
GPA ,
IS_ON_STAFF
)
VALUES
(
p_STUDENT_ID ,
p_PASSWORD ,
p_LASTNAME ,
p_ACTIVE_FLG ,
p_BIRTH_DTTM ,
p_GPA ,
p_IS_ON_STAFF
) ;
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001,
p_STUDENT_ID || ':$:' ||
p_PASSWORD || ':$:' ||
p_ACTIVE_FLG || ':$:' ||
p_LASTNAME || ':$:' ||
p_BIRTH_DTTM || ':$:' ||
p_GPA || ':$:' ||
p_IS_ON_STAFF || ':$:' ||
SQLERRM, TRUE) ;
END sp_STUDENTS_INSERT_BYPK ;
/
You will note that just like in a INSERT statement you do not have to use all of the columns available when creating a stored procedure. You must however populate all columnar data associated with the PK (primary key), and columns associated with unique indexes (note: there are exceptions to this, but they will not addressed here), and columns defined in the ddl as "NOT NULL".
To run the insert stored procedure you need to supply a value to the student_id variable as well as populate all required data. In this example I have included a cross section of columns for your reference, as well as the datatype associated with the columns in our SQL INSERT.
P_GPA = 4 P_BIRTH_DTTM = 2007-01-09 P_IS_ON_STAFF = 1 P_LASTNAME = Frapples P_ACTIVE_FLG = 1 P_PASSWORD = chuckiecheese P_STUDENT_ID = 25 ------------------------------- P_GPA IN NUMBER P_BIRTH_DTTM IN DATE P_IS_ON_STAFF IN NUMBER P_LASTNAME IN VARCHAR2 P_ACTIVE_FLG IN NUMBER P_PASSWORD IN VARCHAR2 P_STUDENT_ID IN NUMBER #OBJECTTYPE=PROCEDURE# #OWNER=COURSEREGISTRATION# #OBJECT=SP_STUDENTS_INSERT_BYPK# #H2DB-END-OF-PROC#
A few thing to point out regarding the code above. Datetime and character data is entered using single quoted string. Integer data is entered without quotes. I also included all columns that were defined as NOT NULL. I also included the password column which is part of a unique index. You will note that I did not include columns for some of the non-unique indexes (SOC_SEC_NUM, OTHER_ID_NUM, and DRIVER_LICENSE_NUM). I did this intentionally to demonstrate that they are not required columns. Having said this, in the "real world" one would only put indexes on columns where the intent was to actually collect the data for that column. I just wanted to make a technical point here. The pragmatic point is that you would want to expose columns that are part of indexes.
I want to call you attention to the fact that all columns that are not referenced in the query get set to null. Also, if the schema had default values defined these would also get stored for that column when the rows gets inserted.
STUDENT_ID = 25
PASSWORD = 'chuckiecheese'
EMPLOYEE_ID_FK = null
CUSTOMER_ID_FK = null
ACTIVE_FLG = 1
LASTNAME = 'Frapples'
FIRSTNAME = null
MIDDLENAME = null
PREFIX_NAME = null
SUFFIX_NAME = null
GENDER_CODE = null
BIRTH_DTTM = TO_DATE('2007-01-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
BIRTH_CITY_NM = null
BIRTH_STATE_CODE = null
BIRTH_COUNTRY_CODE = null
GPA = 4
IS_ON_STAFF = 1
SOC_SEC_NUM = null
DRIVER_LICENSE_NUM = null
DRIVER_LICENSE_STATE_CODE = null
DRIVER_LICENSE_COUNTRY_OCDE = null
OTHER_ID_NUM = null
CITIZEN_COUNTRY_CODE = null
MARITAL_STATUS_CODE = null
RACE_CODE = null
ETHNIC_GROUP_CODE = null
VETERAN_MILITARY_STATUS_CODE = null
FIN_ALERT_CODE = null
INACTIVE_DATE = null
INACTIVE_REASON = null
EMPLOYEE_INACTIVATING = null
