Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database without having to make any code changes.
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
1.
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/
2.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Examples:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment