-
- 安装、配置、连接、卸载
Oracle数据库安装及协助
数据库及客户端下载：
- 打开下载清单：Database Software Downloads | Oracle
- 勾选“Accept License Agreement”，找到所需版本、所需系统的Oracle数据库进行下载。
- 如需下载客户端，则点击所需版本、所需系统的“See All”链接进入下一页面，勾选“Accept License Agreement”，然后找到带client字样的链接，选择64位或32位的下载。
- 下载前会提示登录到oracle的账户，如果没有这里可以注册一个，如果有就直接登录，登录完成后会直接下载文件。
交互式安装（Windows本地桌面类）
| 关于账户： Linux环境必须创建专有账户，最佳实践是创建一个拥有Oracle数据库的账户，和一个拥有Grid Infrastructure软件的账户，这样就可以把数据库管理域和系统管理域的任务分开。在学习模式下通常创建一个账户用于两种功能，账户名oracle，隶属于主要组oinstall，次级组dba。 Windows环境应当创建专有账户。在学习模式下一般在具备管理权限的账户下安装Oracle数据库，安装程序会创建该账户所隶属的操作组ORA_DBA。 |
解压运行setup.exe（Linux版本在图形终端下运行解压目录下的runInstall.sh）
配置安全更新：取消勾选“我希望通过My Oracle Support接收安全更新”，点下一步，提示没有提供电子邮件地址，点是。
安装选项：勾选“创建和配置数据库”
系统类：选“桌面类”
Oracle主目录用户选择：勾选“创建新Windows用户”并输入用户名如Oracle，口令如123456，提示口令不符合标准是否继续，点“是”
典型安装：信息如下：
Oracle基目录：E:\App\Oracle
软件位置：E:\App\Oracle\product\12.1.0\dbhome_1
数据库文件位置：E:\App\Oracle\oradata
数据库版本：企业版
字符集：UTF-8
全局数据库名/口令：orcl/123456
创建为容器数据库（已勾选）——可插入数据库名：pdborcl
提示口令不符合标准是否继续，点“是”
先决条件检查：
概要：截屏保存信息。如果要在其他机器上静默安装，可点击“保存响应文件”保存以备用。
安装产品：
当弹出联网控制时，选允许。（有两次）
当弹出Database Configuration Assistant时，点击口令管理，管理口令。
如不填口令，则默认为前面步骤设定的口令。
这里SYS和SYSTEM都设为123456
完成：
如果要创建另一个数据库，使用Database Configuration Assistant（简称DBCA）。
命令提示符验证安装：
| C:\Users\TWX>sqlplus …… 请输入用户名: system/123456 …… SQL> |
若提示“不是内部或外部命令，也不是可运行的程序或批处理文件”，则需在环境变量path中追加值：
E:\App\Oracle\product\12.1.0\dbhome_1\NETWORK\ADMIN
交互式安装（Linux虚拟机远程服务器类）
静默式安装
如果要在多台机器上安装，或者设计一个可重复的自动过程，在上述单例安装的“概要”（“Summary”）这一步时，点击“保存响应文件”（“Save Response File”），保存生成的db_install.rsp文件，然后取消安装。将db_install.rsp拷贝并替换掉目标机器安装文件夹下的response\db_install.rsp文件，然后返回database文件夹，命令行运行setup.exe -silent -responseFile db_install.rsp即可进行静默无图形界面安装。如果要即使先决条件检查失败也继续安装，可追加参数-ignoreSysPrereqs。
用DBCA创建数据库
除了在安装数据库的“安装选项”步骤中选择“创建和配置数据库”外，还可以在安装完成后用DBCA创建和配置数据库。
Windows上启动DBCA的两种方法：
方法一：开始->(程序)->Oracle - OraDB12Home1->(配置和移植工具)->Database Configuration Assistant
方法二：cmd运行dbca.bat（安装数据库时该脚本已被包含在系统搜索路径中）
Linux上启动DBCA的步骤：
- 设置环境变量（DISPLAY、ORACLE_BASE和ORACLE_HOME依实际情况而定）：
export DISPLAY=myhost:0.0
export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH - 注意：Linux上如果Oracle可执行文件与其他可执行文件同名（例如rman既是Oracle工具，也是SUSE Linux实用程序），应确保将$ORACLE_HOME/bin目录放在搜索路径的开头。
- 启动DBCA：运行位于$ORACLE_HOME/bin目录下的dbca脚本
DBCA对话框响应操作示例：
1）数据库操作：
配置
管理控制台（OEM）登陆：
服务配置：
Oracle完成安装后，会在系统中注册服务，其中以下两个服务必须启动，否则Oracle将无法正常使用：
- OracleServiceORCL：数据库主服务，命名规则：OracleService数据库名称。
- OracleOraDB12Home1TNSListener：监听服务，客户端借此连接到数据库。
监听器配置：
要从SQL Developer等客户端访问Oracle，需先配置监听器。
- 启动Net Configuration Assistant -> 监听程序配置 -> 添加
- 监听程序名默认LISTENER，输入主目录用户口令，点下一步
- 协议默认TCP，点下一步
- 使用标准端口号1521，点下一步
- 不配置另一个监听程序，点下一步
如果提示“不能创建监听程序”，删除下面这个文件后重新配置：
E:\App\Oracle\product\12.1.0\dbhome_1\NETWORK\ADMIN\listener.ora
卸载：
- 首先关闭所有Oracle数据库服务（所有Oracle开头的服务）。
- 命令行运行 ...\（版本号）\dbhome_1\deinstall\deinstall\deinstall.bat 开启卸载，具体界面操作：
指定要取消配置的所有单实例监听程序 【LISTENER】：可以直接选择回车，或者可以输入LISTENER，然后回车。
指定在此Oracle主目录中配置的数据库名列表【BOOK,ORCL】：若没有新增数据库，则仅有ORCL数据库名；若有新增将显示所有数据库名。可以直接选择回车，或者可以输入BOOK,ORCL，然后回车。
指定此数据库的类型：默认即可，然后回车。
指定数据库诊断目标位置：: 默认即可，然后回车。
指定数据库ASM:FS使用的储存类型【】：填写FS，然后回车。
指定数据库spfile位置【】：直接回车即可。
是否继续，按提示填写“y”或“是”，然后回车开始卸载。
注：如果用Universal Installer 进行卸载，会提示用命令行，所以直接用以上步骤。
- 删除注册表中的相关内容：
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 删除该项；
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI 删除该项下除了Microsoft ODBC FOR ORACLE外的所有带Oracle字样的项。
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services 删除该项下所有Oracle、OraWeb开头的项；
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application 删除该项下所有Oracle开头的项；
HKEY_CLASSES_ROOT 删除该项下所有Ora、Oracle、Orcl、EnumOra开头的项；
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu\Programs 删除该项下所有oracle开头的项； - 环境变量PATH、CLASSPATH（若有）中删除含有Oracle字样的值。
- 删除安装目录及C:\Program Files\Oracle，删除ORACLE_BASE目录。
- 删除开始菜单Oracle程序。
- C盘搜索oracle，删除oracle开头的所有文件夹。
- 重启电脑。
Oracle 11g数据库的组成
Oracle的整体架构（Overview of Primary Components）：
| User process ↓ Server process(PGA，程序全局区域，单个会话共享) ↓ | |||
| 实例Instance | SGA（系统全局区域，所有会话共享） | Shared Pool | Library Cache |
| Data Dictionary Cache | |||
| Database Buffer Cache | |||
| Redo Log Buffer | |||
| (Java Pool) | |||
| (Large Pool) | |||
| PMON、SMON、DBWR、LGWR、CKPT、Others | |||
| 数据库Database | Data files | ||
| Control files | |||
| Redo Log files | |||
| Parameter file | |||
| Password file | |||
| Archived Log files | |||
Oracle可以创建多个oracle数据库，一个oracle数据库由实例和数据库构成。如默认安装时创建的orcl数据库外还可再创建其它数据库。创建的数据库将在$oracleHome/oradata/数据库名 目录下以一个个的*.DBF文件体现出来。
1、数据库是一系列物理文件的集合（数据文件，控制文件，联机日志，参数文件等）；Oracle数据库由操作系统文件组成，这些文件也称为数据库文件，为数据库信息提供实际物理存储区。Oracle数据库包括逻辑结构和物理结构。数据库的物理结构包含数据库中的一组操作系统文件。数据库的逻辑结构是指数据库创建之后形成的逻辑概念之间的关系，如表、视图、索引等对象。
2、实例则是一组Oracle后台进程/线程以及在服务器分配的共享内存区。
Oracle 11g数据库服务
Oracle * VSS Writer Service -- Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。(非必须启动)
OracleDBConsole* -- Oracle数据库控制台服务；在运行Enterprise Manager(企业管理器EM)的时候，需要启动这个服务；此服务被默认设置为自动开机启动的(非必须启动)
OracleJobScheduler* -- Oracle作业调度服务。此服务被默认设置为禁用状态(非必须启动)
OracleMTSRecoveryService -- 服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。恢复、闪回需要开启该服务(非必须启动)
OracleOraDb11g_home1ClrAgent -- Oracle数据库.NET扩展服务的一部分。 (非必须启动)
OracleOraDb11g_home1TNSListener -- 监听器服务，服务只有在数据库需要远程访问或使用SQL Developer等工具的时候才需要，此服务被默认的设置为开机启动(非必须启动)
OracleService* -- 数据库服务，是Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常操作。此服务被默认的设置为开机启动。(必须启动)
连接Oracle
SQL Plus 连接
打开SQL Plus，按提示输入用户名和口令，如在安装时解锁了的用户scott，口令为：tiger。
也可以直接在命令行中输入命令登录：sqlplus 用户名/密码
SQL Developer 连接（不需安装Oracle客户端）
- 宿主机与虚拟机ip要在同一网段，虚拟机防火墙要开放1521端口（命令行 telnet 虚拟机ip 1521 验证成功否）。
- SQL Developer中新建连接，填写远程主机ip、端口、sid、用户名、口令，测试成功即可连接！
SID是指定数据库服务器上的全局数据库名称，默认安装的话一般是orcl
PLSQL Developer远程连接方法一：需要下载Instant Client（oracle的简便客户端）（听说64位的没用）
- 宿主机与虚拟机ip要在同一网段，虚拟机防火墙要开放1521端口（命令行 telnet 虚拟机ip 1521 验证成功否）。
- 宿主机到官网下载Instant Client（地址见后面 Instant Client 补充说明）并解压到目录例如:D:/instantclient_11_2
- 新建子目录和文件 D:/instantclient_11_2/NETWORK/ADMIN/tnsnames.ora ，编辑 tnsnames.ora，输入需要远程连接的字符串：
（或者直接将服务器的同名文件复制到 D:/instantclient_11_2/NETWORK/ADMIN/ 目录下进行编辑修改）
| MWDB = |
第一个黄色字块：定义远程数据库在本地的别名，随便起
第二个黄色字块：远程数据库的IP地址
第三个黄色子块：远程数据库实例名
- 配置环境变量：
Path 追加 D:/instantclient_11_2 （ oci.dll 和 NETWORK/ADMIN/tnsnames.ora 所在目录）
TNS_ADMIN=D:/instantclient_11_2/NETWORK/ADMIN （tnsnames.ora所在目录）
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK （具体要看数据库字符集，查询语句：select userenv('language') from dual;）
或者配置PLSQL Developer选项：（Tools → Preferences... → Connection）
Oracle Home：D:/instantclient_11_2 （NETWORK/ADMIN/tnsnames.ora所在目录）
OCI library：D:/instantclient_11_2/oci.dll （oci.dll的路径） - 重启PLSQL Developer，数据库服务名名就会出现在PLSQL Developer的Connect as列表里，选择该服务名，输入用户名密码，就可以登录远程oracle数据库了。
Instant Client 补充说明
Oracle Instant client 是oracle提供的简便客户端, 支持多种平台，包括如下内容：
| 11.2.0.1.0 版 Instant Client 程序包 — Basic： 运行 OCI、OCCI 和 JDBC-OCI 应用程序所需的所有文件 href="http://download.oracle.com/otn/nt/instantclient/112010/instantclient-basic-win32-11.2.0.1.0.zip" instantclient-basic-win32-11.2.0.1.0.zip（51,458,190 字节） *Instant Client 程序包 — Basic Lite： Basic 的精简版本，其中仅带有英文错误消息和 Unicode、ASCII 以及西欧字符集支持（仅 10.2） href="http://download.oracle.com/otn/nt/instantclient/112010/instantclient-basiclite-win32-11.2.0.1.0.zip" instantclient-basiclite-win32-11.2.0.1.0.zip（20,732,681 字节） *Instant Client 程序包 — JDBC Supplement： 对 JDBC 下的 XA、国际化和 RowSet 操作的额外支持 href="http://download.oracle.com/otn/nt/instantclient/112010/instantclient-jdbc-win32-11.2.0.1.0.zip" instantclient-jdbc-win32-11.2.0.1.0.zip（1,565,311 字节） *Instant Client 程序包 — SQL*Plus： 为通过 Instant Client 运行 SQL*Plus 而提供的额外的库和可执行文件 href="http://download.oracle.com/otn/nt/instantclient/112010/instantclient-sqlplus-win32-11.2.0.1.0.zip" instantclient-sqlplus-win32-11.2.0.1.0.zip（758,913 字节） *Instant Client 程序包 — SDK： 为通过 Instant Client 开发 Oracle 应用程序而提供的额外的头文件与示例 makefile href="http://download.oracle.com/otn/nt/instantclient/112010/instantclient-sdk-win32-11.2.0.1.0.zip" instantclient-sdk-win32-11.2.0.1.0.zip（1,096,778 字节） *Instant Client 程序包 — ODBC： 用于支持 ODBC 应用程序的额外的库 href="http://download.oracle.com/otn/nt/instantclient/112010/instantclient-odbc-win32-11.2.0.1.0.zip" instantclient-odbc-win32-11.2.0.1.0.zip（744,125 字节） *Instant Client 程序包 — WRC： 负载重放客户端，用于 RAT 的 DB 重放特性的负载重放 href="http://download.oracle.com/otn/nt/instantclient/112010/instantclient-tools-win32-11.2.0.1.0.zip" instantclient-tools-win32-11.2.0.1.0.zip（12,517 字节） *Instant Client 程序包 — Precompiler： “proc”命令的附属文件，以及用于预编译 Pro*C 应用程序和演示的相关文件 |
Basic 和Basic Lite两个中必须选一个, 其他包都是可选的。
下载后的压缩包内都含有一个instantclient_11_2目录，将其解压到同一个文件夹即可。在这个文件夹下建立文件 tnsnames.ora, 设置环境变量LD_LIBRARY_PATH和TNS_ADMIN指向解压后的目录, 就可以使用sqlplus了
Instant Client中不包含tnsping, exp/imp, rman, netca等工具. 如果需要这些工具则需要安装oracle client
PLSQL Developer远程连接方法二：需要安装Oracle数据库客户端或服务端（自带客户端）：
- 宿主机与虚拟机ip要在同一网段，虚拟机防火墙要开放1521端口（命令行 telnet 虚拟机ip 1521 验证成功否）。
- 宿主机到官网下载并安装：Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit)
win32_11gR2_client.zip (684,581,290 bytes) - 修改安装好后的文件 D:\OracleClient\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora（事先增加写入权限），增加需要远程连接的字符串，内容见PLSQL Developer远程连接方法一，或者直接修改原有的服务名相同的字符串，把 HOST = localhost 改为 HOST = 虚拟机ip
- 配置环境变量：
Path 追加 D:\OracleClient\product\11.2.0\dbhome_1\bin （oci.dll 所在目录）
TNS_ADMIN=D:\OracleClient\product\11.2.0\dbhome_1\NETWORK\ADMIN （tnsnames.ora所在目录）
NLS_LANG=AMERICAN_AMERICA.AL32UTF8 （具体要看数据库字符集，查看数据库字符集的sql语句：select userenv('language') from dual;）
或者配置PLSQL Developer选项：（Tools → Preferences... → Connection）
Oracle Home：D:\OracleClient\product\11.2.0\dbhome_1 （NETWORK\ADMIN\tnsnames.ora所在目录）
OCI library：D:\OracleClient\product\11.2.0\dbhome_1\bin\oci.dll （oci.dll的路径） - 重启PLSQL Developer，数据库服务名名就会出现在PLSQL Developer的Connect as列表里，选择该服务名，输入用户名密码，就可以登录远程oracle数据库了。
SQL Plus 设置与常用命令
显示设置
| set linesize 120 | 设置每行显示的最长字符数 |
| set pagesize 20 | 设置一页显示的行数 |
| set feedback on/off | 设置是否显示一页的记录数 |
| set serveroutput on/off | 打开或取消oracle自带的输出方法dbms_output，并输出内容 |
| column列名 format 9999 col列名 for 9999（简写） | 格式化列的内容：将指定列的值格式化为四位数值长度 |
| col列名 for a10 | 格式化列的内容：将指定列的值格式化为10位字母长度 |
常用命令
| show all | 查看系统所有变量值 |
| show user | 显示当前连接用户 |
| show error | 显示错误 |
| desc 表名 | 显示表的结构；如：desc emp |
| /* */ -- | 多行注释 单行注释 |
| / | 执行缓冲区中的语句 |
| ed | 编辑最近的语句。 把缓冲区中最后一条SQL语句调入afiedt.buf文件中进行编辑（如果提示没有afiedt.buf请使用管理员身份打开SLQ Plus）。常用于语句比较长需要修改时。修改完即可调用“/”命名执行修改后的语句。 |
| spool 文件地址 spool 文件地址 append spool off | 假脱机命令；将命令行的内容（从设置后开始的命令行内容）记录到文本。添加append的意思是在原有的文本内容上追加后续的命令行的内容；需要注意的是所有的这些内容都将在spool off之后才记录。如： spool d:\itcast\itcast.txt spool d:\itcast\test.sql append spool off |
| clear screen 或者 host cls | 清屏 |
| exit或quit | 退出SQL Plus |
-
- rowid和伪列rownum
| 伪表dual | DUAL是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。可以用它来做很多事情。 |
| 查看当前用户 | select user from dual; |
| 调用系统函数 | --查询系统的当前时间并格式化 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; |
| 得到序列的下一个值或当前值 | --获得序列seq的下一个值 select seq.nextval from dual; --获得序列seq的当前值 select seq.currval from dual; |
| 计算器 | select 2*8 from dual; |
| 伪列rowid | rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录，同一条记录在不同查询中对应的rowid相同。 【用法】SELECT ROWID,字段名... FROM 表名; 【示例】select rowid, emp.* from emp; |
| 伪列rownum | rownum是根据sql查询出的结果给每行分配一个逻辑编号；每次的查询都会有不同的编号。编号从1开始。 【用法】SELECT ROWNUM,字段名... FROM 表名; 【示例】select rownum, emp.* from emp; 【注意】不能使用where rownum > ...限定查询结果（查不出任何结果） /* 关于分页：由于不能使用>，所以为了达到分页目的得如下执行；如获取第2页数据（每页3条）*/ select * from (select rownum r,emp.* from emp where rownum < 7) where r > 3; /* 关于排序：由于rownum是查询结果的行编号，排序后这个编号便有可能被打乱，如果需要该编号和排序的结果列表序号保持一致可以如下执行*/ select rownum,t.* from (select empno,ename from emp order by empno desc) t; |
视图简介
视图是由一个或者多个表组成的虚拟表，那些用于产生视图的表叫做该视图的基表。视图不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候只是重新执行SQL。一个视图也可以从另一个视图中产生。视图没有存储真正的数据，真正的数据还是存储在基表中。一般出于对基本的安全性和常用的查询语句会建立视图；并一般情况下不对视图进行新增、更新操作。
视图用途
- 作为一个特定查询，使用户可以重复使用，从而简化数据操作。很多时候SQL语句可能会很长，如果这个动作频繁发生的话，我们可以创建视图，然后，我们只需要select * from view1就可以啦。
- 给基表中的数据一定的安全性。因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，通过视图给用户，视图数据随着基表的更新而更新。同时，用户对视图，不可以随意的更改和删除，可以保证数据的安全性。
- 定制用户数据，聚焦特定的数据。我们在做项目的时候，不是每一个新功能都必须建表的。打个比方,如果你现在要分析几个表的内容，这时候没必要你自己建张表然后维护，你只要建一个视图，然后把你要用的字段按一定的逻辑从他们表中拉出来，组成你要用的东西就好了。
- 可以合并分离的数据，创建分区视图。随着社会的发展，公司的业务量的不断的扩大，一个大公司，下属都设有很多的分公司，为了管理方便，我们需要统一表的结构，定期查看各公司业务情况，而分别看各个公司的数据很不方便，没有很好的可比性，如果将这些
数据合并为一个表格里，就方便多啦，这时我们就可以使用union关键字，将各分公司的数据合并为一个视图。
【语法】
| --创建视图：CREATE [OR REPLACE] VIEW <view_name> AS <SELECT 语句>; --删除视图：DROP VIEW <view_name> ; |
视图操作
| -- 授予itcast用户 创建视图 的权限 grant create view to itcast; -- 登录itcast，创建视图 create or replace view v_emp as select empno,ename from emp; --通过视图查询数据 select * from v_emp; --通过视图添加数据,需要保证基表的其它数据项可以为空 insert into v_emp(empno,ename) values(3333,'itcast3'); --通过视图修改数据 update v_emp set ename='传智播客3' where empno=3333; --通过视图删除数据 delete from v_emp where empno=3333; --基于多个基表的视图，不建议使用视图进行增删改操作 create or replace view v_dept_emp as select dept.deptno,dept.dname,ename from emp inner join dept on emp.deptno=dept.deptno; --查询基于多个基表的视图 select * from v_dept_emp; --创建基于视图的视图 create or replace view vv_emp as select ename from v_emp; --查询基于视图的视图 select * from vv_emp; --删除视图 drop view v_emp; drop view v_dept_emp; drop view vv_emp; |
同义词是数据库模式对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，Oracle数据库将它翻译成对应模式对象的名字。与视图类似，同义词并不占用实际存储空间，只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象，如表、视图、同义词、序列、存储过程等，数据库管理员都可以根据实际情况为他们定义同义词。隐藏对象名称和所有者。
私有同义词
私有Oracle同义词由创建它的用户所有；创建的用户需要具有CREATE SYNONYM权限。
| 【语法】 CREATE SYNONYM <synonym_name> for <tablename/viewname...> 【示例】 --管理员 授权用户itcast创建同义词的权限 grant create synonym to itcast; --创建私有同义词 create synonym syn_emp for emp; create synonym syn_v_emp for v_emp;--为视图v_emp创建私有同义词（别名） --使用私有同义词 select empno,ename from syn_emp; update syn_emp set ename='itcast5' where empno='1234'; --删除同义词 drop synonym syn_emp; |
公有同义词
公有Oracle同义词由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公有同义词。公有同义词往往用来标示一些比较普通的数据库对象，这些对象常需要引用。公有同义词一般由管理员用户创建及删除，普通用户需要创建及删除需要create public synonym和drop public synonym权限。
| 【语法】 CREATE PUBLIC SYNONYM <synonym_name> for <tablename/viewname...> --登陆sys管理员用户，授权用户itcast创建、删除(公有的删除权限需要特别给定)公有同义词权限 grant create public synonym,drop public synonym to itcast; --revoke create public synonym,drop public synonym from itcast; --登陆itcast用户创建公有同义词 conn itcast/itcast; create public synonym syn_public_emp for emp; --使用公有同义词 select * from syn_public_emp; -- 登录system管理员 conn system/orcl; 创建itcast2并授权 --create user itcast2 identified by itcast2 default tablespace itcast_ts; --grant connect,resource to itcast2; --为其它用户itcast2授权使用公有同义词（需要给予使用表的权限） grant select,update on itcast.emp to itcast2;（？？？） --revoke select,update on itcast.emp from itcast2; --登陆itcast2用户下使用公有同义词syn_public_emp select * from syn_public_emp; update syn_public_emp set ename='传智播客5' where empno=5555; --删除同义词 --登陆itcast，删除公有同义词 drop public synonym syn_public_emp; |
-
- 索引
索引是建立在数据库表中的某些列的上面，是与表关联的，可提供快速访问数据方式，但会影响增删改的效率；常用类型（按逻辑分类）：单列索引和组合索引、唯一索引和非唯一索引。
什么时候要创建索引
（1）在经常需要搜索、主键、连接的列上
（2）表很大，记录内容分布范围很广
（3）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
（4）在经常使用在WHERE子句中的列上面创建索引
(创建索引的准则：
1.如果表里有几百行记录则可以对其创建索引(表里的记录行数越多索引的效果就越明显)。
2.不要试图对表创建两个或三个以上的索引。
3.为频繁使用的行创建索引。)
什么时候不要创建索引
（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中
（4）对于那些定义为text, image和bit数据类型的列不应该增加索引
| 创建索引 | 删除索引 | |
| 语法 | CREATE [UNIQUE] INDEX <index_name> ON <table_name>(字段 [ASC|DESC]); | DROP INDEX <index_name>; |
| 说明 | UNIQUE --确保所有的索引列中的值都是可以区分的。 [ASC|DESC] --在列上按指定排序创建索引。 | |
| 示例 | create unique index index_emp_empno on emp(empno); --创建单列唯一索引，列值将不允许重复 create index index_emp_ename on emp(ename); --创建单列非唯一索引 create unique index index_emp_ename_job on emp(ename,job); --创建组合列、唯一索引 create index index_emp_job_sal on emp(job,sal); --创建组合列、非唯一索引 | drop index index_emp_empno; drop index index_emp_ename; drop index index_emp_ename_job; drop index index_emp_job_sal; |
-
- 序列
序列是Oracle提供的一个产生唯一数值型值的机制。通常用于表的主健值，序列只能保证唯一，不能保证连续。
创建序列
| 【语法】 CREATE SEQUENCE <sequencen_name> [INCREMENT BY n] [START WITH n] [MAXVALUE n][MINVALUE n] [CYCLE|NOCYCLE] [CACHE n|NOCACHE]; | 【示例】 --创建递增序列 create sequence seq_test increment by 1 start with 1 maxvalue 1000 nocycle; | 【示例】 --创建递减序列 create sequence seq_test2 increment by -1 start with 5 maxvalue 5 minvalue 1 nocycle; |
| 【说明】 INCREMENT BY n --表示序列每次增长的幅度;默认值为1. START WITH n --表示序列开始时的序列号。默认值为1. MAXVALUE n --表示序列可以生成的最大值(升序). MINVALUE n --表示序列可以生成的最小值(降序). CYCLE --表示序列到达最大值后，在重新开始生成序列.默认值为 NOCYCLE。 CACHE n--允许更快的生成序列.预先生成n个序列值到内存（如果没有使用完，那下次序列的值从内存最大值之后开始；所以n不应该设置太大） | ||
使用序列
序列名.NEXTVAL --返回序列下一个值。第一次访问时，返回序列的初始值；后继每次调用时，按步长增加的值返回。
序列名.CURRVAL --返回序列的当前值。注意在刚建立序列后,序列的CURRVAL值为NULL，所以要先使用NEXTVAL访问序列后才能使用CURRVAL
【示例】
| -- 创建序列 create sequence seq_emp_empno start with 1000 increment by 1 maxvalue 9000 minvalue 1000 nocycle; -- 使用序列作为主键插入emp表的empno列 insert into emp(empno,ename) values(seq_emp_empno.nextval,'itcast1'); insert into emp(empno,ename) values(seq_emp_empno.nextval,'itcast2'); -- 查看emp表数据 select empno,ename from emp; -- 查看当前序列的值 select seq_emp_empno.currval from dual; |
修改序列
alter sequence seq_emp_empno
maxvalue 9999
cycle;
删除序列
DROP SEQUENCE <sequence_name>
序列与sys_guid()函数
sys_guid和序列都可以作为主键值。
| --使用SYS_GUID函数，32位，由时间戳和机器标识符生成，保证唯一 select sys_guid() from dual; |
-
- 分区表
分区表用途
分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。
分区表的优点：
（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；
（2）可以对单独的分区进行备份和恢复；
（3）可以将分区映射到不同的物理磁盘上，来分散IO；
（4）提高可管理性、可用性和性能。
数据量大的表，一般大于2GB；数据有明显的界限划分；对于Long和Long Raw类型列不能使用分区。
分区表类型
一般包括范围分区，散列分区，列表分区、复合分区（范围-散列分区，范围-列表分区）、间隔分区和系统分区等。
| 范围分区 范围分区根据数据库表中某一字段的值的范围来划分分区。 【语法】 在Create Table语句后增加 PARTITION BY RANGE(column_name) ( PARTITION part1 VALUES LESS THAN (range1) [TABLESPACE tbs1], PARTITION part2 VALUES LESS THAN (range2) [TABLESPACE tbs2], .... PARTITION partN VALUES LESS THAN (MAXVALUE) [TABLESPACE tbsN] ); 【说明】 MAXVALUE：当分区列值都不在设置的范围内时，新增数据将到这个分区中 【示例】 -- 创建表，并设置分区 create table myemp ( empno number(4) primary key, ename varchar2(10), hiredate date, sal number(7,2), deptno number(2) ) partition by range(sal) ( partition p1 values less than(1000), partition p2 values less than(2000), partition p3 values less than(maxvalue) ); -- 插入数据 insert into myemp(empno,ename,hiredate,sal,deptno) select empno,ename,hiredate,sal,deptno from emp; -- 查看工资1000-2000的数据 select * from myemp partition(p2); -- 删除工资小于1000的数据 delete from myemp partition(p1); -- 查看数据 select * from myemp; | 列表分区 列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。 【语法】 在Create Table语句后增加 PARTITION BY LIST(column_name) ( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), .... PARTITION partN VALUES (DEFAULT) ); 其中：column_name是以其为基础创建列表分区的列。 part1...partN是分区的名称。 values_list是对应分区的分区键值的列表。 DEFAULT关键字允许存储前面的分区不能存储的记录。 【示例】 -- 创建表，并设置分区 create table myemp2 ( empno number(4) primary key, ename varchar2(10), hiredate date, sal number(7,2), deptno number(2) ) partition by list(deptno) ( partition dept10 values(10), partition dept20 values(20), partition dept30 values(30), partition deptx values(default) ); -- 插入数据 insert into myemp2(empno,ename,hiredate,sal,deptno) select empno,ename,hiredate,sal,deptno from emp; -- 查看部门20的数据 select * from myemp2 partition(dept20); -- 删除部门30的数据 delete from myemp2 partition(dept30); -- 查看数据 select * from myemp2; |
-
- PL/SQL
pl/sql:块结构语言，是sql（Structured Query Language）语言的一种扩展，结合了oracle过程语言（procedural language）进行使用。
pl/sql块由三部分构成：声明部分、执行部分、异常部分。
PL/SQL结构
| [DECLARE] --声明变量等； BEGIN --程序主要部分，一般用来执行过程语句或SQL语句； [EXCEPTION] --异常处理； END; |
运算符
| ＝ | 等于 | 比较运算符 |
| <>,!=,~=,^= | 不等于 | |
| < | 小于 | |
| > | 大于 | |
| <= | 小于或等于 | |
| >= | 大于或等于 | |
| + | 加号 | 算术运算符 |
| - | 减号 | |
| * | 乘号 | |
| / | 除号 | |
| := | 赋值号 | 赋值运算符 |
| => | 关系号 | 关系号 |
| .. | 范围运算符 | 范围运算符 |
| || | 字符连接符 | 连接运算符 |
| is null | 是空值 | 逻辑运算符 |
| between and | 介于两者之间 | |
| in | 在一系列值中间 | |
| and | 逻辑与 | |
| or | 逻辑或 | |
| not | 取反 |
变量与常量
数据类型：
| 常用标准类型 | 属性类型 | ||
| CHAR(CHARATER,NCHAR),VARCHAR2, NUMBER(P,S),DATE,BOOLEAN等 | %type类型 可以用来定义数据变量的类型与已定义的数据变量（表中的列）一致。 | %rowtype类型 与某一数据库表的结构一致(修改数据库表结构，可以实时保持一致）；访问方式声明为rowtype的 变量名.字段名。 | |
| 声明 | 【变量声明】 <变量名> 类型[:=初始值]; 【示例】 name varchar2(20):= 'itcast'; 【常量声明】 <变量名> CONSTANT 类型:=初始值; 【示例】 pi constant number(5,3):=3.14; | 【声明】 变量名称 表名.字段%type; 【示例:】 --表示变量name的类型和emp.ename的类型相同 name emp.ename%type; | 【声明】 变量名称 表%rowtype; 【示例:】 --表示变量test的类型为emp表的行类型;也有 .empno; .ename; .sal ;等属性 test emp%rowtype; |
| 运用 | --设置后台输出显示 set serveroutput on; /*定义常量或变量、赋值使用示例*/ DECLARE p_empno constant number(4):=7369; p_ename varchar2(10); p_sal number(7,2); p_comm number(7,2); BEGIN --赋值方式一：使用select into给变量赋值 select ename,sal into p_ename,p_sal from emp where empno =p_empno; --赋值方式二：使用赋值操作符“:=”给变量赋值 p_comm:=500; --输出相关信息，DBMS_OUTPUT.PUT_LINE为具有输出功能的函数 dbms_output.put_line('员工号:'|| p_empno||',姓名:'|| p_ename||',工资:'|| p_sal||',奖金:'|| p_comm); END; 【注意】 dbms_output是oracle提供的输出对象 put_line是其一个方法，用于输出一个字符串 new_line是其一个方法，用于输出新的一行（换行） | --设置后台输出显示 set serveroutput on; /*定义常量或变量、赋值使用示例*/ DECLARE p_empno constantnumber(4):=7369; p_ename emp.ename%type; p_sal emp.sal%type; p_comm emp.comm%type; BEGIN --赋值方式一：使用select into给变量赋值 select ename,sal into p_ename,p_sal from emp where empno = p_empno; --赋值方式二：使用赋值操作符“:=”给变量赋值 p_comm:=500; --输出相关信息，DBMS_OUTPUT.PUT_LINE为具有输出功能的函数 dbms_output.put_line('员工号:'|| p_empno||',姓名:'|| p_ename||',工资:'|| p_sal||',奖金:'|| p_comm); END; | --设置后台输出显示 set serveroutput on; /*定义常量或变量、赋值使用示例*/ DECLARE p_empno constantnumber(4):=7369; emp_info emp%rowtype; p_comm emp.comm%type; BEGIN --赋值方式一：使用select into给变量赋值 select*into emp_info from emp where empno = p_empno; --赋值方式二：使用赋值操作符“:=”给变量赋值 p_comm:=500; --输出相关信息，DBMS_OUTPUT.PUT_LINE为具有输出功能的函数 dbms_output.put_line('员工号:'|| p_empno||',姓名:'|| emp_info.ename ||',工资:'|| emp_info.sal ||',奖金:'|| p_comm); END; |
控制语句
条件语句
| 【语法】 | 【示例】 |
| IF <条件1> THEN 语句; [...] [ELSIF <条件n> THEN 语句;] [ELSE 语句;] END IF; | /* 根据员工的工资判断其工资等级（工资大于等于5000为A级，工资大于等于4000为B级，工资大于等于3000为C级，工资大于等于2000为D级，其它为E级） */ DECLARE p_empno number(4):=7566; p_sal emp.sal%type; BEGIN --用变量代替条件语句中的真值 select sal into p_sal from emp where empno = p_empno; IF p_sal >=5000THEN dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：A级'); ELSIF p_sal >=4000THEN dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：B级'); ELSIF p_sal >=3000THEN dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：C级'); ELSIF p_sal >=2000THEN dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：D级'); ELSE dbms_output.put_line('员工号为：'|| p_empno ||'的员工的工资级别为：E级'); END IF; END; |
循环语句
1、LOOP
| 【语法】 | 【示例】 |
| LOOP 语句; EXIT WHEN <条件> END LOOP; | /* 计算1-10的总和 */ DECLARE p_sum number(4):=0; p_num number(2):=1; BEGIN LOOP p_sum := p_sum + p_num; p_num := p_num +1; EXITWHEN p_num >10; END LOOP; dbms_output.put_line('1-10的总和为：'|| p_sum); END; |
2、WHILE LOOP
| 【语法】 | 【示例】 |
| WHILE <条件> LOOP 语句; END LOOP; | /* 计算1-10的总和 */ DECLARE p_sum number(4):=0; p_num number(2):=1; BEGIN WHILE p_num <=10 LOOP p_sum := p_sum + p_num; p_num := p_num +1; ENDLOOP; dbms_output.put_line('1-10的总和为：'|| p_sum); END; |
3、FOR
| 【语法】 | 【示例】 |
| FOR <循环变量> IN[REVERSE] 下限..上限 LOOP 语句; END LOOP; 【说明】..两点表示范围，1..4表示时将从1到4进行循环，起始（例如 1）写前边，REVERSE表示反转，循环时变成从4到1进行。 | /* 计算1-10的总和 */ DECLARE p_sum number(4):=0; p_num number(2):=1; BEGIN FOR p_num IN 1..10 LOOP p_sum := p_sum + p_num; ENDLOOP; dbms_output.put_line('1-10的总和为：'|| p_sum); END; |
顺序语句
指定顺序执行的语句；主要包括 null语句。null语句是一个可执行语句，相当于一个占位符或不执行操作的空语句。主要用来提高程序语句的完整性和程序的可读性。
| /* 输出1-10的数字但跳过数字4 */ DECLARE flag number(2):=0; BEGIN WHILE flag <10 LOOP flag := flag +1; if flag =4then null;-- 占位，不能去掉 else dbms_output.put_line(flag); end if; END LOOP; END; |
异常处理
异常语法
| EXCEPTION WHEN <异常类型> THEN 语句; WHEN OTHERS THEN 语句; |
常配套使用的函数：
SQLCODE函数：返回错误代码，
SQLERRM函数：返回错误信息
例如输出异常信息： DBMS_OUTPUT.PUT_LINE('其它异常，代码号：'||SQLCODE||'，异常描述:'||SQLERRM);
预定义异常
预定义异常指PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发(由oracle自动引发)。
常见的预定义异常：
CURSOR_ALREADY_OPEN 试图"OPEN"一个已经打开的游标
DUP_VAL_ON_INDEX 试图向有"UNIQUE"中插入重复的值
INVALID_CURSOR 试图对以关闭的游标进行操作
INVALID_NUMBER 在SQL语句中将字符转换成数字失败
LOGIN_DENIED 使用无效用户登陆
NO_DATA_FOUND 没有找到数据时
NOT_LOGIN_ON 没有登陆Oracle就发出命令时
PROGRAM_ERROR PL/SQL存在诸如某个函数没有"RETURN"语句等内部问题
STORAGE_ERROR PL/SQL耗尽内存或内存严重不足
TIMEOUT_ON_RESOURCE Oracle等待资源期间发生超时
TOO_MANY_ROWS "SELECT INTO"返回多行时
VALUE_ERROR 当出现赋值错误
ZERO_DIVIDE 除数为零
【示例】
| /* 预定义异常捕获并处理 */ DECLARE p_result number(2); BEGIN p_result :=1/0; dbms_output.put_line('没有异常！'); EXCEPTION WHEN ZERO_DIVIDE THEN dbms_output.put_line('除数不能为0！代码为：'||sqlcode||'，异常信息为：'||sqlerrm); WHEN OTHERS THEN dbms_output.put_line('其它异常！代码为：'||sqlcode||'，异常信息为：'||sqlerrm); END; |
自定义异常
自定义异常：程序在运行过程中，根据业务等情况，认为非正常情况，可以自定义异常。对于这种异常，主要分三步来处理：
- 定义相关异常；在声明部分定义相关异常，
格式：<自定义异常名称> EXCEPTION;
- 抛出异常；在出现异常部分抛出异常，
格式：RAISE <异常名称>；
- 处理异常；在异常处理部分对异常进行处理，
格式：when <自定义异常名称> then ...，
处理异常也可以使用RAISE_APPLICATION_ERROR(ERROR_NUMBER,ERROR_MESSAGE)存储过程进行处理，
其中参数ERROR_NUMBER取值为-20999~-20000的负整数，参数ERROR_MESSAGE为异常文本消息。
【示例】
| /* 判断emp中相应empno对应用户的奖金是否低于500，如果低于则抛出并处理自定义异常 */ DECLARE p_comm emp.comm%type; --自定义异常，名称为comm_exception comm_exception EXCEPTION; BEGIN Select nvl(comm,0)into p_comm from emp where empno=7499; --nvl(comm,0)如果comm为null就填充0 if p_comm >=500then dbms_output.put_line('奖金大于等于500。'); else RAISE comm_exception; End if; EXCEPTION WHEN comm_exception THEN RAISE_APPLICATION_ERROR(-20001,'奖金低于500，太少了！'); --dbms_output.put_line('奖金低于500！'); WHEN OTHERS THEN dbms_output.put_line('其它异常！代码为：'||sqlcode||'，异常信息为：'||sqlerrm); END; |
显式游标
游标是映射在结果集中一行数据上的位置实体，使用游标，便可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作；从上向下依次迭代结果集。
游标语法
| 【定义语法】 CURSOR <游标名> IS <SELECT 语句> ; 【操作】 OPEN <游标名> --打开游标 FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,; 或者 FETCH <游标名> INTO 行对象; --取出游标当前位置的值 CLOSE <游标名> --关闭游标 【属性】 %NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE"; %FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE"; %ROWCOUNT --返回游标当前行的行数; %ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE"; |
游标使用
- 使用游标显示员工表中所有的员工姓名、工作和工资
| declare cursor cur_emp is select ename,job,sal from emp; p_ename emp.ename%type; p_job emp.job%type; p_sal emp.sal%type; begin --打开游标 open cur_emp; loop --取游标数据，从上往下移动一行 fetch cur_emp into p_ename, p_job, p_sal; --如果下移后没有数据，则退出 Exit when cur_emp%notfound; --如果存在数据，则处理 dbms_output.put_line('姓名为：'|| p_ename ||'，工作为：'|| p_job ||'，工资为：'|| p_sal); end loop; --关闭游标 close cur_emp; end; |
- 使用游标显示指定部门下的所有的员工姓名、工作和工资
| 带参数的游标 【定义】 CURSOR <游标名>(参数列表) IS <SELECT 语句>; 【示例】 --设置后台输出显示 set serveroutput on; declare cursor cur_emp(dno emp.deptno%type)is select ename,job,sal from emp where deptno=dno; r_cur_emp cur_emp%rowtype; begin --打开游标 open cur_emp(20); loop --取游标数据，从上往下移动一行 fetch cur_emp into r_cur_emp; --如果下移后没有数据，则退出 exit when cur_emp%notfound; --如果存在数据，则处理 dbms_output.put_line('姓名为：'|| r_cur_emp.ename ||'，工作为：'|| r_cur_emp.job ||'，工资为：'|| r_cur_emp.sal); end loop; --关闭游标 close cur_emp; end; --参考：使用while循环实现 declare cursor cur_dept_emps(dno emp.deptno%type)is select ename,job,sal from emp where deptno=dno; emp_info cur_dept_emps%rowtype; begin open cur_dept_emps(20); fetch cur_dept_emps into emp_info; while cur_dept_emps%found loop dbms_output.put_line('员工姓名为：'||emp_info.ename||'，工作为：'||emp_info.job||'，工资为：'||emp_info.sal); fetch cur_dept_emps into emp_info; end loop; close cur_dept_emps; end; --参考：使用for循环实现 declare cursor cur_dept_emps(dno emp.deptno%type)is select ename,job,sal from emp where deptno=dno; emp_info cur_dept_emps%rowtype; begin for emp_info in cur_dept_emps(20) loop if cur_dept_emps%found then dbms_output.put_line('员工姓名为：'||emp_info.ename||'，工作为：'||emp_info.job||'，工资为：'||emp_info.sal); end if; end loop; end; |
- 使用游标按员工的工种涨工资,总裁800，经理600，其他人员300
| declare cursor cur_emp is select empno,job from emp; p_empno emp.empno%type; p_job emp.job%type; begin --打开游标 open cur_emp; loop --取游标数据，从上往下移动一行 fetch cur_emp into p_empno, p_job; --如果下移后没有数据，则退出 exit when cur_emp%notfound; --如果存在数据，则处理 if'PRESIDENT'= p_job then update emp set sal = sal +800where empno = p_empno; elsif'MANAGER'= p_job then update emp set sal = sal +600where empno = p_empno; else update emp set sal = sal +300where empno = p_empno; end if; end loop; --关闭游标 close cur_emp; --提交修改 commit; end; |
隐式游标
当执行一个SQL语句时，Oracle会自动创建一个隐式游标，隐式游标主要处理DML语句，该游标的名称是sql。隐试游标不能进行"OPEN" ,"CLOSE","FETCH"这些操作。
属性:
%NOTFOUND --如果DML语句没有影响到任何一行时，则该属性为"TRUE"，否则为"FALSE";
%FOUND --如果DML语句影响到一行或一行以上时，则该属性为"TRUE"，否则为"FALSE";
%ROWCOUNT --返回游标当最后一行的行数;
【示例】
| /* 通过更新语句判断隐式游标的存在 */ begin update emp set comm=comm +300where empno =7369; if sql%notfound then dbms_output.put_line('empno对应的员工不存在'); else dbms_output.put_line('empno对应的员工数为：'||sql%rowcount); end if; end; |
存储过程
存储过程是命名的pl/sql程序块，封装数据业务操作，具有模块化、可重用、可维护、更安全特点；并且可以被程序调用。一般有4类型的存储过程，分别为不带参数、带输入参数、带输出参数、带输入输出参数。
语法
| 【语法】 CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS|AS [局部变量声明] BEGIN 可执行语句 [EXCEPTION 异常处理语句] END [<过程名>]; 【说明】 参数列表：参数不需要声明长度，可选 参数变量的类型：in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出; 【调用方式】 在PL/SQL块中直接使用过程名; 在PL/SQL程序外使用 exec[ute] <过程名>[(参数列表)]; |
示例
| 无参存储过程 | 有输入参数存储过程 |
| -- 授予itcast创建存储过程的权限 grant creat eprocedure to itcast; /* 使用无参存储过程，注意无参存储过程创建时不能使用() */ create or replace procedure pro_helloWorld as begin dbms_output.put_line('Hello World.'); end; -- 方式一：在PL/SQL块中调用存储过程，可加可不加() begin pro_helloWorld; end; -- 方式二：在PL/SQL程序外调用存储过程，可加可不加() exec pro_helloWorld; | /* 使用有输入参存储过程 */ create or replace procedure pro_add_emp( p_empno in emp.empno%type, p_ename in varchar2, p_sal number ) as begin --将输入参数对应的数据插入emp表 insert into emp(empno, ename,sal)values(p_empno, p_ename, p_sal); end; / -- 调用存储过程，向emp表插入新数据 begin pro_add_emp(2001,'itcast2001',3000); pro_add_emp(2002,'itcast2002',2000); pro_add_emp(2003,'itcast2003',4000); end; |
| 有输出参数存储过程 | 有输入输出参数存储过程 |
| /* 使用有输出参存储过程，计算1到10的总和并通过参数返回 */ create or replace procedure pro_1to10_sum( p_sum out number ) as tem_sum number(4):=0; begin for i in 1..10 loop tem_sum := tem_sum + i; end loop; p_sum := tem_sum; end; / -- 调用存储过程 declare p_sum number(4); begin pro_1to10_sum(p_sum); dbms_output.put_line('1至10的和为：'|| p_sum); end; | /* 使用有输入、输出参存储过程；根据empno查询该员工号对应的员工的姓名和工资 */ create or replace procedure pro_query_enameAndSal_by_empno( s_empno emp.empno%type, s_ename out emp.ename%type, s_sal out emp.sal%type ) as begin select ename,sal into s_ename, s_sal from emp where empno= s_empno; end; / -- 调用存储过程 declare p_ename emp.ename%type; p_sal emp.sal%type; begin --pro_query_enameAndSal_by_empno(7369, p_ename, p_sal); pro_query_enameAndSal_by_empno(7369, s_sal => p_sal, s_ename => p_ename); dbms_output.put_line('员工号为7369的员工名称为：'|| p_ename||'，其工资为：'|| p_sal); end; |
程序中调用存储过程
| package cn.itcast; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.jdbc.OracleTypes; publicclass TestProcedure { publicstaticvoid main(String[] args) { Connection conn = null; CallableStatement call = null; try { Class.forName("oracle.jdbc.OracleDriver"); String url = "jdbc:oracle:thin:@localhost:1521:orcl"; conn = DriverManager.getConnection(url, "itcast", "itcast"); call = conn.prepareCall("{call pro_query_enameAndSal_by_empno(?,?,?)}"); //设置输入型参数 call.setInt(1, 7369); //注册输出型参数 call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER); //调用存储过程 call.execute(); //获取返回值 String ename = call.getString(2);//员工名称 double sal = call.getDouble(3);//员工工资 System.out.println("员工号为7369的员工名称为：" + ename + "，工资为：" + sal); } catch (Exception e) { e.printStackTrace(); } finally { try { if(call != null){ call.close(); } if(conn != null){ conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } } |
删除存储过程
| 【语法】 DROP PROCEDURE <过程名>; 【示例】 dropprocedure pro_1to10_sum; |
存储函数
存储函数与过程不同的是，存储函数有return语句；一般情况下如果在需要一个返回值时可使用存储函数。
语法
| CREATE [OR REPLACE] FUNCTION <函数名>[(参数列表)] RETURN 数据类型 IS|AS [局部变量声明] BEGIN 可执行语句 [EXCEPTION 异常处理语句] RETURN 返回值; END [<函数名>]; 变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出; 【使用方式】 直接在select中使用和其它系统函数使用方式一样； 在PL/SQL块中调用使用； |
示例
| 无参存储函数 | 有输入参数存储函数 |
| /* 使用无参存储函数；注意创建时函数名称不能使用() 但是在调用时候可加可不加() */ create or replace function fun_helloWorld return varchar2 as begin return'Hello World'; end; / -- 方式1：调用存储函数 select fun_helloWorld()from dual; -- 方式2：调用存储函数 declare str varchar2(20); begin str :=fun_helloWorld; dbms_output.put_line(str); end; | /* 使用存储函数：根据员工号，查询并返回该员工的年薪 */ create or replace function fun_get_annualSal_by_empno(p_empno emp.empno%type) return number as p_sal emp.sal%type; p_comm emp.comm%type; begin select sal,comm into p_sal, p_comm from emp where empno=p_empno; return 12*p_sal +nvl(p_comm,0); end; / -- 调用存储函数 select fun_get_annualSal_by_empno(7369)from dual; |
| 有输入输出参数存储函数 | 程序中调用存储函数 |
| /* 使用具有输入输出参数的存储函数：根据员工号，查询并返回该员工的年薪,姓名，奖金 */ create or replace function fun_get_annualSal_by_empno2( p_empno emp.empno%type, p_ename out emp.ename%type, p_comm out emp.comm%type ) return number as p_sal emp.sal%type; begin select ename,sal,nvl(comm,0)into p_ename,p_sal, p_comm from emp where empno=p_empno; return 12*p_sal + p_comm; end; / -- 调用存储函数 declare p_annualSal number(10,2); p_ename emp.ename%type; p_comm emp.comm%type; begin p_annualSal := fun_get_annualSal_by_empno2(7499,p_ename,p_comm); dbms_output.put_line('员工姓名为：'||p_ename||',奖金为：'||p_comm||'，年薪为：'||p_annualSal); end; | package cn.itcast; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.jdbc.OracleTypes; publicclass TestFunction { publicstaticvoid main(String[] args) { Connection conn = null; CallableStatement call = null; try { Class.forName("oracle.jdbc.OracleDriver"); String url = "jdbc:oracle:thin:@localhost:1521:orcl"; conn = DriverManager.getConnection(url, "itcast", "itcast"); call = conn.prepareCall("{? = call fun_get_annualSal_by_empno2(?,?,?)}"); //注册存储函数返回值 call.registerOutParameter(1, OracleTypes.DOUBLE); //设置输入参数，员工号 call.setInt(2, 7499); //注册输出参数，员工姓名 call.registerOutParameter(3, OracleTypes.VARCHAR); //注册输出参数，奖金 call.registerOutParameter(4, OracleTypes.DOUBLE); call.execute(); System.out.println("员工姓名为：" + call.getString(3) + "，奖金为：" + call.getDouble(4) + "，年薪为：" + call.getDouble(1)); } catch (Exception e) { e.printStackTrace(); } finally { try { if(call != null){ call.close(); } if(conn != null){ conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } } |
| 删除存储函数 | |
| 【语法】 DROP FUNCTION <函数名>; 【示例】 dropfunction fun_helloWorld; dropfunction fun_get_annualSal_by_empno; dropfunction fun_get_annualSal_by_empno2; |
存储过程与存储函数的区别
1、返回值的区别,函数一定要有1个返回值或有多个通过输出参数的返回值,而存储过程是通过输出参数返回的,可以有多个或者没有；
2、调用的区别,函数可以在sql语句中直接调用,而存储过程必须单独调用；
3、函数一般情况下是用来计算并返回一个计算结果，而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）
语法
| 【语法】 CREATE [OR REPLACE] TRIGGER <触发器名> BEFORE|AFTER INSERT|DELETE|UPDATE [OF <列名>] ON <表名> [FOR EACH ROW] <pl/sql块> 【说明】 关键字"BEFORE"在操作完成前触发;"AFTER"则是在操作完成后触发; 关键字"FOR EACH ROW"指定触发器每行触发一次，若不指定则为表级触发器. 关键字"OF <列名>" 不写表示对整个表的所有列. pl/sql块中不能使用commit; 【特殊变量】 :new --为一个引用最新的行值; :old --为一个引用以前的行值; 这些变量只有在使用了关键字 "FOR EACH ROW"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old; |
行级触发器
【示例1】涨工资
| /* 触发器使用：给员工涨工资（涨后工资应该大于涨前）后，在后台输出更新前和更新后的工资 */ create or replace trigger tri_emp_upd_sal after update of sal on emp for each row begin if:old.sal <:new.sal then dbms_output.put_line('更新前工资为：'||:old.sal||'，更新后工资为：'||:new.sal); else raise_application_error(-20002,'工资不能越涨越低！'); end if; end; / -- 更新工资值，并触发行级触发器 update emp set sal =8888where empno =1002; |
【示例2】触发器+序列实现主键自增长
| /* 触发器使用：给emp表的empno添加触发器，在插入记录时自动填入值 */ -- 1、创建序列 create sequence seq_emp_empno; -- 2、创建触发器 create or replace trigger tri_emp_ins_empno before insert on emp for each row begin -- 给将要插入表的记录:new 中的empno设置sequence中的值 select seq_emp_empno.nextval into:new.empno from dual; end; / -- 新增员工数据，测试触发器+序列的组合使用 insert into emp(ename,sal)values('itcast002',2000); commit; |
表级触发器
| /* 触发器使用：删除表的同时备份表数据到另一张备份表 */ -- 1、从emp表结果中创建一张表并复制数据 create table emp2 as select * from emp; -- 2、创建备份表emp_bak create table emp_bak as select * from emp2 where 1=2; -- 3、创建表触发器，当对表操作时触发 create or replace trigger tri_emp2_del before delete on emp2 begin -- 将emp2表中的数据备份到emp_bak insert into emp_bak select * from emp2; end; / -- 4、测试删除emp2表的数据 delete from emp2; select * from emp2; select * from emp_bak; |
开启禁用触发器
| 【禁用某个触发器】 ALTER TRIGGER <触发器名> DISABLE 【示例】 alter trigger tri_emp_upd_sal disable; update emp set sal =8888 where empno =1002; 【重新启用触发器】 ALTER TRIGGER <触发器名> ENABLE 【示例】 alter trigger tri_emp_upd_sal enable; update emp set sal =8888 where empno =1002; 【禁用表的所有触发器】 ALTER TABLE <表名> DISABLE ALL TRIGGERS; 【示例】 alter table emp disable all triggers; 【启用表的所有触发器】 ALTER TABLE <表名> ENABLE ALL TRIGGERS; 【示例】 alter table emp enable all triggers; 【删除触发器】 DROP TRIGGER <触发器名>; 【示例】 dropt rigger tri_emp_upd_sal; |
Oracle中的数据字典是一组基表，保存了与用户创建的数据库对象相关的最基本信息。基表属于sys账户，在sys创建数据库时同时被创建。基表永远不会被直接访问，而是由一系列数据字典视图间接访问。
Oracle具有三组数据字典视图：
"USER_"开头的视图允许用户获取其模式中对象的相关信息（也就是用户创建并拥有的对象）。
"ALL_"开头的视图允许用户获取其拥有的或可被访问的对象的相关信息（包括该"USER_"记录和授权至PUBLIC或该用户的对象的信息）。
"DBA_"开头的视图包含所有数据库对象的完整信息（所有用户创建的所有对象），普通用户无法访问这些视图。
| 视图名 | 描述 |
| ALL_CATALOG | All tables, views, synonyms, sequences accessible to the user |
| ALL_COL_COMMENTS | Comments on columns of accessible tables and views |
| ALL_COL_GRANTS_MADE | Grants on columns for which the user is owner or grantor |
| ALL_COL_GRANTS_RECD | Grants on columns for which the user or PUBLIC is the grantee |
| ALL_COL_PRIVS | Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| ALL_COL_PRIVS_MADE | Grants on columns for which the user is owner or grantor |
| ALL_COL_PRIVS_RECD | Grants on columns for which the user, PUBLIC or enabled role is the grantee |
| ALL_CONSTRAINTS | Constraint definitions on accessible tables |
| ALL_CONS_COLUMNS | Information about accessible columns in constraint definitions |
| ALL_DB_LINKS | Database links accessible to the user |
| ALL_DEF_AUDIT_OPTS | Auditing options for newly created objects |
| ALL_DEPENDENCIES | Dependencies to and from objects accessible to the user |
| ALL_ERRORS | Current errors on stored objects that user is allowed to create |
| ALL_INDEXES | Descriptions of indexes on tables accessible to the user |
| ALL_IND_COLUMNS | COLUMNs comprising INDEXes on accessible TABLES |
| ALL_OBJECTS | Objects accessible to the user |
| ALL_REFRESH | All the refresh groups that the user can touch |
| ALL_REFRESH_CHILDREN | All the objects in refresh groups, where the user can touch the group |
| ALL_SEQUENCES | Description of SEQUENCEs accessible to the user |
| ALL_SNAPSHOTS | Snapshots the user can look at |
| ALL_SOURCE | Current source on stored objects that user is allowed to create |
| ALL_SYNONYMS | All synonyms accessible to the user |
| ALL_TABLES | Description of tables accessible to the user |
| ALL_TAB_COLUMNS | Columns of all tables, views and clusters |
| ALL_TAB_COMMENTS | Comments on tables and views accessible to the user |
| ALL_TAB_GRANTS_MADE | User's grants and grants on user's objects |
| ALL_TAB_GRANTS_RECD | Grants on objects for which the user or PUBLIC is the grantee |
| ALL_TAB_PRIVS | Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| ALL_TAB_PRIVS_MADE | User's grants and grants on user's objects |
| ALL_TAB_PRIVS_RECD | Grants on objects for which the user, PUBLIC or enabled role is the grantee |
| ALL_TRIGGERS | Triggers accessible to the current user |
| ALL_TRIGGER_COLS | Column usage in user's triggers or in triggers on user's tables |
| ALL_USERS | Information about all users of the database |
| ALL_VIEWS | Text of views accessible to the user |
| USER_AUDIT_CONNECT | Audit trail entries for user logons/logoffs |
| USER_AUDIT_OBJECT | Audit trail records for statements concerning objects, specifically: table, cluster, view, index, sequence, [public] database link, [public] synonym, procedure, trigger, rollback segment, tablespace, role, user |
| USER_AUDIT_SESSION | |
| USER_AUDIT_STATEMENT | Audit trail records concerning grant, revoke, audit, noaudit and alter system |
| USER_AUDIT_TRAIL | Audit trail entries relevant to the user |
| USER_CATALOG | Tables, Views, Synonyms and Sequences owned by the user |
| USER_CLUSTERS | Descriptions of user's own clusters |
| USER_CLU_COLUMNS | Mapping of table columns to cluster columns |
| USER_COL_COMMENTS | Comments on columns of user's tables and views |
| USER_COL_GRANTS | Grants on columns for which the user is the owner, grantor or grantee |
| USER_COL_GRANTS_MADE | All grants on columns of objects owned by the user |
| USER_COL_GRANTS_RECD | Grants on columns for which the user is the grantee |
| USER_COL_PRIVS | Grants on columns for which the user is the owner, grantor or grantee |
| USER_COL_PRIVS_MADE | All grants on columns of objects owned by the user |
| USER_COL_PRIVS_RECD | Grants on columns for which the user is the grantee |
| USER_CONSTRAINTS | Constraint definitions on user's own tables |
| USER_CONS_COLUMNS | Information about accessible columns in constraint definitions |
| USER_CROSS_REFS | Cross references for user's views and synonyms |
| USER_DB_LINKS | Database links owned by the user |
| USER_DEPENDENCIES | Dependencies to and from a users objects |
| USER_ERRORS | Current errors on stored objects owned by the user |
| USER_EXTENTS | Extents comprising segments owned by the user |
| USER_FREE_SPACE | Free extents in tablespaces accessible to the user |
| USER_INDEXES | Description of the user's own indexes |
| USER_IND_COLUMNS | COLUMNs comprising user's INDEXes or on user's TABLES |
| USER_JOBS | All jobs owned by this user |
| USER_OBJECTS | Objects owned by the user |
| USER_OBJECT_SIZE | Sizes, in bytes, of various pl/sql objects |
| USER_OBJ_AUDIT_OPTS | Auditing options for user's own tables and views |
| USER_REFRESH | All the refresh groups |
| USER_REFRESH_CHILDREN | All the objects in refresh groups, where the user owns the refresh group |
| USER_RESOURCE_LIMITS | Display resource limit of the user |
| USER_ROLE_PRIVS | Roles granted to current user |
| USER_SEGMENTS | Storage allocated for all database segments |
| USER_SEQUENCES | Description of the user's own SEQUENCEs |
| USER_SNAPSHOTS | Snapshots the user can look at |
| USER_SNAPSHOT_LOGS | All snapshot logs owned by the user |
| USER_SOURCE | Source of stored objects accessible to the user |
| USER_SYNONYMS | The user's private synonyms |
| USER_SYS_PRIVS | System privileges granted to current user |
| USER_TABLES | Description of the user's own tables |
| USER_TABLESPACES | Description of accessible tablespaces |
| USER_TAB_AUDIT_OPTS | Auditing options for user's own tables and views |
| USER_TAB_COLUMNS | Columns of user's tables, views and clusters |
| USER_TAB_COMMENTS | Comments on the tables and views owned by the user |
| USER_TAB_PRIVS | Grants on objects for which the user is the owner, grantor or grantee |
| USER_TAB_PRIVS_MADE | All grants on objects owned by the user |
| USER_TAB_PRIVS_RECD | Grants on objects for which the user is the grantee |
| USER_TRIGGERS | Triggers owned by the user |
| USER_TRIGGER_COLS | Column usage in user's triggers |
| USER_TS_QUOTAS | Tablespace quotas for the user |
| USER_USERS | Information about the current user |
| USER_VIEWS | Text of views owned by the user |
| AUDIT_ACTIONS | Description table for audit trail action type codes. Maps action type numbers to action type names |
| COLUMN_PRIVILEGES | Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| DICTIONARY | Description of data dictionary tables and views |
| DICT_COLUMNS | Description of columns in data dictionary tables and views |
| DUAL | |
| GLOBAL_NAME | global database name |
| INDEX_HISTOGRAM | statistics on keys with repeat count |
| INDEX_STATS | statistics on the b-tree |
| RESOURCE_COST | Cost for each resource |
| ROLE_ROLE_PRIVS | Roles which are granted to roles |
| ROLE_SYS_PRIVS | System privileges granted to roles |
| ROLE_TAB_PRIVS | Table privileges granted to roles |
| SESSION_PRIVS | Privileges which the user currently has set |
| SESSION_ROLES | Roles which the user currently has enabled. |
| TABLE_PRIVILEGES | Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| ACCESSIBLE_COLUMNS | Synonym for ALL_TAB_COLUMNS |
| ALL_COL_GRANTS | Synonym for COLUMN_PRIVILEGES |
| ALL_JOBS | Synonym for USER_JOBS |
| ALL_TAB_GRANTS | Synonym for TABLE_PRIVILEGES |
| CAT | Synonym for USER_CATALOG |
| CLU | Synonym for USER_CLUSTERS |
| COLS | Synonym for USER_TAB_COLUMNS |
| DBA_AUDIT_CONNECT | Synonym for USER_AUDIT_CONNECT |
| DBA_AUDIT_RESOURCE | Synonym for USER_AUDIT_RESOURCE |
| DBA_REFRESH_CHILDREN | Synonym for USER_REFRESH_CHILDREN |
| DICT | Synonym for DICTIONARY |
| IND | Synonym for USER_INDEXES |
| OBJ | Synonym for USER_OBJECTS |
| SEQ | Synonym for USER_SEQUENCES |
| SM$VERSION | Synonym for SM_$VERSION |
| SYN | Synonym for USER_SYNONYMS |
| TABS | Synonym for USER_TABLES |
| V$ACCESS | Synonym for V_$ACCESS |
| V$ARCHIVE | Synonym for V_$ARCHIVE |
| V$BACKUP | Synonym for V_$BACKUP |
| V$BGPROCESS | Synonym for V_$BGPROCESS |
| V$CIRCUIT | Synonym for V_$CIRCUIT |
| V$COMPATIBILITY | Synonym for V_$COMPATIBILITY |
| V$COMPATSEG | Synonym for V_$COMPATSEG |
| V$CONTROLFILE | Synonym for V_$CONTROLFILE |
| V$DATABASE | Synonym for V_$DATABASE |
| V$DATAFILE | Synonym for V_$DATAFILE |
| V$DBFILE | Synonym for V_$DBFILE |
| V$DBLINK | Synonym for V_$DBLINK |
| V$DB_OBJECT_CACHE | Synonym for V_$DB_OBJECT_CACHE |
| V$DISPATCHER | Synonym for V_$DISPATCHER |
| V$ENABLEDPRIVS | Synonym for V_$ENABLEDPRIVS |
| V$FILESTAT | Synonym for V_$FILESTAT |
| V$FIXED_TABLE | Synonym for V_$FIXED_TABLE |
| V$LATCH | Synonym for V_$LATCH |
| V$LATCHHOLDER | Synonym for V_$LATCHHOLDER |
| V$LATCHNAME | Synonym for V_$LATCHNAME |
| V$LIBRARYCACHE | Synonym for V_$LIBRARYCACHE |
| V$LICENSE | Synonym for V_$LICENSE |
| V$LOADCSTAT | Synonym for V_$LOADCSTAT |
| V$LOADTSTAT | Synonym for V_$LOADTSTAT |
| V$LOCK | Synonym for V_$LOCK |
| V$LOG | Synonym for V_$LOG |
| V$LOGFILE | Synonym for V_$LOGFILE |
| V$LOGHIST | Synonym for V_$LOGHIST |
| V$LOG_HISTORY | Synonym for V_$LOG_HISTORY |
| V$MLS_PARAMETERS | Synonym for V_$MLS_PARAMETERS |
| V$MTS | Synonym for V_$MTS |
| V$NLS_PARAMETERS | Synonym for V_$NLS_PARAMETERS |
| V$NLS_VALID_VALUES | Synonym for V_$NLS_VALID_VALUES |
| V$OPEN_CURSOR | Synonym for V_$OPEN_CURSOR |
| V$OPTION | Synonym for V_$OPTION |
| V$PARAMETER | Synonym for V_$PARAMETER |
| V$PQ_SESSTAT | Synonym for V_$PQ_SESSTAT |
| V$PQ_SLAVE | Synonym for V_$PQ_SLAVE |
| V$PQ_SYSSTAT | Synonym for V_$PQ_SYSSTAT |
| V$PROCESS | Synonym for V_$PROCESS |
| V$QUEUE | Synonym for V_$QUEUE |
| V$RECOVERY_LOG | Synonym for V_$RECOVERY_LOG |
| V$RECOVER_FILE | Synonym for V_$RECOVER_FILE |
| V$REQDIST | Synonym for V_$REQDIST |
| V$RESOURCE | Synonym for V_$RESOURCE |
| V$ROLLNAME | Synonym for V_$ROLLNAME |
| V$ROLLSTAT | Synonym for V_$ROLLSTAT |
| V$ROWCACHE | Synonym for V_$ROWCACHE |
| V$SESSION | Synonym for V_$SESSION |
| V$SESSION_CURSOR_CACHE | Synonym for V_$SESSION_CURSOR_CACHE |
| V$SESSION_EVENT | Synonym for V_$SESSION_EVENT |
| V$SESSION_WAIT | Synonym for V_$SESSION_WAIT |
| V$SESSTAT | Synonym for V_$SESSTAT |
| V$SESS_IO | Synonym for V_$SESS_IO |
| V$SGA | Synonym for V_$SGA |
| V$SGASTAT | Synonym for V_$SGASTAT |
| V$SHARED_SERVER | Synonym for V_$SHARED_SERVER |
| V$SQLAREA | Synonym for V_$SQLAREA |
| V$STATNAME | Synonym for V_$STATNAME |
| V$SYSSTAT | Synonym for V_$SYSSTAT |
| V$SYSTEM_CURSOR_CACHE | Synonym for V_$SYSTEM_CURSOR_CACHE |
| V$SYSTEM_EVENT | Synonym for V_$SYSTEM_EVENT |
| V$THREAD | Synonym for V_$THREAD |
| V$TIMER | Synonym for V_$TIMER |
| V$TRANSACTION | Synonym for V_$TRANSACTION |
| V$TYPE_SIZE | Synonym for V_$TYPE_SIZE |
| V$VERSION | Synonym for V_$VERSION |
| V$WAITSTAT | Synonym for V_$WAITSTAT |
| V$_LOCK | Synonym for V_$_LOCK |
Oracle提供了三种标准的角色（role）：CONNECT、RESOURCE和DBA。
1. CONNECT Role(连接角色)
临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT role。CONNECT是使用Oracle的简单权限，这种权限只有在对其他用户的表有访问权时，包括select、insert、update和delete等，才会变得有意义。
2. RESOURCE Role(资源角色)
更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程、触发器、索引和簇。
3. DBA Role(数据库管理员角色)
DBA role拥有所有的系统权限--包括无限制的空间限额和给其他用户授予各种权限的能力。
除此以上角色外；还可以自行创建角色。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role，用户必须具有CREATE ROLE系统权限。
创建角色
创建角色后，可以对角色授予权限；授权的语法和前面授权给用户的语法相同。
| 【语法】 CREATE ROLE <role_name>; 【示例】 -- system 用户登录，授予itcast 创建角色的权限 grant create role to itcast; -- 创建角色 create role role_itcast; -- 授予emp的select 操作权限给role_itcast角色 grant select on emp to role_itcast; -- 给scott用户授予role_itcast的角色 grant role_itcast to scott; |
删除角色
| 【语法】 DROP ROLE <role_name>; 【示例】 droprole role_itcast; |
闪回简介
在Oracle的操作工程中，会不可避免地出现操作失误或者用户失误，例如不小心删除了一个表等，这些失误和错误可能会造成重要数据的丢失，最终导致Oracle数据库停止。
在传统操作上，当发生数据丢失、数据错误问题时，解决的主要办法是数据的导入导出、备份恢复技术，这些方法都需要在发生错误前，有一个正确的备份才能进行恢复。为了减少这方面的损失，Oracle提供了闪回技术。有了闪回技术，就可以实现数据的快速恢复，而且不需要数据备份。
闪回特点：
传统的恢复技术缓慢:它是整个数据库或者一个文件恢复，不只恢复损坏的数据在数据库日志中每个修改都必须被检查；
闪回速度快:通过行和事务把改变编入索引，仅仅改变了的数据会被恢复；
闪回命令容易，没有复杂步骤。
闪回类型
主要有三种闪回：闪回表(flashback table)、闪回删除(flashback drop)、闪回数据库（flashback database）；一般情况下对数据库的闪回需要配置闪回数据库，然后自动产生闪回日志；再根据闪回日志恢复数据库。
闪回查询
根据闪回日志可以快速查询在某个时间点的数据。
| --查看10秒之前的emp表 select * from emp as of timestamp sysdate - interval'10'second; select * from emp as of scn timestamp_to_scn(sysdate - interval'10'second); 【说明】 as of timestamp 是固定写法,查询某个时间点对应的数据 as of scn查询某scn对应的数据 sysdate - interval ‘10’second 是时间值的计算 --通过查询某个时间的数据来更新现有数据 --将7499员工的姓名更新为5分钟之前的姓名 update emp e set ename = (select ename from emp as of timestamp systimestamp - interval'5'minute where empno=e.empno) where empno=7499; |
闪回表
闪回表(flashback table)实际上是将表中的数据快速恢复到过去的一个焦点或者系统改变号SCN上；对进行表闪回的表必须row movement为enable。
SCN: System Change Number.
实现表的闪回，需要使用到与撤销表空间相关的undo信息，通过show parameter undo命令可以了解这些信息。
conn sys/orcl as sysdba
show parameters undo; // undo表空间
alter system set undo_retention=1200 scope=both;
undo_retention：数据保留时间长度（默认是900秒）
scope参数的值：
momory-当前session中有效
spfile: 修改配置文件，但当前会话中无效
both:当前会话有效，同时修改配置文件
undo表空间：保存了所有的操作记录(2G的空间) 因为有了该表空间才可以进行闪回
| 【语法】 flashback table [schema.]table_name[,...n] to {[scn] | [timestamp] [[enable | disable] triggers]}; 【说明】 scn：表示通过系统改变号进行闪回；scn系统改变号一般和系统时间相对应；查看当前系统时间和所对应系统scn： select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), timestamp_to_scn(sysdate)from dual; timestamp：表示通过时间戳的形式来进行闪回； enable|disable triggers：表示触发器恢复之后的状态，默认为disable。 rowid这个伪列是Oracle默认提供给每一个表的，主要用于记录每一行数据存储的磁盘物理地址。当删除一行记录后，后面的记录依次跟进上来，当 需要恢复某一个中间的行时，就需要行具备行移动功能（alter table <表名> enable row movement;） 【示例】 -- 授权用户闪回表的权限 grant flashback any table to itcast; -- 查看当前时间点或scn号 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), timestamp_to_scn(sysdate)from dual; -- 删除数据 delete from emp where empno =7449; commit; --允许行移动 alter table emp enable rowmovement; -- 方式一；使用时间点闪回表 flashback table emp to timestamp to_timestamp('时间格式字符串','yyyy-mm-dd HH24:mi:ss'); -- 方式二；使用SCN闪回表 flashback table emp to SCN号; |
闪回删除
闪回删除(flashback drop)。当整个表被删除并在回收站查询到的话；可以对表进行闪回。show recyclebin：可以显示当前用户recyclebin中的表。
系统参数recyclebin控制表删除后是否到回收站，show parameter recyclebin可以查看该参数的状态。
对于系统参数的修改有两种，全局的修改和会话的修改：
（1）alter system set param_name=param_value;
（2）alter session set param_name=param_value;
show recyclebin; --查看回收站
purge recyclebin; --清空回收站
| 【语法】 flashback table table_name to before drop [rename to new_name]; 【说明】 rename to new_name：如果在删除原表之后又重新创建了一个一样名称的表，那么恢复回收站的表时可以对表名进行重命名 【示例】 -- 删除表 droptable emp; -- 恢复表 flashback table emp to before drop; |
ORA-12514错误
用Navicat Premium 连接 Oracle，提示：
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
中文版的提示：
ORA-12514： TNS： 监听服务当前无法识别描述中的请求服务
官方给出的关于ORA-12514的错误原因以及解决办法。原因简单来说可能有以下几种：
一、连接描述符给出的服务尚未在监听器中进行动态注册。
二、可能是先启动了监听器，后启动了数据库服务。
三、也有可能是服务未在监听器中进行静态注册。
官网
云原生分布式数据库-实时 HTAP-开源-PingCAP | 平凯星辰
简介
TiDB是一种开源分布式关系型数据库。
TiDB 是 PingCAP 公司设计的开源分布式 HTAP (Hybrid Transactional and Analytical Processing) 数据库，结合了传统的 RDBMS 和 NoSQL 的最佳特性。TiDB 兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。TiDB 的目标是为 OLTP (Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场景提供一站式的解决方案。
TiDB数据库具备「分布式强一致性事务、在线弹性水平扩展、故障自恢复的高可用、跨数据中心多活」等核心特性，是大数据时代理想的数据库集群和云数据库解决方案。目前，已被近 1000 家不同行业的领先企业应用在实际生产环境，涉及互联网、游戏、银行、保险、证券、航空、制造业、电信、新零售、政府等多个行业，包括美国、欧洲、日本、东南亚等海外用户。
TiDB 的设计目标是 100% 的 OLTP 场景和 80% 的 OLAP 场景，更复杂的 OLAP 分析可以通过 TiSpark 项目来完成。
TiDB 对业务没有任何侵入性，能优雅的替换传统的数据库中间件、数据库分库分表等 Sharding 方案。同时它也让开发运维人员不用关注数据库 Scale 的细节问题，专注于业务开发，极大的提升研发的生产力。
TiDB是一种NewSQL。
NewSQL数据库不仅具有NoSQL对海量数据的存储管理能力，还保持了传统数据库支持ACID和SQL等特性。
NewSQL优点：
- 无限水平扩展能力
- 分布式强一致性，确保数据 100% 安全
- 完整的分布式事务处理能力与 ACID 特性
OLTP和OLAP
OLTP：On-Line Transaction Processing联机事务处理过程
强调支持短时间高并发的事务操作（增删改查）能力，每个操作涉及的数据量都很小
强调事务的强一致性。
例如购物节期间大量下单请求。
OLAP：On-Line Analytic Processing联机分析处理过程
偏向于复杂的只读查询，读取海量数据进行分析计算，查询时间往往很长。
例如运营对订单数据进行分析挖掘，会有各种奇形怪状的复杂查询。
特性
高度兼容 MySQL
大多数情况下，无需修改代码即可从 MySQL 轻松迁移至 TiDB，分库分表后的 MySQL 集群亦可通过 TiDB 工具进行实时迁移。 对于用户使用的时候，可以透明地从MySQL切换到TiDB 中，只是“新MySQL”的后端是存储“无限的”，不再受制于Local的磁盘容量。在运维使用时也可以将TiDB当做一个从库挂到MySQL主从架构中。
分布式事务
TiDB 100% 支持标准的 ACID 事务。
支持HTAP
HTAP：Hybrid Transactional/Analytical Processing TiDB 作为典型的 OLTP 行存数据库，同时兼具强大的 OLAP 性能，配合 TiSpark，可提供一站式 HTAP 解决方案，一份存储同时处理 OLTP & OLAP，无需传统繁琐的 ETL 过程。
支持云原生SQL数据库
TiDB 是为云而设计的数据库，支持公有云、私有云和混合云，配合 TiDB Operator 项目 可实现自动化运维，使部署、配置和维护变得十分简单。
水平弹性扩展
通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景。
无限水平扩展是 TiDB 的一大特点，这里说的水平扩展包括两方面：计算能力(TiDB)和存储能力(TiKV)。
TiDB Server 负责处理 SQL 请求，随着业务的增长，可以简单的添加 TiDB Server 节点，提高整体的处理能力，提供更高的吞吐。
TiKV 负责存储数据，随着数据量的增长，可以部署更多的 TiKV Server 节点解决数据 Scale 的问题。
PD 会在 TiKV 节点之间以 Region 为单位做调度，将部分数据迁移到新加的节点上。
所以在业务的早期，可以只部署少量的服务实例（推荐至少部署 3 个 TiKV， 3 个 PD，2 个 TiDB），随着业务量的增长，按照需求添加 TiKV 或者 TiDB 实例。
真正金融级高可用
相比于传统主从 (M-S) 复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可以实现故障的自动恢复 (auto-failover)，无需人工介入。
高可用是 TiDB 的另一大特点，TiDB/TiKV/PD 这三个组件都能容忍部分实例失效，不影响整个集群的可用性。下面分别说明这三个组件的可用性、单个实例失效后的后果以及如何恢复。
TiDB TiDB 是无状态的，推荐至少部署两个实例，前端通过负载均衡组件对外提供服务。当单个实例失效时，会影响正在这个实例上进行的 Session，从应用的角度看，会出现单次请求失败的情况，重新连接后即可继续获得服务。单个实例失效后，可以重启这个实例或者部署一个新的实例。
PD PD 是一个集群，通过 Raft 协议保持数据的一致性，单个实例失效时，如果这个实例不是 Raft 的 leader，那么服务完全不受影响；如果这个实例是 Raft 的 leader，会重新选出新的 Raft leader，自动恢复服务。PD 在选举的过程中无法对外提供服务，这个时间大约是3秒钟。推荐至少部署三个 PD 实例，单个实例失效后，重启这个实例或者添加新的实例。
TiKV
TiKV 是一个集群，通过 Raft 协议保持数据的一致性（副本数量可配置，默认保存三副本），并通过 PD 做负载均衡调度。单个节点失效时，会影响这个节点上存储的所有 Region。对于 Region 中的 Leader 节点，会中断服务，等待重新选举；对于 Region 中的 Follower 节点，不会影响服务。当某个 TiKV 节点失效，并且在一段时间内（默认 30 分钟）无法恢复，PD 会将其上的数据迁移到其他的 TiKV 节点上。
TiDB 存储和计算能力
存储能力-TiKV-LSM
TiKV Server通常是3+的，TiDB每份数据缺省为3副本，这一点与HDFS有些相似，但是通过Raft协议进行数据复制，TiKV Server上的数据的是以Region为单位进行，由PD Server集群进行统一调度，类似HBASE的Region调度。
TiKV集群存储的数据格式是KV的，在TiDB中，并不是将数据直接存储在 HDD/SSD中，而是通过RocksDB实现了TB级别的本地化存储方案，着重提的一点是：RocksDB和HBASE一样，都是通过 LSM树作为存储方案，避免了B+树叶子节点膨胀带来的大量随机读写。从何提升了整体的吞吐量。
计算能力-TiDB Server
TiDB Server本身是无状态的，意味着当计算能力成为瓶颈的时候，可以直接扩容机器，对用户是透明的。理论上TiDB Server的数量并没有上限限制。
TiDB架构
TiDB 集群主要包括三个核心组件：TiDB Server，PD Server 和 TiKV Server。此外，还有用于解决用户复杂 OLAP 需求的 TiSpark 组件和简化云上部署管理的 TiDB Operator 组件。
TiDB Server
TiDB Server 负责接收 SQL 请求，处理 SQL 相关的逻辑，并通过 PD 找到存储计算所需数据的 TiKV 地址，与 TiKV 交互获取数据，最终返回结果。TiDB Server 是无状态的，其本身并不存储数据，只负责计算，可以无限水平扩展，可以通过负载均衡组件（如LVS、HAProxy 或 F5）对外提供统一的接入地址。
PD Server
Placement Driver (简称 PD) 是整个集群的管理模块，其主要工作有三个：
一是存储集群的元信息（某个 Key 存储在哪个 TiKV 节点）；
二是对 TiKV 集群进行调度和负载均衡（如数据的迁移、Raft group leader 的迁移等）；
三是分配全局唯一且递增的事务 ID。
PD 通过 Raft 协议保证数据的安全性。Raft 的 leader server 负责处理所有操作，其余的 PD server 仅用于保证高可用。建议部署奇数个 PD 节点。
TiKV Server
TiKV Server 负责存储数据，从外部看 TiKV 是一个分布式的提供事务的 Key-Value 存储引擎。存储数据的基本单位是 Region，每个 Region 负责存储一个 Key Range（从 StartKey 到 EndKey 的左闭右开区间）的数据，每个 TiKV 节点会负责多个 Region。TiKV 使用 Raft 协议做复制，保持数据的一致性和容灾。副本以 Region 为单位进行管理，不同节点上的多个 Region 构成一个 Raft Group，互为副本。数据在多个 TiKV 之间的负载均衡由 PD 调度，这里也是以 Region 为单位进行调度。
TiSpark
TiSpark 作为 TiDB 中解决用户复杂 OLAP 需求的主要组件，将 Spark SQL 直接运行在 TiDB 存储层上，同时融合 TiKV 分布式集群的优势，并融入大数据社区生态。至此，TiDB 可以通过一套系统，同时支持 OLTP 与 OLAP，免除用户数据同步的烦恼。
TiDB Operator
TiDB Operator 提供在主流云基础设施（Kubernetes）上部署管理 TiDB 集群的能力。它结合云原生社区的容器编排最佳实践与 TiDB 的专业运维知识，集成一键部署、多集群混部、自动运维、故障自愈等能力，极大地降低了用户使用和管理 TiDB 的门槛与成本。
-
- 安装部署
TiDB-Local单机版
在Centos 6的版本中如果要部署，这个难度还是比较大的，而且会有很多未知的坑，根据官方的建议，是需要在Centos 7以上的版本中，否则glibc的版本问题会很快碰到。
我们安装一套Centos7，采用快速的单机部署的方式来尝鲜。
2.1 下载安装包
wget http://download.pingcap.org/tidb-latest-linux-amd64.tar.gz
2.2 解压文件
tar -zxvf tidb-latest-linux-amd64.tar.gz
cd tidb-latest-linux-amd64
2.3 启动
启动PD
./bin/pd-server --data-dir=pd --log-file=pd.log &
启动tikv
./bin/tikv-server --pd="127.0.0.1:2379" --data-dir=tikv --log-file=tikv.log &
启动tidb-server
./bin/tidb-server --store=tikv --path="127.0.0.1:2379" --log-file=tidb.log &
2.4 登录
mysql -h 127.0.0.1 -P 4000 -u root
ES缺点
1. 不支持ACID特性，无法保证数据完整性。
2. 在高并发写入场景下，会出现数据被覆盖或丢失的情况。
3. 对于海量数据的查询，需要大量的内存和CPU资源，造成负载过大。
4. 对于其它类型的数据查询，如范围查询或关联查询等，性能不如关系型数据库MySQL。
商品数据库表：
分类表
品牌表
分类属性表
分类属性值表
spu表
spu图片表
spu属性表
spu属性值表
sku表
sku图片表
sku属性关系表
sku属性值关系表
7万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
