SqlPlus：
Tab数据表视图词典，Col表列词典
修改日期显示格式
Alter session set nls_date_format='yyyy-mon-dd hh:mi:ss';
测试：select sysdate from table;
相关:Current_date，next_day
select next_day(sysdate,'星期五') from hr.students; //表示sysdate所在周的星期五是几号[@more@]
SqlPlus：
Tab数据表视图词典，Col表列词典
修改日期显示格式
Alter session set nls_date_format='yyyy-mon-dd hh:mi:ss';
测试：select sysdate from table;
相关:Current_date，next_day
select next_day(sysdate,'星期五') from hr.students; //表示sysdate所在周的星期五是几号
转换函数
select to_char(sysdate,'yyyy-mm-dd') from hr.students;
select to_date('07-2007-10','mm-yyyy-dd') from hr.students;
select to_number('1.11') from hr.students;
Select user from dual;//查询当前登录用户
alter table hr.jobs modify job_title varchar2(100);//更改数据列类型长度
Set linesize 200//设置sqlplus的显示行宽，默认为100
DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。
例：select sum(decode(salary,10000,1,0)) aa from employees; //表示salary字段值为10000的个数，当salary等于10000时返回1,否则返回0，Sum处理后等于是记录符合条件的条数
Nvl判断Null值函数：select name,nvl(addr,'未输入') from students;
表间连接查询：
内连接：join
例：Select a.id,a.name,b.department from a join b on a.id=b.id
等价：Select a.id,a.name,b.department from a,b where a.id=b.id
外连接：左连接、右连接
例：左连接Select a.id,a.name,b.department from a,b where a.id(+)=b.id
右连接Select a.id,a.name,b.department from a,b where a.id=b.id(+)
子查询：分类为无关子查询和相关子查询
无关子查询例子：Select * from a where a.id in (select id from b)
相关子查询例子：Select * from a where a.id in (select id from b where a.id=b.id)
无关子查询指子查询与外层查询无直接关系，而相关子查询中的子查询与外层查询有条件关联关系
Union与Intersect
Select id from a Union select id from b//合并显示a表与b表的id字段数据(非物理合并，且过滤重复数据)
Select id from a Intersect select id from b//显示a表与b表中id值都相同的字段数据(非物理合并，且只显示重复数据)
Union All//支持
批量写入表数据：
Insert into a(id,name) select id,name from b //对已有表写入批量数据
Create table a As select id,name from b //对新建数据表写入批量数据
PL/SQL：
DBMS_OutPut //包输出
例：DBMS_OutPut.Put_line输出数据行到屏幕
默认输出选项为OFF，要通过Set设为ON：
Set Serveroutput On
Oracle中字段连接用||号，不同MSSQL用＋号，如Select ‘abcd’||’efgh’等效于MSSQL中Select ‘abcd’+’efgh’
Set ServerOuput On//设置打开系统输出
例：Set ServerOuput On Size 10000 //Size项表示输出大小，这里指定为最多输出10000个字节
语句注释与MSSQL相同，用--及/* */
变量付值：
A：=’abcdefg’;
SQL中：
Select 100,’AAA’可以返回结果，但Oracle中一定要有From表段，所以要加入伪表Dual，改为：
Select 100,’AAA’ From Dual
循环语句的三种方式：
declare
i number;
begin
i:=0;
Loop
i:=i+1;
exit when i>10;//退出可用IF Expression Then EXIT等实现，这是简洁写法dbms_output.put_line(i);
end loop;
end;
declare
i number;
begin
i:=0;
while i<10 loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
declare
begin
for i in reverse 1 .. 5 Loop//范围用两点..表示,reverse表示循环的顺序为从大到小，
//这时输出结果将为54321，没有reverse的话则为12345
dbms_output.put_line(i);
end loop;
end;
在FOR循环中，无论是从小到大还是从大到小循环，计数器都是以加1或减1计数，不能由我们来控制
通过跳转标记GOTO的用法实现循环：
Declare
i number;
begin
i:=0;
<>//设定标签dbms_output.put_line(i);
i:=i+1;
if i<10 then
goto repeat_loop;//跳转至标签end if;
end;
异常处理：
1.系统异常
declare
id varchar2(100);
begin
select job_id into id from hr.jobs;
dbms_output.put_line(id);
Exception
When no_data_found then
Dbms_output.put_line(‘错误：没有找到任何数据!’);
When Too_many_Rows then
Dbms_output.put_line('错误：返回了多行数据！');
When Others Then
Dbms_output.put_line(‘错误：发生其它未知错误！’);End;
系统预定义异常：
No_Data_Found//在Select into语句中无返回值
Too_Many_Rows//Select Into语句中返回了多行值
Dup_Val_On_Index//向有唯一约束的表中插入了重复值
Value_Error//一个算法、转换、类型或大小发生错误
Zero_Divide//遇到被零除错误
Others //遇到其它错误
2.自定义异常
Declare
x number;
error exception;//定义异常类型类量error
begin
x:=0;
<>//设置标签dbms_output.put_line(x);
x:=x+1;
If x=5 then
raise error;//提出异常error
end if;
if x<10 then
goto a;
end if;
Exception
when error then
dbms_output.put_line('5不是需要的值');
end;
复合变量(记录)：
由几个相关值组成的记录，通常用于支持SELECT语句的返回值，而不用对每一列进行变量存储处理。
1.定义N个变量与表字段相同，并使用它们
declare
Type myrecord is record(//定义一个含字符类型两个变量的记录jobid varchar2(100),
title varchar2(100));
real_record myrecord;//申明变量(实例化变量)begin
select job_id,job_title intoreal_record from hr.jobs where job_id='ST_MAN';//Into申明变量，而不是定义时的记录名dbms_output.put_line('ID='|| real_record.jobid||' Title='||real_record.title);//用申明变量名.子变量名的方式调用end;
2.定义与表字段相同数目及类型的变量，并使用它们
declare
myrec hr.jobs%Rowtype;//定义记录myrec，使其包含hr.jobs表中的每个字段相同类型及大小的变量begin
select * intomyrec from hr.jobs where job_id='ST_MAN';//这种试必须将Select *写入记录Myprc中dbms_output.put_line('ID='||myrec.job_id||' Title='||myrec.job_title ||' MinSalary='||myrec.min_salary);//调用时与表字段名相同end;
定义类型配置的变量：
使定义的变量与表中某个字段的类型及长度完全一致
declare
Type myrecord is record(
jobid hr.jobs.job_id%type,//表示定义Myrecord记录，其中jobid变量的类型与长度与表hr.jobs中的job_id字段完全一致title varchar2(100));
real_record myrecord;
begin
select job_id,job_title into real_record from hr.jobs where job_id='ST_MAN';
dbms_output.put_line('ID='||real_record.jobid||' Title='||real_record.title);
end;
表变量(数组)：
1.语法：
TYPE Type-name IS TABLE OF %TYPE
INDEX BY BINARY_INTERGER;
例：
DECLARE
TYPE JOBTITLE IS TABLE OF HR.JOBS.JOB_TITLE%TYPE
INDEX BY BINARY_INTEGER;
TITLE JOBTITLE;
I NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE;
I:=0;
FOR A IN (SELECT * FROM HR.JOBS) LOOP
I:=I+1;
TITLE(I):=A.JOB_TITLE;
END LOOP;
FOR B IN 1..I LOOP
DBMS_OUTPUT.PUT_LINE(TITLE(B));
END LOOP;
END;
游标：
Declare Cursor mycus Is Select * from Books;//定义游标mycus,定义完后自动关闭
Myrecord Books%RowType;//定义复合变量(记录)，用于保存Select * from Books返回的每一个结果字段
Begin
Open mycur;//打游标结果集
Fetch mycur Into myrecord;//读取游标结果集中的第一条数据到Myrecord记录中
While mycur%Found Loop//判断是否还有数据可取，前提为至少已经Fetch取了了一条数据，对能知道是否还有数据可取
DBMS_OutPut.Put_Line(myrecord.books_id|| ’,’|| myrecord.books_name);
Fetch mycur Into Myrecord;
End Loop;
Close mycur;
End; /
游标的属性：
%Found：布尔型判断，判断游标是否还有数据可提取(是否最后一行)，有数据返回True，无返回False
%IsOPen：布尔型判断，判断游标是否已打开
%NotFound：布尔型判断，判断游标是否还有数据可提取(是否最后一行)，无数据返回True，有返回False
%RowCount：返回游标处理行的当前计数器
Oracle游标支持参数：
Declare Cursor Cur (Id varchar) Is//作为参数时时需要给出参数类型，不需给长度(存储过程及函数也是如此)
Select Book_Name From Books Where Book_Id＝Id;
t_name Books.Books_name%Type;//定义一个与Book表的book_name字段同样类型的变量
Begin
Open Cur(‘0001’);//打开游标，传一个参数进去
Loop
Fetch cur Into t_name;
Exit when cur%Notfound;//判断当游标没有数据可取的时候退出
DBMS_OutPut.Put_Line(t_name);
End Loop;
Close Cur
End;
For循环在游标中的应用：
declare
cursor mycur is select * from hr.jobs;
begin
For cur in mycur Loop//For循环中，Cur计数器变量不用定义，使用游标时不用Open及Fetch
dbms_output.put_line(cur.job_title);
End loop;
End;
这样，会将游标mycur中的每条记录取数入到For循环计数器中，并执行循环体中的动作。
要通过游标更改数据时，必须在定义游标时指定为Update选项：
declare
cursor updatejobs is select job_title from hr.jobsfor update;
title varchar(100);
begin
open updatejobs;
fetch updatejobs into title;
while updatejobs%found Loop
Update hr.jobs set job_title=title||'_1' whereCurrent Of updatejobs;//Current of cur表示判断当前行，只更新本条记录行数据End loop;
End ;存储过程：
一、创建语法：
Create [Or Replace] ProceDure procedurename(//括号不是是必须的,当没有参数时一定不能加括号，否则是错误
[Param1 [{IN | OUT | IN OUT}] param1_Type],//Oracel默认参数类型为输入参数IN
[Param1 [{IN | OUT | IN OUT}] param1_Type])//参数必须指定数据类型，而且不能指定长度
…
Is | As
…
Begin
Proc_Body;
End;
说明：
Create ProceDure ProcName //创建新的存储过程ProcName(当已有名为ProcName的存储过程时将报错)
Create or Replace ProceDure ProcName//创建或复盖存储过程ProcName(推荐使用)
查看存储过程编绎错误具体信息：Show Errors//只这样表示显示最近一次编绎的存储过程的信息
查看指定存储过程编绎错误具体信息：Show Errors Procedure ProcName
存储过程体中没有DECLARE段，写了DECLARE段的话是错误的(自动失效)；
执行存储过程的方法：
1.语句块方式：//用于存储过程有参数，而参数是变量付值而不是常数时
Declare
Title varchar(10);
Begin
Title := ‘Anson’
Myproc(Title);
End;
2.Execute方式：//用于参数是常数或不带参数的存储过程
Execute myproc(‘Anson’)
但如果通过语句块执行存储过程时用了Execute是不支持的：
Begin
Execute myproc(‘Anson’)
End;
上面的写法是错误的
函数：
Create [or Replace] Function function-name
Return datatype;
[declaretion]//declare段，但不能有declare关键字
Section//函数主体语句段
Return var;
End function-name;
例：
create or replace function a(salary number)
return varchar2
is
id varchar2(4000);
begin
id:='';
for i in (select job_id from hr.jobs where max_salary=salary) loop
if id is null then
id:=id||i.job_id;
else
id:=id||'****'||i.job_id;
end if;
end loop;
return id;
end a;
包：
包是存储在Oracle数据库中相关的过程或函数的集合体，包必须包括包说明和包体两部分组成；
一、包说明创建语法：
CREATE [Or replace] PACKAGE package-name IS
Section //指包含函数或存储过程的说明
End package-name
例：
Create or replace Package aboutmystudents Is
Procedure UpdateStudents(id In number);
Function maxnumber(id In number) return number;
End aboutmystudents;
说明包aboutmystudents中包含一个存储过程UpdateStudents及一个函数maxnumber。
二、包体创建语法：
CREATE PACKAGE BODY package-name IS
Section
Procedure-Body;
Function-Body;
End package-name;
例：
数据库备份与恢复：
1.逻辑备份恢复(EXP和IMP)
2.物理备份
a.冷备份(关闭数据库后复制相关数据及日志文件)
b.热备份
逻辑备份恢复：
1.Exp “sys/pass as sysdba”
2.设置缓冲区大小
3.设置存放路径及文件名//D:myoraclebak.DMP(Exp备份文件类型为DMP)
4.选择备份模式：E(完整数据库备份)、U(用户模式备份)、T(表模式备份)
5.按提示完成相关选项即可。
1.Imp “sys/pass as sysdba”
2.设置待回复的备份文件路径及文件名//d:myoraclebak.DMP
3.设置缓冲区大小
4.是否包含重要文件？//不清楚具体作用，默认即可
5.是否忽略创建的语句？//如只是数据丢失不需要建表，即表在数据不在时可忽略，但建议在不清楚的情况下选NO
6.其它按提示完成相关选项即可
物理备份－冷备份：(略)
物理备份－热备份(联机备份)：
1.必须将日志模设置为归档模式：
A).archive log list//查看归档模式信息
可能得到结果：
数据库日志模式非存档模式
自动存档禁用
存档终点C:oracleora90RDBMS//指归档日志存放路径
最早的概要日志序列0
当前日志序列1
B).启用归档模式：
alter system set log_archive_start=true scope=spfile;
c).Shutdown immediate //停止数据库
d).Startup mount//mount模式启动数据库(Mount模式指只打开控制文件，不打开数据文件)
e).alter database archivelog;//将数据库切换到归档方式
f.).alter database open;//打开数据文件
函数使用说明及其它经验知识积累：
一、函数使用
1.Row_number() over(order by col)
为结果集生成一个序号，根据col列的排列顺序生成该序号(也可用DESC降序处理COL列),句例1：
select Job_id,salary,row_number() over(order by salary desc) rowno from hr.employees;
也可根据某字段的值分组生成序号，句例2：
select Job_id,salary,row_number() over(partition by job_id order by salary desc) rowno from hr.employees;
句例2表示根据job_id分组、并按salary以DESC排序生成序号
实例：
要求得到每个JOB_ID项目中员工销售数量最多的员工ID、JobID及其在所有员工中的销售排名次，则可以写为
select * from (
select employee_id,job_id,row_number() over(order by salary desc) AllSalNo ,row_number() over(partition by job_id order by salary desc) JobSalNo from hr.employees
) a where a.jobsalno=1;
如果只需要根据某个字段生成序号，不需分组生成，Oracle提供了Rowno隐式字段，则句例1可直接写为：
select Job_id,salary,rownum from (Select job_id,salary from hr.employees order by salary desc )a
三、经验知识积累
1．spool常用的设置set colsep' ';//域输出分隔符set echo off;//显示start启动的脚本中的每个sql命令，缺省为on
set feedback off;//回显本次sql命令处理的记录条数，缺省为on
set heading off;//输出域标题，缺省为on
set pagesize 0;//输出每页行数，缺省为24,为了避免分页，可设定为0。set termout off;//显示脚本中的命令的执行结果，缺省为on
set trimout on;//去除标准输出每行的拖尾空格，缺省为off
set trimspool on;//去除重定向(spool)输出每行的拖尾空格，缺省为off
2.系统选项
Set Serveroutput On/Off//设置输出项，在语句块中使用dbms_output.enable;
Set linesize 1000//设置行宽
Set auto On/Off//设置自动Commit
2.相关表/视图/函数
User_objects //用户对象目录表，列举了用户的table,index,view,proc,fun等所有对象
User_source//用户对象代码表，存储用户某对象的代码，如某Proc的代码信息
dba_objects//数据库对象信息，与MSSQL中的sysobjects类似
四、学习过程中遇到的错误及解决方法：
1.登录时提示ORA-12560: TNS:协议适配器错误。
原因：默认数据库实例有问题
解决方法：set orac
3252
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
