SQL :

Structure Query Language (SQL), sends one line command to a DBMS. SQL is a declarative language, it renders the user to write queries and presents to the db for execution. It can't execute procedural code with iterative-loops, conditional (if-else-case logics) and sequential (GO TO, ERROR CATCHING, NULL) statements.

The power of SQL is immense, it can fetch almost everything and can handle complex data formatting.

SELECT ENAME,ASCII(SUBSTR(ENAME,0,1))"ASCII",CHR(ASCII(SUBSTR(ENAME,0,1)))"CHR" FROM emp WHERE ROWNUM < 7

Note: some database has additional SQL function like CHAR of MySQL, ASCII value of  CHR(n) in Oracle and PostgreSQL, is not available in MySQL, you have to CHAR

Running ASCII chr

SELECT CHR(67)||CHR(65)||CHR(84) "??" FROM DUAL;

With PostgreSQL you don't need dual keyword

MySQL: "SELECT CONCAT(char(65), char(65), char(65)) "??" FROM DUAL "

It is an application developed in combination with SQL and a Procedural language and, supports object oriented  encapsulation.

 PSQL (PL/pgSQL) :

PSQL is provided by POSTGRESQL DB, for the same purpose to overcome the limits of SQL, but has it's own flavor. For example, the packages of Oracle-PL/SQL, is managed by schemas, functions in PSQL are written as string laterals, RETURN key of Oracle stands as RETURNS in PSQL, thee is no pragma like" PRAGMA AUTONOMOUS_TRANSACTION,   and none the less in using delimiters. Since PSQL can be written in other languages, YOU NEED TO MENTION the name of the language at the end.

SQL * Plus (oracle): Client software by Oracle corporation supporting SQL and PL/SQL .

• An Overview:
  • PLSQL was introduced late 1980s as procedural language based on ADA and Pascal programming languages
  • It is portable and OS independent
  • High performance transaction processing language
    An Example:
    sql> set serveroutput on;
    
    DECLARE
    message varchar2(20):= 'Hello, World!';
    BEGIN
    dbms_output.put_line(message);
    END;
    /
    
    The above is an anonymous PL/SQL block.
• Comments and pragma: Pragma or pseudoinstruction is special instruction to the compiler.It siply passes to an instruction to the compiler.
  DECLARE
  message varchar2(20):= 'Hello, World!';
  pragma EXCEPTION_INIT(no_such_sequence,-2289);
  BEGIN
  dbms_output.put_line(message);
  END;
  /

It is a procedural language strongly integrated with SQL. PL/SQL is Oracle’s procedural language extension to SQL, supporting both static and dynamic SQL, meaning  Static SQL supports DML and transaction control, and dynamic SQL allows DDL embedding, with in a PL/SQL block. PL/SQL allows you to mix SQL statements with procedural statements like IF statement, Looping structures etc.

 PL/SQL program units are generally categorized as follows:

Anonymous blocks :
Stored procedures:

• PL/SQL permits an entire block to a database and hence may reduce the network traffic and increase performance of an application.
• PL/SQL block can query , transform and update data very efficiently.
• Supports object oriented programming
• Exception handling is an added benefit.
• Provides logical, compilation and other errors in the routine.
• PL/SQL applications are portable with enhanced security level.

• Variables and constants
  • Scalar : represent single value, e.g. varchar2,DATE NUMBER, CHAR
  • Composite: collection of components, e.g. Table, VARRAY, RECORD.
  • Reference : uses pointer to a referrals ( another item) e.g. REF CURSOR, REF Object type
  • LOB : holds lob locator, eg. BLOB, CLOB AND NCLOB.
• Control structures:
  • Conditional : IF-END, IF-ELSE-END, IF-ELSEIF-ELSE-END
  • Iterative : FOR WHILE  Loops.
  • Sequential Control :GOTO , NULL
  • CASE
• Procedure / Function
  • A stored procedure or function is a routine task in PL/SQL Block which can be invoked by a client to get desired output.
  • It can be created at
    • Stand alone schema level,
    • with in a package : package sub program
    • within a pl/sql block.
  • Procedure (usually) does not have to return any value to the client, but capable of doing so.
  • Function must return some value to the client.
  • Benefits of stored procedure:
    • It is already compiled and ready to go.
    • Increases performances over line by calls.
    • great for repeated use, as the user's first request is saved in a memory, subsequent calls uses the same memory, increases performances.
  • Parameter:
    • IN: inflow variable provided by the caller.
    • OUT: this parameter can't be 

• Cursor : is a private and temporary SQL-WORK  area to use DML statements, store processing information and execute statements. It returns a single row to the client application.
  • From client's perspective, a cursor is a mechanism to retrieve an arbitrary number of rows using select statement.
  • A cursor can hold hold multiple rows as an "active set" , but sends one row at a time.
  • Implicit : when oracle creates a  cursor when you execute DML statements like SELECT, INSERT, DELETE OR  UPDATE in a query.
    
  • Explicit: When you declare / create a cursor
    
• Trigger: A trigger is associated with an event, when a DML statement  like INSERT, DELETE OR UPDATE is executed to apprehend a database. The execution of trigger is transparent to the user.
  • There are two types of triggers based on the which level it is triggered.
    • 1) Row level trigger - An event is triggered for each row updated, inserted or deleted.
      • Statement:  Trigger Before Statement
        • Row: Row Before triger
        • Row : Row After trigger
      • Statement: Trigger After statement
    • 2) Statement level trigger - An event is triggered for each sql statement executed. It is a default the Trigger.
       It is fired once for a DML statement, irrespective to the number of rows affected during this process. For a "n" number of rows a statement trigger will fire once , where as Row Trigger will be fired for "n" time.

  The statement triggers would have no informati

• Object Type : It is composite data type defined by the user. An instance of "Object Type" is called as an Object of that type.
  • Script and uses:
    create or replace type ObjT1 as object
    ( etag1 varchar2(20),
    stdate date
    );
    /
    create table emp5 (
    eid number (5),fname varchar2(20, lname varchar2(20),
    objC1 hr.ObjT1
    );
    /
    In the above you can insert like this
    insert into emp values
    (1,'Peter', "Pan", objC1('From Disney','2004,5,11'));
• Record : consist of different fields, similar to a row of a database table.
  • Table based records:
  • cursor-based records:
  • user-defined records

A package is a PL/SQL unit that consists of related subprograms and the explicit cursors and variables that they use.

Oracle recommends that you put your subprograms into packages. Some of the reasons are:

• Packages allow you to hide implementation details from client programs.

  Hiding implementation details from client programs is a widely accepted best practice. Many Oracle customers follow this practice strictly, allowing client programs to access the database only by invoking PL/SQL subprograms. Some customers allow client programs to use SELECT statements to retrieve information from database tables, but require them to invoke PL/SQL subprograms for all business functions that change the database.

• Package subprograms must be qualified with package names when invoked, which ensures that their names will always work.

  For example, suppose that you developed a schema-level procedure named CONTINUE before Oracle Database 11g Release 1 (11.1). Release 11.1 introduced the CONTINUE statement. Therefore, if you ported your code to 11.1, it would no longer compile. However, if you had developed your procedure inside a package, your code would refer to the procedure as package_name.CONTINUE, so the code would still compile.

• Package subprograms can send and receive records and collections.

  Standalone stored subprograms can send and receive only scalar parameters—single values with no internal components, such as VARCHAR2, NUMBER, and DATE.