Background

In this post we will write and execute actual SQL code. Lets start from very basics and move onto some advance stuff.

Note : This SQL queries are in fact PL/SQL and will be executed on oracle database 11g R2. I am using sqlfiddle site to test the queries. You can also do so.

Table Creation

Lets first create a database to work on. I am going to create a database named EXPENDITURE with columns as NAME representing the name of person whose expense is and the column AMOUNT which is the amount due for a person.

create table EXPENDITURE (NAME varchar(255), AMOUNT int);

Add Primary key constraint and Insert operation

Now lets go ahead and set constraints of primary key on our table . Primary key is a column of set of columns that uniquely define a row. In our case it will be column NAME. So there cannot be two rows in the table with same entry for NAME. Also lets add some dummy values to our table -

Note : Unfortunately DESCRIBE does not seem to work on JSFiddle. It's because "describe" is not a part of SQL. It is something that Oracle's sqlplus shell implements, not the server. So as an alternative we can do -

Explanation - In above code we have first defined a public procedure ADD_EXPENSE. From this procedure we are calling a private procedure DOUBLE_EXPENSE which doubling the expense amount using a private function called double. Finally in DOUBLE_EXPENSE procedure we are simply inserting the value into the EXPENDITURE table. To check the output we are calling this public proc ADD_EXPENSE and then printing the table.

Note : I have used "//" as a query terminator. You can use ';'. You have a list of options in SQLFiddle.

Background

SQL as we all know stands for structured Query language. SQL Statements can be categorized as -

DDL (Data definition language)

DML (Data manipulation language)

DCL (Data Control Language)

TCL (Transaction Control)

In this post we will see what these statements are and what are the differences between the. This post is written in the context of PL/SQL which is scripting language for oracle DB. So some of the statements may be different or just related to oracle DB.

Definitions

DDL (Data definition language): As the name suggests these statements are used define database structure or schema.

Example -

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

COMMENT - add comments to the data dictionary

RENAME - rename an object.

DML (Data manipulation language): These statements are used to change or alter data with the database or schema.

Example -

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

MERGE - UPSERT operation (insert or update)

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

DCL (Data Control Language) : These statements are use to control access or priveledges.

Example-

GRANT - gives user's access privileges to database

REVOKE - withdraw access privileges given with the GRANT command

TCL (Transaction Control) : These statements control transactions like Commit and rollback.

Example-

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Differences

As the name and definition suggests DDL statements are for defining the schema or database where as DML statements are of manipulating the database data.

We can use where clause with DML statements but not with DDL statements.

DDL statements are auto commit meaning they cannot be rolled back where as DML statements can be rolled back.

During the execution of DDL command. DDL command would not copy the actual content to rollback tablespace, hence it is fast compared to DML command.

Difference between delete and truncate command ? - Interview Q

This is a very famous database interview question. If you know these commands truncate and delete - both are used to clean the data in the table.

Notice delete is a DML statement where as truncate is a DDL statement.

Hence with truncate you cannot use where clause and rollback is not possible. Delete will remove all row entries which will satisfy the where clause.

Also truncate is faster than delete as no entries are logged into rollback space.

Lastly delete will trigger all the DML triggers associated with delete which will not be the case with truncate.

Delete will lock each row for deletion whereas truncate locks the entire table.

Note : drop statement will drop the entire table including all of it's row entries.

Background

Whenever we write and run a set of SQL statements together they can be categorized in one of the following -

Stored procedures

User defined functions

Advantage?

A very common interview question. Why would we write a SQL stored procedure when we can do the same via JDBC calls. The most simple answer is to avoid network traffic. Instead of making multiple JDBC calls we just call the procedure and handle the business logic in it.

Consider a very simple scenario. You have to maintain records of the money other people owe you or the amount you owe to others. So you create a table with lets say column as name and amoutDue. Every time you make or receive payment you will have to fetch the data, compare it with current payment and then to addition or subtraction accordingly. This will increase your network calls leading to high network traffic. So instead you call a procedure with name and amount (positive or negative) and let the procedure handle the logic. So in procedure we would have logic to select row for the name provided., depending on the amount alter the amount column for the entry. If the amount is o then there is no need to keep the record/row in the table. So delete it.

There are other benefits as well like transaction management and error handling that can be done at stored procedure level but the major benefit is less network traffic. Another use case in centralized maintained . You have to change only the common stored procedure and the business logic will get reflected in all dependent applications.

Difference between Stored procedure and user defined Functions

A function can return only one value which is mandatory where as a stored procedure can have multiple (0 or more) output parameters.

Function can have only input parameters where as a stored procedure can have multiple input/output parameters.

We can use select as well as DML statements (insert/update/delete) in stored procedures but only select statement in a function.

We can call a function from within a procedure but we cannot call a procedure from a function.

We can do Exception handling as well as transaction management in procedures but we cannot do the same in functions.

Functions can be used in SQL statements anywhere in the WHERE/HAVING/SELECT clause where as stored procedures cannot be.

You can directly call a procedure and exec myUser.myPackage.myProc(myArg1,,myArg2). Function you can use it either in a query or call from a procedure.

Note : Above are very generic points and may not applicable to the database you use. For example in case of Oracle PL/SQL you have most of the functionality in function that is outlined as not possible in above points. So refer to the documentation of your database vendor for specific capabilities.

User defined Functions

Function in a general SQL context can be either -

System Defined

Scalar Functions (Eg. abs(), round(), upper(), lower() etc.)

Aggregate Functions(Eg. max(), min(), avg(), count() etc.) [generally used in group by clause]

Usage : SELECT *, MyUser.MyScalarFunction() FROM MyTable

User Defined

Scalar Function

Scalar function return single value due to
actions perform by the function.

Function can return value of any data type.

Usage : SELECT * FROM MyTableFunction()

Inline Table-Valued Function

These type of functions returns a table variable due to actions perform by function.

We can only use a single SELECT statement to return the value.

Multi-Statement Table-Valued Function

These type of functions returns a custom declared table
variable due to action perform by function.

Here we need to explicitly define the table schema to be returned. We can perform operations on this table to insert/update/delete and then return.

Note : You must have noticed by now that in above User Defined ->Multi-Statement Table-Valued Function we are using insert, update and delete but in the point mentioned at the top we have said we can only use select statement. I had mentioned this in note immediately after those points and will stress it once again those points are generic one and will vary from vendor to vendor. Above user defined functions are specific to SQL server database.

To see actual SQL queries for procedures and functions in PL/SQL refer -