it314

references a data structure that contains multiple scalar variables

join two strings

points to memory location on DB server that the DBMS uses to process the query

Object made up of multiple data elements

Unwanted Event

Gives users options for fixing errors in the EXCEPTION section

Triggered by EXCEPTION, contains error-handling statements

Starts with BEGIN

Retreives and displays data in PL/SQL programs for a query that might retrieve multiple records or none at all

program that receives one or more inputs (parameters) and returns a single output value

points to a context area which contains info about the query (number of rows, etc.)

Interpreter automatically converts a value datatype

Doesn't halt program, but produces incorrect result

Executes statements and evaluates an exit condition to determine if it should repeat or exit

Memory area on DB server that stores output values before they are displayed to the user

Separating a single string of two data items via commas or spaces

• Executes 1 ore more commands before loop evaluates exit condittion for first time
• (Tests Post-action)

most common error in a program

Evaluates exit condition before executing any commands

Uses detailed, sequential instructions to process data

Directly reference a specific DB column or row and assume that datatype

References a single value

Each variable must be declared before it can be used

Command does not follow guidelines of programming language

Less common errors that do not have predefined names

programs you submit to the interpreter to run, but do not ineract with other program units

program units stored on client workstation and execute there

DB index table that contains up to 16 sorted columns that the DBMS can use for finding row location

Distinct DB table that contains data values with corresponding columns that specify physical locations of records

Describes how program unit can change the parameter value

Program unit that can receive multiple input parameters and return multiple output values or none at all

visible to many different PL/SQL programs

specifies internal location of a record in the DB

Other PL/SQL programs can reference and other DB users can execute

Can store up to 32,767 characters

fixed-length, if you put in less than the requested amount it pads the rest

can be DAY TO SECOND or YEAR TO MONTH

Object made of multiple elements

Record, Table, VARRAY

• %type
• %rowtype

• variable_name table.column%type
• references a single field

variable_name table%rowtype

• /**/
• or 
• --

**

SET SERVEROUTPUT ON SIZE 4000

• TO_CHAR(input, 'output');
• TO_CHAR(2.98, '$999.99');

• TO_DATE('input', 'output')
• TO_DATE('07/14/2003', 'MM/DD/YYYY');

removes leading and trailing spaces

LENGTH();

finds location of a substring within a string

Extracts characters from a string. First number is start point, second is endpoint

HH12:MI

• Loop...Exit
• Loop...Exit When
• While...Loop
• FOR  [counter IN x..y]

• SELECT field
• INTO variable
• FROM table
• WHERE condition;
• (CAN ONLY RETURN ONE RESULT)

• CURSOR name IS <select query>;
• OPEN name;
• LOOP
• FETCH name into variables;
• EXIT WHEN name%NOTFOUND;
• END LOOP;
• CLOSE name;

• CREATE INDEX _____
• ON column

DROP INDEX ______

• CREATE OR REPLACE PROCEDURE ______
• (____ in/out NUMBER;)
• IS
• variables
• BEGIN
• EXCEPTION
• END;

• CREATE OR REPLACE FUNCTION ______
• (parameters)
• RETURN DATATYPE
• IS
• BEGIN
• EXCEPTION
• END;

INSERT INTO tablename (targets) VALUES (variables);

DELETE FROM tablename;