IT/Oracle Database Server & Client

Oracle database 2 day Developer's guide

하품하는천둥 2016. 7. 10. 12:58


2 Connecting to Oracle Database and Exploring It

You can connect to Oracle Database only through a client program, such as SQL*Plus or SQL Developer.


☞Note:

After connecting to Oracle Database through a client program, you enter and run commands in that client program. For details, see the documentation for your client program.


Connecting to Oracle Database from SQL*Plus

c:>sqlplus username/password@yourdb


You are in the SQL*Plus environment. At the SQL> prompt, you can enter and run SQL*Plus commands, SQL statements, PL/SQL statements, and operating system commands.


> sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Thu Dec 27 07:43:41 2012
 
Copyright (c) 1982, 2012, Oracle.  All rights reserved.
 
Enter user-name: your_user_name
Enter password: your_password
 
Connected to:
Oracle Database 12c Enterprise Edition Release - 12.1.0.1.0 64bit Production
 
SQL> select count(*) from employees;
 
  COUNT(*)
----------
       107
 
SQL> exit
 
Disconnected from Oracle Database 12c Enterprise Edition Release - 12.1.0.1.0 64bit Production
>

Connecting to Oracle Database from SQL Developer

SQL Developer is a client program with which you can access Oracle Database.

With Oracle Database 12c Release 1 (12.1), Oracle recommends using SQL Developer version 4.0 or later, which you can download from:


http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/


To connect to Oracle Database from SQL Developer:


** seems that connecting to Oracle database from SQL Developer depends severely on your Oracle DBMS settings

** and Installation options vary at each DBMS versions.

** so you need to connect to Oracle database taking your settings into consideration.

Connecting to Oracle Database as User HR

Unlocking the HR Account

By default, when the HR schema is installed, the HR account is locked and its password is expired. You can connect to Oracle Database as the user HR only if the HR account is unlocked.


To unlock the HR account and reset its password:

  1. Using SQL*Plus, connect to Oracle Database as a user with the ALTER USER system privilege.

  2. At the SQL> prompt, unlock the HR account and reset its password:


ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;

The system responds:

User altered.

Connecting to Oracle Database as User HR from SQL*Plus

Connecting to Oracle Database as User HR from SQL Developer

Exploring Oracle Database with SQL*Plus

Viewing HR Schema Objects with SQL*Plus

the static data dictionary view USER_OBJECTS


COLUMN OBJECT_NAME FORMAT A25
COLUMN OBJECT_TYPE FORMAT A25

SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS
ORDER BY OBJECT_TYPE, OBJECT_NAME;


Viewing EMPLOYEES Table Properties and Data with SQL*Plus

DESCRIBE EMPLOYEES


To view other properties of the table, use static data dictionary views (for example, USER_CONSTRAINTS, USER_INDEXES, and USER_TRIGGERS).


DESCRIBE USER_CONSTRAINTS;



to select data only for employees in departments 100, 110, and 120, use this WHERE clause: 

WHERE DEPARTMENT_ID IN (100, 110, 120);


selects data only for employees whose last names start with "Ma"

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'Ma%';


To select data only for employees whose last names include "ma", use this WHERE clause:

WHERE LAST_NAME LIKE '%ma%';


Selecting Data from Multiple Tables

SELECT EMPLOYEES.FIRST_NAME "First",
EMPLOYEES.LAST_NAME "Last",
DEPARTMENTS.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
ORDER BY DEPARTMENTS.DEPARTMENT_NAME, EMPLOYEES.LAST_NAME;



Using Operators and Functions in Queries


SELECT LAST_NAME,
SALARY "Monthly Pay",
SALARY * 12 "Annual Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
ORDER BY SALARY DESC;


round

SELECT LAST_NAME,
ROUND (((SALARY * 12)/365), 2) "Daily Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

LAST_NAME                  Daily Pay
------------------------- ----------
Chen                          269.59
Faviet                        295.89
Greenberg                     394.52
Popp                          226.85
Sciarra                       253.15
Urman                         256.44


truncate

SELECT LAST_NAME,
TRUNC ((SALARY * 12)/365) "Daily Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

LAST_NAME                  Daily Pay
------------------------- ----------
Chen                             269
Faviet                           295
Greenberg                        394
Popp                             226
Sciarra                          253
Urman                            256


The concatenation operator (||) combines two strings into one string

SELECT FIRST_NAME || ' ' || LAST_NAME "Name"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

Name
----------------------------------------------
John Chen
Daniel Faviet
Nancy Greenberg
Luis Popp
Ismael Sciarra
Jose Manuel Urman


Using Character Functions in Queries

SELECT UPPER(LAST_NAME) "Last",
INITCAP(FIRST_NAME) "First",
LOWER(EMAIL) "E-Mail"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY EMAIL;

Result:

Last                      First                E-Mail
------------------------- -------------------- -------------------------
FAVIET                    Daniel               dfaviet
SCIARRA                   Ismael               isciarra
CHEN                      John                 jchen
URMAN                     Jose Manuel          jmurman
POPP                      Luis                 lpopp
GREENBERG                 Nancy                ngreenbe



The query in Example 2-18 uses the EXTRACT and SYSDATE functions to show how many years each employee in department 100 has been employed. The SYSDATE function returns the current date of the system clock as a DATE value.

SELECT LAST_NAME,
(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)) "Years Employed"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY "Years Employed";

Result:

LAST_NAME                 Years Employed
------------------------- --------------
Popp                                   5
Urman                                  6
Chen                                   7
Sciarra                                7
Greenberg                             10
Faviet                                10



the SYSTIMESTAMP function to display the current system date and time. The SYSTIMESTAMP function returns a TIMESTAMP value.

The table in the FROM clause of the query, DUAL, is a one-row table that Oracle Database creates automatically along with the data dictionary. Select from DUAL when you want to compute a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. For more information about selecting from DUAL, see Oracle Database SQL Language Reference.


SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' ||
ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP) || '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date"
FROM DUAL;

Results depend on current SYSTIMESTAMP value, but have this format:

System Time and Date
-------------------------------------------------------------------
18:17:53, 12/27/2012


Using Conversion Functions in Queries

the TO_CHAR function to convert HIRE_DATE values (which are of type DATE) to character values that have the format FMMonth DD YYYY. FM removes leading and trailing blanks from the month name. FMMonth DD YYYY is an example of a datetime format model.

SELECT LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'FMMonth DD YYYY') "Date Started"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

LAST_NAME                 HIRE_DATE Date Started
------------------------- --------- -----------------
Chen                      28-SEP-05 September 28 2005
Faviet                    16-AUG-02 August 16 2002
Greenberg                 17-AUG-02 August 17 2002
Popp                      07-DEC-07 December 7 2007
Sciarra                   30-SEP-05 September 30 2005
Urman                     07-MAR-06 March 7 2006


the TO_NUMBER function to convert POSTAL_CODE values (which are of type VARCHAR2) to values of type NUMBER, which it uses in calculations

SELECT CITY,
POSTAL_CODE "Old Code",
TO_NUMBER(POSTAL_CODE) + 1 "New Code"
FROM LOCATIONS
WHERE COUNTRY_ID = 'US'
ORDER BY POSTAL_CODE;

Result:

CITY                           Old Code       New Code
------------------------------ ------------ ----------
Southlake                      26192             26193
South Brunswick                50090             50091
Seattle                        98199             98200
South San Francisco            99236             99237


Using Aggregate Functions in Queries


ELECT MANAGER_ID "Manager",
COUNT(*) "Number of Reports"
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY MANAGER_ID;

Result:

   Manager Number of Reports
---------- -----------------
       100                14
       101                 5
       102                 1
       103                 4
       108                 5
       114                 5



The query in Example 2-23 shows how much each department spends annually on salaries, but only for departments for which that amount exceeds $1,000,000.

SELECT DEPARTMENT_ID "Department",
SUM(SALARY*12) "All Salaries"
FROM EMPLOYEES
HAVING SUM(SALARY * 12) >= 1000000
GROUP BY DEPARTMENT_ID;

Result:

Department All Salaries
---------- ------------
        50      1876800
        80      3654000


several aggregate functions

SELECT JOB_ID,
COUNT(*) "#",
MIN(SALARY) "Minimum",
ROUND(AVG(SALARY), 0) "Average",
MEDIAN(SALARY) "Median",
MAX(SALARY) "Maximum",
ROUND(STDDEV(SALARY)) "Std Dev"
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;



3 About DML Statements and Transactions


About Data Manipulation Language (DML) Statements

INSERT INTO table_name (list_of_columns)
VALUES (list_of_values);
UPDATE table_name
SET column_name = value [, column_name = value]...
[ WHERE condition ];
UPDATE EMPLOYEES
SET SALARY = 8500
WHERE LAST_NAME = 'Keats';
DELETE FROM table_name
[ WHERE condition ];
DELETE FROM EMPLOYEES
WHERE HIRE_DATE = TO_DATE('01-JAN-07', 'dd-mon-yy');





About Transaction Control Statements

The basic transaction control statements are:

  • SAVEPOINT, which marks a savepoint in a transaction—a point to which you can later roll back. Savepoints are optional, and a transaction can have multiple savepoints.

  • COMMIT, which ends the current transaction, makes its changes permanent, erases its savepoints, and releases its locks.

  • ROLLBACK, which rolls back (undoes) either the entire current transaction or only the changes made after the specified savepoint.


Committing Transactions


☞ Note:

Oracle Database issues an implicit COMMIT statement before and after any data definition language (DDL) statement. For information about DDL statements, see "About Data Definition Language (DDL) Statements".


INSERT INTO regions (region_id, region_name) VALUES (5, 'Africa');
COMMIT;
COMMIT;

Rolling Back Transactions

☞

Rolling back a transaction undoes its changes. You can roll back the entire current transaction, or you can roll it back only to a specified savepoint.


To roll back the current transaction only to a specified savepoint, you must use the ROLLBACK statement with the TO SAVEPOINT clause.


Rolling back the current transaction only to the specified savepoint:

  • Does not end the transaction

  • Reverses only the changes made after the specified savepoint

  • Erases only the savepoints set after the specified savepoint (excluding the specified savepoint itself)

  • Releases all table and row locks acquired after the specified savepoint

Other transactions that have requested access to rows locked after the specified savepoint must continue to wait until the transaction is either committed or rolled back. Other transactions that have not requested the rows can request and access the rows immediately.



Setting Savepoints in Transactions

The SAVEPOINT statement marks a savepoint in a transaction—a point to which you can later roll back. Savepoints are optional, and a transaction can have multiple savepoints.


UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZM';
SAVEPOINT zambia;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'NG';
SAVEPOINT nigeria;
 
UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZW';
SAVEPOINT zimbabwe;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'EG';
SAVEPOINT egypt;
ROLLBACK TO SAVEPOINT nigeria;



4 Creating and Managing Schema Objects

The statements that create, change, and drop schema objects are data definition language (DDL) statements.

Before and after a DDL statement, Oracle Database issues an implicit COMMIT statement; therefore, you cannot roll back a DDL statement.


Some DDL statements that create schema objects have an optional OR REPLACE clause, which allows a statement to replace an existing schema object with another that has the same name and type. When SQL Developer generates code for one of these statements, it always includes the OR REPLACE clause.


Creating and Managing Tables


About SQL Data Types

SQL data types fall into two categories: built-in and user-defined. (PL/SQL has additional data types—see "About PL/SQL Data Types".)


Creating Tables

  • PERFORMANCE_PARTS, which contains the categories of employee performance that are evaluated and their relative weights

  • EVALUATIONS, which contains employee information, evaluation date, job, manager, and department

  • SCORES, which contains the scores assigned to each performance category for each evaluation


Tutorial: Creating a Table with the Create Table Tool

This tutorial shows how to create the PERFORMANCE_PARTS table using the Create Table tool.

Creating Tables with the CREATE TABLE Statement

This section shows how to use the CREATE TABLE statement to create the EVALUATIONS and SCORES tables.


CREATE TABLE EVALUATIONS (
  EVALUATION_ID    NUMBER(8,0), 
  EMPLOYEE_ID      NUMBER(6,0), 
  EVALUATION_DATE  DATE, 
  JOB_ID           VARCHAR2(10), 
  MANAGER_ID       NUMBER(6,0), 
  DEPARTMENT_ID    NUMBER(4,0),
  TOTAL_SCORE      NUMBER(3,0)
);
CREATE TABLE SCORES (
  EVALUATION_ID   NUMBER(8,0), 
  PERFORMANCE_ID  VARCHAR2(2), 
  SCORE           NUMBER(1,0)
);


Ensuring Data Integrity in Tables

To ensure that the data in your tables satisfies the business rules that your application models, you can use constraints, application logic, or both.


☞Tip:

Wherever possible, use constraints instead of application logic. Oracle Database checks that all data obeys constraints much faster than application logic can.


Constraints can be enabled and disabled. By default, they are created in the enabled state.



About Constraint Types

The constraint types are:

  • Not Null

  • Unique


  • Primary Key

  • Foreign Key

  • Check, which requires that a value satisfy a specified condition

  • REF, which further describes the relationship between a REF column and the object that it references


The EMPLOYEES table does not have CHECK constraints. However, suppose that EMPLOYEES needs a new column, EMPLOYEE_AGE, and that every employee must be at least 18. The constraint CHECK (EMPLOYEE_AGE >= 18) enforces the business rule.


Tutorial: Adding Constraints to Existing Tables


To add a Not Null constraint using the ALTER TABLE statement:

ALTER TABLE PERFORMANCE_PARTS
MODIFY WEIGHT NOT NULL;


To add a Unique constraint using the Add Unique tool:

  1. In the list of tables, right-click SCORES.

  2. In the list of choices, select Constraint.

  3. In the list of choices, click Add Unique.


To add a Primary Key constraint using the Add Primary Key tool:

  1. In the list of tables, right-click PERFORMANCE_PARTS.

  2. In the list of choices, select Constraint.

  3. In the list of choices, click Add Primary Key.


To add a Primary Key constraint using the ALTER TABLE statement:

ALTER TABLE EVALUATIONS
ADD CONSTRAINT EVAL_EVAL_ID_PK PRIMARY KEY (EVALUATION_ID);



To add two Foreign Key constraints using the Add Foreign Key tool:

ALTER TABLE EVALUATIONS
ADD CONSTRAINT EVAL_EMP_ID_FK FOREIGN KEY (EMPLOYEE_ID)
REFERENCES EMPLOYEES (EMPLOYEE_ID);


To add a Check constraint using the Add Check tool:

  1. In the list of tables, right-click SCORES.

  2. In the list of choices, select Constraint.

  3. In the list of choices, click Add Check.



Tutorial: Adding Rows to Tables with the Insert Row Tool


To add rows to the PERFORMANCE_PARTS table using the Insert Row tool:


  1. In the list of tables, select PERFORMANCE_PARTS.

  2. In the right frame, click the tab Data.

    The Data pane appears, showing the names of the columns of the PERFORMANCE_PARTS table and no rows.

  3. In the Data pane, click the icon Insert Row.

  4. Click the icon Commit Changes after data insertion.


Tutorial: Changing Data in Tables in the Data Pane

when you change the values in the column,

 an asterisk appears to the left of the row number to indicate that the change has not been committed

Click the icon Commit Changes after data modification


Tutorial: Deleting Rows from Tables with the Delete Selected Row(s) Tool


Managing Indexes


You can create indexes on one or more columns of a table to speed SQL statement execution on that table. When properly used, indexes are the primary means of reducing disk input/output (I/O).

When you define a primary key on a table:

  • If an existing index starts with the primary key columns, then Oracle Database uses that existing index for the primary key. The existing index need not be Unique.

    For example, if you define the primary key (A, B), Oracle Database uses the existing index (A, B, C).

  • If no existing index starts with the primary key columns and the constraint is immediate, then Oracle Database creates a Unique index on the primary key.

  • If no existing index starts with the primary key columns and the constraint is deferrable, then Oracle Database creates a non-Unique index on the primary key.

For example, in "Tutorial: Adding Constraints to Existing Tables", you added a Primary Key constraint to the EVALUATION_ID column of the EVALUATIONS table. Therefore, if you select the EVALUATIONS table in the SQL Developer Connections frame and click the Indexes tab, the Indexes pane shows a Unique index on the EVALUATION_ID column.



Tutorial: Adding an Index with the Create Index Tool

CREATE INDEX EVAL_JOB_IX
ON EVALUATIONS (JOB_ID ASC) NOPARALLEL;

  1. In the list of tables, right-click EVALUATIONS.

  2. In the list of choices, select Index.

  3. In the list of choices, select Create Index.

Tutorial: Changing an Index with the Edit Index Tool

DROP INDEX EVAL_JOB_ID;

CREATE INDEX EVAL_JOB_IX
ON EVALUATIONS (JOB_ID DESC) NOPARALLEL;

  1. In the list of schema object types, expand Indexes.

  2. In the list of indexes, right-click EVAL_JOB_IX.

  3. In the list of choices, click Edit.

  4. In the Edit Index window, change Order to DESC.

  5. Click OK.

Tutorial: Dropping an Index

DROP INDEX EVAL_JOB_ID;


Dropping Tables



Creating and Managing Views

Tutorial: Creating a View with the Create View Tool

  1. In the list of schema object types, right-click Views.

  2. In the list of choices, click New View.

    The Create View window opens, with default values for a new view.

  3. For Schema, accept the default value, HR.

  4. For Name, enter SALESFORCE.

  5. If the SQL Query pane does not show, click the tab SQL Query

  6. In the SQL Query pane, in the SQL Query field:

    • After SELECT, type:

      FIRST_NAME || ' ' || LAST_NAME "Name", SALARY*12 "Annual Salary"

    • After FROM, type:

      EMPLOYEES WHERE DEPARTMENT_ID = 80


Creating Views with the CREATE VIEW Statement

CREATE VIEW EMP_LOCATIONS AS
SELECT e.EMPLOYEE_ID,
  e.LAST_NAME || ', ' || e.FIRST_NAME NAME,
  d.DEPARTMENT_NAME DEPARTMENT,
  l.CITY CITY,
  c.COUNTRY_NAME COUNTRY
FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l, COUNTRIES c
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND
 d.LOCATION_ID = l.LOCATION_ID AND
 l.COUNTRY_ID = c.COUNTRY_ID
ORDER BY LAST_NAME;

Result:

View EMP_LOCATIONS created.


Changing Queries in Views

CREATE OR REPLACE VIEW SALESFORCE AS
  SELECT FIRST_NAME || ' ' || LAST_NAME "Name",
  SALARY*12 "Annual Salary"
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 80 OR DEPARTMENT_ID = 20;


Tutorial: Changing View Names with the Rename Tool

RENAME SALESFORCE to SALES_MARKETING;

  1. In the list of views, right-click SALESFORCE.

  2. In the list of choices, select Rename.

  3. In the Rename window, in the New View Name field, type SALES_MARKETING.

  4. Click Apply.

  5. In the Confirmation window, click OK.


Dropping Views

DROP VIEW SALES_MARKETING;



Creating and Managing Sequences

Sequences are schema objects from which you can generate unique sequential values, which are very useful when you need unique primary keys. The HR schema has three sequences: DEPARTMENTS_SEQUENCE, EMPLOYEES_SEQUENCE, and LOCATIONS_SEQUENCE.

Sequences are used through the pseudocolumns CURRVAL and NEXTVAL, which return the current and next values of the sequence, respectively. After creating a sequence, you must initialize it by using NEXTVAL to get its first value. Only after you initialize a sequence does CURRVAL return its current value.


Tutorial: Creating a Sequence

CREATE SEQUENCE evaluations_sequence
INCREMENT BY 1
START WITH 1 ORDER;

To create EVALUATIONS_SEQUENCE using the Create Database Sequence tool:

  1. In the list of schema object types, right-click Sequences.

  2. In the list of choices, click New Sequence.

  3. In the Create Sequence window, in the Name field, type EVALUATIONS_SEQUENCE over the default value SEQUENCE1.

  4. If the Properties pane does not show, click the tab Properties.


Dropping Sequences

DROP SEQUENCE EVALUATIONS_SEQUENCE;

  1. In the list of schema object types, expand Sequences.

  2. In the list of sequences, right-click the name of the sequence to drop.

  3. In the list of choices, click Drop.


Creating and Managing Synonyms

A synonym is an alias for another schema object.
Some reasons to use synonyms are security (for example, to hide the owner and location of an object) and convenience.
Examples of convenience are:

  • Using a short synonym, such as SALES, for a long object name, such as ACME_CO.SALES_DATA

  • Using a synonym for a renamed object, instead of changing that object name throughout the applications that use it

    For example, if your application uses a table named DEPARTMENTS, and its name changes to DIVISIONS, you can create a DEPARTMENTS synonym for that table and continue to reference it by its original name.

Creating Synonyms

CREATE SYNONYM EMPL FOR EMPLOYEES;

To create the synonym EMP using the Create Database Synonym tool:

  1. In the list of schema object types, right-click Synonyms.

  2. In the list of choices, click New Synonym.

Dropping Synonyms

DROP SYNONYM EMP;




저작자표시 비영리 변경금지