oracle将age改成,oracle笔记——杂记篇

一、创建表的知识

1、创建表

--用户信息

createtabletb_operator(

IdIntprimarykey,--自动编号

NameVarchar2(12),--姓名

SexVarchar2(2),--性别

Ageint,--年龄

IdentitycardVarchar2(12),--证件号码

Workdatedate,--办证日期

TelVarchar2(12) ,--电话号码

keepMoneynumber,--押金

PasswordVarchar2(12) ,--密码

adminVarchar2(10)--管理员

);

2、为表和各列添加注释信息

commentontabletb_operatoris'用户信息';

commentoncolumntb_operator.idis'自动编号';

commentoncolumntb_operator.Nameis'姓名';

commentoncolumntb_operator.Sexis'性别';

commentoncolumntb_operator.Ageis'年龄';

commentoncolumntb_operator.Identitycardis'证件号码';

commentoncolumntb_operator.Workdateis'办证日期';

commentoncolumntb_operator.Telis'电话号码';

commentoncolumntb_operator.keepMoneyis'押金';

commentoncolumntb_operator.Passwordis'密码';

commentoncolumntb_operator.adminis'管理员';

3、对tb_operator表进行插入一列yajin

altertable表名add列名称VARCHAR2(2)

altertabletb_operatoraddyajinVARCHAR2(2)

4、----修改yajin列名称为test

altertable表名renamecolumn原名称to新名称;

altertabletb_operatorrenamecolumnyajintotest;

5、----删除test列

altertable表名dropcolumn列名

altertabletb_operatordropcolumntest

6、删除表

droptable表名;

7、修改表字段长度

altertable表名modify(字段名字符类型长度)

--将tel字段长度由12位改为18位

altertabletb_operatormodify(telvarchar2(18) )

二、对表中内容操作

1、在表中插入数据

insertinto表名(列1,列2，列3，.......)

values('值1','值2','值3',............);

insertintotb_operator(name,sex,age,identityCard,workdate,tel,password,keepmoney)

values('测试','1',21,'123456789123456789','2014-03-07','1500000000','20','20');

注意：插入数据后要commit

2、对表数据进行修改

update表名tset列=replace(列,'修改前的内容','修改后的内同')

wheret.列2='唯一定位数据的值'

--将姓名为“测试”的年龄由21岁改为18岁

updatetb_operator tsetage=replace(age,'21','18')

wheret.name='测试'

3、删除表内容

3.1、delete语句

(1)有条件删除

语法格式：deletefrom表名where条件;

如：删除tb_operator表中的name为‘测试’的数据：deletefromtb_operatorwherename='测试';

(2)无条件删除整个表数据

语法格式：delete表名;

如：删除tb_operator表中的所有数据：deletetb_operator;

3.2Truncate语句

由于delete语句删除记录时候，记录是逐条删除的，而Truncate语句删除数据时不产生回退信息；所以如果需要删除大量数据的时候使用delete则占用较多的系统资源，而如果使用Truncate则会快的多

三、序列与触发器

在例子当中创建用户信息表的时候我们会发现字段当中的ID是自动增加的，那么我们如何子数据库中自动增加呢，这里我们用到了序列和触发器的组合

1、序列

(1)、首先我们要创建序列

格式：

CreateSequence序列名称

incrementby1--增长度

startwith1--从哪里增加，就是说下一个获取的值从这个值开始

nomaxvalue--不设置最大值

order--指定一定往下增加

nocycle--不循环，CYCLE和NOCYCLE表示当序列生成器的值达到限制值后是否循环

cache10--CACHE(缓冲)定义存放序列的内存块的大小，默认为20

例子：创建序列名称为TB_ID_SEQ

CREATESEQUENCETB_ID_SEQ

INCREMENTBY1--每次增加1

STARTWITH1;--从1开始

(2)、查询创建的序列

selectTB_ID_SEQ.nextvalfromdual;

selectTB_ID_SEQ.currvalfromdual;

需注意：

第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。

(3).修改序列

序列的某些部分也可以在使用中进行修改，但不能修改SATRT WITH选项。对序列的修改只影响随后产生的序号，已经产生的序号不变。修改序列的语法如下：

ALTERSEQUENCETB_ID_SEQ

INCREMENTBY10

MAXVALUE10000

CYCLE--到10000后从头开始

NOCACHE;

(4).删除序列

DROPSEQUENCETB_ID_SEQ;

2、触发器

--建立一个触发器:将表和序列联系起来

格式：

CREATEORREPLACETRIGGER触发器名称

BEFOREINSERTON表名

FOREACHROW

BEGIN

SELECT序列名称.NEXTVALINTO:NEW.自增的字段FROMDUAL;

ENDTR_FCBOOK;

例子：创建一个TRG_TB_ID的触发器将表tb_operator和TB_ID_SEQ联系起来实现Id的自增

CREATEORREPLACETRIGGERTRG_TB_ID

BEFOREINSERTONtb_operator

FOREACHROW

BEGIN

SELECTTB_ID_SEQ.NEXTVALINTO:NEW.IdFROMDUAL;

END;

--删除触发器

--DROP TRIGGER TRG_TB_ID;

--测试数据插入，查看ID是否自动增加

-- insert into tb_operator(name,sex,age,identityCard,workdate,tel,password,keepmoney)

values('测试','1',21,'123456789123456789','2014-03-07','1500000000','20','20');

四、存储过程

百度百科：存储过程(StoredProcedure)是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它

格式：

createorreplaceprocedure存储过程名称is

begin--执行部分

操作;

commit;

end;

例子：在这里我们创建一个名称为sp_prol简单的存储过程，实现向表tb_operator插入数据

createorreplaceproceduresp_prolis

begin--执行部分

insertintotb_operator(name,sex,age,identityCard,workdate,tel,password,yajin)

values('超人','1',21,'123456789123456789','2014-03-07','150000000','20','20');

commit;

end;

注：Oracle中的AS和IS的不同，以及使用，两者基本上没有不同。

AS和IS是ORACLE为了方便而设置的同义词。

何时使用

1、在创建存储过程(PROCEDURE)/函数(FUNCTION)，以及自定义类型(TPYE)和包(PACKAGE)时，使用AS和IS无区别。

2、在创建视图(VIEW)时，只能使用AS而不能使用IS。

3、在声明游标(CURSOR)时，只能使用IS而不能使用AS

五、定时器

格式：

--定时器

declare

jobnumber;

begin

sys.dbms_job.submit(

job,--定时器ID，系统自动获得

'sp_prol;',--执行的过程名

sysdate,--定时器开始执行的时间，这样写表示立即执行

'SYSDATE + 1/(60*24)');--interval,设置定时器执行的频率，这样写每隔1分钟执行一次

commit;

end;

可以看出一般情况我们配置定时器时只需要改动一下存储过程和执行时间就可以，其中时间在实际中会经常用到

我们来补充一下

补充：

描述INTERVAL参数值

每天午夜12点''TRUNC(SYSDATE+1)''

---测试可以用dual表查查看

selecttrunc(sysdate+1)fromdual

每天早上8点30分''TRUNC(SYSDATE+1) +(8*60+30)/(24*60)''

--8个小时*60分钟+30分钟/一天24个小时*60分钟

每星期二中午12点''NEXT_DAY(TRUNC(SYSDATE),''''TUESDAY'''') +12/24''

每个月第一天的午夜12点''TRUNC(LAST_DAY(SYSDATE) +1)''

每个季度最后一天的晚上11点''TRUNC(ADD_MONTHS(SYSDATE+2/24,3),''Q'') -1/24''

每星期六和日早上6点10分''TRUNC(LEAST(NEXT_DAY(SYSDATE,''''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) +(6¡Á60+10)/(24¡Á60)''

selectjob,broken,what,interval,t.*fromuser_jobs;

参数介绍

job job的唯一标识，自动生成的

broken是否处于运行状态，N;运行；Y:停止

what存储过程名称

interval定义的执行时间

--删除定时任务,括号里为user_jobs表中job字段的编号到

begindbms_job.remove(26);

commit;

end;

六、Pl/Sql Developer设置小技巧

1、显示window list窗口

默认情况下Window List窗口是不显示的，这十分不方便

(一)在菜单项的Tools下的Preference选项中的User Interface中选择Option，在右边对于的Autosave desktop中把前面的复选框勾选上。

(二)在菜单项的Tools下的Window list选项勾上。

通过上面两步设置，关闭后下次打开Window List窗口将保持显示

2、pl/sql中设置自动提示

如输入表名+'.'后不自动弹出提示列名的做法：

在Tools->preperences->code assistant界面里把automatically activated取消即可；如果想让它自动提示，选中即可。

3、PL/SQL记住登陆密码

在使用PL/SQL Developer时，为了工作方便希望PL/SQL Developer记住登录Oracle的用户名和密码；设置方法：PL/SQL Developer 7.1.2->tools->Preferences->Oracle->Logon History，“Store history”是默认勾选的，勾上“Store with password”即可，重新登录在输入一次密码则记住了。

4、PLSQL不修改tnsname直接连数据库的方式在PLSQL的Database中直接输入192.1

PLSQL 不修改tnsname直接连数据库的方式

在PLSQL的Database中直接输入192.168.1.6:1521/VP。

其中192.168.1.6为数据库的IP；

1521为数据库端口；

VP为数据库的SID。

七、创建查询用户search

create user search identified by search

default tablespace BD_DATA

temporary tablespace TEMP;

--授予连接权限

grant connect to search;

--授予查询任何表

grant select any table to search;

--授予 查询任何字典

grant select any dictionary to search;