PL SQL Variables And Constants

PLSQL Variable

A variable is a name for a specific memory location, which is human readable. With the help of variable (data container you can say) our program can store data. Every variable has a datatype. Datatype defines the type of data and size of data , the variable is going to have. Consequently, the operation on it differs.

Rules :

  • The variable name must be less than 31 characters.
  • The variable name must begin with an ASCII letter. Notice that PL/SQL is case-insensitive.
  • Followed by the first character are any number, underscore ( _), and dollar sign ( $) characters.

Naming convention :
Though each organisation has its own naming convention, however oracle uses following naming

PREFIX DATATYPE
n_ number
t_ table
r_ row
d_ date
b_ boolean

For example, to declare a name you can write v_name VARCHAR2(100);
Here v_name means, name which will contain a varchar2 data.

PLSQL variable declaration:

syntax :
variable_name datatype(scale);
To declare a variable, you need to mention datatype , scale (depending on situations we will discuss in subsequent tutorials) and ends with semicolon ( ; ).

Example :

DECLARE
v_fname varchar2(20);
v_lname varchar2(20);
n_emp_id number;
d_hire_dt date;
BEGIN
NULL;
END;

Note : New To CodeBook.co.in Check Our Other Concepts :
PLSQL Declare
PLSQL Begin Clause 
PLSQL Datatype

Variable Assignment :

Variables can be assigned with values either at the time of declaration ( also termed as data initialization at the time of declaration ) or inside the begin and end block .
Example :

DECLARE
v_name VARCHAR2(50) := 'CodeBook.co.in' ;     --generally used to give a initial value
BEGIN
dbms_output.put_line('v_name value is : ' || v_name);
END:
/
DECLARE
v_name VARCHAR2(50);
BEGIN
v_name := 'PLSQL is simple.';
dbms_output.put_line('v_name value is : ' || v_name);
END:
/

PLSQL Constants :

A constant is a special type of variable whose value remains unchanged throughout the program execution. The key thing is for constant , value has to be assigned at the time of declaration.

Declaration of Constants :

variable_name CONSTANT datatype(scale);
For example,

DECLARE
pi CONSTANT NUMBER := 3.14;
BEGIN
dbms_output.put_line('value of pi is :' || pi);
END:

PLSQL Literals:

A literal is an explicit boolean, string, numeric, string value. It does not have any variable name as its reference is not stored.

Literal Type Value
Numeric Literals 9001
Character Literals ‘A’ ‘&’
String Literals ‘CodeBook.co.in’
Boolean Literals TRUE FALSE NULL
Date and Time Literals DATE ‘2001-03-03’;  TIMESTAMP ‘2016-11-20 02:22:22’;

Example :

DECLARE
text varchar2(30):= 'WOW..PLSQL is simple';
BEGIN
dbms_output.put_line(text);
END;
/

PL SQL Procedure

PLSQL Procedures

Computers are good at doing repeated things.

A plsql procedure is basically a set of statements performing specific task. Instead of writing the code by copy pasting over and again in cross applications , the bunch of statements are given a name. Whenever we want to perform that particular, task we just call the procedures.

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

Here, CREATE, OR, REPLACE, PROCEDURE,IN, OUT,IN OUT, IS, AS, BEGIN, END are reserved PLSQL words.

CREATE basically tells PLSQL that , you know what “I want you to create a procedure with name procedure_name…”

If there is an existing procedure already in the database with the same name then It will through an error.

CREATE PROCEDURE BONUS_CALC AS
BEGIN
NULL;
END BONUS_CALC;

Procedure BONUS_CALC compiled

CREATE PROCEDURE BONUS_CALC AS
BEGIN
NULL;
END BONUS_CALC;
Error starting at line : 1 in command -
CREATE PROCEDURE BONUS_CALC AS
BEGIN
NULL;
END BONUS_CALC;
Error report -
ORA-00955: name is already used by an existing object
00955. 00000 - "name is already used by an existing object"
Cause: Action:

Let us create a procedure which says hello.

CREATE PROCEDURE say_hello AS
BEGIN
dbms_output.put_line('Hello ! Welcome to CodeBook.co.in . Please Subscribe us.');
END say_hello;
set serveroutput on
begin
SAY_HELLO();
end;
Or
begin
SAY_HELLO;
end;
Or
execute say_hello;
Result: PL/SQL procedure successfully completed.
Hello ! Welcome to CodeBook.co.in . Please Subscribe us.

Clean up your database :

drop procedure say_hello
Procedure SAY_HELLO dropped.
drop procedure say_hello
Error starting at line : 20 in command -
drop procedure say_hello
Error report -
SQL Error: ORA-04043: object SAY_HELLO does not exist

Parameters Can Be Of Either of Three Modes :

1. IN Default mode of passing the value
2. IN OUT
3. OUT

IN : Value is passed into the procedure block.
IN OUT : Value is passed into with a variable , and value of variable is modified or so , and reflected in the calling procedure.
OUT : Null is passed in but returns the value assigned by called function to the calling function.

1. IN mode :

--IN Parameter 
create or replace procedure bonus_value (ename varchar2, salary in number)
as
bonus_val    number (20);
begin
dbms_output.put_line
(   'Hello '
|| ename
|| '! Welcome to CodeBook.co.in . Please Subscribe us.'
);
if salary < 2000000
then
bonus_val := salary * .13;
else
bonus_val := salary * .10;
end if;
dbms_output.put_line ('Salary Bonus:' || bonus_val);
end bonus_value;
set serveroutput on;
execute bonus_value('Pari', 50000000);
Hello Pari! Welcome to CodeBook.co.in . Please Subscribe us.
Salary Bonus:65000
execute bonus_value('Pari', 50000000);
Hello Pari! Welcome to CodeBook.co.in . Please Subscribe us.
Salary Bonus:5000000

 2. IN OUT mode :

create or replace procedure bonus_value (ename varchar2, salary in out number)
as
bonus_val    number (20);
begin
dbms_output.put_line
(   'Hello '
|| ename
|| '! Welcome to CodeBook.co.in . Please Subscribe us.'
);
if salary < 2000000
then
bonus_val := salary * .13;
else
bonus_val := salary * .10;
end if;
salary := salary + bonus_val;
dbms_output.put_line ('Salary Bonus:' || bonus_val);
end bonus_value;
set serveroutput on;
declare
salary1    number (20) := 200000;
salary2    number (20) := 2000000000;
begin
bonus_value ('User 1 ', salary1);
bonus_value ('User 2', salary2);
dbms_output.put_line ('user1 ''s salary : ' || salary1);
dbms_output.put_line ('user2 ''s salary : ' || salary2);
end;

 3. OUT mode :

create or replace procedure bonus_value (
ename                     varchar2,
salary          in        number,
gross_salary    out       number
)
as
bonus_val    number (20);
begin
dbms_output.put_line
(   'Hello '
|| ename
|| '! Welcome to CodeBook.co.in . Please Subscribe us.'
);
if salary < 2000000
then
bonus_val := salary * .13;
else
bonus_val := salary * .10;
end if;
gross_salary := salary + bonus_val;
end bonus_value;
set serveroutput on;
declare
salary1         number (20) := 200000;
salary2         number (20) := 2000000000;
gross_salary    number (20);
begin
bonus_value ('User 1 ', salary1, gross_salary);
dbms_output.put_line ('user1 ''s salary : ' || gross_salary);
bonus_value ('User 2', salary2, gross_salary);
dbms_output.put_line ('user2 ''s salary : ' || gross_salary);
end;

 Positional and Mixed Notation :

declare
salary1             number (20) := 200000;
salary2             number (20) := 2000000000;
gross_salary    number (20);
begin
bonus_value (
ename            => 'User 1 ',
salary             =>  salary1,
gross_salary  =>  gross_salary
);
dbms_output.put_line ('user1 ''s salary : ' || gross_salary);
bonus_value ('User 2', salary => salary1, gross_salary => gross_salary);
dbms_output.put_line ('user2 ''s salary : ' || gross_salary);
end;

 

PL SQL Loop

LOOP Statements 


Loop contains a set of statements which has keeps executing same set of statements over and again. The plsql loop looks like ,
Syntax:
LOOP
statements;
END LOOP ;
There has to be a termination condition in the LOOP block, else it will turn into a infinite block. This termination condition is called the EXIT condition.

EXIT :
The EXIT condition in the current loop takes the control just after the current loop. That means it exits out of the loop.

EXIT WHEN :
EXIT WHEN is used to instruct the EXIT of the loop based on certain condition.
Syntax :

DECLARE
 i pls_integer := 0;
BEGIN
LOOP
      i := i + 1;
      dbms_output.put_line (‘Welcome to PL/SQL programming’);
      EXIT WHEN i>5;
 END LOOP;
END;

CONTINUE : CONTINUE is used to skip the current iteration of the loop and gives back the control to its next iteration.

DECLARE
  i pls_integer := 0;
BEGIN
LOOP
    dbms_output.put_line (‘This is iteration : ’ || i);
    i := i + 1;
   CONTINUE;
    dbms_output.put_line (‘This will not be printed at all’);
END LOOP;
END;

CONTINUE WHEN : CONTINUE WHEN is used to skip the current iteration of the loop and gives back the control to its next iteration based on certain condition.

DECLARE
  i pls_integer := 0;
BEGIN
LOOP
   dbms_output.put_line (‘This is iteration : ’ || i);
   i := i + 1;
   CONTINUE WHEN mod(i, 2) = 0;
   dbms_output.put_line (‘This will be printed at all for odd i : i :’ || i);
END LOOP;
END;

FOR Statements :
FOR loop is ideal when you know the number of iterations loop is going to be executed. That is the range is pre known .
Syntax :
FOR i IN [REVERSE] lower_bound..upper_bound
LOOP
Statements;
END LOOP ;

By default if REVERSE is not used then i value starts at the lower_bound increases by 1 till it reaches upper_bound. If lower_bound > upper_bound then the statements never run.
With REVERSE , i value starts at the upper_bound decreases by 1 till it reaches lower_bound. If lower_bound > upper_bound then the statements never run.

EXIT, EXIT WHEN, CONTINUE, CONTINUE WHEN can be used to exit out of the loop early or to skip some of the codes depending upon current iteration and by using CONTINUE statement.

WHILE loop statements:
WHILE loop runs the block of statements till the condition of the same is TRUE.
Syntax:
WHILE condition
LOOP
statements ;
END LOOP;

If the condition is TRUE then the statements execute and the condition gets checked again. This continues till the condition is evaluated as FALSE. If the condition is not updated and TRUE initially, then it turns out to be a infinite loop.

PL SQL Expression

A plsql expression always returns single value.

A single variable or constant

  1. Unary operator
  2. Binary operators

Unary : It has one operand. e.g. -number . Here, – is operator and number is operand.
Binary : It has two operands. So it is called binary. e.g. salary + bonus . Here, + is the operator and ‘salaly,bonus’ are operands.
Please note that operands can be variables or expressions or operator or function invocation or placeholder.
The datatype of operands determine the datatype of the expression. So the datatype of computed result is the datatype of operation.

Concatenation Operator

The concatenation operator appends string literals. Symbol is || (two vertical pipelines)

DECLARE
v_first_name VARCHAR2(30) := 'Sameer';
v_last_name VARCHAR2(30) := 'Pradhan';
v_space char(1) := ' ';
BEGIN
DBMS_OUTPUT.PUT_LINE ('Name : ' || v_first_name || space || v_last_name);
END;
/
Result : Sameer Pradhan

The concatenation || operator ignores null operands.

DECLARE
v_first_name VARCHAR2(30) := 'Sameer';
v_last_name VARCHAR2(30) := 'Pradhan';
v_space char(1) := ' ';
BEGIN
DBMS_OUTPUT.PUT_LINE ('Name : ' || NULL ||v_first_name || space || NULL || NULL || v_last_name);
END;
/
Result : Sameer Pradhan

Operator Precedence

An operation is (unary operator and one operand) or ( binary operator and its two operands ). As we discussed, expressions are combination
of many operations. So operations are evaluated in their precedance order.

Operator Precedence (decreasing order)

Operator Operation
** exponentiation
+, – identity, negation
*, / multiplication, division
+, -, || addition, subtraction, concatenation
=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN comparison
NOT negation
AND conjunction
OR inclusion
DECLARE
a INTEGER := 1+2*5;
b INTEGER := (1+2)*5;
BEGIN
DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
DBMS_OUTPUT.PUT_LINE('b = ' || TO_CHAR(b));
END;
/
Result : 11
15

Logical Operators

Binary Operator
⇝AND
⇝OR
⇝Unary Operator
⇝NOT

These logical operators follow tri-state logic. TRUE, FALSE and NULL.

x y x AND y x OR y NOT x
TRUE TRUE TRUE TRUE FALSE
TRUE FALSE FALSE TRUE FALSE
TRUE NULL NULL TRUE FALSE
FALSE TRUE FALSE TRUE TRUE
FALSE FALSE FALSE FALSE TRUE
FALSE NULL FALSE NULL TRUE
NULL TRUE NULL TRUE NULL
NULL FALSE FALSE NULL NULL
NULL NULL NULL NULL NULL

Comparison Operators

Comparison operators compares between expressions, value of variables, literals.
Result is boolean :
⇝TRUE
⇝FALSE
⇝NULL

The comparison operators in plsql are :
⇝IS[NOT] NULL
⇝LIKE
⇝BETWEEN
⇝IN
⇝Relational Operators

We will discuss about IS[NOT] NULL, LIKE, BETWEEN AND, IN in control flow.

Relational Operators(logical)

Operator Meaning
= not equal to
< less than
> greater than
<= less than or equal to
>= greater than or equal to

Thank you for visiting.

PL SQL Exception

PLSQL run-time errors are called exceptions. PLSQL exceptions can be due to design faults, coding mistakes, hardware failures, and many other sources. Though you can not avoid all the exceptions always, however you can write exception handlers.

Exceptions are good things. These give extra information about the behaviour of the plsql application. What we can do is, we can handle the exceptions and make the rest program run even if some runtime error occurs. We hate to see abnormal termination of program, right ? Yes Exception block does the same thing. It handles it and say “you know what plsql, something goes wrong. However [ Please execute rest of the program/Terminate it right bow ].”

Any plsql block can have exception handlers.
Syntax :

BEGIN
  --statements
  --executable part
EXCEPTION
   WHEN exception_1 THEN statement_1;
   WHEN exception_2 THEN statement_2;
   WHEN exception_3 THEN statement_3;
   .
   .
   WHEN OTHERS THEN statement_n ;
END;
/

Just in case an exception is raised in executable part of the program unit, the control is immediately passed to the EXCEPTION block. If exception_1 then statement_1 runs. Likewise if non of the exception matches it is catched by OTHERS and statement_n is run.

Exception propagates if not handled in the present block.

  • If the exception happens in the sub-program then the control goes back t the invoker.
  • If it is an anonymous block then the control goes to SQL * PLUS (hosting environment).

PL SQL Datatypes

Know PL-SQL Data types

All the constants , variables , parameters has a data type i.e. the type of data it contains. This means the type of data, size if data, which operations can be performed on the same data and the acceptable value range. In PLSQL, many predefined data types are available and many sub types are also configured to add flexibility on data operations.

A sub type is derived from base type data. Subtypes increase reliability, provide compatibility with ANSI/ISO types and improve readability by providing meaningful logical naming.

This tutorial will take you through different data types with their usages. Lets get started.

Predefined PL/SQL Data Types

Data Type Data Description
Scalar Single values with no internal components.
Composite Data items that have internal components that can be accessed individually.
Reference Pointers to other data items.
Large Object (LOB) Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.

Predefined PL/SQL Scalar Data Types and Subtypes

Scalar type stores value with no internal sub data component. Below the scalar datatypes and there description is mentioned.

Scalar Data Type Data Description
Numeric Numeric values (you can perform arithmetic operations).
Character Alphanumeric values (single characters or strings of characters).
BOOLEAN Logical values, on which you can perform logical operations.
Datetime Dates and times.
Interval Time intervals.

Predefined PL/SQL Numeric Data Types and Subtypes

Numeric datatypes represents quantities, performs arithmetic operations, stores numeric data. Below the numeric datatypes and there description is mentioned.

Numeric Data Type Data Description
PLS_INTEGER or BINARY_INTEGER Signed integer -> range -2,147,483,648 to 2,147,483,647, 32 bits representation
BINARY_FLOAT Single-precision IEEE 754-format floating-point number
BINARY_DOUBLE Double-precision IEEE 754-format floating-point number
NUMBER Fixed-point or floating-point number with absolute value in range 1E-130 to (but not including) 1.0E126. A NUMBER variable can also represent 0.

PLS_INTEGER and BINARY_INTEGER :

PLS_INTEGER and BINARY_INTEGER are same.
PLS_INTEGER : signed integers [range -2,147,483,648 through 2,147,483,647 (in 32 bits)].

Advantages of PLS_INTEGER over NUMBER data type and NUMBER sub type:

1. less storage space
2.Faster as uses hardware arithmetic .

Note : If two pls_integer arithmetic operations are done and it exceeds (pls_integer range) then it is assigned to NUMBER datatype.

Predefined Subtypes of PLS_INTEGER Data Type

Data Type Data Description
NATURAL Nonnegative PLS_INTEGER value
NATURALN Nonnegative PLS_INTEGER value with NOT NULL constraint
POSITIVE Positive PLS_INTEGER value
POSITIVEN Positive PLS_INTEGER value with NOT NULL constraint
SIGNTYPE PLS_INTEGER value -1, 0, or 1 (useful for programming tri-state logic)
SIMPLE_INTEGER PLS_INTEGER value with NOT NULL constraint

SIMPLE_INTEGER Subtype of PLS_INTEGER

SIMPLE_INTEGER(predefined sub-type of pls_integer) has the same range as PLS_INTEGER (-2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint. It differs significantly in terms of overflow semantics.

You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary.
1.SIMPLE_INTEGER provides far better performance than PLS_INTEGER when PLSQL_CODE_TYPE=’NATIVE'(why ? Ans:Arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware).
PLSQL_CODE_TYPE=’INTERPRETED’, the performance improvement is smaller.

 

Number Data Type :

The NUMBER data type stores fixed-point or floating-point numbers with absolute values in range(1E-130, 1.0E126). A NUMBER variable can also represent 0.
Oracle recommends using NUMBER literals and results of NUMBER computations that are within specified range. Otherwise, this may lead to error result.
1. Value those are too small is rounded to Zero.
2. A literal value that is too large causes a compilation error.
3. A computation result that is too large may cause unexpected result and run time errors.
A Number datatype has precision and scale.
Precision : Total number of digits
Scale : Number of decimal points
Syntax :
NUMBER(precision, scale)
For example: NUMBER(8,2)
For scale = 0, syntax : NUMBER(precision) i.e. NUMBER(10)

Syntax for floating point number : NUMBER
Note : Precision and Scale can only be Integer Literals. It can not be Variables or constants.
For precision, the maximum value is 38. The default value is 39 or 40, or the maximum for your system, whichever is least.

For scale, the minimum and maximum values are -84 and 127, respectively. The default value is zero.

For example ,

DECLARE
number1 NUMBER;
number2 INTEGER;
number3 REAL;
name VARCHAR2(40);
gender CHAR(1);
BEGIN
dbms_output.put_line( 'PLSQL block successfully executed.' );
END;
/

 CHAR vs VARCHAR2 :

CHAR datatype is used for fixed memory allocation. VARCHAR2 is used for dynamic memory allocation.
lets say,

CREATE TABLE demo(
charcol    CHAR(10),
varcharcol VARCHAR2(10));
SELECT LENGTH(charcol), LENGTH(varcharcol) FROM demo;
/
Result:
LENGTH(CHARCOL) LENGTH(VARCHARCOL)
--------------- ------------------
10                  1

Char is faster then varchar2. Memory allocation of varchar2 depends on the data stored in the variable.

PL SQL Begin End

BEGIN clause Syntax

BEGIN
dbms_output.put_line('Hello User.');
END;
/

Illustration :

1.BEGIN–>PLSQL execution starts from here.
2.dbms_output.put_line(v_say_hello);–>dbms_output is a package. put_line is a function. Package: A block of code wrapped together.
3.END;–>PLSQL execution ends here.

What is the smallest block in PLSQL ?

BEGIN
NULL; --null is a statement which simply does nothing
END;
/

Here, we can see that declare section is not mandatory. In plsql only mandatory block is begin end;

DECLARE
v_mob_num number(20) ;
BEGIN
SELECT 9000000971
INTO v_mob_num
FROM dual
WHERE 1 = 2;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Some error happened. App crashed.');
END;
/

EXCEPTION section is not mandatory. But, it is needed to control unwanted termination/crash of application.
More will be discussed in later topics.

PL SQL Conditional Statements

PLSQL – Control statements can be conditional, loop or sequential statements.

Conditional Control Statements

  1. Conditional control statements are decision making statements. These includes if statement and its variations ,case statements.
  2. Loop statements include while loop, for loop. To add more control to loops exit,exit when,continue, continue when       statements are there.
  3. GOTO statements and NULL statements are termed as sequential purpose statements.

We will go through one by one. Lets begin.

Conditional control statements

  • IF THEN : Unlike many programming languages like C, C++ plsql has its own syntax of if statement. If statement is used in making yes no decision. Its like if something happens do execute the block.

Syntax :

Declare
  is_raining boolean := TRUE ;
Begin
  if is_raining then
    dbms_output.put_line( ' Yes , It is raining ');
  end if;
End;
Output : Yes, It is raining
  • IF THEN ELSE : With the above if block only thing is what if the if block condition is FASLE. In that case it will execute the else block.

Syntax :

Declare    
is_sunday boolean := TRUE ;
Begin    
if is_sunday then       
dbms_output.put_line( ' Today is holiday. ');    
Else      
dbms_output.put_line( ' Today is not holiday. ');    
end if;
End;
Output : Today is holiday.

IF THEN ELSIFTo test multiple conditions we can use if then elsif in plsql.

IF condition_1 THEN
Statement1;
Elsif condition_2 THEN
  Statement2;
Elsif conditon_3 THEN
 Statement3;
.
.
ELSE condition_else THEN
  statementn;
END IF;

Syntax :

Declare
   percentage_marks pls_integer := 65;
Begin
  if percentage_marks >= 60 then
     dbms_output.put_line( 'first division ');
  elsif percentage_marks >= 40 and  percentage_marks < 60  then
     dbms_output.put_line( 'second division ');
  elsif percentage_marks >= 30 and  percentage_marks < 40  then
     dbms_output.put_line( 'third division ');
  Else
     dbms_output.put_line( 'fail ');
  end if;
End;
  • Simple CASE Statement : In simple case statement one selector is chosen. Based on the value of the selector we can execute different values .

    CASE selector
        WHEN selector_value_1 THEN statement_1;
        WHEN selector_value_2 THEN statement_2;
        WHEN selector_value_3 THEN statement_3;
        WHEN selector_value_4 THEN statement_4;
        ELSE statement_5;
    END CASE;

    First selector = selector_value_1 is evaluated. If TRUE then statement_1 is executed and case statement ends.  If FALSE then only next condition is checked. This steps happens for all when clauses sequentially.A selector can have NULL value . However we can not write something likeCASE selector
    WHEN null THEN statemenet_1;
    WHEN selector_value_2 THEN statemenet_2;
    ELSE statement_3;
    END CASE;In this case we have to go for the searched CASE statement.
  •  Searched CASE statement :
    CASE
    WHEN selector = selector_value_1 THEN statement_1;
    WHEN selector < selector_value_2 THEN statement_2;
    WHEN selector > selector_value_3 THEN statement_3;
    WHEN selector IS NULL THEN statement_4;
    ELSE statement_5;
    END CASE;

    Thing is in here , the conditions are generally taken in such a way that they are mutually exclusive.  
    It is must to use else in every case statements. It is because if non of the when condition is true then it will end of with exception (CASE_NOT_FOUND).

PL SQL Record Type

What are records ?
Records in pl sql are composite datatype. That means it can hold more than one data. The type of data can be similar or not.

There are several ways a record can be used. Lets see how.

-- pl sql collections, pl sql, pl sql record, record pl, oracle record type, oracle collections, type, rowtype, oracle pl sql collection, oracle type record, records in plsql
create table books_cb
(
book_id number(10),
book_name varchar2(100),
book_author varchar2(100),
book_pages number(5)
);
/
--populate dummy data
begin
for i in 1 .. 10
loop
insert into books_cb
values (i, 'book' || i, 'author' || i, i + 1000);
end loop;
commit;
exception
when others
then
null;
end;
/
--verify data
select * from books_cb;
--reference FOR LOOP
--loop 10 times and fetch data
declare
type 
lrec_book_typ is record
(
id number(10),
name varchar2(100),
author varchar2(100),
pages number(5)
); 
lrec_book lrec_book_typ;
--    lrec_book    books_cb%rowtype;
begin
for i in 1..11
loop
select *
into lrec_book
from books_cb
where book_id = i;
dbms_output.put_line ('Book ID: ' || lrec_book.id);
dbms_output.put_line ('Book Name: ' || lrec_book.name);
dbms_output.put_line ('Book Author: ' || lrec_book.author);
dbms_output.put_line ('Book No Of Pages: ' || lrec_book.pages);
dbms_output.put_line ('----------------------------------------------------------');
end loop;
exception
when others
then
dbms_output.put_line('Error in execution');
end;
/
declare
lrec_book    books_cb%rowtype;
begin
select *
into lrec_book
from books_cb
where book_id = 1;
dbms_output.put_line ('Book ID: ' || lrec_book.book_id);
dbms_output.put_line ('Book Name: ' || lrec_book.book_name);
dbms_output.put_line ('Book Author: ' || lrec_book.book_author);
dbms_output.put_line ('Book No Of Pages: ' || lrec_book.book_pages);
dbms_output.put_line ('----------------------------------------------------------');
exception
when others
then
null;
end;
/
--reference LOOP
--reference CURSOR
declare
cursor lcur_book is
select book_id, book_name, book_author, book_pages 
from books_cb;
lrec_book lcur_book%rowtype;
begin
open lcur_book;
loop
fetch lcur_book into lrec_book;
exit when lcur_book%notfound;
dbms_output.put_line ('Book ID: ' || lrec_book.book_id);
dbms_output.put_line ('Book Name: ' || lrec_book.book_name);
dbms_output.put_line ('Book Author: ' || lrec_book.book_author);
dbms_output.put_line ('Book No Of Pages: ' || lrec_book.book_pages);
dbms_output.put_line('----------------------------------------------------------');
end loop;
exception
when others
then
null;
end;
/
declare
cursor lcur_book is
select book_id, book_name, book_author, book_pages 
from books_cb;
-- lrec_book lcur_book%rowtype;
type 
lrec_book_typ is record
(
id number(10),
name varchar2(100),
author varchar2(100),
pages number(5)
); 
lrec_book_other lrec_book_typ;
begin
open lcur_book;
loop
fetch lcur_book into lrec_book_other;
exit when lcur_book%notfound;
dbms_output.put_line ('Book ID: ' || lrec_book_other.id);
dbms_output.put_line ('Book Name: ' || lrec_book_other.name);
dbms_output.put_line ('Book Author: ' || lrec_book_other.author);
dbms_output.put_line ('Book No Of Pages: ' || lrec_book_other.pages);
dbms_output.put_line ('----------------------------------------------------------');
end loop;
close lcur_book;
exception
when others
then
null;
end;
/
begin
for lrec_book in (select book_id, book_name, book_author, book_pages
from books_cb)
loop
dbms_output.put_line ('Book ID: ' || lrec_book.book_id);
dbms_output.put_line ('Book Name: ' || lrec_book.book_name);
dbms_output.put_line ('Book Author: ' || lrec_book.book_author);
dbms_output.put_line ('Book No Of Pages: ' || lrec_book.book_pages);
dbms_output.put_line ('----------------------------------------------------------');
end loop;
exception
when others
then
null;
end;
/

 

PL SQL Declare

Declare Clause

/******************************************************************
CodeBook : ILLUSTRATION OF DECLARE 1.0
******************************************************************/
DECLARE
--memory variables
ch_strudent_id CHAR(6) := 123456; --1
v_student_name VARCHAR2(40) := 'S Aditya Patra'; --2
d_student_dob DATE := to_date('30/01/1994', 'dd/mm/yyyy'); --3
pi_student_age PLS_INTEGER := 21; --4
ts_student_birth_time timestamp := to_timestamp('12:01 15', 'hh:mm ss'); --5
d_height NUMBER := 6.1; --6
d_weight INTEGER := 72.5; --7
BEGIN
dbms_output.put_line('Student deatails:');
dbms_output.put_line('Name: '||v_student_name);
dbms_output.put_line('ID: '||ch_strudent_id);
dbms_output.put_line('Date of birth: '||d_student_dob);
dbms_output.put_line('Age: '||pi_student_age);
dbms_output.put_line('Birth time: '||  to_char(ts_student_birth_time, 'hh:mm ss AM'));
dbms_output.put_line('Height: '||d_height);
dbms_output.put_line('Weight: '||d_weight);
END;
/

Lets see what is exactly happening

  • 1.123456 implicitly converted to ‘123456’ (implicite type casting number to char)
  • 2.Name(String literal) declared and initialised.
  • 3.’30/01/1994′ string is converted to date using to_date(string,format) predefined function.
  • 4.pls_integer is plsql datatype. Faster.
  • 5.Timestamp datatype declared and initialised in tha same way as date variable done.

Note:
There are other stuffs that can be included in declare section. Have some snacks and chill. We gonna be covering almost all possible cases.

Best Online Tutorial