一、基础语法
基本
create or replace procedure [proc name]
as
begin
end ;
带参以及输出参数
create or replace procedure [proc name] ([param name] [ data type]
, [ out param name] out [ data type])
as
begin
end ;
局部变量
create or replace procedure [proc name] ([param name] [ data type]
, [ out param name] out [ data type])
as
[var name] [datatype] ;
begin
end ;
普通赋值与select赋值
create or replace procedure [proc name] ([param name] [ data type]
, [ out param name] out [ data type])
as
[var name] [datatype] ;
begin
[var name] := [value];
select [field name list] into [var name]
from
where ...
end ;
游标
(for循环)
declare
cursor [cursor name] as
select [field list] from ....;
[rec] [cursor name]%rowtype;
begin
for [rec] in [cursor name]
loop
dbms_output.put_line([rec].[field name] );
end loop ;
end;
(fetch循环)
declare
cursor [cursor name] as
select [field list] from ....;
[rec] [cursor name]%rowtype;
begin
open [cursor name];
loop
fetch [cursor name] into [rec];
exit when [cursor name]%notfound;
dbms_output.put_line([rec].[field name]);
end loop;
close [cursor name];
end;
============================
if语句
if then
end if ;
====
if then
//
else
//
end if ;
====
if then
//
elsif [condition] then
//
else
//
end if ;
基本
create or replace procedure [proc name]
as
begin
end ;
带参以及输出参数
create or replace procedure [proc name] ([param name] [ data type]
, [ out param name] out [ data type])
as
begin
end ;
局部变量
create or replace procedure [proc name] ([param name] [ data type]
, [ out param name] out [ data type])
as
[var name] [datatype] ;
begin
end ;
普通赋值与select赋值
create or replace procedure [proc name] ([param name] [ data type]
, [ out param name] out [ data type])
as
[var name] [datatype] ;
begin
[var name] := [value];
select [field name list] into [var name]
from
where ...
end ;
游标
(for循环)
declare
cursor [cursor name] as
select [field list] from ....;
[rec] [cursor name]%rowtype;
begin
for [rec] in [cursor name]
loop
dbms_output.put_line([rec].[field name] );
end loop ;
end;
(fetch循环)
declare
cursor [cursor name] as
select [field list] from ....;
[rec] [cursor name]%rowtype;
begin
open [cursor name];
loop
fetch [cursor name] into [rec];
exit when [cursor name]%notfound;
dbms_output.put_line([rec].[field name]);
end loop;
close [cursor name];
end;
============================
if语句
if then
end if ;
====
if then
//
else
//
end if ;
====
if then
//
elsif [condition] then
//
else
//
end if ;