SQL

SQL Data Type and Operator Data Type A data type is a set of data with values having predefined characteristics. Each language has its own data types. Usually, a limited number of such data types come built into a language. The language usually specifies the range of values for a given data type, how the values are processed by the computer, and how they are stored. Some data typesinSQL are: char, number, date etc. The various Data Types along with their description are shown below:   Operators Oracle provides a set of built-in operators-Arithmetic Operators (+, -, *,), Comparison Operators (=, >, <), Logical operators (NOT, AND, OR). These operators take as input one or more arguments (operands) and return a result. The various Operators along with their description are shown below in tabular form:   Data Definition Language (DDL) SQL can be termed as a DDL i.e. Data Definition Language. The DDL part in SQL enables it to create/delete/alter a database table. Through this DDL feature indexes, links between tables as well as constraints can be defined. In this section we will be using commands that would enable us to CREATE, ALTER or DROPa database, table, index etc. This section also describes SELECT INTO which is used create a back up of a database table. The commands have been described with appropriate example one at a time covering all the clauses.  SQL CreateCreate a DatabaseCREATE statement can be used to create a database. A database is nothing but a collection of tables which are related to each other. The inter-relation amongst the tables in a database is created by attributes/fields which may be common to more than one table. Syntax:CREATE DATABASE database_name  Create TableWith CREATE TABLE; a new table can be created. A table can be considered as a basic structure within which data is stored. It consists of rows and columns. A row represents a single record while a column represents attributes/fields which can be thought of as one of the components contributing to make a record. While creating a table, data types have to be mentioned for each field. Here are the most common Data Types:  The following statements/commands consist of DDL portion of SQL:Create Table—- creates a new database table. Alter Table : alters a database table. Drop Table : deletes a database table. Create Index : creates an index. Drop Index : deletes an index. Structured Query Language
The Structured Query Language (SQL) is a computer language for accessing and manipulating databases.
The fundamental concept is to think of the data as being stored in one or more tables. When a request is made to retrieve data from these tables, which is called a “query”, the resultant output is also presented in as table.
There are many different versions of the SQL language, but to be in compliance with the ANSI SQL ’92 Standard, they must use and support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others.
Data Manipulation Language (DML) SQL can be termed as DML i.e. Data Manipulation Language. Database is manipulated so as to insert, update and delete records. CREATE, ALTER or DROP a database, table, index etc. This section also describes SELECT INTO which is used create a back up of a database table. The commands have been described with appropriate example one at a time covering all the clauses.  SQL CreateCreate a DatabaseCREATE statement can be used to create a database. A database is nothing but a collection of tables which are related to each other. The inter-relation amongst the tables in a database is created by attributes/fields which may be common to more than one table. Syntax:CREATEDATABASE database_name  Create TableWith CREATE TABLE, a new table can be created. A table can be considered as a basic structure within which data is stored. It consists of rows and columns. A row represents asingle record while a column represents attributes/fields which can be thought of as one of the components contributing to make a record. While creating a table, data types have to be mentioned for each field. Here are the most common Data Types:  The following statements/commands consist of DDL portion of SQL:Select : Retrieves desired records from a database table. Update : Updates/modifies records in a database table. Delete : Deletes records from a database table. Insert into: Inserts new records into a database table.

What is Database? A database is an organized collection of data that is useful to us. The data inside in a database can be modified, deleted or new data can be added. They are stored in the form of records as shown in the table below:

 The above table shows a single record. The columns-Associate_TID, Associate_name, Age ,Commission are the fields or attributes, while the row containing the corresponding the values-900678432, Smith, 28, 25000 is a single record. There can be a number of records in a table and number of tables in a database. The tables in a database are related to each other through one/more attributes/fields.  A Database is diagrammatically shown below. It consists of two tables-Associate andAsso_INFO. While Associate stores information regarding an Associate TID, dept and salary, the table Associate_INFO stores TID, name and date of join of the Associate. The common link between the tables is established by the field Asociate-TID.

  SQL Database TablesAs explained above, database is nothing but a collection of inter-related tables. A table consists of rows and columns. Rows are the records stored whereas columns are attributes or fields. Each table is identified with a name e.g, Associate (Associate_Tid, Associate_name, Age, Commission), suggesting Table named Associate has four fields.

 

DBMS DBMS (Database Management System) consists of inter-related data and methods to manipulate those data. It is designed to help users in managing data in a database efficiently. A database management system (DBMS), sometimes also called as a database manager, is a program that lets one or more computer users create and access data in a database. The DBMS manages user requests, as well as requests from other programs, so that users and other programs are not bothered to understand where the data is physically located on storage media and, in a multi-user system, who else is also be accessing the data. In handling user requests, the DBMS takes care that integrity of the data (that is, making sure it continues to be accessible and is consistently organized as intended) and security (making sure only those with access privileges can access the data) remain intact. The most typical DBMS is a Relational Database Management System (RDBMS). A DBMS is usually an inherent part of a database product. Some of the popular DBMSs (actually they are all Relational DBMS) are-Oracle’s line of database management products, Sybase’s products, Microsoft’s SQL Server. On PCs, Microsoft Access is a popular example of a single-or small-group user DBMS.

What is SQL?
SQL, an acronym for Structured Query Language, is an ANSI (American National Standard Institute) standard computer language. SQL statements are used for accessing and manipulating data from these database systems. It works with RDBMS like Oracle, Sybase, Microsoft SQL Server, Access, Ingress etc.
SQL:
• Run queries against a database.
• retrieves data from database.
• inserts new records into a database.
• updates records in a database.
• deletes records from a database.
• creates/deletes tables in a database.
• alters the table structure in a database.
• is easy to learn and understand.

Data Control Language (DCL) In this section we will be using commands that control the behavior of database objects. Although DCL form a part of PL/SQL part, they have been described in brief here. The commands have been described one at a time, covering all the clauses. The commands have been described with appropriate example one at a time covering all the clauses before we go on to discuss Data Control Language (DCL) commands, let’s understand the concept of transaction.  What is a Transaction?A transaction consists of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements is executed. For example, when a shopkeeper updates the amount of a particular product sold each week, he will also want to update the total amount sold to date. However, he will not want to update one without also updating the other; otherwise, the data will be inconsistent. The way to ensure that either both actions occur or neither action occurs is to use a transaction. Now we come to various DCL commands which make use of transactions. SavepointWhen a transaction is rolled back to a savepoint all changes made after that savepoint are undone. Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, then the earlier savepoint is erased. After a savepoint has been created, you can continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint. SAVEPOINT Savepoint_nameThe above statement sets a named transaction savepoint whose name is savepoint_name.  RollbackThe Rollback Statement terminates the current transaction and rescinds all changes made under the transaction. It rolls back the changes to the database. Thus, Rollback statement is used to undo work done in the current transaction.ROLLBACK [TO SAVEPOINT savepoint_name] The above statement rolls back a transaction to the SAVEPOINT named savepoint_name. Modifications that this transaction made to rows after the savepoint was set, are undone in the rollback.

The SQL SELECT Statement The SELECT statement is used to select data from a given table. The result is stored in a result table Syntax

  Example

 The table name “eBIZ”

 The Result

 To Select All ColumnsTo select all columns from the “eBIZ” table, use a * symbol as place of column names.

 The Result

 The Result SetThe result from a SQL query is stored in a result-set. SemicolonSemicolon is the specific way to separate each SQL statement in database that allow more than one statement to be executed in the same call. Use of DISTINCT StatementThe DISTINCT keyword is used to return only different values. The SELECT statement returns information from table columns. But what if we only want to select different elements? Syntax

 Use DISTINCT keyword with exampleTo select ALL values from the column named “Company” we use a SELECT statement

  Take table“eBIZ student”

 Result

Note that “Upendra Mishra” is two times in the result. To select only DIFFERENT values from the column ” Paid student “ we use a SELECT DISTINCT statement

 Result

 Now “Upendramishra” is listed only once in the result

Introduction to PL/SQL
Use of WHERE Keyword
The WHERE keyword is used to specifying a selection standard.
About WHERE Clause
To putingconditiona, select data from a table, a WHERE can be added to the SELECTstatement.
Syntax
The following operators can be used in when we use “Where” clause
Using the WHERE into select statement
To select persons name who are living in the city “Banglore”, we add a WHERE to theSELECT statement:
Table name “Associate”
Result
Quotes
we have to used single quotes around the conditional values in the examples.
SQL uses single quotes around text values (). Numeric values should not be enclosed in quotes.

UPDATE DefinitionThe UPDATE statement is used to modify the data in available table. Syntax

  Table name “Associate”

  Use “Update” keyword to Update one Column in a RowTo add a Last name to the person with a First name of “Sunil”.

 Result

  Update several Columns in a RowChange the address and add the name of the city:

 Result

 

DELETE Statement The DELETE statement is used to delete rows in a table where many rows are given in table. Syntax

  Table name “Associate”

  Delete a Row“Ravish” is going to be deleted.

 Result

  Delete All RowsAll rows can deleted without deleting table.It means that the table structure, attributes, and indexes will be over there

 

 

Use Of INSERT INTO The INSERT INTO is used to insert new rows into table. Syntax

 You can also allocate the columns for which you want to insert data in specific location.

  New Row InsertingThis “Associate” table:

 Write Statement :

 Result of Statement.

  Inserting Data/values In SpecificeColumn.Table name “Associate” Syntax

 Result.

 

Use of ORDER BY
The “ORDER BY” is used to sorting the result from table.
“CORRIER DETAILS” TABLE NAME
Example
To shorting the courier service names in alphabetical order:
RESULT
Example
To shorting the courier service names in alphabetical order and the POD Number in numerical order:
SYNTAX
RESULT
Example
To shorting the courier service names in reverse alphabetically order:
SYNTAX
RESULT
Example
To shorting the courier service names in reverse alphabetical order AND the POD Number in numerical order:
RESULT
AND & OR
We use AND and OR in WHERE clause when join two or more conditions.The AND/OR operator displays a row if ALL conditions listed are true.
Table Name is “associate record”
Example
Use AND to display each person with the first name equal to “Priya” the last name equal to “seth”.
RESULT
Example
Use OR to display each person with the first name equal to “suchi” the last name equal to”singh”.
RESULT
Example
You can also combine AND and OR (use parentheses to form complex expressions):
RESULT

ND & OR We use AND and OR in WHERE clause when join two or more conditions.The AND/OR operator displays a row if ALL conditions listed are true. Table Name is “associate record”

 ExampleUse AND to display each person with the first name equal to “Priya” the last name equal to “seth”.

 RESULT

 ExampleUse OR to display each person with the first name equal to “suchi” the last name equal to“singh”.

 RESULT

 ExampleYou can also combine AND and OR (use parentheses to form complex expressions):

 RESULT

 

Use of IN The IN can be used if know the exact valuewhich you want to return for at least one of the columns.

  Original Table (used in the examples)

 Example 1To display the persons with FirstName equal to “niidhi” or ”Deepti” use the follwing sql statement.

 Result:

 

SQL BETWEEN BETWEEN … ANDThe BETWEEN … AND operator selects a range of data between two values. These values can be numbers, text, or dates.

  Original Table (used in the examples)

 

 Result:

  IMPORTANT! The BETWEEN…AND operator is treated differently in different databases. With some databases a person with the LastName of “seth” or “mittal” will not be listed (BETWEEN..AND only selects fields that are between and excluding the test values). Example 2To display the persons outside the range used in the previous example, use the NOT operator:

 Result:

 

SQL Alias With SQL, aliases can be used for column names and table names. Column Name AliasThe syntax is:

  Table Name AliasThe syntax is:

 Example: Using a Column AliasThis table (Persons):

 And this SQL:

 Returns this result:

  Example: Using a Table AliasThis table (Persons):

 And this SQL:

 Returns this result:Table Employees:

 

SQL JOIN Joins and KeysSometimes we have to select data from two or more tables to make our result complete. We have to perform a join. Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. Each primary key value must be unique within the table.The purpose is to bind data together, across tables, without repeating all of the data in every table. In the “Employees” table below, the “Employee_ID” column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name. When you look at the example tables below, notice that:• The “Employee_ID” column is the primary key of the “Employees” table • The “Prod_ID” column is the primary key of the “Orders” • The “Employee_ID” column in the “Orders” table is used to refer to the persons in the“Employees” table without using their names Employees:

  Orders:

  Referring to Two TablesWe can select data from two tables by referring to two tables, like this: ExampleWho has ordered a product, and what did they order?

 Result

 ExampleWho ordered a printer?

 Result

  Using JoinsOR we can select data from two tables with the JOIN keyword, like this: Example INNER JOINSyntax

 Who has ordered a product, and what did they order?

 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. Result

 Example LEFT JOINSyntax

 List all employees, and their orders – if any.

 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. Result

 Example RIGHT JOINSyntax

 List all orders, and who has ordered – if any.

 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. Result

 ExampleWho ordered a printer?

 Result

 

SQL UNION and UNION ALL UNIONThe UNION command is used to select related information from two tables, much like theJOIN command. However, when using the UNION command all selected columns need to be of the same data type. Note: With UNION, only distinct values are selected.

 Employees_India:

 Employees_USA:

  Using the UNION CommandExampleList all different employee names in INDIA and USA:

 Result

 Note: This command cannot be used to list all employees in India and USA. In the example above we have two employees with equal names, and only one of them is listed. The UNION command only selects distinct values. UNION ALLThe UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

 

SELECT INTO Statement Use of SELECT INTO StatementThe SELECT INTO statement is used to create backup copies of tables Syntax

 To Make a Backup CopyThe following example makes a backup copy of the “Persons” table:

 The IN clause can be used to copy tables into another database:

 If you only want to copy a few fields, you can do so by listing them after the SELECT statement:

 You can also add a WHERE clause. The following example creates a “Persons_backup”table with two columns (FirstName and LastName) by extracting the persons who lives in“Sandnes” from the “Persons” table:

 Selecting data from more than one table is also possible. The following example creates a new table “Empl_Ord_backup” that contains data from the two tables Employees and Orders:

 

QL CREATE VIEW Statement
What is a View?
In SQL, a VIEW is a virtual table based on the result-set of a SELECT statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from a single table.
Note: The database design and structure will NOT be affected by the functions, where, or join statements in a view.
Syntax
Note: The database does not store the view data! The database engine recreates the data, using the view’s SELECT statement, every time a user queries a view.
Using Views
A view could be used from inside a query, a stored procedure, or from inside another view. By adding functions, joins, etc., to a view, it allows you to present exactly the data you want to the user.
The sample database Northwind has some views installed by default. The view “Current Product List” lists all active products (products that are not discontinued) from the Products table. The view is created with the following SQL:
We can query the view above as follows:
Another view from the Northwind sample database selects every product in the Products table that has a unit price that is higher than the average unit price:
We can query the view above as follows:
Another example view from the Northwind database calculates the total sale for each category in 1997. Note that this view select its data from another view called “Product Sales for 1997”:
We can query the view above as follows:
We can also add a condition to the query. Now we want to see the total sale only for the category “Beverages”:

Nested Queries In this section we discuss nested queries and why they are used. Nested QueriesA nested query is a query that ‘NESTS’ inside a query, implying a query residing inside another query. The nested queries are also termed as Subqueries. The statement/query containing/enclosing a subquery is called as he parent query. The parent query makes use of the rows/records returned by the sub query. Subqueries are used:• to create tables/views • to insert records in a table • to update records in a table • to supply values in SELECT/UPDATE/DELETE statements where conditions are specified using clauses-WHERE, HAVING, IN etc To explain the subqueries further using examples we would be referring two tables-Studentand Students_Sports as shown below:

 Student_Sports

 From the two tables above, suppose we want to retrieve the names of all students who do not play sports. We key in the following query for that:SELECTS_id, S_name FROM Student

WHERE S_id NOT IN

(SELECT S_id FROM Student_Sports) In the above statement the query within parenthesis is the subquery/nested query. A subquery is always executed first and its output provides the input for the parent query.Thus in the above example, subquery is executed first as shown below:  SELECT S_id FROM Student_SportsThe data retrieved by the above select statement will be passed to the WHERE CLAUSE of the parent query as in:S_id NOT IN (0012, 0013,0014, 0016) Thus the final query after replacing the inner queries with retrieved values will be: SELECT S_id, S_name FROM Student
WHERE S_id NOT IN (0012, 0013,0014, 0016)Which will yield the following output:

 Lets take up a few examples, using some of the operators, that are commonly used withnested queries.

QL Create Database, Table, Create a DatabaseTo create a database:

  Create a TableTo create a table in a database:

  ExampleThis example demonstrates how you can create a table named “Person”, with four columns. The column names will be “LastName”, “FirstName”, “Address”, and “Age”:

  This example demonstrates how you can specify a maximum length for some columns:

  The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:

 

Delete a Table or Database To delete a table (the table structure, attributes, and indexes will also be deleted):

 To delete a database:

  Truncate a TableWhat if we only want to get rid of the data inside a table, and not the table itself? Use theTRUNCATE TABLE command (deletes only the data inside the table):

 

Create Index Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Note: Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexesonly on columns that are often used for a search.  A Unique IndexCreates a unique index on a table. A unique index means that two rows cannot have the same index value.

 The “column_name” specifies the column you want indexed. A Simple IndexCreates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.

 The “column_name” specifies the column you want indexed. ExampleThis example creates a simple index, named “PersonIndex”, on the LastName field of the Person table:

 If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:

 If you want to index more than one column you can list the column names within theparentheses, separated by commas:

 

Drop Index You can delete an existing index in a table with the DROP INDEX statement. Syntax for Microsoft SQLJet (and Microsoft Access):

  Syntax for MS SQL Server:

  Syntax for IBM DB2 and Oracle:

  Syntax for MySQL:

 

QL ALTER TABLE
ALTER TABLE
The ALTER TABLE statement is used to add or drop columns in an existing table.
Note: Some database systems don’t allow the dropping of a column in a database table (DROP COLUMN column_name).
“ASSOCIATE” TABLE NAME
Example
To add a column named “City” in the “Person” table:
Result:
Example
To drop the “Address” column in the “Person” table:
Result:

What is SQL Function? SQL has a lot of built-in functions for counting and calculations. Functions serve the purpose of performing operations on data such as manipulation of data items and returning a result. Functions accept variables or constants, supplied by user and operate upon them. Such variables or constants are called ARGUMENTS. SQL has several in-built functions which are used for this purpose. They can be classified as:

Aggregate functions Aggregate functions operate against a collection of values, but return a single value. Note: If used among many other expressions in the item list of a SELECT statement, theSELECT must have a GROUP BY clause!! Following are SQL Aggregate Functions with their description:

 Syntax:SELECTfunction_name(column_name) FROM table_name Example for the Employee table below:

 We key in the querySELECTAvg(Salary) FROM Employee will yield the result set that shows the average salary of the employees:

 Similarly we can use the sum function to Add all the numeric values in the selected table by typing in the following query:

 Lets take an example using Max function to find out the maximum value from the field-Age. We type the following query:SELECT Max(Age) FROM Employee; which results in:

 Similarly we can use MIn function to find the minimum value from a column We key in another query which is slightly different from others because he function that we use here i.e, count(*), is not supplied with any column, as shown below:SELECT Count(*) FROM Employee The above statement returns total number of rows in the Employee table i.e

 

Scalar functions Scalar functions operate against a single value, and return a single value based on the input value. We group the scalar functions into three categories and discuss them one by one. The three categories are: Numeric functions
String Functions
Conversion functions  AboutDualBefore explaining the various Scalar functions, lets understand first, what a Dual is and its use. A Dual is a small worktable. It consists of one row and one column only and supports arithmetic calculations, date retrieval and it’s formatting. There are situations when arithmetic calculations are to be done such as 14*26, where no table is referenced as only numeric literals are used. However to display the output on screen, SQL has SELECT option which makes mentioning a table name mandatory in its FROM clause. In such cases we use Dual. It is a virtual/dummy table provided by Oracle to facilitate such calculations. In our examples using Scalar functions, we will make use of Dual.  Numeric functionsGiven below is the list of some Numeric Functions with their Descriptions:

 We display the examples using the above functions and their respective output in the tabular form given below:

 Notice in the above table, we have used ‘dual’ as table name.  String FunctionsGiven below is the list of some commonly used STRING FUNCTIONS with their Descriptions:

  We display the examples using the above functions and their respective output in the tabular form given below:

  We display the examples using the above functions and their respective output in the tabular form given below:

 

SQL GROUP BY and HAVING Aggregate functions (like SUM) often need an added GROUP BY functionality. GROUP BY…GROUP BY… was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BYfunction it was impossible to find the sum for each individual group of column values. The syntax for the GROUP BY function is:

  GROUP BY ExampleThis “Sales report” Table:

  And This SQL:

  Returns this result:

 The above code is invalid because the column returned is not part of an aggregate. AGROUP BY clause will solve this problem:

  Returns this result:

  HAVING…HAVING… was added to SQL because the WHERE keyword could not be used againstaggregate functions (like SUM), and without HAVING… it would be impossible to test for result conditions. The syntax for the HAVING function is:

  This “Sales report” Table:

  This SQL:

  Returns this result

 

What is PL/SQL? PL/SQL is Oracle’s procedural language extension to SQL. PL/SQL allows you to mix SQL statements with procedural statements like IF statement, Looping structures etc. PL/SQL is the superset of SQL. It uses SQL for data retrieval and manipulation and uses its own statements for data processing.PL/SQL program units are generally categorized as follows: ” Anonymous blocks ” Stored procedures Anonymous blockThis is a PL/SQL block that appears within your application. In many applications PL/SQL blocks can appear where SQL statements can appear. Such blocks are called as Anonymous blocks. Stored ProcedureThis is a PL/SQL block that is stored in the database with a name. Application programs can execute these procedures using the name. Oracle also allows you to create functions, which are same as procedures but return a value, and packages, which are a collection of procedures and functions.

PL/SQL Engine Every PL/SQL block is first executed by PL/SQL engine. This is the engine that compiles and executes PL/SQL blocks. PL/SQL engine is available in Oracle Server and certain Oracle tools such as Oracle Forms and Oracle Reports. PL/SQL engine executes all procedural statements of a PL/SQL of the block, but sends SQL command to SQL statements executor in the Oracle RDBMS. That means PL/SQL separates SQL commands from PL/SQL commands and executes PL/SQL commands using Procedural statement executor, which is a part of PL/SQL engine

eatures of PL/SQL The following are important features of PL/SQL.  Block structurePL/SQL is a block-structured language. Each program written in PL/SQL is written as a block. Blocks can also be nested. Each block is meant for a particular task.Variables and constantsPL/SQL allows you to declare variables and constants. Variables are used to store values temporarily. Variables and constants can be used in SQL and PL/SQL procedural statements just like an expressionControlstructuresPL/SQL allows control structures like IF statement, FOR loop, WHILE loop to be used in the block. Control structures are most important extension to SQL in PL/SQL. Control structures allow any data process possible in PL/SQLExceptionhandlingPL/SQL allows errors, called as exceptions, to be detected and handled. Whenever there is a predefined error PL/SQL raises an exception automatically. These exceptions can be handled to recover from errors.ModularityPL/SQL allows process to be divided into different modules. Subprograms called as procedures and functions can be defined and invoked using the name. These subprograms can also take parametersCursorsA cursor is a private SQL area used to execute SQL statements and store processing information. PL/SQL implicitly uses cursors for all DML commands and SELECT command that returns only one row. And it also allows you to define explicit cursor to deal with multiple rowqueries.Built-in functionsMost of the SQL functions that we have seen so far in SQL are available in PL/SQL. These functions can be used to manipulate variables of PL/SQL.Built-in functionsMost of the SQL functions that we have seen so far in SQL are available in PL/SQL. These functions can be used to manipulate variables of PL/SQL.

Advantages Of PL/SQL The following are a few important advantages of PL/SQL. Moreover most of the features listed above are also advantages of PL/SQL. Support for SQL PL/SQL also allows SQL statements to be constructed and executed on the fly. The process of creating SQL statements on the fly is called as Dynamic SQL. This is different from writing SQL commands at the time of writing the program, which is called as Static SQL. Starting from Oracle8i, PL/SQL support native dynamic SQL, which makes programming Dynamic SQL easier than its predecessor, where we used DBMS_SQL package. Better performance PL/SQL block is sent as one unit to Oracle server. Without PL/SQL each SQL command is to be passed to Oracle server, which will increase network traffic heavily. As a collection of SQL statements is passed as a block to Oracle server, it improves performance.Portability Applications written in PL/SQL are portable to any platform on which Oracle runs. Once you write a program in PL/SQL, it can be used in any environment without any change at all.

PL/SQL block PL/SQL programs are written as blocks. Block allows you to group logically related statements and declarations. PL/SQL block is consisting of the following three parts: .Declarative part This is the area of the block where variables, cursors etc are declared. All variables used in the block are to be declared in declarative part. PL/SQL Datatypes PL/SQL provides a variety of predefined datatypes, which can be divided into four categories: Scalar——– Represents a single value(NUMBER,CHAR,VARCHAR,DATE, BOOLEAN )Composite—– Is a collection of components(RECORD, TABLE and VARRAY)Reference—– Is a pointer that points to another item(REF CURSOR, REF Object_type)LOB—– Holds a lob locator(DBFILE, BLOB, CLOB, and NCLOB)  .Executable part Is the area where we write SQL and PL/SQL commands that are to be executed. This is the only mandatory part of the entire block.  .Exception-handling part Is the place where we handle exceptions (errors) that are raised in executable part. Exception handlers handle exceptions.

Comments in PL/SQL You can give comments in PL/SQL block in two ways. .First way is by preceding the comment with two hyphens (- -). Example: — this is single line comment . Second way is by starting the comment with /* and ending it with */. Example: /* this comment can be of multiple lines */ SELECT… INTOSQL*Plus displays the data retrieved by SELECT command. Whereas in PL/SQL SELECT command is used only to retrieve the data and storing and using data is to be done explicitly. So Oracle provided INTO clause with SELECT command that is used to specify the variable(s) into which the value(s) retrieved must be copied Declaring Constants Constant is a PL/SQL variable whose value doesn’t change. Any attempt to change the value of a constant will result in error. variable CONSTANT datatype [precision , scale] := expression; The following declarative statement creates a constant that takes value 500. bonus constant number(3) := 500;  Nesting Blocks It is possible to define a block within another block. When blocks are defined one within another they are said to be nested. Scope and visibility of variables- Scope of the variable refers to the region of the program in which the variable can be used. A variable is said to be visible when it can be referred without any qualifier. Available functions- Most of the functions available in SQL are also available in PL/SQLThe functions that are NOT available in procedural statements are:”DECODE””AVG, COUNT, GROUPING, MIN, MAX, SUM, STDDEV, and VARIANCE”However, these functions can be used with SQL commands and those SQL commands may be used in PL/SQL. Implicit cursors. Whenever a SQL statement is issued the Database server opens an area of memory in which the command is parsed and executed. This area is called a cursor. Microsoft tends to refer to cursors as datasets throughout much of their databse product documentation When the executable part of a PL/SQL block issues a SQL command, PL/SQL creates an implicit cursor which has the identifier SQL. PL/SQL manages this cursor for you.PL/SQL provides some attributes which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements. The SQL cursor attributes are : %ROWCOUNT —- The number of rows processed by a SQL statement.%FOUND—-TRUE if at least one row was processed.%%NOTFOUND—-TRUE if no rows were processed. .%ISOPEN—-TRUE if cursor is open or FALSE if cursor has not been opened or has been closed.  Explicit cursors. SELECT statements that occur within PL/SQL blocks are known as embedded, they must return one row and may only return one row. To get around this you can define a SELECT statement as a cursor (an area of memory), run the query and then manipulate the returned rows within the cursor. Cursors are controlled via four command statements.Theyare :- DECLARE—- Defines the name and structure of the cursor together with the SELECT statement that will populate the cursor with data. The query is validated but not executed.OPEN—-Executes the query that populates the cursor with rows.%FETCH—-Loads the row addressed by the cursor pointer into variables and moves the cursor pointer on to the next row ready for the next fetch.CLOSE —-Releases the data within the cursor and closes it. The cursor can be reopened to refresh its data. SELECT statements that occur within PL/SQL blocks are known as embedded, they must return one row and may only return one row. To get around this you can define a SELECT statement as a cursor (an area of memory), run the query and then manipulate the returned rows within the cursor. Cursors are controlled via four command statements.Theyare :- Exception (error) Handling Exceptions are identifiers in PL/SQL that are raised during the execution of a block to terminate its action. A block is always terminated when PL/SQL raises an exception but you can define your own error handler to capture exceptions and perform some final actions before quitting the block. If PL/SQL handles the exception within the block then the exception will not propagate out to an enclosing block or environment.There are two classes of exceptions, these are :-User-defined Declared by the user and raised when specifically requested within a block. You may associate a user-defined exception with an error code if you wish.Predefined– Oracle predefined errors which are associated with specific error codes.If an error occurs within a block PL/SQL passes control to the EXCEPTION section of the block. If no EXCEPTION section exists within the block or the EXCEPTION section doesn’t handle the error that’s occurred then the block is terminated with an unhandled exception. Exceptions propagate up through nested blocks until an exception handler is found that can handle the error. If no exception handler is found in any block the error is passed out to the host environment. Exceptions occur when either an Oracle error occurs (this automatically raises an exception) or you explicitly raise an error using the RAISE statement.