Programming Is Fun

main features 1

2012-08-27T06:12:00.001-07:00

Subprograms

A PL/SQL subprogram is a named PL/SQL block that can be invoked repeatedly. If the subprogram has parameters, their values can differ for each invocation. PL/SQL has two types of subprograms, procedures and functions. A function returns a result. For more information about PL/SQL subprograms, see "PL/SQL Subprograms."

PL/SQL also lets you invoke external programs written in other languages. For more information,

See External Subprograms.

Packages

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. You can think of a package as an application.

You can write your own packages—for details, see PL/SQL Packages. You can also use the many product-specific packages that Oracle supplies. For information about these, see Oracle Database PL/SQL Packages and Types Reference.

Triggers

A trigger is a named PL/SQL unit that is stored in the database and run in response to an event that occurs in the database. You can specify the event, whether the trigger fires before or after the event, and whether the trigger runs once for each event or once for each row affected by the event. For example, you can create a trigger that runs every time an INSERT statement affects the EMPLOYEES table. For more information about triggers, see PL/SQL Triggers

Input and Output

Most PL/SQL input and output (I/O) is done with SQL statements that store data in database tables or query those tables. For information about SQL statements, see Oracle Database SQL Language Reference. All other PL/SQL I/O is done with PL/SQL packages those Oracle supplies, which the Table given below summarizes.

To display output passed to DBMS_OUTPUT, you need another program, such as SQL*Plus. To see DBMS_OUTPUT output with SQL*Plus, you must first issue the SQL*Plus command SET SERVEROUTPUT ON. Some subprograms in the packages in can both accept input and display output, but they cannot accept data directly from the keyboard. To accept data directly from the keyboard, use the SQL*Plus commands PROMPT and ACCEPT.

See Also:

■ SQL*Plus User's Guide and Reference for information about the

SQL*Plus command SET SERVEROUTPUT ON

■ SQL*Plus User's Guide and Reference for information about the

SQL*Plus command PROMPT

■ SQL*Plus User's Guide and Reference for information about the

SQL*Plus command ACCEPT

Main Features of PL/SQL

2012-08-27T02:46:00.001-07:00

PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages. When you can solve a problem with SQL, you can issue SQL statements from your PL/SQL program, without learning new APIs. Like other procedural programming languages, PL/SQL lets you declare constants and variables, control program flow, define subprograms, and trap run-time errors. You can break complex problems into easily understandable subprograms, which you can reuse in multiple applications.

Topics:

Error Handling
Blocks
Variables and Constants
Subprograms
Packages
Triggers
Input and Output
Data Abstraction
Control Statements
Conditional Compilation
Processing a Query Result Set One Row at a Time

Error Handling

PL/SQL makes it easy to detect and handle errors. When an error occurs, PL/SQL raises an exception. Normal execution stops and control transfers to the exception-handling part of the PL/SQL block. You do not have to check every operation to ensure that it succeeded, as in a C program. For more information, see PL/SQL Error Handling

See Also: Oracle Database Advanced Application Developer's Guide for information about developing PSPs

Blocks

The basic unit of a PL/SQL source program is the block, which groups related declarations and statements.A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END.These keywords divide the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. A block can have a label.

For syntax details, see Block

Declarations are local to the block and cease to exist when the block completes execution, helping to avoid cluttered name spaces for variables and subprograms.

Blocks can be nested: Because a block is an executable statement, it can appear in another block wherever an executable statement is allowed.

You can submit a block to an interactive tool (such as SQL*Plus or Enterprise Manager) or embed it in an Oracle Precompiler or OCI program. The interactive tool or program runs the block only once. The block is not stored in the database, and for that reason, it is called an anonymous block (even if it has a label).

An anonymous block is compiled each time it is loaded into memory, and its compilation has three stages:

1. Syntax checking: PL/SQL syntax is checked, and a parse tree is generated.

2. Semantic checking: Type checking and further processing on the parse tree.

3. Code generation

Variables and Constants

PL/SQL lets you declare variables and constants, and then use them wherever you can use an expression. As the program runs, the values of variables can change, but the values of constants cannot. For more information, see Declarations and "Assigning Values to Variables

Note: An anonymous block is a SQL statement.

History - SQL

2012-08-05T13:56:00.004-07:00

SQL stands for Structured Query Language. and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Non-procedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language.

History - PL SQL

2012-08-05T13:31:00.000-07:00

PL/SQL stands for Procedural Language/SQL. PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL expands SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. Basically it runs on the database server, but a few Oracle products such as Developer/2000 also contain a PL/SQL engine that resides on the client. Thus, you can run your PL/SQL code on either the client or the server depending on which is more suitable for the task at hand.

Unlike SQL, PL/SQL is procedural, not declarative.

A declarative (non-procedural) programming language is a language that allows the programmer to state the task to be accomplished without specifying the procedures needed to carry it out.

A Procedural programming language is a language in which programs largely consist of a series of commands to assign values to objects.

The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in the program. PL/SQL is Block Structured.

The Syntax of a PL/SQL Block

DECLARE

Variable_declarations

BEGIN

Program_code

EXCEPTION

Exception_handlers

END;

Below is the basic structure of the PL/SQL program:

Set serveroutput on

Var1 varchar2(20);

Begin

Var1 := ‘Hello World’;

Dbms_output.put_line(var1);

Exception

When others then

Dbms_output.put_line(‘It is an exception’);

End;

/

After going through the above code following points are worth remembering:

In the declaration section all the variables and constants are defined.

In PL/SQL all the errors are handled in the Exception block.

Begin and End are mandatory statements indicating begin and end of the PL/SQL Block.

Variables and Constants must be declared first before they can be used.

The declaration of variables and constants are alike, but constant definitions must contain the keyword CONSTANT and must be assigned a value as part of the definition. Later on any attempts to assign a value to a constant will result in an error message.

Values can be assigned to variables directly using the “:=” assignment operator, by way of a SELECT ... INTO statement or When used as OUT or IN OUT parameter from a procedure.

Declaring PL/SQL variables and constants.

Example of declaring Variables:

Var1 varchar2(100);

Hire_date Date;

Var2 number default 5;

Var3 number not null := 2;

Not Null means a value may change but it can never be assigned Null.

Var4 varchar2(20) := Null;

Var5 varchar2(20) default Null;

Example of declaring Constants:

Var_constant constant number := 100;

Constants cannot be changed.

You must initialize constants at the time of declaration.

%TYPE and %ROWTYPE

%TYPE is used to declare a variable that is of the same type as a specified table’s column.

Emp_number emp.empno%type;

%ROWTYPE is used to declare a record (variable that represents the entire row of a table).

Emp_record emp%rowtype;

Another example of declaring variable:

Declare

name varchar2(30);

Select ename into name from emp where empno = 20;

Begin

Null;

End;

Any DML statements should be after Begin statement;

Begin

Delete from emp where empno = 29;

Commit;

End;