oracle 11g 学习笔记

1、Check规则

Check (Agebetween15and30 )把年龄限制在15~30岁之间

2、新SQL语法

在调用某一函数时，可以通过“=>”符号来为特定的函数参数指定数据。而在Oracle11g中，在SQL语句中也可以这样的语法，例如：

Select f( x => 6)from dual ;

3、将sequence的值赋给变量

11g之前的赋值方式Select seq_x.next_val intov_x from dual ;

11g赋值方式v_x :=seq_x.next_val ;

4、SQL的功能

Ø4.1数据定义

Create ,Drop ,Alter

Ø4.2数据操纵

Select , insert , update , delete

Ø4.3数据控制

Grant, Revoke

5、Oracle创建语法

Ø1、表空间

createtablespacelq_oradatadatafile'd:\oracle_test\user_data01.dbf'

size32mautoextendonnext32mmaxsize2048mextentmanagementlocaluniformsize16m ;

Ø2、临时表空间

createtablespacelq_oradata_temptempfile

'd:\oracle_test\temp_data01.dbf'

size32mautoextendonnext32mmaxsize2048mextentmanagementlocaluniformsize16m ;

Ø3、创建用户

create user lq identified by lq

default tablespacelq_oradata

temporary tablespace temp;

注：此处的temp为系统临时表空间，也可以自定义临时表空间；

Ø4、给用户授权

grant connect,resource to lq;

Ø5、创建表

createtableit_employees (

employee_idnumber(6)notnullunique,

first_namevarchar2(30) ,

last_namevarchar2(30)notnull,

emailvarchar2(30),

phone_numbervarchar2(15),

job_idvarchar2(10),

salary_numbernumber(8,2),

manager_idnumber(6)

)

添加数据

insertintoit_employees(employee_id,first_name,last_name,email,phone_number,job_id,salary_number,manager_id,birth_date)

values(seq_employee.nextval,'Micheal','Joe','mj_aui01@188.com','13149258943','it_prog',5000.67,seq_employee.nextval,to_date('2011-11-01','yyyy-mm-dd'));

Ø6、创建视图

给用户分配创建视图的权限

A:首先授予查询所有表的权限

grant select any

table tolq;

B:再次授予查询所有字典表的权限

grant select any

dictionary tolq;

C：如果已经分配以上权限，还是权限不足,则可分配管理员权限

grant dba tolq;

createviewprog_employees

as

selectemployee_id,first_name,last_name,email,phone_number,salary_number,manager_id

fromit_employees

wherejob_id='it_prog'

with check option;

注：可加上with check option;

Ø7、创建索引

聚簇索引：指索引向的顺序与表中记录的物理顺序相一致的索引组织。

用户可以在查询频率最高的列上建立聚簇索引，从而提高查询效率。由于，聚簇索引是将索引和表记录放在一起存储，所以在一个基表上只能创建一个聚簇索引；在建立聚簇索引后，由于更新索引列数据时会导致表中物理顺序的变更，系统代价较高，因此对于经常更新的列不宜建立聚簇索引。

Create [unique] [cluster] index [索引名]

On <表名> (<列名>[<次序>]，<列名>[<次序>]，<列名>[<次序>]……)

其中，unique表示此索引的每一个索引值不能重复，对应唯一的数据记录。Cluster表示要创建的索引为聚簇索引。索引可以建立在对应表的一列或者多列上，如果是多个列，各个列之间需要用逗号分隔。<次序>用于指定索引值的排列次序，ASC表示升序，DESC表示降序，默认为ASC.

Create indexit_lastnameonit_employees(last_name)

描述：执行后会在表it_employees的last_name列上建立一个索引。

而it_employees表中的数据将按照last_name值升序存放。

6、Oracle删除语法Drop

Ø6.1：删除表

Droptable<表名>

删除表时，表中的数据、在该表上建立的索引一并都会被删除。

Ø6.2：删除视图

Drop view <视图名>

Ø6.3：删除索引

Drop index <索引名>

7、Oracle修改语法Alter

Alter table <表名>

[add <新列><数据类型>[完整性约束]]

新增birth_date列

Altertableit_employeesaddbirth_date date;

[Drop<完整性约束>]

删除employee_id字段的unique约束

Altertableit_employeesDrop unique(employee_id);

[Modify <列名> [数据类型]]

将manager_id字段改为8位

Altertableit_employeesmodifymanager_id number(8)

8、Oracle查询select

Ø8.1：简单查询

8.1.1使用from子句指定表

select * from hr.countries ;

select * from hr.countries,hr.departments;

8.1.2使用select指定列

Selectcolumn name_1, ……

column name_n

Fromtable_name_1 , table_name_2 .

8.1.3算术表达式

Selectem.first_name,em.last_name,

em.salary_number*(1+0.2) "new_salar y"

fromit_employees em;

查询出员工薪资上调10%之后的结果，为了提高刻度性，可以为列重新制定显示标题"new_salar y"；

注：如何列标题中包含一些特殊字符，例如空格等，则必须使用双引号将列标题扩起来。

8.1.3distinct关键字

Select distinct job_id from it_employees;

Ø8.2：where子句

8.2.1条件表达式

selectem.employee_id, em.first_name,em.last_namefromit_employees emwhereem.first_namelike'B%';

判断first_name以“B”开头的雇员；

8.2.2连接运算and / or

selectem.employee_id, em.first_name,em.last_namefromit_employees em

whereem.phone_number='15321981677'

andem.salary_number >6000;

用and做连接符，电话号码和薪酬为判断条件；

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

selectem.employee_id, em.first_name,em.last_namefromit_employees em

whereem.first_name='Britney'

orem.salary_number <6000;

8.2.3 NULL值

首先插入一条记录，此处Email只为NULL

insertintoit_employees(employee_id,first_name,last_name,email,phone_number,job_id,salary_number,manager_id,birth_date)

values(seq_employee.nextval,'Britney','Joe',NULL,'15321981677','it_prog',10000.67,seq_employee.nextval,to_date('2011-11-01','yyyy-mm-dd'));

查询值为null的语句：

select*fromit_employees emwhereem.emailisnull;

select*fromit_employees emwhereem.emailisnotnull;

Ø8.3：order by子句

selectem.last_name, em.job_id , em.salary_number

fromit_employees em

whereem.salary_number >5000

orderbyem.job_id,em.salary_numberdesc;

order by子句后边可指定多个列名，首先根据第一列排序，当第一列值相同时，再对第二列进行比较排序，以此类推；

Ø8.4：groupby子句

通过Group By进行的查询:

selectem.job_id,avg(em.salary_number),sum(em.salary_number),max(em.salary_number),count(em.job_id)

fromit_employees emgroupbyem.job_id ;

可以在group by后使用rollup或者cube进行汇总，在查询结果中都会附加一条汇总信息，sql如下：

selectem.job_id,avg(em.salary_number),sum(em.salary_number),max(em.salary_number),count(em.job_id)

fromit_employees emgroupbyrollup(em.job_id) ;

Ø8.5：Having子句

selectjob_id ,avg(SALARY_NUMBER) ,sum(SALARY_NUMBER),max(SALARY_NUMBER),count(*)

fromIT_EMPLOYEESgroupbyJOB_IDhavingavg(SALARY_NUMBER) >5500

通常与Group by子句一起使用，在完成对分组统计后，可以使用Having子句对分组的结果做进一步筛选；

Ø8.6：多表连接查询

8.6.1基本形式

将表employees和表departments相连接，从而生成一个笛卡积：

Selectemployee_id, last_name , department_name fromemployees , departments ;

8.6.2条件限定

selectit.employee_id,it.last_name, dm.department_name

fromit_employees it , departments dm

whereit.department_id = dm.department_id;

selectit.employee_id,it.last_name, dm.department_name

fromit_employees it , departments dm

whereit.department_id = dm.department_id

anddm.department_name ='Shipping';

8.6.3表别名

8.6.4 Join连接

Fromjoin_table1join_typejoin_table2

[ON(join_Condition)]

8.6.4.1内连接

select em.employee_id, em.last_name,dep.department_name

from it_employees eminnerjoindepartments dep

on em.department_id = dep.department_id

where em.job_id='it_prog';

8.6.4.2自然连接

Selectem.employee_id, em.first_name, em.last_name, dep.department_name fromit_employees emnatural

joindepartmentsdepwhere dep.department_name=’Sales’

8.6.4.3外连接

左外连接：

selectem.employee_id, em.last_name, dep.department_name

fromit_employees emleftouterjoindepartments dep

onem.department_id = dep.department_id

whereem.job_id='it_prog';

右外连接：

selectem.employee_id, em.last_name, dep.department_name

fromit_employees emrightouterjoindepartments dep

onem.department_id = dep.department_id

wheredep.location_id =1700;

完全外连接：

selectem.employee_id, em.last_name, dep.department_name

fromit_employees emfullouterjoindepartments dep

onem.department_id = dep.department_id

wheredep.location_id =1700or em.job_id=’it_prog’;

8.6.4.4自连接

selectem1.last_name "manager" , em2.last_name "employee"

fromit_employees em1leftjoinit_employees em2

onem1.employee_id = em2.manager_id

orderbyem1.employee_id;

Ø8.7：集合操作

Union (并运算)、UNION ALL、InTerSect(交运算)、Minus (差运算)

8.7.1Union

/*** < unionall>***/形成的结果集中包含有两个结果集中重复的行。

selectit.employee_id, it.last_namefromit_employeesit

whereit.last_namelike'%e'orit.last_namelike'j%'

union

selectem.employee_id , em.last_namefromit_employees em

whereem.last_namelike'j%'orem.last_namelike'%o%';

8.7.2Intersect

Intersect查询结果保留Last_name以j开头的雇员

selectit.employee_id, it.last_namefromit_employeesit

whereit.last_namelike'%e'orit.last_namelike'j%'

intersect

selectem.employee_id , em.last_namefromit_employees em

whereem.last_namelike'j%'orem.last_namelike'%o%';

8.7.3Minus

例：返回Last_name以e结尾的集合

selectit.employee_id, it.last_namefromit_employeesit

whereit.last_namelike'%e'orit.last_namelike'j%'

minus

selectem.employee_id , em.last_namefromit_employees em

whereem.last_namelike'j%'orem.last_namelike'%o%';

Ø8.8：子查询

8.7.1in关键字

selectemployee_id, last_name , department_id

from it_employees

where department_idIn

(

selectdepartment_id fromdepartments

wherelocation_id=1700

)

8.7.2Exists关键字

selectemployee_id, last_name , department_id

from it_employeesem

whereexists

(

select * fromdepartmentsdep

where em.department_id= dep.department_id

andlocation_id=1700

)

8.7.3比较运算符

selectemployee_id , last_name , job_id , SALARY_NUMBER

fromit_employees

whereJOB_ID ='it_prog'

andSALARY_NUMBER >= (selectavg(SALARY_NUMBER)fromit_employeeswherejob_id='it_prog')

9、数据操纵

Ø9.1：Insert

9.1.1一般Insert

Insertinto[user.]table[@db_link] [([column1, column2… culumnx])]

Values([express1],[ express2]……)

insertintojobs (job_id,job_title,min_salary,max_salary)

values('it_test','测试员',3500.00,8000.00);

/***注：以下Desc… insert未测试成功***/

descjobs

insertintojobsvalues('it_dba','管理员',5000.00,15000.00)

9.1.2批量Insert

insertintoemployees(employee_id,first_name,last_name,email,phone_number,job_id,salary_number,manager_id)

selectem.employee_id,

em.first_name,

em.last_name,

em.email,

em.phone_number,

em.job_id,

em.salary_number,

em.manager_id

fromit_employees em , departments dep

whereem.department_id = dep.department_id

anddep.department_name='Shipping';

Ø9.2：Update

updateemployeessetsalary_number = salary_number*1.15

wherejob_id ='it_prog'

updateit_employeessetsalary_number=

(

select avg(salary_number) fromit_employees

where job_id='it_prog'

)

whereemployee_id=11;

Ø9.3：Delete

deletefromit_employeeswhereemployee_id=31;

Ø9.4: Truncate

truncatetableemployees;

10、数据控制

Ø10.1 Grant语句

Grantselect on table it_employees to User1 ;

Grantallprivileges on table it_employees , jobs toUser2 ;

Grant selecton table department to public ;

Grant update(employee_id), select on tableit_employees to User4;

Grant insert on table departmentto user5 with grant option ;

Grant createtab on database db_employees toUser8 ;

Ø10.2Revoke语句

Revokeupdate(employee_id)on tableit_employees from user4 ;

Revokeselect on table departmentfrompublic;

Revokeinsert on table department from User5 ;

11、Oracle常用函数

Ø11.1字符类函数

11.1.1 ASCII()

Selectascii('A')big_a,ascii('a') small_afromdual ;

11.1.2CHR()

Selectchr(65) ,chr(97),chr(100)fromdual ;

11.1.3Concat( c1,c2 )

selectconcat('oracle','11g')fromdual ;

返回的是：

11.1.4initcap(C1)

selectinitcap('oracleuniversal installer')fromdual ;

返回的结果：

11.1.5 instr(c1,[c2,,[j]])

select instr('Moisossoppo','o',3,3) from dual;

返回的结果：

select instr('Moisossoppo','o',-2,3) fromdual ;

返回的结果：

11.1.6length(C1)

selectlength('oracle

11g') lgfromdual ;

结果：

11.1.7 lower(C1)

select*fromjobs;

结果：

selectlower(job_id)fromjobswherelower(job_id)like'it%'

结果：

11.1.8Ltrim(c1,c2)

selectltrim('Moisossoppo','Mois')fromdual ;

结果：

11.1.9replace(c1,c2,[c3])

selectreplace('feelblue','blue','yellow')fromdual ;

结果：

11.1.10substr(c1,,[j])

selectsubstr('Message',1,4)fromdual ;

Result :

Ø11.2数字类函数

Ø11.3日期类函数

Ø11.4转换类函数

Ø11.5聚集类函数

第四章：Oracle PL/SQL语言及编程

Ø4.1 PL/SQL简介

4.1.1pl/sql的基本结构

4.1.2pl/sql注释

A：单行注释

createtableclasses (

class_idnumber(6)primarykey,

departmentchar(3)

)

altertableclassesaddcoursenumber ;

createsequenceseq_class_id

incrementby1--每次增加1

startwith100--从100开始

nomaxvalue--不设置最大值

nocycle--一直累加不循环

cache10;

declare--单行注释声明

v_departmentchar(3);--保存3个字符的变量

--系统代码

v_Coursenumber;--保存课程号的变量

begin

v_department:='tes';

v_Course :=19;

insertintoclasses(class_id,department , course)

values(seq_class_id.nextval,v_department,v_Course);

end;

commit;

B：多行注释

/**

多行注释

**/

declare

v_departmentchar(3);

v_Coursenumber;

begin

v_department :='duo';

v_Course :=99;

insertintoclasses(class_id,department ,course)

values(seq_class_id.nextval,v_department,v_Course);

end

;

commit;

4.1.3pl/sql字符集

A：合法字符集

B：分界符(delimiter)

4.1.4pl/sql数据类型

A：数字类型

B：字符类型

C：日期类型

D：布尔类型

E：type定义的数据类型（rowtype）

例：

/**

pl/sql数据类型使用type定义teacher_record记录变量

**/

typeteacher_recordisRecord--该record定义之后，在以后的使用中就可以定义基于teacher_record的记录变量

(

tidnumber(5)notnull:=0,

namevarchar2(50),

titlevarchar2(50),

sexchar(1)

)

--定义一个teacher_record类型的记录变量

ateacher

teacher_record ;

4.1.5pl/sql变量和常量

A：定义常量

Pass_Scoreconstantinteger:=60;

B：定义变量

C：变量初始化

4.1.6pl/sql语句控制结构

A：选择结构

(1)If语句

(2)Case语句

declare

v_gradevarchar2(20) :='及格';

v_scorevarchar2(50);

begin

v_score:=Casev_grade

when'不及格'then'成绩< 60'

when'及格'then'60<=成绩< 70 '

when'中等'then'70<=成绩< 80 '

when'良好'then'80<=成绩< 90 '

when'优秀'then'9 0<=成绩< 100 '

else'输入有误'

end;

dbms_output.put_line(v_score);

end;

运行结果如下：

B：NULL结构

/**

null结构(添加变量是否为null的判断)

**/

declare

v_number1number;

v_number2number;

v_resultvarchar2(7);

begin

ifv_number1isnullorv_number2isnullthen

v_result ='Unknown';

elsifv_number1

v_result ='yes'

elsev_result ='no'

end;

end;

C：循环结构

(1)Loop…exit … end语句

declare

control_valnumber:=0;--初始化control_val值为0

begin

loop--开始循环

dbms_output.put_line(control_val);--打印测试

ifcontrol_val >5then--如果control_val的值大于5则退出循环

exit;

endif;

control_val :=control_val+1;--否则改变control_val的值

endloop;--结束循环

end;

(2)Loop… exitwhen …end语句

declare

control_valnumber:=0;--初始化control_val值为0

begin

loop--开始循环

dbms_output.put_line(control_val);--打印测试

exitwhencontrol_val >6;--如果control_val的值大于6则退出循环

control_val:=control_val+1;

endloop;

end;

(3)While…loop…end;

(4)declare

(5)control_valnumber:=0;

(6)begin

(7)whilecontrol_val <=5--如果变量小于或等于5就循环

(8)loopcontrol_val:=control_val+1;

(9)dbms_output.put_line(control_val);--打印测试

(10)endloop;

(11)end;

(4)for… in … loop …end

这是个预知循环次数的循环控制语句：

declare

control_valnumber:=0;

begin

forcontrol_valin0.. .9loop

dbms_output.put_line(control_val);

null;

endloop;

end;

null为空操作语句，它表示什么也不做，在程序中用来标识此处可以加执行语句，起到一种记号的作用；

(5)GOTO语句示例

4.1.7pl/sql表达式

A：字符表达式

declare

hnvarchar2(20) :='hello';

wnvarchar2(20) :='world!';

rtnvarchar2(20) ;

begin

ifhisnotnullandwisnotnull

thenrt:=h||w;

dbms_output.put_line(rt);

endif;

end;

运行结果：

B：布尔表达式

begin

if 'Scott' not in ('Mike','John','Mary')

then dbms_output.put_line('false');

end if ;

end;

运行结果：

Ø4.2 PL/SQL的游标

4.2.1基本原理

4.2.2显示游标

显示游标的处理包括：声明游标、打开游标、提取游标、关闭游标4个步骤；其操作过程如下图：

注：声明游标需要在块的声明部分进行，其他3步骤都在执行部分或异常处理中进行。

A：声明游标

B：打开游标

Open <游标名>；

1)检查联偏变量的取值；

2)根据联偏变量的取值，确定活动集；

3)活动集指针指向第一行；

C：提取游标

D：关闭游标

Close <游标名>；

查出employees表中的所有数据：

执行下边定义的sql:

--1,游标的声明

declare

FIRST_NAMEVARCHAR2(30);--定义四个变量来存放employees表中的内容

LAST_NAMEVARCHAR2(30);

EMAILVARCHAR2(30);

phone_numvarchar2(30);

cursoremployee_curis

selecte.first_name,e.last_name,e.email,e.phone_number

fromemployeese

wheree.employee_id<10;--选出编号大于5的所有雇员

--2,游标的开启

begin

openemployee_cur;

--3,提取游标(fetch语句每执行一次，游标向后移动一行，直到结束；游标只能逐个向后移动，而不能跳跃移动或者向前移动)

fetchemployee_curintoFIRST_NAME,LAST_NAME,EMAIL,phone_num;

--4,关闭游标

loop

exitwhennotemployee_cur%found;--如果游标到尾则结束

ifFIRST_NAME='lq'then

dbms_output.put_line('名称为：lq');

else

dbms_output.put_line('没有合法用户');

endif;

fetchemployee_curintoFIRST_NAME,LAST_NAME,EMAIL,phone_num;

endloop;

closeemployee_cur;

end;

得出的结果为：

注：使用显式游标时，需要注意以下事项：

4.2.3隐式游标

4.2.4游标属性

A：是否找到游标(%found)

B：是否没找到游标(%found)

C：游标行数(%RowCount)

--1,游标的声明

declare

FIRST_NAMEVARCHAR2(30);--定义四个变量来存放employees表中的内容

LAST_NAMEVARCHAR2(30);

EMAILVARCHAR2(30);

phone_numvarchar2(30);

cursoremployee_curis

selecte.first_name,e.last_name,e.email,e.phone_number

fromemployeese

wheree.employee_id<10;--选出编号大于5的所有雇员

--2,游标的开启

begin

openemployee_cur;

--3,提取游标(fetch语句每执行一次，游标向后移动一行，直到结束；游标只能逐个向后移动，而不能跳跃移动或者向前移动)

fetchemployee_curintoFIRST_NAME,LAST_NAME,EMAIL,phone_num;

--4,关闭游标

loop--如果游标到尾则结束(判断游标当前行数，即当前只抽取3条记录)

exitwhennotemployee_cur%foundoremployee_cur%rowcount=3;

ifFIRST_NAME='lq'then

dbms_output.put_line('名称为：lq');

else

dbms_output.put_line('没有合法用户');

endif;

fetchemployee_curintoFIRST_NAME,LAST_NAME,EMAIL,phone_num;

endloop;

closeemployee_cur;

end;

D：游标是否打开（%IsOpen）

E：参数化游标

--敲回车后会弹出输入对话框，输入即可。

--ACCEPT my_tid prompt 'please input the tid';

ACCEPTmy_tidprompt'please input the tid';

declare

my_tid

number:=10;--定义的参数或者直接用以上prompt输入参数

FIRST_NAMEVARCHAR2(30);--定义四个变量来存放employees表中的内容

LAST_NAMEVARCHAR2(30);

EMAILVARCHAR2(30);

phone_numvarchar2(30);

cursoremployee_cur(cursor_idnumber)is--定义游标时带上参数

selecte.first_name,e.last_name,e.email,e.phone_number

fromemployeese

wheree.employee_id

begin

ifemployee_cur%isopenthen

loop

fetchemployee_cur

intoFIRST_NAME,LAST_NAME,EMAIL,phone_num;

exitwhenemployee_cur%notfound;

endloop;

else

openemployee_cur(my_tid);--带上实参数

loop

fetchemployee_cur

intoFIRST_NAME,LAST_NAME,EMAIL,phone_num;

exitwhenemployee_cur%notfound;

endloop;

endif;

closeemployee_cur;

end;

4.2.5游标变量

Oracle 11 G从入门到精通阅读至126页，文档未完待续。