Tuesday, 11 October 2011

PLSQL Concept

{\rtf1\ansi\ansicpg1252\deff0\deflang16393{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fnil\fcharset0 Calibri;}}
{\*\generator Msftedit 5.41.21.2509;}\viewkind4\uc1\pard\li360\tx1080\lang1033\b\f0\fs24 % Type & % Row Type\par
\pard\b0\fs22\par
\pard\li720\qj 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. \par
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:\par
\par
\tab DECLARE\par
\tab v_EmpRecord emp%ROWTYPE;\par
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:\par
\par
\tab DECLARE\par
\tab v_EmpNo emp.empno%TYPE;\par
\par
\pard\li360\tx1080\b\fs24 Instead of Triggers\par
\pard\li360\b0\fs22\par
\pard\li720 Views are commonly used to separate the logical database schema from the physical schema. Unfortunately the desired transparency often falls short in the case of UPDATE, DELETE or INSERT operations, since all but the simplest views are not updatable.\par
Instead Of Trigger execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.\par
Instead of Trigger can\rquote t be written at Statement Level.\par
\pard\li360\par
\pard\li360\tx1080\b\fs24 Tables/Views (Difference)\par
\pard\b0\fs22\par
\pard\li720 V\b iews\b0 are customized presentations of data in one or more tables or other views. A view can also be considered a stored query. Views do not actually contain data. Rather, they derive their data from the tables on which they are based, referred to as the \b base tables\b0 of the views.\par
Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view.\par
Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.\par
This is the whole list of reasons to use views:\line\line 1) to provide an additional level of table security by restricting access to a predetermined set of rows and/or columns of a table \line\line 2) to hide data complexity \line For example, a single view might be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables. \line to simplify commands for the user \line For example, views allow users to select information from multiple tables without actually knowing how to perform a join. \line 3) to present the data in a different perspective from that of the base table \line For example, the columns of a view can be renamed without affecting the tables on which the view is based.\line 4) to isolate applications from changes in definitions of base tables \line For example, if a view's defining query references three columns of a four column table and a fifth column is added to the table, the view's definition is not affected and all applications using the view are not affected. \line 5) to express a query that cannot be expressed without using a view \line For example, a view can be defined that joins a GROUP BY view with a table, or a view can be defined that joins a UNION view with a table. For information about GROUP BY or UNION, see the Oracle8 Server SQL Reference. \line 6) to save complex queries \line For example, a query could perform extensive calculations with table information. By saving this query as a view, the calculations can be performed each time the view is queried. \line 7) to achieve improvements in availability and performance \line For example, a database administrator can divide a large table into smaller tables (partitions) for many reasons, including partition level load, purge, backup, restore, reorganization, and index building. Once partition views are defined, users can query partitions, rather than very large tables. This ability to prune unneeded partitions from queries increases performance and availability.\par
\pard\par
\pard\sa200\sl276\slmult1\lang9\f1\par
}

No comments:

Post a Comment