Home arrow Oracle arrow Page 3 - Working With Oracle on Windows: Part 3
ORACLE

Working With Oracle on Windows: Part 3


This article is a quick summary of basic SQL commands with a little touch of Oracle to prepare for application development.

Author Info:
By: Ben Shepherd
Rating: 4 stars4 stars4 stars4 stars4 stars / 14
March 10, 2003
TABLE OF CONTENTS:
  1. · Working With Oracle on Windows: Part 3
  2. · Getting ready to use Oracle
  3. · SQL Summary
  4. · Conclusion

print this article
SEARCH DEVARTICLES

Working With Oracle on Windows: Part 3 - SQL Summary
(Page 3 of 4 )

Idea of the Select Statement

The Select statement is a useful query that allows users to retrieve data from a database. There is an infinite set of commands to retrieve data and a finite set of rules. In basic cases, the sequence of the process to retrieve data –that is the query - is using the three clauses SELECT, FROM and WHERE.

SELECT is the clause that allows users to retrieve data from the query.

FROM is the clause that represents the tables required in the query.

WHERE is the clause that represents a condition that the query must undergo.

But First ...

We wish to extract data from a table that is in a relational database. Here is how this can be done. This file (i.e. bank_example.sql) will be included in the zip file attached to this article.

CREATE OR REPLACE TYPE holder_address AS OBJECT
(
          v_streetNumber                      NUMBER,
          v_streetName                         VARCHAR2(30),
          v_citySuburb                          VARCHAR2(30),
          v_state                                  VARCHAR2(4),
          v_postCode                            NUMBER
);

CREATE TABLE account_holder
(
          v_id                                       NUMBER               PRIMARY_KEY,
          v_firstName                           VARCHAR2(30),
          v_lastName                            VARCHAR(40),
          v_homeAddress                      holder_address,
          v_workAddress                       holder_address,
          v_emailAddress                      VARCHAR2(50)
);

CREATE TABLE payment_details
(
          v_id                                               NUMBER       PRIMARY_KEY,
          v_holder_id                                    NUMBER,
          v_amount                                       NUMBER(7,2),
          FOREIGN KEY (v_holder_id) REFERENCES account_holder(v_id)
);

Notice that v_amount is a seven digit number with 2 decimal places. And that v_holder_id is linked to the v_id column in account_holder table. We now have a relational database.

Use of Sequences and Triggers

Let us put values in to these tables, but first, we will create a trigger that allows the primary key to be unique. To do this we need a sequence. So let’s create one.

create sequence primary_seq_for_uniqueness
start with 1
increment by 1
nomaxvalue;

This will let us have an auto increment like sequence called primary_seq_for_uniqueness (I’d call it something smaller in length so there is less chance for typing errors in the code). This sequence you have now created is important for the trigger which will allow us to make sure that we have a unique primary key (i.e. payment). Let’s now create that trigger.

create trigger primary_trig_for_uniqueness
before insert on payment_details
for each row
begin
select primary_seq_for_uniqueness.nextval into :new.v_id from dual;
end;

Ok, we have a trigger that is called before an insert into the payment_details table. The trigger calls the sequence and gets a new number (i.e. 1 more than it’s last value).

Inserting data

Now we insert information into the table as follows (see insert_statement.sql in zip file),

INSERT INTO account_holder VALUES
(
00001,
 ‘I.P’,
‘Daily’,
holder_address(21, ‘Kings Street’, ‘Junkville’, TN, 12345),
holder_address(12, ‘Queens Street’, ‘Klenville’, OR, 54321)
);

INSERT INTO account_holder VALUES
(
00002,
 ‘Kim’,
‘Short’,
holder_address(123, ‘Pitts Lane’, ‘Adelaide’, ‘SA’, 1234),
holder_address(321, ‘Fred Forest Parkway’, ‘Reid’, ‘SA’, 4321)
);

INSERT INTO account_holder VALUES
(
00003,
 ‘Boris’,
‘Lungstrom’,
holder_address(1234, ‘Two Gorges Road’, ‘Jenki’, ‘TO’, 123),
holder_address(4321, ‘Piza Place’, ‘Jenki’, ‘TO’, 321)
);


INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00001, 100.50);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00001, 2004.65);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00001, 0.90);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00001, 5.65);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 56.20);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 102.00);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 65.90);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 12.60);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 998.75);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 45.50);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00002, 34.70);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 23.20);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 12.95);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 13.45);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 0.05);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 69.75);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 423.25);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 581.15);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 1000.00);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 20.50);
INSERT INTO payment_detail(v_holder_id,v_amount) VALUES (00003, 54.85);

Of course, when we are working with applications we don’t need to re-enter these amounts the app will do it for us … phew!!

The Select Statement

Now we have our tables, let’s use the select statement to retrieve some data. Firstly, recall that the character * is used to indicate “all records”. So if we wish to gather all records of the holders who have paid a deposit that was greater than $1000.00, then the expression would like this

SELECT  account_holder.*
FROM account_holder, payment_detail
WHERE payment_detail.v_amount > 1000.00;

Notice that there is a list of all the tables that were required, i.e. in the FROM clause. Also, the SELECT clause uses the * character. If I wanted to display all records in both tables the SELECT statement would have been,

SELECT *
FROM account_holder, payment_detail
WHERE payment_detail.v_amount > 1000.00;

This statement is without the reference to a particular table in the SELECT clause.

The select statement could be used to view all tables in your system by typing,

SELECT table_name from user_tables;

The SELECT statement is a powerful tool when used correctly, so experiment with it.  You could also use built in Oracle function to produce calculations. For example, to can tally up a SUM, COUNT to number of entries and the list goes on. There are many SQL books out there, so give some of them a read and your pick up the language in no time.

Other Basic SQL Statements

To view certain user created objects,

SELECT sequence_name FROM user_sequences;
SELECT trigger_name FROM user_triggers;
SELECT table_name FROM user_tables;

To delete,

DROP SEQUENCE test_seq;
DROP TRIGGER test_trigger;
DROP TABLE account_holder CASCADE CONSTRAINTS;
DROP TABLE payment_detail CASCADE CONSTRAINTS;

You could keep the trigger but disable it so it won't automatically populate the id column with every insert and enable it again later if you want (not wise though):

ALTER TRIGGER test_trigger DISABLE;
ALTER TRIGGER test_trigger ENABLE;

To alter a table by changing a column name simply type

ALTER TABLE payment_details RENAME COLUMN v_amount TO v_deposit;

To update a record

UPDATE account_holder SET id = 00004 WHERE id = 00003;


blog comments powered by Disqus
ORACLE ARTICLES

- Oracle Purchases Taleo to Extend Cloud Compu...
- Partitioning in Oracle. What? Why? When? Who...
- Datatypes Used in Oracle 9i
- Creating a Database in Oracle 9i
- Oracle with Doctor Janusz Getta
- Working With Oracle on Windows: Part 3
- Working With Oracle on Windows: Part 2

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials