statement

EXIT Statement in Oracle PLSQL

November 23, 2012 by techhoneyadmin

EXIT statement in Oracle PLSQL is most commonly used to end the LOOP statement’s execution.

The Syntax for the EXIT Statement in Oracle PLSQL is:

EXIT [WHEN boolean_condition];

Example of an EXIT Statement in Oracle PLSQL is:

1LOOP
2{
3  sum := sum + 1;
4  EXIT WHEN sum = 35;
5}
6END LOOP;

The above loop will execute the “sum := sum+1; statement 35 times. The loop will start counting from 1 and as soon as the value of the “sum” variable crosses 35 the loop will terminate.

GOTO Statement in Oracle PLSQL

November 23, 2012 by techhoneyadmin

A GOTO Statement in Oracle PLSQL is used to redirect the code execution to a “Label” as specified in the GOTO statement.

Syntax of the GOTO Statement in Oracle PLSQL is:

GOTO label_name;

Somewhere further in the code, we need to place the label “label_name” and provide the code to be executed.

Example of GOTO in Oracle PLSQL is:

1GOTO techhoney;
2techhoney:
3{
4  statements;
5}

CASE Statement in Oracle SQL PLSQL

November 23, 2012 by techhoneyadmin

A CASE Statement in Oracle SQL / PLSQL is having the functionality of IF-THEN-ELSE Statement.

Syntax of the CASE Statement in Oracle SQL / PLSQL is:
CASE [expression]
WHEN condition_1 THEN result_1
WHEN consition_2 THEN result_2
WHEN condition_3 THEN result_3
.
.
WHEN condition_N THEN result_N
ELSE default_result
END;

expression is an optional value, if provided; it is used to compare with various conditions (e.g. condition_1, condition_2, . . condition_N)
condtion_1 to condition_N must have the same data type, also the conditions are evaluated in the order in which they are listed, hence once a condition evaluates to true the CASE statement returns the result and does not evaluate further conditions.
result_1 to result_N must also have the same data type, these are the values that will be returned once the condition evaluates to true.
If no condition evaluates to true then the ‘default_result’ from the ELSE clause will be returned by CASE statement
If the ELSE clause is omitted and none of the condition evaluates to true then NULL will be returned by CASE Statement.

Let’s see an example to understand how to use CASE in Oracle SQL / PLSQL SELECT Statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

Now, if we write our query using CASE Statement in Oracle SQL / PLSQL as:

1SELECT employee_id
2       ,employee_name
3       ,salary
4       ,CASE
5         WHEN salary = 30000 THEN 'Salary Between 20000 and 30000'
6         ELSE 'Salary More Than 30000'
7         END SALARY_STATUS
8FROM employee;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	SALARY	SALARY_STATUS
101	Emp A	10000	Salary Less than 20000
102	Emp B	20000	Salary Less than 20000
103	Emp C	28000	Salary More Than 30000
104	Emp D	30000	Salary Between 20000 and 30000
105	Emp E	32000	Salary Between 20000 and 30000
106	Emp F	20000	Salary Less than 20000
107	Emp G	12000	Salary Less than 20000
108	Emp H	12000	Salary Less than 20000

Here we can observe, that the CASE statement returns the text literals ‘Salary less than 20000’, ‘Salary between 20000 and 30000’ and ‘Salary More Than 30000’ for each record based on the salary amount as specified in the CASE Statement.

IF THEN ELSE Statement in Oracle PLSQL

November 23, 2012 by techhoneyadmin

An IF-THEN-ELSE Statement in Oracle PLSQL is basically a conditional statement that evaluates an expression, if the expression evaluates to true, then the ‘THEN’ portion of the code is executed, if the expression evaluates to false, then ‘ELSE’ part of the code gets executed.

There are 3 syntaxes of IF-THEN-ELSE Statement in Oracle PLSQL:

Syntax 1: IF-THEN Statement in Oracle PLSQL
IF condition THEN
Business_Logic
END IF;

Example: IF-THEN Statement in Oracle PLSQL

1IF department = 'IT' THEN
2  commission := 10;
3END IF;

Syntax 2: IF-THEN-ELSE Statement in Oracle PLSQL
IF condition THEN
Business_Logic_1
ELSE
Business_Logic_2
END IF;

Example: IF-THEN-ELSE Statement in Oracle PLSQL

1IF department = 'IT' THEN
2  commission := 10;
3ELSE
4  commission := 20;
5END IF;

Syntax 3: IF-THEN-ELSEIF Statement in Oracle PLSQL
IF condition_1 THEN
Business_Logic_1
ELSEIF condition_2 THEN
Business_Logic_2
ELSEIF condition_3 THEN
Business_Logic_3
.
.
ELSEIF condition_N THEN
Business_Logic_N
ELSE
Default_Business_Logic
END IF;

Example: IF-THEN-ELSEIF Statement in Oracle PLSQL

1IF department = 'IT' THEN
2  commission :=10;
3ELSEIF department = 'Sales' THEN
4  commission := 20;
5ELSEIF department = 'Support' THEN
6  commission := 30;
7ELSE
8  commission := 5;
9END IF;

Let’s see an example to understand how to use IF-THEN-ELSE Statement:

1CREATE OR REPLACE FUNCTION SalaryLevel
2   ( emp_id_in IN NUMBER)
3   RETURN VARCHAR2
4IS
5   salary_value NUMBER(6);
6   SalLevel VARCHAR2(20);
7   CURSOR cur_sal IS
8     SELECT salary
9     FROM employee
10     WHERE employee_id = emp_id_in;
11BEGIN
12   OPEN cur_sal;
13   FETCH cur_sal INTO salary_value;
14   CLOSE cur_sal;
15 
16   IF salary_value <= 15000 THEN
17      SalLevel := 'Low Income';
18   ELSIF salary_value > 15000 AND salary_value <= 30000 THEN
19      SalLevel := 'Average Income';
20   ELSIF salary_value > 30000 AND salary_value <= 45000 THEN
21      SalLevel := 'Moderate Income';
22   ELSE
23      SalLevel := 'High Income';
24   END IF;
25 
26   RETURN SalLevel;
27END;

Change User Password in SQL – PLSQL

October 31, 2012 by techhoneyadmin

In Oracle SQL / PLSQL a user’s password can be changed using ALTER command.

Syntax for changing the user password using ALTER command in Oracle SQL / PLSQL is:

ALTER USER user_name
IDENTIFIED BY new_password;

Example:

`1`	`ALTER` `USER` `techhoney`

`2`	`IDENTIFIED` `BY` `pwdhoney;`

Here we have changed the password for “techhoney” user as “pwdhoney”.