What is a PLSQL

 Next Chapter

Pl sql is stands for Procedural Language & is use to processes the PL-Sql  statements . it is developed by oracle corporation.

you can use these free online training with examples.

A PLSQL  code can be stored on client side or in the database  that is server side.

In this tutoral you will learn about the PL-SQL block & how to execute the first

pl-sql block.
PLSQl block is organize the entrire code into block. & block without name is

called anonymous block. it does not saved in the oracle database.
This anonymous is only one use .
The Follwing is the syntax of annonymous block.
[declare]
Declaration statements;
Begin
Execution statements;
[Exception]
Exception handling statements;
End;
/

If you execute above code you will see a message on the screen that
PL_SQL procedure successfuly completed.

Let us take the example of annonymous block.



DECLARE
  employee_id NUMBER(6);
BEGIN
  SELECT employee_id
  INTO   employee_id
  FROM   employees
  WHERE  last_name='Kochhar';
  DBMS_OUTPUT.PUT_LINE(employee_id);
END;


To display database output on the screen you need to use set serveroutput on

command to instruct sql *plus to echo database output after executiong pl sql

block.
sql> SET SERVEROUTPUT ON

In above example we use dbms_output.put_line  built in package to output a string
on the screen

Example 2.

VARIABLE g_annsal NUMBER

BEGIN
  SELECT (salary+salary*nvl(commission_pct,0))*12
  INTO   :g_annsal
  FROM   employees
  WHERE  employee_id=&empid;
END;
/
PRINT g_annsal

Now you login to the  oracle database by providing username & password.
username :-hr
password :- hr

Login Screen

Example 2 :

DECLARE
  V_NAME VARCHAR2(20);
  V_SAL  NUMBER;
BEGIN
  SELECT LAST_NAME,SALARY
  INTO   V_NAME,V_SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;

  DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('NO SUCH EMPLOYEE...');
END;

Example 3:-

DECLARE
  V_NAME VARCHAR2(20);
  V_SAL  NUMBER;
BEGIN
  SELECT LAST_NAME,SALARY
  INTO   V_NAME,V_SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;

  DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('NO SUCH EMPLOYEE...');
END;