Page1of4
USINGORACLE SQLPLUS
B1.StartingSQLCommand
Run “Run SQL Command”, you can find it by clicking “StartAll programsOracle Database 11g Express EditionRun SQL command”. Then the SQL command window appears. Right now you should type the following:
connect sys/ as sysdba and press the enter button.
And then you are asking to enter the password, the password is the one you input when you install the software. Enter the password and the database has been connected.
B2.Using CREATE tocreateatable
Tablesmay becreatedfromthecommandprompt usingSQLstatements.Use the followingstatement to create a tablenamed DEPT
withthe command
CREATETABLE DEPT(DEPTNO NUMBER(2)PRIMARY KEY,DEPTNAME CHAR(14));
Create a tablenamed EMP withCREATE TABLE EMP (SSN CHAR(11) PRIMARYKEY,DEPTNONUMBER(2), NAME CHAR(12)); TheDEPT table will have two columns,DEPTNO withadatatype ‘number’and DEPTNAMEwithadatatype ‘char’. Column DEPTNOis aprimary key.EMP will have3columnswithSSN being the primary key. (Besure to terminate yourstatements with a semicolon. Ifyou fail toterminate your commandwith asemicolonand hit return,Oracle will advance the cursor to a new lineand expect you to continue typingthe SQL statement. If youdo thisby mistake,justplace asemicolon on thenew line andhit enter again.)
\\babbage\Inetpub\CSE\cslab\Reference\oracle\OracleSQLPlus.doc
02/05/07
Page2of4
Youcanalsocreate tablesbyduplicating an existing table.Toillustrate, use the commandCREATETABLEDEPT2 AS SELECT
*FROMDEPT;tocreate table DEPT2byduplicating thetable youjust created.
B3.Using theDESCRIBE command
Youcan use DESCRIBE to list all the columns andcolumn datatypesof atable. Use the following statements tolist this informationfor the tablesyoucreated.
SQL> DESCRIBE DEPT; SQL> DESCRIBE DEPT2;
SQL>DESCRIBE EMP;
B4.UsingDROP to delete tables
EnterDROP TABLEDEPT2; to delete theDEPT2 table.
B5.UsingINSERT INTOtowrite datavaluesinto a table
UseINSERTINTO to write data values intoyour tables. Createarecord inthe DEPTtablefor thepersonneldepartment using the commandINSERTINTO DEPT(DEPTNO,DEPTNAME VALUES (21,'SALES'));. If youarepopulatingall columns andenter
yourdata inthe order that youcreatedcolumns inthe table,you canomit the columnnames. Create some additionalrecordswiththe
followingstatements
INSERTINTODEPTVALUES (23, ‘PERSONNEL’); INSERTINTODEPTVALUES (32, ‘SECURITY’); INSERTINTODEPTVALUES(45,‘PLANT’);
B6a. Using the SQLPlus editor
The SQLPlus editor can beinvoked fromtheSQL command prompt with the‘edit’command. Ifyouareenteringseveral lines of data it is sometimeseasier to enterthe information froma script, than fromtheSQLcommandprompt. Toillustrate this wewill use the editor to writea script tomake several entries into the EMP table.Enter EDITtemp.sqlfromtheSQLPluscommand prompt to open
atext filenamedtemp.sql. After you havedoneso, a windowswill open witha message indicatingthat the temp.sqlfile could not be found and asking if you wish to create thefile. Answeryes, then enter thefollowingscript intothe editor:
INSERTINTOEMP VALUES (‘111-11-1111’,21,‘FRED’)
/
INSERTINTOEMP VALUES (‘222-22-2222’,21,‘MARY’)
/
INSERTINTOEMP VALUES (‘555-55-5555’,23,‘ELSA’)
/
INSERTINTOEMP VALUES (‘666-66-6666’,32,‘MORRIS’)
/
INSERTINTOEMP VALUES (‘777-77-7777’,32,‘APRIL’)
/
Youhavenowentered5separateSQLstatements into theeditor.Notice that eachstatement is terminated with ‘/’ rather than‘;’ that you used when entering individualstatements fromthecommandline. Be sure that your statements are formed correctly, thenuse File-Save tosave the file. Now, exit thenotepadeditorand returntothe SQLPlus prompt.
Torun the script that youcreated, enterSTARTtemp.sql; fromthecommand prompt. Thescript should run andexecute the5 insert statements that you havewritten. If you receive error messages, youmay open the editorand make correctionsbeforerunningthe script again.If yourscript containsanextraneousspace,you may get anerror messagetotheeffect that somethingwas‘truncated’. The‘truncated’ message canbe ignored. In thenextpart ofthis exercise,youlearn howto verifywhathasbeen writtento thetables you created.
B7.UsingSelect
Oracle maintains arecordof the tablesyou create ina tablenamedTab. Tolist the tables you have createdin thedatabase usethe commandSELECT * FROMTAB; . To retrieve all records inthe DEPT table, enterSELECT* FROMDEPT;. The* is an SQL
wild card character which tells Oracle to retrieveeverything. ToretrievethePersonnelDepartment EmployeerecordsfromEMP
enterSELECT* FROMEMPWHEREDEPTNO = 23;
Ifyou only want thenamesof employeesinthePersonnel Department youcanuseWHEREtolimit thenumberof records returned with SELECTNAME FROM EMPWHEREDEPTNO=23;.
Ifyou want tofindtheDEPTNO for the personneldepartment useSELECTDEPTNO FROMDEPTWHEREDEPTNAME=
‘PERSONNEL’;. Notice thatinthis example the literal ‘PERSONNEL’ capitalizedandenclosedinsingle quotes. Char and varchar
\\babbage\Inetpub\CSE\cslab\Reference\oracle\OracleSQLPlus.doc
02/05/07
Page3of4
datatype values are literals so they are case sensitive and mustbe enclosed insinglequotes inSQL statements. Thenumber datatype however,should notbe quotedasindicatedinthe previousexample. If your select statementdoesnotreturn what youexpected,use SELECT*FROMDEPT; tosee if youcapitalizedthepersonnel entrywhenyou entered it.
B8.UsingDelete
Verify that you have arecord intheEMP table forELSA withthe commandSELECT* FROMEMP WHERENAME=‘ELSA’;.
Nowdelete the record withDELETEFROMEMP WHEREDEPTNO =23; .Verify thatthe record hasbeen removed withSELECT* FROMEMP;
B9. Createaview
Aview isa SQL query that youwish to nameand save. Create a view namedDEPT_NOS with the commandCREATE VIEW
DEPT_NOS AS SELECTDEPTNOFROMDEPT;. Thiswill create aview, calledDEPT_NOS,withonecolumnfromDEPT, called DEPTNO.Display thecontent of viewDEPT_NOS by entering SELECT* FROMDEPT_NOS; . Viewsarenot tables and do not exist permanently in thedatabase.Rather theyarecreated fromother tables wheneverthey arecalled.
B10. Using ALTER
Youcanmodify tablesyoucreatewith ALTER. AddanewcolumnnamedHIREDATEtoEMPwith ALTERTABLEEMP ADD
(HIREDATE DATE);.The format ofthe DATEdata type isDD-MON-YY. Youcannowinsert avalueintotheHIREDATEcolumn with UPDATE EMPSETHIREDATE= ‘03-SEP-96’;.Be sure to putsinglequotes aroundDD-MON-YYbecause it is a stringvalue. Youcan NOTuseALTERto remove anexistingcolumn.You must createanother tablewithoutHIREDATEfromthis tableusinga SELECTstatement within aCREATE statement. Try to plan aheadanddesign your tablessothey do not haveto bealtered.
B11.PrintingOutput
There are several ways to print outputdepending onthe systemyouare using.On aWindows computer, if all ofyour output is contained on one screen, you cancopy it tothewindows clipboard and fromthere intoa text processing utility like Windowsnotepad.
Youcanthen copy current screencontent to theclipboard by pressingthe ‘print screen’ key on your keyboard. Then open aword
processor (MSWord,Wordpad,etc)and used ‘edit–paste’ to pastetheclipboard contentintoa document that you can print.
Anotheris tocapturescreen output intheSQLPluswindow,andthen saveit toa textfile.The technique is similar towhat you may havepreviouslyused in Windows HyperTerminal orother telnetfacilities where you ‘turn on’ an option to write all screen output toa textfile. The text file canthen beopenedin awordprocessor ortexteditor (MSWord,Notepad,Wordpad,etc) and printedin
the normalmanner. Toillustrate this,‘turn on’ the spooloption with thecommandSPOOL fileName;,where‘filename’ is the full path of the file you wouldlike tocreate for savingthe output inthe SQLPlus window. For example, ifyouwant to create
‘OracleSpool.txt’ inthe H drive,thecommand isgoing to be SPOOL H:\OracleSpool.txt;. Ifyou don’t specify the directory and put only the filename instead of a full path, the file is to becreatedyour home directory. Tocontinuetheexample,after initiatingthe
Spoolfacility, enterSELECT*FROMEMP;. Then use thecommand SPOOLOFF; to turn off the spool,. Thefile OracleSpool.txt should nowbeinyour home folder on theHdrive. Open windows notepad(Program-Accessories-Notepad fromtheWindowsStart
menu) andlocate the file you created.Youcan now readand print theoutput you sent to the spoolfile inthe normalmanner.
B12. Commits andRollbacks
Changes youmake toan Oracle Database are initially storedinapending status.Thisfeature isusedin processing distributed
databasetransactionsandisusefulfor undoingmistakes.Distributed databasetransactionsarecovered inadvanced databaseclasses. For now, the important thing to understand is how toCOMMITorROLLBACK thechanges youmake to thedatabase.
Youcanthinkof atransactionasaseriesof SQLcommandsenclosed by apair ofcommit and/or rollbackcommands. All ofthe changesyouhave made in this exercise thusfar are in pending statusbecause wehavenot issuedany COMMIT orROLLBACK commands. If you wereto open another SQLPlussession now, you would not find any tablesinyour database,because uncommitted
changesareonly visible to the session in which theywere created. Beforeproceeding any further, make thechangesthat youhave
made inthis exercise permanent by issuingthe following command:
COMMIT;
The database should respond witha‘ commit complete’message.
Todemonstratehow ROLLBACK works, add anew department tothedept tablewith INSERTINTO DEPTVALUES (27,
‘RESEARCH’);. Afterdoing soenterSELECT *FROMDEPT;to display the results. At thispoint you can elect to COMMIT the change you justmade or to roll it back. Since we issuedaCOMMITjustpriortoinserting theRESEARCHdepartment record, issuing a ROLLBACKshould eraseonly the RESEARCH record. To demonstrate, issue the commandROLLBACK;,Then view the
resultby issuinganotherSELECT * FROMDEPT;. Youshould find thatthe RESEARCHentryhasbeen deleted.
If you exitOracle SQLPlususing the EXITcommand,pendingchanges are automatically committed. Exiting by closingthe SQLwindowwithout issuing an EXIT command,however,will cause your changes to berolled back. Networkfailurescanalsocause unwantedROLLBACKs to occur. Thus, it iswise to issue COMMIT’speriodically during longSQLPlussessions. Also, tobeon the safe side, always COMMITbefore closing anSQLPlus session.
*B13. Exiting SQLPlus – To ExitOracleSQLPlus, typeEXIT;at the Oracle prompt and pressreturn.
\\babbage\Inetpub\CSE\cslab\Reference\oracle\OracleSQLPlus.doc
02/05/07
Page4of4
GO BACK