oracle5

触发器

序列号：有oracle自动生成，为了在多用户同时操作数据库

create sequence name

start with 1000

increment by 1

nomaxvalue

nocycle

查询序列信息

select sequence_name,min_value,increment_by,cycle_flag　from user_sequences;

插入

insert into emp

values(emp_sequence.nextval,'','')--伪列

select sequence_name.currval,sequence_name.nextval from dual

查看用户序列
desc user_sequences;

修改序列号

alter sequence emp_seq

increment by 2

cache 30;

删除序列号

drop sequence emp_seq;

------------------------------------------------

oracle中的函数

create or replace function name(f float)

return float

begin

return 3.14*(f*f);

end;

查询函数

select object_name,object_type ,created,status　from user_objects;

调用函数

select name(4) from dual;

-----------------------------------------

游标

cursor cur_name is sql_statements;

open cur_name;

fetch cur_name into

eg

loop

fetch cur_select_emp into  name,job,sal

exit cur_select_emp%notfound;

end loop;

close cur_select_emp;

游标属性

%isopen,%rowcount,%found

---------------------------------------------

同义词:对象的别名

create public synonym department for scot.dept;

drop public synonym department

---------------------------------------------------

角色管理

create role role_name

alter role role_name

grant create session,create any table,create view to role_name

create user user_name

grant role_name to user_name with admin option

revoke role_name from user_name

drop role role_name

-----------------------------------------------------

用户管理

drop user user_name

alter user name identified by pwd account unlock

----------------------------------------

系统和对象权限

表、索引、对话、表空间

select * from dba_sys_privs

回收权限不是级联的

select * from user_col_privs_made;

grant update(col_name) on table_name to user_name with grand option;

---------------------------------------------

概要文件管理用户资源