Friday, 3 October 2014

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

Background

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

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 |


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.


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.




No comments:

Post a Comment

t> UA-39527780-1 back to top