MIS 325 Lecture Notes - Lecture 17: Stored Procedure, Sql, Data Type
2 May 2018
School
Department
Course
Professor
● Creating a procedure:
○ CREATE [OR REPLACE] PROCEDURE procedure_name
○ (
■ Parameter IN/OUT/IN OUT Data type,
■ Parameter IN/OUT/IN OUT Data type
○ )
○ AS/IS
■ Stored variable Data type;
○ PL/SQL block
● CALL procedure(parameters): Executes a stored procedure
● Parameters:
○ Used to pass a value to the stored procedure from a calling program
○ Can also be used to pass a value back from the stored procedure to the calling
program
○ Passing parameters by position:
■ Must code the parameters in the same order as they are coded in the
CREATE PROCEDURE statement
■ procedure(parameter, parameter);
○ Passing parameters by name:
■ Can list parameters in any order
■ procedure(parameter => value, parameter => value);
○ Types of parameters
■ Input Parameter:
● Passed to the stored procedure from the calling program
● Cannot change value of the parameter
● Parameters are input parameters by default
● IN keyword (optional)
■ Output Parameter:
● Returned to the calling program from the stored procedure
● Values must be set by the body of the stored procedure
● Must code OUT keyword
■ Input/Output Parameter:
● Stores an initial value that's passed in from the calling program
like an input parameter
● Can also change the value and return it to a calling program like
an output parameter
● Code IN OUT keywords
● DROP PROCEDURE procedure_name: Drops a procedure
● User-Defined Function (UDF, Stored Function, Function):
○ Similar to a stored procedure
○ Always returns a value or a table
○ Can’t make changes to the database by using INSERT, UPDATE, or DELETE
statements
