Blocks :
=====
Block is a grouped code or set of statements and these blocks are classified into two types.
- Anonymous blocks
- Sub programs
==============
- Anonymous blocks are those blocks which we can’t store permanently in the database. Once the user log off from the database these blocks are completely vanished or destroyed.
- These blocks don’t have any proper name that’s why these blocks can be called as unnamed PL/SQL blocks.
==========
- Sub programs are those blocks which we can store permanently as an object. So that at any particular point of time the end user can access these blocks.
- These blocks saved with a proper name with in the database that’s why these blocks can be called as named PL/SQL blocks.
PL/SQL block contains four sections
- Declare
- Begin
- Exception
- end
Basic PL/SQL block structure
SQL> declare
-------
// declare variables used to write PL/SQL code
-------
begin
Statement-1
|
|
|
Statement-n
end;
SQL> declare
-------
// declare variables used to write PL/SQL code
-------
begin
Statement-1
|
|
|
Statement-n
end;
Example 1:write a PL/SQL block to display www.TechsNib.com
SQL> begin
dbms_output.put_line(‘www.TechsNib.com’);
end;
SQL>/
PL/SQL Procedure successfully completed
SQL> set serveroutput on
www.TechsNib.com
Note:
Basically the use of SET SERVEROUTPUT is to display the query answer in SQL *PLUS interface.
When you use the DBMS_OUTPUT.PUT_LINE procedure, the procedure will write the passing string into the Oracle buffer. In order to print the content of the Oracle buffer, you should use the SET SERVEROUTPUT command to display the content of the Oracle buffer into your screen. You can also increase the size of the buffer.
Use the “Set serveroutput on” to display the buffer used by dbms_output.
Basically the use of SET SERVEROUTPUT is to display the query answer in SQL *PLUS interface.
When you use the DBMS_OUTPUT.PUT_LINE procedure, the procedure will write the passing string into the Oracle buffer. In order to print the content of the Oracle buffer, you should use the SET SERVEROUTPUT command to display the content of the Oracle buffer into your screen. You can also increase the size of the buffer.
Use the “Set serveroutput on” to display the buffer used by dbms_output.
Example 2: Write a PL/SQL block input two numbers and find the sum.
SQL> declare
a number:=5;
b number:=10;
begin
dbms_output.put_line(‘sum is’ || (a+b));
end;
a number:=5;
b number:=10;
begin
dbms_output.put_line(‘sum is’ || (a+b));
end;
SQL> declare
a number:=&a;
b number:=&b;
begin
dbms_output.put_line(‘sum is’ || (a+b));
end;
a number:=&a;
b number:=&b;
begin
dbms_output.put_line(‘sum is’ || (a+b));
end;
Note: '&' is used to provide runtime values to variables.
Example 3: Write a PL/SQL block input two numbers and interchange the values.
SQL> declare
a number:=&a;
b number:=&b;
c number;
begin
c:=a; -- b:=a-b; -- a:=a+b; //'--' is used to write the comments in PL/SQL Program
a:=b; -- a:=a-b; -- b:=a-b;
b:=c; -- b:=a+b; -- a:=a-b;
dbms_output.put_line(‘a value is’ || a);
dbms_output.put_line(‘b value is’ || b);
end;
a number:=&a;
b number:=&b;
c number;
begin
c:=a; -- b:=a-b; -- a:=a+b; //'--' is used to write the comments in PL/SQL Program
a:=b; -- a:=a-b; -- b:=a-b;
b:=c; -- b:=a+b; -- a:=a-b;
dbms_output.put_line(‘a value is’ || a);
dbms_output.put_line(‘b value is’ || b);
end;
![]() | ![]() | ![]() |
No comments:
Post a Comment