鲁达 select id,wm_concat(name) name from test group by id;
sql语句等同于下面的sql语句
-------- 适用范围：8i,9i,10g及以后版本 （ MAX + decode ）
select id, max(decode(rn, 1, name, null)) || max(decode(rn, 2, ',' || name, null)) || max(decode(rn, 3, ',' || name, null)) str
from (select id,name,row_number() over(partition by id order by name) as rn from test) t group by id order by 1;
-------- 适用范围：8i,9i,10g及以后版本 （ ROW_NUMBER + LEAD ）
select id, str from (select id,row_number() over(partition by id order by name) as rn,name || lead(',' || name, 1) over(partition by id order by name) ||
lead(',' || name, 2) over(partition by id order by name) || lead(',' || name, 3) over(partition by id order by name) as str from test) where rn = 1 order by 1;
-------- 适用范围：10g及以后版本 （ MODEL ）
select id, substr(str, 2) str from test model return updated rows partition by(id) dimension by(row_number() over(partition by id order by name) as rn)
measures (cast(name as varchar2(20)) as str) rules upsert iterate(3) until(presentv(str[iteration_number+2],1,0)=0) (str[0] = str[0] || ',' || str[iteration_number+1]) order by 1;
-------- 适用范围：8i,9i,10g及以后版本 （ MAX + DECODE ）
select t.id id,max(substr(sys_connect_by_pa;,'),2)) str from (select id, name, row_number() over(partition by id order by name) rn from test) t
start with rn = 1 connect by rn = prior rn + 1 and id = prior id group by t.id
Oracle 11g 行列互换 pivot 和 unpivot 说明
select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));
注意： pivot（聚合函数 for 列名 in（类型）） ，其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers
select * from （select sum(nums) 苹果 from demo where name='苹果'）,（select sum(nums) 橘子 from demo where name='橘子'）,
（select sum(nums) 葡萄 from demo where name='葡萄'）,（select sum(nums) 芒果 from demo where name='芒果'）;
select sum(case
when name = '苹果' then
nums
else
0
end) as '苹果',
sum(case
when name = '橘子' then
nums
else
0
end) as '橘子'
from demo
unpivot 列转行
select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )
注意： unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量
select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f
PL/SQL块
PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。
PL/SQL块的结构如下：
DECLARE
--声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数
BEGIN
-- 执行部分: 过程及SQL 语句 , 即程序的主要部分
EXCEPTION
-- 执行异常部分: 错误处理
END;
其中：执行部分不能省略。
各种类型外,还提供一种称为复合类型的类型---记录和表
定义记录类型语法如下:
1 TYPE record_name IS RECORD(
2 v1 data_type1 [NOT NULL] [:= default_value ],
3 v2 data_type2 [NOT NULL] [:= default_value ],
4 ......
5 vn data_typen [NOT NULL] [:= default_value ] );
例子：
1 DECLARE
2 TYPE test_rec IS RECORD(
3 Name VARCHAR2(30) NOT NULL := '胡勇',
4 Info VARCHAR2(100));
5 rec_book test_rec;
6 BEGIN
7 rec_book.Name :='胡勇';
8 rec_book.Info :='谈PL/SQL编程;';
9 DBMS_OUTPUT.PUT_LINE||' ' );
10 END;
可以用 SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。
定义VARRY数据类型语法如下：
TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];(下标从1开始)
varray_name是VARRAY数据类型的名称，size是下整数，表示可容纳的成员的最大数量，每个成员的数据类型是element_type。默认成员可以取空值，否则需要使用NOT NULL加以限制。对于VARRAY数据类型来说，必须经过三个步骤，分别是：定义、声明、初始化。
例子：
DECLARE
--定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型
TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25);
--声明一个该VARRAY数据类型的变量
v_reg_varray REG_VARRAY_TYPE;
BEGIN
--用构造函数语法赋予初值
v_reg_varray := reg_varray_type
('中国', '美国', '英国', '日本', '法国');
DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_varray(1)||'、'
||v_reg_varray(2)||'、'
||v_reg_varray(3)||'、'
||v_reg_varray(4));
DBMS_OUTPUT.PUT_LINE('赋予初值NULL的第5个成员的值：'||v_reg_varray(5));
--用构造函数语法赋予初值后就可以这样对成员赋值
v_reg_varray(5) := '法国';
DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_varray(5));
END;
使用%TYPE
定义一个变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致，这时可以使用%TYPE。
使用%TYPE特性的优点在于：
l 所引用的数据库列的数据类型可以不必知道；
l 所引用的数据库列的数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。
DECLARE
-- 用%TYPE 类型定义与表相配的字段
TYPE T_Record IS RECORD(
T_no emp.empno%TYPE,
T_name emp.ename%TYPE,
T_sal emp.sal%TYPE );
-- 声明接收数据的变量
v_emp T_Record;
BEGIN
SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7369;
DBMS_OUTPUT.PUT_LINE
(TO_CHAR)||' '||' ' || TO_CHAR));
END;
使用%ROWTYPE
PL/SQL 提供%ROWTYPE操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
使用%ROWTYPE特性的优点在于：
l 所引用的数据库中列的个数和数据类型可以不必知道；
l 所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。
DECLARE
v_empno emp.empno%TYPE :=&no;
rec emp%ROWTYPE;
BEGIN
SELECT * INTO rec FROM emp WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE('姓名:'||'工资:'||'工作时间:');
END;
LOB类型
ORACLE提供了LOB (Large OBject)类型，用于存储大的数据对象的类型。ORACLE目前主要支持BFILE, BLOB, CLOB 及 NCLOB 类型。
BFILE (Movie)
存放大的二进制数据对象，这些数据文件不放在数据库里，而是放在操作系统的某个目录里，数据库的表里只存放文件的目录。
BLOB(Photo)
存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小<=4GB。
CLOB(Book)
存储大的字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。
NCLOB
存储大的NCHAR字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。
pl/sql三种变量
1、define（即host变量）
可以不预先声明初始化define变量，直接在sql或plsql中在字符串前面加&符号，会提示你输入变量的值，然后替换。这种情况下是临时的变量
define x=occ_file;
select * from &x where rownum=1;
2、Variable（即邦定变量）
绑定变量在sql和plsql中直接引用必须加前缀 :。如要引用绑定变量a，则是 :a；
用var，var命令是声明一个绑定变量，只能给予名称和类型，定义的时候不能赋值，赋值可以在plsql中或者采用函数赋值(而host变量定义的时候必须赋值)。
SQL> var m number;
//创建一个带输出参数的存储过程test_pro
SQL> create or replace procedure test_pro(num out number)
as
begin
num:=10;
end;
/
Procedure created
//使用存储过程返回变量(引用绑定变量记得带上:)
SQL> exec test_pro(num=>:m);
PL/SQL procedure successfully completed
m
---------
10
//输出绑定变量m的值
SQL> print m
m
---------
10
3、declare (声明变量)
PL/SQL 表（TABLE）
定义记录表（或索引表）数据类型。它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，类似于高级中的二维数组，使得可以在PL/SQL中模仿数据库中的表
例1：按一维数组使用记录表
DECLARE
--定义记录表数据类型
TYPE reg_table_type IS TABLE OF varchar2(25)
INDEX BY BINARY_INTEGER;
--声明记录表数据类型的变量
v_reg_table REG_TABLE_TYPE;
BEGIN
v_reg_table(1) := 'Europe';
v_reg_table(2) := 'Americas';
v_reg_table(3) := 'Asia';
v_reg_table(4) := 'Middle East and Africa';
v_reg_table(5) := 'NULL';
DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_table (1)||'、'
||v_reg_table (2)||'、'
||v_reg_table (3)||'、'
||v_reg_table (4));
DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_table(5));
END;
例2：按二维数组使用记录表
DECLARE
--定义记录表数据类型
TYPE emp_table_type IS TABLE OF employees%ROWTYPE
INDEX BY BINARY_INTEGER;
--声明记录表数据类型的变量
v_emp_table EMP_TABLE_TYPE;
BEGIN
SELECT first_name, hire_date, job_id INTO
v_emp_table(1).first_name,v_emp_table(1).hire_date, v_emp_table(1).job_id
FROM employees WHERE employee_id = 177;
SELECT first_name, hire_date, job_id INTO
v_emp_table(2).first_name,v_emp_table(2).hire_date, v_emp_table(2).job_id
FROM employees WHERE employee_id = 178;
DBMS_OUTPUT.PUT_LINE('177雇员名称：'||v_emp_table(1).first_name
||' 雇佣日期：'||v_emp_table(1).hire_date
||' 岗位：'||v_emp_table(1).job_id);
DBMS_OUTPUT.PUT_LINE('178雇员名称：'||v_emp_table(2).first_name
||' 雇佣日期：'||v_emp_table(2).hire_date
||' 岗位：'||v_emp_table(2).job_id);
END;
变量赋值
在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：
1variable := expression ;
variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式.
2.6.1 字符及数字运算特点
空值加数字仍是空值：NULL + < 数字> = NULL
空值加（连接）字符，结果为字符：NULL || <字符串> = < 字符串>
2.6.2 BOOLEAN 赋值
布尔值只有TRUE, FALSE及 NULL 三个值。如：
1 DECLARE
2 bDone BOOLEAN;
3 BEGIN
4 bDone := FALSE;
5 WHILE NOT bDone LOOP
6 Null;
7 END LOOP;
8 END;
简单数据删除例子：
/***********************************************/
/* 文件名： */
/* 说 明:
简单的删除例子，不是实际应用。 */
/* 作 者： ZRJ */
/* 时 间： 2017-3-22 */
/***********************************************/
DECLARE
v_ename VARCHAR2(20) := 'Bill';
v_sal NUMBER(7,2) :=1234.56;
v_deptno NUMBER(2) := 10;
v_empno NUMBER(4) := 8888;
BEGIN
INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )
VALUES ( v_empno, v_ename, ‘Manager’, v_sal, v_deptno,
TO_DATE(’1954.06.09’,’yyyy.mm.dd’) );
COMMIT;
END;
DECLARE
v_empno number(4) := 8888;
BEGIN
DELETE FROM emp WHERE empno=v_empno;
COMMIT;
END;
介绍PL/SQL的流程控制语句, 包括如下三类:
控制语句: IF 语句
l 循环语句: LOOP语句, EXIT语句
l 顺序语句: GOTO语句, NULL语句
条件语句
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
END IF;
-----------------------
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSE
其它语句
END IF;
-----------------------
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSE
其它语句
END IF;
提示: ELSIF 不能写成 ELSEIF
CASE 表达式
---------格式一---------
CASE 条件表达式
WHEN 条件表达式结果1 THEN
语句段1
WHEN 条件表达式结果2 THEN
语句段2
......
WHEN 条件表达式结果n THEN
语句段n
[ELSE 条件表达式结果]
END;
---------格式二---------
CASE
WHEN 条件表达式1 THEN
语句段1
WHEN 条件表达式2 THEN
语句段2
......
WHEN 条件表达式n THEN
语句段n
[ELSE 语句段]
END;
循环
1. 简单循环
LOOP
要执行的语句;
EXIT WHEN <条件语句> --条件满足，退出循环语句
END LOOP;
例子：
DECLARE
int NUMBER(2) :=0;
BEGIN
LOOP
int := int + 1;
DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int);
EXIT WHEN int =10;
END LOOP;
END;
WHILE 循环
WHILE <布尔表达式> LOOP
要执行的语句;
END LOOP;
例子：
DECLARE
x NUMBER :=1;
BEGIN
WHILE x<=10 LOOP
DBMS_OUTPUT.PUT_LINE('X的当前值为:'||x);
x:= x+1;
END LOOP;
END;
数字式循环
[<<循环标签>>]
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
要执行的语句;
END LOOP [循环标签];
每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环。
例子1：
BEGIN
FOR int in 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);
END LOOP;
END;
BEGIN
FOR cr in 4..10 LOOP
DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||cr);
IF cr = 7 THEN
EXIT;
END IF;
END LOOP;
END;
例子2:
DECLARE
TYPE jobids_varray IS VARRAY(12) OF VARCHAR2(10); --定义一个VARRAY数据类型
v_jobids JOBIDS_VARRAY; --声明一个具有JOBIDS_VARRAY数据类型的变量
v_howmany NUMBER; --声明一个变量来保存雇员的数量
BEGIN
--用某些job_id值初始化数组
v_jobids := jobids_varray('FI_ACCOUNT', 'FI_MGR', 'ST_CLERK', 'ST_MAN');
--用FOR...LOOP...END LOOP循环使用每个数组成员的值
FOR i IN v_jobids.FIRST..v_jobids.LAST LOOP
--针对数组中的每个岗位，决定该岗位的雇员的数量
SELECT count(*) INTO v_howmany FROM employees WHERE job_id = v_jobids(i);
DBMS_OUTPUT.PUT_LINE ( '岗位'||v_jobids(i)||
'总共有'|| TO_CHAR(v_howmany) || '个雇员');
END LOOP;
END;
标号和GOTO
PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。语法如下：
GOTO label;
......
<<label>> /*标号是用<< >>括起来的标识符 */
注意，在以下地方使用是不合法的，编译时会出错误。
u 跳转到非执行语句前面。
u 跳转到子块中。
u 跳转到循环语句中。
u 跳转到条件语句中。
u 从异常处理部分跳转到执行。
u 从条件语句的一部分跳转到另一部分。
例子：
DECLARE
v_i NUMBER := 0;
v_s NUMBER := 0;
BEGIN
<<label_1>>
v_i := v_i + 1;
IF v_i <= 1000 THEN
v_s := v_s + v_i;
GOTO label_1;
END IF;
DBMS_OUTPUT.PUT_LINE(v_s);
END;
creditline
NULL 语句
在PL/SQL 程序中，NULL语句是一个可执行语句，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符或不执行任何操作的空语句，可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。如：
例子：
DECLARE
v_emp_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
v_sal_raise NUMBER(3,2);
BEGIN
v_emp_id := &emp_id;
SELECT first_name, salary INTO v_first_name, v_salary
FROM employees WHERE employee_id = v_emp_id;
IF v_salary <= 3000 THEN
v_sal_raise := .10;
DBMS_OUTPUT.PUT_LINE(v_first_name||'的工资是'||v_salary
||'、工资涨幅是'||v_sal_raise);
ELSE
NULL;
END IF;
END;
游标概念
游标是映射在结果集中一行数据上的位置实体，有了游标用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等等
游标的分类： 显式游标和隐式游标
(1)显示游标的使用：
定义/声明游标：就是定义一个游标名，以及与其相对应的SELECT 语句。
格式：
CURSOR cursor_name[(parameter[, parameter]…)]
[RETURN datatype]
IS
select_statement;
游标参数只能为输入参数，其格式为：
parameter_name [IN] datatype [{:= | DEFAULT} expression]
在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的。
[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是记录数据类型或带“%ROWTYPE”的数据
1.声明游标
CURSOR mycur(vartype number) is
select emp_no,emp_zc from cus_emp_basic
where com_no = vartype;
2.打开游标
open mycur(000627) 注：000627:参数
3.读取数据
fetch mycur into varno,varprice; (执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行)
4.关闭游标
close mycur;
游标的属性
oracle 游标有4个属性： %ISOPEN ， %FOUND ， %NOTFOUND， %ROWCOUNT
%ISOPEN 判断游标是否被打开，如果打开%ISOPEN 等于true,否则等于false
%FOUND %NOTFOUND 判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false(当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE)
%ROWCOUNT 返回当前位置为止游标读取的记录行数。
DECLARE
CURSOR c_cursor
IS SELECT first_name || last_name, Salary
FROM EMPLOYEES
WHERE rownum<11;
v_ename EMPLOYEES.first_name%TYPE;
v_sal EMPLOYEES.Salary%TYPE;
BEGIN
OPEN c_cursor;
FETCH c_cursor INTO v_ename, v_sal;
WHILE c_cursor%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );
FETCH c_cursor INTO v_ename, v_sal; ----执行FETCH语句时，每次返回一个数据行
END LOOP;
CLOSE c_cursor;
END;
没有参数且没有返回值的游标。
DECLARE
v_f_name employees.first_name%TYPE;
v_j_id employees.job_id%TYPE;
CURSOR c1 --声明游标,没有参数没有返回值
IS
SELECT first_name, job_id FROM employees
WHERE department_id = 20;
BEGIN
OPEN c1; --打开游标
LOOP
FETCH c1 INTO v_f_name, v_j_id; --提取游标
IF c1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_f_name||'的岗位是'||v_j_id);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c1; --关闭游标
END;
有参数且没有返回值的游标。
DECLARE
v_f_name employees.first_name%TYPE;
v_h_date employees.hire_date%TYPE;
CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值
IS
SELECT first_name, hire_date FROM employees
WHERE department_id = dept_id AND job_id = j_id;
BEGIN
OPEN c2(90, 'AD_VP'); --打开游标,传递参数值
LOOP
FETCH c2 INTO v_f_name, v_h_date; --提取游标
IF c2%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇佣日期是'||v_h_date);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c2; --关闭游标
END;
有参数且有返回值的游标。
DECLARE
TYPE emp_record_type IS RECORD(
f_name employees.first_name%TYPE,
h_date employees.hire_date%TYPE);
v_emp_record EMP_RECORD_TYPE;
CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数有返回值
RETURN EMP_RECORD_TYPE
IS
SELECT first_name, hire_date FROM employees
WHERE department_id = dept_id AND job_id = j_id;
BEGIN
OPEN c3(j_id => 'AD_VP', dept_id => 90); --打开游标,传递参数值
LOOP
FETCH c3 INTO v_emp_record; --提取游标
IF c3%FOUND THEN
DBMS_OUTPUT.PUT_LINE||'的雇佣日期是'
);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c3; --关闭游标
END;
游标的FOR循环
PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
格式：
FOR index_variable IN cursor_name[(value[, value]…)] LOOP
-- 游标数据处理代码
END LOOP;
当所声明的游标带有参数时，通过游标FOR 循环语句为游标传递参数。
DECLARE
CURSOR c_cursor(dept_no NUMBER DEFAULT 10)
IS
SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
BEGIN
DBMS_OUTPUT.PUT_LINE('当dept_no参数值为30：');
FOR c1_rec IN c_cursor(30) LOOP DBMS_OUTPUT.PUT_LINE||'---');
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默认的dept_no参数值10：');
FOR c1_rec IN c_cursor LOOP DBMS_OUTPUT.PUT_LINE||'---');
END LOOP;
END;
基于游标定义记录变量
使用%ROWTYPE属性不紧可以基于表和视图定义记录变量，也可以基于游标定义记录变量。当基于游标定义记录变量时，记录成员名实际就是SELECT语句的列名和列别名。为了简化显示游标的数据处理，建議开发人员使用记录变量存放游标数据。
For example:
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp；
emp_reocrd emp_cursor%ROWTYPE；
BEGIN
OPEN emp_cursor；
LOOP
FETCH emp_cursor INTO emp_record；
EXIT WHEN emp_record%NOTFOUND；
dbm(‘雇员名:’||’,雇员工资:’)；
END LOOP；
CLOSE emp_cursor；
END；
在游标FOR循环中使用查询
在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过游标查询来定义。
begin
for cr in (
select t.appid,t.OPID from
(select rank() over(partition by b.appid order by b.appdate desc) my_rank, b.appid,b.appdate,b.OPID
from (select t.appid, k.OPID,k.appdate
from T_APP_LOANAPP t
LEFT JOIN T_APPFLW_APPFLWHS k
on t.appid = k.applid
and k.procstatus = 'input'
where t.appcode in (select appcode from tbl_tmp)
order by k.appdate desc) b) t
where my_rank = 1) loop
update T_APP_LOANAPP
set appstatus = 'input', Fieldf = ''
where appid = );
update T_APPFLW_CURRSTAT
set CURRSTEP = 'input', ownopr = cr.OPID
where KEYINDEX = );
end loop;
end;
隐式游标的使用：
如果在pl/sql程序中使用了select语句进行操作，pl/sql会隐含处理游标定义，即称做隐式游标。这种游标不需要声明、打开和关闭。
例：
Create or replace procedure cx_xm
(in_xh in char,out_num out char)
As
Begin
Select xm into out_xm from xs where xh=in_xh; /*隐式游标必须使用into*/
Dbm(out_xm);
End
使用隐式游标时要注意以下几点：
A、每一个隐式游标必须有一个into;
B、和显示游标一样，带有关键字into接收数据的变量时数据类型要与列表一致。
C、隐式游标一次只能返回一行数据。
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。
格式调用为： SQL%
关于 NO_DATA_FOUND 和 %NOTFOUND 的区别
SELECT … INTO 语句触发 NO_DATA_FOUND；
当一个显式游标的WHERE子句未找到时触发%NOTFOUND；
当UPDATE或DELETE 语句的WHERE 子句未找到时触发 SQL%NOTFOUND；在提取循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND
游标变量
与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可
语法格式为：
TYPE ref_type_name IS REF CURSOR
[ RETURN return_type];
l 强类型（限制）（Strong REF CURSOR），规定返回类型
l 弱类型（非限制）（Weak REF CURSOR），不规定返回类型，可以获取任何结果集。
其中：ref_type_name为新定义的游标变量类型名称；
return_type 为游标变量的返回值类型，它必须为记录变量。
在定义游标变量类型时，可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。
声明一个游标变量的两个步骤：
步骤一：定义一个REF CURSOU数据类型，如：
TYPE ref_cursor_type IS REF CURSOR;
步骤二：声明一个该数据类型的游标变量，如：
cv_ref REF_CURSOR_TYPE;
用法１：
declare
type cur_tab is ref cursor;
sqlcur cur_tab;
v_T3100101 char(2);
v_T3100102 char(10);
begin
open sqlcur for select T3100101,T3100102 from T31001;
loop
fetch sqlcur into v_T3100101,v_T3100102;
exit when sqlcur%notfound;
dbm(v_T3100101||v_T3100102);
end loop;
close sqlcur;
end;
用法２：用于存储过程返回结果集
create or replace package selecttable
is
type cur_T31001 is ref cursor return T31001%rowtype; --注意，这里没有begin
end selecttable;
create or replace procedure T31001_select
(
cur out --参数类型定义为先前定义好的T31001
)
is
begin
open cur for
select * from T31001;
end T31001_select;
cursor 和 ref cursor的区别
从技术底层看，两者是相同的。普通plsql cursor在定义时是“静态”的。而Ref cursors可以动态打开。
例如下面例子：
Declare
type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
if ( to_char(sysdate,'dd') = 30 ) then
open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
open l_cursor for select * from dept;
else
open l_cursor for select * from dual;
end if;
open c;
end;
/
l rc根据逻辑动态打开；而游标c定义好了只有就无法修改了。
l ref cursor可以返回给客户端(返回类型)，cursor则不行。
l cursor可以是全局的global ，ref cursor则必须定义在过程或函数中(begin 中 是类型的一种 只是声明的时候不能初始化值)。
l ref cursor可以在子程序间传递(类型)，cursor则不行。
l cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用在：向客户端返回结果集。
异常处理概念
异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行
有三种类型的异常错误：
1． 预定义 ( Predefined )错误
ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。
2． 非预定义 ( Predefined )错误
即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
3． 用户定义(User_define) 错误
程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。
异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:
EXCEPTION
WHEN first_exception THEN <code to handle first exception >
WHEN second_exception THEN <code to handle second exception >
WHEN OTHERS THEN <code to handle others exception >
END;
异常处理可以按任意次序排列，但 OTHERS 必须放在最后
非预定义的异常处理
对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。步骤如下：
1. 在PL/SQL 块的定义部分定义异常情况：
<异常情况> EXCEPTION;
2. 将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：
PRAGMA EXCEPTION_INIT(<异常情况>, <错误代码>);
3. 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。
例2：删除指定部门的记录信息，以确保该部门没有员工。
INSERT INTO departments VALUES(50, 'FINANCE', 'CHICAGO');
DECLARE
v_deptno departments.department_id%TYPE := &deptno;
deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(deptno_remaining, -2292);
/* -2292 是违反一致性约束的错误代码 */
BEGIN
DELETE FROM departments WHERE department_id = v_deptno;
EXCEPTION
WHEN deptno_remaining THEN
DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
用户自定义的异常处理
当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。
对于这类异常情况的处理，步骤如下：
1． 在PL/SQL 块的定义部分定义异常情况：
<异常情况> EXCEPTION;
2． RAISE <异常情况>；
3． 在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。
例3：更新指定员工工资，增加100；
DECLARE
v_empno employees.employee_id%TYPE :=&empno;
no_result EXCEPTION;
BEGIN
UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;
IF SQL%NOTFOUND THEN
RAISE no_result; -----------------抛出异常
END IF;
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
在 PL/SQL 中使用 SQLCODE，SQLERRM异常处理函数
RAISE_APPLICATION_ERROR(-20789,'输入数字不能在10~100之间！') ; --抛出异常
RAISE_APPLICATION_ERROR(错误号，错误信息 [，是否添加到错误堆栈])
语法参数：
错误号：
只接受-20000 ~ -20999范围的错误号，和声明的错误号一致；
错误信息：
用于定义在使用SQLERRM输出时的错误提示信息；
是否添加到错误堆栈：
如果设置为TRUE，则表示将错误添加到任意已有的错误堆栈，默认为FALSE，可选。
DECLARE
data NUMBER ;
BEGIN
DBMS_OUTPUT.put_line('请输入数字：') ;
data := &inputData ;
IF data > 10 AND data < 100 THEN
RAISE_APPLICATION_ERROR(-20789,'输入数字不能在10~100之间！') ; --抛出异常
END IF ;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ;
DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ;
END;
构建动态异常
DECLARE
data NUMBER ;
myexp EXCEPTION ;
PRAGMA EXCEPTION_INIT(myexp,-20789) ; --定义一个异常变量
BEGIN
DBMS_OUTPUT.put_line('请输入数字：') ;
data := &inputData ;
IF data > 10 AND data < 100 THEN
RAISE_APPLICATION_ERROR(-20789,'输入数字不能在10~100之间！') ; --抛出异常
END IF ;
EXCEPTION
WHEN myexp THEN
DBMS_OUTPUT.put_line('SQLCODE = ' || SQLCODE) ;
DBMS_OUTPUT.put_line('SQLERRM = ' || SQLERRM) ;
END;
(异常运用)
CREATE OR REPLACE
PROCEDURE insert_dept
(v_dept_id IN departments.department_id%TYPE,
v_dept_name IN departments.department_name%TYPE,
v_mgr_id IN departments.manager_id%TYPE,
v_loc_id IN departments.location_id%TYPE)
IS
ept_null_error EXCEPTION;
PRAGMA EXCEPTION_INIT(ept_null_error, -1400); ------定义绑定已有异常
ept_no_loc_id EXCEPTION;
PRAGMA EXCEPTION_INIT(ept_no_loc_id, -2291);
BEGIN
INSERT INTO departments
(department_id, department_name, manager_id, location_id)
VALUES
(v_dept_id, v_dept_name, v_mgr_id, v_loc_id);
DBMS_OUTPUT.PUT_LINE('插入部门'||v_dept_id||'成功');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN -----系统预定义异常
RAISE_APPLICATION_ERROR(-20000, '部门编码不能重复');
WHEN ept_null_error THEN
RAISE_APPLICATION_ERROR(-20001, '部门编码、部门名称不能为空'); -------- 抛出新构建的异常
WHEN ept_no_loc_id THEN
RAISE_APPLICATION_ERROR(-20002, '没有该地点');
END insert_dept;
/*调用实例一:
DECLARE
ept_20000 EXCEPTION;
PRAGMA EXCEPTION_INIT(ept_20000, -20000);
ept_20001 EXCEPTION;
PRAGMA EXCEPTION_INIT(ept_20001, -20001);
ept_20002 EXCEPTION;
PRAGMA EXCEPTION_INIT(ept_20002, -20002);
BEGIN
insert_dept(300, '部门300', 100, 2400);
insert_dept(310, NULL, 100, 2400);
insert_dept(310, '部门310', 100, 900);
EXCEPTION
WHEN ept_20000 THEN
DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复');
WHEN ept_20001 THEN
DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空');
WHEN ept_20002 THEN
DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误');
END;
过程与函数（另外还有包与触发器）是命名的PL/SQL块（也是用户的方案对象），被编译后存储在数据库中，以备执行。因此，其它PL/SQL块可以按名称来使用他们。所以，可以将商业逻辑、企业规则写成函数或过程保存到数据库中，以便共享。
过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中，主要介绍：
1． 创建存储过程和函数。
2． 正确使用系统级的异常处理和用户定义的异常处理。
3． 建立和管理存储过程和函数。
1) 返回一个实参结果
CREATE FUNCTION get_bal(acc_no IN NUMBER)
RETURN NUMBER
IS
acc_bal NUMBER(11,2);
BEGIN
SELECT order_total INTO acc_bal FROM orders
WHERE customer_id = acc_no;
RETURN(acc_bal);
END;
2) 返回多个实参结果
CREATE OR REPLACE FUNCTION get_salary(
dept_no IN NUMBER DEFAULT 1, ----可用默认
emp_count OUT NUMBER)
RETURN NUMBER
IS
V_sum NUMBER;
BEGIN
SELECT SUM(SALARY), count(*) INTO V_sum, emp_count FROM EMPLOYEES
WHERE DEPARTMENT_ID=dept_no;
RETURN v_sum;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('数据不存在');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('其它异常：');
DBMS_OUTPUT.PUT_LINE('错误号：' || SQLCODE||'，错误消息：'||SQLERRM);
END get_salary;
函数调用
1)固定顺序入参
DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum := get_salary(27, v_num);
DBMS_OUTPUT.PUT_LINE('部门27的工资总和：'||v_sum||'，人数为：'||v_num);
END;
2)指定形参值入参,无顺序要求
DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum := get_salary(dept_no => 27, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('部门27的工资总和：'||v_sum||'，人数为：'||v_num);
END;
注 默认形参可省(必须用=>的方式)
DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum := get_salary(emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('部门27的工资总和：'||v_sum||'，人数为：'||v_num);
END;
存储过程
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中。经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
(局部 )
DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
PROCEDURE proc_demo
(
Dept_no NUMBER DEFAULT 10,
Sal_sum OUT NUMBER,
Emp_count OUT NUMBER
)
IS
BEGIN
SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
FROM employees WHERE department_id=dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;
--调用方法：
BEGIN
Proc_demo(30, v_sum, v_num); -----(在PL/SQL块中过程可以直接引用)
DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);
Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num);
END;
CREATE OR REPLACE PROCEDURE divide(
num1 IN OUT NUMBER,
num2 IN OUT NUMBER)
IS
v1 NUMBER;
v2 NUMBER;
BEGIN
v1 := trunc(num1 / num2);
v2 := mod(num1,num2);
num1 := v1;
num2 := v2;
END;
当在SQL*PLUS中调用过程时，需要使用CALL或者EXECUTE命令，而在PL/SQL块中过程可以直接引用。
-- 调用删除员工的过程
EXEC remove_emp(1);
-- 调用插入员工的过程
EXECUTE insert_emp(1, 'tommy', 'lin', 2);
下面是一个取过程结果集的
栗子
返回结果集的存储过程
--1.创建一个包,在该包中定义了一个游标类型test_corsor
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
--2.创建存储过程
create or replace procedure lt_pro1
(ltNo in number, t_cursor out ) is
begin
open t_cursor for select * from emp where deptno=ltNo;
end;
3.如何在java中调用
// 1.加载驱动
Cla("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call lt_pro1(?,?)}");
// 4.给？赋值
cs.setInt(1, 10);
// 第二个参数传oracle自带的类型游标
cs.registerOutParameter(2, oracle.jdbc.OracleTy);
// 5.执行
cs.execute();
// 得到结果集
ResultSet rs = (ResultSet) cs.getObject(2); ------第二个参数
while()){
Sy(1)+" " +rs.getString(2));
}
// 关闭
cs.close();
ct.close();
oracle分页
select * from (select t1.*, rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;
--1.创建一个包,在该包中定义了一个游标类型test_corsor
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
-- 存储过程返回分页记录
create or replace procedure fenye
(tableName in varchar2, --表名
myPageSize in number, --每页几条记录
pageNo in number, -- 当前页
allcount out number, -- 总记录数
pagecount out number, -- 总页数
t_cursor out --返回的记录集
) is
-- 定义部分
v_sql varchar2(1000);
-- 定义两个整数
v_begin number:=(pageNo-1)*myPageSize+1;
v_end number:=pageNo*myPageSize;
begin
-- 执行部分
v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1
where rownum<='||v_end||') where rn>='||v_begin;
-- 把游标和sql关联
open t_cursor for v_sql;
-- 计算总页数和总记录数
v_sql:='select count(*) from '||tableName;
-- 执行sql语句，并把结果付给allcount
execute immediate v_sql into allcount;
-- 计算总页数
if mod(allcount,myPageSize)=0 then
pageCount:=allcount/myPageSize;
else
pageCount:=allcount/myPageSize+1;
end if;
-- 关闭游标
--close t_cursor;
end;
// 1.加载驱动
Cla("oracle.jdbc.driver.OracleDriver");
// 2.得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
// 3.创建CallableStatement
CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
// 4.给？赋值
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 1);
// 第二个参数传oracle自带的类型游标
cs.registerOutParameter(4, java.);
cs.registerOutParameter(5, java.);
cs.registerOutParameter(6, oracle.jdbc.OracleTy);
// 5.执行
cs.execute();
// 得到结果集
int count=cs.getInt(4);
int pageCount= cs.getInt(5);
Sy(count);
Sy(pageCount);
ResultSet rs = (ResultSet) cs.getObject(6);
while()){
Sy(1)+" " +rs.getString(2));
}
// 关闭
cs.close();
ct.close();
stmt = conn.prepareCall("{? = call F_GET_PRICE(?)}");
(1, oracle.jdbc.OracleTy); --调用函数返回结果类型
(2, in_price); --参数
******************** 在持久框架中的运用
删除过程或函数
删除过程语法：
DROP PROCEDURE [schema.]procudure_name;
删除函数语法：
DROP FUNCTION [schema.]function_name;
使用过程与函数具有如下优点：
1、共同使用的代码可以只需要被编写和测试一次，而被需要该代码的任何应用程序（如：.NET、C++、JAVA、VB程序，也可以是DLL库）调用。
2、这种集中编写、集中维护更新、大家共享（或重用）的方法，简化了应用程序的开发和维护，提高了效率与性能。
3、这种模块化的方法，使得可以将一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写、调试。因此使程序的结构清晰、简单，也容易实现。
4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。
5、节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。并且，如果多个用户要执行相同的过程或函数时，就只需要在内存中加载一个该过程或函数。
6、提高数据的安全性与完整性。通过把一些对数据的操作放到过程或函数中，就可以通过是否授予用户有执行该过程或的权限，来限制某些用户对数据进行这些操作。
过程与函数的相同功能有：
1、 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
2、 输入参数都可以接受默认值，都可以传值或传引导。
3、 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
4、 都有声明部分、执行部分和异常处理部分。
5、 其管理过程都有创建、编译、授权、删除、显示依赖关系等。
使用过程与函数的原则：
1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。
2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。
3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。
程序包
模块化、更轻松的应用程序设计、信息隐藏、新增功能、性能更佳。
创建包头包体的基本操作如下
create or replace package pack1 --创建包头/规范
is
aa number := 1 ; --在包头声明的为公有变量
procedure update_student (update_row in student %rowtype ); --声明一个更新过程
procedure insert_student (insert_row in student %rowtype ); --声明一个插入过程
end pack1 ; --结束包头
--Package created
create or replace package body pack1 --创建包体/主体
is
bb number := 2 ; --在包体声明的变量类私有变量
procedure insert_student (insert_row in student %rowtype ) --创建过程主体
as
begin
insert into student( id, name, age) values ,in ,in );
dbm ('bb = ' ||bb ||'aa = ' || aa );
end insert_student; --结束过程主体
procedure update_student( update_row in student% rowtype) --创建过程主体
as
begin
update student s set s.name = '赵北' where s.id = u ;
end update_student ;--结束过程主体
end pack1 ;--结束主体/包体
SQL > set serverout on; --打开输出开关
SQL > execute dbm ); --包中的公共变量被输出
1
PL /SQL procedure successfully completed
SQL > execute dbm ); --包中的私有变量不被输出
SQL >
declare
row_student student %rowtype ; --声明行级变量
begin
row_ := 5;
row_ := '关羽';
row_ := 60;
(row_student );--调用包中的过程
end ;
/
PL /SQL procedure successfully completed
---------------------------在程序包中创建显示游标---------------
create or replace package pack2 --创建包头
is
cursor student_cursor return student %rowtype ; --声明显示游标，但是不能跟is select子句
procedure student_pro ; --声明过程
end pack2 ;
create or replace package body pack2 --创建包体
is
cursor student_cursor return student %rowtype is select * from student ; --指定游标所关联的select
procedure student_pro
is
student_row student %rowtype ;
begin
open student_cursor ;
fetch student_cursor into student_row ;
while student_cursor %found
loop
dbm ('学号 = ' || || '姓名 = ' || );
fetch student_cursor into student_row ;
end loop;
close student_cursor ;
end student_pro ;
end pack2 ;
/
SQL > execute ;
学号 = 1姓名 = 张三
学号 = 2姓名 = 李四
学号 = 3姓名 = 王五
学号 = 4姓名 = 麻子
学号 = 5姓名 = 赵北
PL /SQL procedure successfully completed
-----------------------------在程序包中创建ref游标---------------
create or replace package pack3
is
type ref_cursor is ref cursor; --声明一个ref游标类型
procedure ref_student_pro ;
end pack3 ;
--Package created
create or replace package body pack3
is
procedure ref_student_pro
is
student_row student %rowtype ;
student_ref_cursor ref_cursor ;--声明一个ref游标类型的变量
begin
open student_ref_cursor for select * from student ;
fetch student_ref_cursor into student_row ;
while student_ref_cursor %found
loop
dbm ('学号 = ' || || '姓名 = ' || );
fetch student_ref_cursor into student_row ;
end loop;
close student_ref_cursor ;
end ref_student_pro ;
end pack3 ;
--Package body created
SQL > execute ;
学号 = 1姓名 = 张三
学号 = 2姓名 = 李四
学号 = 3姓名 = 王五
学号 = 4姓名 = 麻子
学号 = 5姓名 = 赵北
PL /SQL procedure successfully completed
删除包
DROP PACKAGE emp_package;