学习Oracle基础知识：SQL*PLUS、PL/SQL详解与应用设计

oracle

PL/SQL

需积分: 0 60 浏览量更新于2024-01-04 2 收藏 4.72MB DOC 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

本讲稿《Oracle8i/9i数据库基础》是由赵元杰于2003年1月4日所著，是作者多年工作和授课经验的总结。该教材主要包括两个部分，第一部分是关于Oracle SQL*PLUS基础，主要讲述了Oracle应用系统设计的基本知识并给出了一些实用的实例；第二部分则是介绍了Oracle PL/SQL知识，主要讲述了Oracle数据库PL/SQL程序设计所需的基本知识，为应用设计提供了必要的基础知识。这本教材的特点在于，首先作者用简单明了的语句对各个部分的内容进行解释,省去了一些理论性的解释，使得读者更容易理解。其次，在介绍各个知识点的同时，作者还提供了许多应用设计和开发中的具体例子，为软件人员提供了一些借鉴，省去了查阅大量资料的时间。同时，教材还给出了许多资料所没有提供的一些使用技巧，如导出触发器等，使读者能够更全面地了解Oracle数据库的应用。总之，该教材内容基本涵盖了当时Oracle8i的最新内容，同时也覆盖了部分Oracle9i的内容。对于想系统学习Oracle数据库的人来说，这本书确实是非常合适的选择。通过学习本教材，读者能够系统地了解Oracle数据库的基础知识，掌握SQL*PLUS和PL/SQL的使用方法，提高自己在Oracle数据库应用设计与开发中的技能。作者的经验总结和丰富的案例分析，能够帮助读者更深入地理解Oracle数据库的应用。因此，对于希望深入学习Oracle数据库的人员来说，这本教材是一本不可多得的学习资料。总而言之，赵元杰所著的《Oracle8i/9i数据库基础》是一本权威、实用的Oracle数据库教材，内容详实全面，对于想要系统学习Oracle数据库的人来说是一本难得的宝典。通过学习该教材，读者能够对Oracle数据库有一个系统全面的认识，掌握SQL*PLUS和PL/SQL的基础知识，提高自己在实际工作中的应用能力。因此，这本教材无疑是在Oracle数据库学习领域具有很高的参考价值，是一本不可多得的学习资料。

资源详情

资源推荐

LAB[EL] text....................................................................................................................................150

OF {expr|column|alias} ....................................................................................................................150

OF 子串或表达式或别名.................................................................................................................150

ON {expr|column|alias|REPORT|ROW} .........................................................................................150

ON 子串或表达式或别名或 REPORT 或 ROW.............................................................................150

SQL> BREAK ON JOB SKIP 1.......................................................................................................150

SQL> COMPUTE SUM LABEL ’TOTAL’ OF SAL ON JOB........................................................150

4 ORDER BY JOB, SAL;.................................................................................................................150

JOB ENAME SAL............................................................................................................................150

ANALYST SCOTT 3000..................................................................................................................151

FORD 3000.......................................................................................................................................151

TOTAL 6000.....................................................................................................................................151

CLERK SMITH 800.........................................................................................................................151

JAMES 950.......................................................................................................................................151

TOTAL 4150.....................................................................................................................................151

SALESMAN WARD 1250...............................................................................................................151

TOTAL 5600.....................................................................................................................................151

SQL> COMPUTE SUM OF SAL ON REPORT.............................................................................151

SQL> BREAK ON REPORT...........................................................................................................151

SAL EMPNO....................................................................................................................................151

SQL> BREAK ON DNAME SKIP 1...............................................................................................151

SQL> SELECT DNAME, ENAME, SAL.......................................................................................151

2 FROM DEPT, EMP.......................................................................................................................151

5 ORDER BY DNAME;...................................................................................................................152

DNAME ENAME SAL....................................................................................................................152

Dept Maximum 5000........................................................................................................................152

SQL> COLUMN DUMMY NOPRINT...........................................................................................152

SQL> COMPUTE SUM OF SAL ON DUMMY.............................................................................152

DEPTNO ENAME SAL...................................................................................................................152

10 CLARK 2450...............................................................................................................................152

10 KING 5000...................................................................................................................................152

SQL> COLUMN DUMMY NOPRINT...........................................................................................153

SQL> COMPUTE SUM OF SAL ON DUMMY.............................................................................153

DEPTNO ENAME SAL...................................................................................................................153

10 CLARK 2450...............................................................................................................................153

10 KING 5000...................................................................................................................................153

§2.5 配置会话环境...................................................................................................................153

§2.5.1 ARRAYSIZE（取回的行数）.............................................................................154

§2.5.2 AUTOCOMMIT(自动提交).................................................................................154

§2.5.3 LINESIZE(行显示宽度).......................................................................................154

§2.5.4 LONG(长类型显示字节数)..................................................................................154

SET LONG{80|integer}....................................................................................................................154

§2.5.5 PAGESIZE(页行数)..............................................................................................155

§2.5.6 PAUSE(暂停).........................................................................................................155

SET PAUSE [ ON | OFF ].................................................................................................................155

SQL> set pause on............................................................................................................................155

§2.5.7 SPACE(列间空格).................................................................................................155

§2.5.8 Termout (启/停屏幕显示).....................................................................................155

SET TERMOUT { ON | OFF }.........................................................................................................155

§2.5.9 ECHO (启/停命令显示)........................................................................................156

§2.5.10 TRANSACTION (启动事务).............................................................................156

§2.5.11 SHOW ALL(列出所有参数)..............................................................................156

SQL> show all..................................................................................................................................156

SQL>.................................................................................................................................................158

§2.6 格式化输出.......................................................................................................................158

§2.6.1 一般数据的格式化输出.......................................................................................158

COL[UMN] [{ column | expr } [ option_1 ... option_n ] ]...............................................................158

FOLD_A[FTER]...............................................................................................................................159

FOLD_B[EFORE]............................................................................................................................159

FOR[MAT] format............................................................................................................................159

SQL> col ename heading 姓名 format a10......................................................................................159

SQL...................................................................................................................................................159

COLUMN SALARY FOR $9,999,999.99........................................................................................159

§2.6.2 日期的格式化输出...............................................................................................160

SQL> select sysdate from dual;........................................................................................................160

SYSDATE.........................................................................................................................................160

SQL> connect scott/tiger..................................................................................................................160

§2.7 加标题...............................................................................................................................161

§2.8 建立简单报告...................................................................................................................161

SQL>SPOOL OFF............................................................................................................................163

§2.9 输入变量...........................................................................................................................163

Select sid, serial#,username, command from v$session...................................................................163

SQL> Select sid, serial#,username, command from v$session........................................................163

SQL> Select sid, serial#,username, command from v$session;.......................................................164

SQL> set define ?..............................................................................................................................164

SQL> show define............................................................................................................................165

第三章表及索引的定义操作.........................................................................................................166

§3.1 建立表结构.......................................................................................................................166

§3.1.1 建立表结构命令...................................................................................................166

CREATE TABLE [USER.] table_name...........................................................................................166

§3.1.2 建立表结构例子...................................................................................................168

CREATE TABLE CHECKUP_HISTORY.......................................................................................168

CREATE TABLE SEAPARK.CHECKUP_HISTORY....................................................................169

CHECKUP_NO NUMBER(10) NOT NULL,.................................................................................169

PCTFREE 20.....................................................................................................................................169

PCTUSED 60....................................................................................................................................169

§3.1.3 建立临时表结构...................................................................................................169

SQL> create global temporary table myemp as select * from emp;................................................169

SQL> desc myemp............................................................................................................................169

SQL> select * from myemp;.............................................................................................................170

SQL> insert into myemp values('赵元杰',32456.99,10,'12');..........................................................170

SQL> select * from myemp;.............................................................................................................170

SQL> connect sys/sys.......................................................................................................................170

SQL> /...............................................................................................................................................170

SQL> drop table myemp;..................................................................................................................170

§3.3 修改表结构.......................................................................................................................170

§3.3.1 修改表结构命令...................................................................................................171

§3.3.2 修改表结构例子...................................................................................................171

CREATE TABLE HOLD_TANK AS SELECT TANK_NO, CHIEF_CARETAKER_NAME......172

FROM TANK;...................................................................................................................................172

Alter table emp2 storage( next 256k pctincrease 0 );.......................................................................172

ALTER TABLE . . . . . . DROP COLUMN [ CASCADE CONSTRAINTS ];................................172

ALTER TABLE . . . . . . MOVE TABLESPACE ;............................................................................172

ALTER TABLE . . . . . . SET UNUSED COLUMN;.......................................................................173

§3.3.3 删除表结构............................................................................................................173

DROP TABLE [user.]table_name[CASCADE CONSTRAINTS]...................................................173

§3.3.4 使用 CHECK 作限制约束....................................................................................174

§3.3.5 使用 UNRECOVERABLE 创建表.......................................................................174

SQL> create table new_emp as select * from emp UNRECOVERABLE;.....................................174

§3.3.6 将表移动到新的数据段或新的表空间...............................................................175

Alter table emp MOVE.....................................................................................................................175

SQL> alter table emp move tablespace user_data............................................................................176

Table altered......................................................................................................................................176

SQL> alter table dept move tablespace user_data............................................................................176

Table altered......................................................................................................................................176

SQL> alter table BONUS move tablespace user_data.....................................................................176

Table altered......................................................................................................................................176

SQL> select tablespace_name,table_name,initial_extent from user_tables;...................................176

§3.3.7 手工分配表的存储空间.......................................................................................176

ALTER TABLE emp.........................................................................................................................177

§3.3.8 标记不使用的列和删除不使用的列...................................................................177

ALTER TABLE LONG_TAB DROP COLUMN LONG_PICS;.....................................................177

SQL> alter table emp2 set unused(comm);......................................................................................177

Table altered......................................................................................................................................177

SQL> select * from emp;..................................................................................................................177

SQL> desc emp.................................................................................................................................178

SQL> alter table emp drop unused columns checkpoint;.................................................................178

Table altered......................................................................................................................................178

§3.3 主键...................................................................................................................................179

§3.3.1 创建主键................................................................................................................179

CREATE TABLE [schema.]table_name …......................................................................................179

CREATE TABLE dept......................................................................................................................179

剩余63页未读，继续阅读

sanl85868183

粉丝: 2
资源: 9

学习Oracle基础知识：SQL*PLUS、PL/SQL详解与应用设计

oracle数据库基础.pptx

oracle数据库基础.ppt

oracle数据库基础命令

oracle数据库基础练习

oracle数据库基础数据的导入与导出

oracle12c数据库基础教程pdf

oracle数据库快速学习

oracle数据库原理基础架构

应该如何学习oracle数据库

Oracle数据库视频学习资料

oracle数据库学习官方文档

oracle数据库教程PPT

Oracle数据库管理员怎么学习

oracle数据库11204安装包

Oracle基础架构是什么？Oracle基础架构平台作用是什么？Oracle数据库是作用于Oracle基础架构平台上的嘛，Oracle基础架构有什么用？好处和弊端是什么

oracle必知必会 pdf

oracle数据库管理与开发

Oracle数据库大作业

oracle数据库学习csdn

如何快速的学习Oracle数据库知识

最新资源