Oracle_2

最新推荐文章于 2024-06-19 00:36:57 发布

tiandiren2007

最新推荐文章于 2024-06-19 00:36:57 发布

阅读量279

点赞数

文章标签： oracle table insert user books constraints

本文链接：https://blog.csdn.net/tiandiren2007/article/details/7215076

版权

Oracle

视图：

1.实际上是一条查询语句，是数据的显现方式，虚表不占存储空间

2.create or replace view myview as select *from books;

3. create or replace view myview as select *from books where price>30 with check option;向视图写数据时price必须大于30

4.采用视图封装多表连接子查询的语句，通过查询视图调用这些语句

5.多表连接的视图不能同时更新多个表的数据，可以用触发器的办法解决

6.创建只读视图：create or replace view myview as select *from books with read only;

7.创建视图语句中有group by distinct sum 等聚合函数时，也不能更新视图

8.desc user_views 当前用户下的视图 desc all_views 所有视图 desc dba_views

9.select text from user_views where view_name=’myview’查看myview视图的SQL语句

同义词:一个对象的代名词

1. create synonym dept for scott.dept;专有同义词

2. create public synonym dept for scott.dept;公共同义词

3. desc user_synonyms 本用户下的同义词

4. select synonym_name,table_name,table_ownner from user_synonyms;

序列：

1. 创建序列 create sequence myseq ( start with 1 increment by 1 order oncycle)

2. select myseq.nextval from dual; select myseq.currval from dual;要先写了nextval才能用currval

3. 应用序列：create table auto( a number,b varchar(10));

Insert into auto values(myseq.nextval,’abc’);

可以改变已有序列的自增值：alter sequence myseq

Increment by 3;

4. insert into dept values(&t,&y)采用参数形式输入

权限：sys用户下建立用户 create user tt identified by tt;

create user scott2 identified by scott2

Grant create session to tt;

Grant create table to tt;

Grant unlimited tablespace to tt;

在scott2用户下:conn scott2/scott2

Grant select on dept to tt;则在tt用户下可以查询dept

常用系统函数：select length('dfdf')from dual；字符的长度

select lengthb(‘sffff’)from dual;字节的长度

select ltrim(‘ jfd’)from dual;去掉左边的空格 rtrim trim

varchar2(10) 可变长,没有10字符时，有多少就是多少，char(10)定长，不足则补空格

查询各字段的长度：select length(deptno),length(dname) from dept;

取串：ect substr('dfdsf',3)from dual；

查询系统时间：select sysdate from dual;

查询当前时间: select current_date from dual;

转换函数： to_date, to_number ，to_char

聚集函数：avg sum max min select min(deptno) from dept

增加一个字段: alter table e add sex char(2);

删除一个字段: alter table e drop column sex;

统计性别的人数：(decode(sex,'男',1,0)) 男人数,sum(decode(sex,'女',1,0)) 女人数 from e

有字段为空时：select eid,nvl(ename,'nodata')from e; ename 为空时显示为nodata

select * from e where ename is null;

聚集函数不能做为条件出现在where后面

select ename,count(ename) from e group by ename having count(ename)>1

模糊查询：select * from e where ename like ‘李%’;

多表连接：select eid 编号, ename 姓名, sex 性别,d.dname 部门 from e join d on e.eid=d.did （内连接,完全匹配）

select eid 编号, ename 姓名, sex 性别,d.dname 部门 from e join d on e.eid=d.did（+）左连接左边的数据全部出现，右边的只有匹配的

select eid 编号, ename 姓名, sex 性别,d.dname 部门 from e join d on e.eid（+）=d.did右连接右边的数据全部出现，左边的只有匹配的

子查询：独立子查询

select * from e where eid in (select did from d);子句中不能用*

select * from e where exists(select * from d where d. did=e.eid) 子句中可以用

select * from e where exists(select did from d where d. did=e.eid)

select eid from e

union 重复的行只显示一次

select did from d

select eid from e

intersect 只显示重复的行 oracle独有的

select did from d

insert into e(eid,ename) select did,dname from d 批量插入将一个表的数据放入

create table ttt as (select * from e);复制一个表或其中的某些字段

PL/SQL基础:

Declare ……begin…..exception….end

declare

x varchar2(10):=’sfdf’;初始化

y string(10):=123;

begin

--x: =’this is …’;行注释 /* */块注释

DBMS_OUTPUT.PUT_LINE('x的只为:'||x);输出X的值,默认不输出

SET SERVEROUTPUT ON SIZE 10000即可

end;

||是拼串

分支语句:

If分支

declare

a number;

b varchar2(10);

begin

a:=2;

if a=1 then

b:='a';

elsif a=2 then

b:='b';

else

b:='c';

end if;

DBMS_OUTPUT.PUT_LINE('b的只为:'||b);

end;

case分支

declare

a number;

b varchar2(10);

begin

a:=2;

case

when a=1 then b:='a';

when a=2 then b:='b';

when a=3 then b:='c';

else

b:='d';

end case;

DBMS_OUTPUT.PUT_LINE('b的只为:'||b);

end;

循环语句：

loop

declare

a number;

begin

a:=0;

loop

a:=a+1;

if a>3 then exit when a>3;可以替换if语句

exit;

end if;

DBMS_OUTPUT.PUT_LINE('a内:'||a);

end loop;

DBMS_OUTPUT.PUT_LINE('a外:'||a);

end;

while

declare

a number;

begin

a:=0;

while a<=3 loop

a:=a+1;

DBMS_OUTPUT.PUT_LINE('a内:'||a);

end loop;

DBMS_OUTPUT.PUT_LINE('a外:'||a);

end;

for 语句

begin

for i in 1..6 loop 在in后加reverse可以实现倒序

DBMS_OUTPUT.PUT_LINE('i内:'||i);

end loop;

DBMS_OUTPUT.PUT_LINE('end');

end;

goto实现循环

declare

x number;

begin

x:=0;

<<go>>

x:=x+1;

if x<=3 then

goto go;

end if;

DBMS_OUTPUT.PUT_LINE(x);

DBMS_OUTPUT.PUT_LINE('end');

end;

异常处理

declare

test varchar2(10);

begin

select ename into test from e WHERE eid=14;将eid为了14的ename传给test 没有时使异常处理

exception

when NO_DATA_FOUND then

DBMS_OUTPUT.PUT_LINE('没有找到');

end;

自定义异常

declare

a varchar2(12);

e exception;

begin

select dname into a from d where did=2;

if a<>'a部门' then

raise e;

end if;

dbms_output.put_line(a);

exception

when e then

dbms_output.put_line('没有找到');

end;

记录：复合变量

declare

TYPE myrecord IS RECORD(

id number, --id e.eid%TYPE; id和表的eid的类型长度一样

name varchar2(12));

real_record myrecord;

begin

select eid,ename into real_record from e where eid=1;

dbms_output.put_line(real_record.id);

dbms_output.put_line(real_record.name);

end;

declare

myrecord e%ROWTYPE; 记录中的所有字段及类型都和表中一样

begin

select * into myrecord from e where eid=1;

dbms_output.put_line(myrecord.eid || myrecord.ename|| myrecord.sex);

end;

游标：显示，隐示游标的属性（%found %isopen %notfound %rowcount）

declare

cursor mycur is

select * from e;

myrec e%ROWTYPE;

begin

open mycur;

fetch mycur into myrec;

while mycur%FOUND loop

dbms_output.put_line(myrec.ename);

fetch mycur into myrec;

end loop;

close mycur;

end;

带参数的游标：

declare

cursor mycur(id number) is

select ename from e where eid=id;

name e.ename%TYPE;

begin

open mycur(1);

loop

fetch mycur into name;

exit when mycur%notfound;

dbms_output.put_line(name);

end loop;

close mycur;

end;

采用for实现

declare

cursor mycur(id number) is

select ename from e where eid=id;

begin

for cur in mycur(2) loop

dbms_output.put_line(cur.ename);

end loop;

end;

计数

declare

name varchar2(7);

cursor mycur is

select ename from e ;

begin

open mycur;

loop

fetch mycur into name;

exit when mycur%notfound or mycur%notfound is null;

dbms_output.put_line(mycur%rowcount);

end loop;

close mycur;

end;

用游标修改数据

declare

cursor mycur is

select ename from e for update;

name varchar2(12);

begin

open mycur;

fetch mycur into name;

while mycur%found loop

update e set ename=ename||'_t' where CURRENT OF mycur;

fetch mycur into name;

end loop;

close mycur;

end;

隐示游标

begin

for cur in (select ename from e) loop

dbms_output.put_line(cur.ename);

end loop;

end;

存储过程

create or replace procedure myproc(eid in varchar2)

sname varchar2(12);

begin

select name into sname from student where id=eid;

dbms_output.put_line(sname);

end myproc;

调用方法

第一种

set serverout on;

begin

myproc(2);

end;

第二种

declare

ids number;

begin

ids:=1;

myproc(ids);

end;

第三种

这种情况下，没有参数，参数是常量

execute myproc(1);

带有输出参数的

定义

create or replace procedure myproc(eid in varchar2,sname out varchar2)

begin

select name into sname from student where id=eid;

end ;

调用

declare

id varchar2(12);

sname varchar2(12);

begin

id:=2;

myproc(id,sname);

dbms_output.put_line(sname);

end;

触发器

默认不是自动提交，只能读已提交的数据 commit rollback

select * from scott2.e for update 采用for update 加锁 scott2用户要更新就只有wait,sys用户commit后才能修改

行级触发器:

触发器中不能写commit rollback

删除操作

create or replace trigger delete_d

AFTER delete on d

for each row

begin

delete from e where eid=:old.did;

end trigger;

插入操作

create or replace trigger insert_d

after insert on d

for each row

begin

insert into e(eid,ename)values(10,:new.dname);

end;

更新操作

create or replace trigger update_d

after update on d

for each row

begin

update e set id=:new.did where id=:old.did;

end;

控制不能删除

create or replace trigger yy

after delete on e

for each row

begin

if :old.eid=5 then

RAISE_APPLICATION_ERROR(-20000,'不能删除');

end if;

end;

语句级触发器

create or replace trigger dml_e

after delete or insert or update ON E

begin

if deleting then

insert into mylog values(user,sysdate,'D');

elsif inserting then

insert into mylog values(user,sysdate,'I');

else

insert into mylog values(user,sysdate,'U');

end if;

end;

触发器使用序列

create or replace trigger me

before insert on auto

for each row

declare

sno number;

begin

select myseq.NEXTVAL into sno from dual;

:new.id:=sno;

end ;

替换触发器实现多表视图更新

只能建在视图上

create or replace trigger she

instead of insert on myview

for each row

begin

insert into e values(:new.eid,:new.ename,:new.sex,:new.id);

insert into d values(:new.did,:new.dname);

end;

锁定账户

alter user tt account lock; unlock

修改账户密码

alter user tt identified by t;

对象授权scott2用户下(用户权限)

grant select(all) on e to tt with grant option; tt用户可以查看e表 tt可以将这种权限传给其它用户（with grant option）

grant execute on scott2.proc01 to t ;授权储存过程

在sys用户下将创建用户的权限给tt用户（系统权限）

grant create(drop) user to tt with admin option;tt可以将些权限给别的用户

revoke create user from tt;取消权限

角色:给角色授权，再将角色给用户

create role myrole;

grant select on scott2.d to myrole;

grant myrole to tt;

实体完整性(主键控制)

create table nn(nl varchar2(12) not null, n2 varchar2(12) );

添加对nl字段的唯一性约束

alter table nn add CONSTRAINT pk_nn primary key(nl) 约束名

域完整性:数据类型长度一样

参照完整性（外键控制）

create table mm(m1 varchar2(12),m2 varchar2(12));

alter table mm add constraint fk_mm foreign key(m1) references nn(nl)

check约束

alter table e add constraint ye check(sex='男' or sex='女') sex字段只能是男和女了

desc user_constraints查询用户下的所有约束

select constraint_name,constraint_type from user_constraints

where table_name='E'; 查询E表的约束

索引

经常查询的字段建立索引可以提高查询速度

create index yy on e(ename);

位图索引:表里的数据量很大，唯一值很少

create bitmap index jj on scott2.e(sex);

创建主键时就会创建一个索引，唯一索引也可手动创建

create unique index myidx on d(did)

SQL Loader 实现批量添加

sqlldr scott2/scott2 control=c:\cont.ctl data=c:\loader.txt

data 数据文件长度一定dfd,dff

gff,dfg

control: load data

infile 'c:\loader.txt'

append

into table mm(

m1 position(1:3) char ,

m2 position(5:7) char)

data 数据文件长度不一定dfd,dff

gffdsf,dfgd

control: load data

infile 'c:\loader.txt'

append

into table mm(

m1 char terminated by “,”,

m2 char terminated by “,”)

lsnrctl status 查看监听器的状态

lsnrctl start listener1 启动某个监听程序用户net manager配置一个监听

tiandiren2007

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle_2

Oracle视图： 1.实际上是一条查询语句，是数据的显现方式，虚表不占存储空间 2.create or replace view myview as select *from books; 3. create or replace view myview as select *from books where price>30 with check opt
复制链接

扫一扫