SQL*Plus Quick Start

SQL Plus 有自己的命令和环境，它提供对Oracle数据库的访问。它使您能够输入和执行SQL、sql、sql、SQL +和操作系统命令来执行以下操作：

格式化，执行查询结果的存储、打印和打印计算

检查表和对象定义

开发和运行批处理脚本

执行数据库管理

您可以使用sql Plus 以交互方式生成报告，以生成批处理过程报告，并将结果输出到文本文件、屏幕或HTML文件，以便在Internet上浏览。您可以使用sql +输出的HTML输出设备动态生成报表。
1.1SQLPlus Basics



cl buff 执行后
SQL> /
SP2-0103: Nothing in SQL buffer to run.
SQL> a where TNAME='CDB_APPLY'
2* where TNAME='CDB_APPLY'
SQL> /

TNAME

TABTYPE CLUSTERID

CDB_APPLY
VIEW
SQL> select * from tab
2 where tname<>''
3 ;

no rows selected

SQL> list 2
2 where tname<>''
SQL> del 2
SQL> list
1 select from tab
2
SQL> select from scott.EMP
2 where EMPNO=7934
3 ;

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL

  COMM     DEPTNO

  7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300
               10

SQL> list 2
2 where EMPNO=7934
SQL> change /7934/7902
2 where EMPNO=7902
SQL> run
1 select from scott.EMP
2 where EMPNO=7902
3

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL

  COMM     DEPTNO

  7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000
               20

REMARK， / /，--注释说明
SQL> REMARK 测试
SQL> select from scott.EMP
2 remark 测试
3 where empno=7934;
remark 测试

ERROR at line 2:
ORA-00911: invalid character

SQL> / 测试
SQL> /
SQL> select from scott.EMP
2 /测试
3 */
4 where empno=7934;

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL

  COMM     DEPTNO

  7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300
               10

SQL> select * from scott.EMP
2 --测试
3 where empno=7934;

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL

  COMM     DEPTNO

  7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300

SQL> SELECT &SORTCOL, job

2 FROM &MYTABLE
3 ;
Enter value for sortcol: ename
old 1: SELECT &SORTCOL, job
new 1: SELECT ename, job
Enter value for mytable: scott.emp
old 2: FROM &MYTABLE
new 2: FROM scott.emp

ENAME JOB

SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
KING PRESIDENT
TURNER SALESMAN
JAMES CLERK
FORD ANALYST

ENAME JOB

MILLER CLERK

12 rows selected.
SQL> define
DEFINE _DATE = "2017-12-16 14:29:31" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "CLONEPDB_PLUG" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE = "1202000100" (CHAR)
SQL> DEFINE A = 7934
SQL> select ENAME,JOB
2 from scott.emp where empno=&A;
old 2: from scott.emp where empno=&A
new 2: from scott.emp where empno=7934

ENAME JOB

MILLER CLERK
SQL> unDEFINE A
SQL> variable a number;
SQL> print a;

     A

SQL> exec :a:=7934

PL/SQL procedure successfully completed.

SQL> select ENAME,JOB
2 from scott.emp where empno=:a;

ENAME JOB

MILLER CLERK
SET ECHO ON/OFF 控制是否在调用执行Script的时候显示其内容
SET VERIFY ON/OFF 决定是否在执行带有参数变量的Sql命令操作时，显示赋值代入的过程

按回车继续
PROMPT Before continuing, make sure you have your account card.
PAUSE Press RETURN to continue.
清屏
CLEAR SCREEN

SQL> CLEAR column
columns cleared
SQL> CLEAR column
columns cleared
SQL> VARIABLE ret_val NUMBER
SQL> BEGIN
2 :ret_val:=4;
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> PRINT RET_VAL

RET_VAL

     4

SQL> variable abc number
SQL> exec :abc :=123;

PL/SQL procedure successfully completed.

SQL> select :abc from dual;

  :ABC

   123           

SQL> create table mytab (col1 number, col2 varchar2(10));

Table created.
SQL> var xyz varchar2(10)
SQL> begin
2 :xyz :='test'
3 ;
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> insert into mytab values(:abc,:xyz);

1 row created.

SQL> select * from mytab;

  COL1 COL2

   123 test

SQL> drop table mytab purge;

Table dropped.
SQL> alter session set container=pdbtest;

Session altered.
SQL> CREATE OR REPLACE PACKAGE EmpPack
2 AS
3 type EmpInfoTyp is ref cursor;
4 PROCEDURE EmpInfoRpt (emp_cv OUT EmpInfoTyp);
5 end EmpPack;
6 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY EmpPack AS
2 PROCEDURE EmpInfoRpt (emp_cv OUT EmpInfoTyp) AS
3 BEGIN
4 OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY
5 FROM HR.EMP_DETAILS_VIEW
6 WHERE JOB_ID='SA_MAN' ;
7 END;
SQL> VARIABLE cv REFCURSOR
SQL> EXECUTE EmpPack.EmpInfoRpt(:cv)

PL/SQL procedure successfully completed.

SQL> PRINT cv

EMPLOYEE_ID SALARY

    145      14000
    146      13500
    147      12000
    148      11000
    149      10500

8 END;
9 /

Package body created.
SQL> VARIABLE cv REFCURSOR
SQL> EXECUTE EmpPack.EmpInfoRpt(:cv)

PL/SQL procedure successfully completed.

SQL> PRINT cv

EMPLOYEE_ID SALARY

    145      14000
    146      13500
    147      12000
    148      11000
    149      10500