1 / 30

Oracle 9i

Oracle 9i. Agenda. Start and exit SQL Plus (General) Start and exit SQL Plus (Tah 1006) Syntax Create a new user Create a new table Enter data into a new table Export & import data. Start and Exit SQL Plus. Start SQL Plus Start Select Program Select Oracle-ORACLE_9i

abeni
Download Presentation

Oracle 9i

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Oracle 9i

  2. Agenda • Start and exit SQL Plus (General) • Start and exit SQL Plus (Tah 1006) • Syntax • Create a new user • Create a new table • Enter data into a new table • Export & import data

  3. Start and Exit SQL Plus • Start SQL Plus • Start • Select Program • Select Oracle-ORACLE_9i • Select Application Development • Select SQL Plus • Enter your name for User Name (system) • Enter Enter your password for Password (manager) • Exit SQL Plus • Type exit at SQL prompt

  4. Start and Exit SQL Plus (Tah 1006) • Start Database • Start the computer • Wait for couple minutes • Select Oracle-XP_SP1 • Press Enter for password (no password is needed) • Double clicks the SQL Plus icon • Enter your name for User Name (system) • Enter your password for Password (manager) • Exit SQL Plus • Type exit at SQL prompt • Close the database window

  5. Syntax - General • Use semicolon (;) to terminate a statement • SQL is not case-sensitive • SQL statement can be entered into several lines • Use comma (,) to separate attributes • Use a pair of single quotes (‘) for any character string • Use a pair of double quotes (‘’) for a single ‘ character string

  6. Syntax - General • Use percentage sign (%) for a group of wildcard characters • Use underscore (_) for one wildcard character • Escape character • Select…from….where city like ‘%s/_f%’ escape ‘/’; • Underscore _ after / will be interpreted as a regular meaning • Subquery must be enclosed in a pair of parentheses

  7. Syntax - Name • Table name or attribute name limited to 30 characters (characters, number, special symbols $, _, and #) • Name has to begin with a character • No blank • Not case sensitive • No reserved word

  8. Syntax - Data Types • Characters • Varchar2(n): max 4,000 characters (ASCII, 8-digit) • Char(n): max 2,000 characters (ASCII, 8-digit) • Nvarchar2(n): as char (Unicode, 16-digit) • Nchar(n): as char (Unicode, 16-digit) • Numbers • Integer: number(n) • Fixed-point: number(5,2), 999.99 • Floating-point: number

  9. Syntax - Data Types • Date • Date:DD-MMM-YY HH:MI:SS • Timestamp(fractional seconds precision) with 6 as default value • Large object (LOB): max 4 gigabytes for digitized sounds, images, and binary files (BLOB, CLOB, BFILE, NCLOB)

  10. Syntax - Editor • L or LIST - displays most current SQL statements from buffer • Line number - changes the current line number to the entered line number and shows the listed line number • LIST line number or nn nn - show the indicated line number text or between the nn and nn

  11. Syntax - Editor • A or APPEND text - adds the entered text to the end of the current line • I or INPUT text – create a new line after the current line • C or CHANG/old text/new text/ - replaces an existing text string (old text) in the line with a new text string (new text) • DEL or DELETE - deletes the current line • START filename – execute the contents of the SQL Plus command file

  12. Syntax - Editor • SAVE file name - saves the most current SQL statements to a file (filename.sql) • GET file name - retrieves SQL statements from the file name (filename.sql) • R or / - runs the most current statements • SPOOL file name - saves SQL statements, its query, and other results to a file • SPOOL OFF - terminates SPOOL

  13. Spool File - Menu • SPOOL file • Select File • Select Spool • Select Spool file • Type drive (such as a:) • Type file name (such as f1) • Select spool file (*.lst) • SPOOL off • File • Spool • Spool off

  14. Save File Menu • Select File • Select Save • Select Create to create a *.sql file • Select Replace to replace a *.sql file • Select Append to append a *.sql file

  15. Syntax - View Table Structure • View all of the tables • SELECT TABLE_NAME FROM USER_TABLES; • SELECT TABLE_NAME FROM ALL_TABLES; • View field definitions of a table • DESCRIBE table name • DESCRIBE student; • View all the constraints of a table • SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS; • SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME = ‘student’;

  16. Oracle Constraint Type Identifier • Primary key: p • Foreign key: r • Check condition: c • Not null: n • Unique: u

  17. Error Message • Type http://otn.oracle.com for the Internet address • Click ‘Search’ • Check ‘Oracle Technology Network Documentation’ box • Type ‘ORA-error messages’ in the search field • Click ‘Search icon’

  18. Create A New Table • CREATE TABLE student (stuid char(5) NOT NULL, stuname char(10), major char(10), credit number(3), CONSTRAINT pkstudent PRIMARY KEY (stuid));

  19. Create A New Table • CREATE TABLE faculty (facid char(5) NOT NULL, facname char(10), dept char(10), rank char(10) check (rank in (‘F’,’Aso’, ‘Ast’)), CONSTRAINT pkfaculty PRIMARY KEY (facid));

  20. Create A New Table • CREATE TABLE class (course# char(5) NOT NULL, facid char(5), sched char(10), room char(10), CONSTRAINT pkclass PRIMARY KEY (course#), CONSTRAINT fkclassfaculty FOREIGN KEY (facid) REFERENCES faculty (facid));

  21. Create A New Table • CREATE TABLE enrollment (course# char(5) NOT NULL, stuid char(5) NOT NULL, grade char(10), CONSTRAINT pkenroll PRIMARY KEY (course#, stuid), CONSTRAINT fkenrollclass FOREIGN KEY (course#) REFERENCES class (course#), CONSTRAINT fkenrollstudent FOREIGN KEY (stuid) REFERENCES student (stuid));

  22. Create A New User • CREATE USER username IDENTIFIED BY password • CREATE USER tsai IDENTIFIED BY tsai • GRANT privilege1, privilege2,…TO username or role name • GRANT CREATE USER, CREATE TABLE, DROP TABLE TO tsai (current user’s schema – user level) • GRANT role name TO username • GRANT DBA TO tsai (any user’s schema –DBA level)

  23. Delete An User • REVOKE privilege FROM role name • REVOKE CREATE USER form tsai • REVOKE role name FROM username • REVOKE DBA from tsai

  24. Input Data Into A Table • The ampersand (&) signal the SQL compiler to prompt the user for a value that is then substituted into the query • &prompt_variable is the name of the attribute for which the user is prompted to specify a value • Example INSERT INTO student VALUES (&stuid, &stuname, &major, &credit);

  25. Load Data Into A Table • Tsaitest.dat file contains 11111,Ching,MIS,100 22222,Anderson,MIS,200 33333,Martin,ACT,300 • Tsaitest.ctl contains • LOAD DATA • INFILE ‘tsaitest.dat’ • APPEND • INTO TABLE student • FIELDS TERMINATED BY ‘,’ • (stuid, stuname, major, credit) • The following command should be issued in command line • SQLLDR USERID=tsai/tsai CONTROL= tsaitest.ctl LOG=tsaitest.LOG • Tsaitest.dat, tsaitest.ctl, and tsaitest.log have to be in the same directory such as C:

  26. Export Data • Program • MS-DOS Prompt • Type exp user name/password (such as exp nancy/nancy) • Specify export file (such as a:extf1.dmp where a: is the drive) • Answer the questions to execute the expert program

  27. Import Data • Program • MS-DOS Prompt • Type imp user name/password (such as imp nancy/nancy) • Specify Import file name (such as a:extf1.dmp) • Answer the questions to execute the import program

  28. Process for Oracle Homework • Use notepad to prepare the sql codes for creating tables, data, problems, and control files • Save each control file in an individual file • Save each table data in an individual file • Use copy and paste to move the sql codes of create table from notepad to oracle then press / to run • Copy 4 data files and control files in the root of C drive

  29. Process for Oracle Homework • Type four sqlldr statements at C:> to load the data into the tables (one at the time) • Use copy and paste to move the sql codes of problem from notepad to oracle then press / to run • Use save command to store the solution • Use spool to save every problem together • Use get command to get the saved solution and press / to run • Use spool off to turn off the spooling • Use Word to print the saved spool file

  30. Process for Oracle Homework • Use Imp and exp to import and export records and tables

More Related