使用游标
1）显示游标：
CURSOR name_cursor IS select statement;
2）打开游标：
OPEN name_cursor;
3）提取游标
FETCH name_cursor INTO variable1,variable2.............
4）关闭游标
CLOSE name_cursor;
显示游标的属性：
%ISOPEN:显示游标是否打开
%FOUND：是否从结果集中取到数据
%NOTFOUND:是否未从结果集中取到数据
%ROWCOUNT:已经取到的数据，不是说集合中间有多少数据，请这里注意；
DECLARE
CURSOR a_cursor IS SELECT id,name from test1;
v_id test1.id%TYPE;
v_name test1.name%TYPE;
BEGIN
OPEN a_cursor;
FETCH a_cursor INTO v_id,v_name;
dbms_output.put_line(v_id);
dbms_output.put_line(v_name);
CLOSE a_cursor;
END;
使用游标更新或者删除数据：
如果想通过游标更新或者删除数据的话，那么在定义游标的时候必须指定 FOR UPDATE
如下：
CURSOR my_cursor IS SELECT * FROM test1 FOR UPDATE;
UPDATE table_name SET column=xx WHERE CURRENT OF name_cursor;
DELETE table_name WHERE CURRENT OF name_cursor;
必须指定WHERE CURRENT OF name_current;
使用例外：
EXCEPTION
WHEN e_name1 or e_name2 THEN
statement1;
WHEN 2_name3 THEN
statement2;
ELSE
statement3;
WHEN OTHERS THEN
statement4;
其中自定义异常是这样的：
DECLARE
e_name EXCEPTION;
BEGIN
RAISE e_name;
EXCEPTION
WHEN e_name THEN
dbms_output.put_line('异常抛出');
END;
使用复合数据：
TYPE my_record_type IS RECORD
DECLARE
TYPE my_record_type IS RECORD(
id test1.id%TYPE,
name test1.name%TYPE
);
r1 my_record_type;
BEGIN
SELECT id,name INTO r1 FROM test1 WHERE id=1;
dbms_output.put_line(r1.id || r1.name);
END;
DECLARE
TYPE my_record_type IS RECORD(
id test1.id%TYPE,
name test1.name%TYPE
);
r1 test1%ROWTYPE;
BEGIN
SELECT * INTO r1 FROM test1 WHERE id=1;
dbms_output.put_line(r1.id || r1.name);
END;
/
使用PL/SQl表：
DECLARE
TYPE my_table_type IS TABLE OF NUMBER(2,0)
INDEX BY BINARY_INTEGER;
name_table my_table_type;
BEGIN
select id BULK COLLECT INTO name_table from test1;
dbms_output.put_line(name_table(1));
End;
使用PL/SQL记录表:
DECLARE
TYPE my_table_type IS TABLE OF test1%ROWTYPE
INDEX BY BINARY_INTEGER;
name_table my_table_type;
BEGIN
select * BULK COLLECT INTO name_table from test1;
dbms_output.put_line(name_table(1).name);
End;
DECLARE
TYPE my_table_type IS TABLE OF test1%ROWTYPE
INDEX BY BINARY_INTEGER;
name_table my_table_type;
BEGIN
select * BULK COLLECT INTO name_table from test1; --count可以得到这个表的长度
FOR i in 1..name_table.count LOOP
dbms_output.put_line(name_table(i).name);
END LOOP;
End;