oracle PL SQL编程.docx
《oracle PL SQL编程.docx》由会员分享，可在线阅读，更多相关《oracle PL SQL编程.docx（26页珍藏版）》请在冰点文库上搜索。
oraclePLSQL编程
/*
*数据定义语句（只操作表的结构）
*/
--创建表
--创建部门表
createtabledepartment_13（department_idnumber（6）,department_namevarchar（25）,
manager_idnumber（6）,location_idnumber（4））;
--使用子查询创建表
createtabledepartment_13_tempasselectdepartment_id,department_namefromdepartment_13;
--修改表
--增加字段
altertabledepartment_13_tempadd（manager_idnumber（6））;
--删除字段
altertabledepartment_13_tempdropcolumnmanager_id;
--修改字段类型
altertabledepartment_13_tempmodify（manager_idvarchar（6））;
--修改字段大小
altertabledepartment_13_tempmodify（manager_idnumber（4））;
--删除表
droptabledepartment_13_temp;
--数据字典表
selecttable_namefromdba_tables;
selecttable_namefromuser_tables;
--察看用户拥有的数据库对象类型
selectdistinctobject_typefromuser_objects;
--约束
--创建非空约束（同时也是列级约束）
createtabledepartment_13（department_idnumber（6） constraintdept_13_idnotnull,department_namevarchar（25）,
manager_idnumber（6）,location_idnumber（4））;
--创建唯一性约束（同时也是表级约束）
createtabledepartment_13（department_idnumber（6）,department_namevarchar（25）,
manager_idnumber（6）,location_idnumber（4）,
constraintdep_id_13_uniunique（department_id））;
--创建主键约束
createtabledepartment_13（department_idnumber（6）,department_namevarchar（25）,
manager_idnumber（6）,location_idnumber（4）,
constraintdep_id_13_priprimarykey（department_id））;
--创建外键约束
createtableemployee_13（employee_idnumber（6）,employee_namevarchar（25）,emailvarchar（28）,hire_datedate,
job_idvarchar（20）,salarynumber（8,2）,commission_pctnumber（2,2）,
manager_idnumber（6）,department_idnumber（6）,
constraintemp_13_foreign foreignkey（department_id）
referencesdepartment_13（department_id））;
--check约束
createtableemployee_13_temp（employee_idnumber（6）,employee_namevarchar（25）,emailvarchar（28）,hire_datedate,
job_idvarchar（20）,salarynumber（8,2）,commission_pctnumber（2,2）,
manager_idnumber（6）,department_idnumber（6）,
constraintemp_sal_mincheck（salary>8888））;
--增加约束
altertableemployee_13_tempaddconstraintemp_13_priprimarykey（employee_id）;
altertableemployee_13_tempmodify（salarynotnull）;
--删除约束
altertableemployee_13_tempdropconstaintemp_13_pri;
--删除被外键参照的主键约束
altertabledepartment_13dropprimarykeycascade;
--手工创建索引
createIndexemp_13_salonemployee_13（salary）;
--删除索引
dropindexemp_13_sal;
--创建序列
createsequencehospital_id
minvalue1
maxvalue999999999999
startwith11
incrementby1
cache10;
--创建视图
createorreplaceviewemp_13_11asselectemployee_id,employee_name,salary,job_idfromemployee_13wheredepartment_id=11;
createorreplaceviewemp_13_deptasselectd.department_name,d.manager_id,e.employee_name,e.salaryfromemployee_13e,department_13d
wheree.department_id=d.department_idande.department_id=11;
createorreplaceviewemp_13_dept_tempasselectd.department_name,d.manager_id,e.employee_name,e.salaryfromemployee_13e,department_13d
wheree.department_id=d.department_id;
--删除视图
dropviewemp_13_11;
--查找出薪水最高的三个员工的信息（Top-N分析法）:
使用到了行内视图
selectrownum,employee_name,salaryfrom（selectemployee_name,salaryfromemployee_13orderbysalarydesc）whererownum<=3;
select*from（selectemployee_name,salaryfromemployee_13orderbysalarydesc）whererownum<=3;
--创建一个同义词
createsynonymctfromSystem.emp_13_dept_temp;
--删除同义词
dropsynonymct
/*
*数据操作语句（操作表的数据）
*/
--Insert语句
insertintodepartment_13values（13,'测试部',120,119）;
insertintodepartment_13values（28,null,null,113）;
insertintodepartment_13values（&department_id,'&department_name',&manager_id,&location_id）;
--Update语句
updateemployee_13setsalary=66566whereemployee_id=3;
--merge语句（数据合并语句）
mergeintodepat_13_tempa
usingdepartment_13b
on（a.department_id=b.department_id）
whenmatchedthen
updateset
a.department_name=b.department_name,
a.manager_id=b.manager_id,
a.location_id=b.location_id
whennotmatchedthen
insert（a.department_id,a.department_name,a.manager_id,a.location_id）
values（b.department_id,b.department_name,b.manager_id,b.location_id）;
--提交事务
updatedepartment_13setmanager_id=120wheredepartment_id=14;
commit;
--察看自动提交环境变量
showautocommit;
--打开自动提交
setautocommiton;
--savepoint
updatedepartment_13setmanager_id=130wheredepartment_id>14;
savepointundo1;
deletefromdepartment_13wheredepartment_id>14;
savepointundo2;
rollbacktoundo1;
--SELECT语句
--带算书表达式的select语句
selectemployee_id,employee_name,salary,salary*12fromemployee_13;
--带连接表达式的select语句
selectemployee_name||'的年薪是:
'||salary*12||'美元'fromemployee_13;
--对空值的引用
selectemployee_name,salary,salary*（1+commission_pct）"奖金"fromemployee_13;
--字段别名
selectemployee_name"姓名",salary"薪水",salary*（1+commission_pct）"奖金"fromemployee_13;
--去掉重复值
selectdistinctsalaryfromemployee_13;
--带条件的查询
selectemployee_id,employee_name,salaryfromemployee_13wheredepartment_id=10;
--得到当前日期格式字符串
select*fromv$nls_parameters;
--得到系统当前日期
selectsysdatefromdual;
--比较操作符
--between..and
selectemployee_name,job_id,hire_datefromemployee_13wheresalarybetween4000and7000;
--in
selectemployee_name,job_id,hire_datefromemployee_13wheresalaryin（6111,4111,7222）;
--like
selectemployee_name,job_id,hire_datefromemployee_13whereemployee_namelike'李%';
--isnull
selectemployee_name,job_id,hire_datefromemployee_13wherecommission_pctisnull;
--比较操作的逻辑运算符
-AND
selectemployee_name,job_id,hire_datefromemployee_13wheresalarybetween4000and7000andjob_id='软件架构师';
--and..or
selectemployee_name, salaryfromemployee_13where（job_name='软件工程师'orjob_name='软件架构师'）andsalary>4000;
--排序显示
--单字段排序
selectemployee_name,salaryfromemployee_13orderbysalarydesc;
--组合字段排序（主排序字段相同时，按照辅助排序字段排序）
selectemployee_name,salary,hire_datefromemployee_13orderbysalarydesc,hire_datedesc;
/**
*SQL函数
*/
--单行函数
--字符函数
--大小写转换函数
selectemployee_id,salaryfromemployee_13wherelower（employee_name）='draglong';
selectemployee_id,salaryfromemployee_13whereupper（employee_name）='DRAGLONG';
selectemployee_id,salaryfromemployee_13whereInitcap（employee_name）like'D%';
--字符处理函数
selectreplace（'db2','oracle'）fromdual;
selectemployee_name,concat（employee_name,job_name）name,length（employee_name）len,instr（employee_name,'g'）ins
fromemployee_13wheresubstr（employee_name,1,5）='dragl';
--日期函数
selectemployee_name,job_name,（sysdate-hire_date）/7weeksfromemployee_13;
selecthire_date,months_between（sysdate,hire_date）week,add_months（hire_date,6）week2,next_day（sysdate,'星期六'）nextday,
last_day（hire_date）fromemployee_13;
--round函数
selectemployee_name,hire_date,round（hire_date,'MONTH'）fromemployee_13;
--trunc函数
selecttrunc（sysdate,'D'）,trunc（sysdate,'MM'）,trunc（sysdate,'MONTH'）,trunc（sysdate,'DD'）fromdual;
--转换函数
--日期转换为字符TO_CHAR（字段名,'格式字符串'）
selectemployee_name,to_char（hire_date,'MM/YY'）fromemployee_13;
selectemployee_name,to_char（hire_date,'YEAR"年"MM"月"DD"日"'）fromemployee_13;
selectemployee_name,to_char（hire_date,'"北京时间"YYYY"年"MONDD"日"HH24"时"MI"分"SS"秒"'）FROMemployee_13;
selectemployee_name,to_char（hire_date,'YYYYspth"年"MONDD"日"HH24"时"MI"分"SS"秒"'）FROMemployee_13;
--数字转换为字符TO_CHAR（字段名,'格式字符串'）
selectemployee_name,to_char（salary,'$99,999.99'）fromemployee_13;
selectemployee_name,to_char（salary,'L99,999.99'）fromemployee_13;
selectemployee_name,to_char（salary,'$00,000.00'）fromemployee_13;
--字符型日期转换为日期型日期to_date（'日期字段值','格式字符串'）
insertintoemployee_13values（213,null,null,to_date（'2007年04月28日','YYYY"年"MM"月"DD"日"'）,null,null,null,null,null）;
--第五类函数
/**
*为空处理函数
*/
--NVL函数
selectemployee_name"姓名",salary"薪水",salary*（1+nvl（commission_pct,0））"奖金"fromemployee_13;
--NVL2
selectemployee_name"姓名",salary"薪水",nvl2（commission_pct,'架构师','工程师'）"级别"fromemployee_13;
--NULLIF
selectemployee_name,length（employee_name）a,job_name,length（job_name）b,nullif（length（employee_name）,length（job_name））resultfromemployee_13;
--COALESCE（取得列表中的第一个非空值）
selectemployee_name,coalesce（commission_pct,salary）fromemployee_13;
/**
*CASE语句
*/
selectemployee_name,job_name,salary,
casejob_namewhen'软件工程师'then0.40*salary
when'软件架构师'then0.30*salary
when'系统架构师'then0.20*salary
else salaryend"加薪幅度"
fromemployee_13;
/**
*DECODE语句
*/
selectemployee_name,salary,job_name,
decode（job_name,'软件工程师',0.40*salary,
'软件架构师',0.30*salary,
'系统架构师',0.20*salary,
salary）"工资涨幅"
fromemployee_13;
/**
*分组函数（多行函数）
*/
selectemployee_name,salary,avg（salary）,count（salary）,max（salary）,min（salary）,sum（salary）fromemployee_13;
--Groupby语句
selectdepartment_id,avg（salary）fromemployee_13groupbydepartment_id;
/**
*多表连接和子查询
*/
--等值连接
select d.department_name,e.employee_name,d.department_id,e.salary
fromemployee_13e,department_13d
wheree.department_id=d.department_id
andd.department_name='开发部';
--非等值连接
select d.department_name,e.employee_name,d.department_id,e.salary
fromemployee_13e,department_13d
wheree.department_id=d.department_id
ande.salarybetween4000and7000;
--左外连接
select d.department_name,e.employee_name,d.department_id,e.salary
fromemployee_13e,department_13d
wheree.department_id（+）=d.department_id;
--右外连接
select d.department_name,e.employee_name,d.department_id,e
升级会员 