Basic PL/SQL syntax and query executions on Oracle 11g R2 database

Background

Today has been all about databases. Some posts written earlier today - 

• Difference between DML and DDL statements in SQL  
• Difference between a stored procedure and user defined function in SQL

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 - 

alter table EXPENDITURE ADD CONSTRAINT PRIMARY_KEY PRIMARY KEY (NAME);
insert into EXPENDITURE values ('JOHN',25);
insert into EXPENDITURE values ('SAM',127);
insert into EXPENDITURE values ('EDWARD',12);
insert into EXPENDITURE values ('PENNY',1024);

Now lets print the values in the database - 

select * from EXPENDITURE;


|   NAME | AMOUNT |
|--------|--------|
|   JOHN |     25 |
|    SAM |    127 |
| EDWARD |     12 |
|  PENNY |   1024 |

Add Secondary key constrain

Eg.

create table schema8.EMPLOYEE(ID int, name varchar2(255));
alter table schema8.EMPLOYEE ADD CONSTRAINT PRIMARY_KEY PRIMARY KEY (ID);
create table schema8.EMPLOYEE_SALARY(EMPLOYEE_ID int, department varchar2(255), salary int);
alter table schema8.EMPLOYEE_SALARY ADD CONSTRAINT FK_EMP_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES schema8.EMPLOYEE(ID);

Delete operation

Now lets perform a delete for entry with name ='SAM' and and check the table again.

delete from EXPENDITURE where NAME='SAM';


|   NAME | AMOUNT |
|--------|--------|
|   JOHN |     25 |
| EDWARD |     12 |
|  PENNY |   1024 |

Yup entry is deleted. You can also do a truncate which will remove all entries from the table or you can do drop  which will drop the entire table including all the entries in it.

Renaming and modifying column

Now lets rename the column AMOUNT to EXPENSE and modify the column NAME from varchar(255) to varchar(128) and say it can never be null. Finally lets describe the table to see the changes -

ALTER TABLE EXPENDITURE RENAME COLUMN AMOUNT to EXPENSE;

ALTER TABLE EXPENDITURE MODIFY NAME varchar(128) not null;

DESCRIBE EXPENDITURE;

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  -

ALTER TABLE EXPENDITURE RENAME COLUMN AMOUNT to EXPENSE;

ALTER TABLE EXPENDITURE MODIFY NAME varchar(128) not null;

select column_name, data_type, data_length, nullable from all_tab_columns where table_name = 'EXPENDITURE';

| COLUMN_NAME | DATA_TYPE | DATA_LENGTH | NULLABLE |
|-------------|-----------|-------------|----------|
|        NAME |  VARCHAR2 |         128 |        N |
|     EXPENSE |    NUMBER |          22 |        Y |

GROUP BY AND ORDER BY statements

select * from EXPENDITURE order by EXPENSE;


|   NAME | EXPENSE |
|--------|---------|
| EDWARD |      12 |
|   JOHN |      25 |
|  PENNY |    1024 |




select * from EXPENDITURE order by EXPENSE DESC;


|   NAME | EXPENSE |
|--------|---------|
|  PENNY |    1024 |
|   JOHN |      25 |
| EDWARD |      12 |




select NAME, max(EXPENSE) from EXPENDITURE group by NAME;


|   NAME | MAX(EXPENSE) |
|--------|--------------|
| EDWARD |           12 |
|   JOHN |           25 |
|  PENNY |         1024 |

Note : Since we have unique names (NAME being the primary key) each group is infact a row. So you would see the same result selecting all rows from a database.

NOTE : You cannot use aggregate functions in where clause.

Counting number of rows in a Table

You can do something like - 

SELECT COUNT(NAME) FROM EXPENDITURE;
| COUNT(NAME) |
|-------------|
|           3 |

Note : Use the primary key in the argument of COUNT() function. As primary key guarantees unique row output of above query will give you total number of rows in the table.

Lets move on to something more advance...

Packages, Procedures and Functions....

Consider following code - 

-- create table

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

//



--add primary key

alter table EXPENDITURE ADD CONSTRAINT PRIMARY_KEY PRIMARY KEY (NAME)

//



-- insert data

insert into EXPENDITURE values ('JOHN',25)

//

insert into EXPENDITURE values ('EDWARD',12)

//

insert into EXPENDITURE values ('PENNY',1024)

//

-- rename column

ALTER TABLE EXPENDITURE RENAME COLUMN AMOUNT to EXPENSE

//

-- change data type

ALTER TABLE EXPENDITURE MODIFY NAME varchar(128) not null

//



-- create package spec

create or replace package PKG_EXPENDITURE

is

    -- define one public procedure

    procedure ADD_EXPENSE(p_name in varchar, p_expense in int);

end;

//



-- create package body

create or replace package body PKG_EXPENDITURE

is

    -- private DOUBLE EXPENSE procedure

    procedure DOUBLE_EXPENSE(p_name in varchar, p_expense in int)

    is

    begin

        insert into EXPENDITURE values (p_name, p_expense);

    end;



    -- private function to return twice the input

    function double(p_number in int)

    return int

    is

    begin

        return 2 * p_number;

    end;

    

    procedure ADD_EXPENSE(p_name in varchar, p_expense in int)

    is

    begin

        DOUBLE_EXPENSE(p_name, double(p_expense));

    end;





end;

//

and now execute it as -

-- execute the public procedure of the package

begin

    PKG_EXPENDITURE.ADD_EXPENSE('Aniket',111);

end;

//



select * from EXPENDITURE

//

and you should see the output - 

|   NAME | EXPENSE |
|--------|---------|
|   JOHN |      25 |
| EDWARD |      12 |
|  PENNY |    1024 |
| Aniket |     222 |

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.

Related Links

• Remove duplicate rows from table in Oracle(OSFG)

Difference between DML and DDL statements in SQL

Background

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

1. DDL (Data definition language)
2. DML (Data manipulation language) 
3. DCL (Data Control Language)
4.  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 -
  
  
  1. CREATE - to create objects in the database
  2. ALTER - alters the structure of the database
  3. DROP - delete objects from the database
  4. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  5. COMMENT - add comments to the data dictionary
  6. RENAME - rename an object.


• DML (Data manipulation language): These statements are used to change or alter data with the database or schema.
  
  Example -
  
  
  1. SELECT - retrieve data from the a database
  2. INSERT - insert data into a table
  3. UPDATE - updates existing data within a table
  4. DELETE - deletes all records from a table, the space for the records remain
  5. MERGE - UPSERT operation (insert or update)
  6. CALL - call a PL/SQL or Java subprogram
  7. EXPLAIN PLAN - explain access path to data
  8. LOCK TABLE - control concurrency


• DCL (Data Control Language) : These statements are use to control access or priveledges.
  
  Example-
  
  
  1. GRANT - gives user's access privileges to database
  2. REVOKE - withdraw access privileges given with the GRANT command


• TCL (Transaction Control) : These statements control transactions like Commit and rollback.
  
  Example-
  
  
  1. COMMIT - save work done
  2. SAVEPOINT - identify a point in a transaction to which you can later roll back
  3. ROLLBACK - restore database to original since the last COMMIT
  4. SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
   
  

Differences

1. 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.
2. We can use where clause with DML statements but not with DDL statements.
3. DDL statements are auto commit meaning they cannot be rolled back where as DML statements can be rolled back.
4. 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.

Difference between a stored procedure and user defined function in SQL

Background

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

1. Stored procedures
2. 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

1. A function can return only one value which is mandatory where as a stored procedure can have multiple (0 or more) output parameters.
2. Function can have only input parameters where as a stored procedure can have multiple input/output parameters.
3. We can use select as well as DML statements (insert/update/delete) in stored procedures but only select statement in a function.
4. We can call a function from within a procedure but we cannot call a procedure from a function.
5. We can do Exception handling as well as transaction management in procedures but we cannot do the same in functions.
6. Functions can be used in SQL statements anywhere in the WHERE/HAVING/SELECT clause where as stored procedures cannot be.
7. 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 - 

1. System Defined
1. Scalar Functions (Eg. abs(), round(), upper(), lower() etc.)
2. Aggregate Functions(Eg. max(), min(), avg(), count() etc.) [generally used in group by clause]
3. Usage : SELECT *, MyUser.MyScalarFunction() FROM MyTable
2. User Defined
1. 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()
2. 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.
3. 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 -

• Experimenting with Oracle 11g R2 database

Related Links

• What is the difference between function and procedure in PL/SQL?
• User-Defined Functions in Microsoft SQl server
• Different Types of SQL Server Function with Examples