Skip to main content

PL SQL

Points to Remember : -
  • PL/SQL stands for PROCEDURAL language extensions to SQL.
  • PL/SQL extends SQL by adding programming structures and subroutines available in any high level language.
  • PL/SQL can be used for both server-side and client-side development.
  • PL/SQL has syntax and rules that determine how programming statements work together.
  • PL/SQL is not a standalone programming language, hence it cannot be used individually for developing applications.
  • PL/SQL is a part of the ORACLE RDBMS, and hence can reside in two environments, the client and the server.
  • Any module that is developed using PL/SQL an be moved easily between server side and client side applications.
  • Either in client or server environment any PL/SQL block or sub routine is processed by the PL/SQL engine.
  • PL/SQL engine is a special component that processes and executes any PL/SQL statements and sends any SQL statements processor.
  • The SQL statement processor is always located on the oracle server and hence all SQL Statements are processed on the server only.As per the necessity the PL/SQL engine can be located either at ...

            1) SERVER side.
          2) CLIENT side.

  • When PL/SQL engine is located upon the SERVER. The whole PL/SQL block is passed to the PL/SQL engine on the ORACLE SERVER for processing including client components.
  • This concepts gives security, less load and performance for applications.
  • When the PL/SQL engine is located upon the client, the PL/SQL processing is done on the client side. All SQL statements that are embedded within the PL/SQL block, are sent to the ORACLE SERVER for further processing.
  • If the PL/SQL block doesn't contain any SQL statements, the entire block is executed on the client side itself.
  • Because of the above concept, the application load in the network will highly reduce and the system becomes highly loose coupled in the development process, giving high accessibility for easy maintenance.


Advantages : -

  • PL/SQL improves server performance by reducing the calls from your application to ORACLE.
  • Without PL/SQL, the Oracle server must process eash SQL statement individually, that results in high performance overhead on the server due to additional call for each segment.
  • Your application can pass numerous SQL statements to Oracle at once that improves      network traffic.
  • All PL/SQL code is portable to any operating system and platform on which Oracle runs.
  • Recompilations are minimized because packages do not require to be recompiled if procedures within the package are redefined.
  • PL/SQL enables conditional and sequential control statements, theus provides tremendous programming flexibility.
  • Modularity is promoted because PL/SQL lets you break down an applicatio into manageable ,well-defined logic modules.
  • PL/SQL makes error detection and handling easy.
  • Support for object oriented programming.
  • Tight Security.


Comments

Post a Comment

Popular posts from this blog

About Oracle Buffer

About Oracle Buffer :- All Commands of SQL are typed at the SQL prompt. Only one SQL statement is managed in the SQL buffer. The current SQL statement replaces the previous SQL statement in the buffer. The SQL statements can be divided into different lines within the SQL buffer. Only one line i.e, the current line can be active at a time in the SQL buffer. At SQL prompt, editing is possible only in the current SQL buffer line. Every statement of SQL should be terminated using semi colon " ; ". One sql statement can contain only one semi colon. To run the previous or current SQL statement in the bufer type " / " at SQL prompt. To open the SQL editor type " ED " at SQL prompt.
Post a Comment
Read more

Oracle Object Oriented Concepts

An Object is a reusable application component that developers need to be aware of, rather than how it works. Object are basic entities in a system. They could represent a person, place, bank account, or any item that is handled by program. Every object consists of an attribute and one or more methods. An attribute could be any property of the object. Class:           It is a collection of attributes and functions (method) to plan the object. Object Table : ·    Object table are created by using the user defined data types. ·        In an object table each row or record is treated as an object. ·        Each row in an object table has an object Identified (OID), which is unique through out       the   database. ·       The rows or objects of an object table can be referenced by other objects with in the  database. ·    ...
Post a Comment
Read more

Filtering and Sorting data using SQL statements

  Filtering & Sorting Data using SQL     Once you know the basics ( SELECT , FROM , WHERE ), filtering and sorting helps you get the exact rows from tables .   1. Filtering with Conditions ( AND , OR , NOT )  - Use WHERE to filter rows. - Combine with logical operators :     • AND: all conditions true      • OR: any condition true      • NOT: negate condition   - - All conditions true (AND) SELECT *  FROM employees WHERE department = 'Sales'    AND salary > 50000; -- Any condition true (OR) SELECT *  FROM employees WHERE department = 'Sales'     OR department = 'Marketing'; -- Negate condition (NOT) SELECT *  FROM employees WHERE NOT department = 'HR';   2. Pattern Matching with LIKE   - % = any sequence, _ = single char     • Names starting with 'J'      • Names with 4 letters starting with 'J'   - - N...
Post a Comment
Read more