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;