Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

Oracle/SQL ASCII Function

by

Oracle ASCII FunctionOracle ASCII Function is used to get the NUMBER code that represents the character in ASCII. In other words, SQL ASCII Function returns the ASCII number of the character passed to it.

Oracle ASCII Function Syntax

SELECT ASCII (character)
FROM table_name;

In the above Oracle/SQL ASCII Function Syntax:-

  • character is a single character that is passed to the ASCII function.
  • If more than 1 character is passed to the ASCII function then ASCII function will return the ASCII value of the first character and ignore other characters.

Oracle ASCII Function – Using SQL SELECT Statement Example 1

Oracle ASCII Function is used with the SQL SELECT Statement to get the ASCII value of the character passed.

For example, the SQL ASCII Function query below returns the ASCII value of ‘a’.

SELECT ASCII ('a')
FROM dual;

Will return 97

Oracle ASCII Function – Using SQL SELECT Statement Example 2

The below SQL ASCII Function query returns the ASCII value of ‘abc’.

SELECT ASCII ('abc')
FROM dual;

Will also return 97 and ignore the ASCII values of b and c characters.

Oracle ASCII Function – Using SQL SELECT Statement Example 3

The below SQL ASCII Function query returns the ASCII value of ‘A’.

SELECT ASCII ('A')
FROM dual;

Will return 65

EXIT Statement in Oracle PLSQL

by

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:

LOOP
{
  sum := sum + 1;
  EXIT WHEN sum = 35;
}
END 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.

WHILE Loop in Oracle PLSQL

by

A WHILE Loop in Oracle PLSQL is used when we are not sure as to how many times the loop’s body should be executed.

Unlike LOOP statement, a WHILE LOOP may not get executed even once as the condition to execute a WHILE LOOP is evaluated before execution of the loop.

A WHILE LOOP gets terminated whenever the condition to execute the loop evaluates to false.

The Syntax for the WHILE LOOP in Oracle PLSQL is:
WHILE condition
LOOP
{
Statements to be executed;
}
END LOOP;

Example of a WHILE LOOP in Oracle PLSQL is:

WHILE sum <= 35
LOOP
{
  sum := sum + 1;
}
END LOOP;

The above loop will execute the “sum := sum+1” statement and as soon as the value of the “sum” variable crosses 35 the loop will terminate.
It may happen that the variable “sum” is already assigned a value higher than 35, in this case the WHILE LOOP will not get executed even once.

CURSOR FOR Loop in Oracle PLSQL

by

A CURSOR FOR Loop in Oracle PLSQL is used whenever we want to retrieve and process every record within a cursor.

The CURSOR FOR loop automatically gets terminated as soon as all the records in the cursor are fetched.

The Syntax for the CURSOR FOR LOOP in Oracle PLSQL is:

FOR cursor_records IN cursor_name
LOOP
{
Statements to be executed;
}
END LOOP;

Example of a CURSOR FOR Loop in Oracle PLSQL is:

CREATE OR REPLACE function TotalSalary
   ( emp_id_in IN NUMBER)
   RETURN VARCHAR2
IS
   total_sal NUMBER(6);

   CURSOR cur_salary IS
     SELECT salary
     FROM employee
     WHERE employee_id = emp_id_in;
BEGIN
   total_sal := 0;
   FOR employee_record IN cur_salary
   LOOP
      total_sal := total_sal + employee_record.salary;
   END LOOP;
   RETURN total_sal;
END;

In the above example the CURSOR FOR loop will terminate automatically when all the records have been fetched from the CURSOR ‘cur_salary’.

Loop Statement in Oracle PLSQL

by

A Loop in Oracle PLSQL is used to execute a portion of code i.e. the body of loop until a specific (exit) condition is not met.

In other words, we can say that we use the LOOP statement in Oracle PLSQL when we are not sure as to how many times the loop’s body should execute and we want the loop’s body to get executed at least once.
A LOOP gets terminated when it encounters an EXIT or EXIT WHEN statement which evaluates to true.

The Syntax for the LOOP Statement in Oracle PLSQL is:
LOOP
{
Statements to be executed;
}
END LOOP;

Example of a LOOP Statement in Oracle PLSQL is:

LOOP
{
  sum := sum + 1;
  EXIT WHEN sum = 35;
}
END 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 above 35 the loop will terminate.