Oracle开发学习笔记

1.创建用户（一个账户相当于一个数据库）

1.使用SQL Plus最大权限用户登录(system/sys)
（1）创建表空间：

create tablespace mjjtablespace
datafile 'c:\mjjtablespace.dbf'
size 100m --最大空间
autoextend on --可动态增加
next 10m --每次增加10m

(2)创建用户

create user mjj 
identified by mjj -- 密码
default tablespace mjjtablespace

(3)设置权限

grant dba to mjj; --直接给很大权限

整个项目的结构如下：

2.创建表

用你创建的角色登录
1.数据类型

char
varchar2
long --文本类型
number --number(5),number(5,2) 小数
clob --超大文本 4GB
date

2.创建表语句

create table sys_info
(
       id number primary key,
       s_num number,
       s_desc varchar2(30),
       tab varchar2(30)
)

3.表的增删改
（1）增加一列：

alter table sys_info add
(
      sys_sign varchar2(30)
)

（2）修改字段

alter table sys_info  modify
(
      sys_sign varchar2(20)
)
alter table sys_info add(
      sys_date date default sysdate --时间以及默认值设置
)

（3）修改字段名

alter table sys_info rename column tab to sys_tab

（4）删除字段

alter table sys_info drop column sys_sign

（5）删除表

drop table sys_info

3.数据操作
（1）数据增加
发现一个问题，Oracle不能实现id自增,需要创建序列和一个触发器
自增序列（两属性：currval，nextval）：

create sequence sys_id
start with 1
increment by 1
nomaxvalue;

触发器：

create or replace trigger trg_sys_id
before insert on sys_info
for each row
when(new.id is null)
begin
  select sys_id.nextval
  into :new.id 
  from dual ;
end;--注意要有分号

如果触发器图标上出现❌，则需要调试寻找问题，打开command窗口输入如下语句

 alter trigger trg_sys_id compile;

增加语句如下

insert into sys_info(s_num,s_desc,s_tab) values(1,'初始测试','A');
insert into sys_info(s_num,s_desc,s_tab) values(2,'第二次测试','B');

（2）操作
可以将表托至窗口，就有各种选项：

例子：

update sys_info set s_num = s_num + 1;

3.自定义函数

例子：

create or replace function str_sub_to_num
(
 str in varchar2,--入参用in
 begin_char in char,
 end_char in char
 --截取数字字符串转化为数字
 --参数：
 --       str:被处理字符
 --       begin_char:数字前的字符
 --       end_char:数字后的字符
) return number is
begin
  if begin_char = end_char then  --首尾字符相同时
     --instr用法1：instr(被处理字符串,目标字符,检索起始位置,第n次出现的位置)
     return to_number(substr(str,instr(str,begin_char) + 1,instr(str,end_char,1,2)-instr(str,begin_char) - 1));
  else
    --instr用法2：instr(被处理字符串,目标字符)
     return to_number(substr(str,instr(str,begin_char) + 1,instr(str,end_char)-instr(str,begin_char) - 1));
  end if;
Exception 
  --被截取的不是数字，或截取失败，处理异常
  When Others Then 
    Null; 
    Return 0; 
end str_sub_to_num;

虚表（dual）测试该方法:

select str_sub_to_num('The Pi is ''3.1415926''','''','''')||'' from dual ;
--将数字转为字符串：|| 符号连接字符串（此处连接空字符）
--‘ ' ’符号的转义：使用两次该符号可被认为一个该符号
--数字在显示时在靠右边，字符显示时在靠左边

函数里查询赋值的语句：

CREATE OR REPLACE FUNCTION f_人员计费 (
       学生_id IN NUMBER，
       tab in NUMBER
)RETURN NUMBER IS
    a NUMBER; --声明个人总成绩 如果为字符串类型需要长度限制：varchar2(30)
    b NUMBER; --全班成绩
BEGIN
  --该人的成绩赋值给a;
  select sum(成绩) into a from 学生成绩表 where 学生id= 学生_id group by 学生id;
  if tab = 1 then --类型1 返回该人的总成绩
    RETURN a;
  elsif tab = 2 then --类型2 返回该的平均成绩 --注意elseif是一个词
    select avg(成绩) into b from 学生成绩表;
    RETURN round(b,2); -- 保留两位小数
  end if;
END;--保留‘;’符号是个好习惯

4.存储过程

例子：使用存储过程新增数据（id之前有触发器补充了，创建时间是默认值，都可不用入参）

create or replace procedure p_创建商品(
       商品价格 in NUMBER,
       商品名称 in varchar,
       备注 in varchar
) is
begin
  insert into sys_info
    ( s_num, s_desc, s_tab)
  values
    ( 商品价格, 商品名称, 备注);
end p_创建商品;--注意需要';'符号

可以在sql command窗口测试：

begin
  p_创建商品(55,'一箱营养快线','普通超市购买的');
end;--注意需要';'符号

5.常用语句方法

• sys_guid() 获取唯一标识符（很大数据范围，大概率不重复，单安全性高）
• listagg() :行转列，可加要求进行排序，例如上述的截取数字方法。
• decode(str,a,‘a’,b,‘b’)：如果str=a,返回‘a’，还可以继续加匹配值。
• row_number()：分组排序并给出序号列
• to_char(3.1415926,‘9999999999.99’) 保留两位小数

例子：注意这里是起了别名，例如给最里层结果集起了别名 a

select a.*,row_number() over(partition by 名称 order by 价格) as rn from
  (select s_desc 名称, s_num 价格, s_tab||'('||s_num||')' 品牌价 from sys_info) a

order by 默认升序，DESC为降序
再根据上述结果集做行转列，再做成一个视图（就是代码第一句）：

create or replace view v_商品各类价格情况 as
select sys_guid() id,
       decode(名称,'一箱营养快线', '一件牛奶',名称,名称) 名称,
       listagg(品牌价,',') within group(order by str_sub_to_num(品牌价,'(',')') DESC) 品牌列表 
from
   ( select a.*,row_number() over(partition by 名称 order by 价格) as rn 
     from(
            select s_desc 名称, s_num 价格, s_tab||'('||s_num||')' 品牌价 
            from sys_info
       ) a
    ) b
group by 名称

6.PLSQL使用小技巧

• F8快捷运行
• F6为智能提示
• F5查看代码优化计划（查表时可以加个between and条件语句去尽量避免TABLE ACCESS FULL，因为处理亿万级数据时会很卡）
• 在PLSQL左下角有源SQL语句
• ctrl+ ‘-’ / ‘+’ 改变字号
• 自定义快捷键：
  
• 自定义自动补充，设置完了要一路确认过去才能其效果，尽量别用一个字符去补充（亲试，敲代码会多出不必要的麻烦）
  
• 初下载，需要OCI
  
• 乱码‘???',改字符集：
  点击查看

7.今日技巧：

（1）取OJDBC文件
首先找到本地oracle安装路劲：

• win + R输入 regedit 打开注册表
• 导航到路劲：HKEY_LOCAL_MACHINE\SOFTWARE\Oracle
• 查找OracleHomeKey或OracleHomeDir值，它们分别表示Oracle安装的根键和目录路径。
  然后根据路劲找到ojdbc.jar文件：product\版本号\db1\jdbc\lib

（2）远程连接
cmd输入lsnrctl status可查询所有运行程序的端口号（一般1521）
sqlplus远程连接语句为 username/password@//hostname:port/service_name（一般全局实例名为orcl）