Procedures & Functions

Question 1

Different types of Functions

Answer 1

Aggregate Function

Scalar Functions

User Defined Functions (UDF)

Question 2

Aggregate Function

Answer 2

Returns a single calculated value.
Examples:
AVG()           LAST()         SUM()
COUNT()     MIN()
FIRST()         MAX()

Question 3

Scalar Function

Answer 3

Returns a single value based on input.
Examples:
UCASE()            LEN()          FORMAT()
LCASE()             ROUND()   MID()
NOW()

Question 4

User Defined Functions

Answer 4

UDF Used for complex processing

Usually Input/Output

Question 5

Anonymous Block

Answer 5

An unnamed sequence of actions. Since they are not named they are not stored in the DB

Question 6

Stored Procedure

Answer 6

A named PL/SQL block which performs one or more specific tasks for repeated usage.

Has a header and a body
Header contains name of proc and variables or pram to passed to the proc
Body consists of declaration section, execution section and exception section

Question 7

PL/SQL Function
Stored Function (user or user defined function)

Answer 7

Set of PL/SQL statements call by name
Like a stored procedure but can only return 1 value

User function can be used in an SQL statement

Question 8

Deterministic Function

Answer 8

Always returns the same result for a specific input value

If you specify DETERMINISTIC improves the performance the second time called with the same parameter. No need to execute again for the result

Question 9

Package

Answer 9

A schema object that groups logically PL/SQL Types, items, and sub programs. Packages usually have two parts
Specifications (spec)
Body

A body is not always necessary

Question 10

Package Specification

Answer 10

Interface to your application
Declares the types, variables, constants, exceptions, cursors and sub programs available for use

Procedure and function declaration
Global declaration area

Question 11

Package Body

Answer 11

Fully defines cursors and sub programs and implements the specifications

Procedure and Function definition and exception handling
Local declaration

Question 12

Why would you use a Package

Answer 12

Organized code management- easy to locate
Easy (top down design) application design.
Painless implementation changes
Security and maintain ability through private code
New functionality session wide persistence of public variables
Better Performance first time entire package loaded into memory later calls better performance