标题 Oracle数据库常用基本SQL语法
1.表的创建、插入，删除及Oracle和mysql的数据类型的区别
create table item
(
item_id number(5) primary key not null,
item_name nvarchar2(20),
item_price number(3,2),
item_date date
);
-- mysql 获取当前系统时间 select now()
-- oracle 获取当前系统时间 sysdate
insert into item values(3,'苹果手',1.10,sysdate); --此时插入的数据是临时的，需要手动提交
commit; --Oracle中 增删改操作 都需要 手动提交
select * from item
-- oracle 中临时表(伪表) dual,此处使用是为了平衡select语法的使用
select sysdate from dual;
drop table item
select userenv('language') from dual;
select userenv('language') from dual; -- 查询Oracle服务端的字符编码 AMERICAN_AMERICA.AL32UTF8
select * from v$nls_parameters a where a.PARAMETER = 'NLS_CHARACTERSET'; --查询Oracle客户端的字符编码
select * from V$NLS_PARAMETERS
2.Oracle相关SQL
Oracle中SQL语句不区分大小写，但是推荐使用大写
主键的自动增长策略设置–》序列操作，
创建序列：
create sequence seq_01;
查询序列必须和查询当前时间一样从临时表dual查询
同时必须先查nextval（下一个值），再查currval（当前值），否则会报错，因为还没有被初始化为1，nextval每调用一次就会初始化一个值，即增加一个值，例如：1,2,3,4、、、、
select seq_01.nextval from dual;
select seq_01.currval from dual;
删除序列
drop sequence 序列名;
drop sequence seq_01;
Oracle中独特的序列自增设置，和mysql不同的是，Oracle可以自定义自增的开始位置及自增大小
create sequence seq_02 --创建序列
start with 2 --设置自增开始位置
increment by 1; --设置每次自增大小
select seq_02.nextval from dual; --查询下一个值，结果为2
select seq_02.currval from dual; --查询当前值，也是2
插入表操作增加序列的主键自增策略
select *from item2; --先查询一下表数据
select seq_01.currval from dual; --查询当前序列的值
insert into item2 values(seq_01.nextval,'华为手机',12.30,sysdate,'厉害了我的歌')
为了避免使用序列自增策略时出现主键冲突的问题，最好先查询表和序列当前值
2.表空间操作
1.三部曲–》
1.1创建表空间 ，可以指定创建表空间后的存储路径，设置初始化大小以及每次递增的大小
create tablespace orcl_test --orcl是Oracle数据库的全局表空间，这里表示自己创建的表空间测试
datafile 'E:\typore\OracleFile\orcl_test.DBF' --数据文件的存储路径，文件扩展名为DBF
size 10M --初始化容量
autoextend on --自动增长
1.2.创建用户
create user mike identified by mike --创建用户并设置密码
default tablespace orcl_test --设置默认的表空间为前面创建的
1.3给用户授权
select * from role_sys_privs; --查询所有的角色所拥有的的权限
select * from role_sys_privs where role='CONNECT'; --可以登陆的权限
select * from role_sys_privs where role='RESOURCE'; --可以创建表、序列、触发器、的权限
--授权 grant 、、、to
grant connect,resource to mike; --登陆会话试一下是否成功
--撤销权限
revoke connect,resource to mike; --登陆system或者sys用户操作
用当前Mike用户使用resource权限来创建表测试创建是否成功
create table product
(
pid number(5) primary key not null,
pname nvarchar2(10) not null
);
select * from product;
insert into product values(1,'会话');
3.Oracle的基本函数
select * from item;
select * from item2 order by item_id;
--to_date()函数，把字符串转化为相应的时间格式类型
select to_date('2020-10-31','yyyy-mm-dd') from dual;
insert into item2 values(3,'苹果',30.30,to_date('2020-10-31','yyyy-mm-dd'),'哎');
commit;
--to_char()函数，将任意类型转化为字符串，能转就转，不能转就抛异常
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --mi为分
--to_number()函数，将字符串转化为数字，能转就转，不能转就报错
select to_number('1234') from dual; --成功
select to_number('1234xx') from dual; --报错
4.分页关键字rownum(行号)
在mysql中使用limit 0,1关键字
在Oracle中使用rownum关键字
--rownum特点
--查前五条数据
select rownum,emp.* from emp where rownum<=5; --有数据，正常显示
--查后面五条
select rownum,emp.* from emp where rownum>5; --没有数据
--rownum特点：根据rownum的条件判断，把符合条件的一行数据显示出来，不符合的就把一整行删除，并且rownum当前值就为最小值（1），所以就会没有任何数据显示
--使用子查询查后面五条
select a.* from
(select rownum r,emp.* from emp where rownum<=10) a where r>5;
--分页公式 pageNum,pageSize
--第一页：pageNum=1,pageSize=5
--第二页：pageNum=2,pageSize=5
--所以根据下面这个语句得出分页公式
select a.* from
(select rownum r,emp.* from emp where rownum<=10) a where r>5;
--分页公式
select a.* from
(select rownum r,emp.* from emp where rownum<=pageNum*pageSize) a where r>(pageNum-1)*pageSize;
--用第三页测试一下公式
select a.* from
(select rownum r,emp.* from emp where rownum<=3*5) a where r>(3-1)*5; --有结果，符合预期，公式成立
5.plsql（if/else/for、、、）
declare关键字，作用是定义变量
下面是Oracle的plsql标准结构，即
定义变量
多个变量
开始
逻辑代码(if、else、then、switch等等)、输出语句
结束
declare
--定义变量 变量名称 变量类型 intger 默认值
age integer:=100; --必须要有冒号，否则会报错
begin --开始
dbms_output.put_line(age); --输出语句，在开始和结束之间写逻辑代码
end; --结束
declare
--定义变量 变量名称 变量类型 intger 默认值
age integer; --必须要有冒号，否则会报错
begin --开始
age:=100; --变量可以先定义，再赋值
dbms_output.put_line(age); --输出语句，在开始和结束之间写逻辑代码
end; --结束
if 语句结构：
if then
end if;
必须要有分号，又开有合，表示结束
declare
n integer;
begin
n:=100;
if n>20 then
dbms_output.put_line('成年了'||n); --双竖线表示字符串与变量拼接在一起，相当于java中的加（+）号
end if;
end;
if多条件分支
declare
score integer;
begin
score:=79;
if score>=90 then
dbms_output.put_line('优秀成绩'||score);
elsif score>=80 then --if分支关键字elsif
dbms_output.put_line('良好成绩'||score);
elsif score>=70 then
dbms_output.put_line('一般成绩'||score);
else --最后为else
dbms_output.put_line('差成绩'||score);
end if;
end;
case关键字，相当于java中的switch case
declare
grade nvarchar2(4);
begin
grade:='A';
case grade
when 'A' then dbms_output.put_line('优秀'||grade);
when 'B' then dbms_output.put_line('良好'||grade);
when 'C' then dbms_output.put_line('一般'||grade);
else
dbms_output.put_line('差'||grade);
end case; --也要结束
end;
loop循环关键字
declare
i integer:=1;
begin
loop
if i>100 then
exit;
end if;
--i:=i+1;
dbms_output.put_line('第'||i||'次输出了、、、');
i:=i+1;
end loop;
end;
while循环关键字
--100以内的偶数和
declare
i integer:=1;
total integer:=0;
begin
while i<=100 loop
if i mod 2=0 then --取模为mod关键字
total:=total+i;
end if;
i:=i+1;
end loop;
dbms_output.put_line('100以内的偶数和='||total);
end;
for循环关键字，用法和while类似，也需要loop
--循环打印1-100
declare
begin
for i in 1..100 loop
dbms_output.put_line(i);
end loop;
end;
实例：
declare
v_name nvarchar2(10);
begin
select item_name into v_name from item2 where item_id=1; --相当于从表中根据条件查出结果赋给自定义的变量
dbms_output.put_line('从表中查出的结果为：'||v_name);
end;
declare
n_count integer;
begin
select to_char(sysdate,'D') into n_count from dual;
case n_count
when 1 then dbms_output.put_line('星期天');
when 2 then dbms_output.put_line('星期一');
when 3 then dbms_output.put_line('星期二');
when 4 then dbms_output.put_line('星期三');
when 5 then dbms_output.put_line('星期四');
when 6 then dbms_output.put_line('星期五');
when 7 then dbms_output.put_line('星期六');
else
dbms_output.put_line('未知错误');
end case;
end;
select into语法使用时，如果结果集返回了多条数据，但是目的只需要一跳数据，就会触发多数据异常（too_many_rows exception）；如果返回的结果集为空（预期返回值为空），就会触发数据找不到异常（not_data_found exception）
6、Oracled的异常拦截处理:
declare
v_name nvarchar2(10);
begin
--select item_name into v_name from item2 where item_id=100; 数据找不到异常
select item_name into v_name from item2; --数据返回过多错误
dbms_output.put_line('从表中查出的结果为：'||v_name);
exception
when too_many_rows then dbms_output.put_line('too_many_rows 异常拦截!!!'); --返回数据过多异常
when no_data_found then dbms_output.put_line('no_data_found 异常拦截!!!'); --数据没找到异常
end;
-- execption
-- when 状态码，英文大小写都可以 then
-- 异常处理方式
7、Oracle中触发器（trigger）
触发器是可以自动进行触发的
在Oracle中触发器 ，只能是在添加、删除、修改的时候触发
比如修改或者添加一条数据中的年龄（age）的时候，年龄小于0或者过大就会触发
--触发器的基本使用
--1.创建触发器
create trigger t1 --创建
after --之后
insert on item2 --在插入表时
declare
begin
dbms_output.put_line('往item2表添加的时候触发了我、、、'); --打印
end;
--创建一个当在item2表中添加数据的时候就会触发这个t1（触发器）,就会打印这句话
select * from item2;
delete from item2 where item_id=5;
--2、设置对应的触发条件去触发
insert into item2 values(5,'OPPOs',30.30,to_date('2020-11-02','yyyy-mm-dd'),'good');
commit; --提交插入语句之后，就会触发触发器，在输出窗口打印触发器里的话
也可以创建关于删除和修改时的触发器
8、java代码操作Oracle
a、驱动类（jar包）
mysql中：mysql-conntor5.13.jar
Oracle中：从Oracle安装文件里面找到： E:\Oracle\OracleFile\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar
使用最新jar包
b、连接的URL字符串需要变化
mysql：jdbc:mysql://localhost:3306/ssm
账号：root
密码：1234
Oracle：jdbc:oracle:thin:@localhost:1521:orcl
账号：sys system scott mike
密码：123456 123456 tigger mike
8.1. jdbc连接Oracle
mysql驱动路径：com.mysql.jdbc.Driver
Oracle驱动路径：oracle.jdbc.driver.OracleDriver
jdbc连接Oracle数据库相关java代码：
package com.yao.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 连接Oracle数据库工具类
*/
public class BaseDao {
static {
//注册驱动
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
//使用全局数据库orcl
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String username = "scott";
String password = "tigger";
Connection connection = DriverManager.getConnection(url, username, password);
return connection;
}
}
8.2. ssm中小案例使用Oracle（框架连接Oracle）
扫一扫
510
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
