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.
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.
(b) WHILE: It is a looping statement and loops are condition dependent. We use it when initial value of variable is known.
(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
(d) GOTO <label>;
3. PL / SQL Section
The SQL program is mainly divided into two major sections: DECLARE and BEGIN – - END.
(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.
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.
Step-1: Type following program in Notepad or any text editor and save as absum.sql
Dbms_output.put_line(‘sum=’ || itsum);
Step-2: Open SQL *Plus editor and type following at prompt:
SQL > Set SERVEROUTPUT ON;
And press enter key
- 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.
Step-4: The following command line is used to execute loaded SQL program.
Example: The following program returns area of rectangle.
dbms_output.put_line(‘Area of Rectangle=’ | | area);
Example: The following program reverses the number 6339 as 9336.
Inverto:=inverto | | substr(numb, pot, 1);
dbms_output.put_line(‘The given number:’ | | numb);
dbms_output.put_line(‘The inverted number:’ | | inverto);
Example: The following program displays name, qualification and address.
Example: Following program is loaded and executed directly.
dbms_output.put_line(‘Square=’ | | value);
dbms_output.put_line(‘ The square=’ | | value1);
dbms_output.put_line(‘The cube=’ | | value2);
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:
breadth constant number(4):=30;
area number (6,2);
insert into Areas values(length, breadth, area);
Step-3: you create table named as SquNumb using following statements:
SQL > CREATE TABLE sqrNumb(digit number(2), value number(6));
insert into sqrNumb values(digit, value);
When program is executed following output is obtained as shown in figure below:
- Matrix Formulas Matrix Formulas In mathematics the word ‘Matrix’ means the rectangular...
- SQL in RDBMS SQL (pronounced as “ess-queel”) is used to communicate with database....
- Loops in C++ Loops in C++ Loop is used when we want to...
- Programming Language Language is a medium of communication. There are several languages...
- Programming Techniques Software designing is very anesthetic phase of software development cycle....