PLSQL

PL/SQL is a procedural language extension for SQL. It is the combination of procedural, data manipulation language. Oracle 6.0 introduced PL/SQL.

To view version of pl/sql: select * from v$version;

Basically pl/sql is a block structured:

Declare [optional]

variable declarations,

cursors,

undefined exceptions

Begin (mandatory)

DML, TCL

select – into —

conditional,

control statements;

Exception (optional)

Handling Exceptions

End; (mandatory)

There are two types of blocks supported bfr p t/sq l:

  1. Anonymous blocks
  2. Named blocks

Anonymous blocks : These blocks does not have a name & also not stored in database and we are allowed to call these blocks in another blocks or in client application.

Example:

Declare

———

———

Begin

———

———

end;

Named blocks: These blocks having a name and also autom atically Stored in database. These blocks used by all types of programmers in all applications, these are procedures, functions, triggers, packages, and so on

PL/SQL Datatypes, variables:

it supports all

  1. sql datatypes and boolean datatypes
  2. LOB’s (clob, blob, bfile)
  3. composite datatype
  4. ref objects
  5. non-plsql varables / bind variables / host variables

variable: Variable is used to store a single value into memory locations.

Syntax for declaring a variable:

Variablename datatype ( si ze );

Generally we are declaring variables in declare section of the pl/sql block.

Example:

declare

a number(10);

b number(10);

Storing a value into variable:

by using assignment operator “:=” we can store a value into variable.

Syntax :- variablename := value;

like : x := 50;

Display message or variable value:

dbms_output.put_line(‘message’);

dbms_output -> package name

put_line -> procedure name

dbms_output.put_line(variablename);

example:

sql > set serveroutput on

sql >begin

dbms_output.put_line(‘test output’);

end;

output: test output

 

PLSQL Architecture

 

select — into —- clause

This clause is used to get data from table and storing into pl/sql variables. Select – into – clause always returns single record or value at a time.

Syntax : select col1, col2, …. into var1, var2 … from tablename where condition

This clause is used in executable section of pl/sql program.

Example : 1) write a pl/sql program for user entered empno; display name of the employee and salary from emp table.

Solution : declare

v_name varchar2(10);

v_sal number(10);

begin

select ename, sal into v_name, v_sal from emp where empno = &no;

dbms_output.put_line(v_name|| ‘ ‘ || v_sal);

end;

Powered by k2schools