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

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

set serverout on creaate or replace function fun_getSalSumByDeptno(p_deptno number) return numberis    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 ondeclare    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)isbegin    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_empis    --定义变量    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_empis    -- (一) 定义存储过程，添加雇员信息    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 onbegin    package_emp.proc_addemp(100,'派出所'，5000，40)；    dbms_output.put_line('添加成功！');end;/

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

set serverout ondeclare    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<>10begin     --删除前将数据插入到备份表    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_loginafter logon  on databasebegin     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;