--基本数据变量
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;
--有参数存储过程
create or replace procedure test2(v_age in student.age%type,out_name out student.name%type) is
--申明变量不用写declare，只在匿名内部类里写
v_name student.name%type;
begin
select name into v_name from student where age =v_age;
dbms_output.put_line(‘name:‘||v_name);
out_name:=v_name;
end;
/
--调用
declare
v_name student.name%type;
begin
test2(11,v_name);
dbms_output.put_line(‘222222222222222222222name:‘||v_name);
end;
/