个人oracle复习

创建表空间：

create tablespace TABLESPACE_NAME

datafile ' 目录及文件名 ' size=10M

autoextend on/off;

删除表空间：

drop tablespace TABLESPACE_NAME including contents;

---

创建用户：

create user USER_NAME

identified by PASSWORD

default tablespace TABLESPACE_NAME

temporary tablespace temp TEMP_TABLESPACE_NAME quota 20M on TABLESPACE_NAME

//account lock/unlock;

//授予管理员权限

grant dba to USER_NAME

---

创建存储过程：

create or replace procedure PROCEDURE_NAME(student_ID IN char) //可以传递参数 IN, OUT, IN OUT

is/as

TOTAL_NUMBER number(10)

begin

//查询结果放入TOTAL_NUMBER

select count(*) into TOTAL_NUMBER from TABLE_NAME

where sno=student_ID;

//输出语句

//dbms_output.put_line('人数是：'||TOTAL_NUMBER);

end;

/

//执行存储过程

set serverout on

begin

PROCEDURE_NAME();

end;

exec PROCEDURE_NAME();

---

创建触发器

create trigger TRIGGER_NAME

after insert or update or delete on TABLE_NAME for each row

declare Infor char(10);

begin

if inserting then Infor :='插入';

else if updating then Infor :='更新';

else if deleting then Infor :='删除';

end;

/