oracle的sql语句

发布时间：2017-03-31 00:00:00 编辑：小静手机版

　　// 提交事务

　　ct.commit();

　　// 关闭打开的资源

　　sm.close();

　　ct.close();

　　} catch (Exception e) {

　　// 如果发生异常，就回滚

　　try {

　　ct.rollback();

　　} catch (SQLException e1) {

　　e1.printStackTrace();

　　}

　　e.printStackTrace();

　　}

　　再运行一下，会出现异常，查看数据库，数据没变化。。 ? 只读事务只读事务是指只允许执行查询的操作，而不允许执行任何其它dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。假定机票代售点每天18点开始统计今天的销售情况，这时可以使用只读事务。在设置了只读事务后，尽管其它会话可能会提交新的事务，但是只读事务将不会取得最新数据的变化，从而可以保证取得特定时间点的数据信息。 ? 设置只读事务 set transaction read only;

　　9.oracle的函数

　　sql函数的使用字符函数? 介绍字符函数是oracle中最常用的函数，我们来看看有哪些字符函数： ? lower(char)：将字符串转化为小写的格式。 ? upper(char)：将字符串转化为大写的格式。 ? length(char)：返回字符串的长度。 ? substr(char,m,n)：取字符串的子串 n代表取n个的意思，不是代表取到第n个 ? replace(char1,search_string,replace_string) ? instr(char1,char2,[,n[,m]])取子串在字符串的位置问题：将所有员工的名字按小写的方式显示 SQL> select lower(ename) from emp; 问题：将所有员工的名字按大写的方式显示。 SQL> select upper(ename) from emp; 问题：显示正好为5个字符的员工的姓名。 SQL> select * from emp where length(ename)=5; 问题：显示所有员工姓名的前三个字符。

　　Oracle 笔记

　　SQL> select substr(ename,1,3) from emp; 问题：以首字母大写,后面小写的方式显示所有员工的姓名。 SQL> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; 问题：以首字母小写,后面大写的方式显示所有员工的姓名。 SQL> select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp; 问题：显示所有员工的姓名，用“我是老虎”替换所有“A” SQL> select replace(ename,'A', '我是老虎') from emp; 数学函数? 介绍数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括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的最小整数对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果。问题：显示在一个月为30天的情况下，所有员工的日薪金，忽略余数。 SQL> select trunc(sal/30), ename from emp; or SQL> select floor(sal/30), ename from emp; 在做oracle测试的时候，可以使用dual表 select mod(10,2) from dual;结果是0 select mod(10,3) from dual;结果是1 其它的数学函数，有兴趣的同学可以自己去看看： abs(n)：返回数字n的绝对值 select abs(-13) from dual; acos(n)：返回数字的反余弦值 asin(n)：返回数字的反正弦值 atan(n)：返回数字的反正切值 cos(n)： exp(n)：返回e的n次幂 log(m,n)：返回对数值 power(m,n)：返回m的n次幂日期函数? 介绍日期函数用于处理date类型的数据。默认情况下日期格式是dd-mon-yy 即12-7月-78 (1)sysdate: 该函数返回系统时间 (2)add_months(d,n) (3)last_day(d)：返回指定日期所在月份的最后一天问题：查找已经入职8个月多的员工 SQL> select * from emp where sysdate>=add_months(hiredate,8); 问题：显示满10年服务年限的员工的姓名和受雇日期。

　　Oracle 笔记

　　SQL> select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10); 问题：对于每个员工，显示其加入公司的天数。 SQL> select floor(sysdate-hiredate) "入职天数",ename from emp; or SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp; 问题：找出各月倒数第3天受雇的所有员工。 SQL> select hiredate,ename from emp where last_day(hiredate)-2=hiredate; 转换函数 ? 介绍√ 转换函数用于将数据类型从一种转为另外一种。在某些情况下，oracle server允许值的数据类型和实际的不一样，这时oracle server会隐含的转化数据类型比如： create table t1(id int); insert into t1 values('10');-->这样oracle会自动的将'10' -->10 create table t2 (id varchar2(10)); insert into t2 values(1); -->这样oracle就会自动的将1 -->'1'; 我们要说的是尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数进行转换。 ? to_char 你可以使用select ename, hiredate, sal from emp where deptno = 10;显示信息，可是，在某些情况下，这个并不能满足你的需求。问题：日期是否可以显示时/分/秒 SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp; 问题：薪水是否可以显示指定的货币符号 SQL> yy：两位数字的年份 2004-->04 yyyy：四位数字的年份 2004年 mm：两位数字的月份 8月-->08 dd：两位数字的天 30号-->30 hh24： 8点-->20 hh12：8点-->08 mi、ss-->显示分钟\秒 9：显示数字，并忽略前面0 0：显示数字，如位数不足，则用0补齐 .：在指定位置显示小数点 ,：在指定位置显示逗号 $：在数字前加美元 L：在数字前面加本地货币符号 C：在数字前面加国际货币符号 G：在指定位置显示组分隔符、 D：在指定位置显示小数点符号(.) 问题：显示薪水的时候，把本地货币单位加在前面 SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp; 问题：显示1980年入职的所有员工 SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;

　　Oracle 笔记

　　问题：显示所有12月份入职的员工 SQL> select * from emp where to_char(hiredate, 'mm')=12; ? to_date 函数to_date用于将字符串转换成date类型的数据。问题：能否按照中国人习惯的方式年—月—日添加日期。系统函数 ? sys_context 1)terminal：当前会话客户所对应的终端的标示符 2)lanuage: 语言 3)db_name：当前数据库名称 4)nls_date_format：当前会话客户所对应的日期格式 5)session_user：当前会话客户所对应的数据库用户名 6)current_schema：当前会话客户所对应的默认方案名 7)host：返回数据库所在主机的名称通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库? select sys_context('USERENV','db_name') from dual; 注意：USERENV是固定的，不能改的，db_name可以换成其它,比如select sys_context('USERENV','lanuage') from dual;又比如select sys_context('USERENV','current_schema') from dual;

　　10.数据库管理，表的逻辑备份与恢复

　　内容介绍 1.上节回顾 2.数据库管理员 3.数据库(表)的逻辑备份与恢复 √ 4.数据字典和动态性能视图 √ 5.管理表空间和数据文件 √ 期望目标 1.了解oracle管理员的基本职责 2.掌握备份和恢复数据库/表的方法 3.理解表空间、数据字典、性能视图数据库管理员 ? 介绍每个oracle数据库应该至少有一个数据库管理员(dba)，对于一个小的数据库，一个dba就够了，但是对于一个大的数据库可能需要多个dba分担不同的管理职责。那么一个数据库管理员的主要工作是什么呢： ? 职责 1.安装和升级oracle数据库 2.建库，表空间，表，视图，索引? 3.制定并实施备份和恢复计划 4.数据库权限管理，调优，故障排除 5.对于高级dba，要求能参与项目开发，会编写sql语句、存储过程、触发器、规则、约束、包 ? 管理数据库的用户主要是sys和system (sys好像是董事长，system好像是总经理，董事长比总经理大，但是通常是总经理干事) 在前面我们已经提到这两个用户，区别主要是： 1.最重要的区别，存储的数据的重要性不同

　　Oracle 笔记

　　sys：所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper角色或权限，是oracle权限最高的用户。 system：用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有dba，sysdba角色或系统权限。看图： sysdba可以建数据库，sysope不能建数据库 2. 其次的区别，权限的不同。 sys用户必须以as sysdba或as sysoper形式登录。不能以normal方式登录数据库 system如果正常登录，它其实就是一个普通的dba用户，但是如果以as sysdba登录，其结果实际上它是作为sys用户登录的，从登录信息里面我们可以看出来。 sysdba和sysoper权限区别图，看图： sysdba>sysoper>dba 可以看到：只要是sysoper拥有的权限，sysdba都有;蓝色是它们区别的地方。(它们的最大区别是：sysdba可以创建数据库，sysoper不可以创建数据库) ? dba权限的用户 dba用户是指具有dba角色的数据库用户。特权用户可以执行启动实例，关闭实例等特殊操作，而dba用户只有在启动数据库后才能执行各种管理工作。 (相当于说dba连startup和shutdown这两个权限都没有) 两个主要的用户，三个重要权限，他们的区别和联系，大家要弄清楚管理初始化参数 ? 管理初始化参数(调优的一个重要知识点，凭什么可以对数据库进行调优呢?是因为它可以对数据库的一些参数进行修改修正) 初始化参数用于设置实例或是数据库的特征。oracle9i提供了200多个初始化参数，并且每个初始化参数都有默认值。 ? 显示初始化参数 (1) show parameter命令 ? 如何修改参数需要说明的如果你希望修改这些初始化的参数，可以到文件D:\oracle\admin\myoral\pfile\init.ora文件中去修改比如要修改实例的名字数据库(表)的逻辑备份与恢复介绍 ? 介绍逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程，逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库的过程。物理备份即可在数据库open的状态下进行也可在关闭数据库后进行，但是逻辑备份和恢复只能在open的状态下进行。看图： ? 导出导出具体的分为：导出表，导出方案，导出数据库三种方式。导出使用exp命令来完成的，该命令常用的选项有： userid：用于指定执行导出操作的用户名，口令，连接字符串 tables：用于指定执行导出操作的表 owner：用于指定执行导出操作的方案 full=y：用于指定执行导出操作的数据库 inctype：用于指定执行导出操作的增量类型 rows：用于指定执行导出操作是否要导出表中的数据 file：用于指定导出文件名

　　Oracle 笔记

　　? 导出表 1.导出自己的表 exp userid=scott/tiger@myoral tables=(emp,dept) file=d:\e1.dmp 2.导出其它方案的表如果用户要导出其它方案的表，则需要dba的权限或是exp_full_database的权限，比如system就可以导出scott的表 E:\oracle\ora92\bin>exp userid=system/manager@myoral tables=(scott.emp) file=d:\e2.emp 特别说明：在导入和导出的时候，要到oracle目录的bin目录下。 3. 导出表的结构 exp userid=scott/tiger@accp tables=(emp) file=d:\e3.dmp rows=n 4. 使用直接导出方式 exp userid=scott/tiger@accp tables=(emp) file=d:\e4.dmp direct=y 这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法。这时需要数据库的字符集要与客户端字符集完全一致，否则会报错... ? 导出方案导出方案是指使用export工具导出一个方案或是多个方案中的所有对象(表，索引，约束...)和数据。并存放到文件中。 1. 导出自己的方案 exp userid=scott/tiger@myorcl owner=scott file=d:\scott.dmp 2. 导出其它方案如果用户要导出其它方案，则需要dba的权限或是exp_full_database的权限，比如system用户就可以导出任何方案 exp userid=system/manager@myorcl owner=(system,scott) file=d:\system.dmp ? 导出数据库导出数据库是指利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限或者是exp_full_database权限增量备份(好处是第一次备份后，第二次备份就快很多了) exp userid=system/manager@myorcl full=y inctype=complete file=d:\all.dmp 导入 ? 介绍导入就是使用工具import将文件中的对象和数据导入到数据库中，但是导入要使用的文件必须是export所导出的文件。与导出相似，导入也分为导入表，导入方案，导入数据库三种方式。 imp常用的选项有 userid：用于指定执行导入操作的用户名，口令，连接字符串 tables：用于指定执行导入操作的表 formuser：用于指定源用户 touser：用于指定目标用户 file：用于指定导入文件名 full=y：用于指定执行导入整个文件 inctype：用于指定执行导入操作的增量类型 rows：指定是否要导入表行(数据) ignore：如果表存在，则只导入数据 ? 导入表 1. 导入自己的表 imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp 2. 导入表到其它用户要求该用户具有dba的权限，或是imp_full_database imp userid=system/tiger@myorcl tables=(emp) file=d:\xx.dmp touser=scott 3. 导入表的结构

　　Oracle 笔记

　　只导入表的结构而不导入数据 imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp rows=n 4. 导入数据如果对象(如比表)已经存在可以只导入表的数据 imp userid=scott/tiger@myorcl tables=(emp) file=d:\xx.dmp ignore=y ? 导入方案导入方案是指使用import工具将文件中的对象和数据导入到一个或是多个方案中。如果要导入其它方案，要求该用户具有dba的权限，或者imp_full_database 1. 导入自身的方案 imp userid=scott/tiger file=d:\xxx.dmp 2. 导入其它方案要求该用户具有dba的权限 imp userid=system/manager file=d:\xxx.dmp fromuser=system touser=scott ? 导入数据库在默认情况下，当导入数据库时，会导入所有对象结构和数据，案例如下： imp userid=system/manager full=y file=d:\xxx.dmp

　　11.数据字典和动态性能视图介绍：数据字典是什么数据字典是oracle数据库中最重要的组成部分，它提供了数据库的一些系统信息。动态性能视图记载了例程启动后的相关信息。 ? 数据字典数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户。用户只能在数据字典上执行查询操作(select语句)，而其维护和修改是由系统自动完成的。这里我们谈谈数据字典的组成：数据字典包括数据字典基表和数据字典视图，其中基表存储数据库的基本信息，普通用户不能直接访问数据字典的基表。数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息。数据字典视图主要包括user_xxx，all_xxx，dba_xxx三种类型。 ? user_tables; 用于显示当前用户所拥有的所有表，它只返回用户所对应方案的所有表比如：select table_name from user_tables; ? all_tables; 用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的其它方案的表：比如：select table_name from all_tables; ? dba_tables; 它会显示所有方案拥有的数据库表。但是查询这种数据库字典视图，要求用户必须是dba角色或是有select any table系统权限。例如：当用system用户查询数据字典视图dba_tables时，会返回system，sys，scott...方案所对应的数据库表。 ? 用户名，权限，角色在建立用户时，oracle会把用户的信息存放到数据字典中，当给用户授予权限或是角色时，oracle会将权限和角色的信息存放到数据字典。通过查询dba_users可以显示所有数据库用户的详细信息; 通过查询数据字典视图dba_sys_privs，可以显示用户所具有的系统权限; 通过查询数据字典视图dba_tab_privs，可以显示用户具有的对象权限; 通过查询数据字典dba_col_privs可以显示用户具有的列权限; 通过查询数据库字典视图dba_role_privs可以显示用户所具有的角色。

　　Oracle 笔记

　　这里给大家再讲讲角色和权限的关系。例如：要查看scott具有的角色，可查询dba_role_privs; SQL> select * from dba_role_privs where grantee='SCOTT'; //查询orale中所有的系统权限，一般是dba select * from system_privilege_map order by name; //查询oracle中所有对象权限，一般是dba select distinct privilege from dba_tab_privs; //查询oracle中所有的角色，一般是dba select * from dba_roles; //查询数据库的表空间 select tablespace_name from dba_tablespaces; 问题1：如何查询一个角色包括的权限? a.一个角色包含的系统权限 select * from dba_sys_privs where grantee='角色名' 另外也可以这样查看： select * from role_sys_privs where role='角色名' b.一个角色包含的对象权限 select * from dba_tab_privs where grantee='角色名' 问题2：oracle究竟有多少种角色? SQL> select * from dba_roles; 问题3：如何查看某个用户，具有什么样的角色? select * from dba_role_privs where grantee='用户名' ? 显示当前用户可以访问的所有数据字典视图。 select * from dict where comments like '%grant%'; ? 显示当前数据库的全称 select * from global_name; ? 其它说明数据字典记录有oracle数据库的所有系统信息。通过查询数据字典可以取得以下系统信息：比如 1.对象定义情况 2.对象占用空间大小 3.列信息 4.约束信息 ... 但是因为这些个信息，可以通过pl/sql developer工具查询得到，所以这里我就飘过。 ? 动态性能视图动态性能视图用于记录当前例程的活动信息，当启动oracle server时，系统会建立动态性能视图;当停止oracle server时，系统会删除动态性能视图。oracle的所有动态性能视图都是以v_$开始的，并且oracle为每个动态性能视图都提供了相应的同义词，并且其同义词是以V$开始的，例如v_$datafile的同义词为v$datafile;动态性能视图的所有者为sys，一般情况下，由dba或是特权用户来查询动态性能视图。因为这个在实际中用的较少，所以飞过。

　　12.数据库管理 -- 管理表空间和数据文件 ? 介绍表空间是数据库的逻辑组成部分。从物理上讲，数据库数据存放在数据文件中;从逻辑上讲，数据库则是存放在表空间中，表空间由一个或多个数据文件组成。

　　Oracle 笔记

　　数据库的逻辑结构 ? 介绍 oracle中逻辑结构包括表空间、段、区和块。说明一下数据库由表空间构成，而表空间又是由段构成，而段又是由区构成，而区又是由oracle块构成的这样的一种结构，可以提高数据库的效率。为了让大家明白，我们画图说明逻辑关系：看图：表空间 ? 介绍表空间用于从逻辑上组织数据库的数据。数据库逻辑上是由一个或是多个表空间组成的。通过表空间可以达到以下作用： 1. 控制数据库占用的磁盘空间 2. dba可以将不同数据类型部署到不同的位置，这样有利于提高i/o性能，同时利于备份和恢复等管理操作。 ? 建立表空间建立表空间是使用crate tablespace命令完成的，需要注意的是，一般情况下，建立表空间是特权用户或是dba来执行的，如果用其它用户来创建表空间，则用户必须要具有create tablespace的系统权限。 ? 建立数据表空间在建立数据库后，为便于管理表，最好建立自己的表空间 create tablespace data01 datafile 'd:\test\dada01.dbf' size 20m uniform size 128k; 说明：执行完上述命令后，会建立名称为data01的表空间，并为该表空间建立名称为data01.dbf的数据文件，区的大小为128k ? 使用数据表空间 create table mypart(deptno number(4), dname varchar2(14), loc varchar2(13)) tablespace data01; ? 改变表空间的状态当建立表空间时，表空间处于联机的(online)状态，此时该表空间是可以访问的，并且该表空间是可以读写的，即可以查询该表空间的数据，而且还可以在表空间执行各种语句。但是在进行系统维护或是数据维护时，可能需要改变表空间的状态。一般情况下，由特权用户或是dba来操作。 1. 使表空间脱机 alter tablespace 表空间名 offline; 2. 使表空间联机 alter tablespace 表空间名 online; 3. 只读表空间当建立表空间时，表空间可以读写，如果不希望在该表空间上执行update，delete，insert操作，那么可以将表空间修改为只读 alter tablespace 表空间名 read only; (修改为可写是 alter tablespace 表空间名 read write;) ? 改变表空间的状态我们给大家举一个实例，说明只读特性： 1. 知道表空间名，显示该表空间包括的所有表 select * from all_tables where tablespace_name=’表空间名’; 2. 知道表名，查看该表属于那个表空间 select tablespace_name, table_name from user_tables where table_name=’emp’; 通过2.我们可以知道scott.emp是在system这个表空间上，现在我们可以将system改为只读的但是我们不会成功，因为system是系统表空间，如果是普通表空间，那么我们就可以将其设为只读的，给大家做一个演示，可以加强理解。 3. 4. 使表空间可读写 alter tablespace 表空间名 read write; ? 删除表空间

　　Oracle 笔记

　　一般情况下，由特权用户或是dba来操作，如果是其它用户操作，那么要求用户具有drop tablespace系统权限。 drop tablespace ‘表空间’ including contents and datafiles; 说明：including contents表示删除表空间时，删除该空间的所有数据库对象，而datafiles表示将数据库文件也删除。 ? 扩展表空间表空间是由数据文件组成的，表空间的大小实际上就是数据文件相加后的大小。那么我们可以想象，假定表employee存放到data01表空间上，初始大小就是2M，当数据满2M空间后，如果在向employee表插入数据，这样就会显示空间不足的错误。案例说明： 1. 建立一个表空间 sp01 2. 在该表空间上建立一个普通表 mydment 其结构和dept一样 3. 向该表中加入数据 insert into mydment select * from dept; 4. 当一定时候就会出现无法扩展的问题，怎么办? 5. 就扩展该表空间，为其增加更多的存储空间。有三种方法： 1. 增加数据文件 SQL> alter tablespace sp01 add datafile ‘d:\test\sp01.dbf’ size 20m; 2. 增加数据文件的大小 SQL> alter tablespace 表空间名 ‘d:\test\sp01.dbf’ resize 20m; 这里需要注意的是数据文件的大小不要超过500m。 3. 设置文件的自动增长。 SQL> alter tablespace 表空间名 ‘d:\test\sp01.dbf’ autoextend on next 10m maxsize 500m; ? 移动数据文件有时，如果你的数据文件所在的磁盘损坏时，该数据文件将不能再使用，为了能够重新使用，需要将这些文件的副本移动到其它的磁盘，然后恢复。下面以移动数据文件sp01.dbf为例来说明： 1. 确定数据文件所在的表空间 select tablespace_name from dba_data_files where file_name=’d:\test\sp01.dbf’; 2. 使表空间脱机确保数据文件的一致性，将表空间转变为offline的状态。 alter tablespace sp01(表空间名) offline; 3. 使用命令移动数据文件到指定的目标位置 host move d:\test\sp01.dbf c:\test\sp01.dbf 4. 执行alter tablespace命令在物理上移动了数据后，还必须执行alter tablespace命令对数据库文件进行逻辑修改： alter tablespace sp01 rename datafile ‘d:\test\sp01.dbf’ to ‘c:\test\sp01.dbf’; 5. 使得表空间联机在移动了数据文件后，为了使用户可以访问该表空间，必须将其转变为online状态。 alter tablespace sp01(表空间名) online; ? 显示表空间信息查询数据字典视图dba_tablespaces，显示表空间的信息： select tablespace_name from dba_tablespaces; ? 显示表空间所包含的数据文件查询数据字典视图dba_data_files,可显示表空间所包含的数据文件，如下： select file_name, bytes from dba_data_files where tablespce_name=’表空间’; ? 表空间小结 1. 了解表空间和数据文件的作用

　　Oracle 笔记

　　2. 掌握常用表空间，undo表空间和临时表空间的建立方法 3. 了解表空间的各个状态(online, offline, read write, read only)的作用，及如何改变表空间的状态的方法。 4. 了解移动数据文件的原因，及使用alter tablespace 和alter datatable命令移动数据文件的方法。 ? 其它表空间除了最常用的数据表空间外，还有其它类型表空间： 1. 索引表空间 2. undo表空间 3. 临时表空间 4. 非标准块的表空间这几种表空间，大家伙可以自己参考书籍研究，这里我就不讲。 ? 其它说明关于表空间的组成部分段/区/块，我们在后面给大家讲解。

　　13.约束玩转oracle实战教程(第五天) 期望目标 1.掌握维护oracle数据完整性的技巧 2.理解索引概念，会建立索引 3.管理oracle的权限和角色维护数据的完整性 ? 介绍数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则，在oracle中，数据完整性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。约束 ? 约束约束用于确保数据库数据满足特定的商业规则。在oracle中，约束包括：not null、 unique， primary key， foreign key，和check五种。使用 ? not null(非空) 如果在列上定义了not null，那么当插入数据时，必须为列提供数据。 ? unique(唯一) 当定义了唯一约束后，该列值是不能重复的，但是可以为null。 ? primary key(主键) 用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。 ? foreign key(外键) 用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。 ? check 用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示出错。 ? 商店售货系统表设计案例现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：商品goods(商品号goodsId，商品名 goodsName，单价 unitprice，商品类别category，供应商provider); 客户customer(客户号customerId，姓名name，住在address，电邮email，性别sex，身份证cardId);

　　Oracle 笔记

　　购买purchase(客户号customerId，商品号goodsId，购买数量nums); 请用SQL语言完成下列功能： 1. 建表，在定义中要求声明： (1). 每个表的主外键; (2). 客户的姓名不能为空值; (3). 单价必须大于0，购买数量必须在1到30之间; (4). 电邮不能够重复; (5). 客户的性别必须是男或者女，默认是男; SQL> create table goods(goodsId char(8) primary key, --主键 goodsName varchar2(30)， unitprice number(10,2) check(unitprice>0), category varchar2(8), provider varchar2(30) ); SQL> create table customer( customerId char(8) primary key, --主键 name varchar2(50) not null, --不为空 address varchar2(50), email varchar2(50) unique, sex char(2) default '男' check(sex in ('男'，'女'))， -- 一个char能存半个汉字，两位char能存一个汉字 cardId char(18) ); SQL> create table purchase( customerId char(8) references customer(customerId), goodsId char(8) references goods(goodsId), nums number(10) check (nums between 1 and 30) ); 表是默认建在SYSTEM表空间的维护 ? 商店售货系统表设计案例(2) 如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束。但是要注意：增加not null约束时，需要使用modify选项，而增加其它四种约束使用add选项。 1. 增加商品名也不能为空 SQL> alter table goods modify goodsName not null; 2. 增加身份证也不能重复 SQL> alter table customer add constraint xxxxxx unique(cardId); 3. 增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’; SQL> alter table customer add constraint yyyyyy check (address in (’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’)); ? 删除约束当不再需要某个约束时，可以删除。 alter table 表名 drop constraint 约束名称; 特别说明一下：在删除主键约束的时候，可能有错误，比如： alter table 表名 drop primary key; 这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项如像：

　　Oracle 笔记

　　alter table 表名 drop primary key cascade; ? 显示约束信息 1.显示约束信息通过查询数据字典视图user_constraints，可以显示当前用户所有的约束的信息。 select constraint_name, constraint_type, status, validated from user_constraints where table_name = '表名'; 2.显示约束列通过查询数据字典视图user_cons_columns，可以显示约束所对应的表列信息。 select column_name, position from user_cons_columns where constraint_name = '约束名'; 3.当然也有更容易的方法，直接用pl/sql developer查看即可。简单演示一下下... 表级定义列级定义 ? 列级定义列级定义是在定义列的同时定义约束。如果在department表定义主键约束 create table department4(dept_id number(12) constraint pk_department primary key, name varchar2(12), loc varchar2(12)); ? 表级定义表级定义是指在定义了所有列后，再定义约束。这里需要注意： not null约束只能在列级上定义。以在建立employee2表时定义主键约束和外键约束为例： create table employee2(emp_id number(4), name varchar2(15), dept_id number(2), constraint pk_employee primary key (emp_id), constraint fk_department foreign key (dept_id) references department4(dept_id));

　　14.Oracle索引、权限

　　管理索引-原理介绍 ? 介绍索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引有很多种我们主要介绍常用的几种：为什么添加了索引后，会加快查询速度呢? 创建索引 ? 单列索引单列索引是基于单个列所建立的索引，比如： create index 索引名 on 表名(列名); ? 复合索引复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如： create index emp_idx1 on emp (ename, job); create index emp_idx1 on emp (job, ename); 使用原则 ? 使用原则 1. 在大表上建立索引才有意义 2. 在where子句或是连接条件上经常引用的列上建立索引 3. 索引的层次不要超过4层这里能不能给学生演示这个效果呢? 如何构建一个大表呢?

　　Oracle 笔记

　　索引的缺点 ? 索引缺点分析索引有一些先天不足： 1. 建立索引，系统要占用大约为表1.2倍的硬盘和内存空间来保存索引。 2. 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。比如在如下字段建立索引应该是不恰当的： 1. 很少或从不引用的字段; 2. 逻辑型的字段，如男或女(是或否)等。综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标。其它索引 ? 介绍按照数据存储方式，可以分为B*树、反向索引、位图索引; 按照索引列的个数分类，可以分为单列索引、复合索引; 按照索引列值的唯一性，可以分为唯一索引和非唯一索引。此外还有函数索引，全局索引，分区索引... 对于索引我还要说：在不同的情况，我们会在不同的列上建立索引，甚至建立不同种类的索引，请记住，技术是死的，人是活的。比如： B*树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、不同值相对固定的列上。显示索引信息 ? 显示表的所有索引在同一张表上可以有多个索引，通过查询数据字典视图dba_indexs和user_indexs，可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息： select index_name, index_type from user_indexes where table_name = '表名'; ? 显示索引列通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息 select table_name, column_name from user_ind_columns where index_name = 'IND_ENAME'; ? 你也可以通过pl/sql developer工具查看索引信息管理权限和角色介绍 ? 介绍这一部分我们主要看看oracle中如何管理权限和角色，权限和角色的区别在那里。当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据库操作，则必须为其授予系统的权限;如果用户要访问其它方案的对象，则必须为其授予对象的权限。为了简化权限的管理，可以使用角色。这里我们会详细的介绍。看图：权限 ? 权限权限是指执行特定类型sql命令或是访问其它方案对象的权利，包括系统权限和对象权限两种。系统权限 ? 系统权限介绍系统权限是指执行特定类型sql命令的权利。它用于控制用户可以执行的一个或是一组数据库操作。比如当用户具有create table权限时，可以在其方案中建表，当用户具有create any table权限时，可以在任何方案中建表。oracle提供了100多种系统权限。

　　Oracle 笔记

　　常用的有： create session 连接数据库 create table 建表 create view 建视图 create public synonym 建同义词 create procedure 建过程、函数、包 create trigger 建触发器 create cluster 建簇 ? 显示系统权限 oracle提供了100多种系统权限，而且oracle的版本越高，提供的系统权限就越多，我们可以查询数据字典视图system_privilege_map，可以显示所有系统权限。 select * from system_privilege_map order by name; ? 授予系统权限一般情况，授予系统权限是由dba完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限。在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。为了让大家快速理解，我们举例说明： 1.创建两个用户ken，tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。 create user ken identfied by ken; 2 给用户ken授权 1). grant create session, create table to ken with admin option; 2). grant create view to ken; 3 给用户tom授权我们可以通过ken给tom授权，因为with admin option是加上的。当然也可以通过dba给tom授权，我们就用ken给tom授权： 1. grant create session, create table to tom; 2. grant create view to ken; --ok吗?不ok ? 回收系统权限一般情况下，回收系统权限是dba来完成的，如果其它的用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项(with admin option)。回收系统权限使用revoke来完成。当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联收回的问题?[不是级联回收!] system --------->ken ---------->tom (create session)(create session)( create session) 用system执行如下操作： revoke create session from ken; --请思考tom还能登录吗? 答案：能，可以登录对象权限 ? 对象权限介绍指访问其它方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问别的方案的对象，则必须具有对象的权限。比如smith用户要访问scott.emp表(scott：方案，emp：表) 常用的有： alter 修改 delete 删除 select 查询 insert 添加 update 修改 index 索引 references 引用 execute 执行 ? 显示对象权限通过数据字段视图可以显示用户或是角色所具有的对象权限。视图为dba_tab_privs SQL> conn system/manager; SQL> select distinct privilege from dba_tab_privs; SQL> select grantor, owner, table_name, privilege from dba_tab_privs where grantee = 'BLAKE';

　　Oracle 笔记

　　1.授予对象权限在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操作，则需要用户具有相应的(with grant option)权限，从oracle9i开始，dba用户(sys，system)可以将任何对象上的对象权限授予其它用户。授予对象权限是用grant命令来完成的。对象权限可以授予用户，角色，和public。在授予权限时，如果带有with grant option选项，则可以将该权限转授给其它用户。但是要注意with grant option选项不能被授予角色。 1.monkey用户要操作scott.emp表，则必须授予相应的对象权限 1). 希望monkey可以查询scott.emp表的数据，怎样操作? grant select on emp to monkey; 2). 希望monkey可以修改scott.emp的表数据，怎样操作? grant update on emp to monkey; 3). 希望monkey可以删除scott.emp的表数据，怎样操作? grant delete on emp to monkey; 4). 有没有更加简单的方法，一次把所有权限赋给monkey? grant all on emp to monkey; 2.能否对monkey访问权限更加精细控制。(授予列权限) 1). 希望monkey只可以修改scott.emp的表的sal字段，怎样操作? grant update on emp(sal) to monkey 2).希望monkey只可以查询scott.emp的表的ename，sal数据，怎样操作? grant select on emp(ename,sal) to monkey ... 3.授予alter权限如果black用户要修改scott.emp表的结构，则必须授予alter对象权限 SQL> conn scott/tiger SQL> grant alter on emp to blake; 当然也可以用system，sys来完成这件事。 4.授予execute权限如果用户想要执行其它方案的包/过程/函数，则须有execute权限。比如为了让ken可以执行包dbms_transaction，可以授予execute权限。 SQL> conn system/manager SQL> grant execute on dbms_transaction to ken; 5.授予index权限如果想在别的方案的表上建立索引，则必须具有index对象权限。如果为了让black可以在scott.emp表上建立索引，就给其index的对象权限 SQL> conn scott/tiger SQL> grant index on scott.emp to blake; 6.使用with grant option选项该选项用于转授对象权限。但是该选项只能被授予用户，而不能授予角色 SQL> conn scott/tiger; SQL> grant select on emp to blake with grant option; SQL> conn black/shunping SQL> grant select on scott.emp to jones; ? 回收对象权限在oracle9i中，收回对象的权限可以由对象的所有者来完成，也可以用dba用户(sys，system)来完成。这里要说明的是：收回对象权限后，用户就不能执行相应的sql命令，但是要注意的是对象的权限是否会被级联收回?【级

　　Oracle 笔记

　　联回收】如：scott------------->blake-------------->jones select on emp select on emp select on emp SQL> conn scott/tiger@accp SQL> revoke select on emp from blake 请大家思考，jones能否查询scott.emp表数据。答案：查不了了(和系统权限不一样，刚好相反)

　　15.角色

　　? 介绍角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理，假定有用户a，b，c为了让他们都拥有权限 1. 连接数据库 2. 在scott.emp表上select，insert，update。如果采用直接授权操作，则需要进行12次授权。因为要进行12次授权操作，所以比较麻烦喔!怎么办? 如果我们采用角色就可以简化：首先将creat session，select on scott.emp，insert on scott.emp， update on scott.emp授予角色，然后将该角色授予a，b，c用户，这样就可以三次授权搞定。角色分为预定义和自定义角色两类： ? 预定义角色预定义角色是指oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色connect，resource，dba 1.connect角色 connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和resource角色就够了，那么connect角色具有哪些系统权限呢? alter session create cluster create database link create session create table create view create sequence 2.resource角色 resource角色具有应用开发人员所需要的其它权限，比如建立存储过程，触发器等。这里需要注意的是resource角色隐含了unlimited tablespace系统权限。 resource角色包含以下系统权限： create cluster create indextype create table create sequence create type create procedure create trigger 3.dba角色

　　Oracle 笔记

　　dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system，它们可以将任何系统权限授予其他用户。但是要注意的是dba角色不具备sysdba和sysoper的特权(启动和关闭数据库)。 ? 自定义角色顾名思义就是自己定义的角色，根据自己的需要来定义。一般是dba来建立，如果用别的用户来建立，则需要具有create role的系统权限。在建立角色时可以指定验证方式(不验证，数据库验证等)。 1.建立角色(不验证) 如果角色是公用的角色，可以采用不验证的方式建立角色。 create role 角色名 not identified; 2.建立角色(数据库验证) 采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令。在建立这种角色时，需要为其提供口令。 create role 角色名 identified by 密码; 角色授权当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的系统权限和对象权限。 1.给角色授权给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。 SQL> conn system/manager; SQL> grant create session to 角色名 with admin option SQL> conn scott/tiger@myoral; SQL> grant select on scott.emp to 角色名; SQL> grant insert, update, delete on scott.emp to 角色名; 通过上面的步骤，就给角色授权了。 2.分配角色给某个用户一般分配角色是由dba来完成的，如果要以其它用户身份分配角色，则要求用户必须具有grant any role的系统权限。 SQL> conn system/manager; SQL> grant 角色名 to blake with admin option; 因为我给了with admin option选项，所以，blake可以把system分配给它的角色分配给别的用户。 ? 删除角色使用drop role，一般是dba来执行，如果其它用户则要求该用户具有drop any role系统权限。 SQL> conn system/manager; SQL> drop role 角色名; 问题：如果角色被删除，那么被授予角色的用户是否还具有之前角色里的权限? 答案：不具有了 ? 显示角色信息 1.显示所有角色 SQL> select * from dba_roles; 2.显示角色具有的系统权限 SQL> select privilege, admin_option from role_sys_privs where role='角色名'; 3.显示角色具有的对象权限通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。 4.显示用户具有的角色，及默认角色当以用户的身份连接到数据库时，oracle会自动的激活默认的角色，通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的角色

　　Oracle 笔记

　　SQL> select granted_role, default_role from dba_role_privs where grantee = ‘用户名’; ? 精细访问控制精细访问控制是指用户可以使用函数，策略实现更加细微的安全访问控制。如果使用精细访问控制，则当在客户端发出sql语句(select，insert，update，delete)时，oracle会自动在sql语句后追加谓词(where子句)，并执行新的sql语句，通过这样的控制，可以使得不同的数据库用户在访问相同表时，返回不同的数据信息，如：用户 scott blake jones 策略 emp_access 数据库表 emp 如上图所示，通过策略emp_access，用户scott，black，jones在执行相同的sql语句时，可以返回不同的结果。例如：当执行select ename from emp; 时，根据实际情况可以返回不同的结果。

　　16.PL/SQL 块的结构和实例韩顺平.玩转oralce第24讲.plsql编程(1) 玩转orcle实战教程(第六天) 内容介绍 1.上节回顾 2.pl/sql的介绍 √ 3.pl/sql的基础 √ 期望目标 1.理解oracle的pl/sql概念 2.掌握pl/sql编程技术(包括编写过程、函数、触发器...) pl/sql的介绍 pl/sql是什么 pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误，这样使得它的功能变得更加强大。为什么学pl/sql ? 学习必要性 1.提高应用程序的运行性能 2.模块化的设计思想【分页的过程，订单的过程，转账的过程。。】 3.减少网络传输量 4.提高安全性(sql会包括表名，有时还可能有密码，传输的时候会泄露。PL/SQL就不会) 为什么PL/SQL会快呢?看图：不好的地方：移植性不好(换数据库就用不了)，用什么编写pl/sql ? sqlplus开发工具 sqlplus是oracle公司提供的一个工具，这个因为我们在以前介绍过的：举一个简单的案例：编写一个存储过程，该过程可以向某表中添加记录。 1.创建一个简单的表

　　create table mytest(name varchar2(30),passwd varchar2(30));

　　2.创建过程

　　create or replace procedure sp_pro1 is

　　Oracle 笔记

　　begin--执行部分

　　insert into mytest values('韩顺平','m1234');

　　end;

　　/ replace:表示如果有sp_pro1，就替换如何查看错误信息：show error; 如何调用该过程： 1)exec 过程名(参数值1，参数值2...); 2)call 过程名(参数值1，参数值2...); ? pl/sql developer开发工具 pl/sql developer是用于开发pl/sql块的集成开发环境(ide)，它是一个独立的产品，而不是oracle的一个附带品。举一个简单案例：编写一个存储过程，该过程可以删除某表记录。

　　create or replace procedure sp_pro2 is

　　begin--执行部分

　　delete from mytest where name='韩顺平';

　　end; pl/sql基础 pl/sql介绍 ? 介绍开发人员使用pl/sql编写应用模块时，不仅需要掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql编程可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块。比如：分页存储过程模块、订单处理存储过程模块、转账存储过程模块。而且如果使用pl/sql编程，我们可以轻松地完成非常复杂的查询要求。 pl/sql可以做什么 ? 简单分类 |————过程(存储过程) | |————函数块(编程)—————| |————触发器 | |————包编写规范 ? 编写规范 1.注释单行注释 --

　　select * from emp where empno=7788; --取得员工信息多行注释 /*...*/来划分 2.标志符号的命名规范 1).当定义变量时，建议用v_作为前缀v_sal 2).当定义常量时，建议用c_作为前缀c_rate 3).当定义游标时，建议用_cursor作为后缀emp_cursor 4).当定义例外时，建议用e_作为前缀e_error pl/sql块介绍 ? 介绍

　　Oracle 笔记

　　块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。 ? 块结构示意图 pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分。如下所示： declare /*定义部分——定义常量、变量、游标、例外、复杂数据类型*/ begin /*执行部分——要执行的pl/sql语句和sql语句*/ exception /*例外处理部分——处理运行的各种错误*/ end; 定义部分是从declare开始的，该部分是可选的; 执行部分是从begin开始的，该部分是必须的; 例外处理部分是从exception开始的，该部分是可选的。可以和java编程结构做一个简单的比较。 pl/sql块的实例(1) ? 实例1-只包括执行部分的pl/sql块

　　set serveroutput on --打开输出选项

　　begin

　　dbms_output.put_line('hello');

　　end;

　　相关说明： dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。 pl/sql块的实例(2) ? 实例2-包含定义部分和执行部分的pl/sql块

　　declare

　　v_ename varchar2(5); --定义字符串变量

　　begin

　　select ename into v_ename from emp where empno=&aa;

　　dbms_output.put_line('雇员名：'||v_ename);

　　end;

　　/ 如果要把薪水也显示出来，那么执行部分就应该这么写：

　　select ename,sal into v_ename,v_sal from emp where empno=&aa;

　　相关说明： & 表示要接收从控制台输入的变量。 pl/sql块的实例(3) ? 实例3-包含定义部分，执行部分和例外处理部分为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要。 1.比如在实例2中，如果输入了不存在的雇员号，应当做例外处理。 2.有时出现异常，希望用另外的逻辑处理，[网示] 我们看看如何完成1的要求。相关说明： oracle事先预定义了一些例外，no_data_found就是找不到数据的例外。

　　Oracle 笔记

　　declare

　　--定义变量

　　v_ename varchar2(5);

　　v_sal number(7,2);

　　begin

　　--执行部分

　　select ename,sal into v_ename,v_sal from emp where empno=&aa;

　　--在控制台显示用户名

　　dbms_output.put_line('用户名是：'||v_ename||' 工资：'||v_sal);

　　--异常处理

　　exception

　　when no_data_found then

　　dbms_output.put_line('朋友，你的编号输入有误!');

　　end;

　　17.pl/sql分类 -- 过程，函数，包，触发器

　　? 过程过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分;通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。实例如下： 1.请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资 2.如何调用过程有两种方法; exec call 3.如何在java程序中调用一个存储过程问题：如何使用过程返回值? 特别说明：对于过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。 create procedure sp_pro3(spName varchar2, newSal number) is --不要写成number(3,2),表明类型就可以了，不需要大小。就好像Java写方法时的参数一样

　　begin

　　--执行部分，根据用户名去修改工资

　　update emp set sal=newSal where ename=spName;

　　end;

　　/ java程序中调用一个存储过程 //演示java程序去调用oracle的存储过程案例

　　import java.sql.*;

　　public class TestOraclePro{

　　public static void main(String[] args){

　　try{

　　//1.加载驱动

　　Class.forName("oracle.jdbc.driver.OracleDriver");

　　//2.得到连接

　　Oracle 笔记

　　Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

　　//3.创建CallableStatement

　　CallableStatement cs = ct.prepareCall("{call sp_pro3(?,?)}");

　　//4.给?赋值

　　cs.setString(1,"SMITH");

　　cs.setInt(2,10);

　　//5.执行

　　cs.execute();

　　//关闭

　　cs.close();

　　ct.close();

　　} catch(Exception e){

　　e.printStackTrace();

　　}

　　} ? 函数函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数，实际案例：

　　--输入雇员的姓名，返回该雇员的年薪

　　create function annual_incomec(name varchar2)

　　return number is

　　annual_salazy number(7,2);

　　begin

　　--执行部分

　　select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;

　　return annual_salazy;

　　end;

　　如果函数创建过程有编译错误，可以使用show error;命令显示错误在sqlplus中调用函数

　　SQL> var income number

　　SQL> call annual_incomec('scott') into: income;

　　SQL> print income 同样我们可以在java程序中调用该函数 select annual_income('SCOTT') from dual; 这样可以通过rs.getInt(l)得到返回的结果。 ? 包包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。 1.我们可以使用create package命令来创建包。实例： --创建一个包sp_package --声明该包有一个过程update_sal --声明该包有一个函数annual_income

　　Oracle 笔记

　　create package sp_package is

　　procedure update_sal(name varchar2, newsal number);

　　function annual_income(name varchar2) return number;

　　end; 包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。 2.建立包体可以使用create package body命令 --给包sp_package实现包体

　　create or replace package body sp_package is

　　procedure update_sal(name varchar2, newsal number)

　　begin

　　update emp set sal = newsal where ename = name;

　　end;

　　function annual_income(name varchar2) return number is

　　annual_salary number;

　　begin

　　select sal * 12 + nvl(comm, 0) into annual_salary from emp

　　where ename = name;

　　return annual_salary;

　　end;

　　/ 3.如何调用包的过程或是函数当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。如： SQL> call sp_package.update_sal('SCOTT', 1500); 特别说明：包是pl/sql中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力呵呵。 ? 触发器触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。特别说明：我们会在后面详细为大家介绍触发器的使用，因为触发器是非常有用的，可维护数据库的安全和一致性。

　　18.定义并使用变量,复合类型定义并使用变量 ? 介绍在编写pl/sql程序时，可以定义变量和常量;在pl/sql程序中包括有： 1.标量类型(scalar) 2.复合类型(composite) 3.参照类型(reference) 4.lob(large object) ? 标量(scalar)——常用类型在编写pl/sql块时，如果要使用变量，需在定义部分定义变量。pl/sql中定义变量和常量的语法如下： identifier [constant] datatype [not null] [:=| default expr]

　　Oracle 笔记

　　identifier : 名称 constant ：指定常量。需要指定它的初始值，且其值是不能改变的 datatype ：数据类型 not null ：指定变量值不能为null := 给变量或是常量指定初始值 default 用于指定初始值 expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。 ? 标量定义的案例 1.定义一个变长字符串 v_ename varchar2(10); 2.定义一个小数,范围 -9999.99~9999.99 v_sal number(6,2); 3.定义一个小数并给一个初始值为5.4 :=是pl/sql的赋值号 v_sal2 number(6,2):=5.4; 4.定义一个日期类型的数据 v_hiredate date; 5.定义一个布尔变量，不能为空，初始值为false v_valid boolean not null default false; ? 标量(scalar)——使用标量在定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其它的编程语言，需要在等号前面加冒号(:=) 下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用，看看如何编写。

　　declare

　　c_tax_rate number(3,2):=0.03;

　　--用户名

　　v_ename varchar2(5);

　　v_sal number(7,2);

　　v_tax_sal number(7,2);

　　begin

　　--执行

　　select ename,sal into v_ename,v_sal from emp where empno=&no;

　　--计算所得税

　　v_tax_sal := v_sal*c_tax_rate;

　　--输出

　　dbms_output.put_line('姓名是：'||v_ename||'工资：'||v_sal||' 交税：'||v_tax_sal);

　　end;

　　/ ? 标量(scalar)——使用%type类型对于上面的pl/sql块有一个问题：就是如果员工的姓名超过了5个字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。我们看看这个怎么使用：标识符名表名.列名%type; 比如上例的v_ename，这样定义： v_ename emp.ename%type;

　　Oracle 笔记

　　? 复合变量(composite)——介绍用于存放多个值的变量。主要包括这几种： 1.pl/sql记录 2.pl/sql表 3.嵌套表 4.varray ? 复合类型——pl/sql记录类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：

　　declare

　　--定义一个pl/sql记录类型emp_record_type,类型包含3个数据name,salary,title。说白了，就是一个类型可以存放3个数据，主要是为了好管理

　　type emp_record_type is record(

　　name emp.ename%type,

　　salary emp.sal%type,

　　title emp.job%type);

　　--定义了一个sp_record变量，这个变量的类型是emp_record_type

　　sp_record emp_record_type;

　　begin

　　select ename, sal, job into sp_record from emp where empno = 7788;

　　dbms_output.put_line ('员工名:' || sp_record.name);

　　end; ? 复合类型-pl/sql表相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下：

　　declare

　　--定义了一个pl/sql表类型sp_table_type，该类型是用于存放emp.ename%type

　　--index by binary_integer 表示下标是整数

　　type sp_table_type is table of emp.ename%type

　　index by binary_integer;

　　--定义了一个sp_table变量，这个变量的类型是sp_table_type

　　sp_table sp_table_type;

　　begin

　　select ename into sp_table(-1) from emp where empno = 7788;

　　dbms_output.put_line('员工名:' || sp_table(-1));

　　end;

　　说明： sp_table_type 是pl/sql表类型 emp.ename%type 指定了表的元素的类型和长度 sp_table 为pl/sql表变量 sp_table(0) 则表示下标为0的元素注意：如果把select ename into sp_table(-1) from emp where empno = 7788;变成select ename into sp_table(-1) from emp;则运行时会出现错误，错误如下： ORA-01422:实际返回的行数超出请求的行数解决方法是：使用参照变量(这里不讲)

　　Oracle 笔记

　　? 复合变量——嵌套表(nested table) ? 复合变量——变长数组(varray) ? 参照变量——介绍参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。 ? 参照变量——ref cursor游标变量使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时(open时)需要指定select语句，这样一个游标就与一个select语句结合了。实例如下： 1.请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。 2.在1的基础上，如果某个员工的工资低于200元，就添加100元。 1.

　　declare

　　--定义游标sp_emp_cursor

　　type sp_emp_cursor is ref cursor;

　　--定义一个游标变量

　　test_cursor sp_emp_cursor;

　　--定义变量

　　v_ename emp.ename%type;

　　v_sal emp.sal%type;

　　begin

　　--执行

　　--把test_cursor和一个select结合

　　open test_cursor for select ename,sal from emp where deptno=&no;

　　--循环取出

　　loop

　　fetch test_cursor into v_ename,v_sal;

　　--判断是否test_cursor为空

　　exit when test_cursor%notfound;

　　dbms_output.put_line('名字：'||v_ename||' 工资：'||v_sal);

　　end loop;

　　end;

　　19.pl/sql的进阶--控制结构(分支，循环，控制)

　　玩转oracle实战教程(第七天) 内容介绍 1.上节回顾 2.pl/sql的进阶 √ 3.oracle的视图(具有安全性，和简化复杂查询的功能) √ 4.oracle的触发器 √ 期望目标 1.掌握pl/sql的高级用法(能缩写分页过程模块，下订单过程模块...) 2.会处理oracle常见的例外 3.会编写oracle各种触发器

　　Oracle 笔记

　　4.理解视图的概念并能灵活使用视图 pl/sql的进阶--控制结构 ? 介绍在任何计算机语言(c，java，pascal)都有各种控制语句(条件语句，循环结构，顺序控制结构...)在pl/sql中也存在这样的控制结构。在本部分学习完成后，希望大家达到： 1.使用各种if语句 2.使用循环语句 3.使用控制语句——goto和null; ? 条件分支语句 pl/sql中提供了三种条件分支语句if—then，if – then – else，if – then – elsif – then 这里我们可以和java语句进行一个比较 ? 简单的条件判断 if – then 问题：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。

　　create or replace procedure sp_pro6(spName varchar2) is

　　--定义

　　v_sal emp.sal%type;

　　begin

　　--执行

　　select sal into v_sal from emp where ename=spName;

　　--判断

　　if v_sal<2000 then

　　update emp set sal=sal+sal*10% where ename=spName;

　　end if;

　　end;

　　/ ? 二重条件分支 if – then – else 问题：编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0就在原来的基础上增加100;如果补助为0就把补助设为200;

　　create or replace procedure sp_pro6(spName varchar2) is

　　--定义

　　v_comm emp.comm%type;

　　begin

　　--执行

　　select comm into v_comm from emp where ename=spName;

　　--判断

　　if v_comm<>0 then

　　update emp set comm=comm+100 where ename=spName;

　　else

　　update emp set comm=comm+200 where ename=spName;

　　end if;

　　end;

　　/ ? 多重条件分支 if – then – elsif – then

　　Oracle 笔记

　　问题：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT就给他的工资增加1000，如果该雇员的职位是MANAGER就给他的工资增加500，其它职位的雇员工资增加200。

　　create or replace procedure sp_pro6(spNo number) is

　　--定义

　　v_job emp.job%type;

　　begin

　　--执行

　　select job into v_job from emp where empno=spNo;

　　if v_job='PRESIDENT' then

　　update emp set sal=sal+1000 where empno=spNo;

　　elsif v_job='MANAGER' then

　　update emp set sal=sal+500 where empno=spNo;

　　else

　　update emp set sal=sal+200 where empno=spNo;

　　end if;

　　end;

　　/ ? 循环语句 –loop 是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。案例：现有一张表users，表结构如下：用户id | 用户名 | 请编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加。

　　create or replace procedure sp_pro6(spName varchar2) is

　　--定义 :=表示赋值

　　v_num number:=1;

　　begin

　　loop

　　insert into users values(v_num,spName);

　　--判断是否要退出循环

　　exit when v_num=10;

　　--自增

　　v_num:=v_num+1;

　　end loop;

　　end;

　　/ ? 环语句 –while循环基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while...loop开始，以end loop结束。案例：现有一张表users，表结构如下：用户id 用户名问题：请编写一个过程，可以输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加。

　　create or replace procedure sp_pro6(spName varchar2) is

　　--定义 :=表示赋值

　　v_num number:=11;

　　Oracle 笔记

　　begin

　　while v_num<=20 loop

　　--执行

　　insert into users values(v_num,spName);

　　v_num:=v_num+1;

　　end loop;

　　end;

　　/ ? 循环语句 –for循环基本for循环的基本结构如下

　　begin

　　for i in reverse 1..10 loop

　　insert into users values (i, 'shunping');

　　end loop;

　　end;

　　我们可以看到控制变量i，在隐含中就在不停地增加。 ? 顺序控制语句 –goto，null 1.goto语句 goto语句用于跳转到特定符号去执行语句。注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句。基本语法如下 goto lable，其中lable是已经定义好的标号名，

　　declare

　　i int := 1;

　　begin

　　loop

　　dbms_output.put_line('输出i=' || i);

　　if i = 1{} then

　　goto end_loop;

　　end if;

　　i := i + 1;

　　end loop;

　　<<end_loop>>

　　dbms_output.put_line('循环结束');

　　end; 2.null null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。

　　declare

　　v_sal emp.sal%type;

　　v_ename emp.ename%type;

　　begin

　　select ename, sal into v_ename, v_sal from emp where empno = &no;

　　if v_sal < 3000 then

　　update emp set comm = sal * 0.1 where ename = v_ename;

　　else

　　null;

　　Oracle 笔记

　　end if;

　　end;

　　20.PL/SQL分页编写分页过程 ? 介绍分页是任何一个网站(bbs,网上商城，blog)都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。看图： ? 无返回值的存储过程古人云：欲速则不达，为了让大家伙比较容易接受分页过程编写，我还是从简单到复杂，循序渐进的给大家讲解。首先是掌握最简单的存储过程，无返回值的存储过程：案例：现有一张表book，表结构如下：看图：书号书名出版社请写一个过程，可以向book表添加书，要求通过java程序调用该过程。 --in:表示这是一个输入参数，默认为in --out:表示一个输出参数

　　create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is

　　begin

　　insert into book values(spBookId,spbookName,sppublishHouse);

　　end;

　　/ --在java中调用

　　//调用一个无返回值的过程

　　import java.sql.*;

　　public class Test2{

　　public static void main(String[] args){

　　try{

　　 //1.加载驱动

　　 Class.forName("oracle.jdbc.driver.OracleDriver");

　　 //2.得到连接

　　 Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

　　 //3.创建CallableStatement

　　 CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");

　　 //4.给?赋值

　　 cs.setInt(1,10);

　　 cs.setString(2,"笑傲江湖");

　　 cs.setString(3,"人民出版社");

　　 //5.执行

　　 cs.execute();

　　 } catch(Exception e){

　　 e.printStackTrace();

　　 } finally{

　　 //6.关闭各个打开的资源

　　 cs.close

　　 //Oracle 笔记

　　 ct.close();

　　 }

　　执行，记录被加进去了 ? 有返回值的存储过程(非列表) 再看如何处理有返回值的存储过程：案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名。案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

　　--有输入和输出的存储过程

　　create or replace procedure sp_pro8

　　(spno in number, spName out varchar2) is

　　begin

　　select ename into spName from emp where empno=spno;

　　end;

　　import java.sql.*;

　　public class Test2{

　　public static void main(String[] args){

　　try{

　　//1.加载驱动

　　Class.forName("oracle.jdbc.driver.OracleDriver");

　　//2.得到连接

　　Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

　　//3.创建CallableStatement

　　/*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");

　　//4.给?赋值

　　cs.setInt(1,10);

　　cs.setString(2,"笑傲江湖");

　　cs.setString(3,"人民出版社");*/

　　//看看如何调用有返回值的过程

　　//创建CallableStatement

　　/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");

　　//给第一个?赋值

　　cs.setInt(1,7788);

　　//给第二个?赋值

　　cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

　　//5.执行

　　cs.execute();

　　//取出返回值,要注意?的顺序

　　String name=cs.getString(2);

　　System.out.println("7788的名字"+name);

　　} catch(Exception e){

　　e.printStackTrace();

　　Oracle 笔记

　　} finally{

　　//6.关闭各个打开的资源

　　cs.close();

　　ct.close();

　　}

　　运行，成功得出结果。。案例扩张：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

　　--有输入和输出的存储过程

　　create or replace procedure sp_pro8

　　(spno in number, spName out varchar2,spSal out number,spJob out varchar2) is

　　begin

　　select ename,sal,job into spName,spSal,spJob from emp where empno=spno;

　　end;

　　import java.sql.*;

　　public class Test2{

　　public static void main(String[] args){

　　try{

　　//1.加载驱动

　　Class.forName("oracle.jdbc.driver.OracleDriver");

　　//2.得到连接

　　Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

　　//3.创建CallableStatement

　　/*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");

　　//4.给?赋值

　　cs.setInt(1,10);

　　cs.setString(2,"笑傲江湖");

　　cs.setString(3,"人民出版社");*/

　　//看看如何调用有返回值的过程

　　//创建CallableStatement

　　/*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}");

　　//给第一个?赋值

　　cs.setInt(1,7788);

　　//给第二个?赋值

　　cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

　　//给第三个?赋值

　　cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);

　　//给第四个?赋值

　　cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);

　　//5.执行

　　cs.execute();

　　//取出返回值,要注意?的顺序

　　Oracle 笔记

　　String name=cs.getString(2);

　　String job=cs.getString(4);

　　System.out.println("7788的名字"+name+" 工作："+job);

　　} catch(Exception e){

　　e.printStackTrace();

　　} finally{

　　//6.关闭各个打开的资源

　　cs.close();

　　ct.close();

　　}

　　运行，成功找出记录 ? 有返回值的存储过程(列表[结果集]) 案例：编写一个过程，输入部门号，返回该部门所有雇员信息。对该题分析如下：由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了。所以要分两部分：返回结果集的过程 1.建立一个包，在该包中，我定义类型test_cursor,是个游标。如下：

　　create or replace package testpackage as

　　TYPE test_cursor is ref cursor;

　　end testpackage; 2.建立存储过程。如下：

　　create or replace procedure sp_pro9(spNo in number,p_cursor out testpackage.test_cursor) is

　　begin

　　open p_cursor for

　　select * from emp where deptno = spNo;

　　end sp_pro9; 3.如何在java程序中调用该过程

　　import java.sql.*;

　　public class Test2{

　　public static void main(String[] args){

　　try{

　　//1.加载驱动

　　Class.forName("oracle.jdbc.driver.OracleDriver");

　　//2.得到连接

　　Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

　　//看看如何调用有返回值的过程

　　//3.创建CallableStatement

　　/*CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");

　　//4.给第?赋值

　　cs.setInt(1,10);

　　//给第二个?赋值

　　Oracle 笔记

　　cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

　　//5.执行

　　cs.execute();

　　//得到结果集

　　ResultSet rs=(ResultSet)cs.getObject(2);

　　while(rs.next()){

　　System.out.println(rs.getInt(1)+" "+rs.getString(2));

　　}

　　} catch(Exception e){

　　e.printStackTrace();

　　} finally{

　　//6.关闭各个打开的资源

　　cs.close();

　　ct.close();

　　}

　　运行，成功得出部门号是10的所有用户 ? 编写分页过程有了上面的基础，相信大家可以完成分页存储过程了。要求，请大家编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数，和返回的结果集。如果大家忘了oracle中如何分页，请参考第三天的内容。先自己完成，老师在后面给出答案，并讲解。 --oracle的分页

　　select t1.*, rownum rn from (select * from emp) t1 where rownum<=10;

　　--在分页时，大家可以把下面的sql语句当做一个模板使用

　　select * from

　　(select t1.*, rownum rn from (select * from emp) t1 where rownum<=10)

　　where rn>=6; --开发一个包 --建立一个包，在该包中，我定义类型test_cursor,是个游标。如下：

　　create or replace package testpackage as

　　TYPE test_cursor is ref cursor;

　　end testpackage;

　　--开始编写分页的过程

　　create or replace procedure fenye

　　(tableName in varchar2,

　　Pagesize in number,--一页显示记录数

　　pageNow in number,

　　myrows out number,--总记录数

　　myPageCount out number,--总页数

　　p_cursor out testpackage.test_cursor--返回的记录集

　　) is

　　--定义部分

　　--定义sql语句字符串

　　Oracle 笔记

　　v_sql varchar2(1000);

　　--定义两个整数

　　v_begin number:=(pageNow-1)*Pagesize+1;

　　v_end number:=pageNow*Pagesize;

　　begin

　　--执行部分

　　v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;

　　--把游标和sql关联

　　open p_cursor for v_sql;

　　--计算myrows和myPageCount

　　--组织一个sql语句

　　v_sql:='select count(*) from '||tableName;

　　--执行sql,并把返回的值，赋给myrows;

　　execute inmediate v_sql into myrows;

　　--计算myPageCount

　　--if myrows%Pagesize=0 then这样写是错的

　　if mod(myrows,Pagesize)=0 then

　　myPageCount:=myrows/Pagesize;

　　else

　　myPageCount:=myrows/Pagesize+1

　　end if;

　　--关闭游标

　　close p_cursor;

　　end;

　　/ --使用java测试 //测试分页

　　import java.sql.*;

　　public class FenYe{

　　public static void main(String[] args){

　　try{

　　//1.加载驱动

　　Class.forName("oracle.jdbc.driver.OracleDriver");

　　//2.得到连接

　　Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");

　　//3.创建CallableStatement

　　CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");

　　//4.给第?赋值

　　cs.seString(1,"emp");

　　cs.setInt(2,5);

　　cs.setInt(3,2);

　　//注册总记录数

　　Oracle 笔记

　　cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);

　　//注册总页数

　　cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);

　　//注册返回的结果集

　　cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);

　　//5.执行

　　cs.execute();

　　//取出总记录数 /这里要注意，getInt(4)中4，是由该参数的位置决定的

　　int rowNum=cs.getInt(4);

　　int pageCount = cs.getInt(5);

　　ResultSet rs=(ResultSet)cs.getObject(6);

　　//显示一下，看看对不对

　　System.out.println("rowNum="+rowNum);

　　System.out.println("总页数="+pageCount);

　　while(rs.next()){

　　System.out.println("编号："+rs.getInt(1)+" 名字："+rs.getString(2)+" 工资："+rs.getFloat(6));

　　}

　　} catch(Exception e){

　　e.printStackTrace();

　　} finally{

　　//6.关闭各个打开的资源

　　cs.close();

　　ct.close();

　　}

　　运行，控制台输出： rowNum=19 总页数：4 编号：7369 名字：SMITH 工资：2850.0 编号：7499 名字：ALLEN 工资：2450.0 编号：7521 名字：WARD 工资：1562.0 编号：7566 名字：JONES 工资：7200.0 编号：7654 名字：MARTIN 工资：1500.0 --新的需要，要求按照薪水从低到高排序，然后取出6-10 过程的执行部分做下改动，如下：

　　begin

　　--执行部分

　　v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin; 重新执行一次procedure,java不用改变，运行，控制台输出： rowNum=19 总页数：4 编号：7900 名字：JAMES 工资：950.0 编号：7876 名字：ADAMS 工资：1100.0

　　Oracle 笔记

　　编号：7521 名字：WARD 工资：1250.0 编号：7654 名字：MARTIN 工资：1250.0 编号：7934 名字：MILLER 工资：1300.0

　　21.例外处理例外处理 ? 例外的分类 oracle将例外分为预定义例外，非预定义例外和自定义例外三种。预定义例外用于处理常见的oracle错误非预定义例外用于处理预定义例外不能处理的例外自定义例外用于处理与oracle错误无关的其它情况 ? 例外传递如果不处理例外我们看看会出现什么情况：案例，编写一个过程，可接收雇员的编号，并显示该雇员的姓名。问题是，如果输入的雇员编号不存在，怎样去处理呢?

　　--例外案例

　　declare

　　--定义

　　v_ename emp.ename%type;

　　begin

　　select ename into v_ename from emp where empno=&gno;

　　dbms_output.put_line('名字：'||v_ename)

　　/ 执行，弹出框，看图：随便输个不在的编号，回车，会抛出异常，显示： ORA-01403: 未找到数据 ORA-06512: 在line 6

　　declare

　　--定义

　　v_ename emp.ename%type;

　　begin

　　select ename into v_ename from emp where empno=&gno;

　　dbms_output.put_line('名字：'||v_ename)

　　exception

　　when no_data_found then

　　dbms_output.put_line('编号没有!');

　　执行，输入一个不存在的编号，回车，显示：编号没有! ? 处理预定义例外预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle 规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用的例外。

　　Oracle 笔记

　　? 预定义例外 case_not_found 在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found的例外：

　　create or replace procedure sp_pro6(spno number) is

　　v_sal emp.sal%type;

　　begin

　　select sal into v_sal from emp where empno = spno;

　　case

　　when v_sal < 1000 then

　　update emp set sal = sal + 100 where empno = spno;

　　when v_sal < 2000 then

　　update emp set sal = sal + 200 where empno = spno;

　　end case;

　　exception

　　when case_not_found then

　　dbms_output.put_line('case语句没有与' || v_sal || '相匹配的条件');

　　end; ? 预定义例外 cursor_already_open 当重新打开已经打开的游标时，会隐含的触发例外cursor_already_open

　　declare

　　cursor emp_cursor is select ename, sal from emp;

　　begin

　　open emp_cursor;

　　for emp_record1 in emp_cursor loop

　　dbms_output.put_line(emp_record1.ename);

　　end loop;

　　exception

　　when cursor_already_open then

　　dbms_output.put_line('游标已经打开');

　　end;

　　/ ? 预定义例外 dup_val_on_index 在唯一索引所对应的列上插入重复的值时，会隐含的触发例外dup_val_on_index例外

　　begin

　　insert into dept values (10, '公关部', '北京');

　　exception

　　when dup_val_on_index then

　　dbms_output.put_line('在deptno列上不能出现重复值');

　　end; ? 预定义例外 invalid_cursor 当试图在不合法的游标上执行操作时，会触发该例外例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外

　　declare

　　cursor emp_cursor is select ename, sal from emp;

　　emp_record emp_cursor%rowtype;

　　begin

　　Oracle 笔记

　　--open emp_cursor; --打开游标

　　fetch emp_cursor into emp_record;

　　dbms_output.put_line(emp_record.ename);

　　close emp_cursor;

　　exception

　　when invalid_cursor then

　　dbms_output.put_line('请检测游标是否打开');

　　end; ? 预定义例外 invalid_number 当输入的数据有误时，会触发该例外比如：数字100写成了loo就会触发该例外

　　begin

　　update emp set sal= sal + 'loo';

　　exception

　　when invalid_number then

　　dbms_output.put_line('输入的数字不正确');

　　end; 预定义例外 no_data_found 下面是一个pl/sql块，当执行select into 没有返回行，就会触发该例外

　　declare

　　v_sal emp.sal%type;

　　begin

　　select sal into v_sal from emp

　　when ename='&name';

　　exception

　　when no_data_found then

　　dbms_output.put_line('不存在该员工');

　　end; ? 预定义例外 too_many_rows 当执行select into语句时，如果返回超过了一行，则会触发该例外。

　　declare

　　v_ename emp.ename%type;

　　begin

　　select ename into v_ename from emp;

　　exception

　　when too_many_rows then

　　dbms_output.put_line('返回了多行');

　　end; ? 预义例外 zero_divide 当执行2/0语句时，则会触发该例外。 ? 预定义例外 value_error 当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error，比如：

　　declare

　　v_ename varchar2(5);

　　begin

　　Oracle 笔记

　　select ename into v_ename from emp where empno = &no1;

　　dbms_output.put_line(v_ename);

　　exception

　　when value_error then

　　dbms_output.put_line('变量尺寸不足');

　　end; ? 其它预定义例外(这些例外不是在pl/sql里触发的，而是在用oracle时触发的，所以取名叫其它预定义例外) 1.login_denied 当用户非法登录时，会触发该例外 2.not_logged_on 如果用户没有登录就执行dml操作，就会触发该例外 3.storage_error 如果超过了内存空间或是内存被损坏，就触发该例外 4.timeout_on_resource 如果oracle在等待资源时，出现了超时就触发该例外 ? 非预定义例外非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等。在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外用的不多，这里我就不举例了。 ? 处理自定义例外预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle错误会隐含的触发相应的例外;而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外. 问题：请编写一个pl/sql块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。

　　--自定义例外

　　create or replace procedure ex_test(spNo number)

　　begin

　　--更新用户sal

　　update emp set sal=sal+1000 where empno=spNo;

　　end;

　　/ 运行，该过程被成功创建。 SQL> exec ex_test(56); PL/SQL过程被成功完成这里，编号为56是不存在的，刚才的报异常了，为什么现在不报异常呢? 因为刚才的是select语句怎么解决这个问题呢? 修改代码，如下：

　　--自定义例外

　　create or replace procedure ex_test(spNo number)

　　--定义一个例外

　　myex exception;

　　begin

　　--更新用户sal

　　update emp set sal=sal+1000 where empno=spNo;

　　Oracle 笔记

　　--sql%notfound这是表示没有update

　　--raise myex;触发myex

　　if sql%notfound then

　　raise myex;

　　end if;

　　exception

　　when myex then

　　dbms_output.put_line('没有更新任何用户');

　　end;

　　/ 现在再测试一次： SQL> exec ex_test(56); 没有更新任何用户

　　22.oracle的视图 oracle的视图 ? 介绍视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。(视图不是真实存在磁盘上的) 看图：视与表的区别 ? 视图与表的区别 1.表需要占用磁盘空间，视图不需要 2.视图不能添加索引(所以查询速度略微慢点) 3.使用视图可以简化，复杂查询比如：学生选课系统 4.视图的使用利于提高安全性比如：不同用户查看不同视图创建/修改视图 ? 创建视图 create view 视图名 as select 语句 [with read only] ? 创建或修改视图 create or replace view 视图名 as select 语句 [with read only] ? 删除视图 drop view 视图名当表结构国语复杂，请使用视图吧! --创建视图，把emp表的sal<1000的雇员映射到该视图(view)

　　create view myview as select * from emp where sal<1000; --为简化操作，用一个视图解决显示雇员编号，姓名和部门名称

　　create view myview2 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno; 视图之间也可以做联合查询

一、基础

　　1、说明：创建数据库

　　CREATE DATABASE database-name

　　2、说明：删除数据库

　　drop database dbname

　　3、说明：备份sql server

　　--- 创建备份数据的 device

　　USE master

　　EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'

　　--- 开始备份

　　BACKUP DATABASE pubs TO testBack

　　4、说明：创建新表

　　create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

　　根据已有的表创建新表：

　　A：create table tab_new like tab_old (使用旧表创建新表)

　　B：create table tab_new as select col1,col2… from tab_old definition only

　　5、说明：删除新表

　　drop table tabname

　　6、说明：增加一个列

　　Alter table tabname add column col type

　　注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

　　7、说明：添加主键： Alter table tabname add primary key(col)

　　说明：删除主键： Alter table tabname drop primary key(col)

　　8、说明：创建索引：create [unique] index idxname on tabname(col….)

　　删除索引：drop index idxname

　　注：索引是不可更改的，想更改必须删除重新建。

　　9、说明：创建视图：create view viewname as select statement

　　删除视图：drop view viewname

　　10、说明：几个简单的基本的sql语句

　　选择：select * from table1 where 范围

　　插入：insert into table1(field1,field2) values(value1,value2)

　　删除：delete from table1 where 范围

　　更新：update table1 set field1=value1 where 范围

　　查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!

　　排序：select * from table1 order by field1,field2 [desc]

　　总数：select count as totalcount from table1

　　求和：select sum(field1) as sumvalue from table1

　　平均：select avg(field1) as avgvalue from table1

　　最大：select max(field1) as maxvalue from table1

　　最小：select min(field1) as minvalue from table1

　　11、说明：几个高级查询运算词

　　A： UNION 运算符

　　UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL)，不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

　　B： EXCEPT 运算符

　　EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

　　C： INTERSECT 运算符

　　INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

　　注：使用运算词的几个查询结果行必须是一致的。

　　12、说明：使用外连接

　　A、left (outer) join：

　　左外连接(左连接)：结果集几包括连接表的匹配行，也包括左连接表的所有行。

　　SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

　　B：right (outer) join:

　　右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

　　C：full/cross (outer) join：

　　全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

　　12、分组:Group by:

　　一张表，一旦分组完成后，查询后只能得到组相关的信息。

　　组相关的信息：(统计信息) count,sum,max,min,avg 分组的标准)

　　在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据

　　在selecte统计函数中的字段，不能和普通的字段放在一起;

　　13、对数据库进行操作：

　　分离数据库： sp_detach_db; 附加数据库：sp_attach_db 后接表明，附加需要完整的路径名

　　14.如何修改数据库的名称:

　　sp_renamedb 'old_name', 'new_name'

　　二、提升

　　1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)

　　法一：select * into b from a where 1<>1(仅用于SQlServer)

　　法二：select top 0 * into b from a

　　2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)

　　insert into b(a, b, c) select d,e,f from b;

　　3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

　　insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

　　例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

　　4、说明：子查询(表名1：a 表名2：b)

　　select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

　　5、说明：显示文章、提交人和最后回复时间

　　select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

　　6、说明：外连接查询(表名1：a 表名2：b)

　　select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

　　7、说明：在线视图查询(表名1：a )

　　select * from (SELECT a,b,c FROM a) T where t.a > 1;

　　8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括

　　select * from table1 where time between time1 and time2

　　select a,b,c, from table1 where a not between 数值1 and 数值2

　　9、说明：in 的使用方法

　　select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

　　10、说明：两张关联表，删除主表中已经在副表中没有的信息

　　delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

　　11、说明：四表联查问题：

　　select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

　　12、说明：日程安排提前五分钟提醒

　　SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

　　13、说明：一条sql 语句搞定数据库分页

　　select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

　　具体实现：

　　关于数据库分页：

　　declare @start int,@end int

　　@sql nvarchar(600)

　　set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’

　　exec sp_executesql @sql

　　注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引)

　　14、说明：前10条记录

　　select top 10 * form table1 where 范围

　　15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

　　select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

　　16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

　　(select a from tableA ) except (select a from tableB) except (select a from tableC)

　　17、说明：随机取出10条数据

　　select top 10 * from tablename order by newid()

　　18、说明：随机选择记录

　　select newid()

　　19、说明：删除重复记录

　　1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

　　2),select distinct * into temp from tablename

　　delete from tablename

　　insert into tablename select * from temp

　　评价：这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作

　　3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段

　　alter table tablename

　　--添加一个自增列

　　add column_b int identity(1,1)

　　delete from tablename where column_b not in(

　　select max(column_b) from tablename group by column1,column2,...)

　　alter table tablename drop column column_b

　　20、说明：列出数据库里所有的表名

　　select name from sysobjects where type='U' // U代表用户

　　21、说明：列出表里的所有的列名

　　select name from syscolumns where id=object_id('TableName')

　　22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

　　select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

　　显示结果：

　　type vender pcs

　　电脑 A 1

　　光盘 B 2

　　光盘 A 2

　　手机 B 3

　　手机 C 3

　　23、说明：初始化表table1

　　TRUNCATE TABLE table1

　　24、说明：选择从10到15的记录

　　select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

　　三、技巧

　　1、1=1，1=2的使用，在SQL语句组合时用的较多

　　“where 1=1” 是表示选择全部 “where 1=2”全部不选，

　　如：

　　if @strWhere !=''

　　begin

　　set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere

　　end

　　else

　　begin

　　set @strSQL = 'select count(*) as Total from [' + @tblName + ']'

　　end

　　我们可以直接写成

　　错误!未找到目录项。

　　set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库

　　--重建索引

　　DBCC REINDEX

　　DBCC INDEXDEFRAG

　　--收缩数据和日志

　　DBCC SHRINKDB

　　DBCC SHRINKFILE

　　3、压缩数据库

　　dbcc shrinkdatabase(dbname)

　　4、转移数据库给新用户以已存在用户权限

　　exec sp_change_users_login 'update_one','newname','oldname'

　　5、检查备份集

　　RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

　　6、修复数据库

　　ALTER DATABASE [dvbbs] SET SINGLE_USER

　　DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK

　　ALTER DATABASE [dvbbs] SET MULTI_USER

　　7、日志清除

　　SET NOCOUNT ON

　　DECLARE @LogicalFileName sysname,

　　@MaxMinutes INT,

　　@NewSize INT

　　USE tablename -- 要操作的数据库名

　　SELECT @LogicalFileName = 'tablename_log', -- 日志文件名

　　@MaxMinutes = 10, -- Limit on time allowed to wrap log.

　　@NewSize = 1 -- 你想设定的日志文件的大小(M)

　　Setup / initialize

　　DECLARE @OriginalSize int

　　SELECT @OriginalSize = size

　　FROM sysfiles

　　WHERE name = @LogicalFileName

　　SELECT 'Original Size of ' + db_name() + ' LOG is ' +

　　CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

　　CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

　　FROM sysfiles

　　WHERE name = @LogicalFileName

　　CREATE TABLE DummyTrans

　　(DummyColumn char (8000) not null)

　　DECLARE @Counter INT,

　　@StartTime DATETIME,

　　@TruncLog VARCHAR(255)

　　SELECT @StartTime = GETDATE(),

　　@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

　　DBCC SHRINKFILE (@LogicalFileName, @NewSize)

　　EXEC (@TruncLog)

　　-- Wrap the log if necessary.

　　WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

　　AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)

　　AND (@OriginalSize * 8 /1024) > @NewSize

　　BEGIN -- Outer loop.

　　SELECT @Counter = 0

　　WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

　　BEGIN -- update

　　INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans

　　SELECT @Counter = @Counter + 1

　　END

　　EXEC (@TruncLog)

　　END

　　SELECT 'Final Size of ' + db_name() + ' LOG is ' +

　　CONVERT(VARCHAR(30),size) + ' 8K pages or ' +

　　CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

　　FROM sysfiles

　　WHERE name = @LogicalFileName

　　DROP TABLE DummyTrans

　　SET NOCOUNT OFF

　　8、说明：更改某个表

　　exec sp_changeobjectowner 'tablename','dbo'

　　9、存储更改全部表

　　CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch

　　@OldOwner as NVARCHAR(128),

　　@NewOwner as NVARCHAR(128)

　　DECLARE @Name as NVARCHAR(128)

　　DECLARE @Owner as NVARCHAR(128)

　　DECLARE @OwnerName as NVARCHAR(128)

　　DECLARE curObject CURSOR FOR

　　select 'Name' = name,

　　'Owner' = user_name(uid)

　　from sysobjects

　　where user_name(uid)=@OldOwner

　　order by name

　　OPEN curObject

　　FETCH NEXT FROM curObject INTO @Name, @Owner

　　WHILE(@@FETCH_STATUS=0)

　　BEGIN

　　if @Owner=@OldOwner

　　begin

　　set @OwnerName = @OldOwner + '.' + rtrim(@Name)

　　exec sp_changeobjectowner @OwnerName, @NewOwner

　　end

　　-- select @name,@NewOwner,@OldOwner

　　FETCH NEXT FROM curObject INTO @Name, @Owner

　　END

　　close curObject

　　deallocate curObject

　　10、SQL SERVER中直接循环写入数据

　　declare @i int

　　set @i=1

　　while @i<30

　　begin

　　insert into test (userid) values(@i)

　　set @i=@i+1

　　end

　　案例：

　　有如下表，要求就裱中所有没有及格的成绩，在每次增长0.1的基础上，使他们刚好及格:

　　 Name score

　　 Zhangshan 80

　　 Lishi 59

　　 Wangwu 50

　　 Songquan 69

　　while((select min(score) from tb_table)<60)

　　begin

　　update tb_table set score =score*1.01

　　where score<60

　　if (select min(score) from tb_table)>60

　　break

　　else

　　continue

　　end

　　数据开发-经典

　　1.按姓氏笔画排序:

　　Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多

　　2.数据库加密:

　　select encrypt('原始密码')

　　select pwdencrypt('原始密码')

　　select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')

　　select pwdencrypt('原始密码')

　　select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同

　　3.取回表中字段:

　　declare @list varchar(1000),

　　@sql nvarchar(1000)

　　select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'

　　set @sql='select '+right(@list,len(@list)-1)+' from 表A'

　　exec (@sql)

　　4.查看硬盘分区:

　　EXEC master..xp_fixeddrives

　　5.比较A,B表是否相等:

　　if (select checksum_agg(binary_checksum(*)) from A)

　　(select checksum_agg(binary_checksum(*)) from B)

　　print '相等'

　　else

　　print '不相等'

　　6.杀掉所有的事件探察器进程:

　　DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses

　　WHERE program_name IN('SQL profiler',N'SQL 事件探查器')

　　EXEC sp_msforeach_worker '?'

　　7.记录搜索:

　　开头到N条记录

　　Select Top N * From 表

　　-------------------------------

　　N到M条记录(要有主索引ID)

　　Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc

　　----------------------------------

　　N到结尾记录

　　Select Top N * From 表 Order by ID Desc

　　案例

　　例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段，写一个SQL语句，找出表的第31到第40个记录。

　　select top 10 recid from A where recid not in(select top 30 recid from A)

　　分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。

　　 select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。

　　解决方案

　　1，用order by select top 30 recid from A order by ricid 如果该字段不是自增长，就会出现问题

　　2，在那个子查询中也加条件：select top 30 recid from A where recid>-1

　　例2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。

　　set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'

　　print @s exec sp_executesql @s

　　9：获取当前数据库中的所有用户表

　　select Name from sysobjects where xtype='u' and status>=0

　　10：获取某一个表的所有字段

　　select name from syscolumns where id=object_id('表名')

　　select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')

　　两种方式的效果相同

　　11：查看与某一个表相关的视图、存储过程、函数

　　select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

　　12：查看当前数据库中所有存储过程

　　select name as 存储过程名称 from sysobjects where xtype='P'

　　13：查询用户创建的所有数据库

　　select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

　　或者

　　select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

　　14：查询某一个表的字段和数据类型

　　select column_name,data_type from information_schema.columns

　　where table_name = '表名'

　　15：不同服务器数据库之间的数据操作

　　--创建链接服务器

　　exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

　　exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '

　　--查询示例

　　select * from ITSV.数据库名.dbo.表名

　　--导入示例

　　select * into 表 from ITSV.数据库名.dbo.表名

　　--以后不再使用时删除链接服务器

　　exec sp_dropserver 'ITSV ', 'droplogins '

　　--连接远程/局域网数据(openrowset/openquery/opendatasource)

　　--1、openrowset

　　--查询示例

　　select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

　　--生成本地表

　　select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

　　--把本地表导入远程表

　　insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

　　select *from 本地表

　　--更新本地表

　　update b

　　set b.列A=a.列A

　　from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b

　　on a.column1=b.column1

　　--openquery用法需要创建一个连接

　　--首先创建一个连接创建链接服务器

　　exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

　　--查询

　　select *

　　FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

　　--把本地表导入远程表

　　insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

　　select * from 本地表

　　--更新本地表

　　update b

　　set b.列B=a.列B

　　FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a

　　inner join 本地表 b on a.列A=b.列A

　　--3、opendatasource/openrowset

　　SELECT *

　　FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta

　　--把本地表导入远程表

　　insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名

　　select * from 本地表

　　SQL Server基本函数

　　1.字符串函数长度与分析用

　　1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格

　　2,substring(expression,start,length) 取子串，字符串的下标是从“1”，start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度

　　3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用left于之相反

　　4,isnull( check_expression , replacement_value )如果check_expression为空，则返回replacement_value的值，不为空，就返回check_expression字符操作类

　　5,Sp_addtype 自定义数据类型

　　例如：EXEC sp_addtype birthday, datetime, 'NULL'

　　6,set nocount {on|off}

　　使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。

　　SET NOCOUNT 为 ON 时，不返回计数(表示受 Transact-SQL 语句影响的行数)。

　　SET NOCOUNT 为 OFF 时，返回计数

　　常识

　　在SQL查询中：from后最多可以跟多少张表或视图：256

　　在SQL语句中出现 Order by,查询时，先排序，后取

　　在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。

　　SQLServer2000同步复制技术实现步骤

　　一、预备工作

　　1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户

　　--管理工具

　　--计算机管理

　　--用户和组

　　--右键用户

　　--新建用户

　　--建立一个隶属于administrator组的登陆windows的用户(SynUser)

　　2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:

　　我的电脑--D:\ 新建一个目录,名为: PUB

　　--右键这个新建的目录

　　--属性--共享

　　--选择"共享该文件夹"

　　--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限

　　--确定

　　3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)

　　开始--程序--管理工具--服务

　　--右键SQLSERVERAGENT

　　--属性--登陆--选择"此账户"

　　--输入或者选择第一步中创建的windows登录用户名(SynUser)

　　--"密码"中输入该用户的密码

　　4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)

　　企业管理器

　　--右键SQL实例--属性

　　--安全性--身份验证

　　--选择"SQL Server 和 Windows"

　　--确定

　　5.在发布服务器和订阅服务器上互相注册

　　企业管理器

　　--右键SQL Server组

　　--新建SQL Server注册...

　　--下一步--可用的服务器中,输入你要注册的远程服务器名 --添加

　　--下一步--连接使用,选择第二个"SQL Server身份验证"

　　--下一步--输入用户名和密码(SynUser)

　　--下一步--选择SQL Server组,也可以创建一个新组

　　--下一步--完成

　　6.对于只能用IP,不能用计算机名的,为其注册服务器别名(此步在实施中没用到)

　　(在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)

　　开始--程序--Microsoft SQL Server--客户端网络实用工具

　　--别名--添加

　　--网络库选择"tcp/ip"--服务器别名输入SQL服务器名

　　--连接参数--服务器名称中输入SQL服务器ip地址

　　--如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号

　　二、正式配置

　　1、配置发布服务器

　　打开企业管理器，在发布服务器(B、C、D)上执行以下步骤:

　　(1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导

　　(2) [下一步] 选择分发服务器可以选择把发布服务器自己作为分发服务器或者其他sql的服务器(选择自己)

　　(3) [下一步] 设置快照文件夹

　　采用默认\\servername\Pub

　　(4) [下一步] 自定义配置

　　可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置

　　否,使用下列默认设置(推荐)

　　(5) [下一步] 设置分发数据库名称和位置采用默认值

　　(6) [下一步] 启用发布服务器选择作为发布的服务器

　　(7) [下一步] 选择需要发布的数据库和发布类型

　　(8) [下一步] 选择注册订阅服务器

　　(9) [下一步] 完成配置

　　2、创建出版物

　　发布服务器B、C、D上

　　(1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令

　　(2)选择要创建出版物的数据库，然后单击[创建发布]

　　(3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)

　　(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,

　　SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。

　　但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器

　　(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表

　　注意: 如果前面选择了事务发布则再这一步中只能选择带有主键的表

　　(6)选择发布名称和描述

　　(7)自定义发布属性向导提供的选择:

　　是我将自定义数据筛选,启用匿名订阅和或其他自定义属性

　　否根据指定方式创建发布 (建议采用自定义的方式)

　　(8)[下一步] 选择筛选发布的方式

　　(9)[下一步] 可以选择是否允许匿名订阅

　　1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器

　　方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加

　　否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅

　　如果仍然需要匿名订阅则用以下解决办法

　　[企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅

　　2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示

　　(10)[下一步] 设置快照代理程序调度

　　(11)[下一步] 完成配置

　　当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库

　　有数据

　　srv1.库名..author有字段:id,name,phone,

　　srv2.库名..author有字段:id,name,telphone,adress

　　要求：

　　srv1.库名..author增加记录则srv1.库名..author记录增加

　　srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新

　　--*/

　　--大致的处理步骤

　　--1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步

　　exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql实例名或ip'

　　exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'

　　--2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动

　　。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动

　　--然后创建一个作业定时调用上面的同步处理存储过程就行了

　　企业管理器

　　--管理

　　--SQL Server代理

　　--右键作业

　　--新建作业

　　--"常规"项中输入作业名称

　　--"步骤"项

　　--新建

　　--"步骤名"中输入步骤名

　　--"类型"中选择"Transact-SQL 脚本(TSQL)"

　　--"数据库"选择执行命令的数据库

　　--"命令"中输入要执行的语句: exec p_process

　　--确定

　　--"调度"项

　　--新建调度

　　--"名称"中输入调度名称

　　--"调度类型"中选择你的作业执行安排

　　--如果选择"反复出现"

　　--点"更改"来设置你的时间安排

　　然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行

　　设置方法:

　　我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.

　　--3.实现同步处理的方法2,定时同步

　　--在srv1中创建如下的同步处理存储过程

　　create proc p_process

　　--更新修改过的数据

　　update b set name=i.name,telphone=i.telphone

　　from srv2.库名.dbo.author b,author i

　　where b.id=i.id and

　　(b.name <> i.name or b.telphone <> i.telphone)

　　--插入新增的数据

　　insert srv2.库名.dbo.author(id,name,telphone)

　　select id,name,telphone from author i

　　where not exists(

　　select * from srv2.库名.dbo.author where id=i.id)

　　--删除已经删除的数据(如果需要的话)

　　delete b

　　from srv2.库名.dbo.author b

　　where not exists(

　　select * from author where id=b.id)

　　MERGE INTO TLASTGPS T1 USING (SELECT '3109716' AS deviceid FROM dual) T2 ON (T1.deviceid=T2.deviceid) WHEN MATCHED THEN UPDATE SET status=0,y=693413.854883,x=603015.756594,hight=0,speed=0,direction=13,cartime=to_date('2016-09-01 11:35:20','yyyy-MM-dd hh24:mi:ss') WHEN NOT MATCHED THEN INSERT (status,y,x,hight,speed,direction,cartime,deviceid) values(0,693413.854883,603015.756594,0,0,13,to_date('2016-09-01 11:35:20','yyyy-MM-dd hh24:mi:ss'),'3109716')

下页更精彩：首页上一页 1 2 3

本文已影响871人

上一篇：2017年Oracle认证大全 下一篇：学习Oracle数据库技巧攻略

oracle的sql语句

oracle的sql语句相关推荐