Oracle
Download
1 / 43

ORACLE - PowerPoint PPT Presentation


  • 247 Views
  • 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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

ORACLE

Using ORACLE 8

SQL using ORACLE 8

PL/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

CONSTRAINT pk_cd PRIMARY KEY,

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

update<table_name>

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

Structure:

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 '"'

    (CD_NO, CD_NAME, ARTIST, TYPE)


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’

    102,‘Supernatural’,‘Santana’,‘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 zaurak.cis.ksu.edu

  • 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:

    • <USERNAME, USER_ID, CREATED>

  • e.g.select * from ALL_USERS

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


Granting privileges l.jpg
Granting privileges

  • GRANT

    • 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

  • REVOKE

    • 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> …

end;


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

CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER)

AS BEGIN

UPDATE accounts

SET balance = balance + amount

WHERE account_id = acc_no;

END;


Triggers l.jpg
Triggers

  • Stored procedure associated with a database table

  • Automatically fired when the specified trigger condition is satisfied


Trigger conditional predicates l.jpg
Trigger Conditional Predicates

  • INSERTING

  • DELETING

  • UPDATING

  • UPDATING (column_name)


Types of triggers l.jpg
Types of Triggers

Trigger

AFTER

BEFORE

FOR EACH ROW


Trigger structure l.jpg
Trigger Structure

  • create trigger<trigger_name>

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

    on <table_name> [for each row]

    begin

    … <trigger_body> …

    end;


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

BEFORE INSERT OR DELETE OR UPDATE

ON classified_table FOR EACH ROW

BEGIN

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

END IF;

END;


Views l.jpg
Views

  • 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>;

    Example:

    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