• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

PL/SQL CONCEPTS

Oracle added a procedural programming language known, as PL/SQL (Procedural Language/SQL). PL/SQL is a third generation language and contains the standard programming constructs.
Anonymous Block: An Unnamed block which will not store any where in the database is know as Anonymous block.
Block Structure: PL/SQL programs are divided up into structures known as blocks, with each block containing the PL/SQL and SQL statements. The syntax for the structure is given below.
Syntax: [DECLARE Variable declaration
Begin
Executable_statements
[Exception
Exception_handling_statements]
End;
Ex:  SQL> SET SERVEROUT ON;
SQL> Declare
v_1 number;
v_2 number;
v_3 number;
Begin
v_1:=&v_1;
v_2:=&v_2;
v_3:=&v_3;
if v_1 < v_2 and v_1 < v_3 then dbms_output.put_line(‘1st is small’);
elsif v_2 < v_3 then dbms_output.put_line(‘2nd is small’);
else dbms_output.put_line(‘3rd is small’);
end if;
Exception
When zero_divide then DBMS_OUTPUT.PUT_LINE(‘Division by ZERO’);
End;
O/p: It will ask for the three input values and will display the smallest number in that.

Difference between PL/SQL and SQL: In PL/SQL there is no buffer to hold the values so we pass into variables. Here in SQL there is buffer to hold the data temporarily.
Ex: SQL> Select ename from emp where is empno = 7788;
SQL> Declare V_name varchar2(10);
Begin
Select ename into v_name from emp where empno = 7788;
End;

Cursors: Cursor is a private SQL area provided by the oracle engine. It is mainly used to retrieve the data from more than one column. There are two types of cursors they are given below.

           Implicit Cursors
           Explicit Cursors

Implicit Cursors: Implicit cursor raises implicitly when we use INSERT, UPDATE, DELETE and SELECT..INTO statements. Because the Implicit cursor is opened and closed by the PL/SQL engine.
Ex: SQL> declare
v_edata emp%ROWTYPE;
begin
select * into v_edata from emp where empno=7788;
if sql%notfound then  dbms_output.put_line(‘Record Not Found’);
else dbms_output.put_line(‘Record Found’);
end if;
end;

Processing Explicit Cursors: The four PL/SQL steps necessary for explicit cursor processing are as follows.
        Declare the cursor.
         Open the cursor for a Query.
         Fetch the results into PL/SQL variables.
         Close the Cursor.

Declaration of Cursor: The declaration of the cursor is associated with the select statement. The syntax for the cursor is given below.
Syntax: CURSOR cursor_name IS select_statement;
Ex: — To print the Cumulative SAL of the Employee table order by empno
SQL>declare
v_sal number;
v_dno number;
l_count number;
cursor test_cursor is select * from scott.emp order by empno;
begin
v_sal:=0;
v_dno:=0;
for i in test_cursor
loop
if(i.deptno!=v_dno) then
v_sal:= 0;
end if;
v_sal:=v_sal+i.sal;
dbms_output.put_line(‘Ename: ‘ || i.ename||’ Sal: ‘|| i.sal || ‘_
Cum_sal: ‘|| v_sal || ‘Deptno: ‘ || i.deptno);
l_count:=test_cursor%rowcount;
end loop;
dbms_output.put_line(l_count||’ Row(s) Fetched by the Cursor’);
end test_cursor;

Cursor Attributes: There are four cursors available in PL/SQL that can be applied to cursors. Those are given below.
         %FOUND
         %NOTFOUND
         %ISOPEN
          %ROWCOUNT

%FOUND:  %FOUND is a Boolean attribute. It returns TRUE if the previous FETCH returned a row and FALSE if it didn’t.

%NOTFOUND:  %NOTFOUND is also a Boolean attribute. It returns FALSE if the previous FETCH returned a row and TRUE if it didn’t. It behaves opposite to the %FOUND.

%ISOPEN:  %ISOPEN is a Boolean attribute. It returns TRUE if the associated cursor is open other wise it will return FALSE.
%ROWCOUNT: %ROWCOUNT is a Numeric attribute. It returns number of rows returned by the cursor so far.

Declaring, Opening Fetching and Closing the Cursor:
Ex: SQL> declare
v_eno number;
v_ename varchar2(20);
l_count number;
cursor razia_cursor is select empno,ename from scott.emp;
begin
open razia_cursor;
loop
fetch razia_cursor into v_eno, v_ename;
exit when razia_cursor%notfound;
l_count:=razia_cursor%rowcount;
dbms_output.put_line(‘Ename: ‘ || v_ename||’ ENUM: ‘|| v_eno);
end loop;
close razia_cursor;
dbms_output.put_line(l_count||’ Row(s) Fetched by the Cursor’);
end;
Ex: SQL> — Example to print the odd rows in the Table EMP
declare
n number;
l_count number;
cursor razia_cursor is select empno,ename,rownum from scott.emp;
begin
for i in razia_cursor
loop
n:=mod(i.rownum,2);
if (n>0) then
dbms_output.put_line(‘Empno: ‘ || i.empno||’ NAME: ‘|| i.ename||’
ROWNUM: ‘|| i.Rownum);
end if;
l_count:=razia_cursor%rowcount;
end loop;
dbms_output.put_line(l_count||’ Number of Row(s) Fetched by the Cursor’);
end razia_cursor;

Exceptions: An Exception is a error handling statement. It is used when ever our program terminates abnormally by using the exception we can execute the program from abnormal termination to normal termination. There are mainly two types of exceptions they are given below.
                   Predefined Exceptions.
                   User defined Exceptions.

Predefined Exceptions: Oracle has predefined several exceptions that correspond to the most common oracle errors. Some predefined exceptions are given below.
LOGIN_DENIED
NO_DATA_FOUND
ZERO_DIVIDE
TOO_MANY_ROWS
STORAGE_ERROR
ROWTYPE_MISMATCH
INVALID_CURSOR
CURSOR_ALREADY_OPEN
Ex: SQL> declare
cursor test_cursor is select e.ename, d.deptno, d. dname from scott.emp e, scott.dept d where e.deptno=20 and                                            d.dname=’RESEARCH’;
begin
for i in test_cursor loop
dbms_output.put_line(i.ename || ‘ ‘ || i.deptno ||’ ‘|| i.dname);
end loop;
exception
when no_data_found then
dbms_output.put_line(‘NO DATA FOUND EXCEPTION RAISED’);
when others then
dbms_output.put_line(‘ An Error Raised ‘ || sqlerrm);
end test_cursor;

User Defined Exceptions: A user defined exception is an error defined by the programmer. User defined exceptions are declared in the declarative section of the
PL/SQL block just like variables.
Syntax: DECLARE E_myexception EXCEPTION;
Ex: SQL> create or replace procedure razia_proc(v_sal in number) as
v_sl number;
razia_excep exception;
begin
if(v_sal >= 5000)
then
raise razia_excep;
else
select sal into v_sl from emp order by deptno;
end if;
exception
when razia_excep then
dbms_output.put_line( ‘YOU HAVE PERFORMED AN ILLEGAL OPERATION’);
end razia_proc;

Procedures: Procedures are also known as subprograms. Given below is the syntax for the creation of the procedure.
Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument [{IN | OUT | IN OUT}] type,

[(argument [{IN | OUT | IN OUT}] type) ] {IS | AS}
BEGIN
procedure_body
END procedure_name;
Ex: SQL> create or replace procedure razia_proc as
v_sal number;
cursor razia_cursor is select * from emp order by deptno;
begin
v_sal := &v_s;
for i in razia_cursor
loop
if(i.sal > v_sal) then
dbms_output.put_line( ‘Employee Name: ‘ || i.ename);
end if;
end loop;
exception
when others then
dbms_output.put_line( ‘YOU HAVE PERFORMED AN ILLEGAL OPERATION’);
dbms_output.put_line( ‘THE PROGRAM MAY TERMINATE NOW’);
end razia_proc;
Execution: exec razia_proc;

Passing IN Parameter to the Procedures:
Ex: SQL> create or replace procedure kanthi_proc(p_inpar in number) as
v_name varchar2(30);
begin
select ename into v_name from emp where empno=p_inpar;
dbms_output.put_line( ‘Employee Name: ‘ || v_name);
exception
when others then
dbms_output.put_line( ‘YOU HAVE PERFORMED ILLEGAL OPERATION’);
end kanthi_proc;

 Using IN and OUT parameters to Procedures:
Ex: SQL> create or replace procedure
shabbir_proc(p_outpar out varchar2,p_inpar in number) as
begin
select ename into p_outpar from emp where empno=p_inpar;
end;
–To Catch the Output variable out side the procedure in the SQL
declare
v_name varchar2(20);
v_num number;
begin
mypro(v_name,&v_num);
dbms_output.put_line( ‘Employee Name: ‘ || v_name);
end shabbir_proc;

Functions: Function is similar to a procedure except that a function must return a value to the statement from which it is called. The syntax for creating a function is given below.
Syntax: SQL> CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name[IN | OUT | IN OUT] type [,….])]
RETURN type;
{IS | AS}
BEGIN
Function_body
END function_name;
Ex: create or replace function haritha_func(p_empno number)
return varchar2 is
v_ename varchar2(20);
v_sal number;
begin
select ename,sal into v_ename, v_sal from emp where empno=p_empno;
if v_sal>=6000 then
return ‘TRUE’;
else
return ‘FALSE’;
end if;
end haritha_func;
Execution: SQL> select haritha_func(7788) from dual;
Ex: SQL> create or replace function haritha_fun(p_radius number)
return number as
v_pi number := 3.141;
v_area number;
begin
v_area := v_pi * POWER(p_radius,2);
return v_area;
end haritha_fun;
Execution: SQL> select haritha_fun(7) from dual;
Dropping Function and Procedure: When we want to drop the function or procedure then we use this DROP statement the syntax for the DROP is given below.
Syntax for Dropping Function:
DROP FUNCTION function_name;
Syntax for Dropping Procedure:
DROP PROCEDURE procedure_name;

Packages: Package is declarative part of the functions and procedures which are stored
in that package. There are two blocks in defining a package.
             Package Specification
             Package body
Package Specification: The package specification contains information about the contents of the package. It does not contain code for any subprograms.
Syntax: CREATE [OR REPLACE] PACKAGE package_name {IS | AS}
Type_definition |
Procedure_specification |
Function_specification |
Variable_declaration |
Exception_declaration |
Cursor_declaration |
Pragma_declaration
END [package_name];
Package Body: The package body is separate data dictionary object from the package header. It cannot be successfully compiled unless the package header is already been successfully compiled.
Syntax: CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}
….
BEGIN
Initialization_code;
END package_name;
Ex: SQL> create or replace package vamsi_pack as
procedure razia_proc(mynum in number);
end vamsi_pack;
— Declaration of Package Body and passing value to the procedure
create or replace package body vamsi_pack as
procedure razia_proc(mynum in number) as
cursor mycursor is select ename,empno,sal from emp where empno=mynum;
begin
dbms_output.put_line(‘ NAME ‘ || ‘ NUMBER ‘ || ‘ SALARY ‘);
for i in mycursor
loop
dbms_output.put_line(‘ ‘||i.ename||’ ‘|| i.empno ||’ ‘|| i.sal);
end loop;
exception
when others then
dbms_output.put_line(‘YOU HAVE DONE AN ILLEGAL OPERATION ‘);
end myproc;
end vamsi_pack;
Execution: SQL> exec vamsi_pack.razia_proc(7788);

Triggers: A Trigger is a procedure that will fire automatically by the database. When a specified DML statement is run against the specified table. Triggers are useful for doing things like advanced auditing of changes made to a column values in a table. The syntax
for creating the trigger is given below.
Syntax: CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} trigger_event
ON table_name
[FOR EACH ROW [WHEN trigger_condition]]
BEGIN
Trigger_body;
END;
Ex: This example is when we insert into a table t1 then the same data has to be inserted into the table t2. For that we require two tables of one same column.
SQL> CREATE TABLE t1( Eno number);
SQL> CREATE TABLE t2(Eno number,Ins_date date);
SQL> CREATE OR REPLACE TRIGGER razia_trigger
BEFORE INSERT ON TABLE t1
FOR EACH ROW
BEGIN
INSERT INTO TABLE t2
VALUES(:new.eno,sysdate);
END;
Note: For every insert operation on t1 it will insert the eno col values to t2 along with the current sysdate in the other column.

Disabling and Enabling Trigger: We can stop the trigger from firing by disabling the trigger by using the ALTER TRIGGER statement. The syntax for enabling the trigger and disabling the trigger is given below.
Syntax: ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;

Dropping a Trigger: If we want to drop the trigger from the database then we have to
use DROP TRIGGER statement. The syntax for dropping the trigger is given below.
Syntax: DROP TRIGGER trigger_name;

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on PL/SQL CONCEPTS"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.