oracle 包、触发器、表空间学习笔记

包：用于逻辑组合相关的自定义类型、常量、变量、游标、过程、函数。包由包体和包规范两部分组成。
包的创建：
create [or replace] package  pkg_name
is|as
      ——定义
procedure proc_**();
function func_**();
end;

包体的创建：  
create [or replace] package body pkg_name
is|as
     ——定义
procedure proc_**()
begin
...
end;

function func_**()
begin
...
end;
end;
例子：
create or replace package pkg_name
is
procedure proc_name1;
end;
create or replace package body pkg_name
is
          procedure proc_name1 is    name emp.ename%type; begin    select ename into name from emp where empno=7788; end;
end;

触发器：存放在数据库中，并且被隐含执行的存储过程。触发器分语句触发器和行触发器。
       语句触发器的语法： 
create [or replace] trigger trg_name
       {before|after|instead of} insert [or update or delete] of 字段
         on table_name
       [declare]
        pl/sql block;
       行触发器的语法：   
create or replace trigger trg_name
      {before|after|instead of} insert [or update or delete] of 字段
        on table_name
       [REFERENCING NEW AS New OLD AS Old]
       for each row [when condition] when new.  old.
      [declare]
       pl/sql block;
例子：
create or replace trigger trg_name
before insert on emp1
referencing new as new old as old
for each row
declare
begin
    :new.sal := 1000;
end;

java source
create or replace and compile java source named Change
as
java代码

表空间/临时表空间的创建及删除
grant create tablespace to scott;
create tablespace user1 datafile 'c:\test.dbf' size 5M autoextend on next 1280k maxsize unlimited;
create temporary tablespace temp1 tempfile 'c:\temp.dbf' size 5M autoextend on next 640k maxsize unlimited;

alter user soctt quota unlimited on user1;

drop tablespace user1 including contents and datafiles;
drop tablespace temp1 including contents and datafiles;