Udjuni.com

Mathematics

Science

Computing

Accounting

Business

History

Agriculture

Biology

PL/SQL variable declarations and Block structure

As noted in the previous posting introduction to Oracle PL/SQL has many different features and capabilities , such as the use of block structure to separate certain functions or tasks from others. Under normal circumstances, each block is used to perform a logical unit of work. This also makes it easy for humans writing and debugging the code know what part of the code does what? Instead of looking all over the place to find the code that performs a certain task.

Variable declaration

Variables in PL/SQL are easy to declare. Basically, all you have to do is use the keyword DECLARE followed by the variable name and then the variable data type. There is nothing more to it, it is as simple as that. Remember though, that every statement in Oracle ends with a semicolon (;). An example of variable declaration is as follows:

DECLARE vfirstname varchar(50);

        vlastname varchar(50);

The above statement, declares two variables, vfirstname and vlastname; both of type variable length characters. You can name your variables anything you want.

How PL/SQL blocks work

Some of these functionalities are better explained by means of an example, so we will dive into an example of how block structures are used.

declare 

  vsalaries number(7);

  vjobtitle varchar(100);      

 


begin

    select min_salary, job_title into vsalaries,vjobtitle from jobs where min_salary = 2000;

    if vsalaries < 5000 then

        insert into low_earners(job_title,min_salary) values(vjobtitle, vsalaries);

    end if;

end;

The code above uses a combination of variable declaration and PL/SQL block structure. It first declares two variables, and then populates those variables with data from min_salary and job title for an employee who earns exactly $2000. Then it uses the block structure (IF-THEN statement) to evaluate the value of the variable vsalaries. If that value is less than $5000 we insert a row with the value of the job title and minimum salary into the table "LOW_EARNERS".

If we issued the following query after executing the one above, we get the following results:

 Query: SELECT job_title, min_salary FROM low_earners;

We get the following results:

JOB_TITLE                                   MIN_SALARY 
-------------------------------------------------------
Stock Clerk                                   2000

The next few postings will explore other features of the PL/SQL language such as Looping constructs, functions, cursors, packages and collections.

Introduction to Oracle PL/SQL
PL/SQL stands for Procedural Language/ SQL, and is a pretty sophisticated language used to access Oracle databases. It is integrated within the server environment to ensure smooth and fast execution of PL/SQL code or queries. The bigger part of this language is the SQL componen

Oracle Procedures, Functions and looping constructs
Procedures and functions in PL/SQL are types of blocks that are stored in the database in a compiled form. This is among the major differences between ordinary queries and procedures and functions (otherwise known as subprograms). Creating a function or procedure requires the u

Photosynthesis and structure of leaves

Choose a Structure

variable

PHP Variables
Variables In PHP, unlike other programming languages you can declare a variable or allocate variable storage space without worrying about the type of data that will be stored in it. Thus values are used when data in your script becomes available and can change during the lifetime o

Solving linear equations with two variables
In the last posting titled "Basic Linear equations", we covered the basics of solving an equation with one "unknown". There are rules that govern linear equations that we did not cover in the last posting. I will be covering those rules here before we move on to linear equations with two unkn

Exponents of Variables

Of Variables

Java variables and data types
A variable is defined as a named piece of memory that a java program uses to store data. Each of these storage pieces can only store data of a certain type. In other words, if you defined as variable of type integer, that variable can only hold inte

Udjuni.com

PL/SQL variable declarations and Block structure

Variable declaration

How PL/SQL blocks work

RELATED ARTICLES: