Oracle子程序

定义过程




create or replace procedure mldn_proc
as
begin
dbms_output.put_line('www.mldnjava.cn');
end;


exec mldn_proc;


create or replace procedure get_emp_info_proc(p_eno emp.empno%TYPE)
as
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_count number;
begin
select count(empno) into v_count from emp where empno=p_eno;
if v_count=0 then
return;
end if;
select ename,sal into v_ename,v_sal from emp where empno=p_eno;
dbms_output.put_line('编号为'||p_eno||'的雇员姓名：'||v_ename||'，工资：'||v_sal);
end;


create or replace procedure dept_insert_proc(
p_dno dept.deptno%TYPE,
p_dna dept.dname%TYPE,
p_dlo dept.loc%TYPE)
as
v_deptCount number;
begin
select count(deptno) into v_deptCount from dept where deptno=p_dno;
if v_deptCount>0 then
raise_application_error(-20789,'增加失败：该部门已存在！');
else
insert into dept(deptno,dname,loc) values(p_dno,p_dna,p_dlo);
dbms_output.put_line('新部门添加成功！');
commit;
end if;
exception 
when others then
dbms_output.put_line('SQLERRM='||SQLERRM);
rollback;
end;


定义函数


--函数定义
create or replace  function get_salary_fun(p_eno emp.empno%TYPE)
return number
as
v_salary emp.sal%TYPE;
begin
select sal+nvl(comm,0) into v_salary from emp where empno=p_eno;
return v_salary;
end;
--调用函数
declare
v_salary number;
begin
v_salary:=get_salary_fun(7369);
dbms_output.put_line('雇员7369的工资为：'||v_salary);
end;
--过程调用函数
create or replace procedure invoke_proc
as
v_salary number;
begin
v_salary:=get_salary_fun(7369);
dbms_output.put_line('雇员7369的工资为：'||v_salary);
end;


exec invoke_proc;
--工作SQL语句调用函数
select get_salary_fun(7369) from dual;


查询子程序


select object_name,authid,object_type from user_procedures;


select object_name,authid,object_type from user_procedures;


select * from user_objects;


select * from user_source where name='GET_SALARY_FUN';
 
select * from user_source where name='MLDN_PROC';


drop procedure mldn_proc;


drop function get_salary_fun;


select object_name,authid,object_type from user_procedures;


create or replace procedure in_proc(
  p_paramA in varchar2,
  p_paramB varchar2)
as
begin
  dbms_output.put_line('执行 in_proc()过程：p_paramA = '|| p_paramA);
  dbms_output.put_line('执行 in_proc()过程：p_paramB = '|| p_paramB);
end;


declare
  v_titleA varchar2(50):='Java开发实战经典';
  v_titleB varchar2(50):='Android开发实战经典';
begin
  in_proc(v_titleA,v_titleB);
end;
/


--递归调用


create or replace procedure in_proc(
  p_paramA in varchar2,
  p_paramB VARCHAR2 default'Oracle开发实战经典')
as
begin
  dbms_output.put_line('执行in_proc过程：p_paramA='||p_paramA);
  dbms_output.put_line('执行in_proc过程：p_paramB='||p_paramB);
end;


declare
  v_titleA varchar2(50):='Java开发实战经典';
begin
  in_proc(v_titleA);
end;


create or replace function in_fun(
  p_paramA in varchar2,
  p_paramB varchar2 default'Oracle开发实战经典')
return varchar2
as
begin
  return 'Android开发实战经典';
end;


declare
  v_titleA varchar2(50):='Java开发实战经典';
  v_return varchar2(50);
begin
  v_return:=in_fun(v_titleA);
  dbms_output.put_line('in_fun函数返回值：v_return='||v_return);
end;


create or replace procedure out_proc(
  p_paramA out varchar2,
  p_paramB out varchar2)
as
begin
  dbms_output.put_line('执行out_pro()过程：p_parameA='||p_paramA);
  dbms_output.put_line('执行out_pro()过程：p_parameB='||p_paramB);
  p_paramA:='Java开发实战经典';
  p_paramB:='Android开发实战经典';
end;


declare
  v_titleA varchar2(100):='此处只有声明一个返回数据标记';
  v_titleB varchar2(100):='此内容不会传递过程，但是过程会将修改内容传回';
begin
  out_proc(v_titleA,v_titleB);
  dbms_output.put_line('调用out_pro()过程之后变量的内容：v_titleA='||v_titleA);
  dbms_output.put_line('调用out_pro()过程之后变量的内容：v_titleB='||v_titleB);
end;


create or replace function out_fun(
  p_paramA out varchar2,
  p_paramB out varchar2)
return VARCHAR2
as
begin
  p_paramA:='Java开发实战经典';
  p_paramB:='Android开发实战经典';
  return 'Oracle开发实战经典';
end;


declare
  v_titleA varchar2(100):='随便写的，职位接收内容';
  v_titleB varchar2(100):='内容不会传递的';
  v_return varchar2(200);
begin
  v_return:=out_fun(v_titleA,v_titleB);
  dbms_output.put_line('调用out_fun()函数之后变量内容：v_titleA='||v_titleA);
  dbms_output.put_line('调用out_fun()函数之后变量内容：v_titleB='||v_titleB);
  dbms_output.put_line('调用out_fun()函数的返回值：v_return='||v_return);
end;


create or replace procedure inout_proc(
  p_paramA in out varchar2,
  p_paramB in out varchar2)
as
begin
  dbms_output.put_line('执行inout_proc()过程之后的内容：p_paramA='||p_paramA);
  dbms_output.put_line('执行inout_proc()过程之后的内容：p_paramB='||p_paramB);
  p_paramA:='Java开发实战经典';
  p_paramB:='Oracle开发实战经典';
end;


declare
  v_titleA varchar2(50):='Java Web开发实战经典';
  v_titleB varchar2(20):='Oracle开发实战经典';
begin
  inout_proc(v_titleA,v_titleB);
  dbms_output.put_line('调用inout_proc()过程之后的内容：v_titleA='||v_titleA);
  dbms_output.put_line('调用inout_proc()过程之后的内容：v_titleB='||v_titleB);
end;


create or replace procedure dept_insert_proc(
  p_dno dept.deptno%TYPE,
  p_dna dept.dname%TYPE,
  p_dlo dept.loc%TYPE,
  p_result out number)
as
  v_deptCount number;
begin
  select count(deptno) into v_deptCount from dept where deptno=p_dno;
  if v_deptCount>0 then
    p_result:=-1;
  else
    insert into dept(deptno,dname,loc)values(p_dno,p_dna,p_dlo);
    p_result:=0;
    commit;
  end if;
end;


declare
  v_result number;
begin
  dept_insert_proc(68,'MLDN','中国',v_result);
  if v_result=0 then
    dbms_output.put_line('新部门增加成功！');
  else
    dbms_output.put_line('部门增加失败！');
  end if;
end;


var v_result number;
exec dept_insert_proc(50,'魔乐科技','北京',:v_result);
print v_result;


create or replace function dept_insert_fun(
  d_dno dept.deptno%TYPE,
  d_dna dept.dname%Type,
  d_dlo dept.loc%TYPE)
return number
as
  v_deptCount number;
begin
  select count(deptno) into v_deptCount from dept where deptno=d_dno;
  if v_deptCount>0 then
    return -1;
  else
    insert into dept(deptno,dname,loc)values(d_dno,d_dna,d_dlo);
    commit;
    return 0;
  end if;
end;


declare
  v_result number;
begin
  v_result:=dept_insert_fun(67,'MLDNJAVA','中国');
  if v_result=0 then
    dbms_output.put_line('新部门增加成功！');
  else
    dbms_output.put_line('部门增加失败！');
  end if;
end;


create or replace procedure dept_insert_proc(
  p_dno dept.deptno%TYPE,
  p_dna dept.dname%TYPE,
  p_dlo dept.loc%TYPE,
  p_result out number)
as
  v_deptCount number;
  procedure get_dept_count_proc(
    p_temp dept.deptno%TYPE,
    p_count out number)
  as
  begin
    select count(deptno) into p_count from dept where deptno=p_temp;
  end;
  procedure insert_operate_proc(
    p_temp_dno dept.deptno%TYPE,
    p_temp_dna dept.dname%TYPE,
    p_temp_dlo dept.loc%TYPE,
    p_count number,
    p_flag out number)
  as
  begin
    if p_count>0 then
      p_flag:=-1;
    else 
      insert into dept(deptno,dname,loc) values(p_temp_dno,p_temp_dna,p_temp_dlo);
      p_flag:=0;
      commit;
    end if;
  end;
begin
  get_dept_count_proc(p_dno,v_deptCount);
  insert_operate_proc(p_dno,p_dna,p_dlo,v_deptCount,p_result);
end;


declare
  v_sum number;
  function add_fun(p_num number)return number
  as
  begin
    if p_num=1 then
      return 1;
    else 
      return p_num+add_fun(p_num-1);
    end if;
  end;
begin
  v_sum:=add_fun(100);
  dbms_output.put_line('累加结果：'||v_sum);
end;


declare 
  TYPE dept_nested is table of dept%ROWTYPE;
  v_dept dept_nested;
  procedure useNocopy_proc(p_temp in out nocopy dept_nested)
  is
  begin
    null;
  end; 
begin
  select * bulk collect into v_dept from dept;
  v_dept.extend(2000000,1);
  useNocopy_proc(v_dept);
end;




declare
  procedure dept_insert_proc as
    pragma autonomous_transaction;
  begin
    insert into dept(deptno,dname,loc)values(60,'MLDN','北京');
    commit;
  end;
begin
  insert into dept(deptno,dname,loc)values(50,'开发部','天津');
  dept_insert_proc();
  rollback;
end;


select * from dept;


delete from dept where deptno in(15,66,50,63,67,68);
commit;