一、准备工作
本机采用的是CentOS6.5的操作系统,安装的是Oracle 11g的数据库,安装过程并不复杂,可上网搜索。成功安装数据库之后,使用oracle用户登陆系统,然后在命令行输入以下命令:
[oracle@localhost~]$ cd $ORACLE_HOME/bin
#进入/home/oracle/bin目录
[oracle@localhostbin]$ lsnrctl start-- 打开监听(如果监听已经开启了,则忽略此步骤)
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-AUG-201415:28:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /oracle/11g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/11g/network/admin/listener.ora
Log messages written to/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNRfor Linux: Version 11.2.0.1.0 - Production
Start Date 06-AUG-2014 15:28:48
Uptime 0 days 0hr. 0 min. 0 sec
Trace Level off
Security ON: LocalOS Authentication
SNMP OFF
Listener Parameter File /oracle/11g/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@localhost~]$ sqlplus /nolog-- 打开sqlplus工具,以nolog的形式登陆
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 15:21:00 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL>conn / as sysdba-- 以sysdba连接
Connected to an idle instance.
SQL>startup-- 启动数据库
ORACLE instance started.
Total System Global Area 422670336bytes
Fixed Size 1336960bytes
Variable Size 310380928bytes
Database Buffers 104857600bytes
Redo Buffers 6094848bytes
Database mounted.
Database opened.
(如果要关闭数据库,可以使用以下命令)
SQL>shutdown immediateDatabase closed.
Database dismounted.
ORACLE instance shut down.
下面建一个表来检测当前数据库是否工作正常:
SQL>create table testbl (id integer, name char(10));
Table created.
-- 创建一个名为testbl的表
SQL>insert into testbl values(0,'Mickey');
1 row created.
-- 插入一行新数据
SQL>commit;
Commit complete.
SQL>select * from testbl;-- 能成功查询到表testbl中的数据,说明数据库工作正常
ID NAME
---------- ----------
0 Mickey
二、实验环境介绍
后文中使用是Oracle 11g默认安装的数据库,主要以HR这schema为例。以下是HR这个schema的关系表:
由于刚刚新建的数据库,很多用户是锁住的状态,如果要正常使用,需要先为HR用户解锁,下面来演示一下如何为HR用户解锁:
SQL> desc dba_users
Name Null? Type
------------------------------------------------- -----------------------
USERNAME NOT NULLVARCHAR2(30)
USER_ID NOT NULLNUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULLVARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULLVARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
SQL> select username, account_status
2 from dba_users
3 order by username;USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
ANONYMOUS EXPIRED & LOCKED
APEX_030200 EXPIRED & LOCKED
EXFSYS EXPIRED & LOCKED
FLOWS_FILES EXPIRED & LOCKED
HR EXPIRED & LOCKED
-- 这里会看到HR 用户被锁住了,且密码已过期,因此需要用管理员身份先为该用户解锁,并设置密码;
SQL> alter user hr identified by *******;User altered.
-- 使用alter命令为HR 用户设置密码
SQL> select username, account_status from dba_users;
USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN OPEN
HR LOCKED
-- HR 用户有了密码,但还是被锁住了
SQL> alter user hr account unlock;User altered.
SQL> select username, account_status from dba_users;USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN OPEN
HR OPEN
-- HR 用户已经解锁了,可以以HR用户正常登陆。
SQL> conn hr/******Connected.
HR用户成功登陆,至此,可以开始使用ORACLE数据库进行PL/SQL的学习了
三、设置sql*plus编辑器
在开始正式学习之前,还需要对sql*plus这个编译工具进行一些设置,以方便今后的使用:
[oracle@localhost ~]$ vim $ORACLE_HOME/ /sqlplus/admin/glogin.sql-- Copyright (c) 1988, 2005, Oracle. All Rights Reserved.
-- NAME
-- glogin.sql
-- DESCRIPTION
-- SQL*Plus globallogin "site profile" file
-- Add any SQL*Pluscommands here that are to be executed when a
-- user startsSQL*Plus, or uses the SQL*Plus CONNECT command.
-- USAGE
-- This script isautomatically run
define_EDITOR="vim"
-- 在glogin.sql这文件的最后加上define_EDITOR="vim"这一行,定义文本编辑器为vim。
使用hr用户登录到数据库后,使用SQL语句查询表employees。
SQL> select *
2* from employees;EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
----------- -------------------- ---------------- ------------- -------------------- ------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
--------------------------- --------------------------
197 Kevin Feeney KFEENEY 650.507.9822 23-MAY-06 SH_CLERK 3000
124 50
107 rows selected.
SQL> editselect *
from employees
/
~
--在定义了文本编译器后,可以在sql*plus中使用edit命令调用该文本编译器,编译上次写的语句。
-- 编辑完成后保存退出,使用/就可以执行刚才编辑的语句了
SQL> l-- 使用l命令,可以查看刚才编辑的语句
1 select *
2* from employees