oracle-day6

--存储过程
create or replace procedure pro_query_emp_bydeptno(v_deptno number,csr_emp out sys_refcursor)
is 
begin
  --打开游标并赋值
  open csr_emp for select * from emp where deptno=v_deptno;
end;

--创建包的头部
create or replace package pkg_emp
is
   function fun_query_ename_byeno(v_empno emp.empno%type) return varchar2;
   procedure pro_query_ename_byeno(v_empno in number,v_ename out varchar2);
   procedure pro_query_emp_bydeptno(v_deptno number,csr_emp out sys_refcursor);
end pkg_emp;

--创建包身体
create or replace package body pkg_emp
is
function fun_query_ename_byeno(v_empno emp.empno%type)
  return varchar2 -- 返回值类型
 is
  t_ename emp.ename%type;
begin
  select ename into t_ename from emp where empno = v_empno;
  return t_ename;
exception
  when no_data_found then
    return '编号为' || v_empno || '员工不存在';
end;

procedure pro_query_ename_byeno(v_empno in number,v_ename out varchar2)
as --in 入参 out 出参
begin
  select ename into v_ename from emp where empno=v_empno;
exception
  when no_data_found then
     dbms_output.put_line('工号'||v_empno||'不存在');
end;

procedure pro_query_emp_bydeptno(v_deptno number,csr_emp out sys_refcursor)
is
begin
  --打开游标并赋值
  open csr_emp for select * from emp where deptno=v_deptno;
end;
end pkg_emp;

----------------------------------------------------------

 create table dept_bak as select * from dept where 1=2;
  alter table dept_bak add addtime date;

--创建存储过程  
create or replace procedure pro_bakDept
is  
  t_deptid number(5);
begin
    t_deptid:=seq_dept_id.nextval;
    --插入数据
   insert into dept_bak values(t_deptid,'开发'||t_deptid||'部门',t_deptid||'楼',sysdate);
   commit;
  end;
--创建序列
create sequence seq_dept_id;

 call pro_bakDept(); 
 
 select * from dept_bak;

--定时器
declare
 t_job number(5):=0;
begin
  sys.dbms_job.submit(t_job,
                       'pro_bakDept;',
                     to_date('12-11-2018 16:35:00', 'dd-mm-yyyy hh24:mi:ss'),
                      'trunc(sysdate,''mi'')+1/(24*60)');
  commit;
end;