Creating Stored Procedures, Functions, and Packages
Procedures and functions
CREATE OR REPLACE PROCEDURE name (params) IS ... BEGIN ... END; - a function adds RETURN datatype and must execute a RETURN on every path, otherwise ORA-06503 occurs at run time. Parameter modes are IN (default), OUT and IN OUT; OUT and IN OUT arguments must be actual variables - passing a literal or expression raises PLS-00363.
Functions called from SQL
A function used inside a SQL statement must not modify database state and must not have OUT or IN OUT parameters. It must also return a SQL datatype, so BOOLEAN is rejected. Violating these rules makes the calling statement fail (ORA-14551 and friends) and the statement is rolled back.
Packages
A package has a specification (public declarations) and a body (implementation plus private items). Only what the spec declares is callable from outside. The body may end with an initialisation section - a BEGIN ... END; after the last subprogram - which runs once per session on first reference. Package state (variables, open cursors) survives for the whole session, but is reset when the package is recompiled or the session ends.
Traps
Changing a package spec invalidates every dependent object; changing only the body does not. Overloading requires different formal parameter types or counts - you cannot overload on return type alone.
Rule of thumb: declare in the spec only what callers truly need; everything else stays in the body and remains private.