PL/SQL

-
PL/SQL

PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL).
PL/SQL can execute a number of queries in one block using single command.
One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are stored in the database for reuse by applications.
The PL/SQL engine resides in the Oracle engine.
The Oracle engine can process not only single SQL statement but also block of many statements.

Structure of PL/SQL Block:
DECLARE
    declaration statements;

BEGIN
    executable statements

EXCEPTIONS
    exception handling statements

END;
/

Example:
SET SERVEROUTPUT ON;
SQL> DECLARE
       var varchar2(40) := 'I love GeeksForGeeks' ;
     BEGIN
       dbms_output.put_line(var);
     END;
     /

PL/SQL Functions
PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are similar to procedures but must always return a value. 

Create Function in PL/SQ:
CREATE [OR REPLACE] FUNCTION function_name
  (parameter_name type [, …])

— This statement is must for functions
RETURN return_datatype

{IS | AS}

BEGIN
— program code

[EXCEPTION
exception_section;

END [function_name];

Example:
CREATE OR REPLACE FUNCTION factorial(x NUMBER)
  RETURN NUMBER
IS
  f NUMBER;
BEGIN
  IF x = 0 THEN
    f := 1;
  ELSE
    f := x * factorial(x - 1);
  END IF;
  RETURN f;
END;
/


PL/SQL Views
PL/SQL views are saved queries that you can use to simplify complex data access. They act as virtual tables, presenting a subset of data from one or more tables.
Syntax
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;
Example:
CREATE VIEW employee_view AS 
  SELECT employee_id, first_name, last_name, department 
  FROM 
  employee;
Run: select * from employee_view;

PL/SQL Triggers
PL/SQL triggers are block structures and predefined programs invoked automatically when some event occurs. 
They are stored in the database and invoked repeatedly.

Syntax:
CREATE OR REPLACE TRIGGER trigger_name
BEFORE or AFTER or INSTEAD OF //trigger timings
INSERT or UPDATE or DELETE // Operation to be performed
of column_name
on Table_name
FOR EACH ROW
DECLARE
  Declaration section
BEGIN
  Execution section
EXCEPTION
  Exception section
END;
/

Comments

Post a Comment

Popular posts from this blog

JAVA8 Features

-
Java8 Features 1) Lambda Expressions : Concise functional code using ->. Lambda Expression basically expresses an instance of the functional interface, Lambda Expression provides a clear and concise way to represent a method of the functional interface using an expression. 2) Functional Interfaces : Single-method interfaces. An interface that contains only one abstract method is known as a functional interface. There is no restriction, that can have n number of default and static methods inside a functional interface. A functional interface is an interface that contains only one abstract method.  @FunctionalInterface annotation is used to ensure that the functional interface can’t have more than one abstract method. Runnable, ActionListener, and Comparable are some of the examples of functional interfaces.   Runnable –> This interface only contains the run() method.   Comparable –> This interface only contains the compareTo() method.   ActionListener –> ...
Read more

Build Automation

-
ANT: Ant was the first build tool released in 2000. Ant was developed on procedural programming idea. Ant was improved with an ability to accept plug-ins and dependency management over the network, with the help on Apache-IVY. Drawbacks: 1) XML is used as a format to write the build scripts. 2) Being hierarchical is not good for procedural programming, and 3) XML is relatively unmanageable.   MAVEN: Maven was introduced in 2004. Maven relied on the conventions and was able to download the dependencies over the network. Benefits : Life cycle of Maven, while following the same life cycle for multiple projects continuously. Problems: 1) Maven does not handle the conflicts between versions of the same library. 2) Complex customised build scripts are difficult to write in Maven, as compared to writing the build scripts in ANT. GRADLE : Gradle came into picture in 2012 Features  Declarative builds and build-by-convention 1) Gradle is available with separate Domain Specific Language ...
Read more