PL/SQL in Database Management System(DBMS).

-
PL/SQL is acronym for procedural language and SQL is Structure Query Language /Sequential Query Language/Sequel Sequential English Like Query Language.

 It is a programming language which embeds SQL queries inside a program and hence sometimes it is also called embedded SQL.

 In such a programming language we can write program programming construct such as if else, switch case . for , while. do while, goto etc. along with a query .
 we can also declare different variable ,assign values , read data from keyboard , display data on monitor and define complex objects similar to structure .

 PL/SQL program is classified into basic two types of program which are :
 
1. Unnamed blocks
2. Named block

Unnamed block are also called anonymous bock that is such a block are not  given any program name and hence they can created ones and used but can not be stored for future used.

 Syntax:

 Declare
 ............block......
 Begin
 .............block......
 Exception
 ..............block.....
 end;
 /


 Declare:
Very first section of a program is declare section which is an optional section , in this section we have to define all the variable  that will reused in the program.
Variable can be some simple variable such as Integer , Character , Varchar , Number , date etc. Variable may include user define variables also created using type.
Declare can also have input statements.

Begin:
The second section of a block is begin section which is Begin section which is compulsory section.
In this section the executable program is written. It may include the statement such as if , else ,while, do-while , case , goto etc. It may also have the query that might be needed input , output statement cursor.

Exception:
The third section of a program is the exception section which is an optional section. In this section we specify exception handling statement , where exception is define as a runtime error.

End:
The last section of program is end section, which has to be terminated by ';' and '/' operator. It is also mandatory in program.

Note: We should always write set server output on before starting of blocks.

Example:

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE (MY FIRST PL/SQL'')
END;
/

2. Write a PL/SQL block to take an integer variable and print its sequence.

SET SERVEROUTPUT ON
DECLARE
   a integer:=&a;
BEGIN 
    DBMS_OUTPUT.PUT_LINE ("sequence of "||a||'is'||a*a');
END;
 /  
 OR

SET SERVEROUTPUT ON
DECLARE
   a integer;
BEGIN 
    DBMS_OUTPUT.PUT_LINE ('ENTER A VALUE FOR A');
      a=&a;
     DBMS_OUTPUT.PUT_LINE ("sequence of "||a||'is'||a*a');
END;
 /  

Named block: Named block are the programs stored as database object, such programs are given their name and can be stored with the name given ti it. Such program can also  be used in other program by simply calling them with their name . There are following three type:

1. Procedure
2. Function
3. Package
  

Comments

Post a Comment

Popular posts from this blog

Introduction to Python.

-
What is python? Python is an object-oriented programming language created by Guido Rossum in 1998. If you just beginning your programming career, python suits you best. With python you can do everything from GUI developement, web application, Machine learning, IOT, Game Developement etc. so it is general purpose high level programming language. It is very easy language,it is good for beginners. It is case sensitive language. Python is Dynamically Typed, Write less code do more. Introduction Easy to Learn and Use Python is easy to use and learn, it is user friendly and high level programming language. Expressive language Pyton language is more expressive means that is more understand and readable. Unique style Python is an interpreted language i.e interpreter execute the code line by line at a time . This makes debugging easy and thus suitable for beginners. Cross Platform Language Python can run equally on different platform such as windows, Linu...
Read more

Decision Making Statement.

-
Image
Conditional Statement Decision making of an anticipation of condition occuring by execution of program and dpecific action is taken according to condtion. Following is a general form of typical decision making structure found in most programming language. Python programming language uses any non-zero or non-null value as true and if it is either zero or null then it is assume false value. if Condition: In python the block of statement is executed if the condition is true. Syntax if (condition): Block(s) Example var1=100 if var1: print('i got an true value') print(var1) output i got an true value 100 Question: Write a program that prompt user to enter two integer values, print the message "equal" if both entered value are equal a=int(input("Enter first number")) b=int(input("Enter second number")) if a==b: print("equal") output Enter fir...
Read more