1 / 43

ORACLE - PowerPoint PPT Presentation

  • Uploaded on

ORACLE. Using ORACLE 8 SQL using ORACLE 8 PL/SQL using ORACLE 8. SQL Data Types. Numeric Data Types String Data Types Data/Time Data Types. SQL Data Types (Numeric). INTEGER signed integer 31 bits SMALLINT signed integer 15 bits

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'ORACLE' - jorn

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Oracle l.jpg


Using ORACLE 8

SQL using ORACLE 8


Sql data types l.jpg
SQL Data Types

  • Numeric Data Types

  • String Data Types

  • Data/Time Data Types

Sql data types numeric l.jpg
SQL Data Types (Numeric)

  • INTEGER signed integer 31 bits

  • SMALLINT signed integer 15 bits

  • DECIMAL(p, q) signed number p digits, q decimals

  • FLOAT(p) floating point number, p bits precision

Sql data types string l.jpg
SQL Data Types (String)

  • CHAR(n) fixed length string, of n-8 bits

  • VARCHAR(n) varying length string, up to n-8 bits

  • GRAPHIC(n) fixed length string, n-16 bits

  • VARGRAPHIC(n) varying length string n-16 bits

Sql data types date time l.jpg
SQL Data Types (Date/Time)

  • DATE date (yyyy-mmm-dd)

  • TIME time (hh:mm:ss)

  • TIMESTAMP combination of date and time

Basic table level operations l.jpg
Basic Table Level Operations

  • Creation: Using the create command

  • Populating tables: Entering values into the table using the insert command

  • Modifying data: Modifying data in the tables using the update command

  • Deleting data: Deleting data from tables using the delete command

  • Altering tables: Using the alter command

  • Deleting tables: Deleting tables using the drop command

Creating tables l.jpg
Creating Tables

create table<table_name>


<column1> <datatype> [constraint],

<column2> <datatype> [constraint],

… … …


Example using create l.jpg
Example using “create”

create table CD_MASTER


CD_NO number


CD_NAME varchar2(25),

ARTIST varchar2(25),

TYPE varchar2(15)


Inserting data l.jpg
Inserting Data

insert into<table_name>

(first_column, second_column, … last_column)

values (first_value, second_value, … );

Example using insert l.jpg
Example using “insert”

insert into CD_MASTER values (101, ‘Fields of Gold’, ‘Sting’, ‘Rock’);

insert into CD_MASTER values(102, ‘Supernatural’, ‘Santana’, ‘Rock’);

insert into CD_MASTER values (103, ‘Division Bell’, ‘Pink Floyd’, ‘Rock’);

Modifying data l.jpg
Modifying data


set<column_name> = <new_value>

where <condition>;

Deleting data l.jpg
Deleting Data

delete from<table_name>

where <SQL_condition>;

Altering table definitions l.jpg
Altering table definitions

alter table <table_name>

add | drop | modify

(<column specification[s]>);

Deleting tables l.jpg
Deleting tables

drop table<table_name>

[cascade constraints];

Alternate way of data entry l.jpg
Alternate way of data entry

  • Create tables from using the an SQL command (.SQL) file. Specify all the table definitions & constraints.

  • Create a “control” file (.CTL), giving specific instructions on interpreting a certain data file

  • List out all data in the data file

The sql command file l.jpg
The SQL command file

  • Specify the exact SQL commands for creating tables using the create statement, exactly the same way as on the SQL prompt

  • Drop a table before creating it

The control file l.jpg
The Control File


load data

infile <name of data file in single quotes>

into table <table name>

fields terminated by ',' optionally enclosed by '"'

(<field1>, <field2>, … , <last_field>)

Example control file l.jpg
Example control file

  • (save this in cdmaster.ctl)

    load data

    infile ‘cdmaster.dat’

    into table CD_MASTER

    fields terminated by ',' optionally enclosed by '"'


The data file l.jpg
The Data File

  • field1_val1, field2_val1, …

  • field1_val2, field2_val2, …

  • field1_val3, field2_val3, …

  • field1_val4, field2_val4, …

  • field1_val5, field2_val5, …

  • … … ...

Example data file l.jpg
Example data file

  • (save this in cdmaster.dat)

    101,‘Fields of Gold’,‘Sting’,‘Rock’


    103,‘Division Bell’,‘Pink Floyd’,‘Rock’

    104,'ABBA Gold','ABBA','Pop'

    105,'Unconditional','Classy Davidson','Country'

Running the scripts l.jpg
Running the scripts

  • telnet to

  • sqlplus name/password @<create.sql>

    • creates the tables in SQL

  • sqlldr name/password control=<ctrlfile.ctl>

    • populates the table specified in ctrlfile.ctl

    • Note:The filename should not be in quotes

Useful commands l.jpg
Useful commands

  • / : Executes the previous command

  • ed: Opens edit buffer for typing in and modifying commands

  • help: Online ORACLE help

  • password: To change the user’s password

The spooler l.jpg
The Spooler

  • Syntax:

    SPOOL [OFF | <filename>]

  • e.g. spool a.spl

    • saves the screen display into a file a.spl

  • e.g. spool off

    • turns off the spooler

Useful oracle sql commands l.jpg
Useful ORACLE-SQL commands

  • DESCRIBE: Shows the structure of a database table

  • Syntax:

    describe <table_name>;

    can be shortened to

    desc <table_name>;

The system table tab l.jpg
The system table “tab”

  • tab: It’s the table of tables, i.e. stores the names of all the tables created by the user

  • select * from tab;

    • lists all the tables created

The system table all objects l.jpg
The system table ALL_OBJECTS

  • Stores details of all the tables created by all the users on the system.

  • selectOWNERfrom ALL_OBJECTS;

    • lists all the owners on the system

  • select OBJECT_NAME from ALL_OBJECTS where owner=‘NJAGAN’

    • lists all tables owned by user “NJAGAN”

The system table all users l.jpg
The system table ALL_USERS

  • Keeps information of all the user accounts created on the system

  • Structure:


  • * from ALL_USERS

    • lists all the users who hold accounts on the ORACLE server

Granting privileges l.jpg
Granting privileges


    • Grants a privilege to a user

    • Can grant privilege only if you have been granted that privilege (or if you are the administrator)

      grant<privilege> to <user>;

Examples of granting roles l.jpg
Examples of granting roles

  • grant create table to john;

  • grant all on CD_MASTER to tom;

  • grant SELECT ON CD_MASTER.CD_NAME to john;

  • grant select, update on CD_DB to tom;

  • grant references(CD_NO) on CD_DB to john;

Revoking roles l.jpg
Revoking roles


    • Revokes a privilege from a user

    • Can revoke privilege only if you have been granted that privilege (or if you are the administrator)

      revoke<privilege> from <user>;

Examples of revoking roles l.jpg
Examples of revoking roles

  • revokeDROP ANY TABLEfrom tom, john;

  • revoke DELETE on CD_NAME from tom;

  • revoke references on CD_MASTER.CD_NO from tom;

Stored procedures l.jpg
Stored Procedures

  • a group of PL/SQL statements that you can call by name

  • must have CREATE PROCEDURE system privilege

Syntax of stored procedures l.jpg
Syntax of stored procedures

create procedure

<schema_name>.<proc_name> (parameters…)

as begin

… <body of stored procedure> …


Example of a stored procedure l.jpg
Example of a stored procedure



UPDATE accounts

SET balance = balance + amount

WHERE account_id = acc_no;


Triggers l.jpg

  • Stored procedure associated with a database table

  • Automatically fired when the specified trigger condition is satisfied

Trigger conditional predicates l.jpg
Trigger Conditional Predicates




  • UPDATING (column_name)

Types of triggers l.jpg
Types of Triggers





Trigger structure l.jpg
Trigger Structure

  • create trigger<trigger_name>

    [before | after][insert | update | delete]

    on <table_name> [for each row]


    … <trigger_body> …


Uses of triggers l.jpg
Uses of Triggers

  • to provide sophisticated auditing and transparent event logging

  • to automatically generate derived column values

  • to enforce security

  • to maintain replicate tables

Event logging trigger l.jpg
Event logging trigger

CREATE TRIGGER audit_trigger


ON classified_table FOR EACH ROW


IF INSERTING THEN INSERT INTO audit_table VALUES (USER || ' is inserting' || ' new key: ' || :new.key);



Views l.jpg

  • logical table that contains data from other tables and views

  • no extra storage space

  • restrict access to predetermined sets of rows/columns

  • updateable

  • essentially results of a query

Syntax for creating views l.jpg
Syntax for creating views

  • create view <view_name> as

    <SQL Query>;


    create view CD_LIST as

    select CD_NAME, CD_ARTIST from CD_MASTER;

Database computing l.jpg
Database Computing

Database Computing

Thin Client / Thick Server

Thick Client / Thin Server