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;