oracle数据库sql语句08 PL SQL程序结构 循环 游标
来源:互联网 发布:蔡司三坐标测量机编程软件教程 编辑:程序博客网 时间:2024/05/18 01:19
PL/SQL程序结构
set serveroutput on
declare
season int :=3;
info varchar2(50);
begin
season := &season;
case season
when 1 then
info := season||'季度包含1,2,3月份';
when 2 then
info := season||'季度包含4,5,6月份';
when 3 then
info := season||'季度包含7,8,9月份';
when 4 then
info := season||'季度包含10,11,12月份';
else
info := '季度不合法';
end case;
dbms_output.put_line(info);
end;
/
循环结构
--for
declare
sum_i int:=0;
i int := 0;
begin
for i in 1..100 loop
sum_i:=sum_i+i;
end loop;
dbms_output.put_line(sum_i);
end;
/
--while
declare
sum_i int:=0;
i int := 0;
begin
while i<=99 loop
i:=i+1;
sum_i:=sum_i+i;
end loop;
dbms_output.put_line(sum_i);
end;
/
--loop
declare
sum_i int:=0;
i int := 0;
begin
loop
i:=i+1;
sum_i:=sum_i+i;
exit when i >99;
end loop;
dbms_output.put_line(sum_i);
end;
/
--显式游标
--游标 while
declare
cursor cur_emp is select * from emp where deptno=30;
a emp%rowtype;
begin
open cur_emp;
fetch cur_emp into a;
while cur_emp%found loop
dbms_output.put_line(a.ename);
fetch cur_emp into a;
end loop;
close cur_emp;
end;
/
--游标 loop
declare
cursor cur_emp is select * from emp where deptno=30;
a emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into a;
exit when cur_emp%notfound;
dbms_output.put_line(a.ename);
end loop;
close cur_emp;
end;
/
--游标 for 自动打开、读取、关闭 游标
declare
cursor cur_emp is select * from emp where deptno=30;
begin
for i in cur_emp loop
dbms_output.put_line(i.ename);
end loop;
end;
/
--隐式游标
begin
update emp set sal=sal+100 where job='SALESMAN';
if sql%found then
dbms_output.put_line(sql%rowcount||'有员工跟新');
else
dbms_output.put_line('无员工跟新');
end if;
end;
/
declare
aa emp%rowtype;
begin
select * into aa from emp where job='SALESMAN';
if sql%found then
dbms_output.put_line(sql%rowcount||'有员工跟新');
else
dbms_output.put_line('无员工跟新');
end if;
end;
/
begin
for i in (select * from emp where deptno=30) loop
dbms_output.put_line(i.empno||' '||i.ename);
end loop;
end;
/
set serveroutput on
declare
season int :=3;
info varchar2(50);
begin
season := &season;
case season
when 1 then
info := season||'季度包含1,2,3月份';
when 2 then
info := season||'季度包含4,5,6月份';
when 3 then
info := season||'季度包含7,8,9月份';
when 4 then
info := season||'季度包含10,11,12月份';
else
info := '季度不合法';
end case;
dbms_output.put_line(info);
end;
/
循环结构
--for
declare
sum_i int:=0;
i int := 0;
begin
for i in 1..100 loop
sum_i:=sum_i+i;
end loop;
dbms_output.put_line(sum_i);
end;
/
--while
declare
sum_i int:=0;
i int := 0;
begin
while i<=99 loop
i:=i+1;
sum_i:=sum_i+i;
end loop;
dbms_output.put_line(sum_i);
end;
/
--loop
declare
sum_i int:=0;
i int := 0;
begin
loop
i:=i+1;
sum_i:=sum_i+i;
exit when i >99;
end loop;
dbms_output.put_line(sum_i);
end;
/
--显式游标
--游标 while
declare
cursor cur_emp is select * from emp where deptno=30;
a emp%rowtype;
begin
open cur_emp;
fetch cur_emp into a;
while cur_emp%found loop
dbms_output.put_line(a.ename);
fetch cur_emp into a;
end loop;
close cur_emp;
end;
/
--游标 loop
declare
cursor cur_emp is select * from emp where deptno=30;
a emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into a;
exit when cur_emp%notfound;
dbms_output.put_line(a.ename);
end loop;
close cur_emp;
end;
/
--游标 for 自动打开、读取、关闭 游标
declare
cursor cur_emp is select * from emp where deptno=30;
begin
for i in cur_emp loop
dbms_output.put_line(i.ename);
end loop;
end;
/
--隐式游标
begin
update emp set sal=sal+100 where job='SALESMAN';
if sql%found then
dbms_output.put_line(sql%rowcount||'有员工跟新');
else
dbms_output.put_line('无员工跟新');
end if;
end;
/
declare
aa emp%rowtype;
begin
select * into aa from emp where job='SALESMAN';
if sql%found then
dbms_output.put_line(sql%rowcount||'有员工跟新');
else
dbms_output.put_line('无员工跟新');
end if;
end;
/
begin
for i in (select * from emp where deptno=30) loop
dbms_output.put_line(i.empno||' '||i.ename);
end loop;
end;
/
阅读全文
0 0
- oracle数据库sql语句08 PL SQL程序结构 循环 游标
- oracle数据库sql语句07 PL SQL程序结构
- oracle数据库sql语句09 PL SQL程序结构 异常
- Oracle PL/SQL显示游标、隐式游标、游标循环
- oracle pl sql for循环游标
- oracle pl sql while循环游标
- oracle数据库PL/SQL之游标
- Oracle数据库之PL/SQL游标
- Oracle PL/SQL游标
- oracle pl sql 游标
- oracle pl/sql 游标
- Oracle pl/sql 游标
- oracle数据库之PL/SQL程序结构(上)
- oracle数据库之PL/SQL程序结构(上)
- Oracle——08PL/SQL简介,基本程序结构和语句
- Oracle [PL/SQL]中的循环语句
- [oracle]pl/sql --循环语句demo
- Oracle PL/SQL 循环控制语句
- oracle数据库sql语句06 join
- tensorflow系列1:两种方式优化损失函数
- MySQL的CURD
- PAT1041. 考试座位号(15) C++
- oracle数据库sql语句07 PL SQL程序结构
- oracle数据库sql语句08 PL SQL程序结构 循环 游标
- 第一章 基础知识
- oracle数据库sql语句09 PL SQL程序结构 异常
- 利用yum安装Redis的方法详解_Redis
- Python面向对象之定义类和使用类
- 浮点型数据在内存中的存储
- oracle数据库sql语句10 存储过程
- 最大二叉堆的Java实现
- Django学习(三)模板定制