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