Shulou(Shulou.com)06/01 Report--

SQL Plus has its own commands and environment, which provides access to Oracle databases. It enables you to enter and execute SQL, sql, sql, SQL +, and operating system commands to:

Formatting, performing storage, printing and printing calculations of query results

Check table and object definition

Develop and run batch scripts

Perform database management

You can use sql Plus to generate reports interactively to generate batch process reports and output the results to a text file, screen, or HTML file for browsing on the Internet. You can use the HTML output device exported by sql + to generate reports dynamically.

1.1SQLPlus Basics

After the implementation of 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

two

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 / 7934Blue7902

2 where EMPNO=7902

SQL > run

1 select from scott.EMP

2 where EMPNO=7902

three

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

REMARK, / /,-- comment description

SQL > REMARK test

SQL > select from scott.EMP

2 remark test

3 where empno=7934

Remark test

ERROR at line 2:

ORA-00911: invalid character

SQL > / Test

SQL > /

SQL > select from scott.EMP

2 / Test

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-Test

3 where empno=7934

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300SQL > 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: aDrex7934

PL/SQL procedure successfully completed.

SQL > select ENAME,JOB

2 from scott.emp where empno=:a

ENAME JOB

MILLER CLERK

SET ECHO ON/OFF controls whether its contents are displayed when the execution of Script is called

SET VERIFY ON/OFF decides whether to display the process of assignment substitution when executing a Sql command operation with parameter variables

Press enter to continue

PROMPT Before continuing, make sure you have your account card.

PAUSE Press RETURN to continue.

Clear the screen

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.