oracle学习心得
一、SQLSERVER的理解
SQLSERVER服务器就像一栋大楼，大楼里的机房就像服务器的数据库，机房里的电脑如同数据库里的表
1、登录用户可以登录服务器可以进大楼
2、登录用户成为数据库用户才能进指定的数据库进入大楼的人给了某个机房的钥匙才能进入机房
3、登录用户有权限使用表进入机房的人有电脑的密码才能使用电脑
二、ORACLE的理解
ORACLE服务器（全局数据库）就像一个商场，商场的每一家公司是表空间，公司的业务是表
1、数据库由多个表空间组成商场里有多家公司组成
2、表空间由段组成公司要有自己的经营业务，可以只有一个业务，就是一个表空
间中只有一个段，可以有多个业务，就是一个表空间有多个段3、段由区组成单个业务的细分类别。例如有家公司经营三个业务，卖书，卖家电，
卖衣服，则每个业务就是一个段。而每个业务又有细分，比如卖书的话要进行分类了。计算机区，人文区，小说区等，每一区都要放上书架存放书籍，则书架就是oracle块，存放数据的
三、数据库，表空间，用户（指定默认表空间），表统统由管理员管理四、在OEM中管理数据库的步骤1、创建
1)存储表空间创建表空间(TOMSPACE)（类似于在SQL中创建数据库，
通常可以省略，使用默认表空间为USERS,,临时表空间为TEMP）2)首先展开安全性创建新用户（TOM，指定表空间）（类似于在SQL中指定
数据库用户）3)创建表指定方案（用户）和表空间（列名不要带）4)设置约束
5)输入信息：方案用户名表右击查看/编辑目录…2、修改
1）方案用户名表2）修改表结构，添加约束3、删除：右击表移去
五、注意事项
1、刚创建的用户不会出现在方案中，只有创建了一个表指定方案和表空间，该用户的
方案名就会出现在方案中，此时就可以方便为该方案创建其他表
1）新创建的用户能在SQLPLUS中登录，为什么不能在企业管理器中登录呢？解答：需要授予SELECTANYDIRECTORY权限才能正常登录企业管理器，但
没有其他权限
2）可以对创建的用户在安全性中赋予角色权限，如DBA,则该用户就是数据库管理员
3）SYS用户主要用来维护系统信息和管理实例，只能以SYSOPER或SYSDBA角色登录
4）安装oracle的用户自动为ora-dba权限，自动是sys用户，所以在登录时不需要
用户名和密码，只要选中以sysdba登录。其他用户登录计算机系统，在使用oracle时就是正常登录状态了。
2、向表中添加日期数据：时间格式为DD-MM-YYYY怎么修改呢？如何插入时间数
据1）先在SQLPLUS中SELECTSYSDATEFROMDUAL;就知道日期的格式2）altersessionsetNLS_DATE_FORMAT="YYYY-MM-DD";修改当前输入格式3）oracle中的默认格式是：‘dd-mm-yyyy’；修改语句：altersessionset
nls_date_format="yyyy-mm-dd";修改后输入和显示都为指定格式，缺点是只
对当前会话起作用
假如你是要永久修改的话，改注册表：
regedit->hkey_local_machine->oracle->homeo->NLS_DATE_FORMAT值YYYY-MM-DD（经验证不管用）
默认日期：50年之前是当前世纪，50年之后是之前世纪，31-12月-49代表2049年01-1月-50代表1950年
4）由于在表编辑器中to_date函数的格式是dd-mon-yyyy，输入时为如下格式13-6月-1983（代码编辑时一样，世纪可以省略，代表当前世纪）5）to_char通常用于查询语句；to_date通常用于增删改语句6）两个’代表一个’六、代码编辑器1、SQLPLUS
2、SQLPLUSWORKSHEET3、isqlplus
设置列宽：setlinesize50;
七、与SQL201*中的查询分析器不同，SQLplusworksheet执行所有语句，不单单执行选中语句八、编辑清除；可以清除查询结果
九、查看环境变量：select*fromv$nls_parameters;十、示例用户
1)scott/tigernormal登录
2）hr:需要解锁和重设密码（alteruserhraccountunlock;）
十一、查看和更改oraclehttp占用端口号，D:\\ora9\\Apache\\Apache\\conf下，httpd.conf和httpd.conf.default中将80修改，然后重新启动HTTP服务
加个字符串
六、工具的使用
1、netmanager:配置服务主机名和监听主机名
2、netconfigurationassistant:配置监听程序，本地服务名配置等
/*****************************************************************************/oracle小知识点
1、数据操作时，字段区分大小写
2、Oracle两种认证模式：（1）操作系统认证（2）文件口令认证。初始化参数的文件目录为D:\\oracle_program\\database3、sqlplus中使用helpindex查看帮助信息目录，
?命令：查看某个命令的详细用法连接数据库后才能使用
5、当代码中使用函数时，函数中固定的关键字必须写正确，当放在字符串中的时候，编译没有错误，运行时才会出现错误，通常是SQL递归错误
扩展阅读：Oracle学习心得
成功源自勤奋
ORACLE学习笔录
ORACLE教学视频
1.开始---运行---CMD---SQLPLUS/NOLOG注释：进入不用输入用户、密码的模式
2.SQL->CONN/ASSYSDBA;或SQL->CONNSYS/TIGERASSYSDBA;注释：以SYS身份登录
3.SQL->SHOWUSER;注释：显示当前用户
4.SQL->ALTERUSERSYSACCOUNTUNLOCK;注释：使SYS用户处于解锁状态
5.SQL->ALTERUSERSCOTTIDENTIFIEDBYTIGER;注释：修改SCOTT的密码为：TIGER
SQL*PLUS登录的快捷方式：发送SQLPLUS到桌面快捷方式。在其右键属性目标里加入“CONN/ASSYSDBA”或其他语句.如下图：
===============================================================================登陆时出现ORA--12560协议适配器错误的解决方法。
右键单击我的电脑，属性--高级--环境变量---系统变量--新建，变量名=ORACLE_SID变量值=XXXX,XXXX就是你的DATABASESID
这里软件安装时默认创建了一个实例（即数据库）ORCL，当然默认安装时，实例名称你可以随意起。当然这里的变量值随着你的实例名称的改变而改变。例如
成功源自勤奋
===============================================================================关闭实例（数据库）的解决方法及启动方法
前提是：必须以DBA的身份登录数据库方可关掉数据库
SQL->SHUTDOWN注释：把当前数据库关掉了，再连接时会显示“已连接到空闲例程”如图
解决方法：步骤（一）：关掉当前使用的SQLPLUS
步骤（二）：把服务里面ORACLESERVERORCL重新启动即可步骤（三）：重新启动SQLPLUS就可以连接到实例上去了
===============================================================================ORACLE登录密码问题
===============================================================================1.SYS授权
成功源自勤奋
SQL->CONN/ASSYSDBA;注释：授权人须具有DBA的权限，因此以SYS登录以SYS身份创建一个名称为XIAOMING的用户，操作如下
在不给XIAOMING赋予登录的权限的情况下，XIAOMING是不能登录数据库的，如下图：
以SYS身份把连接数据库的权限赋予XIAOMING
这时候再用XIAOMING来登录数据库，看看效果如何，图如下：
附加说明：
SQL->GRANTCONNECTTOXIAOMING;等价于SQL->GRANTCREATESESSIONTOXIAOMING;注释：SYS用户授予小明连接数据库的权限
SQL->GRANTRESOURCETOXIAOMING;等价于SQL->GRANTCREATETABLETOXIAOMING;注释：SYS用户授予小明来创建表的权限
当然SYS有授权的权力，当然也有回收权限的权力SQL->CONN/ASSYSDBA;
SQL->REVOKECONNECTFROMXIAOMING;注释：SYS回收XIAOMING登录的权限SQL->CONNXIAOMING/TIGER;
SQL->CREATETABLETEXT(USERIDVARCHAR2(25),USERNAMEVARCHAR2(38));
想要查看表TEXT的结构SQL->DESCTEXT;
2.用户SCOTT把他的EMP表的查询权限赋予XIAOMINGSQL->GRANTSELECTONEMPTOXIAOMING;注释：SCOTT用户赋予XIAOMING查询他EMP表
的权限
SQL->GRANTUODATEONEMPTOXIAOMING;注释：SCOTT用户赋予XIAOMING更改他EMP
表的权限
SQL->GRANTALLONEMPTOXIAOMING;注释：SCOTT用户赋予XIAOMING对他EMP表进行
所有操作的权限包括（查询，修改，删除）
SQL->CONNXIAOMING/TIGER;
SQL->SELECT*FROMSCOTT.EMP;注释：此时XIAOMING可以查询SCOTT的EMP表3.用户SCOTT收回赋予用户XIAOMING的查询权限
成功源自勤奋
4.系统用户SYS回收SCOTT赋予XIAOMING对SCOTT的EMP表的查询权限
5.权限的维护
希望XIAOMING用户可以去查询SCOTT的EMP表、还希望XIAOMING可以把这个权限继续给别人。
如果是对象权限，对象权限传递就加入withgrantoption
这时候XIAOMING可以把对SCOTT.EMP表的查询权限往下传给XIAOHONG。
SQL->CONN/ASSYSDBA;注释：首先要以SYS的身份登录，才能创建XIAOHONG用户
SQL->CREATEUSERXIAOHONGIDENTIFIEDBYTIGER;注释：此时成功创建了XIAOHONG用户SQL->GRANTCONNECTTOXIAOHONG;注释：SYS授予XIAOHONG登录数据库的权限SQL->CONNXIAOMING/TIGER;注释：此时登录XIAOMING用户
SQL->GRANTSELECTONSCOTT.EMPTOXIAOHONG;注释：这时XIAOMING可以把SCOTT的
EMP表的查询权限授予XIAOHONG
如果是系统权限时，系统权限传递就加入WITHADMINOPTIONSys授予XIAOMING权限时：
SQL->GRANTCONNECTTOXIAOMINGWITHADMINOPTION;其后面的步骤和对象权限类似。？如果SCOTT把XIAOMING对EMP表的查询权限回收，那么XIAOMING会怎样?查询权限传递路线：SCOTT>XIAOMING>XIAOHONG
把XIAOMING的权限回收，那么XIAOHONG对Scott.emp表的权限也撤销了。(级联回收)注意：如果这里的SCOTT系统用户替换成SYS的话，
此时把XIAOMING的系统权限回收，那么XIAOHONG的系统权限没有被回收（对于系统权限来说，就不是级联回收了）
===============================================================================6．使用PROFILE管理用户口令
PROFILE是口令限制，资源限制的命令集合，当建立数据库时，ORACLE会自动建立名称为DEFAULT的PROFILE。当建立用户没有指定PROFILE选项，那ORACLE就会将DEFAULT分配给用户。(1)账户锁定
概述：指定该账户（用户）登陆时最多可以输入密码的次数，也可以指定用户锁定的时间（天）一般用DBA的身份去执行该命令。
例子：指定SCOTT这个用户最多只能尝试3次登陆，锁定时间为2天，让我们看怎么实现。SQL->CREATEPROFILELOCK_ACCOUNTLIMITFAILED_LOGIN_ATTEMPTS3
PASSWORD_LOCK_TIME2;注意：红色字体为固定句式，LOCK_ACCOUNT是文件名
SQL->ALTERUSERSCOTTPROFILELOCK_ACCOUNT;注意：给SCOTT登录的限制(2)给账户解锁
SQL->ALTERUSERSCOTTACCOUNTUNLOCK;(3)终止命令
为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要DBA身份来操作。
例子：给前面创建的用户XIAOMING创建一个PROFILE文件，要求该用户每隔10天要修改
成功源自勤奋
自家的登陆密码，宽限期为2天。看看怎么做。
SQL->CREATEPROFILEMYPROFILELIMITPASSWORD_LIFE_TIME10PASSWORD_GRACE_TIME2;SQL-l>ALTERUSERXIAOMINGPROFILEMYPROFILE;
(4)口令历史
概述：如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样Oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，ORACLE就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。例子：
1)建立PROFILE
SQL->CREATEPROFILEPASSWORD_HISTORYLIMITPASSWORD_LIFE_TIME10PASSWORD_GRACE_TIME2PASSWORD_REUSE_TIME10
注意：PASSWORD_REUSE_TIME//指定口令可重用时间(即10天后就可以重用)2）分配给某个用户。
SQL->ALTERUSERXIAOMINGPROFILEPASSWORD_HISTORY;
3）删除PROFILE
概述：当不需要某个PROFILE文件时，可以删除该文件。SQL->DROPPROFILEPASSWORD_HISTORY;【CASCADE】
注意：如果该文件已经分发给某个用户了，要删除的话，得在后面添加CASCADE
==============================================================================
Oracle卸载
1.2.3.4.5.
运行ORACLE的UNIVERSALINSTALLER
运行REGEDIT进入注册表，删除时一定要小心别删错了进入HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE
HKEY_LOCAL_MACHINE\\SYSTEM\\CURRENTCONTROLSET\\SERVICES下以ORACLE开头的值。重启计算机删除硬盘上的ORACLE目录，如果该目录不让删除，那么把这个目录改成一个别的名字，然后重启机器，再删除它。删除C:\\PROGRAMFILE下的ORACLE目录
===============================================================================连接命令：
（1）CONN[ECT]
当用特权用户身份连接时，必须带上ASSYSDBA或是ASSYSOPER（2）DISC[ONNECT]
说明：该命令用来断开与当前数据库的连接（3）PASSW[ORD]
说明：该命令用于修改用户的密码，如果要想修改其他用户的密码，需要用
SYS/SYSTEM登陆。
（4）SHOWUSER
说明：显示当前用户名（5）EXIT
说明：该命令会断开与数据库的连接，同时退出SQLPLUS文件操作命令
（1）START和@
说明;运行SQL脚本
案例：SQL->@D:\\A.SQL或者SQL->STARTD:\\A.SQL
（2）EDIT
说明：该命令可以编辑指定的SQL脚本案例：SQL->EDITD:\\A.SQL
（3）SPOOL
说明：该命令可以将SQLPLUS屏幕上的内容输出到指定文件中去案例：SQL->SPOOLD:\\B.SQL并输入SQL->SPOOLOFF
成功源自勤奋
交互式命令（1）&
说明:可以代替变量，而该变量在执行时，需要用户输入。案例：
SQL->SELECT*FROMEMPWHEREJOB=’&JOB’
显示和设置环境变量
概述：可以用来控制输出的格式，SETSHOW如果希望永久的保存相关的设置，可以去修改GLOGIN.SQL脚本。（1）LINESIZE
说明:设置显示行的宽度，默认是80个字符SQL->SHOWLINESIZE;SQL->SETLINESIZE90;
操作如下图：
（2）PADESIZE
说明:设置每页显示的行数目，默认是14用法和LINESIZE一样
===============================================================================PL/SQL中把表中日期（DATE）的格式修改。(原先里面的是DD-MM-YY)ALTERSESSIONSETNLS_DATE_FORMAT=’YYYY-MM-DD’;
注意：这样修改只能对于当前操作有效，并不能永久修改
===============================================================================删除数据:
DELETEFROMSTUDENT;
删除所有记录，表结构还在，写日志，可以恢复的，速度慢DROPTABLESTUDENT;//删除表的结构和数据
DELETEFROMSTUDENTWHEREXH=’A001’//删除一条记录TRUNCATETABLESTUDENT
删除表中所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。
===============================================================================当删掉一个表时，想把它恢复。则需要回滚。语句如：
SQL->SAVEPOINTAA;注意：再删除表之前进行该操作，设置一个名为AA的保存点
SQL->ROLLBACKTOAA;注意：当删除表后的操作，就可以恢复到设置保存点的时候的数据===============================================================================查看表的结构SQL->DESC表名;查询所有列
SELECT*FROMDEPT;//这个会对查询结果影响很大，速度慢查询指定[列]
成功源自勤奋
SELECTENAME,SAL,JOB,DEPTNOFROMEMP;//这种查询方式比较快查看查询所用的时间{
SETTIMINGON;//执行完之前得操作，当执行结束到会显示查询所需要的时间SETTIMINGOFF;}
===============================================================================超级复制（给表中插入它原本的东西）
INSERTINTOUSERS(字段，字段)SELECT字段，字段FROMUSER;查询表中前面几行
SELECT*FROMUSERSWHEREROWNUM‘1-1月-1982’;SELECT*FROMEMPWHERESALBETWEEN800AND1000;如何使用LIKE
%表示任意0到多个字符，_（下划线）代表任意的单个字符
SELECT*FROMSTUDENTWHEREXMLIKE‘%G’;或WHEREXMLIKE‘X%’;
SELECT*FROMSTUDENTWHEREXMLIKE‘__O%’;注意：查询第三个字母是O的姓名；在WHERE中使用IN
SELECT*FROMSTUDENTWHEREXMIN(‘XIAOMING’,’XIAOHONG’);ISNULL使用;
SELECT*FROMSTUDDENTWHEREXMISNULL;注意：切记不是XM=NULLORDERBYDESC或者ORDERBYASC
SELECT*FROMEMPORDERBYEMPTNO[ASC],SALDESC;//按照部门号升序而雇员的工资降序
排列。
使用到的别名排序
SELECTSAL“年薪”FROMEMPORDERBY“年薪”ASC；别名需要用双引号圈中或者SELECTSALAS“年薪”FROMEMPORDERBY“年薪”ASC;
成功源自勤奋
GROUPBY和HAVING子句
？显示每个部门的平均工资与最高工资
SELECTAVG(SAL),MAX(SAL),DEPTNOFROMEMPGROUPBYDEPTNO;//查询字段里面一定要
有DEPTNO，因为是按照他分组的。
？显示每个部门的每种岗位的平均工资与最高工资。
SELECTAVG(SAL),MAX(SAL),DEPTNO,JOBFROMEMPGROUPBYDEPTNO,JOB;
？显示平均工资低于201*的部门号和它的平均工资。
SELECTAVG(SAL),MAX(SAL),DEPTNOFROMEMPGROUPBYDEPTNOHAVINGAVG(SAL)ALL(SELECTSALFROMEMPWHEREDEPTNO=30);等价于
SELECTENAME,SAL,DEPTNOFROMEMPSAL>(SELECTMAX(SAL)FROMEMPWHEREDEPTNO=30);注意：提倡使用第二种方法，查询速度快ANY的用法
成功源自勤奋
SELECTENAME,SAL,DEPTNOFROMEMPWHERESAL>ANY(SELECTSALFROMEMPWHEREDEPTNO=30);等价于
SELECTENAME,SAL,DEPTNOFROMEMPSAL>(SELECTMIN(SAL)FROMEMPWHERE
DEPTNO=30);注意：提倡使用第二种方法，查询速度快？如何查询与SMITH的部门和岗位完全相同的所有雇员
SELECT*FROMEMPWHERE(DEPTNO,JOB)=(SELECTDEPTNO,JOB)FROMEMPWHERE=‘SMITH’);注意：前后两个字段要对应
？如何显示高于自己部门平均工资的员工的信息。
SELECTA.*FROMEMPA,(SELECTDEPTNO,AVG(SAL)ASPPFROMEMPGROUPBYDEPTNO)BWHEREA.DEPTNO=B.DEPTNOANDA.SAL>B.PP;
===============================================================================在FROM子句中使用子查询
这里需要说明的当在FROM子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在FROM子句中使用子查询时，必须给子查询指定别名。ORACLE分页一共有三种方式1．ROWNUM分页
SQL->SELECT*FROMDEPT
2.显示ROWNUM[Oracle分配的]
比如要查询前三条记录的话：
SELECTA.*,ROWNUMNFROM(SELECT*FROMDEPT)AWHEREROWNUM
成功源自勤奋
SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECT*FROMDEPT)AWHEREROWNUM=2;//但他这种方法速度相对于其他数据库来说比较快3.几个查询变化
a.如果指定查询列，只需修改最里面的子查询。
如：SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECTDEPTNO,LOCFROMDEPT)AWHEREROWNUM=2;
或者
SELECT*FROM(SELECTDEPTNO,LOC,ROWNUMNFROMDEPTWHEREROWNUM=2;
b.排序,只需修改最里面的子查询
SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECTDEPTNO,LOCFROMDEPTORDERBYDEPTNO)AWHEREROWNUM=2;
===============================================================================
*用查询结果来创建新表*(注意)和SQLSERVER的区别
CREATETABLEMYTABLE(ID,NAME,SAL,JOB,DEPTNO)ASSELECTEMPNO,ENAME,SAL,JOB,DEPTNOFROMEMP;注意：红色字段得一一对应这个区别于sql201*
SQL201*->SELECTEMPNOASID,ENAME,SAL,JOB,DEPTNOINTOMYTABLEFROMEMP;
===============================================================================合并查询
有时在实际应用中，为了合并多个SELECT语句的结果，可以使用集合操作符号UNION,UNIONALL,INTERSECT,MINUS(1)UNION
该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果中重复行SELECTENAME,SAL,JOBFROMEMPWHERESAL>2500UNIONSELECTENAME,SAL,JOBFROMEMPWHEREJOB=‘MANAGER’;
(2))UNIONALL
该操作符与UNION相似，但是它不会取消重复行，而且不会排序。
成功源自勤奋
SELECTENAME,SAL,JOBFROMEMPWHERESAL>2500UNIONALLSELECTENAME,SAL,JOBFROMEMPWHEREJOB=‘MANAGER’;
(3)INTERSECT
使用该操作符用于取得两个结果集的交集。
SELECTENAME,SAL,JOBFROMEMPWHERESAL>2500INTERSECTSELECTENAME,SAL,JOBFROMEMPWHEREJOB=‘MANAGER’;
(4)MINUS
使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二集合中的数据。
SELECTENAME,SAL,JOBFROMEMPWHERESAL>2500MINUSSELECTENAME,SAL,JOBFROMEMPWHEREJOB=‘MANAGER’;
===============================================================================
创建数据库有两种方法
1)通过ORACLE提供的向导工具//必须掌握
开始--程序--ORACLE-ORADBA10G_HOME1--CONFIGURATIONANDMIGRATIONTOOLS--DATABASECONFIGURATIONASSISTANT
2)我们可以用手工步骤直接创建
===============================================================================ORACLE中操作数据使用TO_DATE函数
ALTERSESSIONSETNLS_DATE_FORMAT=’YYYY-MM-DD’;//改变当前用户日期格式方法1：
INSERTINTOEMPVALUES(8976,‘HAH’,‘CLERK’,6754,‘18-12月-201*’,9876.00,0,20);方法2：
INSERTINTOEMPVALUES(8976,‘HAH’,‘CLERK’,6754,TO_DATE(‘201*-12-18’,‘YYYY-MM-DD’)9876.00,0,20);
===============================================================================
成功源自勤奋
利用子查询来插入大量数据（方法和前面超级复制一样）这个区分于SQLSERVER201*Oracle：
INSERTINTOUSER2(字段1，字段2)SELECT字段3，字段4FROMUSERS【WHERE条件】注意：ORACLE这里USER2要在这之前建好
Sql201*:SELECT字段1，字段2INTOUSER2FROMUSERS【WHERE条件】
注意：SQLSERVER这里USER2不能在该操作之前存在?希望员工Scott的岗位，工资，补助与smith员工一样
UPDATEEMPSET(JOB,SAL,COMN)=(SELECTJOB,SAL,COMNFROMEMPWHEREENAME=’SMITH’)WHEREENAME=’SCOTT’;
==============================================================================
事务
事务用于保证数据的一致性，它由一组相关的DML（即增加，删除，修改）语句组成，该组的DML语句要么全部成功，要么全部失败。
事务和锁
当执行事务操作时（DML语句），ORACLE会在被作用的表上加锁，防止其他用户修改表的结构。
提交事务
当执行COMMIT语句后可以提交事务，当执行了COMMIT语句后，会确认事务的变化，结束事务，删除保存点，释放锁，当使用COMMIT语句结束事务之后，其他会话将可以查看到事务变化后的新数据。
回退事务
在介绍回退事务前，我们先介绍一下保存点（SAVEPOINT）的概念和作用。保存点是事务中的一点，用于取消部分事务、当结束事务时，会自动删除该事务所定义的所有保存点，当执行ROLLBACK时，通过指定保存点可以回退到指定的点。SQL->SAVEPOINTA1;SQL->DELETEFROMEMP;SQL->ROLLBACKTOA1;
SQL->COMMIT;//提交，确保之前所作的是正确的，提交之后，保存点也随之消失了注意：如果在操作的过程中意外退出了，那么系统会默认执行COMMIT提交操作事务的几个重要操作：1）设置保存点
SAVEPOINTA3)取消部分事务
ROLLBACKTOA4)取消全部事务
ROLLBACK
只读事务
只读事务是指只允许执行查询的操作，而不允许执行任何其他DML操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。假定机票代售点每天18点开始统计今天的销售情况。这时可以使用只读事务，在设置了只读事务后，尽管其他会话可能会提交新的事务，但是只读事务将不会取得最新数据的变化，从而可以保持取得特定时间点的数据信息。
成功源自勤奋
设置只读事务
SETTRANSACTIONREADONLY
===============================================================================
SQL函数的使用
字符函数是ORACLE中最常用的函数，我们来看看有哪些字符函数。LOWER（CHAR）：将字符串转化成小写的格式UPPER（CHAR）：将字符串转化成大写的格式LENGTH（CHAR）:返回字符串的长度SUBSTR(CHAR,M,N):取字符串的子串
REPLACE（CHAR1,SEARCH_STRING,REPLACE_STRING）INSTR(CHAR1,CHAR2):取子串在字符串中的位置
这点区别于SQLSERVER中，SQLSERVER用PATINDEX(‘%,%’,字符串)//查找逗号出现的位置字符串转化函数SELECTCAST("2768453"ASNUMBER)-3FROMDUAL;
数学函数数学函数的输入参数和返回值的数据类型都是数字类型的，数学函数包含COS，COSH,EXP,LN,LOG,SIN,SINH,SQRT,TAN,TANH,ACOS,ASIN,ATAN,ROUND,我们讲最常用的
ROUND(N,[M]：/该函数用于执行四舍五入，如果省掉M，则四舍五入到整数；如果M是正
数，则四舍五入到小数点的M位后；如果M是负数，则四舍五入到小数点M位前。
TRUNC(N,[M])：该函数用于截取数字，如果省掉M，就截掉小数部分，如果M是正数就截
取到小数的M位后，如果M是负数，则截取到小数点的前M位。
MOD(M,N)：取模
FLOOR(N)：返回小于或等于N的最大整数CEIL(N)：返回大于或等于N的最小整数
对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果。
例子：ROUND(2456.34)=2456,ROUND(2456.56)=2457,
ROUND(2456.34,1)=2456.3,ROUND(5.67,1)=5.7
TRUNC(5.57,1)=5.5,TRUNC(56.67,-1)=50,MOD(10.3,4)=2.3其他数学函数：
ABS(N):返回数字N的绝对值ACOS(N):返回数字的反余弦值ASIN(N):返回数字的反正弦值ATAN(N):返回数字的反正切值COS(N):返回数字的余弦值EEP(N):返回E的N次方
LOG(M,N)返回以M为底的对数值LN(N):以E为底的对数
POWER(M,N)）:返回m的n次方日期函数
用于处理DATE类型的数据SYSDATE:该函数返回系统时间
例子：SELECTSYSDATEFROMDUAL;
ADD_MONTHS(D,M)
?查找已经入职8个多月的员工
答案：SELECT*FROMEMPWHERESYSDATE>ADD_MONTHS(HIREDATE,8);
成功源自勤奋
？对于每个员工，显示其加入公司的天数
答案：SELECTSYSDATE-HIREDATE"入职天数"FROMEMP;LAST_DAY(D):返回指定日期所在月份的最后一天？找出各月倒数第三天受雇的所有员工
SELECTHIREDATE,ENAME,LAST_DAY(HIREDATE)FROMEMPWHEREHIREDATE=LAST_DAY(HIREDATE)-2;
转换函数：
转换函数用于将数据类型从一种转为另外一种，在某些情况下，ORACLESERVER允许值的数据类型和实际的不一样。这时ORACLESERVER会隐形地转化数据类型。比如：CREATETABLET1(IDINT);
INSERTINTOT1VALUES(‘10’)//这样ORACLE会自动的把‘10’-->10CREATTABLET1(IDvarchar2(100));
Insertintot1values(1);这样ORACLE就会自动得把1-->‘1’TO_CHAR：？日期是否可以显示：时/分/秒
SELECTENAME,TO_CHAR(HIREDATE,’YYYY-MM-DDHH24:MI:SS’)FROMEMP;
?薪水是否可以显示指定的货币符号。
SELECTENAME,TO_CHAR(HIREDATE,’YYYY-MM-DDHH24:MI:SS’),TO_CHAR(SAL,’L99999.99’)FROMEMP;
SELECTENAME,TO_CHAR(HIREDATE,’YYYY-MM-DDHH24:MI:SS’),TO_CHAR(SAL,’L99,999.99’)FROMEMP;
成功源自勤奋
//其中L99,999.99中L表示本地货币号，如果安装的是中文版，那就是人民币。
99,999.99是根据这个题目的要求的，因为SAL是NUMBER(7,2)类型的，也可以随意指定比如：99999.99或99,999.99或99,999.000注意：L和99999.99中不能加空格
9：代表是数字，并忽略前面0
0：显示数字，如位数不足时，则用0补齐.：表示小数点，：表示逗号$：表示美元￥:表示人民币C:表示国际货币
？显示1980年入职的所有员工答案：
SELECTENAME,TO_CHAR(HIREDATE,‘YYYY’),TO_CHAR(SAL,‘L99,999.99’)FROMEMPWHERETO_CHAR(HIREDATE,’YYYY’)=1980;
？显示12月份入职的所有员工
答案：
SELECTENAME,TO_CHAR(HIREDATE,‘MM’),TO_CHAR(SAL,‘L99,999.99’)FRMEMPWHERETO_CHAR(HIREDATE,’MM’)=12;
===============================================================================系统函数：SYS_CONTEXT
1)TERMINAL:当前会话客户所对应的终端的标识符。2)LANGUSGE：语言
3)DB_NAME:当前数据库名称
4)NLS_DATE_FORMAT:当前会话客户所对应的日期格式5)SESSION_USER:当前会话客户所对应的数据库用户名6)HOST：返回数据库所在主机的名称
成功源自勤奋
7)CURRENT_SCHEMA：显示当前用户的方案名
固定句式：SELECTSYS_CONTEXT(‘USERENV’,DB_NAME)FROMDUAL;注意：红色部分是固定的
===============================================================================
数据库管理员
管理数据库的用户主要是SYS和SYSTEM（SYS相当于董事长，SYSTEM相当于总经理）(1)最重要的区别，存储的数据的重要性不同
SYS：所有ORACLE的数据字典的基表和视图都存放在SYS用户中，这些基表和视图对于ORACLE的运行来说是至关重要的，由数据库自己维护，任何用户都不能手动更改，SYS用户拥有DBA（数据库管理员）,SYSDBA（系统管理员/最高权限）,SYSOPER（系统操作员）角色或权限，是ORACLE权限中最高的用户。SYSTEM：用于存放次一级的内部数据，如：ORACLE的一些特性或工具的管理信息，SYSTEM用户拥有DBA，SYSDBA角色或系统权限。(2)其次的区别，权限的不同。
SYS用户必须以ASSYSDBA或ASSYSOPER形式登陆，不能以NORMAL方式登陆数据库。SYSTEM如果正常登陆，它其实就是一个普通的DBA用户，但是如果以ASSYSDBA登陆，其结果实际上它是作为SYS用户登陆的，从登陆信息里我们可以看出来例如：SYS可以建数据库，但SYSTEM不行
===============================================================================
管理初始化参数
初始化参数用于设置实例或是数据库的特征，ORACLE11g提供了200多个初始化参数，并且每个初始化参数都要默认值。
显示初始化参数
（1）SHOWPARAMETER命令注意：DBA权限的才可以查询如何修改参数
需要说明的如果你希望修改这些初始化的参数，可以到文件中去修改实例名
==============================================================================
数据库表的逻辑备份与恢复
逻辑备份是指使用工具EXPORT将数据对象的结构和数据导出到文件的过程，逻辑恢复是指当数据库对象被误操作而损坏后使用工具IMPORT利用备份的文件把数据对象导入数据库的过程，物理备份即可在数据库OPEN的状态下进行也可以关闭数据库后进行，但是逻辑备份和恢复只能在OPEN状态下进行。
导出
导出具体分为;导出表，导出方案，导出数据三种方式导出使用EXP命令来完成，该命令常用的选项有：
USERID：用于指定执行导出操作的用户名，口令，连接字符串TABLE：用于指定执行导出操作的表
成功源自勤奋
OWNER：用于指定执行导出操作的方案FULL=Y：用于指定导出操作的数据库
INCTYPE：用于指定执行导出操作的增量类型
ROWS：用于指定执行导出操作是否要导出表中的数据FILE：用于指定导出文件名导出表：
（1）导出自己的表
EXPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMP（2）导出其他方案的表
如果用户要导出其他方案的表，则需DBA的权限或是
EXP_FULL_DATABASE的权限，比如SYSTEM可以导出SCOTT的表
EXPUSERID=SYSTEM/TIGER@ORCLTABLES=(SCOTT.EMP)FILE=D:\\E1.DMP
注意：
在导入和导出的时候，要到ORACLE目录下的BIN目录下
操作如下图：
或快速进入
输出结果为下图：
导出多张表：
EXPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP,DEPT)FILE=D:\\E1.DMP
成功源自勤奋
（3）导出表的结构
EXPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMPROWS=N
（4）使用直接导出方式
EXPUSERID=SCOTT/TIGER@ORCLTABLES=(EMPFILE=D:\\E1.DMPDIRECT=Y
这种方式比默认的常规方式要快，当数据量大时，可以考虑使用这样的方法这时需要数据库的字符集要和客户端字符完全一致，否则会报错。
导出方案
导出方案是指使用EXPORT工具导出一个方案或是多个方案中的所有对象（表，索引，约束）和数据，并存放到文件中。（1）导出自己的方案
EXPSCOTT/TIGER@ORCLOWNER=SCOTTFILE=D:\\SCOTT.DMP
导出数据库
导出数据库是指利用EXPORT导出所有数据库中的对象及数据，要求该用户具有DBA权限或是EXP_FULL_DATABASE权限
EXPUSERID=SYSTEM/TIGER@ORCLFULL=YINCTYPE=COMPLETEFILE=D:\\X.DMP
（2）导出其他方案
如果用户要导出其他方案，则需要DBA的权限或是
EXP_FULL_DATABASE的权限，例如SYSTEM用户就可以导出任何方案（但SYS另外）EXPSYSTEM/TIGER@ORCLOWNER=(SYSTEM,SCOTT)FILE=D:\\SYSTEM.DMP
===============================================================================
导入
导入就是使用工具import将文件中的对象和数据导入到数据库中，但是导入要使用的文件必须是export所导出的文件，与导出相似，导入也分为导入表，导入方案，导入数据库三种方式。
IMP常用的选项有
USERID：用于指定执行导入操作的用户名，口令，连接字符串TABLE：用于指定执行导入操作的表
成功源自勤奋
FORMUSER：用于指定源用户TOUSER：用于指定目标用户FILE：用于指定导入文件名
FULL=Y：用于指定执行导入整个文件
INCTYPE：用于指定执行导入操作的增量类型ROWS：指定是否要导入表行数据IGNORE:如果表存在，则只导入数据(1)导入自己的表
IMPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMP
(2)导入其他用户的表
要求该用户具有DBA的权限，或是IMP_FULL_DATABASE的权限
IMPUSERID=SYSTEM/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMPTOUSER=SCOTT(3)导入表的结构
IMPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMPROWS=N
(5)如果对象（如此表）已经存在可以只导入表的数据
IMPUSERID=SCOTT/TIGER@ORCLTABLES=(EMP)FILE=D:\\E1.DMPIGNORE=Y导入方案
导入方案是指使用IMPORT工具将文件中的对象和数据导入到一个或是多个方案中。如果要导入其他方案，要求该用户具有DBA的权限，或是IMP_FULL_DATABASE的权限（1）导入自己的方案
IMPUSERID=SCOTT/TIGER@ORCLFILE=D:\\XXX.DMP
（2）导入其他方案
要求该用户具有DBA的权限
IMPUSERID=SYSTEM/TIGER@ORCLFILE=D:\\XXX.DMPFORMUSER=SYSTEMTOUSER=SCOTT
导入数据库
在默认情况下，当导入数据库时，会导入所有对象结构和数据，案例如下：
IMPUSERID=SYSTEM/TIGERFULL=YFILE=D:\\XXX.DMP//这里数据库实例都不用写了，默认===============================================================================数据字典
数据字典是ORACLE数据库中最重要的组成部分，他提供了数据库的一些系统信息
数据字典记录了数据库的系统信息，他是只读表和视图的集合，数据字典的所有者为SYS用户。
用户只能在数据字典上执行查询操作（SELECT语句），而其维护和修改是由系统自动完成的。
数据字典包括字典基表和数据字典视图，其中基表存储数据库的基本信息，普通不能直接访问数据字典的基表，数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息，数据字典视图主要包括USER_XXX,ALL_XXX,DBA_XXX三种类型。动态性能视图记载了例程启动后的相关信息例如：
USER_TABLES:
用于显示当前用户所拥有的所有表，它只返回用户所对应方案的所有表比如:SELECTTABLE_NAMEFROMUSER_TABLES
成功源自勤奋
ALL_USERS:
用于显示当前用户可以访问的所有表。它不仅返回当前用户方案的所有表，还会返回当前用户可以访问的其他方案的表；
比如：SELECTTABLE_NAMEFROMALL_TABLESDBA_TABLES:
它会显示所有方案拥有的数据库表，但是查询这种数据库字典视图，要求用户必须是dba角色或是SELECTANYTABLE系统权限。
例如：当用SYSTEM用户查询数据字典视图DBA_TABLES时，会返回SYSTEM,SYS,SCOTT…方案所对应的数据库表。
===============================================================================用户名、权限、角色
在建立用户时，ORACLE会把用户的信息存放到数据字典中，当给用户授予权限或是角色时，ORACLE会将权限和角色的信息存放到数据字典。
通过查询DBA-USERS可以显示所有数据库用户的所有信息；
通过查询数据字典视图DBA_SYS_PRIVS,可以显示用户所具有的系统权限；通过查询数据字典视图DBA_TAB_PRIVS可以显示用户具有的对象权限；通过查询数据字典DBA_COL_PRIVS可以显示用户具有的列权限；通过查询数据库字典视图DBA_ROLE_PRIVS可以显示用户所具有的角色例如：要查看SCOTT具有的角色，可查询DBA_ROLE_PRIVSSELECT*FROMDBA_ROLE_PRIVSWHEREGRANTEE=’SCOTT’
//查询ORACLE中所有的系统权限，一般是DBA
SELECT*FROMSYSTEM_PRIVILEGE_MAPORDERBYNAME;//查询ORACLE中所有的角色，一般是DBASELECT*FROMDBA_ROLES;
//查询ORACLE中所有对象权限，一般是DBASELECTDISTINCTPRIVILEGEFROMDBA_TAB_PRIVS;//查询数据库的表空间
SELECXTTABLESPACE_NAMEFROMDBA_TABLESPACE;
?问题
1．如何查询一个角色包括的权限
a.一个角色包含的系统权限
SELECT*FROMDBA_SYS_PRIVSWHEREGRANTEE=’DBA’//CONNECT应大写另外也可以这样查看
SELECT*FROMROLE_SYS_PRIVSWHEREROLE=’DBA’b.一个角色包含的对象权限
SELECT*FROMDBA_TAB_PRIVSWHEREGRANTEE=’CONNECT’
2.如何查看某个用户，具有什么样的角色。
SELECT*FROMDBA_ROLE_PRIVSWHEREGRANTEE=’用户名’
显示当前用户可以访问的所有数据字典视图
SELECT*FROMDIETWHERECOMMENTSLIKE‘%GRANT%’
成功源自勤奋
===============================================================================
管理表空间和数据文件
表空间是数据库的逻辑组成部分，从物理上讲，数据库数据存放在数据文件中；从逻辑上讲，数据库则是存放在表空间中，表空间由一个或是多个数据文件组成。ORACLE中逻辑结构包括表空间、段、区、和块。
说明一下数据库由表空间构成，而区又是由ORACLE块构成的这样的一种结构，可以提高数据库的效率。
表空间用于从逻辑上组织数据库的数据，数据库逻辑上是由一个或是多个表空间组成。通过表空间可以达到以下作用：
（1）控制数据库占用的磁盘空间
（2）DBA可以将不同数据类型部署到不同的位置，这样有利于提高I/O性能，同时利
于备份和恢复等管理操作
建立表空间
建立表空间是使用CREATETABLESPACE命令完成的。需要注意的是，一般情况下，建立表空间是特权用户或是DBA来执行的。如果用其他用户来创建表空间，则用户必须要具有CREATETABLESPAC的系统权限。建立数据表空间
在建立数据库后，为了便于管理表，最好建立自己的表空间Createtablespacedata01datafile‘d:\\test\\data01.dbf’Size20muniformsize128k
注意：20m表示表空间的大小，最大不能超过500m128k表示区得大小
说明：执行完上述命令后，会建立名称为data01的表空间，并为该表空间建立名称为data01.dbf的数据文件，区的大小为128k
使用数据表空间//由sys创建，登陆scott用户就可以看到了
Createtablemypart(deptnonumber(4),dnamevarchar2(14),locvarchar2(13))tablespacedata01;改变表空间的状态
当建立表空间时，表空间处于联机的（online）状态，此时该表空间是可以访问的，并且该表空间是可以读写的，即可以查询该表空间的数据，而且还可以在表空间执行各种语句，但是在进行系统维护和数据维护时，可能需要改变表空间的状态，一般情况下，由特权用户或是dba来操作。
（1）使表空间脱机
Altertablespace表空间名offline;
（2）使表空间联机
Altertablespace表空间名online;
（3）只读表空间
当建立表空间时，表空间可以读写，如果不希望在该表空间上执行update，delete，insert操作，那么可以将表空间修改为只读Altertablespace表空间名readonly
（4）知道表空间名，显示该表空间包括的所有表
select*fromall_tableswheretablespace_name="data01";
（5）知道表明查看该表空间属于哪个表空间（所查的用户应该存在表EMP）
selecttablespace_name,table_namefromuser_tableswheretable_name="EMP";
（6）改变表空间的状态使表空间可以读写
Altertablespace表空间名readwrite;
（7）删除表空间
一般情况下，由特权用户或是dba来操作，如果是其他用户操作，那么要求用户具有droptablespace系统权限
Droptablespace‘表空间’includingcontentsanddatafiles;
成功源自勤奋
说明：includingcontents表示删除表空间时，删除该空间的所有数据库对象，而datafiles表示将数据库文件也删除。
扩展表空间
表空间是由数据文件组成的，表空间的大小实际就是数据文件添加的大小，那么我们可以想象，假定employee存放到data01表空间上，初始大小就是2M，当数据满2M空间后，如果再向employee表插入数据，这样就会显示空组不足的错误。案例说明：
1．建立一个表空间data01
2．在该表空间上建立一个普通表mydept，其结构和dept一样
3．向该表空间中加入数据insertintomydeptselect*fromdept;4．当一定时候就会出现无法扩展的问题，怎么办？
5．就扩展该表空间，为其增加更多的存储空间，有三种方法：
解决方法：
（1）增加数据文件
Sql->altertablespacedata01adddatafile‘d:\\data01.dbf’size20m
（2）增加数据文件的大小
Sql->altertablespacedata01datafile‘d:\\data01.dbf’resize20m这里需要注意的是数据文件的大小不要超过500m
（3）设置文件的自动增长
Sql->alterdatabasedatafile‘d:\\data01.dbf’autoextendonnext10mmaxsize500m;
移动数据文件
有时，如果你的数据文件所在的磁盘损坏时，该数据文件不能再使用，为了能够重新使用，需要将这些文件的副本移动到其他的磁盘，然后恢复。下面以移动数据文件data01.dbf为例来说明1)确定数据文件所在的表空间
Selecttablespace_namefromdba_data_fileswherefile_name=’d:\\data01.dbf’2)使表空间脱机
确保数据文件的一致性，将表空间转变为offline状态Altertablespacedata01offline;3)使用命令移动数据文件到指定的目标位置
Sql->hostmoved:\\data01.dbfc:\\data01.dbf4)执行altertablespace命令
在物理上移动了数据后，还必须执行altertablespace命令对数据文件进行逻辑修改；
Sql->altertablespacedata01renamedatafile‘d:\\data01.dbf’to‘c:\\data01.dbf’5)使表空间联机
Altertablespacedata01online;
===============================================================================
约束
约束用于确保数据库数据满足特定的商业规则，在Oracle中，约束包括：notnull、uniqu、
成功源自勤奋
primarykey，foreignkey，和check五种例如:
CREATETABLEGOODS--商品信息表(
GOODIDCHAR(8)PRIMARYKEY,GOODNAMEVARCHAR2(30),
UNITPRICENUMBER(10,2)CHECK(UNITPRICE>0),CATEGORYVARCHAR2(8),PROVIDERVARCHAR2(50));
CREATETABLECUSTOMER--顾客信息表(
CUSTOMERIDCHAR(8)PRIMARYKEY,NAMEVARCHAR2(50)NOTNULL,ADDRESSVARCHAR2(50),
EMAILVARCHAR2(50)UNIQUE,
SEXCHAR(2)DEFAULT‘男’CHECK(SEXIN(‘男’,’女’)),CARDIDVARCHAR2(18));
CREATETABLEPURCHASE--购买信息表(
CUSTOMERIDCHAR(8)REFERENCESCUSTOMER(CUSTOMERID)，//外键指向另一个表的某个字段GOODIDCHAR(8)REFERENCESGOODS(GOODID),NUMNUMBER(5)CHECK(NUMBETWEEN1AND30));
如果建表时忘记了建立必要的约束，则可以在建表后使用altertable命令为表增加约束，但是要注意的是：增加NOTNULL约束时，需要使用modify选项，而增加其他四种约束使用add系选项。
例如：在以上表中增加商品名也不能为空
ALTERTABLEGOODSMODIFYGOODNAMENOTNULL;例如：增加身份证也不重复
ALTERTABLECUSTOMERADDCONSTRAINTHAHAUNIQUE(CARDID);
例如：添加客户的住址只能是‘海淀’，‘朝阳’，‘东城’，‘西城’，‘通州’
ALTERTABLECUSTOMERADDCONSTRAINT名称CHECK(ADDRESSIN("海淀"，"朝阳"，"东城"，"西城"，"通州"));删除约束
ALTERTABLE表名DROPCONSTRAINT约束名称；在删除主键的时候，可能有错误，比如ALTERTABLE表名DROPPRIMARYKEY;这是因为如果两张表存在主从关系，那么在删除主表的主键约束时，必须带上CASCADE选项如：ALTERTABLE表名DROPPRIMARYKEYCASCADE;显示约束的信息1.显示约束信息
通过要查询的数据字典视图USER_CONSTRAINTS,可以显示当前用户所有的约束的信息。
成功源自勤奋
SELECTCONSTRAINT_NAME,CONSTRAINT_TYPE,STATUSFROMUSER_CONSTRAINTSWHERETABLE_NAME=’表名’；
2.显示约束列
通过查询数据字典视图USER_CONS_COLUMNS,可以显示约束所对应的表信息列
SELECTCOLUMN_NAME,POSITIONFROMUSER_CONS_COLUMNSWHERECONSTRAINT_NAME=’约束名’表级定义
表级定义是指定义了所有列之后，再定义约束，这里需要注意：NOTNULL约束只能在列级定义上定义
以在EMPLOYEE表时定义主键约束和外键约束为例：
CREATETABLEEMPLOYEE(
EMP_IDNUMBER(8),NAMEVARCHAR2(18),DEPT_IDNUMBER(2),
CONSTRAINTPK_EMPLOYEEPRIMARYKEY(EMP_ID),CONSTRAINTFK_DEPARTMENTFOREIGNKEY(EMP_ID),REFERENCESDEPARTMENT(DEPT_ID));列级定义
列级定义是在定义列的同时定义约束如在DEPARTMENT表定义主键约束CREATETABLEDEPARTMENT(
DEPT_IDNUMBER(2)CONSTRAINTPK_DEPARTMENTPRIMARYKEY,NAMEVARCHAR2(12),LOCVARCHAR2(12));
管理索引
索引是用来加速数据存取的数据对象，合理地使用索引可以降低I/O次数，从而提高数据访问性能，索引有很多种我们主要介绍常用的几种：单列索引
单列索引是基于单个列所建立的索引，比如：CREATEINDEX索引名ON表名（列名）复合索引
复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：
CREATEINDEXEMP_INDEXON表名（ENAME，JOB）；//先按ENAME查，再按JOB查CREATEINDEXEMP_INDEXON表名（JOB，ENAME）；//这两个是有区别的SQL语句优化
这就提示我们应该尽可能地把那种很容易就把一些数据范围减小的条件写在后面：如：SELECT*FROMCUSTOMERWHERECATEGORY=’’ANDENAME=’’;
Sql语句扫描是从后往前扫描，如：ENAME一下子把范围减小了，应该写GATEGORY后面，这样筛选的速度快。索引的缺点
1.建立索引，系统要占用大约为表的1.2倍的硬盘和内存来保存索引。
成功源自勤奋
2.更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引
的一致性。
实践表明，不恰当的索引不但于事无补，反而降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费的时间更多的系统时间。比如：在如下字段建立索引应该不恰当：1、很少或从不引用的字段；
2、逻辑型的字段，如男或女（是或否）等，综上所述，提高查询查询效率是以消耗一定的
系统资源为代价的，索引不能盲目地建立，这是考验一个DBA是否优秀的很重要的指标。显示表的所有索引
在同一张表上可以有多个索引，通过查询数据字典视图dba_indexes和user_indexes,可以显示索引信息，其中dba_indexs用于显示数据库所有的索引信息，而user_insexs用于显示当前用户的索引信息；
SELECTINDEX_NAME,INDEX_TYPEFROMUSER_INDEXESWHERETABLE_NAME=’表名’；显示索引列
通过查询数据字段视图user_ind_columns,可以显示索引对应的列的信息
SELECTTABLE_NAME,COLUMN_NAMEFROMUSER_IND_COLUMNSWHEREINDEX_NAME=’索引名’===============================================================================
管理权限
//查询Oracle中所有的系统权限，一般是dba
SELECT*FROMSYSTEM_PRIVILEGE_MAPORDERBYNAME;//查询Oracle中所有的角色，一般是dbaSELECT*FROMDBA_ROLES;
//查询Oracle中所有对象权限，一般是dba
SELECTDISTINCTPRIVILEGEFROMDBA_TAB_PRIVS;//查询数据库的表空间
SELECTTABLESPACE_NAMEFROMDBA_TABLESPACES;//查询某个用户具有怎样的角色
SELECT*FROMDBA_ROLE_PRIVSWHEREGRANTEE=’用户名’；//查看某个角色包括哪些系统权限。
SELECT*FROMDBA_SYS_PRIVSWHEREGRANTEE=’DBA’;或者是:
SELECT*FROMROLE_SYS_PRIVSWHEREROLE=’DBA’;
权限
权限是指执行特定类型sql命令或是访问其他方案对象的权利。包括系统权限和对象权限两种。
系统权限：是指执行特定类型sql命令的权利。它用于控制用户可以执行的一个或一组数据库操作。比如当所有用户具有createtable权限时，可以在其方案中建表，当用户具有createanytable权限时，可以在任何方案中建表Oracle提供了100多种系统权限。常用的有：
Createsession连接数据库Createview建视图
Createprocedure建过程、包Createcluster建簇Createtable建表
Createtrigger建触发器显示系统权限
Oracle提供了100多种权限，而且Oracle得版本越高，提供的系统权限就越多，我们可以查询数据字典视图system，privilege，map,可以显示所有系统的权限SELECT*FROMSYSTEM_PRIVILEGE_MAPORDERBYNAME;
成功源自勤奋
GRANTCREATESESSION,CREATETABLETOXIAOMINGWITHADMINOPTION;系统权限不能级联回收例如：
SQL->CONNSYS/TIGERASSYSDBA;//登陆系统用户
SQL->CREATEUSERXIAOMINGIDENTIFIEDBYTIGER;//以系统用户身份创建小明用户SQL->CREATEUSERXIAOHONGIDENTIFIEDBYTIGER;//以系统用户身份创建小红用户SQL->GRANTCONNECTTOXIAOMINGWITHADMINOPTION;//授予小明连接数据库这个系统权
限，并授予他把权限传下去
SQL->CONNXIAOMING/TIGER;//这时小明就可以连接到数据库了
SQL->GRANTCONNECTTOXIAOHONG;//接着小明把连接数据库的权限传给小红SQL->CONNSYS/TIGERASSYSDBA;//登陆系统用户
SQL->REVOKECONNECTFROMXIAOMING;//SYS把小明连接数据库的权限
SQL->CONNXIAOHONG/TIGER;//思考~这时小红的权限有没有被回收呢？答案：没有被回收
授予对象权限
注意：WITHGRANTOPTION选项不能被授予角色。
1.MONKEY用户要操作SCOTT.EMP表，则必须授予相应的对象权限
（1）希望MONKEY可以查询SCOTT.EMP的表数据，怎样操作GRANTSELECTONEMPTOMONKEY
（2）希望MONKEY可以修改SCOTT.EMP的表数据，怎样操作
GRANTUPDATEONEMPTOMONKEY
（3）希望MONKEY可以删除SCOTT.EMP的表数据，怎样操作
GRANTDELETEONEMPTOMONKEY
(4)有没有更加简单的方法，一次把所有权限赋给MONKEY?GRANTALLONEMPTOMONKEY2.能否对MONKEY访问权限更加精细控制。（授予列权限）
（1）希望MONKEY只可以修改SCOTT.EMP的表的SAL字段，怎样操作？
GRANTUPDATEONEMP(SAL)TOMONKEY
（2）希望MONKEY只可以查询SCOTT.EMP的表的ENAME,SAL数据，怎样操作？（3）GRANTSELECTONEMP(ENAME,SAL)TOMONKEY3.授予ALTER权限
如果BLACK用户要修改SCOTT.EMP表的结构，则必须授予ALTER对象权限SQL->CONNSCOTT/TIGER
SQL->GRANTALTERONEMPTOBLACK;当然也可以用SYSTEM，SYS来完成这件事4.授予EXECUTE权限
如果用户想要执行其他方案的包/过程/函数。则须有EXECUTE权限。比如为了让KEN可以执行包DBNS_TRANSACTION,可以授EXECUTE权限SQL->CONNSYSTEM/TIGER;
SQL->GRANTEXECUTEONDBNS_TRANSACTIONTOKEN;5.授予INDEX权限
如果想在别的方案的表上建立索引，则必须具有INDEX对象权限，如为了让BLACK可以在SCOTT.EMP上建立索引，就给其INDEX对象权限SQL->CONNSCOTT/TIGER;
SQL->GRANTINDEXONSCOTT.EMPTOBLACK;6.使用WITHGRANTOPTION选项
该选项用于转授对象权限，但是该选项只能被授予用户，而不能授予角色SQL->CONNSCOTT/TIGER;
SQL->GRANTSELECTONEMPTOBLACKWITHGRANTOPTIONSQL->CONNBLACK/TIGER
SQL->GRANTSELECTONSCOTT.EMPTOJONES
成功源自勤奋
回收对象权限
收回对象的权限可以由对象的所有者来完成，也可以用DBA用户（SYS,SYSTEM）来完成这里要说明的是：收回对象权限后，用户就不能执行相应的SQL命令，但是要注意的是对象的权限是否会被级联收回呢？如：
SCOTT------------>BLACK------------->JONES
SELECTONEMPSELECTONEMPSELECTONEMPSQL->CONNSCOTT/TIGER@ORCL
SQL->REVOKESELECTONEMPFROMBLACK思考：JONES能否查询SCOTT.EMP表数据
答案：这个区别系统权限，这个会被级联回收的，也就是JONES不能查询SCOTT.EMP的数据===============================================================================角色
角色就是相关权限的命令的集合，使用角色的主要目的就是简化权限的管理（为了好管理，ORACLE事先把一系列的权限集中在一起打包赋予某些角色，在吧角色交给某个用户）。假定有用户A,B,C为了让他们都拥有权限（1）连接数据库
（2）在SCOTT.EMP表上SELECT,INSERT,UPDATE如果采用直接授权操作，则需要进行12次授权现在我们采用角色来简化该操作
首先将CREATESESSION,SELECTONSCOTT.EMP,INSERTONSCOTT.EMP,UPDATEONSCOTT.EMP授予角色，然后该角色授予A,B,C用户，这样就可以三次授权搞定了。角色分为预定义角色和自定义角色两类：预定义角色
预定义角色是指ORACLE所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色CONNECT,RESCOURCE,DBA(一)CONNECT角色
CONNECT角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予CONNECT和RESOURCE角色就够了，那么CONNECT角色具有哪些系统权限呢？
ALTERSESSION//修改会话CREATECLUSTER//创建簇
CREATEDATABASELINK//创建数据库链接CREATESESSION//链接数据库CREATETABLE//创建表CREATEVIEW//创建视图CREATESEQUENCE//创建序列(二)RESOURCE角色
RESOURCE角色具有应用开发人员所需要的其他权限，比如建立存储过程，触发器等，这里需要注意的是RESOURCE角色隐含了UNLIMITEDTABLESPACE系统权限。RESOURCE角色包含了以下系统权限：CREATECLUSTERCREATEINDEXTYPECREATETABLECREATESEQUENCECREATETYPE
CREATEPROCEDURECREATETRIGGER(三)DBA角色
DBA角色具有所有的系统权限，及WITHADMINOPTION选项，默认的DBA用户为SYS和SYSTEM他们可以将任何系统权限授予其他用户，但是要注意的是DBA角色不具备
成功源自勤奋
SYSDBA和SYSOPER的特权（启动和关闭数据库）
自定义角色
根据自己的需要来定义，一般是DBA来建立，如果用的别的用户来建立，则需要具有CREATEROLE的权限，在建立角色时可以指定验证方式（不验证，数据库验证等）（一）建立角色（不验证）
如果角色时公用的角色，可以采用不验证的方式建立角色CREATEROLE角色名NOTIDENTIFIED;
（二）建立角色（数据库验证）
采用这样的方式时，角色名、口令名存放在数据库中，当激活该角色时，必须提供口令，在建立这种角色时，需要为其提供口令CREATEROLE角色名IDENTIFIEDBY密码；角色授权
当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相
应的系统权限和对象权限。
（一）给角色授权
举个例子：
SQL->CONN/ASSYSDBA;
SQL->CREATEROLEMTROLENOTIDENTIFIED;
SQL->GRANTCREATESESSIONTOMYROLEWITHADMINOPTION;
SQL->CONNSCOTT/TIGER;
SQL->GRANTSELECTONSCOTT.EMPTOMYROLE;
SQL->GRANTINSERT,UPDATE,DELETEONSCOTT.EMPTOMYROLE
通过上面的步骤，就给角色授权了。接着把角色分配给用户。(具有DBA权限才可以)
SQL->CONN/ASSYSDBA;
SQL->CREATEUSERXIAOMINGIDENTIFIEDBYTIGER;
SQL->GRANTMYROLETOXIAOMINNG;//此时XIAOMING就拥有MYROLE中的权限具体操作如下图：
成功源自勤奋
（二）删除角色
使用DROPROLE,一般是DBA来执行，如果其他用户要求该用户具有DROPANYROLE权限
SQL->CONN/ASSYSDBA;SQL->DROPROLE角色名；
这时删除角色，XIAOMING也就不具有MYROLE中的权限（级联）。
（三）显示角色的信息
1显示所有角色○
SQL->SELECT*FROMDBA_ROLES;//DBA才可以查询2显示角色具有的系统权限○
SQL->SELECTPRIVILEGE,ADMIN_OPTIONFROMROLE_SYS_PRIVSWHEREROLE=’角色名’;
3显示角色具有的对象权限○
通过查询数据字典视图DBA_TAB_PRIVS可以查看角色具有的对象权限或是列的权限。
4显示用户具有的角色，及默认角色○
当以用户的身份连接到数据库时，ORACLE会自动地激活默认的角色，通过查询数据字典视图DBA_ROLE_PRIVS可以显示某个用户具有的所有角色及当前默认的角色
SQL->SELECTGRANTED_ROLE,DEFAULT_ROLEFROMDBA_ROLE_PRIVSWHEREGRANTEE=’用户名’；
成功源自勤奋
（四）精细访问控制
是指用户可以使用函数、策略实现更加细微的安全访问控制，如果使用精细访问控制。则当在客户端发出SQL语句（SELECT,INSERT,UPDATE,DELETE）时，ORACLE会自动在SQL语句后追加谓词（WHERE子句），并执行新的SQL语句，通过这样的控制，可以使得不同的数据库用户在访问相同表时，返回不同的数据信息，如：
用户：SCOTTBLAKEJONES策略：EMP.ACCESS数据库表EMP
上面所示：通过策略EMP.ACCESS,用户SCOTT,BLAKE,JONES在执行相同的SQL语句时，可以返回不同的结果。例如，当执行SELECTENAMEFROMEMP;会根据实际情况返回不同的结果。
===============================================================================PL/SQL编程
PL/SQL（PROCEDURALLANGUAGE/SQL）是ORACLE在标准SQL语言上的扩展，PL/SQL不仅允许嵌入SQL语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能更加强大。（缺点：PL/SQL移植性不好）SQLPLUS开发工具
SQLPLUS是ORACLE公司提供的一个工具.举一个简单的案例：
编写一个存储过程，该过程可以向某表中添加记录解答：1.创建一个简单的表
SQL->CREATETABLEMYTEXT(NAMEVARCHAR2(30),PASSWSVARCHAR2(30));2.创建过程
SQL->CREATEORREPLACEPROCEDURESP_PRO1IS
注释：或如果存在SP_PRO1，就可以REPLACEPROCEDURESQL->BEGIN
SQL->INSERTINTOMYTEXTVALUES(‘WANGP’,’TIGER’);SQL->END;
SQL->/注释：表示让ORACLE去创建这样的一个存储过程
如何查看错误信息？答案：showerror如何调用该过程？○1EXEC过程名（参数值1，参数值2...）;
2CALL过程名（参数值1，参数值2...）;○
PL/SQLDEVELOPER开发工具
PL/SQLDEVELOPER是用于开发PL/SQL块的集成开发环境（IDE），他是一个独立的产品，而不是ORACLE的一个附带品。举一个简单案例：
成功源自勤奋
编写一个存储过程，该过程可以删除某表记录
把里面内容复制到对话框中
PL/SQL编程的基础单位是块（编程），通过块的概念可以编写过程（存储过程）、函数、触发器、包。编写规范：1注释：○
单行注释
SELECT*FROMEMPWHEREEMPNO=7788取得员工信息多行注释
/*......*/来划分2标识符号的命名规范○
1)当定义变量时，建立用V_作为前缀比如;V_SAL2)当定义常量时，建议用C_作为前缀C_RATE
3)当定义游标时，建议用_CURSOR作为后缀EMP_CURSOR4)当定义例外时，建议用E_作为前缀E_ERROR块的介绍
块（BLOCK）是PL/SQL的基本程序单元，编写PL/SQL程序实际上就是编写PL/SQL块。要完成相对简单的应用功能，可能只需要编写一个PL/SQL块，但是如果要想实现复杂的功能，可能需要在一个PL/SQL块中嵌套其他的PL/SQL
PL/SQL块由三个部分构成：定义部分、执行部分、例外处理部分。如下所示：
DECLARE
/*定义部分------定义常量、变量、游标、例外、复杂数据类型*/BEGIN
/*执行部分------要执行的PL/SQL语句和SQL语句*/EXCEPTION
/*例外处理部分-----处理运行的各种错误*/END;
实例1-只包括执行部分的PL/SQL块SETSERVEROUTPUTON打开输出选项BEGIN
DBMS_OUTPUT.PUT_LINE(‘LINE’);END;
？相关说明：
DBMS_OUTPUT是ORACLE所提供的包（类似JAVA的开发包），该包包含一些过程，PUT_LINE就是DBMS_OUTPUT包的一个过程。
成功源自勤奋
实例2-包含定义部分和执行部分的PL/SQL块
DECLARE
V_ENAMEVARCHAR2(5);--定义字符串变量BEGIN
SELECTENAMEINTOV_ENAMEFROMEMPWHEREEMPNO=&NO;DBMS_OUTPUT.PUT_LINE("雇员名："||V_ENAME);END;/
相关说明：
&表示要接受从控制台输入的变量注意：每条语句后面得加分号（；）
DECLARE
V_ENAMEVARCHAR2(5);--定义字符串变量V_SALNUMBER(7,2);--显示用户的工资BEGIN
SELECTENAME，SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&no1;DBMS_OUTPUT.PUT_LINE("雇员名："||V_ENAME||"薪水为："||V_SAL);END;/
成功源自勤奋
实例3-包含定义部分、执行部分和例外部分
为了避免PL/SQL程序的运行错误，提高PL/SQL的健壮性，应该对可能的错误进行处理，这个很有必要：
1比如在实例2中，如果输入了不存在的雇员号，应当做例外处理。○
2有时出现异常，希望用另外的逻辑处理。○
相关说明：ORACLE事先预定义了一些例外，NO_DATA_FOUND就是找不到数据的例外。
DECLARE
V_ENAMEVARCHAR2(5);--定义字符串变量V_SALNUMBER(7,2);--显示用户的工资BEGIN
SELECTENAME，SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&no1;DBMS_OUTPUT.PUT_LINE("雇员名："||V_ENAME||"薪水为："||V_SAL);--异常处理EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE("您输入得编号有误！");END;/
过程：
过程用于执行特定的操作，当建立过程时，既可以指定输入参数（IN），也可以指定输出参数（OUT），通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，
成功源自勤奋
可以将执行部分的数据传递到应用环境，在SQLPLUS中可以使用CREATEPROCEDURE命令来建立过程。
实例如下：
1请考虑编写一个过程,可以输入雇员名，薪工资，可修改雇员的工资○
2如何调用过程有两种方法：EXEC,CALL○
实例4-
CREATEPROCEDURESP_PRO3(SPNAMEVARCHAR2,NEWSALNUMBER)IS
--DECLARE
BEGIN
--执行部分，根据用户名去修改工资
UPDATEEMPSETSAL=NEWSALWHEREENAME=SPNAME;END;
//执行过程用EXEC或CALL
?如何使用过程返回值函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含RETURN子句，而在函数体内必须包含RETURN语句返回的数据，我们可以使用CREATEFUNCTION来建立函数，实际案例：
--函数案例
--输入雇员的姓名返回该雇员的年薪
CREATEFUNCTIONSP_FUN2(SPNAMEVARCHAR2)RETURNNUMBERISYEARSALNUMBER(7,2);--函数只能返回一个值
BEGIN--执行部分
SELECTSAL*12+NVL(COMM,0)*12INTOYEARSALFROMEMPWHEREENAME=SPNAME;RETURNYEARSAL;END;
成功源自勤奋
在PL/SQL中调用函数（左）-----------------在SQLPLUS中调用函数（右）
---------------------------------------
//注意：在PL/SQL中INTO后面的冒号（：）后面不能
有空格，但是SQLPLUS可以允许加空格。
包包用于逻辑上组合过程和函数，它由包规范和包体两部分组成。1我们可以使用CREATEPACKAGE命令来创建包：○
实例：
--创建一个包：SP_PACKAGE--声明了该包有一个过程--声明了该包有一个函数
CREATEPACKAGESP_PACKAGEIS
PROCEDUREUPDATE_SAL(NAMEVARCHAR2,NEWSALNUMBER);FUNCTIONSP_FUN(NAMEVARCHAR2)RETURNNUMBER;END;
包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数，
2建立包体可以使用CREATEPACKAGEBODY命令○
--创建包体
CREATEORREPLACEPACKAGEBODYSP_PACKAGEIS
PROCEDUREUPDATE_SAL(NAMEVARCHAR2,NEWSALNUMBER)ISBEGIN
UPDATEEMPSETSAL=NEWSALWHEREENAME=NAME;END;
FUNCTIONSP_FUN(NAMEVARCHAR2)RETURNNUMBERISANNUAL_SALARYNUMBER;BEGIN
成功源自勤奋
SELECTSAL*12+NVL(COMM,0)INTOANNUAL_SALARYFROMEMPWHEREENAME=NAME;RETURNANNUAL_SALARY;END;END;
注意：包里面定义几个函数,几个过程，包体里面也要想对应地去声明函数例如：现在包里创建了一个过程（UPDATE_SAL）和一个函数（SP_FUN）当包体中少声明了函数（SP_FUN），就会报错。具体操作如下图：
如果该包删掉会出现怎样的错误呢？
如何调用包的过程或是函数当带调用包的过程或是函数时，在过程和函数前需要带有包名，如果需要访问其他方案的包，还需要在包名前加方案名。
如：SQL->CALLSP_PACKAGE.UPDATE.SAL(‘SCOTT’,1500);特别说明：
成功源自勤奋
包是PL/SQL中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力。
===============================================================================问题有待解决
视频上讲call和exec都可以用来调用，但是经过仔细操作，看下面截图：
调用包的过程（左）-----------------------------------直接调用过程（右）
注释：这里对于过程来说，CALL和EXEC都是可行的，但是对于函数来说，请看截图调用包的函数（左）-----------------------------------直接调用函数（右）
注释：可以看出来，函数只用CALL来调用是可行的，一旦用EXEC的话便会报错。（这点我也不懂ing
）===============================================================================
成功源自勤奋
触发器
触发器是指隐含的执行的存储过程，当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括INSERT、UPDATE、DELETE语句，而触发操作实际就是一个PL/SQL块，可以使用CREATETRIGGER来建立触发器。特别说明：
触发器是非常有用的，可维护数据库的安全和一致性。定义并使用变量
在编写PL/SQL程序时，可以定义变量和常量；在PL/SQL程序中包括：1标量类型（SCALAR）○
定义标量的案例
1定义一个变长字符串○
V_ENAMEVARCHAR2(10)
2定义一个小数，范围-9999.99~9999.99○
V_SALNUMBER(6,2);
3定义一个小数并给一个初始值为5.4：=是PL/SQL的赋值号○
V_SAL2NUMBER(6,2):=5.4
4定义一个日期类型的数据○
V_HIREDATEDATE;
5定义一个布尔变量，不能为空，初始值为FALSE○
V_VALIDBOOLEANNOTNULLDEFAULTFALSE;标量的使用
在定义好变量后，就可以使用这些变量。这里需要说明的是PL/SQL块为变量赋值不
同于其他的编程语言，需要再等号前加冒号（：=）
例子：输入员工工号，显示雇员姓名、工资、个人所得税（税率为0.03）
DECLARE
C_TAX_RATENUMBER(3,2):=0.03;
--用户名
V_ENAMEVARCHAR2(5);V_SALNUMBER(7,2);V_TAX_SALNUMBER(7,2);BEGIN--执行
SELECTENAME,SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&NO;--计算所得税
V_TAX_SAL:=V_SAL*C_TAX_RATE;--输出
DBMS_OUTPUT.PUT_LINE("姓名是："||V_ENAME||"他的工资"||V_SAL||"所得税"||V_TAX_SAL);END;
对上面的PL/SQL块有一个问题：
就是如果员工的姓名超过了5字符的话，就会有错误，为了降低PL/SQL程序的维护工作
成功源自勤奋
量，可以使用%TYPE属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
标识符名表名.列名%TYPE
DECLARE
C_TAX_RATENUMBER(3,2):=0.03;
--用户名
V_ENAMEEMP.ENAME%TYPE;--V_ENAME的类型和EMP表中的ENAM字段的类型一致V_SALNUMBER(7,2);--V_SALEMP.SAL%TYPEV_TAX_SALNUMBER(7,2);BEGIN--执行
SELECTENAME,SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&NO;--计算所得税
V_TAX_SAL:=V_SAL*C_TAX_RATE;--输出
DBMS_OUTPUT.PUT_LINE("姓名是："||V_ENAME||"他的工资"||V_SAL||"所得税"||V_TAX_SAL);END;
提倡：最好把所有定义的类型都该成和表字段类型一致2复合类型（COMPOSITE）○
用于存放多个值的变量，主要包括这几种：
1PL/SQL记录○
类似于高级语言的结构体，需要注意的是，当引用PL/SQL记录成员时，必须要加
记录变量作为前缀（记录变量，记录成员）如下：2PL/SQL表○
3嵌套表○
4VARRAY○
--PL/SQL记录实例
DECLARE
--定义一个PL/SQL记录类型EMP_RECORD_TYPE,类型包含三个数据：NAME、SALARY、TITLE
TYPEEMP_RECORD_TYPEISRECORD(NAMEEMP.ENAME%TYPE,SALARYEMP.SAL%TYPE,TITLEEMP.JOB%TYPE);
--定义了一个变量SP_RECORD,这个变量的类型EMP_RECORD_TYPE
SP_RECORDEMP_RECORD_TYPE;BEGIN
SELECTENAME,SAL,JOBINTOSP_RECORDFROMEMPWHEREEMPNO=7788;
DBMS_OUTPUT.PUT_LINE("员工名："||SP_RECORD.NAME||"工资是"||SP_RECORD.SALARY||"工作岗位是："||SP_RECORD.TITLE);END;
复合类型-PL/SQL表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而PL/SQL是可以为负数的，并且表元素的下标没有限制，实例如下：
--PL/SQL表实例
DECLARE
--定义了一个PL/SQL表类型：SP_TABLE_TYPE该类型是用于存放EMP.ENAME%TYPE
成功源自勤奋
--INDEXBYBINARY_INTEGER;表示下标是整数
TYPESP_TABLE_TYPEISTABLEOFEMP.ENAME%TYPEINDEXBYBINARY_INTEGER;
--定义了一个SP_TABLE变量，这个变量的类型是SP_TABLE_TYPE:
SP_TABLESP_TABLE_TYPE;BEGIN
SELECTENAMEINTOSP_TABLE(0)FROMEMPWHEREEMPNO=7788;DBMS_OUTPUT.PUT_LINE("员工名："||SP_TABLE(0));END;
注意：如果把WHERE去掉会怎样呢？
解答：因为SP_TABLE(0)是指定返回一条数据，可是把WHERE去掉，就返回多条数据。3参照类型（REFERENCE）○
参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间，在编写PL/SQL程序时，可以使用游标变量（REFCURSOR）和对象类型变量（REFOBJ_TYPE）两种参照变量类型。参照变量-REFCURSOR游标变量
使用游标时，当定义游标时不需要指定相应地SELECT语句，但是当使用游标时（OPEN时）需要指定SELECT语句，这样一个游标就与一个SELECT语句结合了。实例如下：
1请使用PL/SQL编写一个块，○可以输入部门号，并显示该部门所有员工姓名和他的工资。
DECLARE
--定义游标
TYPESP_EMP_CURSORISREFCURSOR;
--定义一个游标变量
TEST_CURSORSP_EMP_CURSOR;
--定义接收姓名,和工资的变量
V_ENAMEEMP.ENAME%TYPE;V_SALEMP.SAL%TYPE;BEGIN--执行
--把TEST_CURSOR和一个SELECT结合
OPENTEST_CURSORFORSELECTENAME,SALFROMEMPWHEREDEPTNO=&NO;--把这个游标指向一个结果集--循环取出LOOP
--FETCH就是取出的意思
FETCHTEST_CURSORINTOV_ENAME,V_SAL;--判断是否TEST_CURSOR是否为空，然后退出循环EXITWHENTEST_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE("名字："||V_ENAME||"工资："||V_SAL);ENDLOOP;END;
成功源自勤奋
2在○1基础上，如果某个员工的工资低于201*元，就增加100元。○
DECLARE
--定义游标
TYPESP_EMP_CURSORISREFCURSOR;
--定义一个游标变量
TEST_CURSORSP_EMP_CURSOR;
--定义接收姓名,和工资的变量
V_ENAMEEMP.ENAME%TYPE;V_SALEMP.SAL%TYPE;BEGIN--执行
--把TEST_CURSOR和一个SELECT结合
OPENTEST_CURSORFORSELECTENAME,SALFROMEMPWHEREDEPTNO=&NO;--把这个游标指向一个结果集--循环取出LOOP
FETCHTEST_CURSORINTOV_ENAME,V_SAL;--判断是否TEST_CURSOR是否为空EXITWHENTEST_CURSOR%NOTFOUND;IFV_SAL
成功源自勤奋
PL/SQL中的控制结构
小练习
编写一个过程，可以输入一个雇员名，如果该雇员的工资低于201*，就给该雇员工资增加100CREATEORREPLACEPROCEDURESP_PRO3(SPNAMEVARCHAR2)IS
--定义
V_SALEMP.SAL%TYPE;BEGIN
SELECTSALINTOV_SALFROMEMPWHEREENAME=SPNAME;IFV_SAL
成功源自勤奋
多重条件分支IF---THEN---ELSIF---ELSE
?编写一个过程，可以输入一个雇员名，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其他职位的雇员工资增加200CREATEORREPLACEPROCEDURESP_PRO1(SPNAMEVARCHAR2)ISV_JOBEMP.JOB%TYPE;BEGIN
SELECTJOBINTOV_JOBFROMEMPWHEREENAME=SPNAME;IFV_JOB="PRESIDENT"THEN
UPDATEEMPSETSAL=SAL+1000WHEREENAME=SPNAME;ELSIFV_JOB="MANAGER"THEN
UPDATEEMPSETSAL=SAL+500WHEREENAME=SPNAME;ELSE
UPDATEEMPSETSAL=SAL+200WHEREENAME=SPNAME;ENDIF;END;
**注意：这里是ELSIF，而不是ELSEIF
循环语句LOOP是PL/SQL中最简单的循环语句，这种循环语句以LOOP开头，以ENDLOOP结尾，这种循环至少会被执行一次。案例：现有一张表USERS，表结构如下用户ID用户名请编写一个过程，可输入用户名，并循环添加10个用户到USERS表中、用户编号从1开始增加。CREATETABLEUSERS(IDNUMBER(2),NAMEVARCHAR2(67));CREATEORREPLACEPROCEDURESP_PRO2(SPNAMEVARCHAR2)ISV_IDNUMBER(2):=1;BEGINLOOPINSERTINTOUSERSVALUES(V_ID,SPNAME);V_ID:=V_ID+1;EXITWHENV_ID>10;ENDLOOP;END;循环语句WHILE对于WHILE来说，只要有条件为TRUE时，才会执行循环语句，WHILE循环以WHILE..LOOP开始，以ENDLOOP结束请编写一个过程，可输入用户名，并循环添加10个用户到USERS表中、用户编号从11开始增加。案例：现有一张表USERS，表结构同上表；
CREATEORREPLACEPROCEDURESP_PRO2(SPNAMEVARCHAR2)ISV_IDNUMBER:=11;BEGIN
WHILEV_ID○2NULL
成功源自勤奋
NULL语句不会执行任何操作，并且会直接将控制传递到下一条语句，使用NULL语句的主要好处是可以提高PL/SQL的可读性。案例：DECLARE
V_SALEMP.SAL%TYPE;V_ENAMEEMP.ENAME%TYPE;BEGIN
SELECTENAME,SALINTOV_ENAME,V_SALFROMEMPWHEREEMPNO=&NO;IFV_SAL
成功源自勤奋
--创建一个包，在该包中，定义类型TEST_CURSOR,是个游标
CREATEORREPLACEPACKAGETESTPACKAGEASTYPETEST_CURSORISREFCURSOR;ENDTESTPACKAGE;
2创建一个过程，并调用包○
CREATEORREPLACEPROCEDURESP_PRO9(SPNOINNUMBER,P_CURSOROUTTESTPACKAGE.TEST_CURSOR)ISBEGIN
OPENP_CURSORFORSELECT*FROMEMPWHEREDEPTNO=SPNO;END;注意：//该程序是在JAVA中调用
编写分页过程
SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECT*FROMEMP)AWHEREROWNUM=6;
请编写一个存储过程，要求可以输入表名、每页显示记录数，当前页，返回总记录数，总页数和返回的结果集。
--创建一个包，在该包中，定义类型TEST_CURSOR,是个游标
CREATEORREPLACEPACKAGETESTPACKAGEASTYPETEST_CURSORISREFCURSOR;ENDTESTPACKAGE;
--开始编写分页的过程
CREATEORREPLACEPROCEDURESP_FENYE(TABLENAMEINVARCHAR2,PAGESIZESINNUMBER,PAGENOWINNUMBER,
MYROWSOUTNUMBER,--返回总记录数MYPAGECOUNTOUTNUMBER,--返回总页数P_CURSOROUTTESTPACKAGE.TEST_CURSOR)IS
--定义部分
--定义SQL语句字符串
V_SQLVARCHAR2(1000);
--定义两个整数
V_BEGINNUMBER:=(PAGENOW-1)*PAGESIZES+1;V_ENDNUMBER:=PAGENOW*PAGESIZES;BEGIN
V_SQL:="SELECT*FROM(SELECTA.*,ROWNUMNFROM(SELECT*FROM"||TABLENAME||")AWHEREROWNUM="||V_BEGIN;--把游标和SQL语句关联起来】OPENP_CURSORFORV_SQL;--要计算MYROWS和MYPAGECOUNT--组织一个SQL语句
V_SQL:="SELECTCOUNT(*)FROM"||TABLENAME;--执行SQL，并把返回的值，赋给MYROWSEXECUTEIMMEDIATEV_SQLINTOMYROWS;--计算MYPAGECOUNT
IFMOD(MYROWS,PAGESIZES)=0THENMYPAGECOUNT:=MYROWS/PAGESIZES;ELSE
MYPAGECOUNT:=MYROWS/PAGESIZES+1;ENDIF;--关闭游标--CLOSEP_CURSOR;
END;注意：该过程时在JAVA中执行
成功源自勤奋
===============================================================================Java代码：
Publicstaticvoidmain(string[]rags){Try{
class.forName("Oracle.jdbc.driver.OracleDriver");Connectionct=DriverManager.GetConnnection
("jdbc:oracle:thin:@192.168.186:1158:MYORACLE","SCOTT","TIGER");CallableStatementcs=ct.prepareCall("callSP_FENTE(?,?,?,?,?,?)")//给？赋值
cs.setString(1,"EMP");cs.setString(2,5);cs.setInt(3,1);//注册总记录数
cs.registerOutparameter(4,oracle.jdbc.oracleTypes.INTEGER);//注册总列数
cs.registerOutparameter(5,oracle.jdbc.oracleTypes.INTEGER);//注册返回的结果集
cs.registerOutparameter(6,oracle,jdbc.oracleTypes.CURSOR);cs.execute();
//取出总记录数/这里要注意，getInt(4)中4，是由该参数的位置决定的IntMYROWNUM=cs.getInt(4);intMYPAGECOUNT=cs.getInt(5);
Resultsetrs=(ResultSet)cs.getobject(6);//显示一下，看看是否对不对
System.out.println("MYROWNUM="+MYROWNUM);System.out.println("总页数："+MYPAGECOUNT);
while(rs.next()){Sys.out.println("编号："+rs.getint(1)+"名字："+getstring(2))};}catch(exceptione){e.printStackTrace();}}
============================================================================================
PL/SQL的例外处理
案例：编写一个过程，可接收雇员的编号，并显示该雇员的姓名。问题是：如果输入的雇员编号不存在，怎样去处理呢？
DECLARE
V_NAMEEMP.ENAME%TYPE;BEGIN
SELECTENAMEINTOV_NAMEFROMEMPWHEREEMPNO=&NO;DBMS_OUTPUT.PUT_LINE("姓名："||V_NAME);EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE("编号没有！");END;
处理预定义例外
预定义例外时由PL/SQL所提供的系统例外，当PL/SQL应用程序违反了Oracle规定的限制时，则会隐含地触发一个内部例外。
成功源自勤奋
PL/SQL为开发人员提供了二十多个预定义例外，下面来介绍常用的例外。（一）预定义例外CASE_NOT_FOUND
在开发PL/SQL块中编写CASE语句时，如果WHERE子句中没有包含必须的条件分支，就会触发CASE_NOT_FOUND的例外
CREATEORREPLACEPROCEDURESP_PRO9(SPNONUMBER)ISV_SALEMP.SAL%TYPE;BEGIN
SELECTSALINTOV_SALFROMEMPWHEREEMPNO=SPNO;CASE
WHENV_SAL
成功源自勤奋
（三）预定义例外DUP_VAL_ON_INDEX
在唯一索引所对应的列上插入重复的值时，会隐含地触发例外
--DUP_VAL_ON_INDEX
BEGIN
INSERTINTODEPTVALUES(10,"公关部","北京"）;EXCEPTIONWHEN
DUP_VAL_ON_INDEXTHEN
DBMS_OUTPUT.PUT_LINE("在DEPTNO列上不能出现重复值");
END;
（四）预定义例外INVALID_CURSOR
当视图在不合法的游标上执行操作时，会触发该例外
例如：视图从没有打开的游标提取数据，或是关闭没有打开的游标，则会触发该例外
DECLARE
CURSOREMP_CURSORISSELECTENAME,SALFROMEMP;EMP_RECORDEMP_CURSOR%ROWTYPE;BEGIN
--OPENEMP_CURSOR;打开游标FETCHEMP_CURSORINTOEMP_RECORD;DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);CLOSEEMP_CURSOR;EXCEPTION
WHENINVALID_CURSORTHEN
DBMS_OUTPUT.PUT_LINE("请检测游标是否打开");
END;
成功源自勤奋
（五）预定义例外INVALID_NUMBER
当输入的数据有误时，会触发该例外比如：数字100写成1oo就会触发该例外
BEGIN
UPDATEEMPSETSAL=SAL+"1oo";EXCEPTION
WHENINVALID_NUMBERTHEN
DBMS_OUTPUT.PUT_LINE("输入的数字不正确");
END;
（六）预定义例外NO_DATA_FOUND
下面是一个PL/SQL块，当执行SELECTINTO没有返回行，就会触发该例外
DECLARE
V_SALEMP.SAL%TYPE;BEGIN
SELECTSALINTOV_SALFROMEMP--WHEREENAME="&NAME";WHEREENAME="WANGWP";EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE("不存在该员工");
END;
（七）预定义例外TOO_MANY_ROWS
当执行SELECTINTO语句时，如果返回超过了一行，则会触发该例外。
DECLARE
V_ENAMEEMP.ENAME%TYPE;BEGIN
SELECTENAMEINTOV_ENAMEFROMEMP;EXCEPTION
友情提示：本文中关于《oracle学习心得》给出的范例仅供您参考拓展思维使用，oracle学习心得：该篇文章建议您自主创作。
来源：网络整理 免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。
《oracle学习心得》
由互联网用户整理提供,转载分享请保留原作者信息,谢谢!
http://m.bsmz.net/gongwen/712043.html
- 上一篇：实验幼儿园教职工大会制度
- 下一篇：大一总结大二计划