• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

oracle存储过程学习

开发技术 开发技术 2周前 (06-08) 11次浏览

–基本数据变量
declare
name varchar2(10):=’xiang’;
begin
dbms_output.put_line(name);
end;
/
–引用数据变量
declare
name varchar2(222);
name2 student.name%type;
begin
select name as hh into name from student where class=’101′;
select name as hh into name2 from student where class=’102′;

dbms_output.put_line(‘name:’||name||’name2:’||name2);
end;
/
–纪录型变量
declare
student_v student%rowtype;
begin
select * into student_v from student where class=’102′;

dbms_output.put_line(‘name:’||student_v.name||’age:’||student_v.age);
end;
/
–条件分支
declare
num student.age%type;
begin
select age into num from student where class=’101′;
if num>10 then
dbms_output.put_line(‘大于10’);
elsif num=10 then
dbms_output.put_line(‘等于10’);
else
dbms_output.put_line(‘小于10’);
end if;
end;
/

–循环
declare
num number:=1;
begin
loop
exit when num>=10;
dbms_output.put_line(‘num的值:’||num);
insert into student values(‘张’||num,num,’101’,sysdate);
num:=num+1;
end loop;
end;
/

–游标(无参数
declare
v_name student.name%type;
v_age student.age%type;
v_class student.class%type;
–申明游标
cursor mycursor is select name,age,class from student;
begin
–打开游标
open mycursor;
loop
–判断游标是否为空
exit when mycursor%notfound;
–取出游标里面的值
fetch mycursor into v_name,v_age,v_class;
dbms_output.put_line(‘name:’||v_name||’,age:’||v_age||’,class:’||v_class);
end loop;
–关闭游标
close mycursor;
end;
/

–游标(带参数
declare
v_name student.name%type;
v_age student.age%type;
m_class student.class%type;
cursor mycursor(v_class student.class%type) is select name,age,class from student where class=”v_class”;
begin
open mycursor(‘101’);
loop
exit when mycursor%notfound;
fetch mycursor into v_name,v_age,m_class;
dbms_output.put_line(‘name:’||v_name||’,age:’||v_age||’,class:’||m_class);
end loop;
close mycursor;
end;
/

select * from user_source where type =’PROCEDURE’;
–无参数存储过程
create or replace procedure test1 is
begin
dbms_output.put_line(‘hello_world’);
end;
/
–调用存储过程方法1
begin
test1;
end;
/

–调用存储过程方法2(前提set serverout on是打开的)

exec test1;


程序员灯塔
转载请注明原文链接:oracle存储过程学习
喜欢 (0)