Oracle学习及工作笔记整理

Oracle由Oracle数据库和Oracle实例组成
Oracle数据库是存储数据的物理存在
Oracle实例=进程+进程所使用的共享内存池（SGA SYSTEM GLOBAL AREA）系统全局区
一个或多个实例可以访问一个数据库，一个实例只能访问一个数据库
连接数据库：connect scott/H123 ,connect sys/Hyx960411 as sysdba，显示当前用户show user
1.&的作用：select * from table where name=’&name’; 从外部输入代替&name
2.start 运行脚本 edit 编辑脚本 使用：start/edit d:\a.sql;
3.spool 将sqlplus屏幕的内容输入到指定文件 使用：spool d:\a.sql;…spool off;
4.linesize 设置显示行的宽度 默认80； 使用：show linesize; set linesize 90;
5.pagesize 每页显示的行数目 默认14； 使用同上
6.新建用户：create user hhh identified by m123； 密码英文开头
给用户修改密码 自己：password 用户名 别人:alter user 用户名 identified by M123；
删除用户（不能自己删自己）：drop user 用户名
如果要删除的用户已经创建了表，就用级联删除：drop user 用户名 cascade
7.授权： grant/revoke 角色/权限 to/from 用户名(with grant/admin option)
grant/revoke select on 表名 to/from 用户名(with grant/admin option)
角色（系统权限）：connect, dba, resource(在任何表空间建表)–admin
对象权限：select,insert,update,delete,all --grant
谁授权谁收回，顶级回收权限最低级也被回收
8.查询表的结构 desc 表名；
9.profile文件，指定用户最多尝试3次登陆，锁定时间2天
先创建profile文件（规则）:create profile 规则名称 limit
failed_login_attempts 3 password_lock_time 2;整数，天为单位
指定用户遵守规则：alter user 用户名 profile 规则名;
3次后锁定用户，解锁：alter user 用户名 account unlock;
强制修改密码(每隔10天修改，宽限期2天)：
create profile 规则名 limit password_life_time 10 password_grace_time 2;
指定用户遵守规则：alter user 用户名 profile 规则名;
口令历史（用户在修改密码时不允许使用以前用过的密码）：
create profile 规则名 limit password_life_time 10 password_grace_time 2 password_reuse_time 10;(口令10天后可重新启用)
删除profile文件，用户被释放:drop profile 规则名
10.number 既可以表示整数也可以表示小数
number(5,2)表示一个小数有5位有效数，2位小数
number(5)表示一个5位整数
blob 二进制数据 存放图片/声音（保密性高，普通存在文件夹下，数据库存路径）
11.往表里添加一个字段：alter table 表名 add (class number(2));
修改字段的长度或类型/名字（类型名字必须没有数据）：alter table 表名 modify (class number(2));
删除字段：alter table 表名 drop column 字段名；
修改字段：update 表名 set 字段=’’,字段=‘’ where id=‘1’
修改表名：rename 旧表名 to 新表名
12.往表里添加完整数据：insert into 表名 values(‘’，‘’)
添加部分数据：insert into 表名（字段名，字段名） values (’’,’’)
添加空值：insert into 表名（字段名，字段名） values (’’,null)
查询为空值的数据：where id is null
删除所有数据：delete from 表名-------恢复数据（回滚）:删除数据之前 创建保存点（保存点唯一）：savepoint 保存点名; 然后删除，在回滚到保存点:rollback to 保存点名（工作经常做保存点）
删除表所有数据：truncate table 表名，表结构在，不写日志，没办法找回，速度极快
13.Oracle默认日期格式“日-月-年”而且月份必须为2月，修改日期默认格式：
alter session set nls_date_format = ‘yyyy-mm-dd’;
修改后可以添加为’1905-05-06’临时生效，不永久
14.select count() from 表名 查询表中数据总量
select distinct 字段名 from 表名 消除重复行
年工资：select sal13+nvl(comm,0)13 “年工资” ename from emp;
nvl处理null值，如果comm值为null用0代替
查询1982.1.1后入职的人：select ename from emp where date>‘1-1月-1982’；
%0到多个字符，_单个字符
查询多个条件:select * from emp where id in (13,456,789);
关键字：like, asc(升序)desc(降序):order by desc, all 全部 any 任何一个
max, min, avg,（平均）sum(总和),
count:count(distinct dept)消除重复行 列的别名：sal12 as“年薪”,max(sal) as xs给表取别名的时候不能+as
查询最高工资的人的名字：select name,sal from emp where sal=(select max(sal) from emp)
15.group by 用于对查询结果分组统计 having 限制分组显示结果
顺序:group by having order by
16.例子：显示高于自己部门平均工资的员工信息：
select a2.name,a2.sal,a2.dept,a1.mysal from emp a2,(select dept, avg(sal) as “mysal” from emp group by dept) a1 where a2.dept=a1.dept and a2.sal>a1.mysal
from 中的子查询可当作一个视图（内嵌视图）
17.oracle 分页查询有三种：（1）使用rownum :select a1.* ,rownum rn from (select * from emp) a1
oracle给每一行分配的行号；rownum 每个select只能用一下,
（2）图1
18.用查询结果创建一个新表:
create table mytable (id,name,sal,job,dept)as select empno,ename,sal,job,dept from emp
19.合并查询，为了合并多个select语句的集合，可以使用集合操作符号
union:取两个结果集的并集且去掉重复行:select ename,sal,job from emp where sal>2500 union
select ename,sal,job from emp where job=‘manager’
union all:不会取消重复行，而且不会排序：select ename,sal,job from emp where sal>2500 union all
select ename,sal,job from emp where job=‘manager’
intersect：取两个结果集的交集：select ename,sal,job from emp where sal>2500 intersect
select ename,sal,job from emp where job=‘manager’
minus:取两个结果集的差集（所有属于A但不属于B的元素的集合）：哪个在前面哪个是A
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job=‘manager’
20.使用java操作数据库：import java.sql.*
JDBC与ODBC桥链接(主函数里)：try{1.加载驱动:Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
2.得到链接:Connection ct=DriverManager.getConnection(“jdbc:odbc:数据源名称”,“用户名”,“密码”);
3.从下面开始和2000一样：Statement sm=ct.createStatement();
ResultSet rs=sm.executeQuery(“select * from emp”);
while(rs.next()){System.ou.println(“字段”+rs.getString(字段在表中位置2));}
4.关闭打开的资源：rs.close();sm.close();ct.close();}catch(){}
使用JDBC链接:try{1.加载驱动:Class.forName(“oracle.jdbc.driver.OracleDriver”);
2.得到链接:Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:@链接数据库的ip地址：oracle端口号：数据库实例”,“用户名”,“密码”);
3.从下面开始和2000一样：Statement sm=ct.createStatement();
ResultSet rs=sm.executeQuery(“select * from emp”);
while(rs.next()){System.ou.println(“字段”+rs.getString(字段在表中位置2));}
4.关闭打开的资源：rs.close();sm.close();ct.close();}catch(){}
21.使用to_date添加日期（指定格式）：insert into emp valuers(to_date(‘1999-01-21’,‘yyyy-mm-dd’));
或者：insert into emp valuers(to_date(‘1999/01/21’,‘yyyy/mm/dd’));
22.数据迁移，从一个表转移多行数据：insert into kkk (myid,name,dept) select empno,ename,deptno from emp where deptno=10;
更新多个数据:update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=‘smith’) where ename=‘jim’;希望jim的工作工资补助与simth的一样
23.事务：保证数据一致性，由DML语句（增删改）组成，要么全部成功要么全部失败,当执行事务操作时，会在被作用的表上加锁，防止其他用户修改表的结构
提交事务（事务提交了无法退回到保存点）：commit;
只读事务：set transaction read only 设置只读不会取得最新数据变化
24.sql函数：（1）字符函数：lower（char）:将字符串转化为小写：select lower(字段名) from 表
uuper（char）:将字符串转化为大写：select upper(字段名) from 表
length(char):返回字符串长度：显示有5个字符的员工姓名：select * from emp where lenth(name)=5;
substr（char,m,n）:取字符串的字串:显示姓名的前三个字符：select substr(name,1,3) from emp;从第一个字符取三个字符（包括第一个）
例：显示姓名第一个字母大写：select upper(substr(name,1,1))||lower(substr(name,2,length(name)-1)) from emp;
replace:替换字符：select replace(name,“a”,“b”) from emp;将名字中a换成b
（2）数学函数：round(n,[m]):四舍五入:select round(字段,1) from emp;到小数点后1位,如果M为-1，就四舍五入到小数点前1位，如果省略M则保留整数
trunc(n,[m]):截取数字：select trunc(字段,1) from emp;到小数点后1位,如果M为-1，就截取到小数点前1位（个位为0），如果省略M则保留整数
floor(n):返回小于或等于n的最大整数
ceil(n)：返回大于或等于n的最小整数

                       mod(m,n)取模(取余数)：select mod(10,2) from dual(oracle测试表)   0
         (3)日期函数：sysdate:返回系统时间:select sysdate from dual;
                      add_months(d,n):查找已经入职8个月的人：select * from emp where sysdate>add_months(rzdate,8)
                                      显示入职天数：select trunc(sysdate-rzdate) "入职天数",name from emp
                      last_day(n):返回指定日期所在月份的最后一天：显示入职时间为当月倒数第三天的人
                                                    select rzdate,name from emp where last_day(rzdate)-2=rzdate;
         (4)转换函数：将数据类型转换为另一种：图2
                     select to_char(rzdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99999.99') from emp;
                      显示12月份入职的员工：select * from emp where to_char(rzdate,'mm')=12;
         (5)系统函数：sys_context:select sys_context('userenv','1324657') from dual;
                                  (1)terminal:当前会话客户对应的终端的标识符
                                  (2)lanuage:当前语言
                                  (3)ab_name：当前数据库名称
                                  (4)nls_date_format：当前会话客户所对应的日期格式
                                  (5)session_user:当前会话客户所对应的数据库用户名
                                  (6)current_schema:当前会话客户所对应的默认方案名
                                  (7)host:返回数据库所在主机的名称

25.当oracle创建了一个用户，其会默认给创建同名方案，用方案的方式组织数据对象，
26.逻辑备份：利用工具export从数据库中把数据导出到文件
逻辑恢复：数据库对象被误操作利用工具import把备份对象导入数据库
物理备份既可以在数据库open状态下也可以在关闭状态下，逻辑只能在打开状态下
一、导出分三种：导出表，导出方案，导出数据库()
(1)导出表(必须在cmd中，oracle文件夹下bin文件夹中)：
exp userid=账号/密码@数据库实例名 tables=(用户名.表名，表名) file=d:\e1.dmp
只导出表结构：exp userid=账号/密码@数据库实例名 tables=(用户名) file=d:\e1.dmp rows=n
直接导出（速度快，数据量大）：exp userid=账号/密码@数据库实例名 tables=(用户名.表名，表名) file=d:\e1.dmp direct=y
(2)导出方案：expexp userid=账号/密码@数据库实例名 owner=(被导用户名) file=d:\e1.dmp
(3)导出数据库:exp userid=账号/密码@数据库实例名 full=y inctype=complete file=d:\e1.dmp
二、导入同样分三种：
(1)导入表：imp userid=账号/密码@数据库实例名 tables=(表名) file=d:\e1.dmp 导入表到其他用户+ touser=用户名
导入表结构：imp userid=账号/密码@数据库实例名 tables=(表名) file=d:\e1.dmp rows=n
导入表的数据：imp userid=账号/密码@数据库实例名 tables=(表名) file=d:\e1.dmp ignore=y
(2)导入方案：imp userid=账号/密码 file=d:\e1.dmp 导入其他方案+formuser=sys touser=scott 从哪个用户导到哪个用户
(3)导入数据库：imp userid=账号/密码 full=y file=d:\e1.dmp
27.数据字典：数据库系统信息，所有者是sys，静态，是只读表和视图的集合，只能执行select语句，维护和修改系统自动完成
组成：包括数据字典基表和数据字典视图，基表存储数据库基本信息，普通用户不能直接访问，视图是基于基表所建立的视图，普通用户通过查询视图取得系统信息，视图包括三种类型：user_xxx,all_xxx,dba_xxx
select table_name from user_tables;显示当前用户所拥有的表
select table_name from all_tables;显示当前用户可以访问的所有的表
select table_name from dba_tables;显示所有方案中所有的表，用户必须是dba角色，或是select any table的权限
select * from dba_users;显示所有用户
dba_sys_privs 用户具有的系统权限；dba_tab_privs用户具有的对象权限；dba_role_privs用户的角色
查询所有的系统权限：select * from system_privilege_map order by name;
查询所有角色：select * from dba_roles;
查询所有对象权限：select distinct privilege from dba_tab_privs;
查询数据库的表空间:select tablespace_name from dba_tablespaces;
显示当前用户可以访问所有的数据字典视图：select * from dict where comments like ‘%grant%’;
显示当前数据库的全称：select * from global_name;
动态性能视图：记载例程启动后相关信息，动态,当数据库启动自动创建，当关闭，自动删除
28.Oracle的逻辑结构：表空间-段-区-块
建立表空间：表空间分类：数据表空间（最常用）,索引表空间，undo表空间，临时表空间，非标准块的表空间
create tablespace 表空间名 datafile ‘d:\test\表空间名.dbf’ size 20m uniform size 128k（区的大小）;
临时表空间：create temporary tablespace 表空间名 datafile ‘d:\test\表空间名.dbf’ size 20m uniform size 128k（区的大小）;
查询dba_data_files 这张表，可以看到表空间信息，dba_temp_files看到临时表空间信息
建表指定表空间：
create table mypart(dept number(4),name varchar2(10)) tablespace 表空间名
改变表空间状态：alter tablespace 表空间名 offline（脱机）/online（联机）
只读表空间状态，不能执行update,delete,insert：alter tablespace 表空间名 read only（只读）/write（可读可写）;
显示表空间所拥有的表：select * from all_tables where tablespace_name=‘表空间名’
查询表属于哪个表空间：select tablespace_name,table_name from user_tables where table_name=‘表名’
删除表空间：drop tablespace ‘表空间名’ including contents and datafiles;(同时删除数据文件)
扩展表空间：（1）增加数据文件：alter tablespace 表空间名 add datafile ‘d:\test\表空间名.dbf’ size 20m
（2）增加数据文件大小：alter tablespace 表空间名 ‘d:\test\表空间名.dbf’ resize 20m;最大500M
（3）设置自动增长：alter tablespace 表空间名 ‘d:\test\表空间名.dbf’ autoextend on next 10m maxsize 500m
移动数据文件：原因：存放表空间的磁盘损坏
(1)确定数据文件所属表空间：select tablespace_name from dba_data_files where file_name=‘d:\xx.dbf’
(2)使表空间脱机：alter tablespace 表空间名 offline;
(3)移动到指定位置:host move 原始路径 新路径
(4)对数据库进行逻辑修改：alter tablespace 表空间名 reaname datafile ‘d:\xx.dbf’ to ‘c:\xx.dbf’
(5)把表空间联机：alter tablespace 表空间名 online
29.数据完整性：用于确保数据库数据遵从一定的商业和逻辑规则：方式：约束，触发器，过程和函数
30.约束：not null, unique, primary key, foreign ker,check(强制满足条件) ,default(默认)
create table a(sex char(2) default ‘男’ check (sex in (‘男’,‘女’),
id char(8) references goods(id),外键 类型和长度一样
nums number(10) check(nums between 1 and 30));
为表增加约束：alter table goods modify goodsName not null;
alter table customer add constraint 约束名 unique(cardId);
alter table customer add constraint 约束名 check (address in (‘东城’,‘西城’,‘南城’));
删除约束：alter table 表名 drop constraint 约束名
主键删除：alter table 表名 drop primary key cascade;
显示所有约束信息:select * from user_constraints where table_name=‘表名’;
31.索引：create index 索引名 on 表名（列名,列名）;
缺点：建立索引系统占用表的1.2倍的硬盘和内存空间；更新数据的时候系统要有额外的时间来对索引进行更新
按存储方式：B树，反向，位图 按列的个数：单列，复合 按列值唯一性：唯一，非唯一
此外还有函数索引，全局索引，分区索引
显示表的所有索引：select index_name,index_type from user_indexes where table_name=‘表名’
32.预定义角色：connect ,resource,dba
自定义角色：角色的验证（修改的时候需输入密码）
不需要验证创建：create role 角色名 not identified;
需要验证创建：create tole 角色名 identified by 口令;
授权给角色（谁的表谁授权）：grant select/update/delete/insert on 表名 to 角色名；
角色向下授权：grant create session to 角色名 with admin option;
把角色分配给用户（普通用户需要grant any role 权限）：grant 角色名 to 用户名 with admin option（可向下分配角色）
删除角色（普通用户需要drop any role 权限）：drop role 角色名;
显示所有角色：select * from dba_roles;
显示角色具有的系统权限：select privilege,admin_option from role_sys_privs where role=‘角色名’
显示角色具有的对象权限：select privilege,admin_option from dba_tab_privs where role=‘角色名’
显示用户所具有的角色：select granted_role,default_role from dba_role_privs where grantee=‘用户名’
33.精细访问控制：用户可以使用函数、策略实现更细微的安全访问控制，在客户端发出sql语句时，oracle会在sql语句后面追加where子句，并执行新的sql语句，可以使得不同的用户在访问同样的表时，返回不同的数据
34.pl/sql缺点：移植性不好
35.单行注释：-- 多行注释：/…/ 命名规范：定义变量v_xx ,定义常量c_xx ,定义游标xx_cursor ,定义例外e_xxx；
块：是pl/sql基本程序单元，编写pl/sql程序就是编写pl/sql块，如果实现复杂功能，需要一再块中嵌套其他的块；
pl/sql块构成：declear–定义部分，定义常量，变量，游标，例外，复杂数据类型等（可选的）
begin–执行部分，需要执行的pl/sql或者sql语句（必须的）
exception–例外处理部分，处理运行的各种错误（可选的）
end;（三个部分）
案例：简单块：案例（1）set serveroutput on/off（打开输出选项，可不写）
begin
dbms_output.put_line(‘hello’);.前面是包，.后面是过程，也可以是函数
end;
/
（2）declare
v_name varchar2(5);
begin
select name,sal into v_name,v_sal from emp where emono=&xx;(地址符，从控制台输入)
dbms_output.put_line(‘雇员名：’||v_name||‘工资:’||v_sal);
end;
/
(3)处理异常
declare
v_name varchar2(5);
begin
select name,sal into v_name,v_sal from emp where emono=&xx;(地址符，从控制台输入)
dbms_output.put_line(‘雇员名：’||v_name||‘工资:’||v_sal);
exception
when no_data_found then
dbms_output.put_line(‘输入错误’);–如果默认为0呢？
end;
/
36.变量和常量（游标）：（1）标量(scalar)：1.定义变长字符串：v_name varchar2(10)
2.定义小数（范围-9999.99~9999.99）：v_sal number(6,2)
3.定义小数并赋其初始值：v_sal number(6,2):=5.4
4.定义日期类型数据：v_date date
6.定义一个布尔变量，不能空，初始false:v_valid boolean not null default false;
案例：输入员工号，显示姓名、工资、个人所得税（税率0.03）：
declare
c_tax_rate number(3,2):=0.03;–税率
v_name varchar2(5);–v_name emp.ename%type;(定义的v_name与表中字段保持一致)
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_name,v_sal from emp where empno=&aa;
v_tax_sal:=v_salc_tax_rate;
dbms_output.put_line(‘姓名是：’||v_name||‘工资’||v_sal||‘税’||v_tax_sal);
end;
（2）复合变量(composite)：1.pl/sql记录：类似高级语言的结构体
2.pl/sql表：类似高级语言的数组
3.嵌套表
4.varray
案例(记录)：declare
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);–定义记录类型emp_record_type，可存放三个数据
sp_record emp_record_type;定义了一个变量 类型是emp_record_type
begin
select ename,sal,job into sp_record
from emp where empno=7788;
dbms_output.put_line(‘员工名:’||sp_record.name);–取出单个字段
end;
案例（表）：declare
type sp_table_type is table of emp.ename%type index by binary_integer;–定义了表类型sp_table_type，用于存放emp.ename%type这种字段类型的数组
–index by binary_integer;表示下表是整数（可为负数）
sp_table sp_table_type; --定义变量sp_table，类型是sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line(‘员工名:’||sp_table(0));–sp_table(0)下标为0的元素
end;–如果把where语句去掉，会报错，解决用参照变量
（3）参照变量：游标变量：
1.案例：编写pl/sql块，输入部门号，显示所有部门所有员工姓名和工资：
declare
type sp_emp_cursor is ref cursor;–定义变量，类型是游标
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin --把游标和一个select语句结合
open test_cursor for select ename,sal from emp where deptno=&no;
loop --循环取出
fetch test_cursor into v_ename,v_sal;
exit when test_cursor%notfound;–退出循环，判断test_cursor是否为空
dbms_output.put_line(‘名字’||v_ename||‘工资’||v_sal);
end loop;
end;
37.创建存储过程：既可以指定输入参数(in,可以将数据传输到执行部分)也可以指定输出参数(out,可将执行部分的数据传到应用环境)
create procedure 名 is 如果名被用-create or replace procedure 名 is(替换存储过程)
begin
insert into 表名 values(‘name’,‘123’);
end;向表添加数据
/（创建存储过程）
如何查看错误信息:show error;
创建带参数的存储：create procedure 名(name varchar2,newsal number) is（不用设置字段大小）
begin
update emp set sal=newsal where ename=name;
end;向表添加数据
/（创建存储过程）
调用:exec 名(‘scott’,6666);
如何调用存储过程：1.exec 过程名 (参数1，参数2…);
2.call 过程名 (参数1，参数2…);
38.函数：用于返回特定的数据，头部必须包括return子句
案例：输入雇员的姓名，返回年薪
create function sp_fun(函数名)(name varchar2输入的参数) return
number is yearsal(变量名称) number(7,2);
begin
select sal12+nvl(奖金,0) into yearsal from emp where ename=name;
return yearsal;
end;
调用函数(sqlplus):var aa(变量名) number;
call sp_fun(‘scott’) into aa;
39.包：包是在逻辑上组合过程和函数，由包规范和包体组成
创建包：create package 包名 is
procedure update_sal(name varchar2,newsal number);–声明过程
funtion annual_income(name varchar2) return number;–声明函数
end;
给包实现包体：(create or replace)
create package 包名 is
procedure update_sal(name varchar2,newsal number);–声明过程
is begin
update emp set sal=newsal where ename=name;
end;
funtion annual_income(name varchar2) return number;–声明函数
is
annual_salary number;
begin
select sal12+nvl(奖金,0) into annual_salary from emp
where ename=name;
return anuual_salary;
end;
调用包的过程或者函数：带包名
exec 包名.过程/函数名(参数);
end;
40.触发器：隐含的执行的存储过程，

41.三种条件分支语句：if–then if–then–else if–then–elsif–else
案例：编写一个过程，输入一个雇员名，如果工资低于2000，就增加10%
create or replace procedure sp_pro(spname varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=spname;
if v_sal<2000 then update emp set sal=sal1.1 where ename=spname;
end if;
end;—调用过程exec sp_pro(‘scott’);
案例：输入一个雇员名，如果补助不是0在原来的基础上加100，如果为0，就变成200
create or replace procedure sp_pro(spname varchar2) is
v_comm emp.comm%type;
begin
select comm into v_comm from emp where ename=spname;
if v_comm<>0 then update emp set comm=comm+100 where ename=spname;
else update emp set comm=comm+200 where ename=spname;
end if;
end;—调用过程exec sp_pro(‘scott’);
案例：输入一个雇员编号，如果职位是president，工资+1000，如果职位是manager，就+500，其他雇员+200
create or replace procedure sp_pro(spno number) is
v_job emp.job%type;
begin
select job into v_job from emp where empno=spno;
if v_job=‘president’ then update emp set sal=sal+1000 where empno=spno;
elsif v_job=‘manager’ then update emp set sal=sal+500 where empno=spno;
else update emp set sal=sal+200 where empno=spno;
end if;
end;
42.循环语句：(1)loop:以loop开头，以end loop结尾，至少被循环一次
案例：有张user表，字段：用户id，用户名，要求可输入用户名，并循环添加10用户到表，id号从1开始增加
create procedure sp_pro(spname varchar2) is
v_num number:=1;
begin
loop
insert into user values(v_num,spname);
exit when v_num=10;
v_num=v_num+1;
end loop;
end;
(2)while:只有条件为true的时候才会执行循环语句，以while…loop开始,以end loop结束
案例：有张user表，字段：用户id，用户名，要求可输入用户名，并循环添加10用户到表，id号从11开始增加
create procedure sp_pro(spname varchar2) is
v_num number:=11;
begin
while v_num<=20 loop
insert into user values(v_num,spname);
v_num=v_num+1;
end loop;
end;
(3)for循环
43.顺序控制语句：（1）goto（不建议使用，多循环跳到特定的表示符去执行语句）
declare i int:=1;
begin
loop
dbms_output.put_line(‘输出I=’||i);
if i=10 then
goto end_loop;
end if;
i=i+1;
end loop;
<<end_loop>>
dbms_output.put_line(‘over’);
end;
（2）null语句不会执行任何操作，并且会将控制传递到下一条语句，主要好处提高可读性
44.编写分页过程（为JAVA使用）: in 表示字段为输入参数， out 表示为一个输出参数
①.无返回值的存储过程：现有一张book表，编写过程向表添加书
Ⅰ.创建存储过程：
create procedure addbook (bookid in number,bookname in varchar2) is
begin
insert into book values(bookid,bookname);
end;
Ⅱ.在JAVA中调用：
try{1.加载驱动:Class.forName(“oracle.jdbc.driver.OracleDriver”);
2.得到链接:Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:@链接数据库的ip地址：oracle端口号：数据库实例”,“用户名”,“密码”);
3.创建CallableStatement:CallableStatement cs=ct.prepareCall("{call addbook(?,?)}");
4.赋值：cs.setInt(1,10);cs.setString(2,“笑傲”);
5.执行：cs.execute();}catch (Exception e){…}finally{关闭资源}
②.有返回值的存储过程：输入雇员编号，返回姓名
Ⅰ.创建存储过程：
create procedure fanhui(spno in number,spname out varchar2) is
begin
select ename into spname from emp where emmno=spno;
end;
Ⅱ.在JAVA中调用：
try{1.加载驱动:Class.forName(“oracle.jdbc.driver.OracleDriver”);
2.得到链接:Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:@链接数据库的ip地址：oracle端口号：数据库实例”,“用户名”,“密码”);
3.创建CallableStatement:CallableStatement cs=ct.prepareCall("{call fanhui(?,?)}");
4.赋值：cs.setInt(1,7788);cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
5.执行：cs.execute();
6.取出返回值：String name=cs.getString(2);
7.输出：System.out.println(“7788的名字”+name);}catch (Exception e){}finally{关闭资源}
③：有返回值较多（结果集）以集合形式返回：输入部门号，返回部门的所有雇员信息
Ⅰ.先创建一个包：create package test as
type test_cursor is ref cursor;
end test;
Ⅱ.创建存储过程：create procedure t(spno in number,p_cursor out test.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spno;
end;
Ⅲ.在JAVA中调用：
try{1.加载驱动:Class.forName(“oracle.jdbc.driver.OracleDriver”);
2.得到链接:Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:@链接数据库的ip地址：oracle端口号：数据库实例”,“用户名”,“密码”);
3.创建CallableStatement:CallableStatement cs=ct.prepareCall("{call addbook(?,?)}");
4.赋值：cs.setInt(1,10);cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
5.执行：cs.execute();
6.得到结果集：ResultSet rs=(ResultSet)cs.getObject(2);
7.循环取出：while(rs.next()){rs.getInt(1)+rs.getString(2)…}}
catch (Exception e){…}finally{关闭资源}
案例：编写存储过程，要求可以输入表明、每页显示记录数、当前页，返回总记录数，总页数，返回的结果集。(java分页算法)
create package test as type test_cursor is ref cursor;
end test;
create procedure fenye(tablename in varchar2,pagesize in number,pagenow in number,trecord out number,tpage out number,p_cursor out test.test_cursor) is
v_sql varchar2(1000);
v_begin number:=(pagenow-1)pagesize+1;
v_end number:=pagenowpagesize;
begin
v_sql:='select * from (select t1.,rownum rn from (select * from ‘||tablename||’) t1 where rownum<=’||v_end||’) where rn>=’||v_begin;
open p_cursor for v_sql;
计算4，5值:v_sql:='select count(*) from '||tablename;
execute immediate v_sql into trecord;
if
mod(trecord,pagesize)=0 then tpage:=trecord/pagesize;
else
tpage:=trecord/pagesize+1;
end if;
close p_cursor;
end;
使用java程序测试
try{1.加载驱动:Class.forName(“oracle.jdbc.driver.OracleDriver”);
2.得到链接:Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:@链接数据库的ip地址：oracle端口号：数据库实例”,“用户名”,“密码”);
CallableStatement:CallableStatement cs=ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
cs.setString(1,“emp”);表明
cs.setInt(2,5);一页显示几条记录
cs.setInt(3,1);显示第几页
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);注册总记录数
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);注册总页数
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);注册返回的结果集
cs.execute();
int rowNum=cs.getInt(4);取出总记录数
int pageCount=cs.getInt(5);
ResultSet rs=(ResultSet)cs.getObject(6);
System.out.println(“总记录数”+rouNum);
System.out.println(“总页数”+pageCount);
while(rs.next()){System.out.println(“编号”+rs.getInt(1)+“名字”+rs.getString(2));}}1是集合里的字段列序
catch (Exception e){…}finally{关闭资源}
45.例外的处理：预定义例外：用于处理常见的oracle错误
非预定义例外：处理预定义处理不了的错误
自定义例外：与oracle错误无关的其他情况
案例：写过程，可接受雇员的编号，显示雇员姓名，问题：如果编号不存在，怎么处理
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line(‘名字:’||v_ename);
exception
when no_data_found then
dbms_output.put_line(‘编号没有’);
end;
①.常见预定义例外：1.case_not_found 没有再条件内的异常
案例：begin
select sal into v_sal from emp where empno=aa;
case
when v_sal<1000 then update emp set sal=sal+1 where empno=aa;
when v_sal<2000 then update emp set sal=sal+2 where empno=aa;
end case;
exception 如果找到的人工资小于3000，不在条件内
when case_not_found then dbms_output.put_line(‘没有’);
end;
2.cursor_already_open 已经打开一次游标，但是又打开一次
3.dup_val_on_index 在唯一索引上添加重复数据
4.invaild_cursor 从没有打开的游标提取数据，或者关闭没有打开的游标
5.invalid_number 当输入的数据有错误（输入的字符与字段不符）
6.no_data_found 输入的数据找不到
7.too_many_rows 返回记录超过了一行：select ename into v_ename from emp
8.zero_divide 当执行2/0,啥的，
9.value_error 当执行赋值操作时，变量长度不足容纳实际数据
10.login_denide 用户非法登陆 not_logged_on 没登陆就执行dml
storage_error 超出内存空间或者内存被损坏 timeou_on_resource 等待资源超时
②处理自定义例外：由开发人员为特定的情况所定义的例外
案例：接收一个雇员编号，并给他工资增加1000，如果不存在，请提示
create procedure test(spno number) is
定义一个例外：myex exception
begin
update emp set sal=sal+1000 where empno=spno;输入的人不存在，不会抛异常
if sql%notfound then raise myex;(sql%notfound这是表示没有更新成功，raise myex触发例外)
end if;
exception
when myex then
dbms_output.put_line(‘没有更新任何用户’);
end;
46.视图:视图是个虚拟的表，由select定义，不占用空间，不能添加索引，简化复杂查询，有利于提高安全性
create view 视图名 as select * from emp where sal<1000;(with read only) 只读
create view 视图名 as select emp.empno,emp.ename,dept.dname where dept.deptno=emp.deptno
drop view 视图名
47.select * from (select * from t_example order by dbms_random.random) where rownum <= 50 从数据库中随机提取50条数据
48.游标属性，%notfound,%found,%isopen,%rowcount当前提取的行数

Oracle LINUX下操作：

登陆oracle流程：
1.ssh 到服务器
2.切换到oracle ： su - oracle
3.配置环境变量export ORACLE_SID=要登陆的数据库实例名
4.查看监听是否打开： lsnrctl status|start|stop 监听起停
5.sqlplus登陆： sqlplus user/password 普通用户登陆
sqlplus / as sysdba 管理员登陆
6.查看SERVICE_NAME ：show parameter service_name;
7.查看INSTANCE_NAME：show parameter instance_name;

8.备份oracle
查看oracle_home :echo $ORACLE_HOME
在oracle/oradata目录下创建test_bak 文件夹 ：mkdir test_bak（给文件夹权限chmod 777 test_bak）
登陆oracle（以SYSDBA登陆）
创建directory ：create directory 自定义目录名 as ‘/oracle/oradata/newfolder’; #引号中不能有空白符
退出oracle：exist
执行备份命令：
expdp system/oracle schemas=crn dumpfile=crn.dmp DIRECTORY=test_bak;
expdp system/管理员密码 schemas=要备份的用户名 dumpfile=自定义文件名.dmp DIRECTORY=directory_name;
恢复的时候注意，停止触发器！

补充：

ROW_NUMBER() OVER(ORDER BY zdf DESC)
1.dense_rank() over(partition by a order by b nulls last) 按A分组，按B排序 null值为最后 连续rank (非连续rank不加dense_)
2.from i inner join r on 条件：返回两表相同x`得数据，取交集
From i left join r on 条件：左表为主表，右表只返回相同得数据
From i right join r on 条件：右表为主表，左表只返回相同得数据
3.满足条件时用B表数据更新或插入A表数据
只更新：Merge into A using(select 字段 from B ) on (条件)
When matched then
Update set a.year=b.year;
Commit;
只插入：Merge into A using(select 字段 from B ) on (条件)
When not matched then --当条件不关联插入
Insert(a.字段) values(b.字段);
Commit;
带where条件得更新和插入：
Merge into A using(select 字段 from B ) on (条件)
When matched then
Update set a.year=b.year where c.city!=’jiangxi’
When not matched then
Insert(a.字段) values(b.字段) where c.city=’jiangxi’;
Commit;
4.Decode(条件，值，返回值)
含义：if 条件=值 then 返回值
Decode(字段或字段的运算，值1，值2，值3)
含义：当字段或字段的运算的值等于值1 返回值2，否则返回值3
5.填充函数：select lpad(‘aaa’,7,‘b’),rpad(‘aaa’,7,‘b’) from dual
bbbbaaa aaabbbb
Lpad(字符串/字段，总长度，拼接的字符)
5.过去一个月：select add_months(sysdate,-1) from dual
Select sysdate - interval ‘7’ minute/hour/day/month/year from dual
6.下一行减上一行：
整体下移一行 lag(字段) over (order by 字段)
下一行移到上一行Lead(字段) over(order by字段)
7.按逗号分隔字符串
BZQX IN
(SELECT REGEXP_SUBSTR(V_FXQX, ‘[^,]+’, 1, LEVEL)
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(V_FXQX, ‘[,]’, 1) + 1)
OR V_FXQX = ‘TT’)
8.给表和列加备注
– Add comments to the table
comment on table ADB_DJ_FXZQXX
is ‘发行证券信息’;
– Add comments to the columns
comment on column ADB_DJ_FXZQXX.zqdm
is ‘证券代码’;
9.rownum 和 rowid的区别：
ROWNUM是个伪列，是获取数据后加上的.
ROWID是物理存在的，实际存在的一个列，是一种数据类型，基于64位编码的18个字符来唯一标识一条记录的物理位置的一个ID。
10.隔行取数，MOD取余函数实现
11.WM_CONCAT（）列转行
12.instr(qxmc,’(’,-1,1)
查询字符串中指定的字符位置，从后往前查，第一个（
13.定义转义字符：
Select * from adb where a not like ‘%/_%’ escape ‘/’
14.复制表（含数据）：create table a as select * from test;
复制表（不含数据）：create table a as select * from test where 0=1;
15.无条件INSERT：
insert all
into emp1(empno,ename,job) values(empno,ename,job)
into emp2(empno,ename,deptno) values(empno,ename,deptno)
select empno,ename,job,deptno from emp where deptno in (10,20);
同时向两个表插入数据，且两个表数据条数一样。
有条件INSERT：
insert all
when job in (‘1’,‘2’) then
into emp1(empno,ename,job) values(empno,ename,job)
when deptno in (10,20) then
into emp2(empno,ename,deptno) values(empno,ename,deptno)
select empno,ename,job,deptno from emp;
增加条件后按条件插入，但是满足两个条件的都会插入。
insert first
when job in (‘1’,‘2’) then
into emp1(empno,ename,job) values(empno,ename,job)
when deptno in (10,20) then
into emp2(empno,ename,deptno) values(empno,ename,deptno)
select empno,ename,job,deptno from emp;
当第一个表插入符合条件的行时，第二个表不再插入相同的行
16.exists ，in…区别
17.level 伪列
select level from dual connect by level<4
结果：1
2
3
18.select q’[aa’]’ from dual ===
select ‘aa’’’ from dual —>aa’
19.select regexp_count(‘aa,bb,a’,’,’,1) from dual —查找’,‘的个数,从第一位开始
regexp_replace(‘asd,w,ew’,’,’) 替换,为NULL
regexp_replace(‘asd,w,ew’,’[^,]’)替换除了,的字符为NULL
^不在[]里的时候表示为字符的开始，KaTeX parse error: Double superscript at position 145: …gexp_like(a,'^A'̲)=a like 'A%' r…’)=a like ‘%A’
regexp_like(a,’^A$’)=a like ‘A’
regexp_like(a,‘AB+’)=a like ‘AB%’
regexp_like(a,‘AB*’)=a like ‘%A%’
NULL 值不能LIKE ，查询不到
20.‘Michael Hartstein’取名字大写字母
select regexp_replace(‘Michael Hartstein’ ,’([[:upper:]])(.)([[:upper:]])(.)’,’\1.\3’) as a from dual
四个()把字符串分为四部分：M,ichael ,H,artstein
‘\1.\3’：取第一部分和第三部分中间.链接
select regexp_replace(‘Michael Hartstein’ ,’([[:upper:]])(.)([[:upper:]])(.)’,’\1.\3’) as a from dual ;
select regexp_substr(‘Michael Hartstein’,’[[:upper:]]+’,1,level) from dual
connect by level <= regexp_count(‘Michael Hartstein’,’[[:upper:]]’)
select regexp_count(‘Michael Hartstein’,’[[:upper:]]’) from dual
21.oracle11.2–>select sum(sal) as total_sal,
listagg(name,’,’) within group(order by name) as total_name from emp
group by deptno; listagg -->用’,‘把字符串连一起
22.分析函数：
select max/min(name) keep(dense_rank first order by sal) over(partition by deptno),
max/min(name) keep(dense_rank last order by sal) over(partition by deptno) from emp where deptno=10 order by 1,6.
select first_value(name) over(partition by deptno order by sal desc) from emp where deptno=10 order by 1,5 desc
sal最大的人的姓名
比例函数：
select deptno,empno,name,sal
ratio_to_report(sal) over(partition by deptno) as 比例 from emp order by 1,2
查询各员工占本部门的工资比
23.months_between(y1,y2)–Y1和Y2间隔了几个月
to_char(date’2019-5-9’,‘DY’)–查询日期对应的星期
to_char(sysdate,‘d’) 返回值1代表周日2代表周一…
to_char(sysdate,‘dd’)取日期的天
to_char(sysdate,‘day’)取日期对应的星期几
next_day(sysdate,1)下一个周日，2为周一…
trunc(sysdate,‘day’) 本周周一日期
trunc(sysdate,‘y’)年初
last_day(sysdate)月末日期
24.interval 保存时间间隔信息
interval ‘2 12:30:59’ day to second as a
interval ‘’ year/month/day/hour/minute/second/day to second/year to month

提取年月日时分秒：extract(year from sysdate) 返回值为number类型
year/month/day/hour/minute/second

extract 可以提取interval中的信息
25.select trunc(sysdate,‘mm’)+(level-1) from dual
connect by level <=date’2019-6-1’-date’2019-5-1’
26. select count(*) over(partition by )
27.数据库所有数据对象：user_objects,所有包：user_arguments
28.pivot 与 unpivot

29：过程另一种写法：
execute immediate ’
select count(*)
from ADB_DJ_ZQ
Where ZQDM = ‘’’ || v_zqdm || ‘’’ AND substr(to_char(b.ZBKSR,’‘yyyymmdd’’),1,4) = ‘’’ || v_syear || ‘’’
’ into v_coun;
If (coun>=’1’ ) then
Sql := ‘ select * from a where rq =’’’||v_rq||’’’
And sjd = ’’8:10’’‘;
Open lcursor for sql;
End if ;
30.转换函数：
CAST（参数 AS CHAR(21)）
31.获取IP地址
select sys_context (‘userenv’,‘ip_address’) from dual
32.ORACLE输出换行：
dbms_output.put_line(‘雇员名：’ || v_name ||chr(10)|| ‘工资:’ || v_sal);
1.Oracle优化

select /+index(t,IDX_DG_HIS_BOND_DAY_YIELD)/
t.qxmc, t.YIELD, trunc(t.bzqx) as bzqx, t.work_time
from indics02.dg_his_bond_day_yield t
where qxmc like ‘%中债地方政府债收益率曲线%’
and qxfl = ‘0’
t是表别名，后面是索引名
2.v_record_number :=v_record_number+ sql%rowcount;
Sql%roucount用域记录修改的条数，必须放在更新或者删除等修改类语句后执行，不能在select中。
3. --异常处理
EXCEPTION
WHEN OTHERS THEN
v_err_msg := ‘系统错误：SQLCODE=’ || SQLCODE || ‘,SQLSTATE=’ ||
SQLERRM || ’ 数据日期:’ || v_etl_date;
BEGIN
ROLLBACK;
v_sqlerrm := SQLERRM;
INSERT INTO ETL_LOG_DETAIL_CDCTJ
VALUES
(v_etl_date,
v_start_time,
v_end_time,
v_proc_name,
v_soucetable,
v_desttable,
0,
v_status,
‘失败’,
v_sqlerrm,
v_err_sql,
ETL_LOG_DETAIL_CDCTJ_seq.Nextval,’’,’’,’’);
COMMIT;
END;

4.For x 遍历 ，把括号里的数据一条条遍历，在循环里加工
for x in (select distinct qxlbmc
from ADB_XXFW_QXLB_SGWH
where qxlbdm is null) loop
update ADB_XXFW_QXLB_SGWH
set qxlbdm =
(select max(qxlbdm) + 1 from ADB_XXFW_QXLB_SGWH)
where qxlbmc = x.qxlbmc;
commit;
end loop;

误删表恢复：
select * from recyclebin order by droptime desc;
flashback table 表名 to before drop;

过程临时表处理慢:
Call dbms_stats.unlock_schema_stats(‘ads’)–解锁收集统计信息，报错被锁定
Call dbms_stats.gather_table_stats(‘ads’,‘adb_dj_zq’)
dbms_stats.gather_table_stats(‘ads’,‘t_htbh’);
dbms_stats.gather_table_stats(‘ads’,‘t_xqht’);
dbms_stats.gather_table_stats(‘ads’,‘t_cxsgz’);
dbms_stats.gather_table_stats(‘ads’,‘t_zqgz’);