首页 > 代码库 > 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默认安装的数据库，主要以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的学习了
本文出自 “重剑无锋 大巧不工” 博客，请务必保留此出处http://wuyelan.blog.51cto.com/6118147/1537684