Named Program Units
PL/SQL allows you to create a variety of named program units, or containers for code. These include:
- Procedure
A program that executes one or more statements
- Function
A program that returns a value
- Package
A container for procedures, functions, and data structures
- Trigger
A program that executes in response to database changes
- Object type
Oracle’s version of an object-oriented class; object types can contain member procedures and functions
Procedures
Procedures are program units that execute one or more statements and can receive or return zero or more values through their parameter lists. The syntax of a procedure is:
CREATE [OR REPLACE] PROCEDUREname
[ (parameter
[,parameter
]) ] [AUTHID { CURRENT_USER | DEFINER } ] [DETERMINISTIC] { IS | AS }declaration_section
BEGINexecutable_section
[EXCEPTIONexception_section
] END [name
];
A procedure is called as a standalone executable PL/SQL statement:
apply_discount(new_company_id, 0.15);
Functions
Functions are program units that execute zero or more statements and return a value through the RETURN clause. Functions can also receive or return zero or more values through their parameter lists. The syntax of a function is:
CREATE [OR REPLACE] FUNCTIONname
[ (parameter
[,parameter
]) ] RETURNreturn_datatype
[AUTHID { CURRENT_USER | DEFINER } ] [DETERMINISTIC] [PARALLEL_ENABLE] [PIPELINED] [AGGREGATE USING] { IS | AS } [declaration_section
] BEGINexecutable_section
[EXCEPTIONexception_section
] END [name
];
A function must have at ...
Get Oracle PL/SQL Language Pocket Reference, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.