- 浏览: 261477 次
- 性别:
- 来自: 长沙
文章分类
最新评论
-
ae6623:
楼主,没必要用json2.js,jquery自己就可以去解析j ...
JQuery、JSON、Ajax在Servlet中的应用 -
hw1287789687:
http://ljhzzyx.blog.163.com/blo ...
MyEclipse中如何去掉JS/JSP 语法错误提示 -
xiaokang1582830:
不知所云...
处理 Struts2 框架上传的文件 -
halfwaters:
使用vs2010,加上扩展管理器安装的js插件
MyEclipse中如何去掉JS/JSP 语法错误提示 -
ayumi11111:
附件呢 我····
jQuery+Ajax+Struts2. js javascript
例
select ename,sal,next_day(sysdate,'FRIDAY') as_of
from emp
where deptno=20;
(as_of是别名)
如果不用to_char函数,日期在ORACLE中的缺省格式是'DD_MON_YY'
to_char(date,date picture)
select ename,to_char(hiredate,'Dy Mon dd,yyyy') hired
from emp
where deptno=10;
to_date(字符串,格式)
insert into emp(empno,ename,hiredate)
values(7999,'asms',to_date('070387083000','MMDDYYHHMISS'));
日期型数据的格式
dd 12
dy fri
day friday
ddspth twelfth
mm 03
mon mar
month march
yy 87
yyyy 1987
例
Mar 12,1987 'Mon dd,yyyy'
MAR 12,1987 'MON dd,yyyy'
Thursday MARCH 12 'Day MONTH dd'
Mar 12 11:00am 'Mon dd hh:miam'
Thu,the twelfth 'Dy,"the"ddspth'
算术函数
least(v1,v2)
select ename,empno,mgr,least(empno,mgr) lownum
from emp
where empno0
trunc(sal,0)
取sal的近似值(截断)
空值函数
nvl(v1,v2)
v1为列名,如果v1不是空值,nvl返回其列值。
v1为空值,返回v2的值。
聚组函数
select sum(comm)
from emp;
(返回一个汇总信息)
不能把sum用在select语句里除非用group by
字符型、日期型、数字型的聚组函数
min max count可用于任何数据类型
select min(ename)
from emp;
select min(hiredate)
from emp;
select min(sal)
from emp;
有多少人有工作?
select count(job)
from emp;
有多少种不同的工种?
select count(distinct job)
from emp;
count distinct 计算某一字段中不同的值的个数
其它聚组函数(只用于数字型数据)
avg 计算平均工资
select avg(sal)
from emp;
stddev 计算工资的平均差
select stddev(sal)
from emp;
sum 计算总工资
select sum(sal)
from emp;
group by子句
select deptno,sum(sal),avg(sal)
from emp
group by deptno;
按多个条件分组
每个部门的雇员数
select deptno,count(*)
from emp
group by deptno;
每个部门的每个工种的雇员数
select deptno,job,count(*)
from emp
group by deptno,job;
满足条件的分组
(where是针对select的,having是针对group by的)
哪些部门的工资总和超过了9000
select deptno,sum(sal)
from emp
group by deptno
having sum(sal)>9000;
select小结
除去职员,哪些部门的工资总和超过了8000
select deptno,sum(sal)
from emp
where job!='clerk'
group by deptno
having sum(sal)>8000
order by sum(sal);
---------
7 高级查询
等值联接
select empno,ename,job,emp.deptno,dname
from emp,dept
where emp.deptno=dept.deptno;
外联接
select ename,dept.deptno,loc
from emp,dept
where emp.deptno(+)=dept.deptno;
如果在dept.deptno中有的数值,在emp.deptno中没有(如deptno=40),
则作外联接时,结果中会产生一个空值
自联接:同一基表的不同行要做联接,可使用自联接
指出每个雇员的经理名字
select worker.ename,manager.ename manager
from emp worker,emp manager
where worker.mgr=manager.empno;
非等值联接
哪些雇员的工资属于第三级别
select ename,sal
from emp,salgrade
where grade=3
and sal between losal and hisal;
(基表salgrade:grade losal hisal)
集合运算
行的连接
集合运算把2个或多个查询结果合并为一个
union-set union
Rows of first query plus of second query, less duplicate rows
intersect-set intersection
Rows both queries have in common
minus-set difference
rows unique to the first query
介绍几个视图
account view
ename sal job
sales view
ename sal job
research view
ename sal job
union运算
返回一个查询结果中有但又不重要的行,它将基表或视图中的记录合并在一起
所有部门中有哪些雇员工资超过2000
对应列的数据类型必须相同
select ename,sal
from account
where sal>2000
union
select ename,sal
from research
where sal>2000
union
select ename,sal
from sales
where sal>2000;
intersect运算
返回查询结果中相同的部分
各个部门中有哪些相同的工种
select job
from account
intersect
select job
from research
intersect
select job
from sales;
minus运算
返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。
有哪些工种在财会部中有,而在销售部中没有?
select job from account
minus
select job from sales;
子查询
slect ename,deptno
from emp
where deptno=
(select deptno
from emp
where ename='smith');
多级子查询
select ename,job,sal
from emp
where job=
(select job
from emp
where ename='clark')
or sal>
(select sal
from emp
where ename='clark');
多个基表与子查询
select ename,job,sal
from emp,dept
where loc='new york'
and emp.deptno=dept.deptno
and sal>
(select sal
from emp
where ename='scott');
子查询中使用聚组函数
select ename,hiredate
from emp
where hiredate=
(select min(hiredate)
from emp);
8 授权
系统权限
DBA 所有权限
RESOURCE 注册,创建新的基表
CONNECT,注册,查询
只有DBA才有权创建新的用户
grant connect to scott
identified by tiger;
DBA或用户自己可以改变用户口令
grant connect to scott
identified by leopard;
基表权限1
有两种方法获得对基表操作的权限
创建自己的基表
获得基表创建用户的许可
grant select,insert
on emp
to scott;
这些权限有
select insert update delete alter index
把所有权限授于他人
grant all on emp to scott;
同义词
select *
from scott.emp
创建同义词
为用户allen的EMP基表创建同义词employee
create synonym employee
for allen.emp
基表权限2
你可以使其他用户有这样的权力,即其他用户可以把你的基表权限授予他人
grant all
on emp
to scott
with grant option;
收回权限
系统权限 只有被DBA收回
基表权限 随时都可以收回
revoke insert
on emp
from scott;
---------
9 索引
建立索引
create index emp_ename
on emp(ename);
删除索引
drop index emp_ename;
关于索引
只对较大的基表建立索引(至少50条记录)
建立索引之前插入数据
对一个基表可建立任意多个索引
一般是在作为主键的列上建立索引
建立索引之后,不影响SQL命令的执行
建立索引之后,ORACLE自动维护和使用索引
保证数据唯一性
提高执行速度的同时,索引还可以保证每个记录中的每个列值是不重复的。
create unique index emp_empno
on emp(empno);
--------
练习和答案
有没有工资比奖金多的雇员?如果有,按工资的降序排列。
如果有两个以上的雇员工资相同,按他们的名字排序。
select ename employee,sal salary,comm commision
from emp
where sal>comm
order by sal desc,ename;
列出有关雇员姓名、奖金占收百分比的信息。
要求显示时列名意义明确,按雇员姓名排序,不包括奖金未知的雇员。
select ename employee,(comm/(comm+sal))*100 incentive
from emp
where comm is not null
order by ename;
在chicago(部门30)工作的所有雇员的工资上涨10%。
update emp
set sal=1.1*sal
where deptno=30;
update emp
set sal=1.1*sal
where deptno=(select deptno
from dept
where loc='chicago');
为hitech公司新建一个部门,编号为50,其它信息均不可知。
insert into dept(dname,deptno)
values('faclities',50);
创建视图,三个列名,其中不包括职员信息
create view employee("employee name",
"employee number",
"employee job")
as select ename,empno,job
from emp
where job!='clerk';
制作工资报表,包括雇员姓名、受雇时间(按星期计算),工资和部门编号,
一页显示一个部门的信息,每页尾,显示该页的工资之和以及受雇时间之和,
报表结尾处,显示所有雇员的工资总和以及受雇时间总和,
工资按美元计算,受雇时间按星期计算,每页的上方应有标题。
ttitle 'service'
break on deptno on page on report
compute sum of sal on deptno
compute sum of sal on report
compute sum of service_length on deptno
compute sum of service_length on report
column sal format $99,999.00
column service_length format 9999
select deptno,ename employee,(sysdate-hiredate)/7 service_length,sal
from emp
order by deptno;
制作报表,包括雇员姓名、总收入和受佣日期,
且:姓名的第一个字母必须大写,雇佣日期格式为MM/DD/YYYY,
总收入包括没有奖金的雇员的总收入,姓名按字母顺序排列。
col "hire date"ormat A12
col "employee" format A10
col "compensation" format $99,999.00
select initcap(ename) "employee",
(sal+nvl(comm,0)) "compensation",
to_char(hiredate,'MM/DD/YYYY') "hire date"
from emp
order by ename;
列出有超过7个周边国家的国家名字和面积。
select nation,area
from nation
where code in
(select nation_code
from border
group by nation_code
having count(*)>7);
列出所有面积大于等于日本的岛国的国名和人口。
select nation,population
from nation,border
where code=nation_code(+)
and nation_code is null
and area>=
(select area
from nation
where upper(nation)='JAPAN');
列出所有边界在其它国家中的国名,并且显示其边界国家名字。
break on nation
select nation1.nation,
nation2.nation borderin_country
from nation nation1,border,nation nation2
where nation1.code=border.nation_code
and border.border_code=nation2.code
order by nation1.nation;
-----------
-----------
PL/SQL
2 PL/SQL的块结构和数据类型
块结构的特点
嵌套
begin
......
begin
......
exception
......
end;
exception
......
end;
标识符:
不能超过30个字符
第一个字符必须为字母
其余字符可以是字母,数字,$,_,或#
不区分大小写形式
如果用双引号括起来,则字符顺序可以任意排列,并区分大小写形式
无SQL保留字
数据类型
数字型:
整数,实数,以及指数
字符串:
用单引号括起来
若在字符串表示单引号,则使用两个单引号
字符串长度为零(两个单引号之间没有字符),则表示NULL
字符:
长度为1的字符串
数据定义
语法
标识符[常数] 数据类型[NOT NULL][:=PL/SQL表达式];
':='表示给变量赋值
数据类型包括
数字型 number(7,2)
字符型 char(120)
日期型 date
布尔型 boolean(取值为true,false或null,不存贮在数据库中)
日期型
anniversary date:='05-JUL-95';
project_completion date;
布尔型
over_budget boolean not null:=false;
available boolean;
(初始值为NULL)
%type类型匹配
books_printed number(6);
books_sold book_printed%type;
manager_name emp.ename%type;
变量赋值
变量名:=PL/SQL表达式
numvar:=5;
boolvar:=true;
datevar:='11-JUN-87';
字符型、数字型表达式中的空值
null+<数字>=null(空值加数字仍是空值)
null><数字>=null(空值与数字进行比较,结果仍是空值)
null||'字符串'='字符串'(null即'')
(空值与字符串进行连接运算,结果为原字符串)
变量作用范围
标识符在宣言它的块中有效
标识符如果不在子块中重新定义,则在PL/SQL块的所有子块中同样有效
重新定义后的标识符,作用范围仅在本子块中有效
例
declare
e_mess char(80);
begin
/*子块1*/
declare
v1 number(4);
begin
select empno into v1 from emp
where job='president';
exception
when too_many_rows then
insert into job_errors
values('more than one president');
end;
/*子块2*/
declare
v1 number(4);
begin
select empno into v1 from emp
where job='manager';
exception
when too_many_rows then
insert into job_errors
values('more than one manager');
end;
exception
when others then
e_mess:=substr(sqlerrm,1,80);
insert into general errors values(e_mess);
end;
---------
3 SQL和PL/SQL
插入
declare
my_sal number(7,2):=3040.55;
my_ename char(25):='wanda';
my_hiredate date:='08-SEP-88';
begin
insert into emp
(empno,enmae,job,hiredate,sal,deptno)
values(2741,my_ename,'cab driver',my_hiredate,my_sal,20);
end;
删除
declare
bad_child_type char(20):='naughty';
begin
delete from santas_gift_list where
kid_rating=bad_child_type;
end;
事务处理
commit[WORK];
rollback[WORK];
(关键字WORK可选,但对命令执行无任何影响)
savepoint 标记名;(保存当前点)
在事务中标记当前点
rollback [WORK] to [SAVEPOINT] 标记名;(回退到当前保存点)
取消savepoint命令之后的所有对数据库的修改
关键字WORK和SAVEPOINT为可选项,对命令执行无任何影响
函数
PL/SQL块中可以使用SQL命令的所有函数
insert into phonebook(lastname) value(upper(my_lastname));
select avg(sal) into avg_sal from emp;
对于非SQL命令,可使用大多数个体函数
不能使用聚组函数和参数个数不定的函数,如
x:=sqrt(y);
lastname:=upper(lastname);
age_diff:=months_between(birthday1,birthday2)/12;
赋值时的数据类型转换
4种赋值形式:
变量名:=表达式
insert into 基表名 values(表达式1,表达式2,...);
update 基表名 set 列名=表达式;
select 列名 into 变量名 from ...;
数据类型间能进行转换的有:
char转成number
number转成char
char转成date
date转成char
例
char_var:=nm_var;
数字型转换成字符型
date_var:='25-DEC-88';
字符型转换成日期型
insert into 表名(num_col) values('604badnumber');
错误,无法成功地转换数据类型
---------
4 条件控制
例
declare
num_jobs number(4);
begin
select count(*) into num_jobs from auditions
where actorid=&&actor_id and called_back='yes';
if num_jobs>100 then
update actor set actor_rating='word class'
where actorid=&&actor_id;
elsif num_job=75 then
update actor set actor_rating='daytime soaps'
where actorid=&&actor_id;
else
update actor set actor_rating='waiter'
where actorid=&&actor_id;
end if;
end if;
commit;
end;
--------
5 循环
语法
loop
......
end loop;
exit;(退出循环)
exit [when];(退出循环,当满足WHEN时)
例1
declare
ctr number(3):=0;
begin
loop
insert into table1 values('tastes great');
insert into table2 values('less filling');
ctr:=ctr+1;
exit when ctr=100;
end loop;
end;
(注:如果ctr取为NULL,循环无法结束)
例2
FOR语法
for 变量<范围> loop
......
end loop;
declare
my_index char(20):='fettucini alfredo';
bowl char(20);
begin
for my_index in reverse 21..30 loop
insert into temp(coll) values(my_index);
/*循环次数从30到21*/
end loop;
bowl:=my_index;
end;
跟在in reverse后面的数字必须是从小到大的顺序,必须是整数,不能是变量或表达式
----------
6 游标
显式游标
打开游标
open <游标名>
例
open color_cur;
游标属性
%notfound
%found
%rowcount
%isopen
例
fetch my_cur into my_var;
while my_cur %found loop
(处理数据)
fetch my_cur into my_var;
exit when my_cur %rowcount=10;
end loop;
%notfound属性
取值情况如下:
fetch操作没有返回记录,则取值为true
fetch操作返回一条记录,则取值为false
对游标无fetch操作时,取值为null
<游标名> %notfound
例
if color_cur %notfound then...
注:如果没有fetch操作,则<游标名> %notfound将导致出错,
因为%notfound的初始值为NULL。
关闭游标
close <游标名>
例
close color_cur;
游标的FOR循环
语法
for <记录名> in <游标名> loop
<一组命令>
end loop;
其中:
索引是建立在每条记录的值之上的
记录名不必声明
每个值对应的是记录名,列名
初始化游标指打开游标
活动集合中的记录自动完成FETCH操作
退出循环,关闭游标
隐式游标
隐式游标是指SQL命令中用到的,没有明确定义的游标
insert,update,delete,select语句中不必明确定义游标
调用格式为SQL%
存贮有关最新一条SQL命令的处理信息
隐式游标的属性
隐式游标有四个属性
SQL%NOTFOUND
SQL%FOUND
SQL%ROWCOUNT:隐式游标包括的记录数
例:
delete from baseball_team where batting_avg<100;
if sql%rowcount>5 thn
insert into temp
values('your team needs help');
end if;
SQL%ISOPEN:取值总为FALSE。SQL命令执行完毕,PL/SQL立即关闭隐式游标。
7 标号
GOTO语句
用法:
goto you_are_here;
其中you_are_here是要跳转的语句标号
标号必须在同一组命令,或是同一块中使用
正确的使用
<>(标号)
x:=x+1
if a>b then
b:=b+c;
goto dinner;
end if;
错误的使用
goto jail;
if a>b then
b:=b+c;
<>(标号)
x:=x+1;
end if;
标号:解决意义模糊
标号可用于定义列值的变量
<>
declare
deptno number:=20;
begin
update emp set sal=sal*1.1
where deptno=sample.deptno;
commit;
end sample;
如果不用标号和标号限制符,这条命令将修改每条记录。
----------
8 异常处理
预定义的异常情况
任何ORACLE错误都将自动产生一个异常信息
一些异常情况已命名,如:
no_data_found 当SELECT语句无返回记录时产生
too_many_rows 没有定义游标,而SELECT语句返回多条记录时产生
whenever notfound 无对应的记录
用户定义的异常情况
由用户自己获取
在DECLARE部分定义:
declare
x number;
something_isnt_right exception;
用户定义的异常情况遵循一般的作用范围规则
条件满足时,获取异常情况:raise something_isnt_right
注意:同样可以获取预定义的异常情况
exception_init语句
允许为ORACLE错误命名
调用格式:
pragma exception_init(<表达式>,);
例
declare
deadlock_detected exception;
pragma exception_init(deadlock_detected,-60);
raise语句
单独使用RAISE命令,可再一次获取当前的异常情况(就象异常情况被重复处理了一样)。
在异常处理中,此语句只能单独使用。
异常处理标识符
一组用于处理异常情况的语句:
exception
when <表达式> or [表达式...] then
<一组语句>
...
when others then--最后一个处理
<一组语句>
end;既结束PL/SQL块部分,也结束异常处理部分
--------
练习与答案
1:
接收contract_no和item_no值,在inventory表中查找,如果产品:
已发货,在arrival_date中赋值为今天后的7天
已订货,在arrival_date中赋值为今天后的一个月
既无订货又无发货,则在arrival_date中赋值为今天后的两个月,
并在order表中增加一条新的订单记录。
product_status的列值为'shipped'和'ordered'
inventory:
product_id number(6)
product_description char(30)
product_status char(20)
std_shipping_qty number(3)
contract_item:
contract_no number(12)
item_no number(6)
arrival_date date
order:
order_id number(6)
product_id number(6)
qty number(3)
答案:
declare
i_product_id inventory.product_id%type;
i_product_description inventory.product_description%type;
i_product_status inventory.product_status%type;
i_std_shipping_qty inventory.std_shipping_qty%type;
begin
select product_id,product_description,product_status,std_shipping_qty
into i_product_id,i_product_description,
i_product_status,i_std_shipping_qty
from inventory
where product_id=(
select product_id
from contract_item
where contract_no=&&contractno and item_no=&&itemno);
if i_product_status='shipped' then
update contract_item
set arrival_date=sysdate+7
where item_no=&&itemno and contract_no=&&contractno;
elsif i_product_status='ordered' then
update contract_item
set arrival_date=add_months(sysdate,1)
where item_no=&&itemno and contract_no=&&contractno;
else
update contract_item
set arrival_date=add_months(sysdate,2)
where item_no=&&itemno and contract_no=&&contractno;
insert into orders
values(100,i_product_id,i_std_shipping_qty);
end if;
end if;
commit;
end;
2:
1.找出指定部门中的所有雇员
2.用带'&'的变量提示用户输入部门编号
3.把雇员姓名及工资存入prnttable表中,基结构为:
create table prnttable
(seq number(7),line char(80));
4.异常情况为,部门中奖金不为空值的雇员信息才能存入prnttable表中。
答案:
declare
cursor emp_cur is
select ename,sal,comm
from emp where deptno=&dno;
emp_rec emp_cur%rowtype;
null_commission exception;
begin
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is null then
begin
close emp_cur;
raise null_commission;
end;
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_sur;
exception
when null_commission then
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is not null then
insert into temp values(emp_rec.sal,emp_rec.ename);
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_cur;
commit;
end;
select ename,sal,next_day(sysdate,'FRIDAY') as_of
from emp
where deptno=20;
(as_of是别名)
如果不用to_char函数,日期在ORACLE中的缺省格式是'DD_MON_YY'
to_char(date,date picture)
select ename,to_char(hiredate,'Dy Mon dd,yyyy') hired
from emp
where deptno=10;
to_date(字符串,格式)
insert into emp(empno,ename,hiredate)
values(7999,'asms',to_date('070387083000','MMDDYYHHMISS'));
日期型数据的格式
dd 12
dy fri
day friday
ddspth twelfth
mm 03
mon mar
month march
yy 87
yyyy 1987
例
Mar 12,1987 'Mon dd,yyyy'
MAR 12,1987 'MON dd,yyyy'
Thursday MARCH 12 'Day MONTH dd'
Mar 12 11:00am 'Mon dd hh:miam'
Thu,the twelfth 'Dy,"the"ddspth'
算术函数
least(v1,v2)
select ename,empno,mgr,least(empno,mgr) lownum
from emp
where empno0
trunc(sal,0)
取sal的近似值(截断)
空值函数
nvl(v1,v2)
v1为列名,如果v1不是空值,nvl返回其列值。
v1为空值,返回v2的值。
聚组函数
select sum(comm)
from emp;
(返回一个汇总信息)
不能把sum用在select语句里除非用group by
字符型、日期型、数字型的聚组函数
min max count可用于任何数据类型
select min(ename)
from emp;
select min(hiredate)
from emp;
select min(sal)
from emp;
有多少人有工作?
select count(job)
from emp;
有多少种不同的工种?
select count(distinct job)
from emp;
count distinct 计算某一字段中不同的值的个数
其它聚组函数(只用于数字型数据)
avg 计算平均工资
select avg(sal)
from emp;
stddev 计算工资的平均差
select stddev(sal)
from emp;
sum 计算总工资
select sum(sal)
from emp;
group by子句
select deptno,sum(sal),avg(sal)
from emp
group by deptno;
按多个条件分组
每个部门的雇员数
select deptno,count(*)
from emp
group by deptno;
每个部门的每个工种的雇员数
select deptno,job,count(*)
from emp
group by deptno,job;
满足条件的分组
(where是针对select的,having是针对group by的)
哪些部门的工资总和超过了9000
select deptno,sum(sal)
from emp
group by deptno
having sum(sal)>9000;
select小结
除去职员,哪些部门的工资总和超过了8000
select deptno,sum(sal)
from emp
where job!='clerk'
group by deptno
having sum(sal)>8000
order by sum(sal);
---------
7 高级查询
等值联接
select empno,ename,job,emp.deptno,dname
from emp,dept
where emp.deptno=dept.deptno;
外联接
select ename,dept.deptno,loc
from emp,dept
where emp.deptno(+)=dept.deptno;
如果在dept.deptno中有的数值,在emp.deptno中没有(如deptno=40),
则作外联接时,结果中会产生一个空值
自联接:同一基表的不同行要做联接,可使用自联接
指出每个雇员的经理名字
select worker.ename,manager.ename manager
from emp worker,emp manager
where worker.mgr=manager.empno;
非等值联接
哪些雇员的工资属于第三级别
select ename,sal
from emp,salgrade
where grade=3
and sal between losal and hisal;
(基表salgrade:grade losal hisal)
集合运算
行的连接
集合运算把2个或多个查询结果合并为一个
union-set union
Rows of first query plus of second query, less duplicate rows
intersect-set intersection
Rows both queries have in common
minus-set difference
rows unique to the first query
介绍几个视图
account view
ename sal job
sales view
ename sal job
research view
ename sal job
union运算
返回一个查询结果中有但又不重要的行,它将基表或视图中的记录合并在一起
所有部门中有哪些雇员工资超过2000
对应列的数据类型必须相同
select ename,sal
from account
where sal>2000
union
select ename,sal
from research
where sal>2000
union
select ename,sal
from sales
where sal>2000;
intersect运算
返回查询结果中相同的部分
各个部门中有哪些相同的工种
select job
from account
intersect
select job
from research
intersect
select job
from sales;
minus运算
返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。
有哪些工种在财会部中有,而在销售部中没有?
select job from account
minus
select job from sales;
子查询
slect ename,deptno
from emp
where deptno=
(select deptno
from emp
where ename='smith');
多级子查询
select ename,job,sal
from emp
where job=
(select job
from emp
where ename='clark')
or sal>
(select sal
from emp
where ename='clark');
多个基表与子查询
select ename,job,sal
from emp,dept
where loc='new york'
and emp.deptno=dept.deptno
and sal>
(select sal
from emp
where ename='scott');
子查询中使用聚组函数
select ename,hiredate
from emp
where hiredate=
(select min(hiredate)
from emp);
8 授权
系统权限
DBA 所有权限
RESOURCE 注册,创建新的基表
CONNECT,注册,查询
只有DBA才有权创建新的用户
grant connect to scott
identified by tiger;
DBA或用户自己可以改变用户口令
grant connect to scott
identified by leopard;
基表权限1
有两种方法获得对基表操作的权限
创建自己的基表
获得基表创建用户的许可
grant select,insert
on emp
to scott;
这些权限有
select insert update delete alter index
把所有权限授于他人
grant all on emp to scott;
同义词
select *
from scott.emp
创建同义词
为用户allen的EMP基表创建同义词employee
create synonym employee
for allen.emp
基表权限2
你可以使其他用户有这样的权力,即其他用户可以把你的基表权限授予他人
grant all
on emp
to scott
with grant option;
收回权限
系统权限 只有被DBA收回
基表权限 随时都可以收回
revoke insert
on emp
from scott;
---------
9 索引
建立索引
create index emp_ename
on emp(ename);
删除索引
drop index emp_ename;
关于索引
只对较大的基表建立索引(至少50条记录)
建立索引之前插入数据
对一个基表可建立任意多个索引
一般是在作为主键的列上建立索引
建立索引之后,不影响SQL命令的执行
建立索引之后,ORACLE自动维护和使用索引
保证数据唯一性
提高执行速度的同时,索引还可以保证每个记录中的每个列值是不重复的。
create unique index emp_empno
on emp(empno);
--------
练习和答案
有没有工资比奖金多的雇员?如果有,按工资的降序排列。
如果有两个以上的雇员工资相同,按他们的名字排序。
select ename employee,sal salary,comm commision
from emp
where sal>comm
order by sal desc,ename;
列出有关雇员姓名、奖金占收百分比的信息。
要求显示时列名意义明确,按雇员姓名排序,不包括奖金未知的雇员。
select ename employee,(comm/(comm+sal))*100 incentive
from emp
where comm is not null
order by ename;
在chicago(部门30)工作的所有雇员的工资上涨10%。
update emp
set sal=1.1*sal
where deptno=30;
update emp
set sal=1.1*sal
where deptno=(select deptno
from dept
where loc='chicago');
为hitech公司新建一个部门,编号为50,其它信息均不可知。
insert into dept(dname,deptno)
values('faclities',50);
创建视图,三个列名,其中不包括职员信息
create view employee("employee name",
"employee number",
"employee job")
as select ename,empno,job
from emp
where job!='clerk';
制作工资报表,包括雇员姓名、受雇时间(按星期计算),工资和部门编号,
一页显示一个部门的信息,每页尾,显示该页的工资之和以及受雇时间之和,
报表结尾处,显示所有雇员的工资总和以及受雇时间总和,
工资按美元计算,受雇时间按星期计算,每页的上方应有标题。
ttitle 'service'
break on deptno on page on report
compute sum of sal on deptno
compute sum of sal on report
compute sum of service_length on deptno
compute sum of service_length on report
column sal format $99,999.00
column service_length format 9999
select deptno,ename employee,(sysdate-hiredate)/7 service_length,sal
from emp
order by deptno;
制作报表,包括雇员姓名、总收入和受佣日期,
且:姓名的第一个字母必须大写,雇佣日期格式为MM/DD/YYYY,
总收入包括没有奖金的雇员的总收入,姓名按字母顺序排列。
col "hire date"ormat A12
col "employee" format A10
col "compensation" format $99,999.00
select initcap(ename) "employee",
(sal+nvl(comm,0)) "compensation",
to_char(hiredate,'MM/DD/YYYY') "hire date"
from emp
order by ename;
列出有超过7个周边国家的国家名字和面积。
select nation,area
from nation
where code in
(select nation_code
from border
group by nation_code
having count(*)>7);
列出所有面积大于等于日本的岛国的国名和人口。
select nation,population
from nation,border
where code=nation_code(+)
and nation_code is null
and area>=
(select area
from nation
where upper(nation)='JAPAN');
列出所有边界在其它国家中的国名,并且显示其边界国家名字。
break on nation
select nation1.nation,
nation2.nation borderin_country
from nation nation1,border,nation nation2
where nation1.code=border.nation_code
and border.border_code=nation2.code
order by nation1.nation;
-----------
-----------
PL/SQL
2 PL/SQL的块结构和数据类型
块结构的特点
嵌套
begin
......
begin
......
exception
......
end;
exception
......
end;
标识符:
不能超过30个字符
第一个字符必须为字母
其余字符可以是字母,数字,$,_,或#
不区分大小写形式
如果用双引号括起来,则字符顺序可以任意排列,并区分大小写形式
无SQL保留字
数据类型
数字型:
整数,实数,以及指数
字符串:
用单引号括起来
若在字符串表示单引号,则使用两个单引号
字符串长度为零(两个单引号之间没有字符),则表示NULL
字符:
长度为1的字符串
数据定义
语法
标识符[常数] 数据类型[NOT NULL][:=PL/SQL表达式];
':='表示给变量赋值
数据类型包括
数字型 number(7,2)
字符型 char(120)
日期型 date
布尔型 boolean(取值为true,false或null,不存贮在数据库中)
日期型
anniversary date:='05-JUL-95';
project_completion date;
布尔型
over_budget boolean not null:=false;
available boolean;
(初始值为NULL)
%type类型匹配
books_printed number(6);
books_sold book_printed%type;
manager_name emp.ename%type;
变量赋值
变量名:=PL/SQL表达式
numvar:=5;
boolvar:=true;
datevar:='11-JUN-87';
字符型、数字型表达式中的空值
null+<数字>=null(空值加数字仍是空值)
null><数字>=null(空值与数字进行比较,结果仍是空值)
null||'字符串'='字符串'(null即'')
(空值与字符串进行连接运算,结果为原字符串)
变量作用范围
标识符在宣言它的块中有效
标识符如果不在子块中重新定义,则在PL/SQL块的所有子块中同样有效
重新定义后的标识符,作用范围仅在本子块中有效
例
declare
e_mess char(80);
begin
/*子块1*/
declare
v1 number(4);
begin
select empno into v1 from emp
where job='president';
exception
when too_many_rows then
insert into job_errors
values('more than one president');
end;
/*子块2*/
declare
v1 number(4);
begin
select empno into v1 from emp
where job='manager';
exception
when too_many_rows then
insert into job_errors
values('more than one manager');
end;
exception
when others then
e_mess:=substr(sqlerrm,1,80);
insert into general errors values(e_mess);
end;
---------
3 SQL和PL/SQL
插入
declare
my_sal number(7,2):=3040.55;
my_ename char(25):='wanda';
my_hiredate date:='08-SEP-88';
begin
insert into emp
(empno,enmae,job,hiredate,sal,deptno)
values(2741,my_ename,'cab driver',my_hiredate,my_sal,20);
end;
删除
declare
bad_child_type char(20):='naughty';
begin
delete from santas_gift_list where
kid_rating=bad_child_type;
end;
事务处理
commit[WORK];
rollback[WORK];
(关键字WORK可选,但对命令执行无任何影响)
savepoint 标记名;(保存当前点)
在事务中标记当前点
rollback [WORK] to [SAVEPOINT] 标记名;(回退到当前保存点)
取消savepoint命令之后的所有对数据库的修改
关键字WORK和SAVEPOINT为可选项,对命令执行无任何影响
函数
PL/SQL块中可以使用SQL命令的所有函数
insert into phonebook(lastname) value(upper(my_lastname));
select avg(sal) into avg_sal from emp;
对于非SQL命令,可使用大多数个体函数
不能使用聚组函数和参数个数不定的函数,如
x:=sqrt(y);
lastname:=upper(lastname);
age_diff:=months_between(birthday1,birthday2)/12;
赋值时的数据类型转换
4种赋值形式:
变量名:=表达式
insert into 基表名 values(表达式1,表达式2,...);
update 基表名 set 列名=表达式;
select 列名 into 变量名 from ...;
数据类型间能进行转换的有:
char转成number
number转成char
char转成date
date转成char
例
char_var:=nm_var;
数字型转换成字符型
date_var:='25-DEC-88';
字符型转换成日期型
insert into 表名(num_col) values('604badnumber');
错误,无法成功地转换数据类型
---------
4 条件控制
例
declare
num_jobs number(4);
begin
select count(*) into num_jobs from auditions
where actorid=&&actor_id and called_back='yes';
if num_jobs>100 then
update actor set actor_rating='word class'
where actorid=&&actor_id;
elsif num_job=75 then
update actor set actor_rating='daytime soaps'
where actorid=&&actor_id;
else
update actor set actor_rating='waiter'
where actorid=&&actor_id;
end if;
end if;
commit;
end;
--------
5 循环
语法
loop
......
end loop;
exit;(退出循环)
exit [when];(退出循环,当满足WHEN时)
例1
declare
ctr number(3):=0;
begin
loop
insert into table1 values('tastes great');
insert into table2 values('less filling');
ctr:=ctr+1;
exit when ctr=100;
end loop;
end;
(注:如果ctr取为NULL,循环无法结束)
例2
FOR语法
for 变量<范围> loop
......
end loop;
declare
my_index char(20):='fettucini alfredo';
bowl char(20);
begin
for my_index in reverse 21..30 loop
insert into temp(coll) values(my_index);
/*循环次数从30到21*/
end loop;
bowl:=my_index;
end;
跟在in reverse后面的数字必须是从小到大的顺序,必须是整数,不能是变量或表达式
----------
6 游标
显式游标
打开游标
open <游标名>
例
open color_cur;
游标属性
%notfound
%found
%rowcount
%isopen
例
fetch my_cur into my_var;
while my_cur %found loop
(处理数据)
fetch my_cur into my_var;
exit when my_cur %rowcount=10;
end loop;
%notfound属性
取值情况如下:
fetch操作没有返回记录,则取值为true
fetch操作返回一条记录,则取值为false
对游标无fetch操作时,取值为null
<游标名> %notfound
例
if color_cur %notfound then...
注:如果没有fetch操作,则<游标名> %notfound将导致出错,
因为%notfound的初始值为NULL。
关闭游标
close <游标名>
例
close color_cur;
游标的FOR循环
语法
for <记录名> in <游标名> loop
<一组命令>
end loop;
其中:
索引是建立在每条记录的值之上的
记录名不必声明
每个值对应的是记录名,列名
初始化游标指打开游标
活动集合中的记录自动完成FETCH操作
退出循环,关闭游标
隐式游标
隐式游标是指SQL命令中用到的,没有明确定义的游标
insert,update,delete,select语句中不必明确定义游标
调用格式为SQL%
存贮有关最新一条SQL命令的处理信息
隐式游标的属性
隐式游标有四个属性
SQL%NOTFOUND
SQL%FOUND
SQL%ROWCOUNT:隐式游标包括的记录数
例:
delete from baseball_team where batting_avg<100;
if sql%rowcount>5 thn
insert into temp
values('your team needs help');
end if;
SQL%ISOPEN:取值总为FALSE。SQL命令执行完毕,PL/SQL立即关闭隐式游标。
7 标号
GOTO语句
用法:
goto you_are_here;
其中you_are_here是要跳转的语句标号
标号必须在同一组命令,或是同一块中使用
正确的使用
<>(标号)
x:=x+1
if a>b then
b:=b+c;
goto dinner;
end if;
错误的使用
goto jail;
if a>b then
b:=b+c;
<>(标号)
x:=x+1;
end if;
标号:解决意义模糊
标号可用于定义列值的变量
<>
declare
deptno number:=20;
begin
update emp set sal=sal*1.1
where deptno=sample.deptno;
commit;
end sample;
如果不用标号和标号限制符,这条命令将修改每条记录。
----------
8 异常处理
预定义的异常情况
任何ORACLE错误都将自动产生一个异常信息
一些异常情况已命名,如:
no_data_found 当SELECT语句无返回记录时产生
too_many_rows 没有定义游标,而SELECT语句返回多条记录时产生
whenever notfound 无对应的记录
用户定义的异常情况
由用户自己获取
在DECLARE部分定义:
declare
x number;
something_isnt_right exception;
用户定义的异常情况遵循一般的作用范围规则
条件满足时,获取异常情况:raise something_isnt_right
注意:同样可以获取预定义的异常情况
exception_init语句
允许为ORACLE错误命名
调用格式:
pragma exception_init(<表达式>,);
例
declare
deadlock_detected exception;
pragma exception_init(deadlock_detected,-60);
raise语句
单独使用RAISE命令,可再一次获取当前的异常情况(就象异常情况被重复处理了一样)。
在异常处理中,此语句只能单独使用。
异常处理标识符
一组用于处理异常情况的语句:
exception
when <表达式> or [表达式...] then
<一组语句>
...
when others then--最后一个处理
<一组语句>
end;既结束PL/SQL块部分,也结束异常处理部分
--------
练习与答案
1:
接收contract_no和item_no值,在inventory表中查找,如果产品:
已发货,在arrival_date中赋值为今天后的7天
已订货,在arrival_date中赋值为今天后的一个月
既无订货又无发货,则在arrival_date中赋值为今天后的两个月,
并在order表中增加一条新的订单记录。
product_status的列值为'shipped'和'ordered'
inventory:
product_id number(6)
product_description char(30)
product_status char(20)
std_shipping_qty number(3)
contract_item:
contract_no number(12)
item_no number(6)
arrival_date date
order:
order_id number(6)
product_id number(6)
qty number(3)
答案:
declare
i_product_id inventory.product_id%type;
i_product_description inventory.product_description%type;
i_product_status inventory.product_status%type;
i_std_shipping_qty inventory.std_shipping_qty%type;
begin
select product_id,product_description,product_status,std_shipping_qty
into i_product_id,i_product_description,
i_product_status,i_std_shipping_qty
from inventory
where product_id=(
select product_id
from contract_item
where contract_no=&&contractno and item_no=&&itemno);
if i_product_status='shipped' then
update contract_item
set arrival_date=sysdate+7
where item_no=&&itemno and contract_no=&&contractno;
elsif i_product_status='ordered' then
update contract_item
set arrival_date=add_months(sysdate,1)
where item_no=&&itemno and contract_no=&&contractno;
else
update contract_item
set arrival_date=add_months(sysdate,2)
where item_no=&&itemno and contract_no=&&contractno;
insert into orders
values(100,i_product_id,i_std_shipping_qty);
end if;
end if;
commit;
end;
2:
1.找出指定部门中的所有雇员
2.用带'&'的变量提示用户输入部门编号
3.把雇员姓名及工资存入prnttable表中,基结构为:
create table prnttable
(seq number(7),line char(80));
4.异常情况为,部门中奖金不为空值的雇员信息才能存入prnttable表中。
答案:
declare
cursor emp_cur is
select ename,sal,comm
from emp where deptno=&dno;
emp_rec emp_cur%rowtype;
null_commission exception;
begin
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is null then
begin
close emp_cur;
raise null_commission;
end;
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_sur;
exception
when null_commission then
open emp_cur;
fetch emp_cur into emp_rec;
while (emp_cur%found) loop
if emp_rec.comm is not null then
insert into temp values(emp_rec.sal,emp_rec.ename);
end if;
fetch emp_cur into emp_rec;
end loop;
close emp_cur;
commit;
end;
发表评论
-
转载]linux下启动oracle
2012-03-06 14:56 1095启动数据库实例,分为两步:第一步,启动监听;第二步,启动数据库 ... -
报表统计sql语句(decode用法及在sql中巧妙组建map的key)
2011-04-19 20:39 2282/* Formatted on 2011/04/19 20:4 ... -
sql中时间相减(case when then的用法)
2011-04-19 20:24 3435select f.FILE_ID,f.FILE_NAME,f. ... -
Oracle的取整和四舍五入函数 时间相减
2011-03-24 18:50 4573注:以下内容转载自http://hi.baidu.com/de ... -
【转】oracle中导入导出dmp数据库文件语句使用
2010-12-18 11:30 6677Oracle数据导入导出imp/exp ... -
oracle表中怎么建序列
2010-11-30 19:13 1229在oracle中sequence就是所 ... -
oracle伪列 分页
2010-11-27 17:52 1220/* Formatted on 2010/11/26 09:0 ... -
oracle中截取字符串的方法
2010-09-06 20:52 6371oracle 中截取字符串的情况如下: (1) 可以截取某 ... -
sql语句做数据初始化(科创)
2010-09-04 17:09 1392delete from td_project_integral ... -
Oracle通过job定时创建表-入门基础
2010-08-31 15:14 1664在项目中,经常会遇到需要定时完成的任务,比如定时更新数据, ... -
数据库应用开发工具Toad使用笔记
2010-08-07 16:45 1383TOAD使用笔记 1、把 ... -
Oracle 创建删除用户、角色、表空间、导入导出、备份 命令总结
2010-05-20 17:30 2925导入导出实用程序用于实施数据库的逻辑备份和恢复 导出实用将数据 ... -
oracle 的 Sql操作流程
2010-05-20 17:30 1099没有给你用户和密码的时候: 1、创建用户: 打开 ...
相关推荐
PL/SQL是Oracle对标准数据库语言的扩展,Oracle公司已经将PL/SQL整合到Oracle 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本教程将以循速渐进的方式讲述PL/SQL基础语法,结构和组件、以及...
《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...
1.进一步加将加强PL/SQL语句的应用; 2.掌握存储过程的创建与管理、函数的创建和使用、触发器的创建和使用;
《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...
这里是一些关于Oracle的开发的一些常用语句。根本覆盖你所想要的语句
通过学习本书,读者不仅可以掌握oracle常用工具oracle universal installer、net comfiguration assistant、sql developer、sql*plus的作用及使用方法,而且可以掌握sql语句和pl/sql的各种基础知识和高级特征(记录...
本书是专门为oracle开发人员而提供的编程指南 通过学习本书 读者不仅可以掌握编写sql语句和pl/sql块的基础知识 而且还可以掌握sql高级特征 正则表达式 flashback查询 merge语句 sql:1999连接 和pl/sql高级特征 ...
PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL ...
PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL...
Oracle PL/SQL实例精讲第五版--里面的使用表,课后答案,以及书中的案例,文件里有3个包。 很难受,本来只是想1积分的,居然这么高,没法修改。
走进Oracle、认识PL/SQL、数据表的基本操作、表中数据的基本操作、数据的基本查询、查询中函数的使用、数据表的高级查询、索引及视图的使用、...管理、PL/SQL语句优化、ASP NET+Oracle项目开发和Java+Oracle项目开发等内容...
结构化查询语言(Structured Query Language,简称SQL)是用来访问关系型数据库一种通用语言,属于第四代语言(4GL),其执行特点是非过程化,即不用指明执行的具体方法和途径,而是简单地调用相应语句来直接取得结果...
用PL/SQL 是 Oracle公司在标准 SQL 语言上进行一定的扩展而形成的一种数据库语言。它寄托于传统的 SQL 语句,同时又在功能上做了不少的扩充。PL/SQL 有着太多的优势,甚至让SQL 在它面前也黯然失色。 PL/SQL ...
通过学习本书,读者不仅可以掌握Oracle常用工具Oracle Universal Installer、Net Comfiguration Assistant、SQL Developer、SQL*Plus的作用及使用方法,而且可以掌握SQL语句和PL/SQL的各种基础知识和高级特征(记录...
通过学习本书,读者不仅可以掌握PL/SQL的基础编程知识(嵌入SQL语句和编写子程序),而且还可以掌握PL/SQL的所有高级开发特征(使用记录类型、集合类型、对象类型和大对象类型)。另外,本书还为应用开发人员提供了大量...
PL/SQL使用方法: 1、工具--首选项--用户界面--PL/SQL美化器--规则文件,选择.br的规则文件,点确定按钮。 2、点击工具栏的“PL/SQL美化器”,会自动把一段SQL语句重新排版美化。
PL/SQL 编程基础知识详解,PL/SQL 包含过程化语句和SQL语句数据操作和查询语句被包含在PL/SQL代码的程序单元中(PL/SQL块),经过逻辑判断、循环等操作完成复杂的功能或者计算.。