Oracle

第三章、分区表和锁 一.分区表的分类:范围分区、列表分区、散列分区、复合分区 1.范围分区：当数据在范围类均匀分布是，性能最佳。（如一年中月份的天数） **注意** 1.分区的分界点一定是从小到大依次给出 2.分界点不在语句的所指定的分区，而是下一分区 3.如果要所以剩余值分在最后一个区，分界点使用maxvalue，若没有指定maxvalue确定的缺省分区，超出最大分界点的数据将无法插入 语法： create table atable ( <字段列表> ) partition by range(<分区的字段>) （ partition p1 values less than(1001) tablespace svse1, partition p2 values less than(2002) tablespace svse2, partition p3 values less than(maxvalue) tablespace svse3 ） 2.列表分区：按照列表中指定的数据分区 语法： create table MobilePhone ( mid number(11), mtype char(10), location(30) ) partition by list(location) ( partition p1 values('洪山','青山','武昌'), partition p2 values('桥口','汉阳'), partition p3 values(default) ---其余的 ) 3.散列分区：没有特别分区要求，因性能原因要求分区时予以考虑 --只关注分区的数量，系统自动定义分区名称，数据存储在缺省的表空间，主要针对海量数据 ex1. create table orders ( oid number(20), orderdate date, mname varchar(30), count int ) partition by hash(oid) partitions <分区的数量>; ex2.创建时，给定分区名称 create table orders ( ........ ) partition by hash(oid) ( partition p1, partition p2, partition p3, partition p4 ); ex3.创建时，给定分区存储的表空间 create table orders ( ....... ) partition by hash(oid) partition 4 store in(ord1,ord2,ord3,ord4); 4.复合分区:其他分区的组合，如范围分区、列表分区的组合 create table interuser ( <字段列表> ) partition by range(<范围分区字段>) subpartition by list(<列表分区字段>) （ partition p1 values less than(<范围值>) （ subpatition p11 values(<列表值>，<列表值>)， subpatition p12 values(<列表值>，<列表值>)， subpatition p13 values(<列表值>，<列表值>) ）， partition p2 values less than(<范围值>) （ subpatition p21 values(<列表值>，<列表值>)， subpatition p22 values(<列表值>，<列表值>)， subpatition p23 values(<列表值>，<列表值>) ） ） 4.查看分区信息： select table_name,partition_name,tablespace_name from user_tab_partition 二，分区表的维护 1.查询分区中的数据：select * from atable partition(p2); 2.增加分区表(增加分区表的新分区一定是最后一个分区，且要添加分区的表没有使用maxvalue作为分区的分界点)： alter table atable add partition p3 values less than(3001) tablespace svse2; 3.删除一个分区：alter table atable drop partition p3; 4.拆分分区：alter table atable split partition p3 at(1050) into (partition p4,partition p5); 5.合并分区：alter table atable merge partitions p4,p5 into partition p5; 6.分区改名：alter table atable rename partition p3 to p4; 7.将分区改表空间：alter table move partition p4 tablespace svse2 三、锁。 1.锁用于处理多用户并发的存取数据问题的机制。 根据保护对象的不同可分为： a.DML锁（data locks数据锁），用于保证数据的完整性； b.DDl锁（dictionary locks字典锁），用于保护数据库对象的结构； c.内部锁（internal locks）,保护数据库的内部结构。 DML锁根据范围可分为: TM锁（表级锁）、TX锁（行级锁） 根据锁定后使用资源的方式有两种模式： 1>.排他模式（EXCLEUSIVE）:不允许并发以任何方式共享锁的资源。 2>.共享模式（SHARE）：允许并发的读访问，修改数据时，上升为排他模式。 exclusive 2.系统锁的级别。 0：NONE：无 1：NULL：空 2：ROW SHARE 行共享（RS） 3：ROW EXCLUSIVE 行排他（RX） 4：SHARE：共享锁（S） 5:SHARE ROW EXCLUSIVE:共享行排他（SRX） 6.EXCLUSIVE: 排他锁(X) 2、3：行级锁。 4、5、6：表级锁。 备注：当数据没有别操作的时候为0.进行SELECT后，为1.数据没有其他关联的表，进行INSERT...等，自动加锁3.若有关联，则操作表加3，关联表加4.操作DDL时，系统自动加6 加锁语法： LOCK TABLE 表名 IN [ROW SHARE|ROW EXCLUSIVE|SHARE|SHARE ROW EXCLUSIVE|EXCLUSIVE] MODE ex.LOCK TABLE EMP IN EXCLUSIVE MODE. 3，死锁。 当事务一操作数据A，没有提交。 事务二操作数据B，没有提交。然后事务一操作数据B，将会等待事务二释放资源。 接着事务二操作数据A，同样等待事务一释放资源。 这是出现互相等待资源释放资源的情况，称为死锁。

知识点

在dos下运行sqlplus： /nolog是不登陆到数据库服务器的意思 如果没有/nolog参数，sqlplus会提示你输入用户名和密码 select trim(both|leading|trailing ['set'] from string) from dual; 第一章：Oracle入门。 1.sql/plus常用命令： remark（单行注释,在命令窗口使用）或者"--". exit 退出sql/plus. 设置每行字符数 set linesize 数量. 设置每页行数 set pagesize. edit 错误后修改的命令 spool on /spool off保存sql/plus中内容的命令 . start或者@ 执行文本的批处理命令. Desc 表名 查看表结构的命令 show user 查看当前用户. select username from dba_users;查询所有用户。 drop user 用户名 cascade;删除用户。 2.oracle的结构 实例：内存区域和后台进程合称为一个实例。 内存区域：系统全局区（SGA）、程序全局区（PGA）、排序池（Sort Area）、大池（Large Pool） 、Java池（Java Pool） 3.Oracle的逻辑结构 创建表空间语法： create tablespace <表空间名称> datefile <表空间数据文件路径> size [表空间数据文件的初始大小] [autoextend no]; ex：create tablespace tests datafile 'e:\a.dbf' size 5M autoextend on; 修改表空间 a.扩展表空间-改变数据文件大小 alter database datafile 'E:\a.dbf' resize 10M; 增加数据文件 alter tablaspace tests add datafile 'E:\a.dbf' size 10M [autoextend on next 10M maxsize 100M]; 修改表空间名称 alter tablespace tests rename to [更改的名字]; 删除表空间 1.表空间中没有存在对象的时候：drop tablespace tests; 2.表空间中存在对象的时候：drop tablespace tests including contents; drop tablespace tests including contents and datafiles; 四、Oracle中的用户、角色、权限 1.连接用户 conn 用户名/密码 连接系统DBA conn / as sysdba; disconnect;断开连接 2.创建用户语法： create user <用户名> identified by <密码> [defualt tablespace 缺省表空间名称] [temporary tablespace 临时表空间名称] 3.授予权限 grant 权限名 to 用户名 4.锁定用户 alter user 用户名 account lock; 解锁 alter user 用户名 account unlock; 模式：一个用户创建的所有对象的集合。 第二章、SQL查询和SQL函数 一、数据类型 1.数值类型--Number（<p>,<s>） p是总位数，s是小数位。当整数的总位数大于p-s时报错。 p大于整数位是，相当于在后面0补充，小数位>s时，相当于后面用0补充，s为负数时， 表示小数点向左几位舍入（四舍五入） 2.字符类型 char 定长字符，不够自动在右边加空格. varchar varchar2 可变字符.不够自动缩短字符长度，超过同样报错. nchar nvarchar2 每个字符以双字节表示。ex.nchar(4) ,可以定义四个汉字（双字节）。同时也只能定义四个字符（都作为双字节）。 3.日期类型 插入日期类型：to_date insert into test values(1,to_date('<日期>','<格式>')); 输出日期类型：to_char select to_char(‘<日期字段>,'<格式>') from test; 4.大对象类型 blob 、clob、bfile 、raw、long、longraw...... 5.空类型--NULL 在Oracle中使用null标识空值 6.特殊类型 伪列 rowid :记录硬盘、磁道、数据块等信息。 rownum:序列号，从1开始 level：查询数据所对应的层次 二、SQL语句 1.数据定义语言(Date Definition Language):CREATE 、DROP、ALTER 创建备份数据表 create table 备份表名 as select * from emp; --添加字段：alter table student add(<字段名> <类型>)； --修改类型：alter table student modify(<已有字段名> <修改后的类型>)； --删除字段：alter table student drop(<字段名>) 2.数据操作语言（Date Manipulation Language）:insert 、select、update delete ..................... 3.数据控制语言(Date Contral Language):对数据库中权限的授予与取消 ⒈系统权限与角色权限 授予权限：grant 权限 to 用户名 常用的角色权限：connect(基本的链接) 、resource（资源使用）、DBA（数据库管理） ⒉对象权限： grant 权限名|ALL on 对象名 to 用户名 ex.grant select on emp to lichao.当用ALL代替 select 时，表示将所有该对象权限赋予用户 ⒊权限回收：revoke 系统权限名 from 用户名 revoke 角色权限名 on 对象名 from 用户名 4.事务控制语言(Transaction Contral Language):COMMIT、ROLLBACK、SAVEPOINT（保存点） 5.集合运算 １.并集（UNION） union用法中，select语句的字段类型要匹配，个数要相同 select * from A union select * from B 交集（intersect）、割集(minus)、

用户管理

oracle大约有140多种权限。权限分为系统权限，和对象权限。 系统权限： 描述用户对数据库访问的一些权限。 对象权限：用户对其他用户的数据对象访问的权限。 角色： 一些权限的集合，角色分为预定义角色，自定义角色。 给用户赋予/收回权限： grant 权限名 to 用户名； revoke 权限名 from 用户名. 给用户赋予某个表的权限。 grant select/update/delete/insert/all on 表名 to 用户名。 要想给一个用户的权限，该用户转移权限还可以给其他用户，那么需要在赋予权限后加上 with grant option grant select/update/delete/insert/all on 表名 to 用户名 with grant option 。 创建用户限制：profile 1.账户锁定： create profile lock_account limit failed_login_attempts 3 password_lock_time 2; 当用户登录三次错误，则锁定2两天。 应用该限制： alter user 用户名 profile lock_account; 2.终止口令： create profile myprofile limit password_life_time 10 password_grace_time 2; 每过十天需要更改密码，否则两天后锁定账户。 create profile myprofile limit password_life_time 10 password_grace_time 2 password_reuse_time 10; 每过十天需要更改密码，否则两天后锁定账户,十天后可以重用。

表的管理

一，表名和列的命名规则 1.必须已字母开头 2.长度不能超过30个字符。 3.不能只用oracle的关键字。 4.只能使用A-Z，a-z，0-9，#，$字符。 二、数据类型 1.字符型 char 定长 最大为2000. （查询速度快） varchar2 变长 最大为4000. （节省空间） clob 字符型大对象 最大为4G。 2.数字型 number 范围 10的-38次方 -10的38次方。 number（p，s） p表示有效数，s表示小数位。 3.时间类型 date 包括年月日、时分秒。 timestamp alter session set nls_date_format = 'yyyy-mm-dd'; //修改时间类型格式 4.图片类型 blob 可以存放图片，声音、视频等 三、回滚 设置回滚点： sava point 名字；

事务管理、对象（同义词、序列、视图、索引）

1.只读事务。set transaction read only; 只读事务只允许执行查询操作。 一、同义词 1.分类：同义词可分为公有同义词、私有同义词。 公有同义词：所有的oracle对象都可以访问。 私有同义词：只有创建者可以访问。 2.创建与删除。 创建和删除需要赋予权限。 CREATE ANY SYNONYM 、DROP ANY SYNONYM. CREATE PUBLIC ANY SYNONYM 、DROP PUBLIC ANY SYNONYM. 一个对象可以有多个同义词。 语法。 CREATE SYNONYM 同义词名 FOR 对象名。 二、序列。 1.创建语法： CREATE SEQUENCE [USER.]序列名称 [increment by ]{1|integer} --序列增长的步长 [start with integer] --序列值的起始值 [maxvalue integer|nomaxvalue] --序列值的最大值 [minvalue integer|nominvalue] --序列值的最小值 [cycle|nocycle] [序列循环取值|不循环取值] [cache{20|integer}|nocache] --是否启用缓冲，默认启用 备注：步长可以为负数。 2.使用。 NEXTVAL(获取一个新的序列) CURRVAL(获取序列的当前值) 三、视图。 1.语法： CREATE [OR REPLACE] [FORCE/NO /FORCE] VIEW --force 强行创建一个视图，无论视图的基表是否存在或拥有者是否有权限，但执行dml前提条件必须为真 [SCHEMA.] VIEW --账户，视图名 [column_name1,column_name2] AS 查询语句 [with object oid | default] [with check option ] --控制不能修改查询条件的字段 [with read only] 备注：视图查询不能选取currval，nextval 四、索引。 1.索引的原理：数据库存储数据的时候并不会根据数据的特征进行排序。而是根据硬盘空余的情况随机存储的，因此数据在硬盘上的数据顺序是无序的。索引就是要解决 数据排序的问题。 2.索引的分类： 1>.普通索引：使用环境为索引列没有显著的特点。 ex.CREATE INDEX 索引名 ON 表名（字段） CREATE INDEX INORDERID ON ORDERS(ORDERid) 2>.组合索引：如果对某个表常用的几个字段作为查询条件，这样可以将这几个字段一起作为索引列进行排序，并存入索引， ex. CREATE INDEX 索引名 ON 表名 （字段列表） CREATE INDEX INORDERAREA ON ORDRES(ORDERID ,AREA) 如果条件中包含ORDERID和AREA说着只有ORDERID，oracle将会自动调用索引。 3>.唯一索引：索引列唯一 CREATE UNIQUE INDEX INORDERID ON ORDERS(ORDERID) 4>.反向键索引：将索引列的内容反向存储，再排序。 CREATE INDEX 索引名 ON 表名（字段列表） REVERSE 5>.位图索引: CREATE BITMAP INDEX 索引名 ON 表名（字段列表） 6>.基于函数的索引： CREATE INDEX 索引名 ON 表名（表达式） ex. CREATE INDEX IDXSNAME ON STUDENT(UPPER(SNAME)) 使用基于函数的索引用户需要授予Query rewrite权限。 7>.索引组织表：针对于修改很少的数据。 CREATE TABLE t_student （ s_id NUMBER NOT NULL, s_name VARCHAR2(2) ）organization index 8>.分区表的索引 本地：每个子分区建立各自独立的索引： CREATE INDEX 索引名 ON 表名 （索引列） LOCAL 全局：每个子分区建立各自独立的索引： CREATE INDEX 索引名 ON 表名 （索引列） GLOBAL 9>.索引的分区

函数

1.字符函数 lower（char）：将字符串转化为小写格式。 upper(char):将字符串转化为大写的格式。 length(char):返回字符串长度。 substr(char,m,n):取字符串子串。

触发器和内置子程序

一、触发器分类 触发器非为：数据库触发器、DML触发器 1.数据库触发器：数据库触发器是对数据库对象进行创建、删除、修改的时候触发的。 2.DML触发器：对数据库表进行DML的时候触发的触发器。 2.1：DML触发器分为： 1>.行级触发器：每条被修改的语句都触发的触发器。 ex. CREATE OR REPLACE TRIGGER EMP_TRI BEFORE UPDATE ON emp FOR EACH ROW BEGIN DBMS_OUTPUT.put_line('成功修改一条语句'); END; UPDATE EMP SET MGR = SAL+1111 WHERE MGR=4111; 以上修改了三条语句，输出三条‘成功修改一条语句’。当去掉 【 FOR EACH ROW】后，三条数据被修改，输出一条‘成功修改~~~’（触发器只执行一次） 在行级触发器中有两个内置对象（NEW和OLD） 2>.语句级触发器： FOR EACH ROW 指定触发器是行级触发器，弱不指定，则为语句级触发器。 3>.替代触发器： 二、触发器管理 1.ALTER TRIGGER 触发器名称 DISABLE|ENABLE 启用或禁用触发器。 2.如果一个表上的触发器很多，可以用以下方式对一个表上的所有触发器一起启用和禁用。 ALERT TABLE 表名 DISABLE ENABLE ALL TRIGGER.

PL/SQL

PL/SQL(Procedural Language/SQL):过程化SQL语言。 一、PL/SQL语法 从版本6开始（1988年）PL/SQL就被可靠的整合到ORACLE中。pl/sql不是一个独立的产品，它是整个到ORACLE服务器和ORACLE工具中的技术。 1.pl/sql的特点。 ①.PL/SQL是一种基于事务处理的语言，能运行到任何oracle环境中，支持所有的数据处理命令。 ②.在该技术中，可以明确的指定COMMIT、ROLLBACK、SAVESPOINT语句。 ③.支持所有的sql数据类型和所有的sql函数。 ④.可以被命名的存储在oracle服务器中。 ⑤.可以用任何ASCII文本编辑器编写。 ⑥.对于SQL，ORACLE必须在同一时间处理每一条SQL语句。在网络环境下，这意味着作为每一个调用都必须被oracle服务器处理，这就占用了大量的服务器时间，容易导致 网络拥挤。而pl/sql是以整个语句块发给服务器，降低了网络拥挤。 2.PL/SQL的结构。 [DECLARE] --声明部分 BEGIN --执行部分 [EXCEPTION] --异常处理部分 END 备注：[]括号内容是可选的。PL/SQL块中每一条语句都必须以分号结束。 3.pl/sql中的逻辑类型。 3.1.空值：NULL 3.2.对象：ORACLE是一个面向对象的数据库。pl/sql拥有对象类型。 3.3.属性类型： @TYPE：获取累的数据类型。 %ROWTYPE:获取表的行结构，可以作为一个对象的类型。 4.常量变量的声明： 名字 [CONSTANT] 数据类型 [NOT NULL] [:=|DEFAULT 具体的值] ex. DECLARE s_name VARCHAR2(20); --声明变量 s_age CONSTANT NUMBER DEFAULT 20; --声明常量，以‘DEFAULT value’方式赋值。赋值还可以用 ‘：=’ s_age CONSTANT NUMBER ：=20 BEGIN s_name:='李超'; DBMS_OUTPUT.put_line('我叫：'||s_name||',今年：'||s_age||'岁。'); END; 5.pl/sql中的控制语句。 5.1. 条件控制语句语法： IF 条件表达式 THEN 执行语句 END IF; 分支条件： IF...THEN...ELSIF...THEN...ELSE...THEN...END IF; CASE 字符变量 WHEN 结果1 THEN 执行语句； WHEN 结果2 THEN 执行语句； WHEN 结果2 THEN 执行语句； .... ELSE 执行语句； END CASE; 或者 CASE WHEN 条件表达式1 THEN 执行语句； WHEN 条件表达式2 THEN 执行语句； ELSE 执行语句 END CASE; 5.2. 循环语句。 LOOP EXIT WHEN 条件表达式； [执行语句] END LOOP; FOR 变量 IN 起始值...结束值 LOOP 执行语句 END LOOP; 备注：在for循环中，变量的定义不用声明，变量的边界值都会进入循环。 5.3. 跳转控制 ex. DECLARE num NUMBER DEFAULT 12; BEGIN IF num>10 THEN GOTO A; ELSE GOTO B; END IF; <<A>> DBMS_OUTPUT.put_line('first'); RETURN; <<B>> DBMS_OUTPUT.put_line('second'); RETURN; END; 5.4 PL/SQL记录 TYPE <类型名> IS RECORD <列名1 类型1,列名2 类型2,列名3 类型3,...列名n 类型n,> ex. DECLARE TYPE mytype IS RECORD(aa EMP.EMPNO%TYPE,bb EMP.ENAME%TYPE); myemp mytype; BEGIN SELECT empno,ename INTO myemp FROM EMP WHERE empno = 7369; DBMS_OUTPUT.put_line('编号：'||myemp.aa); DBMS_OUTPUT.put_line('姓名：'||myemp.bb); END; 6.异常 6.1异常分为内部异常和用户自定义异常。 内部异常： Oracle对有些错误码的异常提供了错误名称，这种异常称为预定义异常，但有绝大多数异常好吗没提供错误名称，我们称为非预定义异常。 6.2.预定义异常 Oracle定义了65535个错误序号，每个错误序号对应一个系统内部错误定义。 ex. 错误序号 异常名称 异常说明 ORA-001 DUP_VAL_ON_INDEX 试图破坏一个唯一性限制 6.3. 自定义异常 语法： DECLARE myexception EXCEPTION; BEGIN RAISE myexception --抛出异常 EXCEPTION WHEN myexception THEN 执行语句 END; 6.4 非预定义异常。 使用非预定义异常，首先定义一个自定义异常，然后将异常变量与一个异常编号关联 ex.DECLARE myexc EXCEPTION; PRAGMA EXCEPTION_INIT(myexc ,-1476) ; --ORA-1476 ：被零除的错误编号。 num1 NUMBER DEFAULT 2; BEGIN num1 := num1/0; EXCEPTION WHEN myexc THEN DBMS_OUTPUT.put_line('被除数不能为0'); END; 6.5 RAISE_APPLICATION_ERROR:抛出自定义比较详细的异常信息 语法： RAISE_APPLICATION_ERROR（错误编码，'错误消息'）; 错误编码：用户指定的编号，范围在-20000~-20999之间。 错误消息：用户定义的消息，消息长度为2048。 ex. DECLARE num NUMBER DEFAULT 2; BEGIN IF num=2 THEN RAISE_APPLICATION_ERROR(-20005,'测试！'); END IF; END; 异常传播。 raise； 将重新抛出异常。 6.6 动态SQL 我们可以在pl/sql里执行dml( Data Manipulation Language数据操作语言)，却不能执行DDL（Data Definition Language数据定义语言）。 动态构造一个sql语句，而这些语句都是字符串， 在pl/sql中把这种直接执行这写字符串的查询语句的操作称为动态sql。 语法： EXECUTE IMMEDIATE 'SQL语句' 1.创建表： ex. DECLARE myid NUMBER :=1; myname VARCHAR2(20) :='curtain'; BEGIN EXECUTE IMMEDIATE 'CREATE TABLE t_student(s_id NUMBER PRIMARY KEY ,s_name VARCHAR2(20))'; EXECUTE IMMEDIATE 'INSERT INTO t_student VALUES (:1,:2)' USING myid,myname; --':1,:2'为占位符。用USING引入参数。 END;

游标

一、游标的分类 根据使用特性可以分为： 静态游标：静态游标是在游标声明时就已经确定了游标的结构和内容。 引用游标：引用游标好比一个变量，可以在使用的过程中设定结构和内容。 二、游标的属性 Oracle游标有4个属性：%ISOPEN 、%FOUND 、%NOTFOUND、%ROWCOUNT。 %ISOPEN ：判断游标是否被打开，是为TRUE，否则为FALSE。 %FOUND 、%NOTFOUND ：判断右边所在的行是否有效。有效，则FOUND为TRUE。 %ROWCOUNT :返回当前位置止，游标读取的记录行数。 三、静态游标 1.隐式游标： 运行DML语句时、PL/SQL打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，运行DML语句时打开，完成后关闭。 隐式游标只使用了SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT三个属性，SQL%FOUND插入数据、至少有一行被DELETE或UPDATE、SELECT...INTO是为TRUE。 SQL%ROWCOUNT在执行DML语句前为NULL对于SELECT...INTO语句，执行成功值为1.否者为0，同时产生异常NO_DATA_FOUND。执行其他如INSERT等时、值为影 响的记录数，为0时，值为0. SQL%ISOPEN由于DML语句执行时打开、执行完关闭，所以，值总是FALSE. 2.显示游标：当查询返回超过一行时并且需要逐条数据进行处理的时候，就需要一个显示游标。 显示游标一定要关联一个查询结果集,并且在游标定义的时候就跟这个结果集绑定在一起。 ex.DECLARE CURSOR empcur IS SELECT * FROM EMP; --定义游标获得结果集 theemp emp%rowtype; --定义变量，用于接收每一行。 BEGIN OPEN empcur; --打开游标 LOOP FETCH empcur INTO theemp; --遍历游标 EXIT WHEN empcur%NOTFOUND; DBMS_OUTPUT.put_line('编号：'||theemp.empno||', 姓名：'||theemp.ename); END LOOP; CLOSE empcur; --关闭游标 END; 3.带参数游标： ex.DECLARE CURSOR empcur(cur_deptno emp.deptno%TYPE) IS SELECT * FROM EMP WHERE deptno=cur_deptno; --定义游标获得结果集 theemp emp%rowtype; --定义变量，用于接收每一行。 BEGIN OPEN empcur(20); --打开游标 LOOP FETCH empcur INTO theemp; --遍历游标 EXIT WHEN empcur%NOTFOUND; DBMS_OUTPUT.put_line('编号：'||theemp.empno||', 姓名：'||theemp.ename); END LOOP; CLOSE empcur; --关闭游标 END; 4.循环游标：操作游标的过程中始终打开游标。 语法： DECLARE CURSOR 游标变量 IS 游标的查询语句 BEGIN FOR 查询变量 IN 游标变量 LOOP 执行语句 END LOOP; END; 备注：查询变量只能取数据，不能给查询变量赋值。 四、利用游标进行修改数据。 为了避免事务的冲突的可能性，需要在打开游标的时候进行数据的锁定 --定义时锁定 CURSOR 游标名称 IS 查询语句 FOR UPDATE; --对当前游标进行删除和修改的语法 UPDATE 语句 WHERE CURRENT OF 游标名称； ex. DECLARE CURSOR empcur(cur_deptno emp.deptno%type) IS SELECT * FROM EMP WHERE deptno=cur_deptno FOR UPDATE; --定义游标获得结果集 theemp emp%rowtype; --定义变量，用于接收每一行。 BEGIN FOR EMPROW IN empcur(20) LOOP UPDATE EMP SET SAL = SAL+1 WHERE CURRENT OF empcur; END LOOP; END; 五、引用游标 无法在游标声明的时候就确定查询语句时，可使用引用游标。 1.强类型游标：声明时确定了游标的结构。 语法： DECLARE TYPE 游标类型名称 IS REF CUROSR; 游标变量名称 游标类型名称； BEGIN OPEN 游标变量名称 FOR 查询语句； END; 2.弱类型游标：声明时没有确定游标的结构。 语法: DECLARE TYPE 游标类型名称 IS REF CUROSR RETURN EMP%ROWTYPE; 游标变量名称 游标类型名称； BEGIN OPEN 游标变量名称 FOR 查询语句； END; ex. DECLARE TYPE mycur IS REF CURSOR RETURN SCOTT.EMP%ROWTYPE; cur mycur; myemp EMP%ROWTYPE; BEGIN OPEN cur FOR SELECT * FROM EMP WHERE DEPTNO=20; LOOP FETCH cur INTO myemp; EXIT WHEN cur%NOTFOUND; DBMS_OUTPUT.put_line('编号：'||myemp.empno||',姓名:'||myemp.ename||',工资:'||myemp.sal); END LOOP; CLOSE cur; END; 强类型游标指定了游标的结构，弱结构不一致会产生异常。

过程和程序包

一、存储过程 1.过程的定义： 语法： CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS|AS [局部变量去] BEGIN 可执行语句 [EXCEPTION] 异常处理语句 END [<过程名>]; 调用： 命令窗口下调用； EXECUTE 过程名。 SQL编辑区调用 ： BEGIN 过程名; END; 2.带参数的过程。 在pl/sql过程中，有三中类型的参数： IN参数，读入参数，调用向过程传递参数。 OUT参数，输出参数，过程向调用传递参数。 IN OUT参数，双向参数。 ex.CREATE OR REPLACE PROCEDURE proc_emp(c_empno NUMBER ,c_empsal IN OUT NUMBER) IS BEGIN DBMS_OUTPUT.put_line('传入结果：'||c_empsal); --3000 SELECT SAL INTO c_empsal FROM EMP WHERE empno=c_empno; DBMS_OUTPUT.put_line('执行后结果：'||c_empsal); --810 END ; --调用，传递参数，获取参数。 DECLARE thesal NUMBER; BEGIN thesal := 3000; proc_emp(7369,thesal); DBMS_OUTPUT.put_line('调用结果：'||thesal); --810 END; 二、函数 语法： CREATE [OR REPLACE ] FUNCTION 函数名称[<参数列表{IN|OUT|IN OUT}>] RETURN 返回类型 {IS|AS} 函数的执行语句。 函数类似于过程。 1.通过OUT参数，函数也可以返回多个值。 函数的两种调用方式： 1>.在SQL语句中调用，和系统的内置函数一样使用。 ex. CREATE OR REPLACE FUNCTION myadd(a INT ,b INT)RETURN INT AS BEGIN RETURN A+B; END; 1.SELECT MYADD(3,3) FROM DUAL; 2.在PL/SQL中使用，调用必须使用一个参数接收。 DECLARE RESULT NUMBER; BEGIN RESULT :=myadd(10,4); DBMS_OUTPUT.put_line(RESULT); END; 三、包 包就是一个命名的声明部分。任何可以出现在声明中的语句都可以在包中使用，如过程、函数、游标、类型以及变量。 1.包的说明。 包（也叫做包头），不包含包的代码部分。 2.包体。 包体只能在包头完成编译后才能编译、包中的子过程或游标在包体中必须实现才能编译通过。 --创建包 CREATE OR REPLACE PACKAGE pkg_test --包中只出现声明部分 AS myemp EMP%ROWTYPE; PROCEDURE upp_emp; FUNCTION addnum(n INT) RETURN INT; CURSOR allemp IS SELECT * FROM EMP; END pkg_test; --创建包体 CREATE OR REPLACE PACKAGE BODY pkg_test AS PROCEDURE upp_emp IS BEGIN UPDATE EMP SET SAL=SAL+100 WHERE EMPNO=7369; DBMS_OUTPUT.put_line('过程执行完成！'); OPEN allemp; LOOP FETCH allemp INTO myemp; DBMS_OUTPUT.put_line('编号:'||myemp.empno||'姓名:'||myemp.ename); END LOOP; END ; FUNCTION addnum(n INT) RETURN INT IS BEGIN RETURN n*n; END addnum; END pkg_test; 调用格式和普通过程、函数一样，不同的是在调用的函数或过程前需加上包名作前缀。 包中子过程可以重载。 四、自主事务。 CREATE OR REPLACE PROCEDURE pro_1 AS pragma autonomous_transaction; mysal NUMBER; BEGIN SELECT SAL INTO mysal FROM EMP WHERE EMPNO = 7369; DBMS_OUTPUT.put_line('子过程中：'||mysal); ROLLBACK; END; CREATE OR REPLACE PROCEDURE pro_2 AS mysal NUMBER; BEGIN SELECT SAL INTO mysal FROM EMP WHERE EMPNO = 7369; DBMS_OUTPUT.put_line('调用前：'||mysal); UPDATE EMP SET SAL = SAL+6666 WHERE EMPNO = 7369; pro_1(); SELECT SAL INTO mysal FROM EMP WHERE EMPNO = 7369; DBMS_OUTPUT.put_line('调用后：'||mysal); END; BEGIN pro_2(); END; 结果：调用前：811 子过程中：7477 调用后：811 备注：子过程的事务会影响父过程的事务，为了使子过程和父过程事务相对独立，不受影响，可以在被调用过程的声明中加上 pragma autonomous_transaction; 如上红色部分。