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
Dividing the large table into different smaller part is known as the table partitioning and partitioned table can stored in the different table.
There are many advantages of Table Partitioning.
Increases the performance of servers and we can retrieve data from existing table easily.
Backup/Recovery operation is easy for DBA.
Note: We can divide the table into different part only with the during the creation of the table. Once table can create it is not possible to dividing the table in to different part. It means that unpartition the table cannot be partition.
There are various type of partitions technique supported by oracle.
1: Range Partition
2: Hash Partition
3: List Partition
4: Composite Partition
1: Range Partition: In this type of partition we can divide the table based on some range, generaly we can divide into numeric part.
Create table table_name(colum1 datatype(size), colum2 datatype(size), -------- column datatype(size) ) partition by range(column_name) (partition partition_name values less than (value) ----- partition partition_name values less than (value));
Example:
Create table table1(emp_id number(5), emp_name varchar2(20), emp_sal number(6)) partition by range (sal)(partition p1 values less than (5000), partition p2 values less than (10000));
Syntax to retrieve the data from specific partition
Select * from table_name partition (partition_name);
Select * from table1 partition (p1);
Select * from table1 partition (p2);
2: Hash Partition:Hash partitioning is a partitioning method where a hash key is used to distribute rows consistently across the different partitions of table. This is usually used where ranges aren't appropriate.
The hash Partitioning is a best choice than range partition when.
You do not know beforehand how much data will map into a given range.
Sizes of range partition would differ quite substantially.
Example:
create table table1 ( emp_id number(4), emp_name varchar2(30),emp_sal number(6)) partition by hash(emp_id) ( partition p1 tablespace emp1, partition p2 tablespace emp2, partition p3 tablespace emp3);
3: List Partition: List partitioning is a partitioning method where we specify a list of separate values for the partitioning key in the description for each partition.
Example:
create table table1(emp_id number(3), emp_name varchar2(15), join_month varchar2(10)) partition by list(join_month) (partition p1 values(‘jan’,’fab’,’march’,’apr’), partition p1 values(‘may’,’jun’,’jul’,’aug’), partition p1 values(‘sep’,’oct’,’nov’,’dec’);
A cross join is a Cartesian product in database.
Syntax and Example of Cross Join
We have Two given table name table1 and table2 and how the cross join.
Cross Join of These tables
select * from table1,table2; (non ansi type)
select * from table1 cross join table2; (ANSI type)
Output of non ansi type Cross Join
Output of ansi type Cross Join
There are three type of outer join i.e. Left, right and full outer join. These are given with example.
We have Two given table name table1 and table2, and use it for examples
You can use to join two tables, in tis join you will get matching values from given two tables and also you can get un-matched value s from left side table.
Example:
select * from table1 left outer join table2 on table1.dept_name=table2.dept_name;
or
select * from table1,table2 where table1.dept_name=table2.dept_name(+);
Output of left outer join 1st second method
Output of left outer join 2nd second method
in tis join you will get matching values from given two tables and also you can get un-matched value s from right side table.
Example:
select * from table1 right outer join table2 on table1.dept_name=table2.dept_name;
or
select * from table1,table2 where table1.dept_name(+) =table2.dept_name;
Output of right outer join 1st second method
Output of right outer join 2nd second method
In this join you will get matching values from given two tables and also you can join two datasets from left-to-right and right-to-left.
Example:
select * from table1,table2 where table1.dept_name=table2.dept_name;
or
select * from table1,table2 where table1.dept_name =table2.dept_name(+) union select * from table1,table2 where table1.dept_name(+) =table2.dept_name;
Output of Full outer join 1st second method
Output of Full outer join 2nd second method
A natural join will join two datasets on all matching column names, regardless of whether the columns are actually related in anything other than name.
Example: Table1 and table2 share common column name and natural join between two tables
select * from table1 natural join table2;
Output of natural join
In this join where the rows and column are matching or values are matching only those will be display as output. In this join we need to equal to operator in the where clause of select string.
These are two tables
These are two methods to join these two table
select * from table1 table2 where table1.dept_name=table2.dept_name;
select table1.emp_id,table1.emp_name,table1.salary,table2.adress from table1 inner join table2 using(dept_name);
select * from table1 inner join table2 using(dept_name);
Joins are a mechanism which is used to combine or merge one or more than one table at a time.
Some time your information may not complete in a single table then at this particular situation we need to combine the table with one another. Join are mainly divided into following type.
Left Outer Join
Right Outer Join
Full Outer Join
A Check Constraints allows an entered value to be checked against a set of defined conditions.
You can check student average marks between 0 and 60, and employee present in a year in days between 0 and 250. You can check your answer is either yes or no.
Example of Check Constraints
create table student(s_id number(10), s_name varchar2(15), status number(3) constraint st check(status=’pt’or status=’ft’));
create table student(Roll_no number(10) Unique, Student_name varchar(20), Dept_name varchar(5));
insert into student(Roll_no, Student_name, Dept_name) values(10001,'jhon','cse')
insert into student(Roll_no, Student_name, Dept_name) values(10002,'hari','it')
insert into student(Roll_no, Student_name, Dept_name) values(10001,'raju','ME')
insert into student(Roll_no, Student_name, Dept_name) values(null,'raju','ME')
In the Oracle implements the concepts of Constraint
A Constraint is a statement that describes some features of our data which is store in the Database.
When a Constraint is validate and enabled then database does not allow entering the data which is making statement to be false.
All the Constraint is stored in the data dictionary.
The Constraint also prevents the deletion of a table if there are any dependencies from the other table. Constraints enforce rules on the data whenever a row is inserted, updated, or deleted from a table. There are various type of Constraints.
1: NOT NULL Constraints
2: UNIQUE KEY Constraints
3: PRIMARY KEY Constraints
4: FOREIGN KEY Constraints
5: CHECK Constraints
Functions is sub program which is used to perform some operations
Functions are classified into two type:
1: System Defined Functions 2: User Defined Functions
System Defined Functions A Functions defined by the system is called system Defined Functions.
User Defined Functions A Functions defined by the user is called User Defined Functions.
In Oracle there are three types of Functions.
1: Single Row Functions: Single Row Functions return a value for every row that is processed in a query.
2: Group Functions: The Group functions are the group of rows of data based on the values returned by the query.
The Group Functions are used to calculate aggregate values like total or average, which return just one total or one average values after processing a group of rows.
Group Functions or Multiple Row Functions These are classified into following type
1. SUM()
2. AVG()
3. MIN()
4. MAX()
5. COUNT()
3: Null Functions: These functions are used on NULL values.
1: Single Row Functions
2: Numeric Functions
3: Character Function
4: Date Functions
5: Conversion Functions
Null Functions: These functions are used on NULL values (NVL)
NVL Function ( NVL() ) This Function is used in cases where we want to consider Null values as Zero.
NVL(expression1, expression2)
If expression1 is null NVL will return expression2. If expression2 is null NVL will return expression1.
If expression1 and 2 to the data type of expression1 and then compares it.
Select empno, nvl(deptno, 0)from emp;
Select empno, nvl(comm, 0)from emp;
Null values and zeros are not equivalent.
Data Control Languages are used to organize the database objects.
Data Control Languages is used to control the kind of a data access to the database. Data Control Languages has two commands.
Grant: This command gives right and privileges on database objects or schema.
Revoke: This commands remove the restrict user right or privileges on database objects.
Statement used to manage the changes by DML statements and it allows statements to be grouped together into transactions. In Transaction Control Language contains two commands.
1: Commit: it make durable changes done in the data.
2: Rollback: It restores data to its state prior to user changes.
COMMIT Command: it makes changes permanently.
create table student(Roll_no number(10) , Student_name varchar2(20), Dept_name varchar2(5));
ROLLBACK Commands: undo any changes made since transaction began.
create table student(Roll_no number(10) , Student_name varchar2(20), Dept_name varchar2(5));
This sub language is used to manipulating within the table like storing, updating, deleting and modifying. This Command contains the Four commands.
1: INSERT
2: SELECT
3: UPDATE
4: DELETE
This Command is used to retrieve the record from existing table.
* using this command we can retrieve all the record and also retrieve the specific record (using where clause).
Select * from table_name;
This command is used to insert record into the existing table: Using this command we can insert the record into the table by two methods.
1: Explicit Type.
2: Implicit Type.
1: Explicit Type: In this method, if user need to insert all the values in all the column, user cannot left any column.
insert into table_name values (val1, val2,val3…….valn);
create table student(Roll_no number(10) , Student_name varchar2(20), Dept_name varchar2(5));
insert into student values(1100,’Jhon’,’cse’);
Last values in table Student inserted;
2:Implicit Type: In this method we can enter the values at the required column.
Insert into table_name (col1,col2…coln) values (val1,val2….valn);
Insert into student(roll_no,dept_name)values(1200,’ME’);
.Last values in table Student inserted;
Note: If we do not enter any column in the table then column automatically take NULL. NULL means no values and it is not equal to ZERO or SPACE.
This Command is used to modify the data in the table. By using this command we can modify all the record in the table and also some specific record in the table(by using where clause).
update table_name set column name=value;
update student set student_name='raju';
Update command using where clause; by using where clause we can update any specific data.
.update table_name set column_name=value where column_name=value;
update student set student_name=’jhon’ where roll_no=1003;
If we want to mare column update at a time.
.update table_name set col1=value,col2=value------coln=value;
Update student set roll_no=1111,Student_name=’jhon’;
This command is used to delete the record in existing table. By using this command we can delete all the record and any specific record from the table (by using the where clause).
Delete from table name;
Delete form table1
This is first Sub Language of SQL which is used to defining the Database object like Table. View, Procedure, Function, and Trigger etc. in this sub language has predefine syntax for describing the data.
Data Definition Language (DDL) is used for create, altering, renaming and deleting the schema object from. This Sub Language Contains 5 Commands. Those are
This Command is used to create Database object such as table, view, synonyms etc.
create table table_name (column_1 datatype(size), column_2 datatype(size), column_3 datatype(size)…… column_n datatype(size));
create table student(Roll_no number(10) , Student_name varchar2(20), Dept_name varchar2(5));
Table must start with an alphabet and it contains minimum one and maximum 30 character, it does not allows space or any other special character such as _,#,$,0-9 numbers.
Oracle does not allow the reserve or data type in table names or columns name.
A Table has minimum 1 and maximum 1000 columns (from the oracle 9i onwards) A table can store minimum 0 record and maximum n numbers of records (capacity of HDD).
This Command is used to modify the structure of the table.
Using this command we can perform 4 different operations on the table.
This Command is used to increase or decrease the size of the column or we can change the data type of column.
Alter table table_name modify column name datatype(size);
Alter table student modify(student_name char(20));
This Command is used to Add one or more than one column in table.
This Command is used to change the column name and also table name.
This Command is used to remove the column from the exiting table.
Rename old_table_name to new_table_name;
Rename student to scholar;
This command is used to delete te record from the existing table permanently.
This command is used to remove the table form the database.
Drop table table_name;
Drop table student;
The structured Query Language is divided in to Four sub Language these are:
Each sub Language has our own commands and each Sub language is responsible for our own work.
For Example Data Definition Language is responsible for the define the Database Object such as create table and number of column and number of rows in a table and wich data type in which row. And the Data Manipulating Language give option to inserting deleting the values in table.
Operators in SQL Operator are the some special symbols which perform some operations on operands. There are various type of Operators
1: Arithmetic Operators: The Arithmetic Operators manipulate the numeric operands.
2: Character Operators
3: Logical Operators
4: Comparison Operators
5: Set Operators
A Data Type represents the which type of Data stores in memory.
1: Numeric Data types:This Data type is allows to enter numeric values such as integer or decimal value.
This Data type is further divided into type
1: Number (size):This data type allows us to enter integer values i.e. +ve or –ve. Suppose if we need to enter the Employee id or student serial no the then we use this type of data type.
2: Number (p,s): This Data type allows us to enter decimal values like average marks, Employee Salary then we use this datatype. Here P represents the precession and S represents scale.
2: Alphabets: This Data type allows us to enter character values, such as Employee name, Student name etc.
1: Char(size)
2: varchar(size)
3: Varchar2(size)
1: Char(size) This Data type is allows us to enter character values such as Student name, employee name etc. the maximum size of this data type is 1 to 2000 character and it always allocate the memory in static fashion.
Student_name char(20);2/3: varchar(size) /Varchar2(size): These two data type also allows us to enter character values and the maximumsize of the these data type is 1 to 4000 character and it always allocated the memory in dynamic fashion.
Note: The difference between the varchar and Varchar2 data type is varchar data type is developed by along with SQL whereas varchar2 data type is developed by Oracle Corporation.
Date: This data type is allowes us to enter date values in database such as employee joining date. And the default formate of date in SQL is DD-MM-YY The range of date formate in oracle data base is 1- JAN-4712BC to 31-DEC-9999 AD
Timestamp: This data type allowes us to enter both date and time the default formate of timestamp is DD-MM-YY HH:MM:SS
The Language Structured English Query Language (SEQUAL), and later SEQUEL becomes SQL.
The SQL is common language interface because SQL is only language to communicate with all type of databases such as DB2, Teradata, Oracle, etc
SQL (Structured Query Language) is used for communicating the database. It is a non-procedural language. According to ANSI (American National Standard Institute) it is the regular language for relational Database Management System. SQL is used to perform many tasks such as data retrieve, update, insert etc. in database.
1: It is a Non-Procedural Language.
A table is called First Normal form when each cell of the table contains precisely one value if we want to converting table into First Normal form there are some properties.
There is no any repeating group in the table.
Must have a unique primary key
In this Unnormalized Table EMP_ID is the primery key
The most important and widely used normalization forms are.
1:First Normal form
2: Second Normal form
3: Third Normal form
4: BCNF (Boyce - Codd Normal form)
An entity is an object or thought about which you want to store information in database.
Entity-Relational Model considers the real world consisting of a set of basic objects and that objects is called entity. And this shows the logical structure of database.
Entity-Relationship Diagram is a graphical structure of a database. Showing this graphical structure we use some special symbol that’s given.
| Rectangle |  |
Represents entity sets |
|---|---|---|
| Eclipses |  |
Represents attributes |
| Diamonds |  |
Represents relationship sets |
| Lines | Links attributes to entity sets and entity sets to relationship | |
| Double rectangle |  |
Represents weak entity sets |
| Double eclipses |  |
Represents Multivalued attributes |
| Dashed eclipses |  |
Represents derived attributes |
| Double lines | Represents total participation of an entity in a relationship set |
The Relational Model is introduced by a German Scientist Dr. E.F. Codd in 1970 on Mathematical concept known as relational Algebra.
Dr. E.F. Codd provides some Laws for Fully Functional Relational Database Management System these are given.
1: Relational Database Management A Relational database management system uses only its relational experiences to manage the data in his database.
2: Information Representation
3: Logical Accessibility.
4: Insert, update and delete
5: Representation of Null values
6: Catalog facilities
7: Data Language
8: View updatability
9: Physical data independence
10: Logical data independence
11: Database Distribution
12: Non Subversion
13: Integrity Constraints
The Object Oriented Model is based on collection of objects. An object contains values stored in instance variable within the object.
An object also contains bodies of code that operate on the object. These bodies of code are called methods objects that contain the same type of values and the same methods are grouped together into classes.
Advantages of Object Oriented Model
1:Applications require less code.
2: Application use more natural data model.
3: It provides higher performance management of objects and complex interrelationship between objects.
4: Object Oriented features improve productivity.
5: Data access is easy.
The Combination of objects infrastructure and set of relational is called as Object Relational Model.
Object Relational System combines the advantages of OOPs concepts and Relational Database features such as multiple view of data and high-level, non-procedural query language.
There are many Object Relational system available in market are DB2, oracle 9 and later, SQL server 7 etc.
The Relational Model is introduced by a German Scientist Dr. E.F. Codd in 1970 on Mathematical concept known as relational Algebra. In relational model represents data in the form of two dimension table. Each table represents the real world object or thing. The management of data into relational model is known as the logical view of the database.
The examples of relational model databases are Oracle, Microsoft SQL Server, MySQL, Sybase etc.
There are some features of relational model.
1: The relational model removed all parent child relationships and instead characterizes all data in the database as simple row/column tables of a data values.
2: A relation is similar to a table row /columns of data values.
3: Each table is an independent entity and there is no physical relationship between tables.
4: relational model is bsed on set theory.
5: Most data management system based on the relational model take support of query languages. These query language are in simple English language.
This Network Model support only the one-to-one and one-to-many type only.
These are advantges of the Network Model.
1: The basic object in this model are data items, data Aggregates, records, and Sets.
2: This Network Model is improvement on the previous model (Hierarchical Model).
3: In Network Model maintain multiple parent child relationship.
The hierarchical Model is introduced in the Information Management System(IMS) Devloped by IBM in 1968.
This hierarchical Model is like as a tree structure, used to construct a hierarchical record in the form of set of nodes and edges. The Relationship beetwen among data have parent-child.
This hierarchical model database is widely used in the first main frame database management system.
There are many advantages and disadvantages of hierarchical model database.
1: Ease to maintain and updates.
2: High speed of access to large database
3: Simplicity
4: Data Security
1: Implementation Complexity: A hierarchical model database is simple but complex to implement.
2: Database management problems
3: Lack of structural independence
The Database Model is known as the relational structure of Database method of storing, managing and manipulating the data that mode is said to database model.
These are some common Database base models are given.
1:Flat file Model
2:hierarchical Model
3:Network Model
4:Relational Model
5:Object Relational Model
6:Object Oriented Model
Nowadays the relational model is the very famous.
These are some basic need of database
1: The main goal of DBMS is to provide an effective environment to use in retrieving and storing database information.
2: Database systems are designed to manage large bodies of information.
3: The database system provides the safety of the information stored, despite system crashes or attempts at unauthorized access and if data is shared among several user the n system must maintain consistency.
What is Database ?
Database is a group of data. It holds information about one particular enterprise. A database management system contains of a collection of interrelated data and set of programs to access that data.
Or
Database is a Collection of information that is given by any enterprise. that can be process those through one or more application program by multiple user.
This enterprise may be individual or a complete corporation.
An Oracle database is a group of data which is treated as a single component. The main purpose of database is to storing and retrieving the.
The Oracle Database stores data logically in the form of table and in the form of data files ("datafiles"). Tables can contain numerous types of memory sections, such as Data sections, Index sections, etc. This Memory section in the one or more levels. Extents comprise groups of contiguous data blocks. Data blocks from the basic units of data storage.
Oracle Database is most famous Database Management System. It is a most famous RDBMS (Relational Database Management System) from the Oracle Corporation. The oracle database originally developed by Larry Ellison and his two friends and former co-workers, Bob Miner and Ed Oates in the year 1977. Oracle DB is one of the most reliable and widely-used relational database engines.
The Oracle Database stores data logically in the form of table and in the form of data files ("datafiles"). Tables can contain numerous types of memory sections, such as Data sections, Index sections, etc. This Memory section in the one or more levels. Extents comprise groups of contiguous data blocks. Data blocks form the basic units of data storage.
