Oracle PL/SQL Basics

PL/SQL basics are important to write programs.

Block Structure:
DECLARE
variable declaration;
BEGIN
execution statements;
EXCEPTION
execution statements;
END;

Blocks are two types:

Anonymous Block:
The block having no name called as anonymous. This cannot be called. Used in forms.

Named Block:
The black having names. It can be called. Examples are package,procedures, functions, triggers,…

DELCARE
——
——
BEGIN
——
——
END;

Variables:

  • Variables are used to store data values that are used by PL/SQL.
  • Represented memory location used to store data.
  • We need a datatype.
  • Boolean is also accepted.

Example:

pno number(20):=567;
pname varchar2(30) not null;
pdate date;

Comments:
–single line comment.
/*
——
—— multi line comment
——
*/

dbms_output.put_line(”): This is used to print variables and messages.
Where
dbms_output –> Is the package
put_line –> is the function of package.

Simple Programs in PL/SQL: By default serveroutput set as off. We have to set it as ”

1) The smallest program in PL/SQL
BEGIN
null;
END;

2. Printing output as Oracle
BEGIN
dbms_output.put_line(‘Oracle’);
END;

3. Printing custom output
DECLARE
pfno NUMBER;
pfname VARCHAR2(30);
pfsal NUMBER;
BEGIN
pfno=&pfno;
pfname=&pfname;
pfsal=&pfsal;
dbms_output.put_line(‘The employee ‘||pfname||’ no is ‘||pfno||’salary is ‘||pfsal);
END;

Variable Attributes:

  • Variable attributes are used in place of datatypes in variable declarations.
  • Whenever we are using variable attributes, then Oracle server automatically memory for variables based on corresponding column datatypes in a table.
  • These attributes are also called as anchor notations.

PL/SQL having two types variable attributes:Column Level Attributes and Row Level Attributes

Column Level Attributes:

    • In this method, we define attributes for individual columns.
    • Column level attributes are represented by using %TYPE.

Whenever we are defining column level attributes Oracle server automatically allocates memory for the variables as same as corresponding column datatype in a table.

Syntax:
Variable_Name table_name.column_name%TYPE;

Example:

DECLARE
p_ename emp.ename%TYPE;
p_sal emp.sal%TYPE;
p_hiredate emp.hiredate%TYPE;
BEGIN
SELECT ename, sal, hiredate into p_name, p_sal, p_hiredate
FROM emp WHERE empno=&no;
dbms_output.put_line(p_ename||’ ‘||p_sal||’ ‘||p_hiredate)
END;
/

Row Level Attributes:

  • In this method, a single variable can represent all different data types in a row within a table.
  • It is also same as structures in C language.
  • Row level attributes represented by using %ROWTYPE.

Syntax:
Variable_Name table_name_name%ROWTYPE;

Example:

DECLARE
p emp%ROWTYPE;
BEGIN
SELECT ename, sal, hiredate into p.name, p.sal, p.hiredate
FROM emp WHERE empno=&no;
dbms_output.put_line(p.ename||’ ‘||p.sal||’ ‘||p.hiredate)
END;
/

Powered by k2schools