Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Understanding Oracle PL/SQL

If you are a newbie, in this article we will discuss about PL/SQL, an important programing language in Oracle.

As we know that data is kept in database using the database language which is SQL language, but did you ask yourself what is the dynamic language that control that queries and control what user need to do on the program.

If you ask yourself this question then the answer will be in that article and the answer would be : it’s PL/SQL.

PL/SQL is the language which employ the thoughts of user on the program for example if you are the user and want to make a small process like you enter a number of names and store them on database then how you will take that names from user? And how you will store them? And how you will save the process you did on system? Simply all that question PL/SQL language will answer all this questions.

PL/SQL queries consist of:

  • Declare.
  • Begin.
  • Exception.

1. DECLARE

It’s the first part of program and it’s optional (you can ignore that part of the program in case the program doesn’t need to make a declare).

That part contains all the variables of the program and the fixed values, also it has the pointers.

2. BEGIN

It’s the most important part of the program because it has the acting part of queries and it’s (attempt) and never be ignored because it’s the operation part, that’s mean with out that part in program then there’s no program.

3. EXCEPTION

It’s also an optional part, which contain the orders that will be taken when any mistake happened in the excution part in Begin part.

Definition of Mass?

Mass, it’s a group of orders which is arranged in the program. And program consists of groups of masses.

Types of Mass:

  • ANONYMOUS.
  • PROCEDURE.
  • FUNCTION.

1. ANONYMOUS

It’s unnamed type and is defined in the same place of operating in the program and can’t be saved.

2. PROCEDURE

It’s a type of mass that can be used and stored in oracle server and can be called and used more than one time with the same name.

3. FUNCTION

It’s small type of mass and can be used partially in the procedure.

Listing 1: shows how masses used.

Sql>Begin
Insert into emp(empno,deptno) values(13,20);
Delete from emp where empno=7788;
End;
/

This example explain how a program starts with begin and then transferred to inserting values in columns empno and deptno, then delete from emp table a value 7788, then program ended with the word (end;) and that’s how any example is written in PL/SQL Language.

VARIABLES AND USAGE OF VARIABLES

It’s a storage area and can be called in any time of the executing part of the program.

  • Can be used more than one time.
  • Variable is defined in declare part.

Variable is used to take data from users and make a lot of operations on it in the executing part.

Variable can be defined on that way:

V_number number;

As the variable name is v_number and the type of variable is number then the usual sign (;).

Listing 2 : shows how variable can be used through a program

Sql>declare
V_deptno number;
V_dname varchar(10);
Begin
V_deptno :=30;
V_dname :=’IT’;
Update dept SET dname= V_dname where deptno=V_deptno;
END;
/

And this is the result of the program on PL/SQL simulator

shows the simulation of the program to define variables

Figure 1 : shows the simulation of the program to define variables

Types of variables:

  • Scaler
  • Date
  • Boolean
  • Number
  • Composite

1. Scaler

That’s like varchar identification which can be contained a lot of data and dates and numbers also

Listing 3 : shows the scaler variables and its usage in program

Sql>declare
Var_n varchar(40);
Begin
Var_n :=01021021448;
END;

This program explain how variable is used in the program as we saw before, we made a variable called var_n which contain a 40 character, then we put a value which is the number.

2. Date

This variable is used specially in dates and only for dates

Listing 4 : shows the example of using dates

Sql>declare
Var_d date;
Begin
Var_d = sysdate;
End;
/

In this example we shows a variable named var_d and is a date function and we put the system date on the execution part with the phrase sysdate.

3. Boolean

This variable is called in oracle programming yes or no variable because it accepts three values:

  • True
  • False
  • Null

Only these three values can be stored in that variable

Kindly check the coming example to clear the view to you

Listing 5 : shows the boolean variable

Sql>declare
X boolean;
Y number:=6;
Z number:=6;
Begin
X:=(y=z);
If x then
Dbms_output.put_line(‘true’);
Else
Dbms_output.put_line(‘false’);
End if;
End;
/

This example shows a many variable types like x, y and z , here x is a boolean and it compare between y and z if they are equaled then it will type true else it will type false.

There is another type of composite which takes the part of records

4. Composite

It’s concerned with record type and it’s a variable contains a lot of variables.

How to define a composite:

  • Type comp_type is record (variables is written here……);
  • Variable_name comp_type;
  • And to call a composite variable we type that:
  • V_comp_type.x;

Listing 6: shows the usage of composite variables

Sql>declare
Type comp_type is record
(x number,y varchar(40),z date);
V_com_type comp_type;
Begin
Select empno,ename,hiredate into v_comp_type from emp where sal=5000;
DBMS_OUTPUT.PUT_LINE(v_comp_type.x||’  ‘||v_comp_type.y||’  ‘||v_comp_type.z);
End;
/

This example explained the usage of a composite variable like x,y and z and it used them in the select query then dbms function.

Finally there’s a type of variables called end variable and this is identified as a general variable outside the mas or the whole program and can be called in a lot of masses and can be written as follow:

Var N varchar(40);
And can be used as follow in the program:
:N:= 01021021448;

Listing 7 : shows the end variable usage

Sql>var N varchar(40);
Sql>begin
:N:= 01021021448;
End;
/
Sql>print N;
  • Print N here is used to type the value of N.
  • Usage of DBMS_OUTPUT function:
  • Usage of the function is very useful for a lot of purposes like printing as we used it before.

This function is activated with that sql order:

Sql>Set serveroutput on;

Listing 8 : shows the usage of DBMS_OUTPUT

Sql>set serveroutput on;
Sql>declare
V_empno number(4);
Begin
Select empno into v_empno from emp where ename=’smith’;
DBMS_OUTPUT.PUT_LINE(V_EMPNO);
END;
/

This example types the content of variable v_empno through the dbms function which is put_line.

Usage of if statement:

If statement is used in oracle as a circlum statement which is used widely in the oracle programs.

Listing 9 : shows the usage of if statement.

Sql>declare
X number:=1700;
Y varchar(40);
Begin
If x between 1000 and 1500
Then
Y:=’low sal’;
Elsif x between 1500 and 2500
Then
Y:=’med sal’;
Else
Y:=’not available’;
End if;
DBMS_OUTPUT.PUT_LINE(y);
END;
/

When we say if statement that will push us to the loops of any programming languages

In the coming part we will study some types of loops

Loops

Types of loops:

  • Basic loop
  • For loop
  • While loop

This is the three types of loops in PL/SQL that used originally in Oracle

1. Basic loop:

It’s used to repeat the order more than one time and the following example is used to show how loops is used in the programs

Lisitng 10 : shows how the basic loop is used

Sql>declare
X number :=0;
Begin
Loop
X:=x+1;
Exit when x=10;
DBMS_OUTPUT.PUT_LINE(X);
End loop;
End;
/

As we see in the previous example the loop begins with x:=x+1 which means that the loop will continue counting from 0 and with a one step forward to the second then it ends when x=10, as we notice that loop function must be closed with the order end loop.

Then we end the program with the order end; , and that is the main loop or the basic loop.

2. FOR Loop:

It’s a type of loop which is used to make a small loop with a different way from the basic loop.

For loop is written as follow :

For n(the variable) in (begin number).. (end number ) loop

Listing 11: shows the usage of for loop and how it can be written.

Sql>declare
X number;
Begin
For N in 1.. 5 Loop
Insert into dept values(N,’IT’,50);
End loop;
End;
/

As we see the program used for loop which consists of for word then the variable n then the first number then two dots then the last number and loop word finally and then it starts to insert the values in dept table with n of times till the loop stop.

3. While loop

It’s a different way of loops which is also useful for PL/SQL, and can be written as follow:

While + condition + loop

Listing 12: shows how the while loop is used.

Sql>declare
X number:=10;
Begin
While x<20 loop
DBMS_output.PUT_LINE(x);
End loop;
End;
/

The example here explains the while loop which the condition made as if x

When the conditions achieved correctly, then close the loop and close the program.

As we know there’s a lot of operations in all programming languages we can’t even say it in one article but we just need the reader when he reads that article, he can understand what PL/SQL means in oracle development.

And in the same way of oracle development we can’t end our article without informing about the last part of PL/SQL masses which is solving mistakes or exceptions.

Mistake solving (exceptions):

Exceptions here means the orders taken in case the program has errors.

The following example will clear to us how we can make an exception for a running program to take errors in consideration

Listing 13: shows the exception taken from the programmer.

Sql>declare
X exception;
Begin
If sysdate>’12-jun-2000’ then
Raise x;
End if;
Exception when x then
DBMS_OUTPUT.PUT_LINE(‘ USER DEFINED EXCEPTION’);
END;
/

This is the exception taken if the system is not like we compared then the exception will appear

Finally and not the end, oracle PL/SQL is the most important part of programming language which is used to operate any program, also you can make a lot of researchers because PL/SQL is very big knowledge which can’t be collected one time.



I am well versed with Computer Programming languages and possess good working knowledge on software languages such as C, Java, PHP, HTML and CSS

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login