1、存储过程
1）：根据人员名称修改年龄
create or replace procedure SP_Update_Age( uName in varchar, Age in int)as begin update students set UserAge = UserAge + Age where userName = uName; commit;end SP_Update_Age;
调用存储过程：
begin sp_update_age('jack',2);end;
2）：根据传入的值获取该值得绝对值
create or replace procedure testnum(x in out number)isbegin if x<0 then begin x := 0-x; end; elsif x >0 then begin x := x; end; else x := 0; end if;end testnum;
测试：
declare
num number;
begin
num:= -1;
test(num);
dbms_output.put_line( 'num = ' || num );
end;
2、for 循环
declare x number := 100;begin for i in 1..10 loop if mod(i,2) = 0 then dbms_output.put_line('i:' || i || ' is even'); else dbms_output.put_line('i:' || i || ' is odd'); end if; x := x + 100; dbms_output.put_line('x value: ' || x); end loop; commit;end;
执行结果：
i: 1 is odd
x value: 200
i: 2 is even
x value: 300
i: 3 is odd
x value: 400
i: 4 is even
x value: 500
i: 5 is odd
x value: 600
i: 6 is even
x value: 700
i: 7 is odd
x value: 800
i: 8 is even
x value: 900
i: 9 is odd
x value: 1000
i: 10 is even
x value: 1100
3、while循环
create or replace procedure testwhile(i in out number)asbegin while i<10 loop begin i := i+1; end; end loop;end testwhile;
测试：
declare num number;begin num := 1; testwhile(num); dbms_output.put_line('num = ' || num);end;
输出结果：
num = 10
4、oracle相关知识
1）：我们想查看姓名=Jack 的信息,这里只有一条记录。
declare l_name varchar(100); --l_name student.sName%TYPE; 相同的效果，推荐使用这个。begin select sName into l_name from student where sName = 'Jack'; dbms_output.put_line('find the name：' || l_name);end;
输出结果：
find the name：Jack
2）：%RowType 的使用，获取某行的数据类型。
表中数据
declare rowData student%rowtype;begin select * into rowData from student where sName='Jack'; dbms_output.put_line('find the name:' || rowData.sName); dbms_output.put_line('find the age:' || rowData.sage); dbms_output.put_line('find the email:' || rowData.semail); dbms_output.put_line('find the phone:' || rowData.sphone); dbms_output.put_line('find the address:' || rowData.saddress);end;
输出结果：
find the name:Jackfind the age:21find the email:dfdf@qq.comfind the phone:2134343find the address:Singapore
