TRIGGERS:
Trigger is a stored procedure which is called implicitly by oracle engine whenever a insert, update or delete statement is fired.
Advantages of database triggers:---> Data is generated on it's own---> Replicate table can be maintained---> To enforce complex integrity contraints---> To edit data modifications---> To autoincrement a fieldetc..
Syntax: Create or replace trigger --triggername-- [before/after] [insert/pdate/delete] on --tablename-- [for each satement/ for each row] [when --condition--] plus..begin.and exception
Triggers are of following type:
before or after trigger ....and for each row and For each statement trigger...
before trigger is fired before insert/update/delete statement while after trigger is fired after insert/update/delete statement..
for each row and for each statements triggers are self explainatory..
There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words:
CURSORS
Cursor is a work area in pl/sql which is used to store the result of a query.
Each column value is pointed using pointer.
You can independently manipulate cursor values.
WORKING..... suppose you ask for a query stored in the server ... at first a cursor consisting of query result is created in server...now the cursor is transferred to the client where again cursor is created and hence the result is displayed......
TYPES: 2 types:
implicit and explicit.......
Implicit cursors are created by oracle engine itself Explicit cursors are created by the users.....
USAGE:cursors are generally used in such a case when a query returns more than one rows....normal pl/sql returning more than one rows gives error but using cursor this limitation can be avoided....so cursors are used....
Cursor attributes
%ISOPEN == returns true if ursor is open, false otherwise%FOUND == returns true if recod was fetched successfully, false otherwise%NOTFOUND == returns true if record was not fetched successfully, false otherwise%ROWCOUNT == returns number of records processed from the cursor.
Very important: Cursor can be controlled using following 3 control statements.
They are Open, Fetch, Close.....
open statement identifies the active set...i.e. query returned by select statement...
close statement closes the cursor...and
fetch statement fetches rows into the variables...
Cursors can be made into use using cursor for loop and fetch statement...
3. INDEXES
Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
4. Isolation levels
An isolation level determines the degree of isolation of data between concurrent transactions.
The default SQL Server isolation level is Read Committed.
Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted,Read Committed, Repeatable Read, Serializable.
5. Types of joins:
INNER JOINs: Retrives common data from two tables -- intersection.
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in <>Employees that do not have matches in <>Orders, those rows will not be listed. EX: SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield OUTER JOINs:
OUTER JOINs are further classified as
1) LEFT OUTER JOINS: The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield
2) RIGHT OUTER JOINS: The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID
3)FULL OUTER JOINS
SELF JOIN : Self join is just like any other join, except that two instances of the same table will be joined in the query
INNER JOINs: Retrives common data from two tables -- intersection.
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in <
OUTER JOINs are further classified as
1) LEFT OUTER JOINS: The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield
2) RIGHT OUTER JOINS: The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID
3)FULL OUTER JOINS
SELF JOIN : Self join is just like any other join, except that two instances of the same table will be joined in the query
6. PACKAGES
A package is an oracle object, which holds other objects within it.
Objects commonly held within a package are procedures, functions, variables, constants, cursors and exceptions.
//Packages in plsql is very much similar to those packages which we use in JAVA......yeah!! java packages holds numerous classes..right!!!...
A package has 2 parts..... package specification and package body
A package specification part consists of all sort of declaration of functions and procedures
while package body consists of codings and logic of declared functions and procedures...
--specificationcreate or replace package pack2 isfunction rmt(x in number) return number;procedure rmt1(x in number);end;
--bodycreate or replace package body pack2 is function rmt(x in number) return number isbegin return (x*x);end;
A package is an oracle object, which holds other objects within it.
Objects commonly held within a package are procedures, functions, variables, constants, cursors and exceptions.
//Packages in plsql is very much similar to those packages which we use in JAVA......yeah!! java packages holds numerous classes..right!!!...
A package has 2 parts..... package specification and package body
A package specification part consists of all sort of declaration of functions and procedures
while package body consists of codings and logic of declared functions and procedures...
--specificationcreate or replace package pack2 isfunction rmt(x in number) return number;procedure rmt1(x in number);end;
--bodycreate or replace package body pack2 is function rmt(x in number) return number isbegin return (x*x);end;
7. primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined.
But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.
Another major difference is that, primary key doesn't allow NULLs, but Unique key allows one NULL only.
Both primary key and unique enforce uniqueness of the column on which they are defined.
But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.
Another major difference is that, primary key doesn't allow NULLs, but Unique key allows one NULL only.
8. PROCEDURES AND FUNCTIONS
procedure is a subprogram...which consists of a set of sql statement.
A stored procedure or function is a named pl/sql code block that have been compiled and stored in one of the oracle engines's system tables
Syntax for stored procedure:
CREATE OR REPLACE PROCEDURE [schema] procedure name (argument { IN, OUT, IN OUT} data type, ..) {IS, AS}variable declarations; constant declarations; BEGINpl/sql subprogram body;EXCEPTIONexception pl/sql block;END;
Syntax for stored function:
CREATE OR REPLACE FUNCTION [schema] functionname(argument IN data type, ..) RETURN data type {IS, AS}variable declarations; constant declarations; BEGINpl/sql subprogram body;EXCEPTIONexception pl/sql block;END;
procedure is a subprogram...which consists of a set of sql statement.
A stored procedure or function is a named pl/sql code block that have been compiled and stored in one of the oracle engines's system tables
Syntax for stored procedure:
CREATE OR REPLACE PROCEDURE [schema] procedure name (argument { IN, OUT, IN OUT} data type, ..) {IS, AS}variable declarations; constant declarations; BEGINpl/sql subprogram body;EXCEPTIONexception pl/sql block;END;
Syntax for stored function:
CREATE OR REPLACE FUNCTION [schema] functionname(argument IN data type, ..) RETURN data type {IS, AS}variable declarations; constant declarations; BEGINpl/sql subprogram body;EXCEPTIONexception pl/sql block;END;
9. PROCEDURES AND FUNCTIONS
procedure is a subprogram...which consists of a set of sql statement.
A stored procedure or function is a named pl/sql code block that have been compiled and stored in one of the oracle engines's system tables
Syntax for stored procedure:
CREATE OR REPLACE PROCEDURE [schema] procedure name (argument { IN, OUT, IN OUT} data type, ..) {IS, AS}variable declarations; constant declarations; BEGINpl/sql subprogram body;EXCEPTIONexception pl/sql block;END;
Syntax for stored function:
CREATE OR REPLACE FUNCTION [schema] functionname(argument IN data type, ..) RETURN data type {IS, AS}variable declarations; constant declarations; BEGINpl/sql subprogram body;EXCEPTIONexception pl/sql block;END;
procedure is a subprogram...which consists of a set of sql statement.
A stored procedure or function is a named pl/sql code block that have been compiled and stored in one of the oracle engines's system tables
Syntax for stored procedure:
CREATE OR REPLACE PROCEDURE [schema] procedure name (argument { IN, OUT, IN OUT} data type, ..) {IS, AS}variable declarations; constant declarations; BEGINpl/sql subprogram body;EXCEPTIONexception pl/sql block;END;
Syntax for stored function:
CREATE OR REPLACE FUNCTION [schema] functionname(argument IN data type, ..) RETURN data type {IS, AS}variable declarations; constant declarations; BEGINpl/sql subprogram body;EXCEPTIONexception pl/sql block;END;
10. QUERY PERFORMANCE
This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.
Download the white paper on performance tuning SQL Server from Microsoft web site. Don't forget to check out sql-server-performance.com
This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.
Download the white paper on performance tuning SQL Server from Microsoft web site. Don't forget to check out sql-server-performance.com
11. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% TYPE : provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
12. constraints:
NOT NULL,
CHECK,
UNIQUE,
PRIMARY KEY,
FOREIGN KEY