Oracle基础教程：SQL*PLUS与PL/SQL入门与实践

需积分: 1 197 浏览量更新于2024-07-26 收藏 3.18MB DOC 举报

Oracle基础学习是一份针对Oracle数据库应用系统设计和PL/SQL编程的教程，由赵元杰编撰，适合初学者和专业人员使用。该讲稿分为两大部分：第一部分是Oracle SQL*PLUS基础，涵盖了关系数据库系统(RDBMS)的概念，包括关系模型、Codd十二法则、RDBMS的构成（如内核和数据字典）、SQL与SQL*PLUS的区别，以及如何在不同操作系统环境下登录和使用SQL*PLUS。此外，还介绍了常用的SQL*PLUS附加命令，如登录、退出和描述命令。第二部分则是关于Oracle PL/SQL，深入讲解了PL/SQL语言，它是Oracle数据库中用于编写存储过程、函数和触发器的重要工具。这部分内容旨在为软件开发人员、DBA和系统分析师提供实践所需的PL/SQL基础知识，以便进行应用程序设计和数据库管理。赵元杰强调了讲稿的实用性和简洁性，避免过多理论，注重实际操作中的技巧和实例，旨在节省读者查找资料的时间。同时，内容覆盖了Oracle 8i/9i的最新技术和OCP培训大纲，适用于不同层次的学习者，包括初级、中级和高级数据库管理人员，以及计算机专业的大学生。然而，由于作者水平和个人知识局限，以及Oracle产品和内容的复杂性，可能存在一些错误和不足之处，作者鼓励读者在阅读过程中提出批评和建议，共同完善学习资料。这是一份实用且全面的Oracle入门指南，有助于读者快速掌握Oracle数据库的核心技能。

FISCAL_YEAR................................................................................................................................189

TO_MULTI_BYTE(<c>)及 TO_SINGLE_BYTE...........................................................................189

§2.2.5 其它的单记录函数...............................................................................................189

BFILENAME( <dir>, <file> )..........................................................................................................189

INSERT INTO file_tbl......................................................................................................................189

CONVERT (‘x’,’desc_set’ [, ‘source_set’]).....................................................................................190

DUMP( s,[,fmt [, start [, length ] ] ] )...............................................................................................191

SQL> col global_name for a30.........................................................................................................191

ORA816.US.ORACLE.COM Typ=1 Len=20 CharacterSet=ZHS16GBK: U,S,.,O,R....................191

EMPTY_BLOB() 和 EMPTY_CLOB() 函数.................................................................................191

Insert into proposal............................................................................................................................192

GREATEST( <exp_list> ).................................................................................................................192

GR.....................................................................................................................................................192

AC.....................................................................................................................................................192

SQL> select greatest('啊','安','天') from dual;..................................................................................193

GR.....................................................................................................................................................193

LEAST ( <exp_list> ).......................................................................................................................193

SQL> select least('啊','安','天') from dual;.......................................................................................193

LE......................................................................................................................................................193

UID 函数...........................................................................................................................................193

SQL> show user................................................................................................................................194

SQL> /...............................................................................................................................................194

USER 函数........................................................................................................................................194

SQL> select user from dual;.............................................................................................................194

USER.................................................................................................................................................194

SYSTEM...........................................................................................................................................194

USERENV( <opt> )..........................................................................................................................194

ISDBA 函数......................................................................................................................................195

SQL> show user................................................................................................................................195

USEREN...........................................................................................................................................196

FALSE...............................................................................................................................................196

SQL> connect sys/sys@ora816........................................................................................................196

USEREN...........................................................................................................................................196

FALSE...............................................................................................................................................196

SQL> connect internal......................................................................................................................196

USEREN...........................................................................................................................................196

SESSIONID 函数.............................................................................................................................196

ENTRYID 函数................................................................................................................................197

USERENV(‘ENTRYID’).................................................................................................................197

INSTANCE 函数..............................................................................................................................197

SQL> select userenv('INSTANCE') from dual;................................................................................198

USERENV('INSTANCE').................................................................................................................198

LANGUAGE 函数............................................................................................................................198

SQL> select userenv('LANGUAGE') from dual;.............................................................................198

USERENV('LANGUAGE')..............................................................................................................198

SIMPLIFIED CHINESE_CHINA.ZHS16GBK...............................................................................198

LANG 函数.......................................................................................................................................198

SQL> l...............................................................................................................................................198

SQL> /...............................................................................................................................................199

USERENV('LANG').........................................................................................................................199

ZHS...................................................................................................................................................199

TERMINAL 函数.............................................................................................................................199

SQL> select userenv('TERMINAL') from dual;..............................................................................199

USERENV('TERMINA....................................................................................................................199

ZHAOYUANJIE...............................................................................................................................199

VSIZE( <x> )....................................................................................................................................199

SQL> select vsize(user),user from dual;..........................................................................................199

§2.3 SQL 中的组函数..............................................................................................................200

§2.3.1 多记录组函数.......................................................................................................200

AVG( [ { DISTINCT | ALL}] ).........................................................................................................200

SQL> l...............................................................................................................................................200

SQL> /...............................................................................................................................................200

AVG(SAL)........................................................................................................................................200

MAX( [ { DISTINCT | ALL}] ).......................................................................................................201

SQL> select max(sal) from emp;......................................................................................................201

MAX(SAL)......................................................................................................................................201

MIN( [ { DISTINCT | ALL}] ).........................................................................................................201

SQL> select min(sal) from emp;......................................................................................................201

MIN(SAL)........................................................................................................................................201

STDDEV( [ { DISTINCT | ALL}] ).................................................................................................202

SQL> select stddev(sal) from emp;..................................................................................................202

STDDEV(SAL).................................................................................................................................202

VARIANCE( [ { DISTINCT | ALL}] ).............................................................................................202

SQL> select variance(sal) from emp;...............................................................................................202

VARIANCE(SAL)............................................................................................................................202

§2.3.2 带 GROUP BY 的计算.........................................................................................203

SQL> select deptno,count(*) ,sum(sal) from emp group by deptno;...............................................203

§2.3.3 用 HAVING 来限制分组的计算..........................................................................204

SQL> select deptno,count(*) ,sum(sal) from emp group by deptno................................................204

SQL> select deptno,count(*) ,sum(sal) from emp having count(*)>=5.........................................204

§2.4 控制和格式化输出...........................................................................................................205

§2.4.1 用 ORDER BY 子句来对结果进行排序.............................................................205

§2.4.2 用 BREAK 命令来对结果进行排列..................................................................206

SQL> break on deptno skip 2...........................................................................................................208

SQL> break on deptno skip page......................................................................................................209

§2.4.3 用 COMPUTE 命令对结果进行格式化..............................................................211

COMP[UTE] [function [LAB[EL] text] ..........................................................................................211

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

ON {expr|column|alias|REPORT|ROW} ...].....................................................................................211

MAX[IMUM] NUMBER, CHAR,NCHAR, VARCHAR2(VARCHAR),.......................................211

NVARCHAR2 (NCHARVARYI NG) 类型的最大值.....................................................................211

LAB[EL] text....................................................................................................................................212

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

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

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

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

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

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

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

JOB ENAME SAL............................................................................................................................212

ANALYST SCOTT 3000..................................................................................................................212

FORD 3000.......................................................................................................................................212

TOTAL 6000.....................................................................................................................................213

CLERK SMITH 800.........................................................................................................................213

JAMES 950.......................................................................................................................................213

TOTAL 4150.....................................................................................................................................213

SALESMAN WARD 1250...............................................................................................................213

TOTAL 5600.....................................................................................................................................213

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

SQL> BREAK ON REPORT...........................................................................................................213

SAL EMPNO....................................................................................................................................214

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

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

2 FROM DEPT, EMP.......................................................................................................................214

5 ORDER BY DNAME;...................................................................................................................214

DNAME ENAME SAL....................................................................................................................214

Dept Maximum 5000........................................................................................................................215

SQL> COLUMN DUMMY NOPRINT...........................................................................................215

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

DEPTNO ENAME SAL...................................................................................................................216

10 CLARK 2450...............................................................................................................................216

10 KING 5000...................................................................................................................................216

SQL> COLUMN DUMMY NOPRINT...........................................................................................217

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

DEPTNO ENAME SAL...................................................................................................................217

10 CLARK 2450...............................................................................................................................217

10 KING 5000...................................................................................................................................217

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

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

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

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

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

剩余63页未读，继续阅读

hpjy_langw

粉丝: 0
资源: 2

Oracle基础教程：SQL*PLUS与PL/SQL入门与实践

ORACLE基础学习.ppt

Oracle基础学习ppt

Oracle基础学习资料

oracle 基础入门教程图解

Oracle dba学习路线

oracle数据库学习官方文档

oracle ebs学习资料

oracle ebs 学习办法

oracle数据库快速学习

oracle数据库学习csdn

最新资源