Oracle

2023-07-21 16:46:16

<!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;