PL/SQL Procedures

A procedure is a group of PL/SQL statements that you can call by name which performs one or more specific task.

A procedure has a header and a body.

  • The header consists of the name of the procedure and the parameters or variables passed to the procedure.
  • The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block.

Syntax for creating procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
 < procedure_body > 
END procedure_name;

How to pass parameters in procedure:

We can pass parameters to procedures in three ways.

1) IN-parameters : The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.
2) OUT-parameters : The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3) IN OUT-parameters : The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

  • A procedure may or may not return any value.
  • IS – marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.
  • The AS keyword is used instead of the IS keyword for creating a standalone procedure.

How to execute a Stored Procedure

There are two ways to execute a procedure.

1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

procedure_name;

PL/SQL Create Procedure

CREATE OR REPLACE PROCEDURE 

remove_cust (cust_id NUMBER) IS
 
 BEGIN
 DELETE FROM CUSTOMERS
 WHERE CUSTOMERS.id = remove_cust.cust_id;
 
 DBMS_OUTPUT.PUT_LINE('Customer deleted');
 END;
/
Procedure REMOVE_CUST compiled
SET SERVEROUTPUT ON
BEGIN
remove_cust(2);

END;

Customer deleted

PL/SQL procedure successfully completed.

To view the Procedure availability

select object_name,object_type,status 
from USER_OBJECTS WHERE
object_name='REMOVE_CUST';

Recompile an Existing Procedure.

ALTER PROCEDURE remove_cust
 COMPILE;

Dropping Procedure.

Similar to dropping a table, procedure can also be dropped.

DROP PROCEDURE remove_cust;

IN & OUT Mode Example 1

This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the OUT parameters.

DECLARE 
 a number; 
 b number; 
 c number;
PROCEDURE findmin(x IN number, y IN number, z OUT number) IS 
BEGIN 
 IF x < y THEN 
 z:= x; 
 ELSE 
 z:= y; 
 END IF; 
END;

BEGIN 
 a:= 25; 
 b:= 40; 
 findmin(a, b, c); 
 dbms_output.put_line(' Minimum of (25, 40) : ' || c); 
END; 
/


Minimum of (25, 40) : 25

PL/SQL procedure successfully completed.

IN & OUT Mode Example 2

This procedure computes the square of value of a passed value.
This example shows how we can use the same parameter to accept a value and then return another result.

DECLARE 
 a number; 
PROCEDURE square(x IN OUT number) IS 
BEGIN 
 x := x * x; 
END; 
BEGIN 
 a:= 25; 
 square(a); 
 dbms_output.put_line(' Square of (25): ' || a); 
END; 
/


Square of (25): 625

PL/SQL procedure successfully completed.

Methods for Passing Parameters

Actual parameters can be passed in three ways

Positional notation

It is simple an association of the values by POSITION of the argumrnts at call time with that of declaration in the header of the procedure creation.
The order of the parameters used when executing the procedure should match the order in the procedure header exactly.

Ex: findmin(a, b, c);

Named notation

It is an explicit association using the symbol =>

In Named notation, the order of the parameters is doesnot matter.

Ex: findmin(x => a, y => b, z => c);

Mixed notation

In mixed notation, you can mix both notations in procedure call; however, the positional notation should precede the named notation.

Ex: findmin(a, b, z => c); — is valid

Ex: findmin(X => a, b, c); — is not valid

 

PL/SQL – Loops

A LOOP statement is an iterative control statement that allows you to executes a sequence of statements multiple times.

PL/SQL provides these loop statements.

  • Basic loop
  • FOR loop
  • WHILE loop
  • Cursor FOR loop

Loop Control Statements

To prevent an infinite loop, at least one statement must transfer control outside the loop.
The statements that can transfer control outside the loop are

  • CONTINUE
  • EXIT / EXIT WHEN
  • GOTO

CONTINUE

The CONTINUE statement exits the current iteration of a loop, either conditionally or unconditionally, and transfers control to the next iteration of either the current loop or an enclosing labeled loop.

Restrictions on CONTINUE Statement

  • A CONTINUE statement must be inside a LOOP statement.
  • A CONTINUE statement cannot cross a subprogram or method boundary.

EXIT

The EXIT statement exits the current iteration of a loop, either conditionally or unconditionally, and transfers control to the end of either the current loop or an enclosing labeled loop.

Restriction on EXIT Statement

  • An EXIT statement must be inside a LOOP statement.

GOTO

The GOTO statement transfers control to a labeled block or statement.

Restrictions on GOTO Statement

  • A GOTO statement cannot transfer control into an IF statement, CASE statement, LOOP statement, or sub-block.
  • A GOTO statement cannot transfer control from one IF statement clause to another, or from one CASE statement WHEN clause to another.
  • A GOTO statement cannot transfer control out of a subprogram.
  • A GOTO statement cannot transfer control into an exception handler.
  • A GOTO statement cannot transfer control from an exception handler back into the current block.

Basic loop

With each iteration of the basic LOOP statement, its statements run and control returns to the top of the loop. The LOOP statement ends when a statement inside the loop transfers control outside the loop or raises an exception.

Syntax :

LOOP
sequence_of_statements;
END LOOP;

Basic loop statement with no EXIT keyword will be an INFINITE-LOOP that will never stop. To prevent this, the EXIT condition should be given inside this execution block so that control exit from the loop.

Syntax :

LOOP
 sequence_of_statements;
 EXIT;
END LOOP;
LOOP
 sequence_of_statements;
 EXIT WHEN condition;
END LOOP;

Example :

SET SERVEROUTPUT ON;
DECLARE 
counter NUMBER := 0;
BEGIN
 LOOP
 counter := counter + 1;
 DBMS_OUTPUT.PUT_LINE('Counter : ' || counter);
 IF counter = 5 THEN
 EXIT;
 END IF;
 END LOOP;
END;
/

Counter : 1
Counter : 2
Counter : 3
Counter : 4
Counter : 5


PL/SQL procedure successfully completed.

SET SERVEROUTPUT ON;
DECLARE 
counter NUMBER := 0;
BEGIN
 LOOP
 counter := counter + 1;
 DBMS_OUTPUT.PUT_LINE('Counter : ' || counter);
 EXIT WHEN counter=3;
 END LOOP;
END;
/
Counter : 1
Counter : 2
Counter : 3


PL/SQL procedure successfully completed.

FOR loop

PL/SQL FOR LOOP is an iterative statement that allows you to execute a sequence of statements a fixed number of times. The FOR LOOP statement ends when its index reaches a specified value, or when a statement inside the loop transfers control outside the loop or raises an exception.

Syntax :

FOR loop_counter IN [REVERSE] lower_bound .. higher_bound
LOOP
 sequence_of_statements;
END LOOP;

Example :

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/

Iteration :1
Iteration :2
Iteration :3
Iteration :4
Iteration :5

PL/SQL procedure successfully completed.

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 IF i=3 THEN
 CONTINUE;
 END IF;
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/
Iteration :1
Iteration :2
Iteration :4
Iteration :5

PL/SQL procedure successfully completed.

In above example, CONTINUE statement skipped the iteration when the condition is true and transfer the control to the next iteration of the current loop.

CONTINUE WHEN statement :

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 CONTINUE WHEN i=3;
 
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/

Reverse FOR LOOP Statements

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN REVERSE 1 .. n_counter LOOP
 CONTINUE WHEN i=3;
 
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/
Iteration :5
Iteration :4
Iteration :2
Iteration :1

PL/SQL procedure successfully completed.

FOR LOOP with GOTO Statement.

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 IF i=3 THEN
 GOTO counter_reached;
 END IF;
 END LOOP;
 
 <<counter_reached>>
 
 DBMS_OUTPUT.PUT_LINE('Counter reached to 3'); 
END;
/

In above example, when the condition is true, GOTO statement transfer the control out side the loop to the label “counter_reached”.

GOTO Statement Goes to Labeled NULL Statement

DECLARE
 done BOOLEAN;
BEGIN
 FOR i IN 1..10 LOOP
 IF done THEN
 GOTO end_loop;
 END IF;
 <<end_loop>>
 NULL;
 END LOOP;
END;
/

GOTO Statement Cannot Transfer Control into IF Statement

DECLARE
 valid BOOLEAN := TRUE;
BEGIN
 GOTO update_row;
 
 IF valid THEN
 <<update_row>>
 NULL;
 END IF;
END;
/
Error report -
ORA-06550: line 4, column 3:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'UPDATE_ROW'
ORA-06550: line 6, column 12:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

NULL Statement

The NULL statement only passes control to the next statement. Some languages refer to such an instruction as a no-op (no operation).

WHILE loop

The WHILE LOOP statement runs one or more statements while a condition is TRUE. The WHILE LOOP statement ends when the condition becomes FALSE or NULL, or when a statement inside the loop transfers control outside the loop or raises an exception.

Syntax :

WHILE condition
LOOP
 sequence_of_statements;
END LOOP;

Example :

DECLARE
 n_counter NUMBER := 5;
 n_factorial NUMBER := 1;
 n_number NUMBER;
BEGIN
 n_number := n_counter;
 WHILE n_counter > 0
 LOOP
 n_factorial := n_factorial * n_counter;
 n_counter := n_counter - 1;
 END LOOP;
 
 DBMS_OUTPUT.PUT_LINE('factorial of ' || n_number ||
 ' is ' || n_factorial);
 
END;
/

factorial of 5 is 120


PL/SQL procedure successfully completed.

We can include EXIT, EXIT WHEN, CONTINUE and GOTO statements in WHILE LOOP as well as per requirement.

Cursor FOR LOOP

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor.

With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor.

Examples :

Implicit Cursor FOR LOOP Statement

BEGIN
 FOR item IN (
 SELECT last_name, job_id
 FROM employees
 WHERE job_id LIKE '%CLERK%'
 AND manager_id > 120
 ORDER BY last_name
 )
 LOOP
 DBMS_OUTPUT.PUT_LINE
 ('Name = ' || item.last_name || ', Job = ' || item.job_id);
 END LOOP;
END;
/

Explicit Cursor FOR LOOP Statement

DECLARE
 CURSOR c IS
 SELECT last_name, job_id FROM employees
 WHERE job_id LIKE '%CLERK%' AND manager_id > 120
 ORDER BY last_name;
BEGIN
 FOR item IN c
 LOOP
 DBMS_OUTPUT.PUT_LINE
 ('Name = ' || item.last_name || ', Job = ' || item.job_id);
 END LOOP;
END;
/

Passing Parameters to Explicit Cursor FOR LOOP Statement

DECLARE
 CURSOR c (job VARCHAR2, max_wage NUMBER) IS
 SELECT * FROM employees
 WHERE job_id = job
 AND salary > max_wage;
BEGIN
 FOR person IN c('ST_CLERK', 3000)
 LOOP
 
 DBMS_OUTPUT.PUT_LINE (
 'Name = ' || person.last_name || ', salary = ' ||
 person.salary || ', Job Id = ' || person.job_id
 );
 END LOOP;
END;
/

We will see more about cursors in detail in next chapters.

Nested Loops

The loop statements can also be nested. The outer and inner loop can be of different types. In nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully.

Syntax :

LOOP -- outer
 statements;
 LOOP -- inner
 statements;
 END LOOP;
 statements;
END LOOP;

Example :

Labelling of Loops

In PL/SQL, the loops can be labeled. The label should be enclosed between “<<” and “>>”. The labeling of loops particularly in nested loop codes will give more readability.

The label can be given to GOTO statement to exit from that particular loop. Using label, the control can be made to directly exit the outer loop of the nested loops from anyplace inside the loops.

Syntax :

<<outer_loop>>
LOOP
 statements;
 <<inner_loop>>
 LOOP
 statements;
 END LOOP;
 statements;
END LOOP';

 

PL/SQL Conditional Statements

PL/SQL supports the conditional statements statements like other programming languages such as C++, java etc.

The conditional selection statements, IF and CASE, run different statements for different data values.

The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement has these forms:

  • IF THEN
  • IF THEN ELSE
  • IF THEN ELSIF

The CASE statement chooses from a sequence of conditions, and runs the corresponding statement. The CASE statement has these forms:

  • Simple, which evaluates a single expression and compares it to several potential values.
  • Searched, which evaluates multiple conditions and chooses the first one that is true.

IF THEN Statement

IF THEN statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is true, the statements get executed and if the condition is false or NULL then IF statement does nothing.

Syntax :

IF condition THEN
  statements
 END IF;

Example :

DECLARE
a number(3) :=100;
b number(3) :=150;
BEGIN
if a < b then
dbms_output.put_line ('b is the highest number');
END IF;
END;

b is the highest number

PL/SQL procedure successfully completed.

IF THEN ELSE

IF statment adds the keyword ELSE followed by an alternative sequence of statements. If the condition is false or NULL, then alternative sequenceof statements get executed. It ensure that either of sequence of statements are executed.

Sytax :

IF condition THEN
  statements
 ELSE
  else_statements
 END IF;
DECLARE
a number(3) :=100;
b number(3) :=150;
BEGIN
if a > b then
dbms_output.put_line ('a is the highest number');
ELSE
dbms_output.put_line ('b is the highest number');
END IF;
END;

b is the highest number

PL/SQL procedure successfully completed.

IF THEN ELSIF

The IF THEN ELSIF statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the else_statements run, if they exist; otherwise, the IF THEN ELSIF statement does nothing.

Syntax :

IF condition_1 THEN
  statements_1
 ELSIF condition_2 THEN
  statements_2
 [ ELSIF condition_3 THEN
  statements_3
 ]...
 [ ELSE
  else_statements
 ]
 END IF;

Example :

DECLARE
 grade CHAR(1);
BEGIN
 grade := 'C';
 
 IF grade = 'A' THEN
 DBMS_OUTPUT.PUT_LINE('Excellent');
 ELSIF grade = 'B' THEN
 DBMS_OUTPUT.PUT_LINE('Very Good');
 ELSIF grade = 'C' THEN
 DBMS_OUTPUT.PUT_LINE('Good');
 ELSIF grade = 'D' THEN
 DBMS_OUTPUT. PUT_LINE('Fair');
 ELSIF grade = 'F' THEN
 DBMS_OUTPUT.PUT_LINE('Poor');
 ELSE
 DBMS_OUTPUT.PUT_LINE('No such grade');
 END IF;
END;
/


Good

PL/SQL procedure successfully completed.

Simple CASE Statement

The simple CASE statement runs the first statements for which selector_value equals selector. Remaining conditions are not evaluated. If no selector_value equals selector, the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise.

Syntax :

CASE selector
 WHEN selector_value_1 THEN statements_1
 WHEN selector_value_2 THEN statements_2
 ...
 WHEN selector_value_n THEN statements_n
 [ ELSE
  else_statements ]
 END CASE;]
DECLARE
 grade CHAR(1);
BEGIN
 grade := 'B';

CASE grade
 WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
 WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
 WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
 WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
 WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
 ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
 END CASE;
END;
/

Very Good

PL/SQL procedure successfully completed.

Searched CASE Statement

The searched CASE statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise.

Syntax :

CASE
 WHEN condition_1 THEN statements_1
 WHEN condition_2 THEN statements_2
 ...
 WHEN condition_n THEN statements_n
 [ ELSE
  else_statements ]
 END CASE;]
DECLARE
 grade CHAR(1);
BEGIN
 grade := 'C';
 
 CASE
 WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
 WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
 WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
 WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
 WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
 ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
 END CASE;
END;
/

Good

PL/SQL procedure successfully completed.

PL/SQL Arrays

Like other programming languages, PL/SQL provides a data structure called VARRAY, which can store collection of elements of the same type.

Varray stands for variable-size array. When you create varrays you must provide the maximum size for them.

All varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically.

Creating a VARRAY type.

Syntax :

TYPE varray_type_name IS VARRAY(n) of 

Example :

TYPE names IS VARRAY(5) OF VARCHAR2(20); 
Type percentage IS VARRAY(5) OF INTEGER;
SET SERVEROUTPUT ON;
DECLARE
TYPE v_array is varray(5) OF VARCHAR2(20); 
messages v_array := v_array();

BEGIN

messages.extend;
messages(1) := 'Hello';
messages.extend;
messages(2) := 'PL/SQL';
messages.extend;
messages(3) := 'Learner';
dbms_output.put_line( messages(1) || ' ' || messages(2) || ' ' || messages(3));
END;
/

Hello PL/SQL Learner

PL/SQL procedure successfully completed.

In PL/SQL, the starting index for varrays is always 1. Varrays are one-dimensional arrays.

A varray is automatically NULL when it is declared. We must be initialized before its elements can be referenced.

Once the array is initialized you can extend it and add elements.

We access each element using the VARRAY variable and the index number. When the PL/SQL block ends, the memory used by the array is recovered automatically by the PL/SQL engine.

Another example with loop statement.

SET SERVEROUTPUT ON;
DECLARE 
 TYPE names IS VARRAY(5) OF VARCHAR2(20); 
 Type percentage IS VARRAY(5) OF INTEGER;
 students names; 
 marks percentage; 
 total integer; 
BEGIN 
 students := names('AAA', 'BBB', 'CCC', 'DDD', 'EEE'); 
 marks:= percentage(99, 79, 78, 75, 91); 
 total := students.count; 
 dbms_output.put_line('Total '|| total || ' Students'); 

FOR i in 1 .. total

LOOP 
 dbms_output.put_line('Student: ' || students(i) || ' Marks: ' || marks(i)); 
 END LOOP;
END; 
/
Total 5 Students
Student: AAA Marks: 99
Student: BBB Marks: 79
Student: CCC Marks: 78
Student: DDD Marks: 75
Student: EEE Marks: 91




PL/SQL procedure successfully completed.

Here, we added all elements like above and printed one by one with looping statement.

Elements of a varray could also be a %ROWTYPE of any database table or %TYPE of any database table field.

SET SERVEROUTPUT ON;
DECLARE
CURSOR c_customer is
select name from customers;
TYPE v_names IS VARRAY(10) OF CUSTOMERS.NAME%TYPE;
names v_names := v_names();
counter integer :=0;

BEGIN

FOR n in c_customer

LOOP
counter := counter+1;
names.extend;
names(counter) := n.name;
dbms_output.put_line('Name('||counter ||'):'||names(counter)); 
END LOOP;

END;
/
Name(1):Sriniva
Name(2):Ahkil
Name(3):Gowtham
Name(4):Shiva
Name(5):Jagath
Name(6):Raju
Name(7):Bujji
Name(8):Ammulu

PL/SQL procedure successfully completed.

Take look into below examples as well.

SET SERVEROUTPUT ON;
DECLARE
TYPE my_varray IS VARRAY(10) OF NUMBER;
list_varray my_varray := my_varray(1,2,3,4,5,6);

BEGIN

dbms_output.put_line('list_varray.count is ' ||list_varray.count ); 
dbms_output.put_line('list_varray.limit is ' ||list_varray.limit ); 
dbms_output.put_line('list_varray.first is ' ||list_varray.first ); 
dbms_output.put_line('list_varray.last is ' ||list_varray.last ); 
list_varray.extend(2,4);
dbms_output.put_line('list_varray.last is ' ||list_varray.last ); 
dbms_output.put_line('list_varray(' || list_varray.last || ')= ' ||list_varray(list_varray.last) );
list_varray.trim(2);
dbms_output.put_line('list_varray.last is ' ||list_varray.last );

END;
/
list_varray.count is 6
list_varray.limit is 10
list_varray.first is 1
list_varray.last is 6
list_varray.last is 8
list_varray(8)= 4
list_varray.last is 6




PL/SQL procedure successfully completed.

Here, list_varray.extend(2,4) appends two copies on the fourth element to the collection like below.

list_varray(1):1
list_varray(2):2
list_varray(3):3
list_varray(4):4
list_varray(5):5
list_varray(6):6
list_varray(7):4
list_varray(8):4

list_varray.trim(2) will remove last two elements.

list_varray(1):1
list_varray(2):2
list_varray(3):3
list_varray(4):4
list_varray(5):5
list_varray(6):6

PL/SQL – Strings

What is a String?

A string, also referred to as character data, is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all.

There are three kinds of strings in PL/SQL:

Fixed-length strings – The string is right-padded with spaces to the length specified in the declaration. In such strings, programmers specify the length while declaring the string

Variable-length strings – A maximum length for the string is specified (and it must be no greater than 32,767), but no padding takes place.

Character large objects (CLOBs) – CLOBs are variable-length strings that can be up to 128 terabytes.

Strings can be literals or variables. A string literal is enclosed within quotation marks.

'This is a string literal'

If you need to embed a single quote inside a string literal, you need to type two single quotes right next to one another.

'This isn''t a date'

You can also use the “q” character to indicate an alternative terminating character for the literal.

q'[This isn't a date]'

A string variable is an identifier declared with a string datatype and then assigned a value (which could be a literal or an expression).

Declaring String Variables

Oracle Database offers numerous string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes that are prefixed with an “N” are “national character set” datatypes, which means they are used to store Unicode character data.

If you need to declare a variable-length string, you must provide the maximum length of that string. The following code declares a variable, using the VARCHAR2 datatype, that will hold a application name, which cannot have more than 50 characters:

DECLARE
application_name VARCHAR2(50);

You must provide the maximum length; if you leave it out, Oracle Database raises a compile error, as shown below.

DECLARE
application_name VARCHAR2;
BEGIN
application_name := 'Learn PL/SQL';
END;




Error report -
ORA-06550: line 2, column 20:
PLS-00215: String length constraints must be in range (1 .. 32767)
06550. 00000 - "line %s, column %s:n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

To declare a fixed-length string, use the CHAR datatype.

DECLARE
yes_or_no CHAR(1) := 'Y';

With CHAR (unlike with VARCHAR2) you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length of 1. In other words, the two declarations below are identical:

DECLARE
 yes_or_no1 CHAR(1) := 'Y';
 yes_or_no2 CHAR := 'Y';

If you declare a CHAR variable with a length greater than 1, Oracle Database automatically pads whatever value you assign to that variable with spaces to the maximum length specified.

To declare a character large object, use the CLOB datatype and you don’t need to specify maximum length. The length is determined automatically by Oracle Database based on the database block size.

DECLARE
large_content CLOB;

Here are some guidelines.

  • If your string might contain more than 32,767 characters, use the CLOB (or NCLOB) datatype.
  • If the value assigned to a string always has a fixed length, use CHAR (or NCHAR).
  • Otherwise (most of the time), use the VARCHAR2 datatype (or NVACHAR2, when working with Unicode data).

Using Built-in Functions with Strings.

S.No Function & Purpose
1 ASCII(x);

Returns the ASCII value of the character x.

2 CHR(x);

Returns the character with the ASCII value of x.

3 CONCAT(x, y);

Concatenates the strings x and y and returns the appended string.

4 INITCAP(x);

Converts the initial letter of each word in x to uppercase and returns that string.

5 INSTR(x, find_string [, start] [, occurrence]);

Searches for find_string in x and returns the position at which it occurs.

6 INSTRB(x);

Returns the location of a string within another string, but returns the value in bytes.

7 LENGTH(x);

Returns the number of characters in x.

8 LENGTHB(x);

Returns the length of a character string in bytes for single byte character set.

9 LOWER(x);

Converts the letters in x to lowercase and returns that string.

10 LPAD(x, width [, pad_string]) ;

Pads x with spaces to the left, to bring the total length of the string up to width characters.

11 LTRIM(x [, trim_string]);

Trims characters from the left of x.

12 NANVL(x, value);

Returns value if x matches the NaN special value (not a number), otherwise x is returned.

13 NLS_INITCAP(x);

Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT.

14 NLS_LOWER(x) ;

Same as the LOWER function except that it can use a different sort method as specified by NLSSORT.

15 NLS_UPPER(x);

Same as the UPPER function except that it can use a different sort method as specified by NLSSORT.

16 NLSSORT(x);

Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used.

17 NVL(x, value);

Returns value if x is null; otherwise, x is returned.

18 NVL2(x, value1, value2);

Returns value1 if x is not null; if x is null, value2 is returned.

19 REPLACE(x, search_string, replace_string);

Searches x for search_string and replaces it with replace_string.

20 RPAD(x, width [, pad_string]);

Pads x to the right.

21 RTRIM(x [, trim_string]);

Trims x from the right.

22 SOUNDEX(x) ;

Returns a string containing the phonetic representation of x.

23 SUBSTR(x, start [, length]);

Returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied.

24 SUBSTRB(x);

Same as SUBSTR except that the parameters are expressed in bytes instead of characters for the single-byte character systems.

25 TRIM([trim_char FROM) x);

Trims characters from the left and right of x.

26 UPPER(x);

Converts the letters in x to uppercase and returns that string.

SET SERVEROUTPUT ON;
DECLARE 
message varchar2(30) := 'greetings from PL/SQL'; 
BEGIN 
dbms_output.put_line(UPPER(message)); 
 
dbms_output.put_line(LOWER(message));
 
dbms_output.put_line(INITCAP(message));

/* find the location of the first "e" */ 
dbms_output.put_line ( INSTR (message, 'e'));

dbms_output.put_line(LENGTH(message));
 
/* retrieve the first character in the string */ 
dbms_output.put_line ( SUBSTR (message, 1, 1)); 
 
/* retrieve the last character in the string */ 
dbms_output.put_line ( SUBSTR (message, -1, 1)); 
 
/* retrieve five characters, 
starting from the seventh position. */ 
dbms_output.put_line ( SUBSTR (message, 7, 5)); 
 
/* retrieve the remainder of the string, 
starting from the second position. */ 
dbms_output.put_line ( SUBSTR (message, 2)); 
 
 
END;
/
GREETINGS FROM PL/SQL
greetings from pl/sql
Greetings From Pl/Sql
3
21
g
L
ngs f
reetings from PL/SQL




PL/SQL procedure successfully completed.

Results :

dbms_output.put_line(UPPER(message));

GREETINGS FROM PL/SQL

dbms_output.put_line(LOWER(message));

greetings from pl/sql

dbms_output.put_line(INITCAP(message));

Greetings From Pl/Sql

dbms_output.put_line ( INSTR (message, 'e')); 

3

dbms_output.put_line(LENGTH(message));

21

dbms_output.put_line ( SUBSTR (message, 1, 1)); 

g

dbms_output.put_line ( SUBSTR (message, -1, 1)); 

L

dbms_output.put_line ( SUBSTR (message, 7, 5)); 

ngs f

dbms_output.put_line ( SUBSTR (message, 2));

2

First Program – PL/SQL

A simple program in PL/SQL

Firtsprogram

Above program executed and printed the output as “PL/SQL procedure successfully completed.” but not printed “Hello Learner”.

We need to execute “SET SERVEROUTPUT ON” if we need to see the output of the code.

Firtsprogram2

Declaring and usage of variables in program

declare

Here, DECLARE statement is used to declare the variables and := operation is used to assign value to the variable.

DBMS_OUTPUT.PUT_LINE is used to print the values.

Commenting Codes

Comments let you include  text within your code to explain what the code does. PL/SQL compiler ignores comments.

PL/SQL supports two types of comments.

  • Single line comments (- -)
  • Multi line comments (/*    ……….*/)

Comments.JPG

SQL Plus and SQL Developer

SQL Plus

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.

SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:

  • Format, perform calculations on, store, and print from query results
  • Examine table and object definitions
  • Develop and run batch scripts
  • Perform database administration

To start SQL*Plus and connect to the default database

Open a Linux or a Windows terminal and enter sqlplus command.

sqplus

sqplus2.png

When prompted, enter your Oracle Database username and password.

Alternatively, enter the SQL*Plus command in the form sqlplus username/password

Commandprompt
To hide your password, enter the SQL*Plus command in the form sqlplus username
You will be prompted to enter your password.

sqplus3.JPG

SQL*Plus starts and connects to the default database.

Now you can start entering and executing SQL, PL/SQL and SQL*Plus statements and commands at the SQL> prompt.

SQL Developer.

Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle. 

If oracle database server installed in your local system, just search for SQL developer, it will install along with db server.

If oracle database installed remotely, you should install oracle client to communicate to the remote database over the internet. Here also, SQL developer will come along with Oracle client software. 

If you want to install other latest SQL Developer software, you can download from Oracle website and install in your computer.

Welcome screen of SQL Developer

SQLDeveloper.png

Connecting to Database

Click on new connection (+) Button. It will prompt for data base details.

Connection

Enter Connection Name, user name, passwd, hostname (for remote connection enter IP address) and SID and click on Test button.

Once connection is successful, click on Save button and Connect to connect the database.

Success_Connection.JPG

Then worksheet will open to work with database.

Worksheet.jpg

You can see all data base objects by expanding the connection.

objects

Advantages of PL/SQL

Advantages of PL/SQL

Tight Integration with SQL

PL/SQL is tightly integrated with SQL, the most widely used database manipulation language.

PL/SQL fully supports SQL data types and lets use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators.

High Performance

PL/SQL executes block of statements at a time, significantly reducing traffic between the application and the database.

The PL/SQL compiler has an optimizer that can rearrange code for better performance.

High Productivity

PL/SQL lets you write compact code for manipulating data. Just as a scripting language like PERL can read, transform, and write data in files, PL/SQL can query, transform, and update data in a database.

PL/SQL has many features that save designing and debugging time, and it is the same in all environments. If you learn to use PL/SQL with one Oracle tool, you can transfer your knowledge to other Oracle tools. For example, you can create a PL/SQL block in SQL Developer and then use it in an Oracle Forms trigger.

Portability

You can run PL/SQL applications on any operating system and platform where Oracle Database runs.

Scalability

PL/SQL stored subprograms increase scalability by centralizing application processing on the database server.

Manageability

PL/SQL stored subprograms increase manageability because you can maintain only one copy of a subprogram, on the database server, rather than one copy on each client system. Any number of applications can use the subprograms, and you can change the subprograms without affecting the applications that invoke them.

Support for Object-Oriented Programming

PL/SQL supports object-oriented programming with “Abstract Data Types”.

Support for Developing Web Applications

PL/SQL lets you create applications that generate web pages directly from the database, allowing you to make your database available on the Web and make back-office data accessible on the intranet.

You can implement a Web browser-based application entirely in PL/SQL with PL/SQL Gateway and the PL/SQL Web Toolkit.

Main Features of PL/SQL

PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages.

When you can solve a problem with SQL, you can issue SQL statements from your PL/SQL program, without learning new APIs.

Like other procedural programming languages, PL/SQL lets you declare constants and variables, control program flow, define subprograms, and trap runtime errors.

You can break complex problems into easily understandable subprograms, which you can reuse in multiple applications.

Types of PL/SQL blocks

      A PL/SQL program cab be written in various types of blocks, they are

  1. Anonymous blocks
  2. Named blocks
  3. Nested Blocks

Anonymous blocks:

      Anonymous blocks have no name as a result they cannot be stored in database.They are declared at the point in an application where they are to be executed and passed to PL/SQL engine for execution at run time.

Syntax :

[DECLARE]
Declaration statements;
BEGIN
Execution statements;
[EXCEPTION]
Exception handling statements;
END;
/

 

Named blocks:

      Named PL/SQL blocks have names and they have all features as specified for the anonymous blocks. They can be stored in the database and referenced by their name later. Named blocks help in associating with the scope and resolution of variables in different blocks.

They give the specifications of the named spaces as provided in high level OOPs languages like C++ and Java. Named blocks are conveniences for variable management.

Named blocks make the PL/SQL blocks more clear and reliable and also increasing the clarity of programming when we attempt to nesting process and control statements.

 

Nested Blocks :

      The blocks inside another block are called nested blocks. The outer PL/SQL block is called as parent block or enclosing block and the inner PL/SQL block is known as child block or nested block.

 

Comments in PL/SQL

Comments let you include  text within your code to explain what the code does. PL/SQL compiler ignores comments.

PL/SQL supports two types of comments.

  • Single line comments (- -)
  • Multi line comments (/*    ……….*/)