What is PL/SQL? Why use PL/SQL over SQL.

PL/SQL

Why PL/SQL?

  1. Supports Object-Oriented Programming
  2. Support for SQL programming
  3. In SQL we can not write more than one statement but PL/SQL gives us the flexibility to write.
  4. PL/SQL supports looping.
  5. Useful in writing complex queries.

Let's understand it with an example you are given 1000 columns have to select 100 columns and display the output. Now using SQL statement you have to select all 100 columns manually and then you can display the output.

Eg:

  1. SELECT col_1,col_2,col_3,col_n

2. FROM table_name

Now using PL/SQL you can just use the loop and set the condition to terminate the loop at 100.

Eg:

  1. SET SERVEROUTPUT ON;
  2. DECLARE
  3. v_dept number;
  4. cursor c_cursor is SELECT * FROM table_name;
  5. BEGIN
  6. dbms_output.put_line(‘100 columns are :’);
  7. OPEN c_cursor;
  8. LOOP
  9. FETCH c_cursor INTO v_dept;
  10. EXIT WHEN c_cursor%notfound;
  11. dbms_output.put_line(v_dept);
  12. END LOOP;
  13. CLOSE c_dept;
  14. END;
  • So what is happening underhood, firstly in DECLARE block I am defining a variable with the number data type.
  • Same declare block defining cursor(it is like a pointer or simply saying I am storing my all columns in c_cursor variable).
  • Now coming to BEGIN block, here I am opening my cursor and then looping with the condition if cursor not found then exit or c_cursor not found statement become true then exit.
  • Now using dbms_output.put_line to output the columns.
  • Now ending loop and cursor and exiting the programming before execution.
  1. DECLARE
  2. variable_name variable_dataType;
  3. BEGIN
  4. dbms_output.put_line(“My first program”);
  5. END;
  6. /

Some of the basic syntaxes of the loop are very useful in programming.

  1. DECLARE
  2. counter number;
  3. BEGIN
  4. for counter in 1..10
  5. dbms_output.put_line(counter);
  6. end loop;
  7. EXIT;
  8. /

Output “Number found” when number 10 is found.

  1. DECLARE
  2. num number:=10;
  3. BEGIN
  4. if num>20
  5. dbms_output.put_line(“Number found”);
  6. els if 10≥num<20
  7. dbms_output.put_line(“Number found”);
  8. else
  9. dbms.out_put_line(“Number found”);
  10. end if;
  11. END;
  12. /

So this is the simple program of if/else statement for better understanding of PL/SQL statement.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store