PROCEDURE:
***********
-->Procedure is a named PL/SQL block which perform one or more specific tasks.
-->Procedures are Mainly to perform on acton.
-->Procedure is a database object
-->Procedure is mainly created to perform one or more DML operation over database.
-->Procedure can not have a RETURN clause in its definition or specification.
-->Procedures may or may not return a value.
-->It is not mandatory to return the value.
-->Procedures return morethan one value while using OUT parameters.
-->While defining the procedure we do not specify any return clause in its specification but return statement is optional for inside the procedure.
-->Procedures can not execute in select statement.
-->Procedure internally having one time compilation process.
-->Procedures are used to improved the performance of business transactions.
-->Procedures can promote reusablity and maitainability.
-->Procedure will accept IN,OUT and INOUT parameters.
-->Base Tables: ALL_SOURCE,USER_SOURCE,ALL_OBJECTS,DBA_OBJECTS
-->Procedures will be divided into two parts.
a) Procedure Specification.
-->In procedure specification we are specifying name of the procedure and types of the parameters.
b)Procedure Body
-->In procedure body we are solving the tasks(like objects).
-->Procedures can execute in 2 ways.
a) Anonymous Block
b)Exec
SYNTAX:
*******
CREATE [OR REPLACE] PROCEDURE
<procedure_name>
[
<parameterl IN/OUT/IN OUT <datatype>
]
IS | AS
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END [<procedure_name>];
EX1:
****
CREATE OR REPLACE PROCEDURE P1
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('WELCOME TO ORACLE PL/SQL TRAINING');
END P1;
/
--PROCEDURE EXECUTION
EXEC P1;
/
EX2:
****
CREATE OR REPLACE PROCEDURE P2
IS
A NUMBER :=2;
B NUMBER :=4;
BEGIN
DBMS_OUTPUT.PUT_LINE(A + B);
END P2;
/
--PROCEDURE EXECUTION
EXEC P2;
/
EX3:
****
CREATE OR REPLACE PROCEDURE P3
is
LV_ENAME EMP.ENAME%TYPE;
LV_SAL EMP.SAL%TYPE;
BEGIN
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP
WHERE 1=1
AND EMPNO=7839;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
END P3;;
/
--PROCEDURE EXECUTION
EXEC P3;
/
EX4:
****
CREATE OR REPLACE PROCEDURE P4
IS
CURSOR C1
IS
SELECT *
FROM EMP;
I EMP%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('************PROGRAM STARTED***************'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
OPEN C1;
LOOP
BEGIN
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||','||I.JOB||','||I.SAL);
END;
END LOOP;
CLOSE C1;
DBMS_OUTPUT.PUT_LINE('************PROGRAM ENDED***************'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS :'||SQLERRM);
END ;
/
--PROCEDURE EXECUTION
EXEC P4;
/
CREATE OR REPLACE PROCEDURE P5
IS
LV_CNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO LV_CNT
FROM EMP
WHERE DEPTNO=20;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEES FROM DEPT 20 IS : '|| LV_CNT);
END;
/
--PROCEDURE EXECUTION
EXEC P5;
/
EX6:
****
create or replace procedure p6
--return number
is
lv_ename varchar2(20);
lv_sal number;
lv_empno number;
lv_job varchar2(20);
cursor c1 is select empno,ename,sal,job from emp;
begin
open c1;
loop
fetch c1 into lv_empno,lv_ename,lv_sal,lv_job;
exit when c1%notfound;
dbms_output.put_line(lv_empno||','||lv_ename||','||lv_sal||','||lv_job);
end loop;
close c1;
end;
/
EXEC P6;
/
CREATE OR REPLACE PROCEDURE P6 IS
cursor c1 is select empno,ename,sal,job from emp;
I C1%ROWTYPE; --cursor based record
begin
open c1;
loop
fetch c1 into I;
exit when c1%notfound;
dbms_output.put_line(I.empno||','||I.ename||','||I.sal||','||I.job);
end loop;
close c1;
end;
/
EXEC P6;
/
EX7:
***
--declare
create or replace procedure p7
is
cursor c1 is select * from emp;
i emp%rowtype; --table based record
begin
for i in c1
loop
dbms_output.put_line(i.empno||','||i.ename||','||i.sal);
end loop;
end;
/
EXEC P7;
/
CREATE OR REPLACE PROCEDURE P8
IS
CURSOR C1 IS SELECT * FROM EMP;
LV_CNT NUMBER;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.SAL);
--DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT);
LV_CNT :=C1%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF RECORDS :' ||LV_CNT);
END p8;
/
EXEC P8;
/
CREATE OR REPLACE PROCEDURE P8
IS
CURSOR C1 IS SELECT * FROM EMP;
LV_CNT NUMBER :=0;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.SAL);
--DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT);
--LV_CNT :=C1%ROWCOUNT;
LV_CNT :=LV_CNT+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF RECORDS :' ||LV_CNT);
END p8;
/
EXEC P8;
/
SELECT *
FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'FND%USER%'
/
select *
from fnd_responsibility_tl
SELECT *
FROM FND_USER_RESP_GROUPS_INDIRECT
/
FND_USER
FND_USER_RESP_GROUPS_INDIRECT
fnd_responsibility_tl
/
select user_id from fnd_user;
/
select * from FND_USER_RESP_GROUPS_INDIRECT;
/
select *
from fnd_responsibility_tl;
/
CREATE OR REPLACE PROCEDURE P1
IS
CURSOR USER_CUR
IS
SELECT FU.USER_NAME,FRT.RESPONSIBILITY_NAME
FROM FND_USER FU,
FND_USER_RESP_GROUPS_INDIRECT FURG,
FND_RESPONSIBILITY_TL FRT
WHERE FU.USER_ID=FURG.USER_ID
AND FURG.RESPONSIBILITY_ID=FRT.RESPONSIBILITY_ID
AND FRT.RESPONSIBILITY_NAME='Application Diagnostics';
BEGIN
FOR FU_USER IN USER_CUR
LOOP
DBMS_OUTPUT.PUT_LINE(FU_USER.USER_NAME||','||FU_USER.RESPONSIBILITY_NAME);
END LOOP;
END;
/
exec p1;
/
-- for loop 2nd method
CREATE OR REPLACE PROCEDURE P1
IS
BEGIN
FOR FU_USER IN(SELECT FU.USER_NAME,FRT.RESPONSIBILITY_NAME
FROM FND_USER FU,
FND_USER_RESP_GROUPS_INDIRECT FURG,
FND_RESPONSIBILITY_TL FRT
WHERE FU.USER_ID=FURG.USER_ID
AND FURG.RESPONSIBILITY_ID=FRT.RESPONSIBILITY_ID
AND FRT.RESPONSIBILITY_NAME='Application Diagnostics')
LOOP
DBMS_OUTPUT.PUT_LINE(FU_USER.USER_NAME||','||FU_USER.RESPONSIBILITY_NAME);
END LOOP;
END;
/
EXEC P1;
Parameters in Procedure:
************************
-->Parameters are used to pass the value into procedures and also return values from procedures.
-->In this case we must use two types of parameters.
A)Formal Parameters.
b)Actual Parameters.
A)Formal Parameters:
********************
-->Formal parameters are defined in Procedure Specification.
-->In Formal parameter we are defining parmaeter name and mode of the parameter.
-->There are three types of modes supported by oracle.
1)IN Mode
2)OUT Mode
3)IN OUT Mode
IN Mode:
********
-->By default procedure parameters having IN mode.
-->IN mode is used to pass the values into procedure body.
-->It is used to send values to procedure or function.
-->This mode behaves like a constant in procedure body.
OUT MODE:
*********
-->This mode is used to return the values from procedure body.
-->It is used to get the values from procedure.
IN OUT MODE:
***********
-->This mode is used to pass the values into subprograms and return the values from subprograms.
-->It is used to send values and get values form procedure.
Note:
******
-->If a parameter is not explicitly defined a parameter type,then by default it is an IN type parameter.
/
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO IN NUMBER)
IS
CURSOR C1 IS SELECT EMPNO,ENAME,SAL FROM EMP1 WHERE DEPTNO=P_DEPTNO;
LV_ENAME VARCHAR2(20);
LV_SAL NUMBER;
LV_EMPNO NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('DEPTNO'||' '||P_DEPTNO||' '||'DETAILS');
FOR I IN C1
LOOP
BEGIN
SELECT EMPNO,SAL
INTO LV_EMPNO,LV_SAL
FROM EMP1
WHERE ENAME=I.ENAME;
DBMS_OUTPUT.PUT_LINE(LV_EMPNO||','||LV_SAL);
EXCEPTION
WHEN NO_dATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ENAME SHOULD NOT BE NULL');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('NORETHAN ONE ROW');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
END;
BEGIN
SELECT EMPNO,ENAME
INTO LV_EMPNO,LV_ENAME
FROM EMP1
WHERE SAL=I.SAL;
DBMS_OUTPUT.PUT_LINE(LV_EMPNO||','||LV_ENAME);
EXCEPTION
WHEN NO_dATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('SALARY SHOULD NOT BE NULL');
END;
END LOOP;
END;
/
DROP TABLE XX_PO_DETAILS;
/
CREATE TABLE XX_PO_DETAILS
(
SEGMENT1 VARCHAR2(100),
TYPE_LOOKUP_CODE VARCHAR2(20),
PO_HEADER_ID NUMBER,
VENDOR_ID NUMBER,
CREATION_dATE DATE,
CREATED_BY NUMBER,
LASt_UPDATE_dATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER,
PROCESS_FLAG VARCHAR2(1),
ERROR_MESSAGE VARCHAR2(4000)
);
/
INSERT INTO XX_PO_DETAILS(SEGMENT1,TYPE_LOOKUP_CODE,CREATION_dATE,CREATED_BY,LASt_UPDATE_dATE,LASt_UPDATED_BY,LASt_UPDATE_LOGIN,PROCESS_FLAG)
VALUES
(10000,'STANDARD',SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.USER_ID,'N');
/
INSERT INTO XX_PO_DETAILS(SEGMENT1,TYPE_LOOKUP_CODE,CREATION_dATE,CREATED_BY,LASt_UPDATE_dATE,LASt_UPDATED_BY,LASt_UPDATE_LOGIN,PROCESS_FLAG)
VALUES
(10001,'STANDARD',SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.USER_ID,'N');
/
INSERT INTO XX_PO_DETAILS(SEGMENT1,TYPE_LOOKUP_CODE,CREATION_dATE,CREATED_BY,LASt_UPDATE_dATE,LASt_UPDATED_BY,LASt_UPDATE_LOGIN,PROCESS_FLAG)
VALUES
(500000,'STANDARD',SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.USER_ID,'N');
/
COMMIT;
/
SELECT * FROM XX_PO_DETAILS;
/
CREATE OR REPLACE PROCEDURE P1
IS
--DECLARE
lv_PO_HEADER_ID number;
LV_TYPE_LOOKUP_CODE VARCHAR2(100);
LV_ERROR_NUM NUMBER;
LV_ERROR_MESSAGE VARCHAR2(4000);
LV_TOTAL_COUNT NUMBER :=0;
LV_ERROR_COUNT NUMBER :=0;
LV_SUCCESS_COUNT NUMBER :=0;
cursor c1 is select ROWID ROW_ID,XPD.* from XX_PO_DETAILS XPD WHERE XPD.PROCESS_FLAG='N'; -- STAGING TABLE OR CUSTOM TABLE
begin
for I in c1
loop
LV_TOTAL_COUNT :=LV_TOTAL_COUNT+1;
lv_PO_HEADER_ID :=NULL;
LV_ERROR_NUM :=0;
LV_ERROR_MESSAGE :=NULL;
LV_TYPE_LOOKUP_CODE :=NULL;
BEGIN
SELECT PO_HEADER_ID
INTO lv_PO_HEADER_ID
FROM PO_HEADERS_ALL
WHERE SEGMENT1=I.SEGMENT1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LV_ERROR_NUM :=1;
LV_ERROR_MESSAGE :='PO NUMBER SHOULD NOT BE NULL';
dbms_output.put_line(LV_ERROR_MESSAGE);
WHEN OTHERS THEN
LV_ERROR_NUM :=1;
LV_ERROR_MESSAGE :='UNKNOWN ERROR : '||SQLERRM;
dbms_output.put_line(LV_ERROR_MESSAGE);
END;
IF LV_ERROR_NUM=1 THEN
LV_ERROR_COUNT :=LV_ERROR_COUNT+1;
UPDATE XX_PO_DETAILS
SET PROCESS_FLAG='E',
ERROR_MESSAGE=LV_ERROR_MESSAGE
WHERE ROWID=I.ROW_ID;
ELSE
LV_SUCCESS_COUNT :=LV_SUCCESS_COUNT+1;
UPDATE XX_PO_DETAILS
SET PROCESS_FLAG='S',
PO_HEADER_ID=LV_PO_HEADER_ID
WHERE ROWID=I.ROW_ID;
END IF;
end loop;
COMMIT;
DBMS_OUTPUT.PUT_LINE('TOTAL RECORDS ARE PROCESSED : '||LV_TOTAL_COUNT);
DBMS_OUTPUT.PUT_LINE('SUCCESS RECORDS ARE PROCESSED : '||LV_SUCCESS_COUNT);
DBMS_OUTPUT.PUT_LINE('ERROR RECORDS ARE PROCESSED : '||LV_ERROR_COUNT);
end;
/
EXEC P1;
/
EX8:
****
CREATE OR REPLACE PROCEDURE P8(P_DEPTNO IN NUMBER)
IS
LV_CNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO LV_CNT
FROM EMP
WHERE DEPTNO=P_DEPTNO;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEES FROM DEPTNO '||P_DEPTNO||' IS : '|| LV_CNT);
END;
/
--PROCEDURE EXECUTION FOR IN MODE
EXEC P8(30);
/
EX9:
****
CREATE OR REPLACE PROCEDURE P9(P_JOB IN VARCHAR2)
IS
LV_CNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO LV_CNT
FROM EMP
WHERE JOB=P_JOB;
DBMS_OUTPUT.PUT_LINE('JOB IS : '||P_JOB);
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEES FROM '||P_JOB||' WISE IS: '|| LV_CNT);
END;
/
--PROCEDURE EXECUTION FOR IN MODE
EXEC P9('CLERK');
/
EX10:
*****
-->Procedure calculates the new commision for all employees of a particular department .
create or replace procedure cal_comm
(vdept number) is
cursor c1 is select empno,ename,comm from emp1
where deptno = vdept;
LV_CNT NUMBER :=0;
--i c1%rowtype;
begin
dbms_output.put_line
(' Employ commision Report ');
for i in c1
loop
if i.comm is null then
i.comm := 3000;
elsif i.comm = 0 then
i.comm := 2500;
else
i.comm := i.comm + i.comm * .25;
end if;
update emp1 set comm = i.comm
where empno = i.empno;
dbms_output.put_line(i.empno||' '||i.ename
||' '||i.comm);
LV_CNT :=LV_CNT+1;
end loop;
--commit;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEES COMISSION UPDATED IS : '||LV_CNT);
end;
/
EXEC cal_comm(10);
/
EXEC cal_comm(20);
/
create or replace procedure p1 (p_empno in number,p_amt in number)
is
cursor c1 is select * from emp1 where empno=p_empno;
lv_count number :=0;
begin
for i in c1
loop
lv_count :=lv_count+1;
update emp1
set sal=sal+p_amt
where empno=i.empno;
end loop;
dbms_output.put_line('total number of employees are updated :'||lv_count);
end;
/
exec p1(7839,1000);
/
create or replace procedure p1(p_deptno number)
is
cursor c1 is select * from emp1 where deptno=p_deptno;
lv_count number;
begin
lv_count :=0;
for i in c1
loop
delete from emp1 where empno=i.empno;
lv_count :=lv_count+1;
end loop;
dbms_output.put_line('total records are deleted :'||lv_count);
end;
/
exec p1(30);
/
CREATE OR REPLACE PROCEDURE P1 (P_JOB IN VARCHAR2)
IS
CURSOR C1 IS SELECT ENAME,JOB,SAL FROM EMP WHERE JOB=P_JOB;
LV_ENAME VARCHAR2(20);
LV_JOB VARCHAR2(20);
LV_SAL NUMBER;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO LV_ENAME,LV_JOB,LV_SAL;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ENAME IS :'||LV_ENAME);
DBMS_OUTPUT.PUT_LINE('JOB IS :'||LV_JOB);
DBMS_OUTPUT.PUT_LINE('SAL IS :'||LV_SAL);
--EXCEPTION
--WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS :'||SQLERRM);
END LOOP;
CLOSE C1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS :'||SQLERRM);
END;
/
exec p1('CLERK');
/
CREATE TABLE EMP_INSERT AS SELECT * FROM EMP WHERE 1=2;
/
create or replace procedure p1(p_deptno number)
is
cursor c1 is select * from emp where deptno=p_deptno;
begin
for i in c1
loop
insert into emp_insert
(
empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno
)
values
(
i.empno,
i.ename,
i.job,
i.mgr,
i.hiredate,
i.sal,
i.comm,
i.deptno
);
end loop;
end;
/
exec p1(10);
/
create or replace procedure p1(p_deptno number,p_amt number)
is
cursor c1 is select * from emp where deptno=p_deptno;
begin
for i in c1
loop
update emp1
set sal=i.sal+p_amt
where empno=i.empno;
end loop;
end;
/
select *
from emp1
where deptno=20;
/
rollback;
/
exec p1(20,1500);
/
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO IN NUMBER)
IS
CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=P_DEPTNO;
LV_SUCC_COUNT NUMBER :=0;
LV_ERR_CNT NUMBER :=0;
lv_TOT_count number;
BEGIN
BEGIN
SELECT COUNT(*)
INTO LV_TOT_COUNT
FROM EMP WHERE DEPTNO=P_DEPTNO;
END;
FOR I IN C1
LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME);
--DBMS_OUTPUT.PUT_LINE('TOTAL EMPLOYEES : '||C1%ROWCOUNT);
LV_SUCC_COUNT :=LV_SUCC_COUNT+1;
EXCEPTION
WHEN OTHERS THEN
LV_ERR_CNT :=LV_ERR_CNT+1;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL EMPLOYEES COUNT : '||LV_TOT_COUNT);
DBMS_OUTPUT.PUT_LINE('SUCCESS EMPLOYEES : '||LV_SUCC_COUNT);
DBMS_OUTPUT.PUT_LINE('ERROR EMPLOYEES : '||LV_ERR_CNT);
END;
/
EXEC P1(10);
/
TRUNCATE TABLE XX_PO_HEADERS;
/
--TO TRANSFER PO_HEADERS_ALL TABLE DATA TO XX_PO_HEADERS TABLE
CREATE OR REPLACE PROCEDURE P1(P_TYPE_LOOKUP_CODE VARCHAR2)
IS
CURSOR C1 IS SELECT SEGMENT1,PO_HEADER_ID,AGENT_ID,TYPE_LOOKUP_CODE FROM PO_HEADERS_ALL WHERE TYPE_LOOKUP_CODE=P_TYPE_LOOKUP_CODE;
LV_PO_CNT NUMBER :=0;
BEGIN
FOR I IN C1
LOOP
INSERT INTO XX_PO_HEADERS
(
SEGMENT1,
PO_HEADER_ID,
AGENT_ID,
TYPE_LOOKUP_CODE
)
VALUES
(
I.SEGMENT1,
I.PO_HEADER_ID,
I.AGENT_ID,
I.TYPE_LOOKUP_CODE
);
LV_PO_CNT :=LV_PO_CNT+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(LV_PO_CNT);
END;
/
EXEC P1('STANDARD');
/
EXEC P1('BLANKET');
/
EXEC P1('CONTRACT');
/
--TO UPDATE THE XX_PO_HEADERRS TABLE DATA(STANDARD STD_TEXT, BLANKET BLK_TEXT,CONTRACT CRC_TEXT)
CREATE OR REPLACE PROCEDURE P1(P_TYPE_LOOKUP_CODE IN VARCHAR2,P_VALUE IN VARCHAR2)
IS
CURSOR C1 IS SELECT SEGMENT1,PO_HEADER_ID,AGENT_ID,TYPE_LOOKUP_CODE FROM PO_HEADERS_ALL WHERE TYPE_LOOKUP_CODE=P_TYPE_LOOKUP_CODE;
LV_PO_CNT NUMBER :=0;
BEGIN
FOR I IN C1
LOOP
UPDATE XX_PO_HEADERS
SET TYPE_LOOKUP_CODE=P_VALUE
WHERE TYPE_LOOKUP_CODE=I.TYPE_LOOKUP_CODE;
LV_PO_CNT :=LV_PO_CNT+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(LV_PO_CNT);
END;
/
EXEC P1('STANDARD','STD_TEXT');
/
EXEC P1('BLANKET','BLK_TEXT');
/
EXEC P1('CONTRACT','CRC_TEXT');
/
DROP TABLE XX_EMP2;
/
CREATE TABLE XX_EMP2 AS SELECT * FROM EMP1 WHERE 1=2;
/
CREATE OR REPLACE PROCEDURE P1(P_JOB VARCHAR2)
IS
BEGIN
FOR I IN(SELECT * FROM EMP1 WHERE JOB=P_JOB)
LOOP
INSERT INTO XX_EMP2
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
VALUES
(
I.EMPNO,
I.ENAME,
I.JOB,
I.MGR,
I.HIREDATE,
I.SAL,
I.COMM,
I.DEPTNO
);
END LOOP;
END;
/
EXEC P1('CLERK');
/
BEGIN
P1('MANAGER');
END;
/
SELECT * FROM XX_EMP2;
/
CREATE OR REPLACE PROCEDURE P1
IS
BEGIN
FOR I IN (SELECT * FROM XX_EMP2)
LOOP
DELETE FROM XX_EMP2 WHERE EMPNO=I.EMPNO;
END LOOP;
END;
/
SELECT * FROM XX_EMP2;
/
BEGIN
P1;
END;
/
OUT MODE:
*********
EX11:
*****
CREATE OR REPLACE PROCEDURE P11(A IN NUMBER,B OUT NUMBER)
IS
BEGIN
B:=A*A;
DBMS_OUTPUT.PUT_LINE(B);
END;
/
EXECUTION:
**********
EXEC P11(10,B);
--IT WILL THROW THE ERROR.
ANYNOMOUS BLOCK:
****************
DECLARE
B NUMBER;
BEGIN
P11(10,B);
--DBMS_OUTPUT.PUT_LINE(B);
END;
/
EX12:
*****
--DEVELOP A PROGRAM FOR PASSING EMPLOYEE NAME AS IN PARAMETER RETURN SALARY OF THAT EMPLOYEE USING OUT PARMAETER FROM EMP TABLE?
CREATE OR REPLACE PROCEDURE P12(P_ENAME IN VARCHAR2,P_SAL OUT NUMBER)
IS
BEGIN
SELECT SAL INTO P_SAL FROM EMP WHERE ENAME=P_ENAME;
DBMS_OUTPUT.PUT_LINE(P_SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('INVALID ENAME');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS :'||SQLERRM);
END;
/
DECLARE
X NUMBER;
BEGIN
P12('SMITH',X);
--DBMS_OUTPUT.PUT_LINE(X);
END;
/
CREATE OR REPLACE PROCEDURE P12(P_ENAME IN VARCHAR2,P_SAL OUT NUMBER,P_HIREDATE OUT DATE,P_DEPTNO OUT NUMBER)
IS
BEGIN
SELECT SAL,HIREDATE,DEPTNO INTO P_SAL,P_HIREDATE,P_DEPTNO FROM EMP WHERE ENAME=P_ENAME;
DBMS_OUTPUT.PUT_LINE('SALARY IS :'||P_SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS :'||P_HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS :'||P_DEPTNO);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('INVALID ENAME');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS :'||SQLERRM);
END;
/
DECLARE
P_SAL NUMBER;
P_HIREDATE DATE;
P_DEPTNO NUMBER;
BEGIN
P12('KING',P_SAL,P_HIREDATE,P_DEPTNO);
END;
/
EX13:
*****
--DEVELOP A PROGRAM FOR PASSING DEPTNO AS IN PARAMETER RETURN HOW MANY EMPLOYEES ARE WORKING IN A DEPT FROM EMP TABLE.
CREATE OR REPLACE PROCEDURE P13(P_DEPTNO IN NUMBER,P_TOTAL OUT NUMBER)
IS
BEGIN
SELECT COUNT(*)
INTO P_TOTAL
FROM EMP
WHERE DEPTNO=P_DEPTNO;
DBMS_OUTPUT.PUT_LINE('TOTAL EMPLOYEES FOR DEPTNO '||P_DEPTNO||' IS :'||P_TOTAL);
END;
/
DECLARE
P_TOTAL NUMBER;
BEGIN
P13(10,P_TOTAL);
END;
EX14:
*****
-->Procedure Returns Total Salary, No.of Employees in a department.
create or replace procedure P14
(dno in number,dtot out number, dcnt out number,DMIN OUT NUMBER,DMAX OUT NUMBER,DAVG OUT NUMBER) is
begin
select sum(sal),count(*),MIN(SAL),MAX(SAL),AVG(SAL) into dtot, dcnt,DMIN,DMAX,DAVG from emp
where deptno = dno;
end;
/
DECLARE
VTOT NUMBER;
VCNT NUMBER;
VMIN NUMBER;
VMAX NUMBER;
VAVG NUMBER;
BEGIN
P14(10,VTOT,VCNT,VMIN,VMAX,VAVG);
DBMS_OUTPUT.PUT_LINE(VTOT||','||VCNT||','||VMIN||','||VMAX||','||VAVG);
END;
/
CREATE OR REPLACE PROCEDURE UPDATE_PRC(P_EMPNO NUMBER,P_INCR NUMBER,P_SAL OUT NUMBER)
IS
BEGIN
SELECT SAL INTO P_SAL FROM EMP1 WHERE EMPNO=P_EMPNO;
dbms_output.put_line('before updated salary is : '||P_SAL);
UPDATE EMP1
SET SAL=SAL+P_INCR
WHERE EMPNO=P_EMPNO;
COMMIT;
SELECT SAL INTO P_SAL FROM EMP1 WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE('AFTER UPDATED SALARY IS : '||P_SAL);
END;
/
DECLARE
P_SAL NUMBER;
BEGIN
UPDATE_PRC(7499,1000,P_SAL);
--DBMS_OUTPUT.PUT_LINE(P_SAL);
END;
/
CREATE OR REPLACE PROCEDURE emp_name (p_empno IN NUMBER, p_emp_name OUT varchar2,P_SAL OUT NUMBER)
IS
BEGIN
SELECT ename,SAL INTO p_emp_name,P_SAL
FROM emp WHERE empno=p_empno;
--dbms_output.put_line(P_emp_name);
END;
/
declare
l_emp_name varchar2(20);
L_SAL NUMBER;
begin
emp_name(7839,l_emp_name,L_SAL);
dbms_output.put_line(l_emp_name);
dbms_output.put_line(l_SAL);
end;
/
declare
lv_ename varchar2(20);
LV_SAL NUMBER;
cursor c1 is select empno from emp;
begin
for i in c1
loop
emp_name(i.empno,lv_ename,LV_SAL);
dbms_output.put_line(i.empno||','||lv_ename||','||LV_SAL);
end loop;
end;
/
DECLARE
P_EMPNO NUMBER;
P_EMP_NAME VARCHAR2(20);
P_SAL NUMBER;
CURSOR C1 IS SELECT EMPNO FROM EMP;
BEGIN
FOR I IN C1
LOOP
P_EMPNO :=I.EMPNO;
emp_name(P_EMPNO,P_EMP_NAME,P_SAL);
DBMS_OUTPUT.PUT_LINE(P_EMP_NAME||','||P_SAL);
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO IN NUMBER,P_ENAME OUT VARCHAR2,P_SAL OUT NUMBER,P_HIREDATE OUT DATE)
IS
CURSOR C1 IS SELECT ENAME,SAL,HIREDATE FROM EMP WHERE DEPTNO=P_DEPTNO;
BEGIN
FOR I IN C1
LOOP
P_ENAME :=I.ENAME;
P_SAL :=I.SAL;
P_HIREDATE :=I.HIREDATE;
DBMS_OUTPUT.PUT_LINE(P_ENAME||','||P_SAL||' '||P_HIREDATE||P_DEPTNO);
END LOOP;
END;
/
DECLARE
p_ename varchar2(20);
P_SAL NUMBER;
P_HIREDATE DATE;
BEGIN
P1(10,p_ename,P_SAL,P_HIREDATE);
END;
/
CREATE OR REPLACE PROCEDURE P1(P_SAL OUT NUMBER,P_HIREDATE OUT DATE,P_DEPTNO IN NUMBER)
IS
CURSOR C1 IS SELECT ENAME,SAL,HIREDATE FROM EMP WHERE DEPTNO=P_DEPTNO;
BEGIN
FOR I IN C1
LOOP
P_SAL :=I.SAL;
P_HIREDATE :=I.HIREDATE;
DBMS_OUTPUT.PUT_LINE(P_SAL||' '||P_HIREDATE);
END LOOP;
END;
/
DECLARE
P_SAL NUMBER;
P_HIREDATE DATE;
BEGIN
P1(P_SAL,P_HIREDATE,10);
END;
/
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO IN NUMBER,P_SAL OUT NUMBER,P_HIREDATE OUT DATE)
IS
CURSOR C1 IS SELECT ENAME,SAL,HIREDATE,DEPTNO FROM EMP WHERE DEPTNO=P_DEPTNO;
BEGIN
FOR I IN C1
LOOP
P_SAL :=I.SAL;
P_HIREDATE :=I.HIREDATE;
DBMS_OUTPUT.PUT_LINE(P_SAL||' '||P_HIREDATE||' '||P_DEPTNO);
END LOOP;
END;
/
DECLARE
P_SAL NUMBER;
P_HIREDATE DATE;
BEGIN
P1(10,P_SAL,P_HIREDATE);
END;
/
CREATE OR REPLACE PROCEDURE P1(P_EMPNO IN NUMBER,P_EXP OUT NUMBER)
IS
--LV_EMPNO NUMBER :=NULL;
BEGIN
IF P_EMPNO IS NOT NULL THEN
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)
INTO P_EXP
FROM EMP
WHERE EMPNO=P_EMPNO;
DBMS_OUTPUT.PUT_LINE(P_EMPNO||','||P_EXP);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NUMBER DOES NOT EXIST');
END;
/
DECLARE
P_EXP NUMBER;
BEGIN
P1(7839,P_EXP);
END;
/
DECLARE
P_EMPNO NUMBER;
P_EXP NUMBER;
CURSOR C1 IS SELECT EMPNO FROM EMP;
BEGIN
FOR I IN C1
LOOP
P_EMPNO :=I.EMPNO;
P1(P_EMPNO,P_EXP);
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE P1(P_EMPNO IN NUMBER,P_EXP OUT NUMBER)
IS
--LV_EMPNO NUMBER :=NULL;
BEGIN
IF P_EMPNO IS NOT NULL THEN
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)
INTO P_EXP
FROM EMP
WHERE EMPNO=P_EMPNO;
--DBMS_OUTPUT.PUT_LINE(P_EXP);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NUMBER DOES NOT EXIST');
END;
/
DECLARE
P_EXP NUMBER;
CURSOR C1 IS SELECT EMPNO,ENAME,SAL FROM EMP;
BEGIN
FOR I IN C1
LOOP
P1(I.EMPNO,P_EXP);
DBMS_OUTPUT.PUT_LINE(I.EMPNO||', '||I.ENAME||','||I.SAL||','||P_EXP);
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO IN NUMBER,P_EXP OUT NUMBER)
IS
--LV_EMPNO NUMBER :=NULL;
CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=P_DEPTNO;
BEGIN
FOR I IN C1
LOOP
IF I.EMPNO IS NOT NULL THEN
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)
INTO P_EXP
FROM EMP
WHERE EMPNO=I.EMPNO;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||P_EXP);
END IF;
END LOOP;
END;
/
DECLARE
P_EXP NUMBER;
BEGIN
P1(10,P_EXP);
END;
/
DROP TABLE EMP1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
INSERT INTO EMP1(EMPNO,ENAME,SAL,DEPTNO) VALUES(1000,NULL,1000,30);
/
COMMIT;
/
--anonymous block
DECLARE
CURSOR C1 IS SELECT * FROM EMP1;
LV_EMPNO NUMBER;
LV_SAL NUMBER;
BEGIN
FOR I IN C1
LOOP
BEGIN
SELECT EMPNO,SAL
INTO LV_EMPNO,LV_SAL
FROM EMP1
WHERE ENAME=I.ENAME;
DBMS_OUTPUT.PUT_LINE(LV_EMPNO||','||LV_SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ENAME IS NOT FOUND');
END;
END LOOP;
END;
/
--procedure without any parameters
CREATE OR REPLACE PROCEDURE P1
IS
CURSOR C1 IS SELECT * FROM EMP1;
LV_EMPNO NUMBER;
LV_SAL NUMBER;
BEGIN
FOR I IN C1
LOOP
BEGIN
SELECT EMPNO,SAL
INTO LV_EMPNO,LV_SAL
FROM EMP1
WHERE ENAME=I.ENAME;
DBMS_OUTPUT.PUT_LINE(LV_EMPNO||','||LV_SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ENAME IS NOT FOUND');
END;
END LOOP;
END;
/
EXEC P1;
/
--procedure using with in parameters
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO NUMBER)
IS
CURSOR C1 IS SELECT * FROM EMP1 WHERE DEPTNO=P_DEPTNO;
LV_EMPNO NUMBER;
LV_SAL NUMBER;
BEGIN
FOR I IN C1
LOOP
BEGIN
SELECT EMPNO,SAL
INTO LV_EMPNO,LV_SAL
FROM EMP1
WHERE ENAME=I.ENAME;
DBMS_OUTPUT.PUT_LINE(LV_EMPNO||','||LV_SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ENAME IS NOT FOUND');
END;
END LOOP;
END;
/
EXEC P1(30);
/
--procedure using out parametrers
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO NUMBER,P_EMPNO OUT NUMBER,P_SAL OUT NUMBER)
IS
CURSOR C1 IS SELECT * FROM EMP1 WHERE DEPTNO=P_DEPTNO;
--LV_EMPNO NUMBER;
--LV_SAL NUMBER;
BEGIN
FOR I IN C1
LOOP
BEGIN
SELECT EMPNO,SAL
INTO P_EMPNO,P_SAL
FROM EMP1
WHERE ENAME=I.ENAME;
DBMS_OUTPUT.PUT_LINE(P_EMPNO||','||P_SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ENAME IS NOT FOUND');
END;
END LOOP;
END;
/
exec p1(30);
/
CREATE OR REPLACE FUNCTION F1(P_EMPNO IN NUMBER,P_EXP OUT NUMBER)
RETURN VARCHAR2
IS
BEGIN
IF P_EMPNO IS NOT NULL THEN
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)
INTO P_EXP
FROM EMP
WHERE EMPNO=P_EMPNO;
RETURN('EXPEIRIENCE IS : '||P_EXP);
END IF;
END;
/
DECLARE
P_EXP NUMBER;
P_ABC VARCHAR2(20);
BEGIN
P_ABC:=F1(7839,P_EXP);
DBMS_OUTPUT.PUT_LINE(P_ABC);
END;
/
DECLARE
P_EMPNO NUMBER;
P_EXP NUMBER;
P_ABC VARCHAR2(20);
CURSOR C1 IS SELECT * FROM EMP;
BEGIN
FOR I IN C1
LOOP
P_EMPNO :=I.EMPNO;
P_ABC :=F1(P_EMPNO,P_EXP);
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.SAL||','||P_ABC);
END LOOP;
END;
/
CREATE OR REPLACE FUNCTION F1(P_DEPTNO IN NUMBER,P_EXP OUT NUMBER)
RETURN VARCHAR2
IS
--LV_EMPNO NUMBER :=NULL;
CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=P_DEPTNO;
BEGIN
FOR I IN C1
LOOP
BEGIN
IF I.EMPNO IS NOT NULL THEN
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)
INTO P_EXP
FROM EMP
WHERE EMPNO=I.EMPNO;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||P_EXP);
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN('ERROR MESSAGE IS : '||SQLERRM);
RETURN('EMPLOYEE NUMBER DOES NOT EXIST');
END;
END LOOP;
RETURN 0;
END;
/
DECLARE
P_EXP NUMBER;
P_ABC VARCHAR2(20);
BEGIN
P_ABC :=F1(10,P_EXP);
END;
/
CREATE OR REPLACE PROCEDURE P_USER(P_USER_ID IN NUMBER,P_USER_NAME OUT VARCHAR2)
IS
BEGIN
SELECT USER_NAME
INTO P_USER_NAME
FROM FND_USER
WHERE USER_ID=P_USER_ID;
DBMS_OUTPUT.PUT_LINE('USER NAME IS : '||P_USER_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('USER ID DOES NOT EXIST');
END;
/
DECLARE
P_USER_NAME VARCHAR2(240);
BEGIN
P_USER(2,P_USER_NAME);
END;
/
DECLARE
CURSOR C1 IS SELECT USER_ID FROM FND_USER;
P_USER_NAME VARCHAR2(2000);
BEGIN
FOR USER_IDX IN C1
LOOP
P_USER(USER_IDX.USER_ID,P_USER_NAME);
END LOOP;
END;
/
SELECT COUNT(*) FROM FND_USER;
/
IN OUT MODE:
***********
EX15:
*****
CREATE OR REPLACE PROCEDURE P15(A IN OUT NUMBER)
IS
BEGIN
A:=A*A;
--DBMS_OUTPUT.PUT_LINE(A);
END;
/
ANYNOMOUS BLOCK:
****************
DECLARE
A NUMBER:=&A;
BEGIN
P15(A);
DBMS_OUTPUT.PUT_LINE(A);
END;
/
CREATE OR REPLACE PROCEDURE P15(A IN OUT NUMBER,B IN OUT NUMBER)
IS
BEGIN
--A:=A*A;
--B:=B*B;
B:=A*A;
--DBMS_OUTPUT.PUT_LINE(A);
DBMS_OUTPUT.PUT_LINE(B);
END;
/
DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
BEGIN
P15(A,B);
DBMS_OUTPUT.PUT_LINE(A);
END;
/
EX16:
*****
CREATE OR REPLACE PROCEDURE P16(P_A IN OUT NUMBER)
IS
BEGIN
SELECT SAL INTO P_A FROM EMP WHERE EMPNO=P_A;
--DBMS_OUTPUT.PUT_LINE(P_A);
END;
/
ANYNOMOUS BLOCK:
****************
DECLARE
A NUMBER:=&A;
BEGIN
P16(A);
DBMS_OUTPUT.PUT_LINE(A);
END;
/
EX17:
/
CREATE OR REPLACE PROCEDURE P1(P_DEPTNO IN OUT VARCHAR2)
IS
BEGIN
DELETE FROM EMP1 WHERE DEPTNO=P_DEPTNO;
IF SQL%ROWCOUNT=0 THEN
RAISE_APPLICATION_ERROR(-20140,'DEPTNO'||P_DEPTNO||'DOES NOT EXIST');
ELSE
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF RECORDS ARE DELETED :'||SQL%ROWCOUNT);
commit;
END IF;
EXCEPTION
WHEN OTHERS THEN
P_DEPTNO :=SQLERRM;
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS :'||V_DEPTNO);
END;
/
ANYNOMOUS BLOCK:
****************
DECLARE
--V_ERR_MSG VARCHAR2(200);
V_DEPTNO varchar2(240) :=60;
BEGIN
P1(V_DEPTNO);
--DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS :'||V_DEPTNO);
END;
/
CREATE OR REPLACE PROCEDURE emp_salary_increase(salary_inc IN OUT number)
IS
BEGIN
SELECT sal
INTO salary_inc
FROM emp
WHERE empno= salary_inc;
END;
/
declare
p_salary_inc number :=&no;
begin
emp_salary_increase(p_salary_inc);
dbms_output.put_line(p_salary_inc);
end;
/
DECLARE
CURSOR updated_sal is
SELECT empno,sal
FROM emp;
pre_sal number;
BEGIN
FOR emp_rec IN updated_sal
LOOP
--pre_sal := emp_rec.sal;
emp_salary_increase(emp_rec.empno);
dbms_output.put_line(emp_rec.empno||','||pre_sal );
--dbms_output.put_line('The salary of ' || emp_rec.empno ||
-- ' increased from '|| pre_sal || ' to '||emp_rec.sal);
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE P1(P_ABC IN OUT VARCHAR2,P_SAL OUT NUMBER)
IS
BEGIN
SELECT ENAME,SAL
INTO P_ABC,P_SAL
FROM EMP
WHERE EMPNO=P_ABC;
DBMS_OUTPUT.PUT_LINE(P_ABC||','||P_SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMP DOES NOT EXIST');
END;
/
DECLARE
P_ABC VARCHAR2(20) :=7839;
P_SAL NUMBER;
BEGIN
P1(P_ABC,P_SAL);
END;
/
DECLARE
P_ABC VARCHAR2(20);
CURSOR C1 IS SELECT EMPNO FROM EMP;
P_SAL NUMBER;
BEGIN
FOR I IN C1
LOOP
P_ABC :=I.EMPNO;
P1(P_ABC,P_SAL);
END LOOP;
END;
/
---PROCEDURE WITH TCL COMMANDS
****************
PROCEDURE COMMIT
****************
EX17:
*****
CREATE OR REPLACE PROCEDURE XXAP_COMMIT
IS
BEGIN
INSERT INTO DEPT1(DEPTNO,DNAME,LOC) VALUES (60,'ORACLE','QATAR');
--
COMMIT;
--
DBMS_OUTPUT.PUT_LINE('INSERTED');
END XXAP_COMMIT;
/
EXEC XXAP_COMMIT;
/
CREATE OR REPLACE FUNCTION F1(P_ABC IN OUT VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
SELECT SAL
INTO P_ABC
FROM EMP
WHERE EMPNO=P_ABC;
RETURN(P_ABC);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN('EMP DOES NOT EXIST');
END;
/
DECLARE
P_ABC VARCHAR2(20) :=7839;
P_TEST NUMBER;
BEGIN
P_TEST :=F1(P_ABC);
DBMS_OUTPUT.PUT_LINE(P_TEST);
END;
/
DECLARE
P_ABC VARCHAR2(20);
P_TEST VARCHAR2(20);
CURSOR C1 IS SELECT * FROM EMP;
P_SAL NUMBER;
BEGIN
FOR I IN C1
LOOP
P_ABC :=I.EMPNO;
P_TEST :=F1(P_ABC);
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||P_TEST);
END LOOP;
END;
/
******************
PROCEDURE ROLLBACK
*******************
CREATE OR REPLACE PROCEDURE XXAP_ROLLBACK
IS
LV_LOC DEPT1.LOC%TYPE;
BEGIN
SELECT LOC INTO LV_LOC FROM DEPT1 WHERE DEPTNO=60;
DBMS_OUTPUT.PUT_LINE('Before Update :'||LV_LOC);
--
UPDATE DEPT1 SET LOC='LEADS' WHERE DEPTNO=60;
--
SELECT LOC INTO LV_LOC FROM DEPT1 WHERE DEPTNO=60;
DBMS_OUTPUT.PUT_LINE('After Update +Before ROLLBACK :'||LV_LOC);
--
ROLLBACK;
--
SELECT LOC INTO LV_LOC FROM DEPT1 WHERE DEPTNO=60;
DBMS_OUTPUT.PUT_LINE('After ROLLBACK :'||LV_LOC);
END XXAP_ROLLBACK;
/
EXEC XXAP_ROLLBACK;
/
*******************
PROCEDURE SAVEPOINT
*******************
CREATE OR REPLACE PROCEDURE XXAP_SAVEPOINT
IS
LV_DEPTNO DEPT1.DEPTNO%TYPE;
LV_DNAME DEPT1.DNAME%TYPE;
LV_LOC DEPT1.LOC%TYPE;
BEGIN
INSERT INTO DEPT1 (DEPTNO,DNAME,LOC)
VALUES (50,'HR','INDIA');
--
DBMS_OUTPUT.PUT_LINE('Inserted');
--
SELECT DEPTNO,DNAME,LOC
INTO LV_DEPTNO,LV_DNAME,LV_LOC
FROM DEPT1
WHERE DEPTNO=50;
---
DBMS_OUTPUT.PUT_LINE('selected : '||lv_deptno||' : '||lv_dname||' : '||lv_loc);
---
SAVEPOINT S1;
--
DBMS_OUTPUT.PUT_LINE('Savepoint S1');
--
INSERT INTO DEPT1 (DEPTNO,DNAME,LOC)
VALUES (70,'OPERATIONS','UK');
--
DBMS_OUTPUT.PUT_LINE('Inserted');
--
SELECT DEPTNO,DNAME,LOC
INTO LV_DEPTNO,LV_DNAME,LV_LOC
FROM DEPT1
WHERE DEPTNO=70;
---
DBMS_OUTPUT.PUT_LINE('selected : '||lv_deptno||' : '||lv_dname||' : '||lv_loc);
---
SAVEPOINT S2;
--
DBMS_OUTPUT.PUT_LINE('Savepoint S2');
--
INSERT INTO DEPT1 (DEPTNO,DNAME,LOC)
VALUES (80,'OPERATIONS2','LONDON');
--
DBMS_OUTPUT.PUT_LINE('Inserted');
--
SELECT DEPTNO,DNAME,LOC
INTO LV_DEPTNO,LV_DNAME,LV_LOC
FROM DEPT1
WHERE DEPTNO=80;
---
DBMS_OUTPUT.PUT_LINE('selected : '||lv_deptno||' : '||lv_dname||' : '||lv_loc);
---
SAVEPOINT S3;
--
DBMS_OUTPUT.PUT_LINE('Savepoint S3');
--
ROLLBACK TO S2;
--
DBMS_OUTPUT.PUT_LINE('ROLLBACK to S2');
--
END XXAP_SAVEPOINT;
/
EXEC XXAP_SAVEPOINT;
/
SELECT * FROM DEPT1;
/
--REAL TIME EXAMPLE
--CREATE TABLE IN CUSTOM SCHEMA
--GIVE GRANTS TO APPS SCHEMA
--CREATE SYNONYM IN APPS SCHEMA
CREATE TABLE XXAP_INVOICE_CREATION
(
INVOICE_NUM VARCHAR2(50),
INVOICE_ID NUMBER,
INVOICE_AMOUNT NUMBER,
INVOICE_CURRENCY_CODE VARCHAR2(10),
PAYMENT_CURRENCY_CODE VARCHAR2(10),
DESCRIPTION VARCHAR2(1000),
INVOICE_TYPE_LOOKUP_CODE VARCHAR2(20),
PAY_GROUP_LOOKUP_CODE VARCHAR2(20),
VENDOR_NAME VARCHAR2(50),
VENDOR_NUMBER VARCHAR2(50),
VENDOR_SITE_CODE VARCHAR2(50),
OU_NAME VARCHAR2(50),
ORG_ID NUMBER,
CREATION_DATE DATE, --WHO COLUMNS
CREATED_BY NUMBER, --WHO COLUMNS
LAST_UPDATE_DATE DATE, --WHO COLUMNS
LAST_UPDATED_BY NUMBER, --WHO COLUMNS
LAST_UPDATE_LOGIN NUMBER --WHO COLUMNS
);
/
--REAL TIME EXAMPLE
CREATE OR REPLACE PROCEDURE P1
IS
--declare
cursor c1
is
select aia.INVOICE_NUM,
aia.INVOICE_ID
,aia.INVOICE_AMOUNT
,aia.INVOICE_CURRENCY_CODE
,aia.PAYMENT_CURRENCY_CODE
,aia.DESCRIPTION
,aia.INVOICE_TYPE_LOOKUP_CODE
,aia.PAY_GROUP_LOOKUP_CODE
,aps.VENDOR_NAME
,aps.segment1 vendor_number
,assa.VENDOR_SITE_CODE
,hou.name ou_name
,hou.organization_id org_id
from ap_invoices_all aia,
ap_suppliers aps,
ap_supplier_sites_all assa,
hr_operating_units hou
where 1=1
and aia.VENDOR_ID=aps.VENDOR_ID
and aps.VENDOR_ID=assa.VENDOR_ID
and aia.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and hou.organization_id=aia.org_id
and rownum<=100;
LV_COUNT NUMBER;
LV_STG_CNT NUMBER;
BEGIN
BEGIN
select COUNT(*)
INTO LV_STG_CNT
from ap_invoices_all aia,
ap_suppliers aps,
ap_supplier_sites_all assa,
hr_operating_units hou
where 1=1
and aia.VENDOR_ID=aps.VENDOR_ID
and aps.VENDOR_ID=assa.VENDOR_ID
and aia.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and hou.organization_id=aia.org_id
and rownum<=100;
DBMS_OUTPUT.PUT_LINE('STG TBL COUNT : '||LV_STG_CNT);
END;
LV_COUNT :=0;
FOR I IN C1
LOOP
LV_COUNT :=LV_COUNT+1;
BEGIN
INSERT INTO XXAP_INVOICE_CREATION
(
INVOICE_NUM,
INVOICE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
DESCRIPTION,
INVOICE_TYPE_LOOKUP_CODE,
PAY_GROUP_LOOKUP_CODE,
VENDOR_NAME,
VENDOR_NUMBER,
VENDOR_SITE_CODE,
OU_NAME,
ORG_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
I.INVOICE_NUM,
I.INVOICE_ID,
I.INVOICE_AMOUNT,
I.INVOICE_CURRENCY_CODE,
I.PAYMENT_CURRENCY_CODE,
I.DESCRIPTION,
I.INVOICE_TYPE_LOOKUP_CODE,
I.PAY_GROUP_LOOKUP_CODE,
I.VENDOR_NAME,
I.VENDOR_NUMBER,
I.VENDOR_SITE_CODE,
I.OU_NAME,
I.ORG_ID,
SYSDATE,
FND_GLOBAL.USER_ID, -- -1
SYSDATE,
FND_GLOBAL.USER_ID, -- -1
-1
);
END;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF RECORDS ARE INSERTED :'||LV_COUNT);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS :'||SQLERRM);
END;
/
exec p1;
/
select * from xxap_invoice_creation;
/
CREATE OR REPLACE PROCEDURE P1
IS
--DECLARE
begin
insert into XXAP_INVOICE_CREATION
(
INVOICE_NUM,
INVOICE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
DESCRIPTION,
INVOICE_TYPE_LOOKUP_CODE,
PAY_GROUP_LOOKUP_CODE,
VENDOR_NAME,
vendor_number,
VENDOR_SITE_CODE,
ou_name,
org_id,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
select aia.INVOICE_NUM,
aia.INVOICE_ID
,aia.INVOICE_AMOUNT
,aia.INVOICE_CURRENCY_CODE
,aia.PAYMENT_CURRENCY_CODE
,aia.DESCRIPTION
,aia.INVOICE_TYPE_LOOKUP_CODE
,aia.PAY_GROUP_LOOKUP_CODE
,aps.VENDOR_NAME
,aps.segment1 vendor_number
,assa.VENDOR_SITE_CODE
,hou.name ou_name
,hou.organization_id org_id
,SYSDATE CREATION_DATE
,'-1' CREATED_BY
,SYSDATE LAST_UPDATE_DATE
,'-1' LAST_UPDATED_BY
,'-1' LAST_UPDATE_LOGIN
from ap_invoices_all aia,
ap_suppliers aps,
ap_supplier_sites_all assa,
hr_operating_units hou
where 1=1
and aia.VENDOR_ID=aps.VENDOR_ID
and aps.VENDOR_ID=assa.VENDOR_ID
and aia.VENDOR_SITE_ID=assa.VENDOR_SITE_ID
and hou.organization_id=aia.org_id;
COMMIT;
end;
/
EXEC P1;
/
SELECT * FROM XXAP_INVOICE_CREATION;
/
CREATE OR REPLACE PROCEDURE P1
IS
LV_INVOICE_AMOUNT NUMBER;
LV_INVOICE_NUM VARCHAR2(20);
LV_INVOICE_DATE DATE;
LV_INVOICE_CURRENCY_CODE VARCHAR2(20);
CURSOR C1 IS SELECT INVOICE_ID,INVOICE_CURRENCY_CODE,INVOICE_AMOUNT,INVOICE_NUM,INVOICE_DATE
FROM AP_INVOICES_ALL
WHERE 1=1
AND ROWNUM<=100;
BEGIN
FOR I IN C1
LOOP
BEGIN
SELECT INVOICE_AMOUNT,INVOICE_NUM,INVOICE_DATE,INVOICE_CURRENCY_CODE
INTO LV_INVOICE_AMOUNT,LV_INVOICE_NUM,LV_INVOICE_DATE,LV_INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_ID=I.INVOICE_ID;
DBMS_OUTPUT.PUT_LINE(LV_INVOICE_AMOUNT||','||LV_INVOICE_NUM||','||LV_INVOICE_DATE||','||LV_INVOICE_CURRENCY_CODE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
END;
END LOOP;
END;
/
EXEC P1;
/
No comments:
Post a Comment