table:(表) 创建表
create table test3 (tid number,tname varchar2(20),hiredate date default sysdate);
create table emp20 as select * from emp where deptno=20;
create table empinfo as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e, dept d where e.deptno=d.deptno
修改表
alter table test3 add photo blob;
alter table test3 modify tname varchar2(40);
alter table test3 drop column photo;
alter table test3 rename column tname to username;
rename test3 to test5;
删除表
drop table test5;(没有正真的删除,只是放入了oracle的回收站中)
show recyclebin(查看回收站)
purge recyclebin;(清空回收站)
(管理员没有回收站)
flashback table TESTSAVEPOINT to before drop;(闪回回收站中的表)drop table TESTSAVEPOINT purge;
表/列约束:
check : 检查约束 gender varchar2(2) check (gender in ('男','女')),
create table student
(
sid number constraint student_pk primary key, //主键(索引)
sname varchar2(20) constraint student_name_notnull not null, //非空
gender varchar2(2) constraint student_gender check (gender in ('男','女')), //检查
email varchar2(40) constraint student_email_unique unique //唯一
constraint student_email_notnull not null, //非空
deptno number constraint student_fk references dept(deptno) on delete set null //外键(级联置空)
); (on delete cascade :级联删除)
view(视图) 虚表 创建视图(要有视图权限):
create view empinfoview
as
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e, dept d
where e.deptno=d.deptno; //可以进行DML操作,但是不建议该操作.
create or replace view empinfoview
as
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e, dept d
where e.deptno=d.deptno
with read only; //屏蔽了 DML操作.
删除视图:
drop view empinfoview;
sequence(序列) :维护表的主键,但是回滚等操作可能会出现裂缝. 创建序列:
create sequence myseq; //创建序列
select myseq.nextval from dual; //下移指针,返回序列值
select myseq.currval from dual; //返回当前指针序列值.
create table testseq (tid number,tname varchar2(20));
insert into testseq values(myseq.nextval,'aaa'); //使用序列维护主键(类似自动增长列).
删除序列
drop sequence myseq;
index(索引) :类似目录,增加查询效率 自动创建: 当定义了 PRIMARY KEY 和 UNIQUE 之后,自动在相应的列上创建唯一性索引.
创建索引:
create index myindex on emp(deptno); //B树索引
create bitmap index myindex on emp(deptno); //位图索引索引(适合查询)
删除索引:
drop index myindex;
synonym(同义词) :表的别名 (为了安全) 创建同义词:
create synonym myemp for emp; //私有同义词
create public synonym myemp for emp; //公有同义词
procedure(存储过程) 创建存储过程 (不带参数的)
create or replace procedure sayHelloWorld //不带参数
as
--说明部分
begin
dbms_output.put_line('Hello World');
end;
/
(带参数)
--给指定的员工涨100，(带参数)
create or replace procedure raisesalary(eno in number) //带一个输入参数参数的
as
--定义变量保存涨前的薪水
psal emp.sal%type;
begin
--得到涨前的薪水
select sal into psal from emp where empno=eno;
--涨100
update emp set sal=sal+100 where empno=eno;
--要不要commit(不要,调用者提交回滚)
dbms_output.put_line('涨前:'||psal||' 涨后:'||(psal+100));
end;
/
//带有out参数的存储过程(可以有返回值(OUT))
create or replace procedure queryempinfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/
调用存储过程(sql中):
1. exec sayHelloWorld();
2. begin
sayHelloWorld();
sayHelloWorld();
end;
/
function(存储函数) 创建一个存储函数
create or replace function queryempincome(eno in number)
return number
as
--定义变量保存月薪和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno;
--返回年收入
return psal*12+nvl(pcomm,0);
end;
/
//原则,只有一个返回值使用存储函数,否则使用存储过程.
package(程序包) 包头
CREATE OR REPLACE PACKAGE MYPAKCAGE AS
type empcursor is ref cursor;
procedure queryEmpList(dno in number, empList out empcursor);
END MYPAKCAGE;
包体
CREATE OR REPLACE PACKAGE BODY MYPAKCAGE AS
procedure queryEmpList(dno in number, empList out empcursor) AS
BEGIN
open empList for select * from emp where deptno=dno;
END queryEmpList;
END MYPAKCAGE;
trigger(触发器): 类似对insert,update,delete的监听器 创建触发器: 语句级触发器(每条语句操作一次,无论改变多少行)
create trigger abcd
after/before insert/delete/update[of 列名]
on emp
declare
begin
dbms_output.put_line('成功操作了表格');
end;
/
/* 触发器应用一：实施复杂的安全性检查 禁止在非工作时间插入新员工
周末:to_char(sysdate,'day') in ('星期六','星期日') 上班前 下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 17 */
create or replace trigger securityemp
before insert
on emp
begin
if to_char(sysdate,'day') in ('星期六','星期日','星期三') or
to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
--禁止insert
raise_application_error(-20001,'禁止在非工作时间插入新员工');
end if;
end;
/
行级触发器(每改变一行操作一次)
create or replace trigger checksalary
before update
on emp
for each row //有此行就是行级触发器
begin
--if 涨后的薪水 < 涨前的薪水 then
if :new.sal < :old.sal then
raise_application_error(-20002,'涨后的工资不能少于涨前的工资。
涨前:'||:old.sal||' 涨后:'||:new.sal);
end if;
end;
/