MIS 325 Lecture Notes - Lecture 27: Transaction Processing System, Stored Procedure, Sql
24 May 2018
School
Department
Course
Professor
MIS 325 – Procedures – 3.4
• Why procedures?
o So that you do not have to keep rewriting code
o Procedures do repetitive tasks
• Parameters for procedures
o Input parameters cannot be changed
▪ Input cannot change value during a procedure
o Output can be changed
• Syntax
o CREATE [OR REPLACE] PROCEDURE procedure_name
o [(
▪ parametername1, datatype
o )]
o {IS|AS}
o PL/SQL Block
• 3 ways to get stored procedure
o call statement
▪ CALL procedure name(parameters)
▪ ‘ ‘ around number because varchar
o write a script
▪ BEGIN
• Procedure name (parameters)
▪ END
o Script that passes parameters by name
▪ BEGIN
• Procedure name(
o Parameter and value
o Parameter and value)
▪ END
• Functions vs procedures
o To be part of a table, use function
o To update table, use procedures
o Functions used in class
▪ Aggregate
• Sum
• Min
o Works with
▪ String
▪ Dates
▪ numbers
• Max
o User defined functions
▪ Very similar to stored procedure
▪ Except:
• Always returns a value
find more resources at oneclass.com
find more resources at oneclass.com
