Oracle SQL & PL/SQL

SQL Introduction

SQL stands for Structured Query Language. and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural 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.

SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.

SQL Statements categories: DDL - Data Definition Language.

DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.

DDL Statements:

Create	It is used to create objects(tables, views) in the database.
Alter	It is used to alter the structure of the database objects.
Drop	delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints ).
Truncate	remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark.)
Grant	assigning privileges

DML - Data Manipulation Language.

DML is used to access, create, modify or delete data in the structures of the database.

DML Statements:

Select	Select data from the database
Insert	It is used to insert data into a table
Update	It is used to update existing data within a table
Delete	It removes rows from the table.

DCL - Data Control Language

Following are the examples of Data control Statements.

DCL Statements:

Commit	It will end the current transaction making the changes permanent and visible to all users..
Savepoint	It will identify a point(named SAVEPOINT) in a transaction to which you can later roll back
Rollback	It will undo all the changes made by the current transaction.
Set- Transaction	It is used to define the properties of a transaction.

Sitemap

SQL Update

SQL Update

Update Statement is used to change existing values in a table or a view's base table.

Example 1 :

update emp
set sal = sal*2.25
where deptno = 30

Example 2 :

update emp
set sal = sal*2.25
where empno in ( select empno from emp_1 )

QC-5
41) C, 42) E, 43) A, 44) B, 45) A, 46) B, 47) A, 48) C, 49) B, 50) B

SQL Delete

SQL Delete

How to delete all records from a table?

Delete from dept;

How to delete specific records from a table?

Delete from emp where empno=20;

How to delete duplicate records from the table?

Suppose we have a table t1(id integer, name varchar(10))

select * from t1;

id	name
1	aaa
2	bbb
3	bbb
4	ccc
5	ccc
6	ddd

delete from t1
where id not in ( select min(id)
from t1
group by name )

Few Examples on SQL Delete:

BOTH THE BELOW EXAMPLES OF UPDATE AND DELETE USE CORRELATED SUBQUERIES:

We need to update sal of all the employees in the emp table to the maximum salary in the corresponding dept.

UPDATE emp e1
SET sal = (SELECT MAX(sal)FROM emp e2
WHERE e1.deptno = e2.deptno);

We need To delete the records of all the employees in the emp table whose sal is below the average sal in the department

DELETE FROM emp e
WHERE sal < (SELECT AVG(sal) FROM emp
WHERE deptno = e.deptno);

SQL PL/SQL Interview Questions

SQL PL/SQL Interview Questions

These questions will be updated and new questions will be added on regular basis, so stay tuned and subscribe to RSS Feed.

What special operators does Oracle provide for dealing with NULLs?

NVL - Converts a NULL to another specified value, as in:

my_var := NVL (your_var, 'Hello');

IS NULL and IS NOT NULL

You can use this syntax to check specificaly to see if a variable's value is NULL or NOT NULL.

Explain three different rules that apply to NULLs when doing comparisons?

1. For all operators except for concatenation (||), if a value in an expression is a NULL, that expression evaluates to NULL

2. NULL is never equal or not equal to another value

3. NULL is never TRUE or FALSE

What command would you use to encrypt a PL/SQL application?

WRAP

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

A function has a return type in its specification and must return a value specified in that type. A procedure does not have a return type in its specification and should not return any value, but it can have a return statement that simply stops its execution and returns to the caller.

What steps are included in the compilation process of a PL/SQL block?

The compilation process includes syntax checking, binding, and p-code generation. Syntax checking involves checking PL/SQL code for compilation errors. After syntax errors have been corrected, a storage address is assigned to the variables that are used to hold data for Oracle. This process is called binding. Next, p-code is generated for the PL/SQL block. P-code is a list of instructions to the PL/SQL engine. For named blocks, p-code is stored in the database, and it is used the next time the program is executed.

How does a syntax error differ from a runtime error?

A syntax error can be detected by the PL/SQL compiler. A runtime error occurs while the program is running and cannot be detected by the PL/SQL compiler.

A misspelled keyword is an example of a syntax error. For example, this script:

BEIN
DBMS_OUTPUT.PUT_LINE ('This is a test');
END;

contains a syntax error. Try to find it.

A SELECT INTO statement returning no rows is an example of a runtime error. This error can be handled with the help of the exception-handling section of the PL/SQL block.

SQL PL/SQL Interview Questions

Define Commit, Rollback and Savepoint.

When a COMMIT statement is issued to the database, the transaction has ended, and the following results are true:

. All work done by the transaction becomes permanent.

. Other users can see changes in data made by the transaction.

. Any locks acquired by the transaction are released.

When a ROLLBACK statement is issued to the database, the transaction has ended, and the following results are true:

. All work done by the transaction is undone, as if it hadn’t been issued.

. Any locks acquired by the transaction are released.

The ROLLBACK statement undoes all the work done by the user in a specific transaction. With the SAVEPOINT command, however, only part of the transaction can be undone.

SQL PL/SQL Interview Questions

Explain Implicit and Explicit cursors

Oracle automatically declares an implicit cursor every time a SQL statement is executed. The user is unaware of this and cannot control or process the information in an implicit cursor.

The program defines an explicit cursor for any query that returns more than one row of data. This means that the programmer has declared the cursor within the PL/SQL code block. This declaration allows the application to sequentially process each row of data as the cursor returns it.

SQL PL/SQL Interview Questions

How an Implicit cursor works?

Any given PL/SQL block issues an implicit cursor whenever a SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.
A cursor is automatically associated with every DML (data manipulation) statement (UPDATE, DELETE, INSERT).
All UPDATE and DELETE statements have cursors that identify the set of rows that will be affected by the operation.
An INSERT statement needs a place to receive the data that is to be inserted into the database; the implicit cursor fulfills this need.
The most recently opened cursor is called the SQL cursor.

How an Explicit cursor works?

The process of working with an explicit cursor consists of the following steps:

1. Declaring the cursor. This initializes the cursor into memory.

2. Opening the cursor. The declared cursor is opened, and memory is allotted.

3. Fetching the cursor. The declared and opened cursor can now retrieve data.

4. Closing the cursor. The declared, opened, and fetched cursor must be closed to release the memory allocation.

SQL PL/SQL Interview Questions

What are Explicit Cursor attributes

%NOTFOUND	cursor_name%NOTFOUND	A Boolean attribute that returns TRUE if the previous FETCH did not return a row and FALSE if it did.
%FOUND	cursor_name%FOUND	A Boolean attribute that returns TRUE if the previous FETCH returned a row and FALSE if it did not.
%ROWCOUNT	cursor_name%ROWCOUNT	The number of records fetched from a cursor at that point in time.
%ISOPEN	cursor_name%ISOPEN	A Boolean attribute that returns TRUE if the cursor is open and FALSE if it is not.

Answer any three PL/SQL Exceptions?

Too_many_rows,
No_Data_Found,
Value_Error,
Zero_Error,
Others

SQL PL/SQL Interview Questions

What are PL/SQL Cursor Exceptions?

Cursor_Already_Open, Invalid_Cursor

SQL PL/SQL Interview Questions

What is the maximum number of triggers, can apply to a single table?

12 triggers.

SQL PL/SQL Interview Questions

What is a mutating table error and how can you get around it?

This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

SQL PL/SQL Interview Questions

What packages (if any) has Oracle provided for use by developers?

Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

SQL PL/SQL Interview Questions

Describe the use of PL/SQL tables

PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

SQL PL/SQL Interview Questions

When is a declare statement needed?

The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

SQL PL/SQL Interview Questions

In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?

OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

SQL PL/SQL Interview Questions

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

SQL PL/SQL Interview Questions

How can you find within a PL/SQL block, if a cursor is open?

Use the %ISOPEN cursor status variable.

SQL PL/SQL Interview Questions

How can you generate debugging output from PL/SQL?

Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.

SQL PL/SQL Interview Questions

What are the types of triggers?

There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.

SQL PL/SQL Interview Questions

How can I define a two-dimensional array of numbers in PL/SQL?

Although PL/SQL does not natively support the declaration and manipulation of multidimensional arrays, you can emulate these structures using nested collection definitions, which were first supported in Oracle9i Database Release 1.

Here is a brief example to get you started and introduce you to some of the challenges you may encounter as you use collections in this way.

First, create a collection of associative arrays.

CREATE OR REPLACE PACKAGE twodim_aa
IS
TYPE data_t IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;

TYPE array_t IS TABLE OF data_t
INDEX BY PLS_INTEGER;
END twodim_aa;
/

The first, inner collection—data_t—contains the data for each cell in the two-dimensional array. Each row in the outer collection—array_t—contains a collection of the first type.

Now declare a variable based on that outer collection type —array_t—, which will serve as a two-dimensional array. In the following script, I declare such a collection—

DECLARE
l_2d_grid twodim_aa.array_t;

—and then assign values to three cells: (1,1), (1,2), and (200,206). Notice that the syntax is different from that used in traditional array cell specification, namely: (1)(1), (1)(2), and (200)(206). Also, since I am using associative arrays to define my two-dimensional array, I do not have to specify a size for this two-dimensional array.

DECLARE
l_2d_grid twodim_aa.array_t;
BEGIN
l_2d_grid (1) (1) := 100;
l_2d_grid (1) (2) := 120;
l_2d_grid (200) (206) := 200;

IF l_2d_grid (1)(2)

source of above question: http://www.oracle.com/technology/oramag/oracle/06-jan/o16plsql.html

See all multiple choice questions on SQL

Oracle PL/SQL Cursors

Oracle PL/SQL Cursors

We can manipulate the information within a SQL statement by means of assigning a name to a "select statement", this concept is known a cursor. A cursor is used for processing individual rows returned as a result for a query.

Oracle PL/SQL Implicit Cursors
Oracle PL/SQL Explicit Cursors
Oracle PL/SQL Cursor For Loops
Oracle PL/SQL Cursors With Parameters
Oracle PL/SQL REF Cursors
Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE
Examples of Oracle PL/SQL Cursors

PL/SQL Triggers

PL/SQL Triggers

A PL/SQL trigger is a construct in PL/SQL that runs or "triggered" on event of changes being made to a table in the database. The triggering event is a INSERT, UPDATE or DELETE done on a table. The trigger can be made so it can be "fired" either BEFORE or AFTER the Data Manipulation Language is executed.

>A database trigger is a block of code that is automatically executed in response to certain events.

>Triggers are executed implicitly whenever the triggering event happens.

>The triggering event is an INSERT, DELETE, or UPDATE command.

>The timing can be either BEFORE or AFTER, INSTEAD OF trigger.

The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

You can write triggers that fire whenever one of the following operations occurs:

DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

A trigger has three basic parts:

A triggering event or statement
A trigger restriction
A trigger action

Example of PL/SQL trigger

In the below example line 2 is A triggering event or statement, lines 4-9 are A trigger action.

Example of creating a trigger based on the following two tables:

CREATE TABLE T1 (a INTEGER);
CREATE TABLE T2 (b INTEGER);

We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.

1 CREATE TRIGGER tr1
2 AFTER INSERT ON T1
3 REFERENCING NEW AS newRow
4 FOR EACH ROW
5 WHEN (newRow.a <= 5)
6 BEGIN
7 INSERT INTO T2
VALUES(:newRow.a);
8 END tr1;
9 .
10 run;

Different types of triggers can be:

Row Triggers and Statement Triggers: A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects. A row trigger fires once for each row affected by the triggering event.

BEFORE and AFTER Triggers: BEFORE triggers run the trigger action before the triggering statement is run. AFTER triggers run the trigger action after the triggering statement is run.

INSTEAD OF Triggers: INSTEAD OF triggers describe how to perform insert, update, and delete operations against views that are too complex to support these operations natively. INSTEAD OF triggers allow applications to use a view as the sole interface for all SQL operations (insert, delete, update and select).

Triggers on System Events and User Events: You can use triggers to publish information about database events to subscribers. System events are for example Database startup and shutdown, Data Guard role transitions etc and User Events are User logon and logoff, DDL statements (CREATE, ALTER, and DROP) etc.

Displaying Trigger Errors

If we get a message Warning: Trigger created with compilation errors. you can check the error messages with:

Show errors trigger <trigger_name>;

You can also type, SHO ERR (SHOW ERRORS) to see the most recent compilation error.

Viewing Defined Triggers

To view all the defined triggers, use:

select name_of_trigger from user_triggers;

For more details on a particular trigger:

select trigger_type, triggering_event, table_name, referencing_names, trigger_body
from user_triggers
where trigger_name = '<name_of_trigger>';

Disabling Triggers

To disable or enable a trigger:

alter trigger <name_of_trigger> {disable | enable};

SQL Joins

SQL Joins

You will get vast amount of data/ information on this page regarding Oracle SQL JOINS like

SQL Insert

Inserting a new row into the dept table

insert into dept (deptno, dname, loc)
values (10,'Apple','Boston')

You can omit the column list in the insert statement but then you have to enter those in the same order as they appear in the table and you have to include all the columns in the values.

The following example illustrates the use of the "default" keyword while inserting the records.

insert into table_name values(default);

insert into table_name(column1,column2..) values(default)

How to create a new table having the same structure as some other table?

create table new_table
as
select *
from old_table

How to insert data into multiple tables?

There are two terms used INSERT ALL AND INSERT FIRST

EXAMPLE:

Suppose there are three tables emp,emp_1 and emp_2 with the same sructure and columns.

insert all
when sal in (800,1600) then
into emp (empno,ename,job) values (empno,ename,job)
when sal = 3200 then
into emp_1 (empno,ename,job) values (empno,ename,job)
else
into emp_2 (empno,ename,job) values (empno,ename,job)
select empno,ename,job
from emp

INSERT FIRST will breakout as soon as it sees a condition that evaluates to true.

INSERT ALL will evaluate all the conditions even if the previous condition evaluates to true.

Insert All and Insert First are available on Oracle only.

Insert Default Values

Some RDBMSs, including SQL Server, allow you to create a new row based on default values that are defined in the table specification using the DEFAULT VALUES method in place of the VALUES section. Suppose you have a table like this:

CREATE TABLE A (
A_ID int IDENTITY PRIMARY KEY,
Name varchar(100) DEFAULT 'Anonymous',
Dates varchar(20) DEFAULT 'Unknown);

and you want to insert a new row with just the default values for each column and the auto-generated ID field. You can't do it using the standard INSERT...VALUES syntax, so instead you'd use this:

INSERT INTO A DEFAULT VALUES;

And that does the trick. Using DEFAULT VALUES instead of the standard VALUES clause inserts the default value for every column (or an auto-generated value for identity columns). If there isn't a default value for a non-identity column, the value is treated as NULL. If any column in the table is NOT NULL, isn't an identity column, and doesn't have a default value, then an error will be raised.

Performing a Multi-Row INSERT

INSERT INTO S_Backup SELECT EveryName, EveryCost FROM S;

Alternatively, you can easily reshape data and rename columns of rows using a statement such as this:

INSERT INTO S_1 (TheName, TheCost)
SELECT EveryName, EveryCost FROM S;

SQL Syntax

SQL Syntax

On this page, you will find SQL syntax for some of the most important SQL commands. These below SQL Syntaxes will be suitable for quick reference.

SELECT [hint][DISTINCT] select_list
FROM table_list

[WHERE conditions]
[GROUP BY group_by_list]
[HAVING search_conditions]

[ORDER BY order_list [ASC DESC] ]
[FOR UPDATE for_update_options]

SQL Select Statement

SELECT "column name" FROM "table name"

Example:
Select salary from emp;

SQL Where

SELECT "column name"
FROM "table name"
WHERE "condition"

Example:
Select salary from emp
Where salary > 2000

SQL Distinct

SELECT DISTINCT "column name"
FROM "table name"

Example:
Select DISTINCT name from emp;

SQL And/Or

SELECT "column name"
FROM "table name"
WHERE "condition"
{[ANDOR] "condition"}+

Example:
SELECT salary
FROM emp
WHERE Salary > 1000
OR (Salary <> 275)

SQL Between

SELECT "column name"
FROM "table name"
WHERE "column name" BETWEEN 'value1' AND 'value2'

Example:
SELECT *
FROM emp
WHERE Date BETWEEN 'Jan-01-1999' AND 'Jan-15-1999'

SQL In

SELECT "column name"
FROM "table name"
WHERE "column name" IN ('value1', ‘value2’ ...)

Example:
SELECT *
FROM emp
WHERE last_name IN ('sharma', 'dhall')

SQL Like

SELECT "column name"
FROM "table name"
WHERE "column name" LIKE {PATTERN}

Example:
SELECT *
FROM emp
WHERE last_name LIKE '%EN%'

SQL Order By

SELECT "column name"
FROM "table name"
[WHERE "condition"]
ORDER BY "column name" [ASC, DESC]

Example:
SELECT name, Salary
FROM emp
ORDER BY name DESC

SQL Count

SELECT COUNT ("column name")
FROM "table name"

Example:
SELECT COUNT (salary)
FROM emp

SELECT * FROM Table;

In the above query, SELECT and FROM are SQL keywords, * is a wildcard which means “all columns”

According to wikipedia Commonly available SQL keywords related to SELECT include:

FROM is used to indicate from which tables the data is to be taken, as well as how the tables JOIN to each other.
WHERE is used to identify which rows to be retrieved, or applied to GROUP BY. WHERE is evaluated before the GROUP BY.
GROUP BY is used to combine rows with related values into elements of a smaller set of rows.
HAVING is used to identify which of the "combined rows" (combined rows are produced when the query has a GROUP BY keyword or when the SELECT part contains aggregates), are to be retrieved. HAVING acts much like a WHERE, but it operates on the results of the GROUP BY and hence can use aggregate functions.
ORDER BY is used to identify which columns are used to sort the resulting data.

Returning a Single Column from a table

The syntax is as follows:
SELECT Column FROM Table;

Oracle uses the ANSI standard concatenation operator, . Because this operator is reserved for string concatenation, the operands don't need to be cast to a string type—they'll be converted automatically:

SELECT FirstName ' ' LastName AS Name FROM EMP_Database;

Sorting with the ORDER BY Clause

The ORDER BY clause is always applied after all other clauses are applied, such as the WHERE and GROUP BY clauses. Without an ORDER BY clause in an SQL statement, rows will often be retrieved in the physical order in which they were added to the table. The default behavior is to sort rows in ascending order.

SELECT ColumnA, ColumnB FROM Table
ORDER BY ColumnA ASC;

Although this is the default behavior so you don't need to explicitly specify the ASC keyword, it's a better idea to include it to ensure that your queries are as easy to read as possible.

Also, you may want to sort your rows in descending order, in which case you use the DESC keyword:

SELECT ColumnA, ColumnB FROM Table
ORDER BY ColumnA DESC;

Filtering Data

DISTINCT always retrieves the first value from a repeating group. If there are multiple repeating groups DISTINCT will retrieve the first row from each group.

Hence, DISTINCT will always require a sort. DISTINCT can operate on a single or multiple columns.

SELECT DISTINCT ColumnA FROM Table;

In order to filter out duplicate rows, you use the DISTINCT keyword.

If you want to retrieve multiple columns, you can guarantee that every row you obtain contains unique data for the specified set of columns.
For example, the following query will return only unique combinations of customer names and debit card details:

SELECT DISTINCT CustomerName, DebitCard FROM Customers;

This doesn't mean that you won't have duplicates in either column, only that each combination is unique. You could, for example, have several customers with the same name but with different debit card numbers.

Using WHERE Clause

We can use the WHERE clause to restrict the rows returned by a query.

SELECT ColumnA, ColumnB, ColumnC FROM Table
WHERE Condition;

Condition is very flexible, allowing you to test for equalities and inequalities in column data, ranges of values to look for etc. You achieve all this using a simple syntax that includes various operators and keywords that, when combined, allow you to search for pretty much anything.

We can use following comparison operators with the WHERE clause:

Operator	Meaning
=	Equal
<>, !=, ^=	Not Equal
>	Greater Than
<	Less Than
>=	Greater Than Or Equal
<=	Less Than Or Equal

And many more for example BETWEEN ...AND..., LIKE, IS NULL etc.

Some examples of where

1)
SELECT name FROM bedroom
WHERE bedcolor = 'BLACK'
OR ceilingcolor = 'GREEN'
OR wallcolor = 'YELLOW'

2)
SELECT z
FROM t
WHERE x = 6 AND y > 7 ;

3)
Find the average price of Bud.

Sells (bar, beer, price)

SELECT AVG (price)
FROM Sells
WHERE beer = 'Bud';

PL/SQL Collections

PL/SQL Collections

Records

PL/SQL Tables

Varrays

Nested Tables

PL/SQL Introduction

PL/SQL Introduction

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;

PL/SQL Tables

PL/SQL Tables

Nested tables	Varrays	Index-by-tables
Declare Cursor name_cur IS Select last_name From student Where rownum <= 10; Type last_name_type Is Table Of student.last_name%Type; last_name_tab last_name_type := last_name_type(); v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_tab.EXTEND; last_name_tab(v_counter):= name_rec.last_name; Dbms_Output.Put_Line ('last_name('\|\|v_counter\|\|'): '\|\| last_name_tab(v_counter)); END LOOP; END;	Declare Cursor name_cur IS Select last_name From student Where rownum <= 10; Type last_name_type Is Table Of student.last_name%TYPE Index By Binary_Integer; last_name_tab last_name_type; v_counter INTEGER := 0; Begin For name_rec IN name_cur Loop v_counter := v_counter + 1; last_name_tab(v_counter) := name_rec.last_name; Dbms_Output.Put_Line ( 'last_name('\|\|v_counter\|\|'): '\|\| last_name_tab(v_counter)); END LOOP; END;	Declare Cursor name_cur IS Select last_name From student Where rownum <= 10; Type last_name_type Is Table Of student.last_name%Type; last_name_tab last_name_type := last_name_type(); v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_tab.EXTEND; last_name_tab(v_counter):= name_rec.last_name; Dbms_Output.Put_Line ('last_name('\|\|v_counter\|\|'): '\|\| last_name_tab(v_counter)); END LOOP; END;

Oracle PL/SQL Varrays

Oracle PL/SQL Varrays

Varray stands for variable-size array. Varray can be stored in the columns of your tables. When you create varrays you must provide the maximum size for them. They retain their ordering and subscripts when stored in and retrieved from a database table. They are similar to PL/SQL table, and each element in a varray is assigned a subscript/index starting with 1.These are dense and Not sparse, which means there is no way to delete individual elements of a Varray.

Example 1 of PL/SQL Varray

Once the VARRAY is defined, elements can easily be inserted into the VARRAY. We can initialize our PLSQL varrays either in the declaration or separately. If the varray is only partially initialized we have to use the EXTEND method to define extra cells.

SQL> declare
2    type auth_var is varray(10) of
3                      author.author_last_name%type;
4    x_auth auth_var := auth_var();
5  begin
6    x_auth.extend;
7    x_auth(1) := 'Hello';
8    x_auth.extend;
9    x_auth(2) := 'World';
10    dbms_output.put_line(
x_auth(1) ||' & '|| x_auth(2));
11  end; /

Hello & World

In the lines 2 and 3 we define the VARRAY type with a maximum of 10 elements. In line 4 we define the variable x_auth of auth_var type and it also initializes the array. A VARRAY can not be used until it is initialized. In line 4 the auth_var() function actually does the initialization.

Once the array is initialized you can extend it and add elements, which is done in lines 6 through 9. We access each element using the VARRAY variable and the index number. When the PL/SQL block ends (or the array variable goes out of scope), the memory used by the array is recovered automatically by the PL/SQL engine. Unlike a cursor, you do not close a collection.

Example 2 of PL/SQL Varray

Example 3 of PL/SQL Varray

PL/SQL procedure successfully completed.

Let us take a look what the line varray.EXTEND(2, 4);does in the above code. It appends two copies on the fourth element to the collection. As a result, the seventh and eighth elements both contain a value of 4.