论坛设计

<%'以下为页码显示。 if Page<>1 then%>

(首页) <%else%> (当前为首页) <%end if%>

<%if PageCount>PageN and CurrentPageN>=2 then%>

&CurrentPageN=<%=CurrentPageN-1%>\">(上<%=PageN%>页)

<%end if%>

<%for i=(CurrentPageN-1)*PageN+1 to (CurrentPageN-1)*PageN+PageN%> <%if i = Page then%>

-<%=i%>-

<%elseif i

&CurrentPageN=<%=CurrentPageN%>\"> <%=i%> 

<%end if%> <%Next%>

<%if PageCount>PageN and CurrentPageN

&CurrentPageN=<%=CurrentPageN+1%>\">(下<%=PageN%>页)

<%end if%>

<%if PageCount>PageN and Page<>PageCount then%>

&CurrentPageN=<%=int(PageCount/PageN)+1%>\">(最后一页)

<%end if%>

<%if PageCount=Page then%>(当前为最后页)<%end if%> (共<%=PageCount%>页)

保留版权2006-2007

Oracle学习笔记

JAVA 2009-08-13 15:46 阅读31 评论0

字号： 大 中 小

1、http://127.0.0.1:5560/isqlplus/ 访问自己的或者别人的oracle。

2、isqlplus不用装客户端也行。 3、sqlplus sys/bjsxt as sysdba 4、desc emp/dept/salgrade;//显示表结构 5、select sysdate from dual;//显示日期

6、select a*12 \"nihao Y\" from dual;//别名中有空格用双引号

7、select emname||'aa' from emp;//连接字符

8、select 'wo'||'a''a' from emp;两个单引号代表一个一个点印单引号,返回结果为woa'a

9、select distict a from dual;

10、select name from dual where name is null//空值查找。

11、select name from dual where name like '%a%'//包含a的所有name，like „%\\%%‟包含%的name。

/为转义字符 12、自己定义转义字符

select name from dual where name like '%$%%' escape'$'//name里包含%的纪录，并定义了$为转义

字符。

13、select name from dual order by id desc/asc ;--按降序/升序排列

14、select name from dual order by id desc,age asc;--先按id降序排列再按age升序排列。

15、select lower(name) from dual;--name 都小写

16、select substr(name,2,3)from dual;--把name从第二个字母开始截取3个字符。

17、select chr(65)from dual;--转换成A

18、slect ascii（„A‟）from dual;--转换成ascii码65； 19、select to_char(sal,$99,999,9999)from emp

--结果$800.0000 ,$1,600.0000。如果把$换成L则输出￥800.0000 ,￥1,600.0000。如果把99，999，9

999换成00，000，0000则输出为$00800.0000，$01,600.0000

20、select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual ;--格式化时间,hh为12进制小时，hh24

为24进制小时。

21、select hierdate wehre hierdate> to_date('1981-2-30 12:34:51','yyyy-m-dd hh24:mi:ss');--格式化特

定字符串为日期

22、select sal from emp where sal >to_number('$1,250.00','$9,999,00');

23、select to_char (12.356,'999,99') from dual;--12.35 select round(12.356,'999,99') from dual;--12.36

24、组函数：sum avg count max min

25、select count(name)from emp;--统计name不为null的纪录数。

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

1、select deptno,max(sal) from emp group by deptno where deptno=10；--不对，设计分组的条件必

须用having而不能用where。

2、一条综合查询 select avg(sal) from emp

where sal > 1200 --重点

group by deptno having avg(sal) > 2000 order by avg(sal) desc

3、select ename from emp

where sal in (select max(sal) from emp group by deptno) ；--条件返回多条值 ，肯定会出错，因为

sal满足的是in后（）内的多条值中的一条即可。 4、sql1992不支持全外连接，sql1999支持

select ename,dname from emp e full join dept d on (e.deptno=d.deptno)；--把全部记录输出，相当

于同时用了左右连接。 5、笛卡尔积出现的例子

select ename,dname from emp,dept;--没有where条件，输出笛卡尔积

6、不用组函数求最高工资

select sal from emp where sal not in

(select distinct (e1.sal) from emp e1, emp e2 where e1.sal < e2.sal)

7、可以把一个很长的sql当作一个view 求部门平均工资等级最低的部门名称

select dname from dept where deptno = (select deptno

from (select avg(grade) avgrade, deptno from (select e.sal, s.grade, e.deptno

from emp e, salgrade s

where e.sal between s.losal and s.hisal)

group by deptno) where avgrade =

(select min(avgrade) avgrade

from (select avg(grade) avgrade, deptno from (select e.sal, s.grade, e.deptno

from emp e, salgrade s

where e.sal between s.losal and s.hisal)

group by deptno)))

可以创建视图 deptsal

create view deptsal as (select avg(grade) avgrade, deptno

from (select e.sal, s.grade, e.deptno

from emp e, salgrade s

where e.sal between s.losal and s.hisal)

group by deptno)

则sql语句可以简写成

select dname from dept where deptno = (select deptno from deptsal

where avgrade = (select min(avgrade) avgrade from deptsal))

8、取工资前五名 用rownum

select t.* from (select ename from emp order by sal desc )t where rownum <5 ；--rownum只能

和<或<=使用

select t.* from (select ename from emp order by sal desc )t where rownum >5；--错误

要想取5到8位只能这么取（论坛分页显示用）

select r, ename

from (select ename, rownum r

from (select ename from emp order by sal desc))

where r >= 5 and r <= 8 ---------------------------------

1、conn sys/bjsxt as sysdba ;--系统管理员登陆

2、查看表所在的表空间

select TABLESPACE_NAME from all_tables where table_name='EMP';

------------------导出数据库-----------------------

2、导出某用户下的所有数据 进入一个空文件夹；

exp scott/tiger@fxjsbps_test ；--执行exp命令

3、创建用户

create user ynp identified by 507 --用户ynp 密码507

default tablespace users --默认的表空间是 users

quota 10M on users; --分配10M空间，也就是这个用户只能存10M的东东。

分配权限

grant create session, --授予连接数据库权限

create table, --授予创建表的权限 create view to ynp; --授予创建视图的权限；

4、导入数据库

进入数据库备份文件所在的文件夹中； ynp/507 -- 要导入数据的用户名

scott --只导入socct这个用户的数据（这些表从这个用户导出）

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

1、备份数据

create table dept2 as select * from dept;

2、插入数据 insert（三种方式） insert into dept2 values(50,'jim','beijing')； insert into dept2 (deptno,dname) values(60,'tom')； insert into dept2 select * from dept；--注意没有values

3、dml语句

selecty、update、insert、delete 后直接跟表名 --------------------------事务------------------------ 1、事务的开始起始于第一条dml语句；

---------------------------------- 1 、varchar2的最大储存是4k

2、创建表

create table student (id number(6) primary key, --主键约束

name varchar2(20) constraint stu_name_nn not null, --字段级约束条件创建以不起名

称，系统用默认名称 sex number(1), age number(3), sdate date,

grade number(2)default 1,

class number(4)references class(id), --外键约束 email varchar2(50) unique,--创建唯一约束

consraint student_name_email_uniq unique(name,email) --表级约束条 件 name和email字段同时不能重复

)

create table class ( id number(4) primary key, name varchar2(10) not null

)

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

1、alter table student add (adddress varchar2(10)) ;--添加字段

2、alter table student drop (adddress)；--删除某个字段

3、alter table student modify (adddress varchar2(50))；--修改某个字段

4、alter table student drop constraint stu_class_fk;--删除约束

5、alter table student add constraint stu_class_fk foreign key(class)references class(id);--添加约束

6、几个数据字典表

select * from dictionary --查看数据字典表 select * from User_Tables --用户表 select * from user_views --用户视图 select * from user_constraints --用户约束

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

1、create index idx_stu_email on students(email);--创建索引

drop insex idx_stu_email;--删除索引

当创建主键约束或唯一约束或着两者组合时索引自动创建。

索引提高查询速度，减慢修改效率； -------------------------视图------------

视图增加了维护难度（如修改了表结构），但简化了查询；

隐藏敏感信息；

create v$_sal as select sal from emp;--创建视图

-------------------------序列--------------------- 1、create sequence seq ；--创建序列对象 select seq.nextval from dual；--取序列值

insert into student (id,name)values(seq.nextval,'zhangsan')；--保证了id不重复

drop sequence seq；--删除序列对象 -------------------------- 三范式----------------- 1、三范式目的是尽量少的冗余数据；

2、要求：

第一范式：要有主键，列不可分；（不能像xx*xy*进行设计） 第二范式：不能存在部分依赖（不是主键的字段不能部分依赖主键）； 第三范式：不能存在传递依赖（如：学号（主键）<-班级编号<-班级信息）

--------------------------PL/SQL------------------------------------

1、一个简单的程序 set serveroutput on;

begin

dbms_output.put_line('HelloWorld!');

end; / --执行语句 2、一个简单的程序块

declare

v_name varchar2(20);

begin

v_name:='i am ynp'; dbms_output.put_line(v_name);

end;

3、一个完整的简单程序

declare

v_num number := 0;

begin

v_num :=2/v_num; dbms_output.put_line(v_num);

exception when others then dbms_output.put_line('error');

end;

4、变量声明尽量以v_ 开头

5、常用变量类型

binary_integer ：整数，主要用来计数而不是用来表示字段类型

number :数字类型 char ：定长字符串类型 varchar2 ：变长字符串类型 最大4K

date： 日期 long：长字符串，最长2G

boolean：布尔类型，可以取值为 false，True，null。null参与运算值不确定，所以最好声明变量时就给

它赋值。 6、变量声明 declare

v_temp number(1); v_count binary_integer :=0; v_sal number(7,2):=4000.00; v_date date := sysdate; v_pi constant number(3,2):=3.14;

v_valid booolean := false; v_name varchar2(20) not null :='ynp';

begin

dbms_output.put_line('v_temp is'||v_temp); --为连接符

end;

7、变量声明，使用%type属性

声明变量的数据对应表的某个字段的数据，当表结构改变时（如把number(7,2)，改为number(8,3)时，

必须修改相应程序），用%type属性可以解决该问题。

例： declare

v_empno number(4);

v_empno2 emp.empno%type; --empno2数据类型于emp表中的empno字段相同；

v_empno3 v_empno2%type;--v_empno3数据类型于v_empno2 数据类型相同

begin

dbms_output.put_line('Test');

end;

-----------------------自定义变量--------------------- 1、table 变量类型(相当于java中的数组)

declare

type type_table_emp_empno is table of emp.empno%type index by binary_integer;

v_empnos type_table_emp_empno;

begin

v_empnos(0) := 356; v_empnos(-1):=12; v_empnos(2):=111;

dbms_output.put_line(v_empnos(-1));

end;

说明：table变量类型命名规则：type（自定义变量）-table（table类型变量）-emp（表emp）-empno（e

mpno字段）； 下标值可以为负值；

2、Record变量类型(类似于java中的类)

declare

type type_record_dept is record

(

deptno dept.deptno%type, dname dept.dname%type,

loc dept.loc%type

);

v_temp type_record_dept;

begin

v_temp.deptno:=50; v_temp.loc:='bj' ; v_temp.dname:='aaa';

dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);

end;

但上述dept表变动时，此程序也得手动做相应改动，可以用下述方法自动改动：

使用%rowtype声明record变量

declare

v_temp dept%rowtype;

begin

v_temp.deptno:=50; v_temp.loc:='bj' ; v_temp.dname:='aaa';

dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);

end;

-----------------------------pl/sql中的sql语句----------------------

1、select语句

有且只有一条返回值，且必须加into

例子： declare

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

select ename,sal into v_ename,v_sal from emp where empno=7369; --有且只有一条返回

值， --且必须加into

dbms_output.put_line(v_ename||' '||v_sal);

end;

2、update 、delete、insert语句和sql中相同，可能和变量混用；

declare

v_deptno emp2.deptnpo%type:=10;

v_count number;

begin

update emp2 set sal =sal/2 where deptno =v_deptno;

dbms_output.put_line(sql%rowcount||'条记录被修改'); --sal%rowcount 返回记录数

commit; end;

3、执行ddl语句--（记住有execute immediate）

begin

execute immediate 'create table t(nn varchar(2) default ''a'')';

end; 4、if语句 declare

v_sal emp.sal%type;

begin

select sal into v_sal from emp where empno =7839;

if v_sal <= 800 then dbms_output.put_line('low') ; elsif v_sal >= 2800 then --不是elseif

dbms_output.put_line('high');

else

dbms_output.put_line('middle'); end if; --注意有这条语句

end; 5、while语句 declare

k binary_integer:=1;

begin

while ( k <11 ) loop dbms_output.put_line(k) ;

k:=k+1; end loop; end;

6、do ..while语句

declare

k binary_integer:=1;

begin loop

dbms_output.put_line(k) ;

k:=k+1; exit when(k>=11);

end loop; end; 7、for循环 declare

k binary_integer:=1;

begin

for k in 1..10 loop dbms_output.put_line(k) ;

end loop; end; declare

k binary_integer:=1;

begin

for k in reverse 1..10 loop --加上reverse为逆序打印

dbms_output.put_line(k) ;

end loop; end;

-------------------游标（pl/sql重点）-----------------

1、plsql中select语句只能返回一条语句，要想返回多条语句，用游标。

2、简单循环 declare cursor c is select * from emp; v_emp c%rowtype;

begin open c; loop

fetch c into v_emp;--fetch完后自动定位到下条记录

exit when(c%notfound);

dbms_output.put_line(v_emp.ename) ; --不能放在exit前，否则最后一条记录打印两次

end loop; close c; end; 3、while循环 declare cursor c is select * from emp; v_emp c%rowtype;

begin open c; fetch c into v_emp; while(c%found)loop

dbms_output.put_line(v_emp.ename) ;

fetch c into v_emp;

end loop; close c; end;

4、for循环 （循环时最简单）

declare cursor c is select * from emp;

begin

for v_emp in c loop --不用定义v_emp，不用打开关闭游标了

dbms_output.put_line(v_emp.ename) ;

end loop;

end; 5、带参数的游标

declare

cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is select * from emp where deptno = v_deptno and job= v_job;

begin

for v_emp in c(30,'CLERK') loop dbms_output.put_line(v_emp.ename) ;

end loop;

end; 6、可更新的游标

游标一般是作为记录集读取数据用的，但有时候用游标修改记录，这就是可更新游标；

declare cursor c is

select * from emp2 for update;

begin

for v_emp in c loop if(v_emp.sal <2000) then

update emp2 set sal =sal+1 where current of c ; --修改定位到的当前记录，注意形式

elsif(v_emp.sal>=2000) then delete from emp2 where current of c;

end if; end loop; commit; --提交

end;

-------------------------存储过程--------------------

1、把过程的declare变成 create or Replace produce p is 就行。

--declare

create or replace procedure p

is cursor c is

select * from emp2 for update;

begin

for v_emp in c loop if(v_emp.sal <2000) then

update emp2 set sal =sal+1 where current of c ;

elsif(v_emp.sal>=2000) then delete from emp2 where current of c;

end if; end loop; commit; end;

创建了存储过程不代表运行了存储过程；

运行此存储过程 ： 方式一 exec p；

方式二 begin p; end;

2、带参数的存储过程

in 相当于程序里的参数，供传入用，在存储过程不能改变其值； out 相当于程序里的返回值，在存储过程中可以为其赋值传出；

in out 既可以当参数又可以当返回值用；

不带上述说明符默认为in类型；

下例中v_a v_b 为in类型 v_c 为out类型 v_d 为in out 类型

create or replace procedure p(v_a in number,v_b number,v_c out number,v_d in out number)

is begin

if(v_a > v_b) then v_c := v_a;

else v_c := v_b; end if; v_d := v_d+1;

end; ---> 调试时：

可以在命令窗口调试，出错时 用show errors 显示出错信息；

可以在plDv中调试；

---> 运行时： 可以在命令窗口运行：

declare v_a number:=3; v_b number:=4; v_c number; v_d number:=5;

begin

p(v_a,v_b,v_c,v_d); dbms_output.put_line(v_c); dbms_output.put_line(v_d);

end;

可以在plDv中调试； ------------------函数-------------------

1、它有返回值

create or replace function tax_tag(sal number)return number --计算税率

is begin

if(sal > 1000)then return 0.1; elsif(sal>=2000)then return 0.15;

else return 0.2; end if; end;

select ename, tax_tag(sal) from emp ；-- 直接用函数tax_tag

-------------------------recursion 通过递归写树--------------------

-----》创建表并插入记录 create table article ( id number primary key, cont varchar2(4000),--文章内容

pid number, --父类id

isleaf number(1),--0代表非叶子节点，1代表叶子节点

alevel number(2) --等级

)

insert into article values(1,'蚂蚁大战大象',0,0,0); insert into article values(2,'大象被打趴下',1,0,1); insert into article values(3,'蚂蚁也不好过',2,1,2);

insert into article values(4,'瞎说',2,0,2); insert into article values(5,'没有瞎说',4,1,3); insert into article values(6,'怎么可能',1,0,1); insert into article values(7,'怎么没有可能',6,1,2); insert into article values(8,'可能行很大的',6,1,2); insert into article values(9,'大象进医院了',2,0,2); insert into article values(10,'蚂蚁是护士',9,1,3);

----------》存储过程

create or replace procedure p (v_pid article.pid%type,v_level binary_integer) is

cursor c is select * from article where pid = v_pid;

v_perStr varchar2(2000):=' ';

begin

for i in 1..v_level loop v_perStr := v_perStr||'***';

end loop; for v_article in c loop

dbms_output.put_line(v_perStr||v_article.cont);

if (v_article.isleaf = 0)then p(v_article.id,v_level + 1);

end if; end loop;

end;

----------》输出结果 蚂蚁大战大象 ***大象被打趴下 ******蚂蚁也不好过

******瞎说 *********没有瞎说 ******大象进医院了 *********蚂蚁是护士

***怎么可能

******怎么没有可能 ******可能行很大的