oracle 存储过程和函数例子
时间:2023-06-26 08:19:30
作者:peace.zhao
关于 游标 if,for 的例子
create or replace procedure peace_if
is
cursor var_c is select * from grade;
begin
for temp in var_c loop
if temp.course_name = 'OS' then
dbms_output.put_line('Stu_name = '||temp.stu_name);
elsif temp.course_name = 'DB' then
dbms_output.put_line('DB');
else
dbms_output.put_line('feng la feng la ');
end if;
end loop;
end;
---关于游标 for,case 的例子1
create or replace procedure peace_case1
is
cursor var_c is select * from test_case;
begin
for temp in var_c loop
case temp.vol
when 1 then
dbms_output.put_line('haha1');
when 2 then
dbms_output.put_line('haha2');
when 3 then
dbms_output.put_line('haha3');
when 4 then
dbms_output.put_line('haha4');
else
dbms_output.put_line('qita');
end case ;
end loop;
end;
---关于游标 for,case 的例子2
create or replace procedure peace_case2
is
cursor var_c is select * from test_case;
begin
for temp in var_c loop
case
when temp.vol=1 then
dbms_output.put_line('haha1');
when temp.vol=2 then
dbms_output.put_line('haha2');
when temp.vol=3 then
dbms_output.put_line('haha3');
when temp.vol=4 then
dbms_output.put_line('haha4');
else
dbms_output.put_line('qita');
end case ;
end loop;
end;
---关于for 循环的例子
create or replace procedure peace_for
is
sum1 number :=0;
temp varchar2(500);
begin
for i in 1..9 loop
temp := '';
for j in 1 .. i
loop
sum1 := i * j;
temp := temp||to_char(i) || ' * ' ||to_char(j) ||' = ' ||to_char(sum1) ||' ';
end loop;
dbms_output.put_line(temp );
end loop;
end;
---关于 loop循环的例子
create or replace procedure peace_loop
is
sum1 number := 0;
temp number :=0 ;
begin
loop
exit when temp >= 10 ;
sum1 := sum1+temp;
temp := temp +1;
end loop;
dbms_output.put_line(sum1 );
end;
---关于游标和loop循环的例子
create or replace procedure loop_cur
is
stu_name varchar2(100);
course_name varchar2(100);
cursor var_cur is select * from grade ;
begin
open var_cur;
loop
fetch var_cur into stu_name,course_name;
exit when var_cur%notfound;
dbms_output.put_line(stu_name|| course_name);
end loop;
close var_cur;
end;
---关于异常处理的例子
create or replace procedure peace_exp(in1 in varchar2)
is
c_n varchar2(100);
begin
select course_name into c_n from grade where stu_name = in1;
dbms_output.put_line(c_n);
exception
when no_data_found
then
dbms_output.put_line('try');
when TOO_MANY_ROWS
then
dbms_output.put_line('more');
end;
---关于异常处理的例子2
create or replace procedure peace_insert ( c_n in varchar2)
is
error EXCEPTION;
begin
if c_n = 'OK'
then
insert into course (course_name) values (c_n);
elsif c_n = 'NG' then
insert into course (course_name) values (c_n);
raise error;
else
Dbms_Output.put_line('c_n' || c_n);
end if;
commit;
exception
when error then
rollback;
Dbms_Output.put_line('ERRO');
end;
---关于包的例子 定义包
create or replace package peace_pkg
as
function test1(in1 in varchar2)
return number;
procedure test2 (in2 in varchar2);
end peace_pkg;
---关于包的例子 定义包体
create or replace package body peace_pkg
as
function test1(in1 in varchar2)
return number
as
temp number;
begin
temp := 0;
return temp;
end;
procedure test2 (in2 in varchar2)
is
begin
dbms_output.put_line(in2);
end;
end peace_pkg;
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Django中Middleware中的函数详解
Python实现爬取逐浪小说的方法
Python编写检测数据库SA用户的方法
pandas 选取行和列数据的方法详解
![](https://img.aspxhome.com/file/2023/1/124761_0s.png)
Python浅析迭代器Iterator的使用
Windows7 64位安装最新版本MySQL服务器的图文教程
![](https://img.aspxhome.com/file/2023/4/88484_0s.png)
jmeter实现接口关联的两种方式(正则表达式提取器和json提取器)
![](https://img.aspxhome.com/file/2023/1/69111_0s.png)
键盘上下键的操作代码(选择)
Python进阶学习修改闭包内使用的外部变量
![](https://img.aspxhome.com/file/2023/1/112931_0s.png)
关于最新IDEA2020.2.1,2.2,3以上破解,激活失效,重新激活的问题
![](https://img.aspxhome.com/file/2023/9/107159_0s.png)
跟老齐学Python之重回函数
Python如何爬取qq音乐歌词到本地
![](https://img.aspxhome.com/file/2023/8/97238_0s.jpg)
Python字符串对齐、删除字符串不需要的内容以及格式化打印字符
关于 SQL Server ErrorLog 错误日志说明
技术性击倒与抬杠
mysql中截取字符串的6个函数讲解
![](https://img.aspxhome.com/file/2023/9/118159_0s.png)
springboot配置数据库密码特殊字符报错的解决
MySQL数据库闭包Closure Table表实现示例
![](https://img.aspxhome.com/file/2023/3/121263_0s.png)
SQL Server中链接服务器将替代远程服务器
CentOS 7.6安装MySQL 5.7 GA版的教程图解
![](https://img.aspxhome.com/file/2023/7/118247_0s.png)