Basic structure of PL/SQL

This forum is for Database developer ( PostgreSQL, MySQL, SQL Server, Oracle etc...) and database Administrator
tushar
Posts: 2
Joined: Sat Sep 27, 2014 2:12 pm
Contact:

Basic structure of PL/SQL

Postby tushar » Wed Oct 01, 2014 10:25 am

What is the basic structure in PL/SQL? What is the Architecture of PL/SQL?


Guest

Re: Basic structure of PL/SQL

Postby Guest » Wed May 06, 2015 11:59 pm

PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL.

The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program.

Guest

Re: Basic structure of PL/SQL

Postby Guest » Thu May 07, 2015 12:02 am

PL/SQL is block structured language divided into three logical blocks.

BEGIN block and END; keyword are compulsory, and other two block DECLARE and EXCEPTION are optional block. END; is not a block only keyword to end of PL/SQL program.

PL/SQL block structure follows divide-and-conquer approach to solve the problem stepwise.

Code: Select all

DECLARE
-- this section is optional
  number1 NUMBER(2);
  number2 number1%TYPE    := 17;             -- value default
  text1   varchar4(12) := 'Hello world';
  text2   DATE         := SYSDATE;        -- current date and time
BEGIN
-- this section is mandatory, must contain at least one executable statement
  SELECT street_number
    INTO number1
    FROM address
    WHERE name = 'INU';
EXCEPTION
-- this section is optional
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(SQLCODE )  );
     DBMS_OUTPUT.PUT_LINE('Error Message is ' || SQLERRM   );
END;

Guest

Re: Basic structure of PL/SQL

Postby Guest » Thu May 07, 2015 12:04 am

PL/SQL program units organize the code into below blocks. The block without name is known as anonymous block.
The anonymous block is the simplest unit in PL/SQL. It is called anonymous block because it is not saved in the Oracle database.

The following illustrates anonymous block syntax:


[DECLARE]
Declaration statements;
BEGIN
Execution statements;
[EXCEPTION]
Exception handling statements;
END;



Return to “PostgreSQL, MySQL, SQL Server, SQL,PL/SQL, TeraData Developer and DataBase Administration”

Who is online

Users browsing this forum: No registered users and 0 guests