Sunday, January 14, 2018
Oracle PLSQL Tutorials For Beginners 5 : PLSQL Cursors , Implicit Cursor...
/*****************************************************************************
PLSQL Cursors & Types.
------------------------
1) Implicit Cursors
2) Explicit Cursors
Cursor Atributes.
--------------------
1) %FOUND
2) %NOTFOUND
3) %ISOPEN
4) %ROWCOUNT
Explicit Cursors Process:
-----------------
1) DECLARE - Cursor
2) OPEN - Cursor
3) FETCH - Cursor
4) CLOSE - Cursor
Declaring the cursor for initializing the memory
Opening the cursor for allocating the memory
Fetching the cursor for retrieving the data
Closing the cursor to release the allocated memory
******************************************************************************/
-- Implecit Cursor Attributes....
select * from emp;
-- SQL%ROWCOUNT
DECLARE
l_vc_name emp.ename%type;
l_vc_sal emp.sal%type;
BEGIN
SELECT ENAME, SAL
INTO l_vc_name, l_vc_SAL
FROM EMP
WHERE empno=7566;
dbms_output.put_line('Number of rows processed: '||sql%rowcount);
END;
/
--- SQL%FOUND, SQL%NOTFOUND & SQL%ROWCOUNT
DECLARE
v_total_Updated_rows number(2);
BEGIN
UPDATE emp
SET sal = sal + 500;
--- Checking Implicit Cursor Data %NOTFOUND Attribute..
IF sql%notfound THEN
dbms_output.put_line('None of the Employee Salaries Updated');
-- Checking Implicit Cursor Data using %FOUND Attribute..
ELSIF sql%found THEN
-- Getting Row Count using %ROWCOUNT Attribute..
v_total_Updated_rows := sql%rowcount;
dbms_output.put_line( v_total_Updated_rows || ' Total No.Of Employee Salaries Updated ');
END IF;
commit;
END;
/
/*****************************************************************************
Explicit Cursor Example....
*****************************************************************************/
CREATE OR REPLACE PROCEDURE Get_emp_names_Proc (Dept_num IN NUMBER) IS
Emp_name VARCHAR2(10);
V_Total_Employees NUMBER(10);
CURSOR c1 (Depno NUMBER) IS
SELECT Ename FROM Emp
WHERE deptno = Depno;
BEGIN
OPEN c1(Dept_num);
LOOP
FETCH c1 INTO Emp_name;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Emp_name);
END LOOP;
V_Total_employees:=c1%rowcount;
DBMS_OUTPUT.PUT_LINE('Total Employees for Dept 10 :: '||V_Total_employees);
CLOSE c1;
END;
/
select * from emp;
exec Get_emp_names_Proc(10);
Oracle PLSQL Tutorials for Beginners 3 : Oracle PLSQL Procedure with ex...
/******************************************************************************
SAMPLE PLSQL PROCEDURE
=> IN Parameter -- Passed By Reference
=> OUT Parameter -- Passed By Value
=> IN OUT Parameter -- Passed By Value
=> Position Based Parameters Notation.
=> Named Based Parameters Notation. (=>) Arrow Symbol
=> Mixed Parameters Notaion.
=> NOCOPY Clause for changing Pass by value to reference.
******************************************************************************/
CREATE OR REPLACE PROCEDURE First_PROC
AS
BEGIN
dbms_output.put_line(' Welcome To First PLSQL Procedure...');
END;
/
exec first_proc;
/*****************************************************************************
IN & OUT Parameter
*****************************************************************************/
DECLARE
V_Num1 number;
V_Num2 number;
V_Num3 number;
PROCEDURE findMax(A IN number, B IN number, C OUT number) IS
BEGIN
IF A > B THEN
C:= A;
ELSE
C:= B;
END IF;
END;
BEGIN
V_Num1:= 12;
V_Num2:= 55;
--- Position based Notation
findMax(V_num1, V_Num2, V_NUM3);
dbms_output.put_line(' Position Notation ::: Maximum Value of (12, 55) Is => ' || V_NUM3);
--- Named Based Notation using Arrow =>
findMax(c=>V_NUM3,b=>V_Num2,a=>V_num1 );
dbms_output.put_line(' Named Notation ::: Maximum Value of (12, 55) Is => ' || V_NUM3);
-- Mixed Notation
findMax( V_num1, c=>V_NUM3, b=>V_Num2);
dbms_output.put_line(' MIXED Notation ::: Maximum Value of (12, 55) Is => ' || V_NUM3);
END;
/
/******************************************************************************
IN OUT Parameter Example
******************************************************************************/
DECLARE
-- Declaring A Variable for Taking output from Procedure..
a number;
-- Declaring Procedure in Declaration Block for IN OUT Parameter....
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
/** Inside Procedure Doing Calculation for Square Root... */
x := x * x;
END;
-- Ending Procedure....
BEGIN
--Declaring A Value for In Put Parameter for Above Proc..
a:= 5;
/** Calling Procedure **/
dbms_output.put_line('Calling PLSQL Procedure here....With IN OUT Parameter');
dbms_output.put_line('...................');
squareNum(a);
/* Printing OUT Parameter Value using DBMS_OUTPUT Package */
dbms_output.put_line(' Square of (5): ' || a);
dbms_output.put_line('...................');
END;
/
/******************************************************************************
Creating Procedure in DB
=> TYPE --- Record Type
=> ROWTYPE -- Collection Type
******************************************************************************/
varchar2(30)
CREATE OR REPLACE PROCEDURE Get_emp_rec_proc (Emp_number IN Emp.Empno%TYPE,
Emp_ret OUT Emp%ROWTYPE) IS
BEGIN
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
INTO Emp_ret
FROM emp
WHERE Empno = Emp_number;
END;
/
/******************************************************************************
Executing Procedure with Parameters....
******************************************************************************/
DECLARE
Emp_row Emp%ROWTYPE; -- declare a record matching a
-- row in the Emp table
BEGIN
Get_emp_rec_proc(7499, Emp_row); -- call for Emp_tab# 7499
DBMS_OUTPUT.PUT(Emp_row.Ename || ' ' || Emp_row.Empno);
DBMS_OUTPUT.PUT(' ' || Emp_row.Job || ' ' || Emp_row.Mgr);
DBMS_OUTPUT.PUT(' ' || Emp_row.Hiredate || ' ' || Emp_row.Sal);
DBMS_OUTPUT.PUT(' ' || Emp_row.Comm || ' '|| Emp_row.Deptno);
DBMS_OUTPUT.NEW_LINE;
END;
/
/******************************************************************************
OUT & IN OUT Parameter WITH NOCOPY Example
******************************************************************************/
create or replace procedure No_Copy_Proc
(PI_Name in varchar2,
PO_Date out nocopy date,
PIO_Status in out nocopy varchar2) is
begin
PO_Date := sysdate;
PIO_Status := PI_name || ' ::: Sample IN OUT Parameter NOCOPY Testing';
end;
/
DECLARE
V_DATE DATE;
V_Status Varchar2(200);
BEGIN
No_Copy_Proc('Raveendra Reddy',V_DATE,V_Status);
dbms_output.put_line(V_DATE);
dbms_output.put_line(V_Status);
END;
Oracle PLSQL Tutorials: PLSQL Functions with OUT Parameter & DML Operati...
/******************************************************************************
PLSQL FUNCTION - SUBPROGRAM....
*******************************************************************************/
-- PLSQL FUNCTION SYNTAX
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
/******************************************************************************
Sample Example For PLSQL Function...
*******************************************************************************/
CREATE OR REPLACE FUNCTION Get_Max_Sal(FI_Deptno IN NUMBER)
RETURN NUMBER
IS
max_sal NUMBER(11,2);
BEGIN
SELECT max(sal)
INTO max_sal
FROM EMP
WHERE deptno = FI_Deptno;
RETURN(max_sal);
END Get_Max_Sal;
/
select * from emp where deptno=20;
select Get_Max_Sal(20) from dual;
select * from emp where deptno=10;
/*****************************************************************************
PLSQL Function without Parameter Example....
*****************************************************************************/
CREATE OR REPLACE FUNCTION Total_employees
RETURN number IS
V_Total number(3) := 0;
BEGIN
SELECT count(*) into V_Total
FROM emp;
RETURN V_Total;
END;
/
select Total_employees from dual;
/*****************************************************************************
Function with OUT Parameter Example..
*****************************************************************************/
CREATE OR REPLACE FUNCTION get_outparam (A IN NUMBER, B IN NUMBER ,C OUT NUMBER)
RETURN NUMBER IS
BEGIN
C := A+B;
RETURN A-B;
END get_outparam;
/
select get_outparam(12,15,A) from dual;
SET serveroutput ON;
DECLARE
A NUMBER:=25;
B NUMBER:=15;
C NUMBER;
D NUMBER;
BEGIN
D:=get_outparam(A,B,C);
DBMS_OUTPUT.put_line('OUT PARAMETER VALUE ::: '||C);
DBMS_OUTPUT.put_line(' FUNCTION RETURN VALUE ::: '||D);
END;
/
/*****************************************************************************
Recursive Function Example..
*****************************************************************************/
DECLARE
num number;
factorial number;
FUNCTION fact(x number)
RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;
BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/
CREATE OR REPLACE FUNCTION EMP_Update(depnt IN NUMBER)
RETURN VARCHAR2 IS
v_max_sal_before number := 0;
v_max_sal_after number := 0;
BEGIN
SELECT max(sal) into v_max_sal_before FROM emp
where deptno =depnt;
update emp
set sal = sal+500
where deptno =depnt;
commit;
SELECT max(sal) into v_max_sal_after FROM emp
where deptno =depnt;
RETURN 'Before Updating :::'||v_max_sal_before||' ::After Update :::'||v_max_sal_after;
END;
/
select * from emp where deptno=10;
select EMP_UPDATE(10) from dual;
DECLARE
V_MAX_SAL VARCHAR2(100);
BEGIN
V_MAX_SAL:= EMP_UPDATE(10);
dbms_output.put_line(V_MAX_SAL);
END;
/
select EMP_Update(10) from dual;
Oracle PLSQL Tutorials For Beginners 2, PLSQL Variables & SubTypes Tu...
/*****************************************************************************
PLSQL DATA TYPES
=> Char
=> Varchar2
=> Date
=> NUMBER ( Integer )
=> Boolean True or Flase
*****************************************************************************/
DECLARE
V_Char char(100):='This is Char';
V_Varchar varchar2(100):='This is Varchar Data Type';
V_Number Number(10):=55555;
V_Date DATE:=SYSDATE;
V_Boolean BOOLEAN:=true;
BEGIN
dbms_output.put_line('Welcome Char Variable : ::: '||V_Char );
dbms_output.put_line('Welcome Varchar Variable : ::: '||V_Varchar );
dbms_output.put_line('Welcome Number Variable : ::: '||V_Number );
dbms_output.put_line('Welcome Date Variable : ::: '||V_DATE );
IF V_Boolean THEN
dbms_output.put_line( 'This is Boolean Variable ::: TRUE');
else
dbms_output.put_line( 'This is Boolean Variable ::: FALSE');
END IF;
END;
/
/*****************************************************************************
PLSQL SUB TYPES
*****************************************************************************/
DECLARE
SUBTYPE name IS char(20);
SUBTYPE address IS varchar2(100);
SUBTYPE phone IS NUMBER(10);
V_MyName name;
V_My_Address address;
V_My_Phone phone;
BEGIN
V_MyName := 'Test Char... ';
V_My_Address := 'Welcome to the PLSQL SubTypes..';
V_My_Phone :=999999999;
dbms_output.put_line('Sub Type Char : ::: ' || V_MyName );
dbms_output.put_line('Sub Type Varchar2 : ::: '|| V_My_Address);
dbms_output.put_line('Sub Type Varchar2 : ::: '|| V_My_Phone);
END;
/
Oracle PLSQL Tutorials for Beginners Introduction To PLSQL
DECLARE
/* this is multi line <declarations section> */
-- this is a single line comment
BEGIN
/* <executable command(s)> */
EXCEPTION
/* <exception handling> */
END;
/
BEGIN
NULL;
END;
/
SET SERVEROUTPUT OFF;
SET SERVEROUTPUT ON;
BEGIN
dbms_output.put_line('This Is First PLSQL Program');
END;
/
DECLARE
V_MyVar varchar2(100):= 'Hello, World! This is First PLSQL Program';
V_Number number:=55;
BEGIN
dbms_output.put_line(V_Myvar);
dbms_output.put_line(V_Number);
END;
/
/******************************************************************************
PL/SQL Program Objects - Execution Units
******************************************************************************/
A PL/SQL unit is any one of the following Objects.......
=> PL/SQL Default block
=> Procedure - Object
=> Function - Object
=> Package - Object
=> Package body - Object
=> Triggers - Object
=> Type - Object
=> Type body -Object
Saturday, January 6, 2018
Subscribe to:
Posts (Atom)