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:
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.
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.
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:
It’s unnamed type and is defined in the same place of operating in the program and can’t be saved.
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.
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:
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
Figure 1 : shows the simulation of the program to define variables
Types of variables:
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.
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.
This variable is called in oracle programming yes or no variable because it accepts three values:
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
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:
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
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.