Oracle 存储过程 触发器 游标 账号数据备份和复原 包管理

 ---存储过程

create or replace procedure
pro1(dno number,asal out number)
is

begin
  select avg(sal) into asal from
  emp where deptno=dno;
end;

declare
r number;
begin
  pro1(10,r);
  dbms_output.put_line(r);
end;

---触发器
---行级触发器
--更新--
create or replace trigger trg1
before update on emp
for each row
---declare:定义触发器变量关键字
begin
  :new.sal:=:old.sal;
end;

update emp set sal=1;
select * from emp;

--删除--
create or replace trigger trg2
after delete on emp
for each row
begin
  dbms_output.put_line(:old.ename||'被删除了');
end;

delete from emp;

---视图触发器

select * from  emp_view

create view emp_view
as
select * from emp

create or replace trigger trg_viev_emp
instead of update on emp_view
for each row
begin
  if :new.sal>10000 then
    update emp set sal =:old.sal
    where ename=:old.ename;
    dbms_output.put_line(:old.ename||'更改工资大于10000被阻止');
   else
    update emp set sal =:new.sal
    where ename=:old.ename;
  end if;
end;

update emp_view set sal=30000 where ename='SCOTT'

select * from emp_view

delete from emp;

drop table emp 
select * from emp

SCOTT账号备份和复原

exp scott/tiger@ORCL file=H:\OracleBack\scott_back.dmp owner=scott

imp scott/tiger@ORCL file=H:\OracleBack\scott_back.dmp ignore=y full=y
---创建包
create or replace package pkg01
is
  procedure sum(a number,b number,r out number);

  ---函数
  function fun(eno number) return varchar2;

  ----游标
  cursor cur return emp%rowtype;

  ---显示游标数据的存储过程
  procedure showEmp;
  
end pkg01;

create or replace package body pkg01
is
   ---指定游标结果集
   cursor cur return emp%rowtype
   is
   select * from emp;
--存储过程
   procedure sum(a number,b number,r out number)
   is
   begin
     r:=a+b;
   end sum;

   function fun(eno number)
   return varchar2
   is
    en emp.ename%type;
   begin
     select ename into en from emp
     where empno=eno;
     return en;
   end fun;

   procedure showEmp
   is
   begin
     for e in cur
       loop
         dbms_output.put_line(e.ename||','||e.sal);
       end loop;
   end showEmp;

end pkg01;