oracle创建trigger procedure function sequence plsql

最新推荐文章于 2024-04-28 15:35:13 发布

原创最新推荐文章于 2024-04-28 15:35:13 发布 · 749 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#function #trigger procedure fo

oracle 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了SQL中触发器、函数、存储过程的创建及使用方法，并详细解释了序列、参数化函数和存储过程的实现步骤，同时展示了如何通过调用函数和存储过程来解决实际问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--触发器自动插入序列
create or replace trigger tr_upload
before insert on upload
referencing new as new old as old
for each row
begin
select seq_upload.nextval into :new.upid from dual;

end;

--无参函数
create or replace function fun_up
return number is total number(9) := 0;
begin
select count(*) into total from upload;
return total;
end;

--有参函数
create or replace function fun_upload(nm in varchar)
return number is total number(9) := 0;
begin
select count(*) into total from upload where upfolder like '%'||nm||'%';
return total;
end;

--存储过程
create or replace procedure proc_upload(nm in varchar)
is
begin
declare
da number(9) :=0;
begin
da :=fun_upload(nm);
dbms_output.put_line('da is :'||da);
insert into upload(upfolder,upfilename) values(da,da);
end;
end;

--调用无参的函数
declare
a number;
begin
a := fun_up();
dbms_output.put_line('not param function:'||a);
end;

--调用有参函数
declare
a varchar(10);
e number;
begin
a := 'do';
e :=fun_upload(a);
dbms_output.put_line('param function is:'||e);
end;

--调用存储过程
declare
a varchar(10);
begin
a:='d';
proc_upload(a);
end;

--创建序列

create sequence seq_xxx
start with 1
increment by 1
maxvalue 999999999999;

--for循环存储过程

create or replace procedure proc_insert is
i number;
begin
i := 0;
for i in 1 .. 1000 loop
insert /*+append*/
into test
(c1)
select /*+parallel(t,4)*/
c1
from tmp_table t;
end loop;
commit;
end proc_insert;