1) what is the plsql block structure?
[DECLARE] --Optional
BEGIN
[EXCEPTION]--Optional
END;
Example:
DECLARE
--v_billingdate TIMESTAMP(9):= SYSTIMESTAMP;
v_billingdate TIMESTAMP := SYSTIMESTAMP;
v_transactiondate DATE:= SYSDATE;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_billingdate);
DBMS_OUTPUT.PUT_LINE(v_transactiondate);
END;
/
16-OCT-13 01.25.24.846209000 PM
16-OCT-13
PL/SQL procedure successfully completed.
2) Describe procedure under DBMS_OUTPUT packages .
- DBMS_OUTPUT.PUT_LINE
An oracle supplied packaged procedure
-
DBMS_OUTPUT is a package and PUT_LINE is a procedure within the package
The string which has to be printed should be specified in parenthesis, following the
PUT_LINE keyword
- Using SET SERVEROUTPUT ON this package should be enabled in SQLPLUS
Example:
SET SERVEROUTPUT ON
DECLARE
v_productcategory VARCHAR2(20) :='Mobile Phone';
v_itemqty PLS_INTEGER := 1000;
v_itemprice NUMBER(7,2) DEFAULT 60.50;
v_transactiondate DATE:= SYSDATE;
v_billingdate TIMESTAMP := SYSTIMESTAMP;
v_test BOOLEAN:=TRUE;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_productcategory);
DBMS_OUTPUT.PUT_LINE(v_transactiondate);
DBMS_OUTPUT.PUT_LINE (v_billingdate);
END;
/
More about DBMS_OUTPUT package
- Used to display messages to the screen from an anonymous PL/SQL block
- Debugging is the most popular use of this package
- When executing a PL/SQL block, any DBMS_OUTPUT lines are placed in an output buffer, which displays its contents on the screen when the program has completed execution
3) What are the procedures under the DBMS_OUTPUT package?
Procedure
available
Description
----------------------------
------------------
DBMS_OUTPUT.ENABLE Allow
msg display (not necessary if SERVEROUTPUT set ON in the SQL*PLUS)
DBMS_OUTPUT.DISABLE Does
not allow message display
DBMS_OUTPUT.PUT
Place information in the buffer
DBMS_OUTPUT.PUT_LINE Place information
in the buffer followed by an end-of-line marker
DBMS_OUTPUT.NEW_LINE Place an end-of-line
marker in the buffer
Example1
BEGIN
DBMS_OUTPUT.PUT('This is ');
DBMS_OUTPUT.put('line one ');
DBMS_OUTPUT.PUT_LINE('This is line one continued');
DBMS_OUTPUT.PUT('This is line two');
END;
OUTPUT: This is line one This is line one continued
Example2
BEGIN
DBMS_OUTPUT.PUT('This is');
DBMS_OUTPUT.PUT('line one ');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('This is line two ');
DBMS_OUTPUT.PUT('This is line three');
DBMS_OUTPUT.NEW_LINE;
END;
/*
This isline one
This is line two
This is line three
*/
NOTE :
DBMS_OUTPUT.NEW_LINE('INBETWEEN');
--NOT ALLOWED
DBMS_OUTPUT.NEW_LINE();
--VALID
DBMS_OUTPUT.NEW_LINE;
--VALID
4) What is the Anchored declarations in PL/SQL ?
Variables that directly maps to a column definition in the database
SQL> DECLARE
1 -- variablename TABLENAME.COLUMNNAME%TYPE; --Syntax
2 v_itemcode ITEM.ITEMCODE%TYPE;
. . . .
. . . .
Rules
- Same datatype will be applied as it is in Database.
- Change of column precision or datatype of ITEMCODE in the database, would in turn change v_itemcode automatically as the variable is anchored
- Convenient way of associating PL/SQL variable with database column definitions
- NOT NULL constraint or CHECK constraint associated with table column would not be applicable to PL/SQL variable
Usage 2: is to declare variables that directly maps to a datatype of previously declared variable
SQL> DECLARE
2 v_quantityonhand NUMBER(7) NOT NULL:= 500;
3 v_reorderqty v_quantityonhand%TYPE :=50;
. . . .
. . . .
Rules
1.The datatype of v_quantityonhand and NOT NULL constraint is applied to v_reorderqty.
2.Value of v_quantityonhand would not be copied to v_reorderqty
5)What do you mean by Bind variables in PL/SQL ?
- Declared in the host environment such as SQL*PLUS
- Used to pass runtime values out of one or more PL/SQL programs to the host environment
- PRINT command helps us in displaying the value of one bind variable
- Would be alive only in the current session
SET SERVEROUTPUT ON
VARIABLE g_itemid varchar2(20);
BEGIN
:g_itemid :='&itemid';
END;
/
PL/SQL procedure successfully completed.
SQL> PRINT g_itemid
G_ITEMID
----------------
STN001
6) What is the unconstitutional variable in PL/SQL ?
- Declared in the host environment such as SQL*PLUS
- Used to pass runtime values into one or more PL/SQL programs
- You will not be prompted to enter value during execution of the block
- Would be alive only in the current session
Example:
SET SERVEROUTPUT ON
DEFINE g_itemid = "PEN";
DECLARE
v_itemname VARCHAR2(30);
BEGIN
v_itemname :='&g_itemid';
DBMS_OUTPUT.PUT_LINE(v_itemname);
END;
/
PEN
PL/SQL procedure successfully completed.
7) How to accept user input in PL/SQL ?
--Accepting input in PL/SQL
1.Declare the variables in the declaration section of PL/SQL block
2.Accept the value for variables in the executable block
3.Display the accepted values as shown below
SET SERVEROUTPUT ON
DECLARE
v_itemname VARCHAR2(30);
BEGIN
v_itemname :='&v_item';
DBMS_OUTPUT.PUT_LINE(v_itemname);
END;
/
Enter value for v_item:
Note :
PL/SQL is not interactive. Please follow the code snippet to understand the same.
DECLARE
v_customername VARCHAR2(20);
v_qtyrequired NUMBER;
BEGIN
v_customername := '&v_customername';
DBMS_OUTPUT.PUT_LINE('Customer Name : '||v_customername);
v_qtyrequired := &v_qtyrequired;
DBMS_OUTPUT.PUT_LINE('Required Qty : '||v_qtyrequired);
END;
/
Enter value for v_customername: JAMES
old 5: v_customername := '&v_customername';
new 5: v_customername := 'JAMES';
Enter value for v_qtyrequired: 20
old 7: v_qtyrequired := &v_qtyrequired;
new 7: v_qtyrequired := 20;
Customer Name : JAMES
Required Qty : 20
Rules
- While executing the above PL/SQL block, the system would ask us to enter the customer name and quantity required both, only after which it will
- display the entered customer name and quantity required details. Even though there is a presence of DBMS_OUTPUT.PUT_LINE statement immediately
- after accepting the customer name, the system would not do so. Please be aware of this behavior.
8) What is the SET VERIFY ON/OFF in PL/SQL ?
Prints two lines for every substitution performed within a PL/SQL block
ON --> Displays the substitution performed (Default)
OFF--> Suppress the display of substitution performed
Example:
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
v_itemname VARCHAR2(30);
BEGIN
v_itemname :='&v_item';
DBMS_OUTPUT.PUT_LINE(v_itemname);
END;
/
Enter value for v_item: STN001
STN001
PL/SQL procedure successfully completed.
9) What are the operators in the PL/SQL ?
1.Concatenation Operator ( || )
2.Arithmetic Operators( +, -, *, /,**)
3.Relational Operators( =, !=, <, >, <=, >=)
4.Logical Operators (AND, OR and NOT)
Concatenation Operator
|| is the concatenation operator
Concatenates two or more strings together
Do not use || instead of Logical operator OR
DECLARE
v_customername varchar2(10):='John';
BEGIN
v_customername := v_customername || '10';
DBMS_OUTPUT.PUT_LINE('value of v_customername : '|| v_customername);
END;
Output:
Value of v_customername : John10
Arithmetic Operator - Addition
DECLARE
v_reorderlevel NUMBER;
BEGIN
v_reorderlevel := v_reorderlevel+10; -- NULL +10
DBMS_OUTPUT.PUT_LINE('value of v_reorderlevel : '|| v_reorderlevel);
END;
Value of v_reorderlevel : (NULL)
--Addition of NULL with any number is NULL
--Only numeric and date data types can be used along with arithmetic operators
Arithmetic Operator - Exponentiation
** is the exponential operator
DECLARE
v_number NUMBER:=2;
BEGIN
v_number:=v_number **2;
DBMS_OUTPUT.PUT_LINE( 'value of v_number : '|| v_number);
END;
value of v_number : 4
Arithmetic operator With Date
Arithmetic operators can be used with date
SET SERVEROUTPUT ON
DECLARE
v_today DATE := '10-MAR-2009';
v_tomorrow DATE;
BEGIN
v_tomorrow := v_today + 1;
DBMS_OUTPUT.PUT_LINE('Tomorrow''s date is '||v_tomorrow);
END;
10) what is Nested PL/SQL blocks and what are the rules of using?
1.A PL/SQL block defined within another PL/SQL block is called nested PL/SQL block.
2.Can be nested in the executable section or in exception handling section
3.One or more nested blocks can be present within an anonymous PL/SQL block
4.Overlapping of nested blocks are not allowed
Example:
DECLARE
--declaration of variables in the enclosed block
BEGIN
--SQL and PL/SQL statement(s)
DECLARE
--- declaration of variables in the nested block
BEGIN
-- SQL & PL/SQL statement(s) in nested block
END;
DECLARE
-- declaration of variables in the nested block
BEGIN
-- SQL & PL/SQL statement(s) in nested block
END;
--SQL and PL/SQL statement(s)
END;
Example2:
DECLARE
--declaration of variables in the enclosed block
BEGIN
--SQL and PL/SQL statement(s)
DECLARE
-- declaration of variables in the nested block
BEGIN
-- SQL & PL/SQL statement(s) in nested block
DECLARE
-- declaration of variables
BEGIN
-- SQL and PL/SQL statement(s)
END;
END;
--SQL and PL/SQL statement(s)
END;
Example3:
DECLARE
--declaration of variables in the enclosed block
BEGIN
--SQL and PL/SQL statement(s)
DECLARE
-- declaration of variables in the nested block
BEGIN
-- SQL & PL/SQL statement(s) in nested block
DECLARE
-- declaration of variables
END;
BEGIN
-- SQL and PL/SQL statement(s)
END;
--SQL and PL/SQL statement(s)
END;
Overlapping of PL/SQL blocks are not allowed. The above is an example of the same.
11) Scope of variables
- Variables declared in the DECLARE section would be visible in the EXECUTABLE section and EXCEPTION section
- Lifetime of variables declared in the nested block will be only within the nested block
- Variables declared in the outermost block are visible in all the nested blocks
Example1:
SET SERVEROUTPUT ON;
DECLARE
v_qoh NUMBER:=10;
BEGIN
DECLARE
v_price NUMBER :=20;
BEGIN
DBMS_OUTPUT.PUT_LINE('The value of v_qoh: '||v_qoh);
DBMS_OUTPUT.PUT_LINE('The value of v_price :'||v_price);
END;
DBMS_OUTPUT.PUT_LINE('The value of v_qoh: '||v_qoh);
--DBMS_OUTPUT.PUT_LINE('The value of v_price :'||v_price);
END;
/
Example2:
DECLARE
v_itemid NUMBER(4) := 1001;
BEGIN
DECLARE
v_itemid NUMBER(4) := 1002;
BEGIN
DBMS_OUTPUT.PUT_LINE( v_itemid);
END;
DECLARE
v_quantityonhand NUMBER(4):=300;
BEGIN
DBMS_OUTPUT.PUT_LINE( v_itemid);
END;
DBMS_OUTPUT.PUT_LINE(v_itemid);
END;
/*
1002
1001
1001
*/
Note:
v_itemid with value 1001 declared in the outermost block is visible in both the nested blocks
But another v_itemid with value 1002 is declared in the first nested block with similar name as that of outer block
As local variable takes higher precedence within any block only v_itemid with value 1002 would be visible in the first nested block
12)What is the qualifying identifier in PL/SQL ?
Anonymous PL/SQL blocks can be qualified with identifiers (or names). While qualifying use << and >> angle brackets to enclose the identifiers.
If the variable name is same then we can refer each variable with the help of the labels
<<supplier>>
DECLARE
v_price Number:=10;
BEGIN
<<shopkeeper>>
DECLARE
v_price Number:=20;
BEGIN
DBMS_OUTPUT.PUT_LINE('Supplier Price: '||supplier.v_price);
DBMS_OUTPUT.PUT_LINE('Shopkeeper Price: '||shopkeeper.v_price);
END;
DBMS_OUTPUT.PUT_LINE('Supplier Price: '||supplier.v_price);
--DBMS_OUTPUT.PUT_LINE('Shopkeeper Price: '||shopkeeper.v_price);--this variable will not be visible here
END;
13) IF conditions in the PL/SQL Block.
Condition can be any expression,variable,constant, or identifier compared to any other expression, variable, constant, or identifier.
The condition must evaluate to TRUE, FALSE or NULL
--IF-THEN
IF condition
THEN
action;
END IF;
Example:
DECLARE
v_qoh NUMBER :=10;
v_itemrequired NUMBER :=&b_itemrequired;
BEGIN
IF v_itemrequired>v_qoh THEN
DBMS_OUTPUT.PUT_LINE('Item not available ');
END IF;
END;
Enter value for b_itemrequired: 11
old 3: v_itemrequired NUMBER :=&b_itemrequired;
new 3: v_itemrequired NUMBER :=11;
Item not available
--IF-THEN-ELSE
IF condition
THEN
action;
ELSE
action;
END IF;
Example:
DECLARE --Comparing NUMBER datatypes
v_qoh NUMBER :=10;
v_itemrequired NUMBER :=&b_itemrequired;
BEGIN
IF v_itemrequired > v_qoh THEN
DBMS_OUTPUT.PUT_LINE('Item not available ');
ELSE
DBMS_OUTPUT.PUT_LINE('Item available ');
END IF;
END;
Enter value for b_itemrequired: 9
old 3: v_itemrequired NUMBER :=&b_itemrequired;
new 3: v_itemrequired NUMBER :=9;
Item available
--IF-THEN-ELSE
Inequality of 2 given numbers or dissimilarity of strings can be checked with either != or <>
SET SERVEROUTPUT ON
DECLARE --Comparing VARCHAR2 datatypes
v_string1 VARCHAR2(10) := 'hello';
v_string2 VARCHAR2(10) := 'hello';
BEGIN
IF v_string1 <> v_string2 THEN
DBMS_OUTPUT.PUT_LINE('Both are unequal');
ELSE
DBMS_OUTPUT.PUT_LINE('Both are equal');
END IF;
END;
OP:Both are equal
--IF-THEN-ELSIF
IF condition
THEN
action;
ELSIF condition
THEN
action;
[ELSE
action;]
END IF;
Example:
DECLARE
v_qoh NUMBER :=10;
v_itemrequired NUMBER :=&b_itemrequired;
BEGIN
IF v_itemrequired>v_qoh THEN
DBMS_OUTPUT.PUT_LINE('Item not available ');
ELSIF v_itemrequired=10 THEN
DBMS_OUTPUT.PUT_LINE('Item available but no more stock ');
ELSE
DBMS_OUTPUT.PUT_LINE('Item not available at all');
END IF;
END;
Note:
For every IF condition there must be END IF.
If we are using ELSE IF condtion.. Then for every IF we are using we have to use END IF.
The advantage of using ELSIF is, we can close all the IF s with one END IF .
Please note the spelling of ELSIF.
14) give some examples of loops in PL/SQL.
LOOP
action
END LOOP;
Example:
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('I AM IN LOOP!!!');
END LOOP;
END;
DECLARE
v_price NUMBER:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
v_price:=v_price+1;
EXIT WHEN v_price >5;
END LOOP;
END;
Price: 1
Price: 2
Price: 3
Price: 4
Price: 5
--Numeric FOR Loop
FOR counter IN low_number .. high_number
LOOP
action;
END LOOP;
BEGIN
FOR v_price IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
END LOOP;
END;
OUTPUT:
---------
Price: 1
Price: 2
Price: 3
Price: 4
Price: 5
The variable is automatically declared and initialized
What happens if we declare and initialize the same variable?
--Numeric FOR Loop REVERSE
FOR counter IN REVERSE low_number .. high_number
LOOP
action;
END LOOP;
BEGIN
FOR v_price IN REVERSE 1..5
LOOP
DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
END LOOP;
END;
OUTPUT:
---------
Price: 5
Price: 4
Price: 3
Price: 2
Price: 1
The variable is automatically declared and initialized
What happens if we declare and initialize the same variable, which was used in the FOR loop?
--WHILE condition
LOOP
action;
END LOOP;
Example:
DECLARE
v_price NUMBER:=1;
BEGIN
WHILE v_price <=5
LOOP
DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
v_price:=v_price+1;
END LOOP;
END;
OUTPUT:
---------
Price: 1
Price: 2
Price: 3
Price: 4
Price: 5
15) What are the coding standards in PL/SQL Block ?
v_variable for variables
Example: v_itemname, v_productname, v_employeeno
c_constant for constants
Example: c_discount
e_Exception for user-defined exceptions (Discussed later)
Example: e_Invalid_ItemId, e_Invalid_EmpId
In PL/SQL
cur_cursorname for cursor variable (Discussed later)
Example: cur_branch, cur_emp
All table names and column names in lower case
Example: emp, employeename
All Keywords should be in UPPER case
Example: SELECT, FROM, WHERE, GROUP BY, ORDER BY
Usage: SELECT ename FROM emp WHERE empno=v_empno;
