oracle自定义函数、存储过程2

select * from dept;
delete from dept where to_char(deptno) not like '%0';

commit;

create or replace procedure add_dept_data(p_dname dept.dname%type
       , p_loc dept.loc%type , p_count out number)
is
     v_index number := 11;
begin
     p_count := 0;
     while v_index < 19 loop
           insert into dept 
           values(v_index , p_dname , p_loc);
           v_index := v_index + 1;
           p_count := p_count + 1;
     end loop;
end;

select * from emp;

create table img_inf
(
 img_id number primary key,
 image_name varchar2(50),
 image_content blob
);

create sequence img_id_seq
start with 1
increment by 1
nomaxvalue
nominvalue
cache 10;

create or replace function sal_fun(p_sal number , p_comm number)
   return number
is
     v_sum number;
begin
     v_sum := p_sal + p_comm;
      
     return v_sum * 0.8;
end;

select * from emp;
-- 自定义函数可以嵌套。
select ename , sal_fun(nvl(sal , 0) , nvl(comm , 0)) from emp;

select * from dept;

delete from dept where to_char(deptno) not like '%0';

commit;

create or replace procedure add_dept_data(
 p_dname dept.dname%type,
 p_loc dept.loc%type default '新位置',
 p_count number)
is 
    v_index number := 11;
    v_max number;
begin
    v_max := v_index + p_count;
    while v_index < v_max loop
          insert into dept values
          (v_index , p_dname , p_loc); 
          
          v_index := v_index + 1;  
    end loop;
end;

begin
    add_dept_data(p_count=>5 , p_dname=>'aaa');
end;

create or replace function circle_zhouchang(
     p_radius number ,
     p_pi number default 3.1415)
return number
is
begin
     return p_pi * 2 * p_radius;
end;

select circle_zhouchang(20 , 3.14) from dual;

select circle_zhouchang(20) from dual;

select circle_zhouchang(p_radius=>20) from dual;

select * from user_procedures;

select * from user_source;