Friday 23 March 2018
Thursday 22 March 2018
Tuesday 20 March 2018
Monday 19 March 2018
Sunday 18 March 2018
Saturday 17 March 2018
Tuesday 13 March 2018
Monday 12 March 2018
Saturday 10 March 2018
Sunday 4 March 2018
Saturday 3 March 2018
Thursday 1 March 2018
Thursday 8 February 2018
Wednesday 7 February 2018
Tuesday 6 February 2018
Monday 5 February 2018
Sunday 4 February 2018
Saturday 3 February 2018
Friday 2 February 2018
Thursday 1 February 2018
Sunday 14 January 2018
PLSQL Difference Between Procedures and Functions in Oracle
/*****************************************************************************
Differences Between PLSQL Procedure & Function..
******************************************************************************/
PROCEDURE:
---------
1) Procedure may or may not return a value
2) Procedure Does not have a RETURN Key work
3) Procedure Cannot be called in SQL Query
4) Procedure Cannot be used in Expressesion like A+B .. A-B..
5) Procedure Can be used for both IN , OUT & INOUT Parameters without
Limitations.
6) Procedure can be done both DRL & DML Operations.
FUNCTION:
---------
1) Function Should Return A Value
2) Function will have RETURN key work to return a value.
3) Function Can be called in SQL Query.
4) Function Can be Used in Expression..
5) Function Can be used only IN Parameter But OUT & INPUT Parameter will be
Used with some limitations.
6) Function Can be used only for DRL (Select), But DML can be allowed with
Some Limitations and it wont be suggested..
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;
Saturday 13 January 2018
Oracle PLSQL Tutorials : PLSQL Explicit Cursors with Examples
/*****************************************************************************
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);
Wednesday 10 January 2018
Saturday 6 January 2018
Subscribe to:
Posts (Atom)