--向表中插入一条记录
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
