FINAL

The %______________ attribute lets you declare a record that represents a row in a table or view. For each column in the referenced table or view, the record has a field with the same name and data type. - If the referenced item table or view changes, your declaration is automatically updated.

%ROWTYPE

%__________ is used to declare variables with relation to the data type of a column in an existing table. * Always inherits the data type of the referenced item.

%TYPE

Block Structure EX: DECLARE /* data */ BEGIN /* regular, executable code */ EXCEPTION /* exception code */ END;

Anonymous blocks don't have names, (Reside in the SQL Buffer)

___________ variables are real variables (Have type and length) ____________ variables let us bridge SQL*Plus and PL/SQL ____________ variable name in SQL*Plus environment is preceded by a colon (:) when used in SQL statements

Bind Variables

You can't execute or run a package - It's just a container for code elements - It's primarily useful for object re-use and data hiding (roughly, encapsulation) Use dot notation to reference objects -Outside the package, use package_name.element_name -Inside the package, no need for the package_name

Calling Packaged Elements

Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement; for example, the number of rows processed, etc. A ___________ is a pointer to this context area, which holds the rows (one or more) returned by a SQL statement.

Cursor

Cursor For Loop: FOR cursorvariable IN cursor LOOP END LOOP; -No open or fetch commands -No close cursor command

Cursor For Loop

3 standard structures of a Named/Anonymous Block: * _____________: Define and initialize cursors and variables used in the block (optional) *____________ commands: Perform the processing of the block// only block required *____________ Handling: Handle errors that occur in processing (optional)

Declarations Executable Commands Exception Handling

____________ Processing = - Begins with keyword exception - Optional section - Can handle specific errors

Exception Processing

For Loops: FOR var IN start .. end LOOP END LOOP; FOR var IN REVERSE start .. end LOOP END LOOP;

For Loops

A _________________ is same as a procedure except that it returns a value// single value. A standalone ____________ is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows − CREATE [OR REPLACE] FUNCTION (function_name) [(parameter_name [IN | OUT | IN OUT] type [, ...])] RETURN return_datatype {IS | AS} BEGIN < function_body > END [function_name];

Function

Function Return Datatype: * Function can return any PL/SQL datatype - The only exception is Exceptions * The return type needs to be consistent with the RETURN portion of the function header * Will return a single value to the calling variable - e.g. local_var := fn_name(param1, param2); will place the results of the function call in the local variable local_var

Function Return Datatype

Local ____________ is a named procedure or function defined in declaration area of a PL/SQL block (named or anonymous) Scope of local _________ is the PL/SQL block it's defined in (Can't be called outside of the block) Local __________ need to be declared after all other declarations in the section

Local Module

Declaring Cursors: CURSOR your_cursor_name (optional parameters) IS select_statement_goes_here [FOR UPDATE OF col, name, list];

MEMBER THISS

___________ Blocks are blocks that may be nested in the BEGIN and EXCEPTION parts of a block. - Local subprograms may be defined in the DECLARE part of a block.

Nested

___/______ is... Procedural Language superset of SQL *Oracle specific *Portable across PL/SQL environments *Actually can port to IBM DB2 nowadays Block structured *Each block has certain capabilities/responsibilities *Blocks can be nested *Named block

PL/SQL

Parameter in Stored Procedures: param_name mode datatype [:= value] -param_name local variable name - mode IN = Input Only (default) OUT = Output Only//retur IN OUT = Both -datatype valid SQL datatype,%type or %rowtype -value Default value (optional)

Parameters in Stored Procedures

A _____________ is created with the CREATE OR REPLACE PROCEDURE statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows: CREATE OR REPLACE PROCEDURE xxx /* parameter list */ IS /* data */ BEGIN /* regular, executable code */ EXCEPTION /* exception code */ END; When creating named _____________, the object definition or ____________ name replaces DECLARE (reside/stored in the database)

Procedure

The SELECT ___________ statement retrieves data from one or more database tables, and assigns the selected values to variables or collections. * Select statement extension: SELECT (table_attrib, table_attrib) INTO (local_var, local_var) FROM (tablename) WHERE ... SELECT * INTO rowtype_var FROM tablename WHERE -Can only be one row / value returned with a Select statement in PL/SQL

SELECT INTO statement

SIMPLE LOOP (______-test): LOOP INSERT INTO temp_table VALUES(v_counter, 'Loop Index'); v_counter := v_counter + 1; IF v_counter > 99 THEN EXIT; END IF; END LOOP;

Simple Loop (Post-test)

____________ Case Statement = - Evaluates statement - Executes the statements after value equals statement expression := CASE expression THEN ... END; ______________ Case Statement = Evaluates a list of Boolean statements expression := CASE WHEN expression1 THEN ... END; **If case isn't found, raises a CASE_NOT_FOUND exception

Simple, Searched

___________ Procedure Syntax: CREATE OR REPLACE PROCEDURE (name) (var1 IN NUMBER, var2 OUT VARCHAR, var3 IN OUT CHAR) IS (or AS ... You can use either key word here) /* declarations */ BEGIN /* procedure */ EXCEPTION /* exception handling */ END;

Stored Procedure Syntax

S vs B Variables: _______________ variables can be used for: - Displaying information at PROMPT - Getting input with ACCEPT - Place information in TTITLE/BTITLE ___________ variables can be used for: Returning information to SQL*PLUS from PL/SQL OUT or IN OUT parameters

Substitution/ / Bind

The Package ___________ is the Implementation of the Specification - All of the code required to fulfill the Specification Can also contain private elements not available (or defined) in the Specification

The Package Body

The Package ______________ is the Definition of all public parts of the package (Think API - makes package a black box)

The Package Specification

______________ are stored programs which Execute/Fire upon event occurrence

Triggers

See the values from stored procedures to variables in our SQL environment: var my_var number execute addem(1, 2, :my_var); select :my_var from dual; Or - from SQL*Plus print my_var (No colon in SQL*Plus)

Two ways to see the content of a bind variable in your SQL*Plus session

In the Declaration Section, __________ and Cursor (return set) definitions: -Must be valid Oracle names -May not be Oracle reserved words -Should not be the same as an Oracle object referenced in the same block

Variable

While Loop (____-test): WHILE condition LOOP . . END LOOP;

While Loop (Pre-test)

Why Make a Local Module? Reduce code - Repetitive code can be called multiple times - Allows you to modularize and re-use To improve readability of code - Segregates chunks of related code - Less overhead than calling external procedures

Why Make A Local Module

All executable (programing commands) are in the execute block of the procedure - Start with BEGIN - End with END

_

An ______________ block is a pl/sql block that appears in your application and is not named and stored in your database. A ______________ procedure or a named block is a pl/sql block that oracle stores in the database and can be called by name from an application

anonymous procedure // stored procedure

Variables have scope: -Names (variables) are local to the defined block or ___________ blocks. -Names (variables) are global to nested blocks or __________ blocks

inner outer

A database ______________ (DML): statement (DELETE, INSERT, or UPDATE) A database___________ (DDL): statement (CREATE, ALTER, or DROP).

manipulation; definition

- A ______________ is a collection of related PL/SQL objects (procedures, functions, named exceptions, defined variables) that are stored together - Provide the ability to manage elements as a unit - Also provide encapsulation, so calling procedures only need to know the API

package