Databases
09. Oracle SQL Developer
2018. Fall
Instructor: Joonho Kwon
[email protected]
Data Science Lab @ PNU
⚫ Oracle has released the free SQL Developer tool (formerly known as Project Raptor) and it is
super!
⚫
If you have ever used the Free Toad tool or PL/SQL Developer this tool will feel familiar; plus it has more features and supports Oracle 10g too!
⚫ SQL Developer is a Java-based GUI tool (works in Windows, Unix, and Linux)
Oracle SQL Developer (1/2)
2
⚫
Power of SQL*Plus without a local Oracle client installation
⚫
Intuitive software; not completely new, based upon JDeveloper
⚫
Provides features often found only in expensive third-party software including:
⚫
GUI browsing of database objects, creation, deletion, alteration
⚫
Debugging complete with breakpoints
⚫
Query execution
⚫
Database reporting
⚫
Multiple connections
Oracle SQL Developer (2/2)
⚫ Oracle SQL Developer is a client application that can be used to access the Oracle database server
⚫ Go to
http://www.oracle.com/technetwork/developer- tools/sql-developer/downloads/index.html
Install Oracle SQL Developer (1/2)
4
⚫
Download SQL Developer 4.13
⚫
Windows 32/64-bit
Install Oracle SQL Developer (2/2)
⚫ 1. Extract SQL Developer
⚫
E.g.) C:\sqldeveloper
⚫ 2. Change the language of SQLDevelpoer
⚫
File: sqldeveloper.conf
⚫
location: C:\sqldeveloper\sqldeveloper\bin
⚫
Add followings
Run SQL Developer (1/2)
66
# Change the language
AddVMOption -Duser.language=en
AddVMOption -Duser.country=US
⚫ 3. Double click sqldeveloper
⚫
Under C:\sqldeveloper directory
⚫ 4. Specify the JDK home
⚫
Needed when you choose SQLDeveloper without JDK
⚫
E.g) C:\Program Files\Java\jdk1.8.0_73\
Run SQL Developer (2/2)
SQL Developer: Start Page
8
⚫ Click “new connection” button one the left-top.
Create New connection
1. Connection for Oracle XE 11g
10
⚫ Setting for Oracle XE
⚫
Connection Name(접속이름)
⚫
XE (anything will be fine)
⚫
Username: hr
⚫
password: oracle
⚫
Hostname: localhost
⚫
Port number: 1521
⚫
Oracle SID: xe
Connection for Oracle XE (1/4)
⚫ Click Test first and save
Connection for Oracle XE (2/4)
12
⚫ Check the connection name
Connection for Oracle XE (3/4)
⚫ then click connect
Connection for Oracle XE (4/4)
14
⚫ After logging in to a connection, SQL Developer shows three subpanels:
SQL developer
SQL Worksheet
⚫ The SQL Worksheet area has a dialog box where SQL may be keyed, viewed, and modified
SQL Worksheet
16
Note the “pull-down” showing the Connection that will be used to execute any SQL, PL/SQL, or script in the Worksheet
⚫ Type the following SQL
Type SQL
SELECT * FROM departments;
⚫ SQL Toolbar
⚫
Run Statement (Ctrl+Enter)
⚫
Run Script (F5)
⚫
Explain Plan (F10)
⚫
Auto Trace (F6)
⚫
Commit (F11)
⚫
Rollback (F12)
⚫
Clear (Ctrl+D)
⚫
SQL History (F9)
SQL Toolbars
18
⚫ Run Statement
⚫
If only one statement is in SQL Worksheet, select “Run Statement ”
⚫ Run Script (F5)
⚫
If more than one statement is coded, position the cursor in the statement to be executed (or highlight the statement), then select “Run Script”
Execute Statement
⚫ Click Run button
⚫
The “Results” tab shows the results of a single- statement execution
Statement Execution Results
20
⚫ If multiple SQL, PL/SQL, or script statements are to be executed together, select “Run Script”
⚫ Results show up in the “Script Output” tab
Run Script
⚫ When a script is executed, the results show up in the “Script Output” tab
Script Output
22
⚫ CREATE
⚫ INSERT
⚫ SELECT
Test the following SQL statements
Practice1: CREATE Table (1/2)
24
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
⚫ Run Script
Practice1: CREATE Table (2/2)
⚫ Browsing the tree view:
⚫
if EMP does not appear,
⚫
right click Tables and select refresh
⚫
Double click EMP
Checking a table
26
⚫ Multiple Insert Statements
Pracetice2: Insert into a table (1/3)
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
Pracetice2: Insert into a table (2/3)
28
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
⚫ Run Script
Insert into a table (3/3)
⚫ Click Run
Practice3: SELECT (1/2)
30
SELECT * FROM EMP;
Practice3: SELECT (2/2)
2. Connection for DataLab’s Oracle
32
⚫ Setting for Oracle Server @ DataLab
⚫
Connection Name(접속이름)
⚫
DataLab (anything will be fine)
⚫
DB username, DB password
⚫
Will be posted at the class web site
⚫
Hostname: 164.125.37.214
⚫
Port number: 1521
Create New connection for DataLab (1/4)
Create New connection for DataLab (2/4)
34
⚫ Click Test first and save
⚫ Check the connection name
Create New connection for DataLab (3/4)
Create New connection for DataLab (4/4)
36
⚫ then click connect
⚫ The SQL Worksheet area has a dialog box where SQL may be keyed, viewed, and modified
SQL Worksheet
Note the “pull-down” showing the Connection that will be used to execute any SQL, PL/SQL, or script in the Worksheet
⚫ CREATE
⚫ INSERT
⚫ SELECT
Test the following SQL statements
38
