<!doctype html>
Oracle
存储过程
是存储在数据库里的
pl/sql语言基础
- 游标
xxxxxxxxxx
10
1
declare
2
cursor name is select......
3
begin
4
open name;
5
loop
6
fetch name into ....
7
exit when name%notfound;
8
end loop;
9
close name;
10
end;
xxxxxxxxxx
11
1
declare
2
cursor name is ...
3
begin
4
open name;
5
fetch name into ....
6
while name%found loop
7
...
8
fetch name into ......;
9
end loop;
10
close loop;
11
end;
%found:判断最近一次使用fetch语句是否从缓存中检索导数据，如果检索到数据，返回true，否则返回false;(经常与while连用)
%notfound:判断最近一次使用fetch语句是否从缓存中检索导数据，如果没有检索到数据，返回true，否则返回false;(经常与when连用)
pl/sql程序开发
- 存储过程
xxxxxxxxxx
10
1
create or replace procedure lisi(
2
id student.studentId%type,
3
na student.name%type)
4
as
5
begin
6
update student set studentId=id, name=na where studentId=id;
7
exception
8
when others then
9
DBMS_OUTPUT.PUT(‘无法修改‘);
10
end;
- 函数
xxxxxxxxxx
8
1
create or replace function jindao
2
return number
3
as
4
c number;
5
begin
6
select COUNT(*) into c from student where name=‘金导‘;
7
return c;
8
end jindao;
- 包
xxxxxxxxxx
5
1
create or replace package pkg
2
as
3
procedure hello;
4
FUNCTION getid(id number) return number;
5
end pkg;
- 包体
xxxxxxxxxx
13
1
create or replace package body pkg
2
as
3
procedure hello
4
as
5
begin
6
dbms_output.put_line(‘hello‘);
7
end hello;
8
FUNCTION getid(id number) return number
9
as
10
begin
11
return id;
12
end getid;
13
end pkg;
- 调用包
xxxxxxxxxx
7
1
declare
2
c number;
3
begin
4
pkg.hello;
5
c:=pkg.getid(1);
6
DBMS_OUTPUT.put_line(c);
7
end;
- 触发器
xxxxxxxxxx
5
1
create or replace trigger jindaogrant
2
after update or delete on student
3
begin
4
DBMS_OUTPUT.put_line(‘触发器发现你了‘);
5
end;