Architecture of PL/SQL
A PL/SQL compilation and run time system is an engine that compiles and executes PL/SQL blocks and programs. That engine installed in an Oracle server or in application development tool. The Pl/SQL engine accepts as input any valid blocks or program. The PL/SQL engine processing an anonymous block, and PL/SQL engine executes procedural but sends SQL statements to the SQL engine in the Oracle Database.
Architecture of PL/SQL
PL/SQL stands for procedural Language extension of SQL. PL/SQL is the combination of the SQL along with the some procedural features of programing language. The PL/SQL is the developed by the ORACLE Corporation for improving the ability of the SQL.
Main Features of the PL/SQL
1: It Combine the Data manipulating power of SQL with the power of procedural Language.
2: We can define, variable, Procedures, and functions in Pl/SQL.
3: We can break complex problems into easily understandable procedural code.
4: Code can be reused for multiple applications.
There are many advantage of the PL/SQL
Block Structures
Procedural Language Capability
Better Performance
Error handling
Portable and Transaction Processing Language
A Subquery is a query inside a query. In Oracle, you can make sub queries inside your main statements. The Outside query is called as main query and inner query is called as subquery.
These subqueries can exist in the WHERE clause, the From Clause and the SELECT clause, A subquery is basically a select statement which is used instead of another statement.
1: WHERE clause: Most frequently subquery will be start with the WHERE clause. These subqueries are also called Nested subqueries. There are 255 sub-queries in oracle.
2: FORM clause: Subqueries are also found in the FORM clause. These are called inline views.
3: SELECT clause:
An index is a performance-tuning method of allowing faster retrieval of records. Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table.
An index creates an entry for each value that appears in the indexed columns. By default, by default, Oracle creates B-tree indexes.
There are many types of indexes in Oracle all designed for different circumstances:
A Sequence is a database object that generates unique numbers; mostly Sequence is used for the primary key values. A Sequence is an object in oracle database that used to generates the number Sequence and this number Sequence useful when you need to create a unique number which act as primary key.
The Sequence of number can be generates in either ascending or descending order.
Create sequence<sequence_name> increment by <integer> start with<integer>maxvalue <integer> minvalue<integer>cache<integer>;
CACHE: Cache the specified number of sequence values into buffer in the SGA. This speed access, but it lost when database shutdown. The default values is 20 and we give maximum value=maxvalue - minvalue.
Create sequence seq_1 minvalue1 maxvalue 99 start with 1 increment by 1 cache 20;
OR
Create sequence seq_2 increment by 1 start with 1 maxvalue 1000 minvalue 1 cache 20;
A View is object consisting of stored query, it doesn’t contain data. Views are logical tables of data extracted from existing tables. A view can be thought of as a stored query or virtual table; we can views in most places where a table can be used.
A View is a method of organizing table data a specified need and View are baesd on SELECT statements, which derive their data real tables.
Use of View in oracle
1: It is used for hiding sensitive columns.
2: It is used for hiding complex queries which is involving multiple tables.
3: Views can be created with check option and prevents the updating of other rows and columns.
4: Views can be offer an extra level of table security by limiting to a predetermined set of rows and/or columns of a table.
5: Views isolate applications from changes in definitions of base tables.
6: View provides data in a different side than of a base table by renaming columns without affecting the base table.
Create or replace view view_name as sql_query;
Create or replace view view_1 as select e_name, e_dept from table_1;
Type of Views
1: Simple Views
2: Complex Views
3: Read-only Views
4: Inline Views
5: Force Views
What is a Materialized View?
It is a database object that stores the results of a query, and it can be stored in the same database as its base table or different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.
Materialized view give the indirect access to table data by storing the results of a query in a distinct schema object. Not like as ordinary view, which is does not take up any storage space or contain any data.
The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. In updatable materialized view you can insert, update, and delete the data.
Features of Materialized View
1: In can be partitioned and indexed
2: Can be queried directly
3: Can have DML applied against it
4: Several refresh options are available
5: Best in read-intensive environments
