TECH GUIDE BLOG

In PL/SQL* Oracle, we can use conditional statement like other programming language, such as CASE and IF statement. How to do it in PL/SQL? Here’s the syntax for IF statement.

IF condition THEN
  statements;
[ELSIF condition THEN
  statements;]
[ELSE
  statements;]
END IF;

Just remember, I write ELSIF,  it’s on purpose and it’s not mistyping, in this case we write “else if” in PL/SQL as ELSIF. There is an example for IF statement usage in PL/SQL. This statement will ask your age and show output based on your age input.

SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT age PROMPT 'Insert your age'
DECLARE
  age NUMBER := &age;
BEGIN
  IF age < 17 THEN
    DBMS_OUTPUT.PUT_LINE('You are teen');
  ELSIF age < 56 THEN
    DBMS_OUTPUT.PUT_LINE('You are adult');
  ELSE
    DBMS_OUTPUT.PUT_LINE('You are old');
END;
/

Just a little different than IF, CASE expression selects a result and returns it. The values returned by CASE is used to select one of several alternatives. The CASE syntax is shown below.

CASE selector
  WHEN axpression1 THEN result1
  WHEN expression2 THEN result2
  ...
  WHEN expressionN THEN resultN
  [ELSE resultN+1]
END;
/

For the example of CASE expression, we can use IF statement example above, just change it to CASE expression. We add text variable to store the return values from CASE expression since CASE return values.

SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT age PROMPT 'Insert your age'
DECLARE
  age NUMBER := &age;
  text VARCHAR2(20);
BEGIN
  text:=
    CASE
      WHEN age < 17 THEN 'You are teen'
      WHEN age < 56 THEN 'You are adult'
      ELSE 'You are old'
    END;
  DBMS_OUTPUT.PUT_LINE(text);
END;
/

Example above didn’t use selector after CASE expression, you can use selector as alternatives way. I don’t really use CASE with selector anyway, but I will give you an example with selector below, I hope this can help.

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
  grade CHAR(1) := UPPER('&grade');
  text VARCHAR2(20);
BEGIN
  text :=
    CASE grade
      WHEN 'A' THEN 'Excellent'
      WHEN 'B' THEN 'Very Good'
      WHEN 'C' THEN 'Good'
      WHEN 'D' THEN 'Bad'
      WHEN 'E' THEN 'Poor'
      ELSE 'Not valid grade'
    END;
  DBMS_OUTPUT.PUT_LINE(text);
END;
/

I think it’s enough for Conditional in PL/SQL Oracle tutorial, maybe this can help you little, feel free to give comment to improve my blog.

Popularity: 7% [?]

What exactly recursion is in programming? Recursion is a function that calls the function itself. Thus, in the body of function there is a command that called the function itself. If you use it well, it can solve a problem much more simple and easy. Recursion itself uses a large amount of memory, so be aware not to use it if not necessary. To make a good recursion, of course the functions must have a condition that makes it stop, so the function will not do an infinite looping. I’ve tried it without a stop condition, I compiled it and wow, NOD32 detected my .exe as a virus, I don’t know if the NOD32 made a false detection or not, but if it’s true, then it’s not too difficult to make a virus, eh? Just a little mistake in a program that you’ve made for good reasons and you’ve made a virus.

This code below is an example of how recursion function is working.

#include <stdio.h>
#include <conio.h>

void letter(char alp)
{
	if (alp <= 'e')
	{
		printf("%c ", alp);
		letter(alp + 1);
	}
}

void main()
{
	char temp = 'a';
	letter(temp);
	getch();
}

We call the function and it will print letters from ‘a’ to ‘e’. From the start of the program at void main, we fill a variable with char type called temp with ‘a’. We then call the letter function with temp as parameter, into the letter function, the variable named alp will be filled with ‘a’. Here’s the stop condition, if alp is lower or equal than ‘e’, the function will continue its routine, the function will print what is in the alp variable. After the function printed what is in the alp variable, it call itself with alp + 1 as the parameter, thus this looks like the function is going down 1 level. This routine ended when alp variable is filled with ‘f’, it will go up to the upper level and so on until back to the top level.

It’s a little confusing for me, so I made a ‘level’ for this explanation. Every time a function (you can say function1) called another function (function2), I decided to make a function2 as level 2 function, below the level 1 function (function1). So when letter function with temp as parameter called a letter function with alp + 1 parameter, I set in my mind the letter function with temp parameter as level 1 function and letter function with alp + 1 parameter as level 2, letter function with (alp + 1) + 1 parameter as level 3 function respectively. I hope this tutorial can help.

Popularity: 7% [?]

Let’s get to the point, what’s the different beetwen Circular and Linear? Just like previous post, the different is the head is always move around or you can say dynamic. The first and the last data is likely become linked to another one. If we do enqueue, the tail will be point to the new data. If you can see the picture below, you will know that the data are never move like linear queue, but the head node. This head node always move when you do dequeue and enqueue new data.

Circular Queue

While we using circular queue, we can use mod (%) to help us for the head movement. Here is the example of Circular Queue. This code is the class of Queue, it’s just made for keep integer data up to small size, it’s just for simple example program of Circular Queue.

#include <iostream.h>
#include <conio.h>
#include <windows.h>

#define MAX 5

class Cqueue
{
private:
	int element[MAX];
	int head;
	int tail;
public:
	Cqueue()
	{
		head=0;
		tail=0;
	}
	void enqueue(int x)
	{
		if(!full())
		{
			element[(head+tail)%MAX]=x;
			tail++;
		}
	}
	int dequeue()
	{
		if(!empty())
		{
			int temp;
			temp=element[head];
			head++;
			head=head%MAX;
			tail--;
			return temp;
		}
	}
	int full()
	{
		if(tail==MAX)
			return 1;
		else
			return 0;
	}
	int empty()
	{
		if(tail==0)
			return 1;
		else
			return 0;
	}
	void view()
	{
		for(int i=0;i<tail;i++)
			cout<<element[(i+head)%MAX]<<endl;
		getch();
	}
};

Popularity: 43% [?]