Oracle 之 存储过程|程序包|触发器|视图|同义词

1.定义函数：统计部门编号为10的所有员工工资总和

set serverout on 
creaate or replace function fun_getSalSumByDeptno
(p_deptno number) return number
is
    v_salsum number;
begin 
    select sum(sal) into v_salsum from emp where deptno=p_deptno;
    return v_salsum;

exception
    when no_data_found then
        dbms_output.put_line('没有查询到你要寻找的数据!');
    when others then
        dbms_output.put_line('其他错误！');
end;
/

2.调用函数

set serverout on
declare
    v_deptno emp.deptno%type:=&p_deptno;
    v_salsum emp.sal%type;
begin
    v_salsum:=fun_getSalSumByDeptno(v_deptno);
    dbms_output.put_line(v_deptno||'部门下的员工工资总和为：'||v_salsum);
end;
/

3.存储过程实现：

统计某个部门编号下的所有员工工资总和
create or replace procedure proc_getSalSumByDeptno
(p_deptno in number,p_salsum out number)
is
begin
    select sum(sal) into p_salsum from emp where deptno=p_deptno;
end;

4.调用存储过程

set serverout on 
declare 
    v_deptno emp.deptno% type:=&p_deptno;
    v_salsum emp.sal%type;
begin
    proc_getSalSumByDeptno(v_deptno,v_salsum);
    dbms_output.put_line(v_deptno||'部门下的员工工资总和为：'||v_salsum);
end;
/

---

将有联系的对象打成包,方便使用

包中对象包括储存过程,函数,游标,自定义类型和变量,可以在PL_SQL块中应用这些对象

5.定义程序包的包头

create or replace package package_emp
is
    --定义变量
    minsal emp.sal%type;
    maxsal emp.sal%type;

    -- 定义存储过程，添加雇员信息
    procedure add_emp(p_empno number,p_ename varchar2,p_sal number,p_deptno number);

    -- 定义函数，根据员工编号查询员工工资
    function fun_getSalByEmpno(p_empno number) return number;
end package_emp;
/

只有当包头编辑成功后才能编辑包体.其中的函数名与过程名须和包头中的函数过程一样.

6.定义程序包的包体

create or replace package package_emp
is
    -- (一) 定义存储过程，添加雇员信息
    procedure add_emp(p_empno number,p_ename varchar2,p_sal number,p_deptno number);
    is
        -- 定义一个字符串变量
        v_info varchar2(50);
    begin
        if p_sla between minsal and maxsal then
            insert into emp(empno,ename.sal,deptno) values
        (p_empno,p_ename,p_sal,p_deptno)
        returning empno||'-'||ename||'-'||sal||'-'||deptno 
        into v_info;
            dbms_output.put_line(v_info)
        else 
            dbms_output.put_line('工资不在最大值和最小值之间，不能实现添加操作！');
        end if;
    end;

    -- (二) 定义函数：根据员工编号查询员工工资
    function fun_getSalByEmpno(p_empno number) return number
    is
        --定义接受值的变量
        v_sal emp.sal%type;
    begin
        select sal into v_sal from emp where empno=p_empno;
        return v_sal;
    end;

    -- 初始化部分
    begin
        sekect min(sal),max(sal) into minsal,maxsal from emp;
end package_emp;
/

7.调用程序包的内容

set serverout on
begin
    package_emp.proc_addemp(100,'派出所'，5000，40)；
    dbms_output.put_line('添加成功！');
end;
/

8.调用程序包的内容：函数

set serverout on
declare
    v_empno emp.empno%type:=&p_empno;
    v_sal emp.sal%type;
begin
    v_sal:=package_emp.fun_getSalByEmpno(v_empno);
    dbms_output.put_line(v_empno||'的员工工资为：'||v_sal);
ebd;
/

9.快速复制表结构，但不复制表数据
create table del_emp as select * from emp where 1=2;

10.创建行级触发器：在删除 deptno!=10的数据的时候进行触发备份

create or replace trigger tr_del_emp
    before delete --触发的时机是删除前触发
    on emp
    for each row
    when old.deptno<>10
begin
     --删除前将数据插入到备份表
    insert into del_emp(empno,ename,sal,ddeptno) values
(:old.emp,:old.ename,:old.sal,:old.deptno);
end;
/

11.在删除数据时触发
delete from emp where empno=7369;

12.查看触发器的备份表
select empno,ename,sal,deptno from del_emp;

13.删除触发器
drop trigger tr_del_emp;

---

14.创建模式触发器备份表

create table event_ddl(
    event varchar2(20),
    username varchar2(10),
    owner varchar2(10),
    objname varchar2(20),
    objtype varchar2(20),
    time date
);

15.创建模式触发器: 操作表来讲

create or replace trigger tr_ddl
    after ddl on holly.schema
    -- 记录holly模式的所有ddl的操作
begin
    insert into event ddl values
    (ora_sysevent,
    ora_login_user,
    ora_dict_obj_owner,
    ora_dict_obj_name,
    ora_dict_obj_type,
    sysdate
);
end;
/

16.创建表时触发
conn holly/sys;

17.创建表删除表
create table temp113(id number);
drop table temp113;

18.查看模式备份表
conn scott/tiger;
select event,objname,objtype from event_ddl;

---

19.创建数据库级别的触发器备份表

create table log_table
(
    username varchar2(20),
    login_time date,
    logginoff_time date,
    address varchar2(20)    
);

20.创建数据库级别的触发器

create or replace trigger tr_login
after logon  on database
begin 
    insert into log_table(username,login_time,address) values
(ora_login_ser,sysdate,ora_client_ip_address);
end;
/

21.切换用户时触发数据库级别的触发器

conn holly/sys;
conn system/accp;
comm scott/tiger;

22.查看数据库级别触发器备份表
select username,login_time,address from log_table;

---

23.创建【视图】
create or replace view view_empdept
as
select d.dname,count(d.dname) count // 聚合函数给别名
from emp e,dept d
where e.deptno(+) = d.deptno
group by d.dname;

24.查看视图
select * from view_empdept;

---

25.创建公共同义词【同义词：跨用户访问】
create table synonym myemp from scott.emp;

26.切换用户
conn holly/sys;

27.查看同义词
select empno,ename from myemp;