• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » Oracle PLSQL Block Structure and Types

Oracle PLSQL Block Structure and Types

March 6, 2021 by techgoeasy Leave a Comment


Here we will be discussing about Oracle PLSQL Block Structure and types

Table of Contents

  • What is PL/SQL
  • Why Use PLSQL
  • PLSQL Block Structure
  • PLSQL Block Types
  • Difference  Between  Function and Procedure
  • Oracle PLSQL Block Syntax and Guidelines

What is PL/SQL

Oracle PLSQL Variable


PL/SQL is Oracle’s proprietary extension to SQL that allowsfor procedural language capabilities. It gives all the features of modern software engineering such as data encapsulation, exception handling, information hiding and object orientation

Why Use PLSQL

1) Integration with development tools like Oracle forms, Reports

2) Multiple statement can be grouped into single plsql block   and send to the oracle server which result in less network traffic

3) Modularized program development: Breakdown the complex logic/problem into set of manageable, well defined, logical modules and implement them using blocks

4) Portability: Since it is native to Oracle server, it can be moved easily

5) It provide procedural language control structures like if condition, looping

6) It has the capability to handle errors and raise exception based on it

PLSQL Block Structure

A plsql block is made in three section Declare, executable and exception.

DECLARE(Optional) – this section contains variables, constants, cursors, and user-defined exceptions.
BEGIN(Mandatory)

EXECUTABLE – this section contains any SQL statements.

EXCEPTION (optional)- this section contains the error handlers.

END(Mandatory);

Oracle PLSQL Variable

So Declare and exception section are Optional.

Example Block structure

DECLARE 
 l_number  NUMBER;
 BEGIN
 l_number := 1;
 Dbms_output.put_line(l_number);
 Exception
 When others then
 Dbms_output.put_line(‘Exception occurred’);
 END;
 /

Important points to Note down

1) All the variables and constants are defined in declare section

2) Place a  semicolon ; at the end of sql statement or plsql control statement

3) In PL/SQL all the errors are handled in the Exception block.

4) Begin and End are mandatory statements indicating begin and end of the PL/SQL Block.

5) Variables and Constants must be declared first before they can be used.

6) Values can be assigned to variables directly using the “:=” assignment operator, by way of a SELECT … INTO statement or When used as OUT or IN OUT parameter from a procedure.

7) Section keyword Declare, Begin  ,exception are not followed by semicolon

8) End and all other plsql statement  requires  a semicolon to terminate the statement

PLSQL Block Types

Anonymous: Anonymous Blocks are unnamed blocks. They are declared at the point in application where they need to be executed

DECLARE 
 l_number  NUMBER;
 BEGIN
 l_number := 1;
 Dbms_output.put_line(l_number);
 Exception
 When others then
 Dbms_output.put_line(‘Exception occurred’);
 END;
 /

Function: These are names PLSQL block that can accept parameter and compute some function and return it. It can be stored in Oracle server or application

Syntax
FUNCTION name [(parameter[, parameter, …])] RETURN datatype IS
 [local declarations]
 BEGIN
 executable statements
 [EXCEPTION exception handlers]
 END [name];
 Example
 Create or replace function FUNC return number
 As
 l_number  NUMBER;
 BEGIN
 Select count(*) into l_number  from emp;
 return(l_number)
 END;
 /

Procedure: These are names PLSQL block that can accept parameter and process some information and may or may return values. It can be stored in Oracle server or application

Syntax
 PROCEDURE name [(parameter[, parameter, …])] IS
 [local declarations]
 BEGIN
 executable statements
 [EXCEPTION exception handlers]
 END [name];
 Example
 Create or replace procedure  remove_emp (emp_id  number)
 As
 BEGIN
 Delete from emp where employee_id=emp_id;
 END;
 /

Difference  Between  Function and Procedure

FunctionProcedure
Function must return a valueProcedure need not
Syntax Syntax
FUNCTION name (argument list …..) Return datatype isPROCEDURE name (parameter list…..)
local variable declarationsis
Beginlocal variable declarations
executable statementsBEGIN
ExceptionExecutable statements.
execution handlersException.
End;exception handlers
 end;
  
Function can be used in SQL with some restrictionProcedure cannot be called directly from SQL.

Oracle PLSQL Block Syntax and Guidelines

1) Character and date literals must be enclosed in single quotation marks

2) Place multiple line comments between /* and */

3)  Most of the sql functions can be used in  PLSQL .We have char,date,number function available in PLSQL just like SQL

4) group by function are not available in PLSQL. It can be only used in the sql statement in the PLSQL

5) We should use proper indentation to make the code readable

6) It is recommended to write DML statement in Upper case , PLSQL Keywords ,Datatypes in Upper case and Put identifiers and parameter in lower case for better readability and maintenance

Related links

Oracle PLSQL documentation
Most commonly asked 25 Oracle PlSQL interview question
Oracle sql and plsql
how to write sql queries


Filed Under: Oracle, Oracle Sql Tagged With: Oracle PLSQL Block Structure and Oracle PLSQL Variable

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • How to check Tablespace in Oracle -Size ,Free space,datafiles ,growth
  • Query to check table size in Oracle database
  • how to check all constraints on a table in oracle
  • Oracle Indexes and types of indexes in oracle with example
  • ORA-01017 Oracle Error Resolution Tips



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us