首页 > 代码库 > 1.PL/SQL——准备工作和实验环境简介

1.PL/SQL——准备工作和实验环境简介

一、准备工作

本机采用的是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 immediate

Database 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默认安装的数据库,主要以HRschema为例。以下是HR这个schema的关系表:

wKioL1PlE9GjirsoAAH0oDgpMoc059.jpg

由于刚刚新建的数据库,很多用户是锁住的状态,如果要正常使用,需要先为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的学习了


本文出自 “重剑无锋 大巧不工” 博客,请务必保留此出处http://wuyelan.blog.51cto.com/6118147/1537684