Oracle Database

Oracle SQL Developer is a new, free graphical tool that enhances productivity and simplifies database development tasks. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own.

Oracle SQL Developer is formerly called Project Raptor.

Oracle SQL Developer is available for three types of operating Systems:

► Windows

► Linux

► Mac OSX

If you want to download a copy of Oracle SQL Developer, visit http://www.oracle.com/technology/software/products/sql/.

If you are using Windows systems, click the "Oracle SQL Developer for Windows" link. This allows you to download the Windows version in ZIP format with file name of sqldeveloper-v1.0.0.zip in 55,295,721 bytes.

Once you have sqldeveloper-v1.0.0.zip downloaded, extract the zip file into the root directory: .

When the extraction is done, the installation is completed. Your copy of Oracle SQL Developer is installed in directory: sqldeveloper, and ready to run.

PL/SQL language is not case sensitive:

► Reserved words are not case sensitive. For example: CASE and Case are identical.

► Variable names and other names are not case sensitive. For example: TOTAL_SALARY and total_salary are identical.

But values in string literals are case sensitive. For example: 'DBA' and 'dba' are different.

There are two ways to place comments into PL/SQL codes:

► SQL Statement Style: Starts you comment any where in the line but prefixed with '--'. The comment ends at the end of the line.

► C Language Style: Starts you comment any where in the line with '/*' and ends it with '*/' in the same line or some lines below.

Here is some example of PL/SQL comments:

BEGIN

-- This is a comment

/* To do:

Need to write some codes here

*/

END;

There are 3 types of PL/SQL code blocks:

► Anonymous Block - A block of codes with no name. It may contain a declaration part, an execution part, and exception handlers.

► Stored Program Unit - A block of codes with a name. It is similar to an anonymous block. But it can take parameters and return values.

► Trigger - A block of code that can be defined to fire based an specific event.

An anonymous block is a PL/SQL code block with no name. It consists of three parts:

► Declaration Part - Defining local variables and local procedures. Declaration part is optional.

► Execution Part - Defining execution logic with executable statements. Execution part is required.

► Exception Part - Defining error handling logics. Exception part is optional.

Here how a complete anonymous block should look like:

DECLARE

-- Declaration statements

BEGIN

-- Executable statements

EXCEPTION

-- Error handling statements

END;

A named program unit is a PL/SQL code block with an name. It consists of three parts:

► Declaration Part - Defining the program unit name, calling parameters, local variables and local procedures. Declaration part is required.

► Execution Part - Defining execution logic with executable statements. Execution part is required.

► Exception Part - Defining error handling logics. Exception part is optional.

There are two types of named program units:

► Procedure - Has no return values.

► Function - Has a return value.

A procedure is a named program unit. It consists of three parts:

► Declaration Part - Defining the procedure name, calling parameters, local variables and local procedures. Declaration part is required.

► Execution Part - Defining execution logic with executable statements. Execution part is required.

► Exception Part - Defining error handling logics. Exception part is optional.

Here how a complete procedure should look like:

PROCEDURE name (parameter_1, parameter_2) AS

-- Declaration statements

BEGIN

-- Executable statements

EXCEPTION

-- Error handling statements

END;

A local variable can be defined in the declaration part with a declaration statement, which is a variable name followed a data type identifier. Below are some examples of declaration statements:

PROCEDURE proc_var_1 AS

domain VARCHAR2(80);

price REAL;

is_for_sale CHAR;

BEGIN

-- Executable statements

END;

There are two ways to assign default values to variables at the time of declaration:

► Using key word DEFAULT - Appending "DEFAULT value" to the end of declaration statements.

► Using assignment operator - Appending ":= value" to the end of declaration statements.

The script below show you some examples of declaration statements with default values:

PROCEDURE proc_var_1 AS

domain VARCHAR2(80) := 'rendc.org';

price REAL DEFAULT 999999.99;

is_for_sale CHAR := 'N';

BEGIN

-- Executable statements

END;

There are 4 basic arithmetic operations on numeric values as shown in the following sample script:

PROCEDURE proc_arithmetic AS

addition NUMBER;

subtraction NUMBER;

multiplication NUMBER;

division NUMBER;

BEGIN

addition := 7 + 8;

subtraction := addition - 7;

multiplication := subtraction * 5;

division := multiplication / 8;

-- division should contain 5 now

END;

PL/SQL supports 6 basic numeric comparison operations as shown in the following sample script:

PROCEDURE proc_comparison AS

res BOOLEAN;

BEGIN

res := 1 = 2;

res := 1 < 2;

res := 1 > 2;

res := 1 <= 2;

res := 1 >= 2;

res := 1 <> 2;

-- more statements

END;

You can use assignment statements to assign values to variables. An assignment statement contains an assignment operator ":=", which takes the value specified on the right to the variable on left. The script below show you some examples of assignment statements:

PROCEDURE proc_var_2 AS

is_done BOOLEAN;

counter NUMBER := 0;

message VARCHAR2(80);

BEGIN

is_done := FASLE;

counter := counter + 1;

message := 'Hello world!';

END;

PL/SQL supports 3 logical operations as shown in the following sample script:

PROCEDURE proc_comparison AS

x BOOLEAN := TRUE;

y BOOLEAN := FALSE;

res BOOLEAN;

BEGIN

res = x AND y;

res = x OR y;

res = NOT x;

-- more statements

END;

PL/SQL data types are grouped into 4 categories:

► Scalar Data Types: A scalar data type holds a single value.

► Composite Data Types: A composite data type has internal components, such as the elements of an array.

► LOB Data Types: A LOB data type holds a lob locator that specify the location of a large object.

► Reference Data Types: A reference data type holds a pointer that points to another data object.

PL/SQL supports many scalar data types divided into 4 groups:

► Numeric Types: BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT.

► Character Types: CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2, RAW, ROWID, STRING, UROWID, VARCHAR, VARCHAR2.

► Boolean Types: BOOLEAN.

► Date Types: DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND.