20130828SQL PLus学习

来源：互联网发布：我国网络基础设施编辑：程序博客网时间：2024/05/12 03:35

学习笔记供大家分享

解锁用户
alter user scott identified by zitadmin account unlock

用sqlplus进入
命令:help index
下查看所有的命令

SQL> help index

Enter Help [topic] for help.

@             COPY         PAUSE                    SHUTDOWN
@@            DEFINE       PRINT                    SPOOL
/             DEL          PROMPT                   SQLPLUS
ACCEPT        DESCRIBE     QUIT                     START
APPEND        DISCONNECT   RECOVER                  STARTUP
ARCHIVE LOG   EDIT         REMARK                   STORE
ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
BREAK         EXIT         REPHEADER                TTITLE
BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
CHANGE        HELP         RESERVED WORDS (PL/SQL) VARIABLE
CLEAR         HOST         RUN                      WHENEVER OSERROR
COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
COMPUTE       LIST         SET                      XQUERY
CONNECT       PASSWORD     SHOW

set linesize 200 这个是把宽度设置成200

/表示重新执行上次sql,记住一定是sql 如果你上次执行的是命令。这个是执行你命令的前面执行的sql

SQL> show user
USER 为 "SCOTT
SQL> conn/as sysdba;
已连接。
SQL> show user
USER 为 "SYS"

解释：conn/as sysdba 中不用输入密码是因为操作系统认证的关系，以DBA的身份进入

show user 显示当前的用户

连接用户

SQL> conn scott/zitadmin;
已连接。

退出连接
SQL> conn scott/zitadmin;
已连接。
SQL> disconn scott/zitadmin;
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

show all显示所有操作
REFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE 为 OFF
pno 1
recsep WRAP
recsepchar " " (hex 2
release 1102000100
repfooter OFF 为 NUL
repheader OFF 为 NUL
securedcol is OFF
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility
sqlprefix "#" (hex 23
sqlprompt "SQL> "
sqlterminator ";" (he
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF 为下一条 S
underline "-" (hex 2d
USER 为 "SCOTT"
verify ON
wrap : 将换至下一行
errorlogging is OFF

显示刚刚设置的行宽
SQL> show linesize
linesize 200

清空屏幕

SQL> clear screen

清除的内容可以有：
SQL> help clear

CLEAR
-----

Resets or erases the current value or setting for the specified option.

CL[EAR] option ...

where option represents one of the following clauses:
     BRE[AKS]
     BUFF[ER]
     COL[UMNS]
     COMP[UTES]
     SCR[EEN]
     SQL
     TIMI[NG]

SQL> clear buff
buffer 已清除
SQL>

表：all_objects

分页显示
SQL> set pause on

set pause off

please input enter

select * from all_objects;
关闭分页显示

SQL> show errors
没有错误。
SQL> CREATE or replace PROCEDURE HR.PROC1 AS
2   BEGIN
3 :P1 := 1;
4   END;
5   /

警告: 创建的过程带有编译错误。

SQL>
SQL> show errors
PROCEDURE HR.PROC1 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00049: 错误的赋值变量 'P1'
SQL> grant dba to scott;

change /原来的/想要的
C /ENMAE/ENAME

SP2-0110: 无法创建保存文件 "afiedt.buf"
SQL> ;
1* select * from emp
SQL> select deptno,ename,sal from emp where deptno=30;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        30 ALLEN            1600
        30 WARD             1250
        30 MARTIN           1250
        30 BLAKE            2850
        30 TURNER           1500
        30 JAMES             950

已选择6行。

SQL> edit
SP2-0110: 无法创建保存文件 "afiedt.buf"
SQL>

没有通过

保存文件:保存上一次的脚本

SQL> save d:/a.sql
已创建 file d:/a.sql
SQL>
spool是保存多少条都可以
spool d:\b.sql
现在再做一些操作
select * from emp;
select * from dept;
spool off

append在原文件后面追加
d:\b.sql
spool d:\b.sql append

SQL> ;
1* select * from dept
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
        30

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
        10

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000
        20

      7839 KING       PRESIDENT            17-11月-81           5000
        10

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100
        20

      7900 JAMES      CLERK           7698 03-12月-81            950
        30

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-12月-81           3000
        20

      7934 MILLER     CLERK           7782 23-1月 -82           1300
        10

已选择14行。

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> spool off

语法：
spool d:\b.sql
select * from emp
select * from dept
spool off

这样就是保存select * from emp
select * from dept
save只能保存上一次运行的语句
spool可以保存多条

在SQL Plus中执行sql文件

方法：@....\a.sql

@D:\app\windsna\product\11.2.0\dbhome_1\demo\schema\human_resources\hr_popul.sql