Skip to main content

What is PLSQL ?

PL/SQL is Oracle's procedural language extension to SQL. It provides a server-side, stored procedural language that is easy-to-use, seamless with SQL, robust, portable, and secure. The PL/SQL compiler and interpreter are embedded in Oracle Database, providing developers with a consistent and leveraged development model on both the client and the server side. In addition, PL/SQL stored subprograms can be called from Oracle clients. PL/SQL enables you to mix SQL statements with procedural constructs.
With PL/SQL, you can create and run PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks, stored subprograms, and packages.

The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program can be nested inside one another.

You can place declarations close to where they are used, such as inside a large subprogram. The declarations are local to the block and cease to exist when the block completes, helping to avoid cluttered namespaces for variables and procedures.

You can nest blocks in the executable and exception-handling parts of a PL/SQL block or subprogram, but not in the declarative part. You can define local subprograms in the declarative part of any block. You can call local subprograms only from the block in which they are defined.

Anonymous block
An anonymous block is a PL/SQL block that appears in your application and is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear. A PL/SQL block groups related declarations and statements.

Stored or standalone subprogram
A stored or standalone subprogram is a PL/SQL block that Oracle stores in the database and can be called by name from an application. Subprograms can be procedures or functions; the difference is that functions return a value when executed. When you create a stored subprogram, Oracle parses the subprogram and stores its parsed representation in the database. See Subprograms and Packages: Usage Information.

Package
A package is a group of subprograms and variable definitions that Oracle stores in the database. Subprograms and variables in packages can be called from other packages or subprograms.

--Extract from Oracle documentation

Comments

Post a Comment

Popular posts from this blog

Only native data types are supported in PLSQL programming

create or replace package check1 is procedure p1(a1 integer); procedure p1(a1 number); end check1; / create or replace package body check1 is procedure p1(a1 integer) as begin dbms_output.put_line('Using Integer'); end p1; procedure p1(a1 number) as begin dbms_output.put_line('Using Number'); end p1; end; / --The above code gets compiled successfully. --But when you try to execute it .. begin check1.p1(8779); end; --See what happens.............. This concludes ONLY different native datatypes can be used in PLSQL OO implementation. Happy learning!!!!!
2 comments
Read more

Editor's Column - Traditional DBMS

Editor's column There were days when Rooms-sized processors used for processing data. IT boomed up at the start of 21st Century and so many innumerable inventions made Computer world and few among them are still up in the markets for thier better implemented programming Alogrithms which couples with computer hardware and software ( OS ). Textpads or notepads were the repository of data and programmers had to be Experts to do data manipulation. In other words, Programmers had to use thier own logic to do insert , delete, update in data source. For instance, Consider the following: file1.txt: 1 I am line number 1 2 I am line number 2 3 I am line number 3 4 I am line number 4 5 I am line number 5 6 I am line number 6 7 I am line number 7 8 I am line number 8 9 I am line number 9 10 I am line number 10 To programmatically delete 8 th record , developer had to use the following logic with OLD file handling methods in 3rd generation language. 1. Open file1.txt, an...
Post a Comment
Read more

PLSQL programming ( Learn by examples - Part 3 )

/** In this section , lets learn the following. How to use a for loop to process multiple records... **/ set serveroutput on size 100000 begin --Select objects of table type that are valid under the schema Susil for var in (Select * from all_objects where object_name not like '%$%' and object_name in ('EMPLOYEE','EMP') and status='VALID' and owner='SUSIL') loop if var.object_type='TABLE' then /* Dbms_metadata package provides us a function get_ddl to get the data definition on object. For your understanding , i have just chosen two tables in this example If you remove Object_name condition in the above SQL , it will print DDL definitions for all tables under the specified schema **/ dbms_output.put_line('====================================================================='); dbms_output.put_line('Table name =>'||var.objec...
Post a Comment
Read more