1 什么是存储过程（事先经过编译并存储在数据库中的一套 sql 语句）
1.1存储过程（Stored Procedure）
是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。
1.2存储过程的优点
(1) 提高 sql 执行效率 ：‘预编译’ 后存储于内存中，故使用时，无需再次编译
(2) 减少 ‘网络流量(I/0)’：减少 sql 与服务器之间的上下文交换
(3) 提高系统的 ‘安全性’ ：减少 sql 注入的危险、权限控制
1.3存储过程的缺点
(1) 增加数据库服务器的 ‘负荷’
2 语法
2.1 创建
create or replace procedure schema.过程名(变量名 in|out 数据类型)
AS/IS -- 此处 is 和 as 等同
声明语句段;
begin
执行语句段;
exception
异常处理语句段;
end 过程名;
--注：
--在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别；
--在视图(VIEW)中只能用AS不能用IS；
--在游标(CURSOR)中只能用IS不能用AS。
--as：关键字。
--begin：关键字。
--dbms_output.put_line();输出内容。
--end;关键字。
--声明declare关键字
示例
-- 示例：两数相除并输出结果
-- 功能说明：计算两数相除
-- 参数说明: i_a 除数
-- i_b 被除数
-- o_result 结果
-- io_flag 程序执行标识
create or replace procedure scott.p_procedure_demo(i_a in number,
i_b in number,
o_result out number,
io_flag in out varchar2) is
v_message varchar2(30) := '存储过程模板';
begin
io_flag := 'Y';
-- i_b = 0 时，可验证报错
o_result := i_a / i_b;
-- 格式调整 1/2 = .5 => 0.5
o_result := regexp_replace(o_result, '^\.', '0.');
exception
when others then
io_flag := 'N';
dbms_output.put_line(sqlcode || ', ' || sqlerrm);
dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/
无参存储过程
CREATE OR REPLACE PROCEDURE demo AS/IS
变量2 DATE;
变量3 NUMBER;
BEGIN
--要处理的业务逻辑
EXCEPTION --存储过程异常
END
带参数的存储过程
CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
AS/IS
name student.name%TYPE;
age number :=20;
BEGIN
--业务处理.....
END
--第1行：param1 是参数，类型和student表id字段的类型一样。
--第3行：声明变量name，类型是student表name字段的类型（同上）。
--第4行：声明变量age，类型数数字，初始化为20
带参数的存储过程并且进行赋值
CREATE OR REPLACE PROCEDURE 存储过程名称(
s_no in varchar,
s_name out varchar,
s_age number) AS
total NUMBER := 0;
BEGIN
SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
dbms_output.put_line('符合该年龄的学生有'||total||'人');
EXCEPTION
WHEN too_many_rows THEN
DBMS_OUTPUT.PUT_LINE('返回值多于1行');
END
--其中参数IN表示输入参数，是参数的默认模式。
--OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
--OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
--IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去
2.2参数说明（in | out）：默认 in，可省略
--1. 无参数时: 不用带 '()'
scott.p_procedure_demo
--2. 有参数时: '固定参数类型' 或 '动态参数类型'（推荐，不用考虑变量的数据类型）
--(1) 仅有入参 in：默认，可以省略
scott.p_procedure_demo(p_ename in varchar2)
scott.p_procedure_demo(p_ename in scott.emp.ename%type)
--(2) 仅有出参 out：不可以省略
scott.p_procedure_demo(p_ename out scott.emp.ename%type)
--(3) 既有入参 in，也有出参 out
-- 多个参数时
scott.p_procedure_demo(p_empno in scott.emp.empno%type,
p_ename out scott.emp.ename%type)
-- 一个参数时：既是入参，也是出参
scott.p_procedure_demo(p_empno in out scott.emp.empno%type)
2.3 调用
在 pl/sql 中调用存储过程(‘任何情况均适用’)
declare--可省略
begin
procedure_name; -- 若有参，则加上 "()"
end;
在命令窗口(请注意：仅在 ‘无参’ 或 ‘仅有入参’ 时适用)
exec procedure_name;
execute procedure_name; -- exec 是 execute 的缩写
call procedure_name();
-- exec 和 call 的区别：
-- exec 只能在命令窗口执行
-- call 即可以在命令窗口执行又可以在 pl/sql 程序窗口中执行，但必须有括号，即使没有参数
异常
create or replace procedure myDemo03
as
age int;
begin
age:=1/0;
dbms_output.put_line(age);
--异常
exception when others then
dbms_output.put_line('error');
end;
call myDemo03();
while 循环
create or replace procedure myDemo5
as
n_count number := 0;
begin
while n_count < 5 loop
dbms_output.put_line(n_count);
n_count := n_count + 1;
end loop;
end;
begin
myDemo5;
end;
for循环
create or replace procedure myDemo06
as
begin
FOR USE in (select * from T_USER_INFO) loop
if (USE.id<3) then
dbms_output.put_line(USE.USER_NAME);
end if;
end loop;
end;
CALL myDemo06();
基本CRUD
create or replace procedure mydemo07(ID in int, user_name in varchar,pssword in varchar, created_date in DATE,created_by IN varchar,UPDATED_date IN DATE,UPDATED_by IN varchar,is_valid IN varchar)
as
begin
insert into T_USER_INFO VALUES(ID,user_name,pssword,created_date,created_by,UPDATED_date,UPDATED_by,is_valid);
--UPDATE T_USER_INFO t SET t.USER_NAME='sunqi' WHERE t.ID=ID ;
--DELETE T_USER_INFO t WHERE t.ID=ID ;
commit; --提交
end;
begin
mydemo07(3,'ex_sunqi','666666',SYSDATE,'ex_sunqi',SYSDATE,'ex_sunqi','Y');
end;
2.4 修改、删除
4. create or replace... -- 支持重复操作
5. drop procedure scott.p_procedure_demo; -- 删除
Debug
6. 选中过程名，'添加调试信息'
-- 若已添加，则跳过此步骤（若没有，则不会进入该过程）
7. 选择 '测试'，进入 '测试窗口'
8. 调试（Debug）
3.存储过程举例
3.1 运算符
3.2 SELECT INTO STATEMENT语句
CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
s_name VARCHAR2; --学生名称
s_age NUMBER; --学生年龄
s_address VARCHAR2; --学生籍贯
BEGIN
--给单个变量赋值
SELECT student_address INTO s_address
FROM student where student_grade=100;
--给多个变量赋值
SELECT student_name,student_age INTO s_name,s_age
FROM student where student_grade=100;
--输出成绩为100分的那个学生信息
dbms_output.put_line('姓名：'||s_name||',年龄：'||s_age||',籍贯：'||s_address);
END
3.3 选择语句（IF…END IF）
IF s_sex=1 THEN --1-男生；0-女生
dbms_output.put_line('这个学生是男生');
END IF
b.IF..ELSE..END IF
IF s_sex=1 THEN
dbms_output.put_line('这个学生是男生');
ELSE
dbms_output.put_line('这个学生是女生');
END IF
3.4 循环语句
--a.Loop基本循环
LOOP
IF 表达式 THEN
EXIT;
END IF
END LOOP;
--b.while循环
WHILE 表达式 LOOP
dbms_output.put_line('haha');
END LOOP;
--c.for循环1
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
--c.for循环2
create or replace procedure myDemo06
as
begin
FOR emp in (select * from employees) loop
if (emp.EMPLOYEE_ID=104) then
dbms_output.put_line(emp.LAST_NAME );
end if;
end loop;
end;
3.5 游标
Oracle会创建一个存储区域，被称为上下文区域，用于处理SQL语句，其中包含需要处理的语句，例如所有的信息，行数处理，等等。
游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。
常用的游标属性：
| 属性 | 描述 |
|---|---|
| %FOUND | 如果DML语句执行后影响有数据被更新或DQL查到了结果，返回true。否则，返回false。 |
| %NOTFOUND | 如果DML语句执行后影响有数据被更新或DQL查到了结果，返回false。否则，返回true。 |
| %ISOPEN | 游标打开时返回true，反之，返回false。 |
| %ROWCOUNT | 返回DML执行后影响的行数。 |
使用游标
--声明游标定义游标的名称和相关的SELECT语句：
CURSOR cur_cdd IS SELECT s_id, s_name FROM student;
--打开游标游标分配内存，使得它准备取的SQL语句转换成它返回的行：
OPEN cur_cdd;
--抓取游标中的数据，可用LIMIT关键字来限制条数，如果没有默认每次抓取一条：
FETCH cur_cdd INTO id, name ;
--关闭游标来释放分配的内存:
CLOSE cur_cdd;
4.案例实战
有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。
如果我们直接运行运行这条sql，因数据量太大会把数据库undo表空间撑爆，从而发生异常。那我们来写个存储过程，进行批量更新，我们每10万条提交一次。
CREATE OR REPLACE PROCEDURE process_student is
total NUMBER := 0;
i NUMBER := 0;
BEGIN
SELECT COUNT(1) INTO total FROM student;
WHILE i<=total LOOP
UPDATE student SET grade=grade+1 WHERE s_no=i;
i := i + 1;
IF i >= 100000 THEN
COMMIT;
END IF;
END LOOP;
dbms_output.put_line('finished!');
END;
5.存储过程进阶
在上面的案例中，我们的存储过程处理完所有数据要多长时间呢？事实我没有等到它执行完，在我可接受的时间范围内它没有完成。那么对于处理这种千万级数据量的情况，存储过程是不是束手无策呢？答案是否定的，接下来我们看看其他绝招。
我们先来分析下执行过程的执行过程：一个存储过程编译后，在一条语句一条语句的执行时，如果遇到pl/sql语句就拿去给pl/sql引擎执行，如果遇到sql语句就送到sql引擎执行，然后把执行结果再返回给pl/sql引擎。遇到一个大数据量的更新，则执行焦点（正在执行的，状态处于ACTIVE）会不断的来回切换。
Pl/SQL与SQL引擎之间的通信则称之为上下文切换，过多的上下文切换将带来过量的性能负载。最终导致效率降低，处理速度缓慢。
从Oracle8i开始PL/SQL引入了两个新的数据操纵语句：FORALL、BUIK COLLECT，这些语句大大滴减少了上下文切换次数（一次切换多次执行），同时提高DML性能，因此运用了这些语句的存储过程在处理大量数据时速度简直和飞一样。
1.BUIK COLLECT
Oracle8i中首次引入了Bulk Collect特性，Bulk Collect会能进行批量检索，会将检索结果结果一次性绑定到一个集合变量中，而不是通过游标cursor一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT，接下来我们一起看看这些语句中是如何使用BULK COLLECT的。
(1).SELECT INTO
查出来一个结果集合赋值给一个集合变量。
语法结构是：
SELECT field BULK COLLECT INTO var_conllect FROM table where colStatement;
说明：
field：要查询的字段，可以是一个或多个（要保证和后面的集合变量要向对应）。
var_collect：集合变量（联合数组等），用来存放查到的结果。
table：表名，要查询的表。
colStatement：后面过滤条件语句。比如s_age < 10;
例子：查出年龄小于10岁的学生姓名赋值给数组arr_name变量
SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;
(2).FETCH INTO
从一个集合中抓取一部分数据赋值给一个集合变量。
语法结构如下：
FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]
说明：
cur1：是个数据集合，例如是个游标。
var_collect：含义同上。
[LIMIT rows]：可有可无，限制每次抓取的数据量。不写的话，默认每次一条数据。
例子：给年龄小于10岁的学生的年级降一级。
--查询年龄小于10岁的学生的学号放在游标cur_no里
CURSOR cur_no IS
SELECT s_no FROM student WHERE s_age < 10;
--声明了一个联合数组类型，元素类型和游标cur_no每个元素的类型一致
TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;
--声明一个该数组类型的变量no
no ARR_NO;
BEGIN
FETCH cur_no BULK COLLECT INTO no LIMIT 100;
FORALL i IN 1..no.count SAVE EXCEPTONS
UPDATE student SET s_grade=s_grade-1 WHERE no(i);
END;
说明：先查出年龄小于10岁的学生的学号放在游标里，再每次从游标里拿出100个学号，进行更新，给他们的年级降一级。
(3).RETURNING
BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用，可以返回这些DML语句执行后所影响的记录内容（某些字段）。
再看一眼学生表的字段情况：student(s_no, s_name, s_age, s_grade)
语法结构如下：
DMLStatement
RETURNING field BULK COLLECT INTO var_field;
说明：
DMLStatement：是一个DML语句。
field：是这个表的某个字段，当然也可以写多个逗号隔开（field1，field2, field3）。
var_field：一个类型为该字段类型的集合，多个的话用逗号隔开，如下：
（var_field1, var_field2, var_field3）
例子：获取那些因为年龄小于10岁而年级被将一级的学生的姓名集合。
TYPE NAME_COLLECT IS TABLE OF student.s_name%TYPE;
names NAME_COLLECT;
BEGIN
UPDATE student SET s_grade=s_grade-1 WHERE s_age < 10
RETURNING s_name BULK COLLECT INTO names;
END;
说明：
NAME_COLLECT：是一个集合类型，类型是student表的name字段的类型。
names：定义了一个NAME_COLLECT类型的变量。
(4).注意事项
a.不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
b.只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
c.BULK COLLECT INTO 的目标对象必须是集合类型。
d.复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
e.如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO 子句中使用。
f.如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。
2.FORALL
(1).语法
FORALL index IN bounds [SAVE EXCEPTIONS]
sqlStatement；
说明：
index是指下标；
bounds是一个边界，形式是start..end
[SAVE EXCEPTIONS] 可写可不写，这个下面介绍；
sqlStatement是一个DML语句，这里有且仅有一个sql语句；
例子：
–例子1：移除年级是5到10之间的学生
FORALL i IN 5..10
DELETE FROM student where s_grade=i;
–例子：2，arr是一个数组,存着要升高一年级的学生名称
FORALL s IN 1..arr.count SAVE EXCEPTIONS
UPDATE student SET s_grade=s_grade+1 WHERE s_name=arr(i);
(2).SAVE EXCEPTIONS
通常情况写我们在执行DML语句时，可能会遇到异常，可能致使某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句，那么DML语句会在执行到一半的时候停下来。
如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句，当在执行过程中如果遇到异常，数据处理会继续向下进行，发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中，该游标属性是个记录集合，每条记录有两个字段，例如：（1, 02300）;
ERROR_INDEX：该字段会存储发生异常的FORALL语句的迭代编号；
ERROR_CODE：存储对应异常的，oracle错误代码；
SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。而这个异常记录集合异常的个数则由它的COUNT属性表示，即:
SQL%BULK_EXCEPTONS.COUNT，SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。
(3). INDICES OF
在Oracle数据库10g之前有一个重要的限制，该数据库从IN范围子句中的第一行到最后一行，依次读取集合的内容，如果在该范围内遇到一个未定义的行，Oracle数据库将引发ORA-22160异常事件：ORA-22160: element at index [N] does not exist。针对这一问题，Oracle后续又提供了两个新语句：INDICES OF 和 VALUES OF。
接下来我们来看看这个INDICES OF语句，用于处理稀疏数组或包含有间隙的数组（例如：一个集合的某些元素被删除了）。
该语句语法结构是：
FORALL i INDICES OF collection [SAVE EXCEPTIONS]
sqlStatement;
说明：
i：集合（嵌套表或联合数组）下标。
collection：是这个集合。
[SAVE EXCEPTIONS]和sqlStatement上面已经解释过。
例子：arr_std是一个联合数组，每个元素包含（name,age,grade）,现在要向student表插入数据。
FORALL i IN INDICES OF arr_stu
INSERT INTO student VALUES(
arr_stu(i).name,
arr_stu(i).age,
arr_stu(i).grade
);
(4). VALUES OF
VALUES OF适用情况：绑定数组可以是稀疏数组，也可以不是，但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是，VALUES OF在使用时有一些限制：
如果VALUES OF子句中所使用的集合是联合数组，则必须使用PLS_INTEGER和BINARY_INTEGER进行索引，VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER；
当VALUES OF 子句所引用的集合为空，则FORALL语句会导致异常；
该语句的语法结构是：
FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
sqlStatement;
说明：i和collection含义如上
联合数组请看文章（或自行百度）：https://blog.csdn.net/leshami/article/details/7372061
3.pl/sql调试存储过程
首先，当前这个用户得有能调试存储过程的权限，如果没有的话，以数据库管理员身份给你这个用户授权：
–userName是你要拿到调试存储过程权限的用户名
GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;
4.案例实战
场景和上面的案例实战是同一个，如下：
有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。
这条sql，写出来如下：
update student set s_grade=s_grade+1
编写存储过程：
(1).存储过程1
名称为：process_student1，student表的s_no字段类型为varchar2(16)。
CREATE OR REPLACE PROCEDURE process_student1 AS
CURSOR CUR_STUDENT IS SELECT s_no FROM student;
TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
students REC_STUDENT;
BEGIN
OPEN CUR_STUDENT;
WHILE (TRUE) LOOP
FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
FORALL i IN 1..students.count SAVE EXCEPTIONS
UPDATE student SET s_grade=s_grade+1 WHERE s_no=students(i);
COMMIT;
EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
END LOO;
dbms_output.put_line('finished');
END;
说明：
把student表中要更新的记录的学号拿出来放在游标CUR_STUDENT，每次从这个游标里抓取10万条数据赋值给数组students，每次更新这10万条记录。循环进行直到游标里的数据全部抓取完。
FETCH .. BULK COLLECT INTO .. LIMIT rows语句中：这个rows我测试目前最大可以为10万条。
(2).存储过程2（ROWID）
如果我们这个student表没有主键，也没有索引呢，该怎么来做呢？
分析下：
ROWNUM是伪列，每次获取结果后，然后在结果集里会产生一列，从1开始排，每次都是从1开始排。
ROWID在每个表中，每条记录的ROWID都是唯一的。在这种情况下，我们可以用ROWID。但要注意的是，ROWID是一个类型，注意它和VARCHAR2之间的转换。有两个方法：ROWIDTOCHAR（）是把ROWID类型转换为CHAR类型；CHARTOROWID()是把CAHR类型转换为ROWID类型。
接下来我们编写存储过程process_student2，脚本如下：
CREATE OR REPLACE PROCEDURE process_student1 AS
CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
students REC_STUDENT;
BEGIN
OPEN CUR_STUDENT;
WHILE (TRUE) LOOP
FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
FORALL i IN 1..students.count SAVE EXCEPTIONS
UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
COMMIT;
EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
END LOO;
dbms_output.put_line('finished');
END;
说明：
我们首先查到记录的ROWID并把它转换为CHAR类型，存放到游标CUR_STUDENT里，
再每次抓取10万条数据赋值给数组进行更新，更新语句的WHERE条件时，又把数组元素是CAHR类型的rowid串转换为ROWID类型。
6 Oracle中rownum的基本用法
mysql中我们会想到用limit,然而oracle中没有limit用rownum可以解决这一问题。
基本使用
注意：rownum不支持>, >=, =, !=, between…and…这几个运算符，只能用符号(<、<=)
ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。
它取得第一条记录则rownum值为1，第二条为2，依次类推。
如果你用>,>=,=,between…and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除， 接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。(select id,name from table where rownum between 1 and 5这种情况除外)
示例：查询表3条以后的记录
先把rownum的列与table"拼接"作为一个子表来查询，这样rownum所在的列就是代表一个属性（重点是取别名，不取别名它依然是按照以前那种序列排序）
select *
from (
select ROWNUM r,employees.*
from employees
) where r > 3
结果
R EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
4 103 Alexander Hunold AHUNOLD 590.423.4567 1990/1/3 IT_PROG 9000.00 102 60
5 104 Bruce Ernst BERNST 590.423.4568 1991/5/21 IT_PROG 6000.00 103 60
6 107 Diana Lorentz DLORENTZ 590.423.5567 1999/2/7 IT_PROG 4200.00 103 60
选取列表中某个值前3的所有记录
select *
from employees where ROWNUM < 3 order by employee_id desc
建议这样写
select *
from (
select distinct * from employees order by employee_id desc
) where rownum <= 3
选取区间[a,b]的记录
这里的思想和第一个例子思想是一样的，先把rownum的列与table"拼接"作为一个子表来查询，这样rownum所在的列就是代表一个属性（重点是取别名，不取别名它依然是按照以前那种序列排序）在对这个值进行between 处理；
分页查询
SELECT * FROM (
SELECT e.*,ROWNUM r FROM employees e WHERE ROWNUM <= 30(pagenumber*pagesize+pagesize)
) WHERE r >= 25(pagenumber*pagesize)
7 参考游标(refcursor)的使用
我们知道在sql server中可以直接在存储过程中使用select * from 来产生记录集，但在oracle的存储过程里却不可以，可以用参考游标来实现，作用是允许在存储过程，函数，包中返回记录集。
首先定义一个参考游标：
TYPE ref_type_name IS REF CURSOR
[RETURN {cursor_name%ROWTYPE
|ref_cursor_name%ROWTYPE
|record_name%TYPE
|record_type_name
|table_name%ROWTYPE} ];
如：
CREATE OR REPLACE PACKAGE Types AS
TYPE cursor_type IS REF CURSOR;
END Types;
/
然后在存储过程中引用这个游标：
CREATE OR REPLACE
PROCEDURE GetEmpRS (p_deptno IN emp.deptno%TYPE,
p_recordset OUT Types.cursor_type) AS
BEGIN
OPEN p_recordset FOR
SELECT ename,
empno,
deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;
/
Oracle 9i及以后版本，可省略第一步的定义，用sys_refcursor来代替：
CREATE OR REPLACE
PROCEDURE GetEmpRS (p_deptno IN emp.deptno%TYPE,
p_recordset OUT sys_refcursor ) AS
BEGIN
OPEN p_recordset FOR
SELECT ename,
empno,
deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;
/
在程序中如何调用呢？
ADO中：
Dim conn, cmd, rs
Set conn = Server.CreateObject("adodb.connection")
conn.Open "DSN=TSH1;UID=scott;PWD=tiger"
Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "GetEmpRS"
cmd.CommandType = 4 'adCmdStoredProc
Dim param1
Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = 30
Set rs = cmd.Execute
Do Until rs.BOF Or rs.EOF
-- Do something
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = nothing
Set param1 = nothing
Set cmd = nothing
Set conn = nothing
Java中：
import java.sql.*;
import oracle.jdbc.*;
public class TestResultSet {
public TestResultSet() {
try {
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@w2k1", "scott", "tiger");
CallableStatement stmt = conn.prepareCall("BEGIN GetEmpRS(?, ?); END;");
stmt.setInt(1, 30); // DEPTNO
stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
stmt.execute();
ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
while (rs.next()) {
System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno"));
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
}
catch (SQLException e) {
System.out.println(e.getLocalizedMessage());
}
}
public static void main (String[] args) {
new TestResultSet();
}
}
8 oracle 中TYPE 关键字 IS RECORD 和 IS TABLE OF 关键字的使用（循环）
declare
--定义一个自定义类型
type aaa is record (
a_field varchar2(10),
b_field varchar2(10)
)
--定义一个存放自定义类型的集合(默认下标 index 为 number)
type aaa_array is table of aaa;
begin
--插入自定义类型数据
index := nvl(aaa_array.last,0)+1;
aaa_array(index).a_field := 'a_value';
aaa_array(index).b_field := 'b_value';
--遍历自定义类型集合aaa_array
for i in aaa_array.first .. aaa_array.last
loop
dbms_output.put_line(aaa_array(i).a_field;
end loop;
end;
/
--定义一个自插入数组并遍历 (5000 为数组长度)
declare
TYPE varray_type IS VARRAY(5000) OF varchar2(10);
v1 varray_type;
begin
v1 := varray_type('740151715',
'736045418',
'737086339',
'738277723');
for i in v1.first..v1.last loop
dbms_output.put_line(v1(i));
end loop;
end;
/
--定义一个index 为 varchar2 的map 类型并遍历(2000 为数组长度)
declare
TYPE map_type IS TABLE OF varchar2(2000) INDEX BY varchar2(50);
v_map_set map_type;
key varchar2(50) := null;
begin
--插入key-->value
map_set(v_distinct) :=v_deal_name||',null, '||v_master_service_type||','||j.category_code;
--遍历map
key:=v_map_set.first;
loop
dbms_output.put_line(v_map_set(key));
key:=v_map_set.next(key);
exit when key is null;
end loop;
end;
/
--循环一个query
for letter_rec in (
select * from ord_letter_master where status =1
)
loop
dbms_output.put_line(letter_rec.ord_letter_code);
end loop;
--循环一个syscursor
declare
--定义Oracle 游标
rec_master_cur SYS_REFCURSOR:=null;
--用于装载对应数据的游标row
rec_master brx_ord_factor_master%rowtype:=null;
begin
--打开游标
open rec_master_cur for
SELECT *
from brx_ord_factor_master bofm
where bofm.order_channel = 'RET'
and bofm.status = 'A'
and bofm.pps = in_pps;
--遍历游标
loop
fetch rec_master_cur into rec_master;
exit when rec_master_cur%NOTFOUND;
dbms_output.put_line(rec_master.ord_master_code);
end loop;
end;
/
9 添加表备注 and 添加列注释
--添加表备注
COMMENT ON TABLE b_so IS '发货订单';
复制代码 添加列备注
–添加列注释
COMMENT ON column b_so.c_customerup_id IS ‘上级经销商’;
扫一扫
558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
