32
常用的有：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 笔记
33
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 笔记
34
联回收】 如：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 笔记
35
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 笔记
36
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 笔记
37
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 笔记
38
块(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 笔记
39
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 笔记
40
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 笔记
41
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)
is
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;
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 笔记
42
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 笔记
43
? 复合变量(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 笔记
44
? 复合变量——嵌套表(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 笔记
45
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 exec ex_test(56); PL/SQL过程被成功完成 这里，编号为56是不存在的，刚才的报异常了，为什么现在不报异常呢？因为刚才的是select语句 怎么解决这个问题呢？修改代码，如下：
--自定义例外
create or replace procedure ex_test(spNo number)
is
--定义一个例外
myex exception;
begin
--更新用户sal
update emp set sal=sal+1000 where empno=spNo;
Oracle 笔记
59
--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表的sal5
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
电脑 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'
go
5、检查备份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
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)
AS
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
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i=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基本函数
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,'用户名','密码'
go
--2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动
。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动
go
--然后创建一个作业定时调用上面的同步处理存储过程就行了
企业管理器
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)"
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句: exec p_process
--确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选择"反复出现"
--点"更改"来设置你的时间安排
然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行
设置方法:
我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
--3.实现同步处理的方法2,定时同步
--在srv1中创建如下的同步处理存储过程
create proc p_process
as
--更新修改过的数据
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)
go
