SELECT INTO Clause:
*******************
-->This clause is used to fetched the data from table and storing into PL/SQl variables.
-->It always returns single record or value at a time.
-->This clause is used in executable section of the program.
SYNTAX:
*******
SELECT COL1,COL2,COL3,.... INTO VAR1,VAR2,VAR3.. FROM <Table Name> where condition;
--Write a PL/SQL program for user entered empno will dispay name of the ename and salary from emp table
DECLARE
LV_ENAME VARCHAR2(20);
LV_SAL NUMBER(10);
BEGIN
-- Retrieving data from table
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP
WHERE EMPNO=7839; --&NO
DBMS_OUTPUT.PUT_LINE('Ename :'||LV_ENAME||','||'Sal : '||LV_SAL);
END;
/
DECLARE
--EMP VARIABLES
LV_EMPNO NUMBER;
LV_ENAME VARCHAR2(20);
LV_JOB VARCHAR2(20);
LV_SAL NUMBER;
LV_DEPTNO NUMBER;
--DEPT VARIABLES
LV_DNAME VARCHAR2(20);
LV_LOC VARCHAR2(20);
BEGIN
--EMP DETAILS
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
INTO LV_EMPNO,LV_ENAME,LV_JOB,LV_SAL,LV_DEPTNO
FROM EMP
WHERE EMPNO=7521;
DBMS_OUTPUT.PUT_LINE('*********EMP DETAILS START***********');
--DBMS_OUTPUT.PUT_LINE('EMPNO IS : '||LV_EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS : '||LV_ENAME);
DBMS_OUTPUT.PUT_LINE('JOB IS : '||LV_JOB);
DBMS_OUTPUT.PUT_LINE('SAL IS : '||LV_SAL);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS : '||LV_DEPTNO);
DBMS_OUTPUT.PUT_LINE(LV_EMPNO||','||LV_ENAME||','||LV_JOB||','||LV_SAL||','||LV_DEPTNO);
DBMS_OUTPUT.PUT_LINE('*********EMP DETAILS END***********');
--DEPT DETAILS
SELECT DNAME,LOC
INTO LV_DNAME,LV_LOC
FROM DEPT
WHERE DEPTNO=10;
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('*********DEPT DETAILS START***********');
DBMS_OUTPUT.PUT_LINE('DNAME IS : '||LV_DNAME);
DBMS_OUTPUT.PUT_LINE('LOC IS : '||LV_LOC);
DBMS_OUTPUT.PUT_LINE('*********DEPT DETAILS START***********');
END;
/
DECLARE
--DEPT VARIABLES
LV_DEPTNO NUMBER;
LV_DNAME VARCHAR2(20);
LV_LOC VARCHAR2(20);
--EMP VARIABLES
LV_ENAME VARCHAR2(20);
LV_JOB VARCHAR2(20);
LV_SAL NUMBER;
--LV_DEPTNO NUMBER;
---PO DETAILS
LV_PO_HEADER_ID NUMBER;
LV_TYPE_LOOKUP_CODE VARCHAR2(25);
LV_SEGMENT1 VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('**************DEPT DETAILS START************');
DBMS_OUTPUT.PUT_LINE(' ');
SELECT DEPTNO,DNAME,LOC
INTO LV_DEPTNO,LV_DNAME,LV_LOC
FROM DEPT
WHERE DEPTNO=20;
DBMS_OUTPUT.PUT_LINE(LV_DEPTNO||','||LV_DNAME||','||LV_LOC);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('**************DEPT DETAILS END************');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('**************EMP DETAILS START************');
DBMS_OUTPUT.PUT_LINE(' ');
SELECT ENAME,JOB,SAL,DEPTNO
INTO LV_ENAME,LV_JOB,LV_SAL,LV_DEPTNO
FROM EMP
WHERE EMPNO=7654;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_JOB||','||LV_SAL||','||LV_DEPTNO);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('**************EMP DETAILS END***************');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('**************PO DETAILS START**************');
DBMS_OUTPUT.PUT_LINE(' ');
SELECT PO_HEADER_ID,TYPE_LOOKUP_CODE,SEGMENT1
INTO LV_PO_HEADER_ID,LV_TYPE_LOOKUP_CODE,LV_SEGMENT1
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID=1;
DBMS_OUTPUT.PUT_LINE(LV_PO_HEADER_ID||','||LV_TYPE_LOOKUP_CODE||','||LV_SEGMENT1);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('**************PO DETAILS END ***************');
END;
/
CREATE TABLE XX_DUMMY1 AS SELECT * FROM EMP WHERE 1=2;
/
DECLARE
-- HR_OPERATING_UNIT VARIABLES
LV_BUSINESS_GROUP_ID NUMBER;
LV_ORGANIZATION_ID NUMBER;
LV_SET_OF_BOOKS_ID NUMBER;
--EMP VARIABLES
LV_EMPNO NUMBER(4);
LV_ENAME VARCHAR2(10);
LV_JOB VARCHAR2(9);
LV_SAL NUMBER;
BEGIN
--ASSING THE VALUE TO HR TABLE
SELECT BUSINESS_GROUP_ID,ORGANIZATION_ID,SET_OF_BOOKS_ID
INTO LV_BUSINESS_GROUP_ID,LV_ORGANIZATION_ID,LV_SET_OF_BOOKS_ID
FROM HR_OPERATING_UNITS
WHERE NAME='Vision ADB';
--PRINT THE HR TABLE DATA
DBMS_OUTPUT.PUT_LINE(LV_BUSINESS_GROUP_ID||','||LV_ORGANIZATION_ID||','||LV_SET_OF_BOOKS_ID);
--ASSING THE VALUE TO EMP TABLE
SELECT EMPNO,ENAME,JOB,SAL
INTO LV_EMPNO,LV_ENAME,LV_JOB,LV_SAL
FROM EMP
WHERE EMPNO=7499;
--PRINT THE EMP TABLE DATA
DBMS_OUTPUT.PUT_LINE(LV_EMPNO||' '||LV_ENAME||' '||LV_JOB||' '||LV_SAL);
--INSERT THE DATA INTO DUMMY TABLE
INSERT INTO XX_DUMMY1 SELECT * FROM EMP;
--UPDATE DATA
UPDATE XX_DUMMY1
SET SAL=10000
WHERE DEPTNO=30;
--DELETE THE DATA
DELETE FROM XX_DUMMY1 WHERE DEPTNO=20;
COMMIT;
END;
/
CREATE TABLE XX_INS
(
SNO NUMBER,
SNAME VARCHAR2(20)
);
/
DECLARE
LV_CNT NUMBER;
LV_MAX_SAL NUMBER;
LV_DEPT_CNT NUMBER;
LV_PO_CNT NUMBER;
BEGIN
SELECT COUNT(*),MAX(SAL)
INTO LV_CNT,LV_MAX_SAL
FROM EMP;
SELECT COUNT(*)
INTO LV_DEPT_CNT
FROM DEPT;
SELECT COUNT(*)
INTO LV_PO_CNT
FROM PO_HEADERS_ALL;
INSERT INTO XX_INS VALUES (1,'A');
INSERT INTO XX_INS VALUES (2,'B');
COMMIT;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEES : '||LV_CNT);
DBMS_OUTPUT.PUT_LINE(LV_CNT||','||LV_MAX_SAL);
DBMS_OUTPUT.PUT_LINE('DEPT COUNT IS : '||LV_DEPT_CNT);
DBMS_OUTPUT.PUT_LINE('PO COUNT IS : '||LV_PO_CNT);
END;
/
SELECT * FROM XX_INS;
/
DECLARE
LV_SAL NUMBER;
LV_COMM NUMBER;
LV_SALCOMM NUMBER;
BEGIN
SELECT SAL,COMM,SAL+NVL(COMM,0)
INTO LV_SAL,LV_COMM,LV_SALCOMM
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE('SAL :'||LV_SAL);
DBMS_OUTPUT.PUT_LINE('COMM :'||LV_COMM);
DBMS_OUTPUT.PUT_LINE('SALCOMM :'||LV_SALCOMM);
END;
/
--Write a PL/SQL Program to display maximum salary for emp table
DECLARE
LV_SAL NUMBER(10);
BEGIN
-- Retrieving data from table
SELECT MAX(SAL)
INTO LV_SAL
FROM EMP;
DBMS_OUTPUT.PUT_LINE('Sal : '||' :: '||LV_SAL);
END;
/
DECLARE
LV_MAX_SAL NUMBER;
LV_MINSAL NUMBER;
LV_AVGSAL NUMBER;
LV_TOTCNT NUMBER;
BEGIN
SELECT MAX(SAL),MIN(SAL)MINSAL,AVG(SAL)AVGSAL,COUNT(*)TOTCNT
INTO LV_MAX_SAL,LV_MINSAL,LV_AVGSAL,LV_TOTCNT
FROM EMP;
DBMS_OUTPUT.PUT_LINE('MAX SALARY :'||LV_MAX_SAL);
DBMS_OUTPUT.PUT_LINE('MIN SALARY :'||LV_MINSAL);
DBMS_OUTPUT.PUT_LINE('AVG SALARY IS:'||LV_AVGSAL);
DBMS_OUTPUT.PUT_LINE('TOTAL COUNT IS :'||LV_TOTCNT);
END;
/
declare
lv_ename varchar2(10);
lv_job varchar2(20);
lv_sal number;
lv_deptno number;
begin
select ename,job,sal,deptno
into lv_ename,lv_job,lv_sal,lv_deptno
from emp
where empno=7369;
dbms_output.put_line(to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
dbms_output.put_line('employee details report');
dbms_output.put_line(' ');
dbms_output.put_line('ename'||' '||'job'||' '||'sal'||' '||'deptno');
dbms_output.put_line(lv_ename||','||lv_job||','||lv_sal||','||lv_deptno);
end;
/
-->Write a PL/SQL Program retrieve the employ details and calculates the Net salary and Prints.
declare
veno number(4) := &employ;
vname varchar2(10);
vsal number(7,2);
vcomm number(5,2);
net number(7,2);
begin
-- Retrieving data from table
select ename,sal,comm into vname,vsal,vcomm from emp where empno = veno;
-- calculating net salary
net := vsal + nvl(vcomm,0);
dbms_output.put_line(' Employ details are : ');
dbms_output.put_line(veno||' '||vname||' '||
vsal||' '||vcomm||' '||net);
end;
/
DECLARE
LV_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME
INTO LV_ENAME
FROM EMP;
DBMS_OUTPUT.PUT_LINE('ENAME IS :'||LV_ENAME);
END;
/
-- it will throw the error
--exact fetch returns more than requested number of rows
/
DECLARE
LV_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME
INTO LV_ENAME
FROM EMP
WHERE DEPTNO=20;
DBMS_OUTPUT.PUT_LINE('ENAME IS :'||LV_ENAME);
END;
/
-- it will throw the error
--exact fetch returns more than requested number of rows
/
DECLARE
LV_SUM_SAL NUMBER;
LV_DEPTNO NUMBER;
BEGIN
SELECT SUM(SAL),DEPTNO
INTO LV_SUM_SAL,LV_DEPTNO
FROM EMP
GROUP BY DEPTNO;
DBMS_OUTPUT.PUT_LINE('SUM OF SALARY IS :'||LV_SUM_SAL);
END;
/
-- it will throw the error
--exact fetch returns more than requested number of rows
/
SELECT ORGANIZATION_ID
FROM HR_OPERATING_UNITS
WHERE NAME='Vision Operations';
/
DECLARE
LV_ORGANIZATION_ID NUMBER;
BEGIN
SELECT ORGANIZATION_ID
INTO LV_ORGANIZATION_ID
FROM HR_OPERATING_UNITS
WHERE NAME='Vision Operations';
DBMS_OUTPUT.PUT_LINE('ORGANIZATION ID IS : '||LV_ORGANIZATION_ID);
END;
/
SELECT BUSINESS_GROUP_ID,ORGANIZATION_ID,SET_OF_BOOKS_ID
FROM HR_OPERATING_UNITS
WHERE NAME='Vision Operations';
/
DECLARE
LV_BUSINESS_GROUP_ID NUMBER;
LV_ORGANIZATION_ID NUMBER;
LV_SET_OF_BOOKS_ID NUMBER;
BEGIN
SELECT BUSINESS_GROUP_ID,ORGANIZATION_ID,SET_OF_BOOKS_ID
INTO LV_BUSINESS_GROUP_ID,LV_ORGANIZATION_ID,LV_SET_OF_BOOKS_ID
FROM HR_OPERATING_UNITS
WHERE NAME='Vision Operations';
DBMS_OUTPUT.PUT_LINE(LV_BUSINESS_GROUP_ID||','||LV_ORGANIZATION_ID||','||LV_SET_OF_BOOKS_ID);
END;
/
DECLARE
LV_VENDOR_ID NUMBER;
BEGIN
SELECT VENDOR_ID
INTO LV_VENDOR_ID
FROM AP_SUPPLIERS
WHERE VENDOR_NAME='TP1 Supp';
DBMS_OUTPUT.PUT_LINE(LV_VENDOR_ID);
END;
/
DECLARE
LV_DESCRIPTION VARCHAR2(100);
LV_INVOICE_ID NUMBER;
BEGIN
SELECT INVOICE_ID,NVL(DESCRIPTION,'TEXT') DESCRIPTION
INTO LV_INVOICE_ID,LV_DESCRIPTION
FROM AP_INVOICES_ALL
WHERE INVOICE_ID='10005';
DBMS_OUTPUT.PUT_LINE(LV_INVOICE_ID||','||LV_DESCRIPTION);
END;
/
DECLARE
LV_PO_LINE_ID NUMBER;
LV_PO_HEADER_ID NUMBER;
LV_ITEM_DESCRIPTION VARCHAR2(240);
LV_UNIT_PRICE NUMBER;
BEGIN
SELECT PO_LINE_ID,PO_HEADER_ID,ITEM_DESCRIPTION,UNIT_PRICE
INTO LV_PO_LINE_ID,LV_PO_HEADER_ID,LV_ITEM_DESCRIPTION,LV_UNIT_PRICE
FROM PO_LINES_ALL
WHERE PO_HEADER_ID=1;
DBMS_OUTPUT.PUT_LINE(LV_PO_LINE_ID||','||LV_PO_HEADER_ID||','||LV_ITEM_DESCRIPTION||','||LV_UNIT_PRICE);
END;
/
DECLARE
LV_INVOICE_ID NUMBER;
LV_VENDOR_ID NUMBER;
LV_VENDOR_SITE_ID NUMBER;
LV_INVOICE_CURRENCY_CODE VARCHAR2(20);
LV_PAYMENT_CURRENCY_CODE VARCHAR2(20);
LV_INVOICE_AMOUNT NUMBER;
BEGIN
SELECT INVOICE_ID,VENDOR_ID,VENDOR_SITE_ID,INVOICE_CURRENCY_CODE,PAYMENT_CURRENCY_CODE
INTO LV_INVOICE_ID,LV_VENDOR_ID,LV_VENDOR_SITE_ID,LV_INVOICE_CURRENCY_CODE,LV_PAYMENT_CURRENCY_CODE
FROM AP_INVOICES_ALL
WHERE INVOICE_NUM='ERS-9163-109073';
DBMS_OUTPUT.PUT_LINE(LV_INVOICE_ID||','||LV_VENDOR_ID||','||LV_VENDOR_SITE_ID||','||LV_INVOICE_CURRENCY_CODE||','||LV_PAYMENT_CURRENCY_CODE);
END;
/
DML:
*****
DROP TABLE EMP1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE 1=2;
/
SELECT * FROM EMP1;
/
DROP TABLE DEPT1;
/
CREATE TABLE DEPT1 AS SELECT * FROM DEPT WHERE 1=2;
/
SELECT * FROM DEPT1;
/
BEGIN
DBMS_OUTPUT.PUT_LINE('EMP DETAILS');
INSERT INTO EMP1 VALUES(1,'A','HR',NULL,SYSDATE,2000,NULL,10);
INSERT INTO EMP1 VALUES(2,'B','SALES',7000,SYSDATE,3000,NULL,20);
INSERT INTO EMP1 VALUES(3,'C','CLERK',NULL,SYSDATE,4000,NULL,30);
COMMIT;
DBMS_OUTPUT.PUT_LINE('DEPT DETAILS');
INSERT INTO DEPT1 VALUES(10,'RESEARCH','DALLAS');
INSERT INTO DEPT1 VALUES(20,'SALES','CHICAGO');
COMMIT;
END;
/
TRUNCATE TABLE EMP1;
/
TRUNCATE TABLE DEPT1;
/
BEGIN
DBMS_OUTPUT.PUT_LINE('EMP DETAILS');
INSERT INTO EMP1 VALUES(1,'A','HR',NULL,SYSDATE,2000,NULL,10);
INSERT INTO EMP1 VALUES(2,'B','SALES',7000,SYSDATE,3000,NULL,20);
INSERT INTO EMP1 VALUES(3,'C','CLERK',NULL,SYSDATE,4000,NULL,30);
COMMIT;
DBMS_OUTPUT.PUT_LINE('DEPT DETAILS');
INSERT INTO DEPT1 VALUES(10,'RESEARCH','DALLAS');
INSERT INTO DEPT1 VALUES(20,'SALES','CHICAGO');
COMMIT;
--UPDATE
UPDATE EMP1
SET SAL=20000
WHERE EMPNO=2;
COMMIT;
--DELETE
DELETE FROM DEPT1 WHERE DEPTNO=20;
ROLLBACK;
END;
/
TRUNCATE TABLE DEPT1;
/
--INSERT
BEGIN
INSERT INTO DEPT1
VALUES(60,'HR','HYD');
DBMS_OUTPUT.PUT_LINE('INSERTED');
COMMIT;
END;
/
--UPDATE
BEGIN
UPDATE DEPT1
SET LOC='UK'
WHERE DEPTNO=60;
--
DBMS_OUTPUT.PUT_LINE('UPDATED');
--
END;
--DELETE
BEGIN
DELETE FROM DEPT1
WHERE DEPTNO=60;
--
DBMS_OUTPUT.PUT_LINE('DELETED');
--
END;
/
TRUNCATE TABLE EMP1;
/
TRUNCATE TABLE DEPT1;
/
BEGIN
INSERT INTO DEPT1 VALUES (10,'HR','HYD');
DBMS_OUTPUT.PUT_LINE('INSERTED SUCCESFULLY');
COMMIT;
----UPDATE
UPDATE DEPT1
SET DEPTNO=20
WHERE DEPTNO IS NULL;
DBMS_OUTPUT.PUT_LINE('UPDATED SUCCESFULLY');
COMMIT;
--DELETE
DELETE FROM DEPT1
WHERE DEPTNO=10;
COMMIT;
DBMS_OUTPUT.PUT_LINE('DELETED SUCCESFULLY');
END;
/
TRUNCATE TABLE EMP1;
/
TRUNCATE TABLE DEPT1;
/
--MERGE
BEGIN
MERGE INTO DEPT D
USING DEPT1 D1
ON (D.DEPTNO=D1.DEPTNO)
WHEN MATCHED THEN
UPDATE SET LOC=D1.LOC
WHEN NOT MATCHED THEN
INSERT(DEPTNO,DNAME,LOC) VALUES (D1.DEPTNO,D1.DNAME,D1.LOC);
DBMS_OUTPUT.PUT_LINE('MERGED');
END;
/
TCL:
*****
--ROLLBACK
BEGIN
INSERT INTO DEPT1 (DEPTNO,DNAME,LOC)
VALUES('60','HR','INDIA');
ROLLBACK;
--
DBMS_OUTPUT.PUT_LINE('ROLLBACK');
--
END;
/
--COMMIT
BEGIN
INSERT INTO DEPT1 (DEPTNO,DNAME,LOC)
VALUES('60','HR','INDIA');
COMMIT;
--
DBMS_OUTPUT.PUT_LINE('INSERTED+COMITTED');
--
END;
/
No comments:
Post a Comment