--PLSQL VARIABLE ATTRIBUTES
A)COLUMN LEVEL ATTRIBUTES
B)ROW LEVEL ATTRIBUTES
COLUMN LEVEL ATTRIBUTES:(%TYPE)
********************************
-->In this method we are defining attributes for individual columns.
-->%TYPE is used to declare a variable with the same datatype as that of the column in a table.
-->This attribute is represented using %type
-->It is used to store one value at a time.
-->It is not posible to hold morethan one column values or row values.
-->This will hold data type and size at runtime.
Syntax:
*******
Variablename tablename.columnname%type;
/
DROP TABLE EMP1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
DECLARE
LV_ENAME VARCHAR2(10);
LV_SAL NUMBER;
BEGIN
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP1
WHERE EMPNO=7698;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
END;
/
ALTER TABLE EMP1 MODIFY ENAME VARCHAR2(12);
/
UPDATE EMP1
SET ENAME='BLAKE1234567'
WHERE EMPNO=7698;
/
DECLARE
LV_ENAME VARCHAR2(10);
LV_SAL NUMBER;
BEGIN
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP1
WHERE EMPNO=7698;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
END;
/
--It will throw the error to Overcome the problem by using %TYPE
/
DECLARE
LV_ENAME EMP1.ENAME%TYPE;
LV_SAL EMP1.SAL%TYPE;
BEGIN
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP1
WHERE EMPNO=&NO;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
END;
/
DECLARE
LV_DNO DEPT.DEPTNO%TYPE;
LV_DNAME DEPT.DNAME%TYPE;
LV_LOC DEPT.LOC%TYPE;
BEGIN
SELECT DEPTNO,DNAME,LOC
INTO LV_DNO,LV_DNAME,LV_LOC
FROM DEPT
WHERE DEPTNO=10;
DBMS_OUTPUT.PUT_LINE(LV_DNO||','||LV_DNAME||','||LV_LOC);
END;
/
--HR_OPERATING_UNITS
DECLARE
LV_NAME HR_OPERATING_UNITS.NAME%TYPE;
LV_ORGANIZATION_ID HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
BEGIN
SELECT NAME,ORGANIZATION_ID
INTO LV_NAME,LV_ORGANIZATION_ID
FROM HR_OPERATING_UNITS
WHERE NAME='Vision Operations';
DBMS_OUTPUT.PUT_LINE(LV_NAME||','||LV_ORGANIZATION_ID);
END;
/
DECLARE
V_ENO EMP.EMPNO%TYPE;
V_ENAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE;
V_MGR EMP.MGR%TYPE;
V_HIREDATE EMP.HIREDATE%TYPE;
V_SAL EMP.SAL%TYPE;
V_COMM EMP.COMM%TYPE;
V_DNO EMP.DEPTNO%TYPE;
BEGIN
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
INTO V_ENO,V_ENAME,V_JOB,V_MGR,V_HIREDATE,V_SAL,V_COMM,V_DNO
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(V_ENO||','||V_ENAME||','||V_JOB||','||V_MGR||','||','||V_HIREDATE||','||V_SAL||','||','||V_COMM||','||V_DNO);
END;
ROW LEVEL ATTRIBUTES:
*********************
-->%ROWTYPE Attribute is used to declare a record type that represents row in a table.
-->The record can store entire a row or some specific data selected from the table.
-->This will hold column name,data type and size at runtime.
-->It is Used to define the pl/sql variables dynamically according to the Table structure.
-->It is used to declare a record with the same datatype as found in a table or view.
Syntax:
*******
Variablename tablename%rowtype;
EX:
****
DECLARE
I EMP%ROWTYPE;
BEGIN
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
INTO I.EMPNO,I.ENAME,I.JOB,I.MGR,I.HIREDATE,I.SAL,I.COMM,I.DEPTNO
FROM EMP
WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.JOB||','||I.MGR||','||I.HIREDATE||','||I.SAL||','||I.COMM||','||I.DEPTNO);
END;
/
EX:
***
DECLARE
I EMP%ROWTYPE;
BEGIN
SELECT ENAME,SAL,HIREDATE,JOB
INTO I.ENAME,I.SAL,I.HIREDATE,I.JOB
FROM EMP
WHERE EMPNO=&NO;
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL||','||I.HIREDATE||','||I.JOB);
END;
/
DECLARE
I EMP%ROWTYPE;
BEGIN
SELECT *
INTO I.EMPNO,I.ENAME,I.JOB,I.MGR,I.HIREDATE,I.SAL,I.COMM,I.DEPTNO
FROM EMP
WHERE EMPNO=&NO;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.JOB||','||I.MGR||','||I.HIREDATE||','||I.SAL||','||I.COMM||','||I.DEPTNO);
END;
/
DECLARE
LV_EMPNO NUMBER :=&EMPNO;
I EMP1%ROWTYPE;
BEGIN
SELECT *
INTO I
FROM EMP1
WHERE EMPNO=LV_EMPNO;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.JOB||','||I.MGR||','||I.HIREDATE||','||I.SAL||','||I.COMM||','||I.DEPTNO);
END;
/
DECLARE
I EMP%ROWTYPE;
BEGIN
SELECT *
INTO I
FROM EMP
WHERE EMPNO=7698;
DBMS_OUTPUT.PUT_LINE(I.empno||','||I.ENAME||','||I.JOB||I.SAL||','||I.DEPTNO);
END;
/
DECLARE
ABC PO_HEADERS_ALL%ROWTYPE;
BEGIN
SELECT *
INTO ABC
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID=1;
DBMS_OUTPUT.PUT_LINE(ABC.PO_HEADER_ID||','||ABC.SEGMENT1||','||ABC.VENDOR_ID||','||ABC.TYPE_LOOKUP_CODE);
END;
/
DECLARE
I CE_BANK_BRANCHES_V%ROWTYPE;
BEGIN
SELECT BANK_NAME,BANK_BRANCH_NAME,BANK_PARTY_ID,BRANCH_PARTY_ID
INTO I.BANK_NAME,I.BANK_BRANCH_NAME,I.BANK_PARTY_ID,I.BRANCH_PARTY_ID
FROM CE_BANK_BRANCHES_V
WHERE BANK_NAME='Citibank'
AND BANK_BRANCH_NAME='Citibank Moscow';
DBMS_OUTPUT.PUT_LINE(I.BANK_NAME||','||I.BANK_PARTY_ID||','||I.BANK_BRANCH_NAME||','||I.BRANCH_PARTY_ID);
END;
/
DECLARE
ABC PO_HEADERS_ALL%ROWTYPE;
DEF AP_INVOICES_ALL%ROWTYPE;
I EMP%ROWTYPE;
LV_VENDOR_NAME AP_SUPPLIERS.VENDOR_NAME%TYPE;
LV_SEGMENT1 AP_SUPPLIERS.SEGMENT1%TYPE;
BEGIN
SELECT *
INTO ABC
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID=1;
DBMS_OUTPUT.PUT_LINE(ABC.PO_HEADER_ID||','||ABC.AGENT_ID||','||ABC.TYPE_LOOKUP_CODE);
SELECT *
INTO DEF
FROM AP_INVOICES_ALL
WHERE INVOICE_ID=145054;
DBMS_OUTPUT.PUT_LINE(DEF.INVOICE_ID||','||DEF.INVOICE_NUM||','||DEF.INVOICE_AMOUNT);
SELECT *
INTO I
FROM EMP
WHERE EMPNO=7782;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.JOB||','||I.MGR||','||I.HIREDATE||','||I.SAL||','||I.COMM||','||I.DEPTNO);
SELECT VENDOR_NAME,SEGMENT1
INTO LV_VENDOR_NAME,LV_SEGMENT1
FROM AP_SUPPLIERS
WHERE VENDOR_ID=30163;
DBMS_OUTPUT.PUT_LINE(LV_VENDOR_NAME||','||LV_SEGMENT1);
END;
No comments:
Post a Comment