drop table student;
create table student
(
stuNo int primary key,
Name varchar2(10),
address varchar2(30),
birthday date
);
insert into student values(1,'梅超风','山东',to_date('1860-02-12 12:12:23','YYYY-MM-DD HH24:MI:SS'));
insert into student values(2,'陆成风','山西',to_date('1860-2-12','YYYY-MM-DD'));
insert into student values(3,'冯默风','安徽','10-2月-1886');
insert into student values(4,'曲灵风','湖南常德',to_date('1870-02-12 12:12:23','YYYY-MM-DD HH24:MI:SS'));
Rem ===================================================================
Rem 隐式游标
Rem ===================================================================
SET SERVEROUTPUT ON;
BEGIN
UPDATE student SET address='河南洛阳'
WHERE stuNo = 1;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('表已更新');
END IF;
END;
/
BEGIN
UPDATE student SET address=replace(address,'南','北')
WHERE address LIKE '%南%';
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('未找到数据........');
ELSE
DBMS_OUTPUT.PUT_LINE('影响了'||SQL%ROWCOUNT||'行');
END IF;
END;
/
Rem ===================================================================
Rem 显式游标
Rem ===================================================================
--使用游标显示name列的值
DECLARE
l_name VARCHAR2(20);
CURSOR stu_name_cur IS SELECT name from student;
BEGIN
OPEN stu_name_cur; --打开游标
LOOP
FETCH stu_name_cur INTO l_name;
DBMS_OUTPUT.PUT_LINE(stu_name_cur%ROWCOUNT);
EXIT WHEN stu_name_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:'||l_name);
END LOOP;
CLOSE stu_name_cur; --关闭游标
END;
/
--带参数的游标
DECLARE
l_name VARCHAR2(20);
l_stuNO NUMBER(2);
CURSOR stu_name_cur(sNo NUMBER)
IS SELECT name from student
WHERE stuNo>sNo;
BEGIN
l_stuNO:=&stuNO;
OPEN stu_name_cur(l_stuNO); --打开游标
LOOP
FETCH stu_name_cur INTO l_name;
EXIT WHEN stu_name_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:'||l_name);
END LOOP;
CLOSE stu_name_cur; --关闭游标
END;
/
Rem ===================================================================
Rem 使用显示游标修改数据
Rem ===================================================================
DECLARE
l_birthday DATE;
l_stuNO NUMBER(2);
CURSOR stu_name_cur(sNo NUMBER)
IS SELECT birthday from student
WHERE stuNo>sNo FOR UPDATE OF birthday;
BEGIN
l_stuNO:=&stuNO;
OPEN stu_name_cur(l_stuNO); --打开游标
LOOP
FETCH stu_name_cur INTO l_birthday;
EXIT WHEN stu_name_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('出生年月:'||to_char(l_birthday,'yyyy"年"mm"月"dd"日"'));
UPDATE student SET birthday=ADD_MONTHS(l_birthday,2)
WHERE CURRENT OF stu_name_cur; --更新当前行
END LOOP;
CLOSE stu_name_cur; --关闭游标
COMMIT;
END;
/
Rem ===================================================================
Rem 使用显示游标删除记录
Rem ===================================================================
DECLARE
l_stu_rec student%ROWTYPE;
CURSOR del_cur IS select * from student FOR UPDATE;
BEGIN
OPEN del_cur;
LOOP
FETCH del_cur into l_stu_rec;
EXIT WHEN del_cur%NOTFOUND;
IF l_stu_rec.stuno=2 THEN
DELETE FROM student WHERE CURRENT OF del_cur; --删除当前行
END IF;
END LOOP;
END;
/
Rem ===================================================================
Rem 循环游标
Rem ===================================================================
DECLARE
CURSOR stu_cur IS
SELECT stuNo,name,address FROM student;
BEGIN
FOR stu IN stu_cur --不要加分号
LOOP
DBMS_OUTPUT.PUT_LINE(stu.stuNO||' '||stu.name||' '||stu.address);
END LOOP;
END;
/
Rem ===================================================================
Rem 弱类型REF游标
Rem ===================================================================
DECLARE
sNo student.stuNo%type;
sName student.name%type;
sAddress student.address%type;
TYPE stu_cur_ref IS REF CURSOR; --声明REF游标类型
stu_cur stu_cur_ref; --声明“stu_cur_ref”游标类型变量
BEGIN
OPEN stu_cur FOR
SELECT stuNo,name,address FROM student;
LOOP
FETCH stu_cur INTO sNo,sName,sAddress;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(sNo||' '||sName||' '||sAddress);
END LOOP;
CLOSE stu_cur;
END;
/
DECLARE
l_stuno student.stuno%TYPE;
l_name student.name%TYPE;
TYPE stu_cur_ref IS REF CURSOR;
curStu stu_cur_ref;
BEGIN
OPEN curStu FOR
SELECT stuno,name FROM student;
LOOP
FETCH curStu INTO l_stuno,l_name;
EXIT WHEN curStu%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stuno||' '||l_name);
END LOOP;
CLOSE curStu;
END;
/
Rem ===================================================================
Rem 强类型REF游标
Rem ===================================================================
DECLARE
TYPE l_stu_type IS RECORD( --自定义记录类型
sNo student.stuNo%type,
sName student.name%type,
sAddress student.address%type
);
l_stu l_stu_type; --声明自定义类型变量
TYPE stu_cur_ref IS REF CURSOR --声明REF游标类型
RETURN l_stu_type; --返回类型为自定义类型
stu_cur stu_cur_ref; --声明“stu_cur_ref”游标类型变量
BEGIN
OPEN stu_cur FOR
SELECT stuNo,name,address FROM student;
LOOP
FETCH stu_cur INTO l_stu;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stu.sNo||' '||
l_stu.sName||' '||l_stu.sAddress);
END LOOP;
CLOSE stu_cur;
END;
/
DECLARE
TYPE l_stu_type IS RECORD(
sno student.stuno%TYPE,
sname student.name%TYPE
);
l_stu l_stu_type;
TYPE stu_cur_ref IS REF CURSOR RETURN l_stu_type;
stu_cur stu_cur_ref;
BEGIN
OPEN stu_cur FOR
SELECT stuno,name FROM student;
LOOP
FETCH stu_cur INTO l_stu;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stu.sno||' '||l_stu.sname);
END LOOP;
CLOSE stu_cur;
END;
/
Rem ===================================================================
Rem 使用游标变量执行动态 SQL
Rem ===================================================================
DECLARE
l_stu student%ROWTYPE;
TYPE stu_cur_ref IS REF CURSOR; --声明REF游标类型
stu_cur stu_cur_ref; --声明“stu_cur_ref”游标类型变量
l_stuNo NUMBER(2);
BEGIN
l_stuNo := &sNo;
OPEN stu_cur FOR
'SELECT * FROM student WHERE stuNo>:1'
USING l_stuNo; --绑定参数
LOOP
FETCH stu_cur INTO l_stu;
EXIT WHEN stu_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_stu.stuNo||' '||l_stu.name||' '||l_stu.address||
' '||l_stu.birthday);
END LOOP;
CLOSE stu_cur;
END;
/
分享到:
相关推荐
Oracle游标语法总结.doc Oracle游标语法总结.doc
Oracle游标使用方法及语法大全.doc
游标语法简介和最简单使用教程(适合初学者) 最简单的方法示例
SQL Server 事务 游标 基本语法操作实例 全SQL语句
Oracle游标使用方法及语法大全
Oracle游标使用方法及语法大全[整理].pdf
了解orcale的游标的使用 和应用
本人整理的实用SQL脚本,本人经常使用,包括游标语法,跨服务器数据库连接等
PRINT '游标中的第-2行[相对地址]:' + @value; -- 填充数据. FETCH PRIOR FROM c_test_main INTO @id, @value; PRINT '游标中的上一行:' + @value; -- 填充数据. FETCH NEXT FROM c_test_main INTO @id, @...
本文提供了在MS SQL SERVER 中应用游标所应具有的有关游标的必要知识和各种语法。从中读者可以了解游标的优点、种类、作用、学会如何定义、打开、存取、关闭、释放游标以及游标的应用。除此之外,在本章的后半部分...
PLSQL 存储过程 语法
7. 把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。 8. 用 Transact-SQL 过程代替 PL/SQL 过程、函数和包。 9. 把 PL/SQL 触发器转换为 Transact-SQL 触发器。 10. 使用 SET SHOWPLAN 语句,优化...
Python程序设计基础语法
语法入门、操作plsql、存储过程、函数、触发器、游标、包、返回类型;一个execle表轻松搞定。
本文实例讲述了mysql游标的原理与用法。分享给大家供大家参考,具体如下: 本文内容: 什么是游标 创建游标 使用游标 首发日期:2018-04-18 什么是游标: ... 语法: 1.定义游标:declare 游标名 c
对SQL游标中的常用语法进行说明,同时包含简单案例方便查看和了解
OraclePL/SQL练习,基本的OraclePL/SQL的语法,游标的在PL/SQL中的使用,如何声明游标,打开游标,提取记录,关闭游标。
主要介绍了Mysql 游标的相关资料,帮助大家更好的理解和使用MySQL数据库,感兴趣的朋友可以了解下