ORACLE

1. ORACLE Using ORACLE 8 SQL using ORACLE 8 PL/SQL using ORACLE 8

2. SQL Data Types • Numeric Data Types • String Data Types • Data/Time Data Types

3. 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

4. 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

5. SQL Data Types (Date/Time) • DATE date (yyyy-mmm-dd) • TIME time (hh:mm:ss) • TIMESTAMP combination of date and time

6. 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

7. Creating Tables create table<table_name> ( <column1> <datatype> [constraint], <column2> <datatype> [constraint], … … … );

8. 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) );

9. Inserting Data insert into<table_name> (first_column, second_column, … last_column) values (first_value, second_value, … );

10. 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’);

11. Modifying data update<table_name> set<column_name> = <new_value> where <condition>;

12. Deleting Data delete from<table_name> where <SQL_condition>;

13. Altering table definitions alter table <table_name> add | drop | modify (<column specification[s]>);

14. Deleting tables drop table<table_name> [cascade constraints];

15. 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

16. 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

17. 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>)

18. 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)

19. The Data File • field1_val1, field2_val1, … • field1_val2, field2_val2, … • field1_val3, field2_val3, … • field1_val4, field2_val4, … • field1_val5, field2_val5, … • … … ...

20. 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'

21. 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

22. 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

23. 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

24. Useful ORACLE-SQL commands • DESCRIBE: Shows the structure of a database table • Syntax: describe <table_name>; can be shortened to desc <table_name>;

25. 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

26. 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”

27. 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

28. 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>;

29. 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;

30. 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>;

31. 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;

32. Stored Procedures • a group of PL/SQL statements that you can call by name • must have CREATE PROCEDURE system privilege

33. Syntax of stored procedures create procedure <schema_name>.<proc_name> (parameters…) as begin … <body of stored procedure> … end;

34. 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;

35. Triggers • Stored procedure associated with a database table • Automatically fired when the specified trigger condition is satisfied

36. Trigger Conditional Predicates • INSERTING • DELETING • UPDATING • UPDATING (column_name)

37. Types of Triggers Trigger AFTER BEFORE FOR EACH ROW

38. Trigger Structure • create trigger<trigger_name> [before | after][insert | update | delete] on <table_name> [for each row] begin … <trigger_body> … end;

39. Uses of Triggers • to provide sophisticated auditing and transparent event logging • to automatically generate derived column values • to enforce security • to maintain replicate tables

40. 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;

41. 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

42. 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;

43. Database Computing Database Computing Thin Client / Thick Server Thick Client / Thin Server