oracle中的procedure,function,trigger

1.procedure

--修改student表中对应id的学生姓名
create or replace procedure p_modifyName
(v_Name in varchar2,v _Id char) as --and another:out,in out
begin
update student set name=v_name where id=v_Id;
commit;
end p_modifyName;

--调用存储过程
declare

v_Name varchar2(10);
v_Id char(10);
begin
v_Name:='朱林';
v_Id:='0420622';

--调用上面创建的存储过程
p_modifyName(v_Name,v_Id);

end;

--p_modifyName('朱林','420622');

--位置标志法:要按照位置排序
--带名标志法:
declare
v_Sname varchar2(10);
v_Sid char(10);
begin
v_Sname:='朱林';
v_Sid:='0420622';

p_modifyName(v_Id=>v_Sid,v_Name=>v_Sname);

--删除存储过程
drop procedure P_modifyName;

--增加权限
grant execute on ProceduceName to UserName;
revoke execute on ProceduceName from UserName;

实例
create or replace procedure for_loop(
v_num1 in number;
v_num2 in number;)
is
i number(3);
begin
if v_num1<=v_num2 then
i:=v_num1;
for i in v_num1..v_num2 loop --这里的循环是循环到v_num2的值就结束循环了
dbms_output.putline(i);
end loop;
end if;
end;
查看所写的存储过程
select *from dba_source where type='procedure';

2.function

--函数功能:
--得到指定系,指定课程的出勤情况,如果全部出勤,则返回'full',如果出勤超过80%,则返回'Some Room',超过60%,则返回'More Room',如果出勤小余60%,返回'Lots of room',
--没有出勤,返回'Empty'
create or replace function GetClassInfo(
--系名
v_Dept classes.department%type,
--%type:v_Dept 继承了classes表中department字段的数据类型和精度
--课程名
v_Course class.course%type)
return varchar2 is
--出勤的学生数
n_CurrSNum number;
--学生人数
n_MaxSNum number;
--出勤率
n_PercentFull number;
begin
--得到学生的出勤人数和学生总人数
select current_students,max_students
--查询的返回值被赋予INTO子句中的变量
into n_CurSNum,n_MaxSnum
from classes
where department=v_Dept
and course=v_Course;
--计算出勤率

n_PercentFull:=n_CurSum/n_MaxSum*100;
--返回值
if n_PercentFull=100 then
return 'Full';
....
...
end if;
end GetClassInfo;

--调用函数
declare
v_Dept varch2(30);
v_Course varchar2(30);
v_ClassInfo varchar2(20);
begin
v_Dept:='水利系';
v_Course varchar2:='水力学';
v_ClassInfo:=GetClassInfo(v_Dept,v_Course);
dbms_output.put_line(v_ClassInfo);
end;

3.trigger

--indert,delete,update触发t_UpdateMajorStats,保持major_stats最新记录
--create major_stats
create table major_stats(
major varchar2(30),
total_credits number(3),
total_students number(4);

-- create trigger major_stats,update major_stats
create or replace trigger t_UpdateMajorStats

after insert or delete or update on student
declare
cursor c_Statistics is --define a cursor
select major,count(*) as total_students, --count(name) etc
sum(current_credits) as total_credits
from student --get new record from student

group by major;
begin
delete from major_stats; --clear major_stats
for v_StatsRecord in c_Statistics loop --
--for 循环变量 in [reverse] 初始值..结束值 loop
insert into major_stats(major,total_credits,total_students)
values (v_StatsRecord.major,v_StatsRecord.total_credits,
v_StatsRecord.total_students); --insert new record into major_stats
end loop
end t_UpdateMajorStats;
/

--"show errors;" can show error from trigger

--another simple example
create or replace trigger T_DEL_EMP
before delete on emp
for each row
insert into del_emp(depno,empno,ename)
values (:old.depon,:old.empno,:old.ename);
end;

--delete the trigger forerver
drop trigger trigger_name;
--undo trigger
alter trigger trigger_name disable;
alter trigger trigger_name enable;

--格式化代码

*每开始一个新的代码块时，应该缩进2－5个空格，注意再每个这样的代码结束后，应该取消这个缩进
*关键字用大写，这样可以区分常规代码和oracle提供的代码
*变量名采用大小写混合模式
*每条语句用一行，增强程序的可读性

刚接触oracle，希望和我一样的朋友共同进步！！