oracle 存储过程详解编程语言

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