ORACLE学习笔记（不断追加）

最新推荐文章于 2022-05-31 17:09:37 发布

cuicai6848

最新推荐文章于 2022-05-31 17:09:37 发布

阅读量98

点赞数

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 into real_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 * into myrec 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.jobs for update;
title varchar(100);
begin
open updatejobs;
fetch updatejobs into title;
while updatejobs%found Loop
Update hr.jobs set job_title=title||'_1' where Current 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