Sunday, March 31, 2013

PL/SQL Introduction

What is PL/SQL?
  • PL/SQL stands for Procedural Language extension of SQL.It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
  • In SQL we can’t execute more than one statement concurrently, whereas in PL/SQL we can execute more than one statement simultaneously.
  • In SQL we can’t execute the same statement repeatedly to a particular number of times, where as it is possible with PL/SQL.
  • PL/SQL supports all the procedural language concepts such as control statements, conditional statements, procedures, functions etc. and also it supports some features of object oriented programming principles such as polymorphism, exception handling etc.
  • PL/SQL is a combination of SQL along with the procedural features of programming languages.
  • All procedure language statements are executed by PL engine whereas all SQL statements are executed by SQL engine.
  • PL/SQL is the procedural implementation of SQL i.e. you can pass sql statements in procedural format using PL/SQL. Normal sql does not have any procedural capabilities moreover you can only pass one statement at a time to Oracle Engine. Hence, PL/SQL have come up to avoid this limitation. Hence, PL/SQL is the structured programming language for oracle. It's structure is similar to any other procedural language such as C or C++

Data Types and Operators in SQL :
The data types and Operators which are using in SQL , the same data types and Operators are supported by PL/SQL except one operator ‘:=’.

NOTE: If you want more information regarding this Oracle SQL Datatypes just visit this Link.

Input statement in PL/SQL :
There are no input statement or input function in PL/SQL to input the values at runtime. If we want to give the input values at runtime then we are using insertion or substitution operator i.e &

Output statement in PL/SQL :

  • The above two statements represents output statements in PL/SQL which is used to print the values on the standard output device.
  • The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.

  • The DBMS_OUTPUT package is typically used for debugging, or for displaying messages and reports to SQL*DBA or SQL*Plus (such as are produced by applying the SQL command DESCRIBE to procedures).

  • The PUT Procedure and PUT_LINE Procedure in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE Procedure and GET_LINES Procedure.

Examples :
Type:-1 dbms_output.put_line(‘Message’);// display content in quotes
Ex: dbms_output.put_line(‘techsnib’);

Type:-2 dbms_output.put_line(‘sum is ‘ || s); // display content in quotes with value

Type:-3 dbms_output.put_line(variable); // display variable value
Ex: dbms_output.put_line(c);

  • To get more information on DBMS_OUTPUT package click this Link

No comments:

Post a Comment

back to top