SQL Programming





PL / SQL stands for Programming Language SQL. The PL / SQL is called extension of SQL and contains query statements and cursor control statements to enhance performance of SQL. SQL is the natural language of Oracle Engine.

It creates and maintains data manipulation objects such as tables, views, sequences etc.

 

  • DDL: It stands for Data Description Language used to create objects.
  • DML: It stands for Data Manipulation Language used to manipulate data within created objects.
  • DCL: It is a Data Control Language for behavior control of objects.

 

1. Comments

 

The comments are non-executable statements used to define program objectives, date, time, and programming environment. Every programming language has its own techniques to represent comments, but in SQL programming environment, two methods are commonly applied:

(a) C like comment: Comments are written within /* and */.

(b) Comment is started by double hypen (- -).

 

2. Branching and Looping

 

The program is interwoven by several decisions making and looping statements. Every programming language has different keywords and syntax for handling decision making and looping statements.

 

(a) IF – -END IF: It is a conditional and decision making statement depends on condition. If condition is true, the statement after THEN clause is executed, otherwise ELSE clause is executed.

IF<condition1>THEN

Statement;

ELSEIF<condition2>THEN

Statement;

ELSE

Statement;

END IF;

 

(b) WHILE: It is a looping statement and loops are condition dependent. We use it when initial value of variable is known.

While<condition>

LOOP

Statements;

END LOOP;

 

(c) FOR – - END LOOP: It is also looping statements used to repeat statements written within LOOP and END LOOP.

FOR variable IN/REVERSE start. . end

LOOP

Statement;

END LOOP;

 

(d) GOTO <label>;

 

3. PL / SQL Section

 

The SQL program is mainly divided into two major sections: DECLARE and BEGIN – - END.

SQL Section

SQL Section


 

(a) DECLARE: In this section memory variable, constants, cursors are declared. It is a section in which all the oracle objects are declared. The values of variables are also assigned here.

Example:

DELCARE

x number(-4);

name varchar2(15);

pi constant number(4,2):=3.14;

 

(b) BEGIN – -END: The main body of PL/SQL is BEGIN – END section in which all valid statements are written. It contains EXCEPTION section in which error handling codes are written.

Example:

Step-1: Type following program in Notepad or any text editor and save as absum.sql

DECLARE

A  number(5):=15;

B number(5):=10;

Itsum number(5);

BEGIN

Itsum:=A+B;

Dbms_output.put_line(‘sum=’ || itsum);

END

/

 

Step-2: Open SQL *Plus editor and type following at prompt:

SQL > Set SERVEROUTPUT ON;

And press enter key

OR

  • Options > Environment
  • Check Current and ON
  • Click OK

Step-3: The following command line is used to load or open SQL program file.

SQL > GET absum;

and type slash(’/’) to execute program.

Or

Step-4: The following command line is used to execute loaded SQL program.

SQL>START absum;

Example: The following program returns area of rectangle.

DELCARE

L number(4):=15;

B number(4):=12;

area number(6,2);

BEGIN

area:=L*B;

dbms_output.put_line(‘Area of Rectangle=’ | | area);

END;

/

 

Example: The following program reverses the number 6339 as 9336.

DECLARE

numb varchar(5):=’6339’;

pot number(2);

inverto varchar(5);

BEGIN

Pot:=length(numb);

While pot>0

Loop

Inverto:=inverto | | substr(numb, pot, 1);

Pot:= pot-1;

End loop;

dbms_output.put_line(‘The given number:’ | | numb);

dbms_output.put_line(‘The inverted number:’ | | inverto);

END;

/

 

Example: The following program displays name, qualification and address.

DECLARE

BEGIN

dbms_output.put_line(‘—————–‘);

dbms_output.put_line(‘Subash Ghimire’);

dbms_output.put_line(‘BCA’);

dbms_output.put_line(‘Golbasti-3, ilam’);

dbms_output.put_line(‘——————-‘);

END;

/

 

Example: Following program is loaded and executed directly.

DECLARE

x number(4):=5;

value number(4,2);

BEGIN

value:=power(x,2);

dbms_output.put_line(‘———ANSWER———–‘);

dbms_output.put_line(‘Square=’ | | value);

dbms_output.put_line(‘————————–‘);

END;

.

RUN;

 

Example:

DECLARE

x number(4):=5;

value1 number(4,2);

value2 number(6,2);

BEGIN

Value1:=power(x,2);

Value2:=value1*x;

dbms_output.put_line(‘——————ANSWER——————-;);

dbms_output.put_line(‘ The square=’ | | value1);

dbms_output.put_line(‘The cube=’ | | value2);

dbms_output.put_line(‘—————————‘);

END;

 

Running the table based program:

Step-1: At SQL> Prompt, create table:

SQL> CREATE TABLE areas (length number(4), breadth number(4), area number(6,2));

Step-2: At SQL, prompt type ED areas; and click yes

SQL> ED areas;

Type following program and save:

DECLARE

length number(4);

breadth constant number(4):=30;

area number (6,2);

BEGIN

Length:=50;

While length<=100

LOOP

area:=length*breadth;

insert into Areas values(length, breadth, area);

length:=length+1;

END LOOP;

END

/

 

Step-3: you create table named as SquNumb using following statements:

SQL > CREATE TABLE sqrNumb(digit number(2), value number(6));

DECLARE

digit number(2);

value number(6);

BEGIN

digit:=1;

WHILE digit<=10

LOOP

value:=power(digit,2);

insert into sqrNumb values(digit, value);

digit:=digit+1;

END LOOP;

END;

/

 

When program is executed following output is obtained as shown in figure below:

Output



Related posts:

  1. Matrix Formulas Matrix Formulas In mathematics the word ‘Matrix’ means  the rectangular...
  2. SQL in RDBMS SQL (pronounced as “ess-queel”) is used to communicate with database....
  3. Loops in C++ Loops in C++ Loop is used when we want to...
  4. Programming Language Language is a medium of communication. There are several languages...
  5. Programming Techniques Software designing is very anesthetic phase of software development cycle....