oracle 存储过程
时间：2021-07-01 10:21:17
帮助过：52人阅读
向表中插入一条记录
create or replace procedure pro_insertDept
is
begin
insert into scott.dept
values(
‘77‘,
‘dog‘,
‘dog‘);
commit;
end pro_insertDept;
--执行
begin
pro_insertDept;
end;
--定义三个输入参数，插入一条记录
create or replace procedure pro_InsetIn
(num_deptno in number,
var_ename in varchar2,
var_loc in varchar2)
is
begin
insert into scott.dept
values(num_deptno,var_ename,var_loc);
commit;
end pro_InsetIn;
----------------------------------------
begin
pro_InsetIn(83,
‘dog88‘,
‘dog88‘);
end;
--定义out参数
create or replace procedure pro_selectdept
(
num_deptno in number,
var_dname out scott.dept.dname%type,
var_loc out scott.dept.loc%type
)
is
begin
select dname,loc
into var_dname,var_loc
from scott.dept
where scott.dept.deptno
=num_deptno;
end pro_selectdept;
-------------------------------------
declare
var_dname scott.dept.dname%type;
var_loc scott.dept.loc%type;
begin
pro_selectdept(4,var_dname,var_loc);
dbms_output.put_line(var_dname);
end;
--in out 参数
create or replace procedure pro_square(
num in out
number,
flag in boolean
)
is
i int:
=2;
begin
if flag
then
num:=power(num,i);
else
num:=sqrt(num);
end if;
end;
------------------------------
declare
num number:
=20;
flag boolean:=false;
begin
pro_square(num,true);
dbms_output.put_line(num);
end;
--in 参数的默认值
create or replace procedure inser_deptDefault
(
num_deptno in number,
var_dname in scott.dept.dname
%type
default ‘dog2017923‘
)
is
begin
insert into scott.dept(deptno,dname)
values(num_deptno,var_dname);
commit;
end;
---------------------
begin
inser_deptDefault(47);
end;
--指定参数名称传递参数(存储过程定义的参数)
declare
row_dept scott.dept%rowtype;
begin
inser_deptDefault(41,var_dname
=>‘dog2222222‘);
select * into row_dept
from scott.dept
where deptno
=41;
dbms_output.put_line(row_dept.dname);
end;
select * from scott.dept
oracle 存储过程
标签：har values name arch ace false scott tput replace
