Oracle
序列
create table tb_goods
(
gid number(8,0),
gname varchar2(50) not null,
gprice number(8,2) not null,
gdesc varchar2(500)
);
alter table tb_goods add constraint
pk_goods_gid primary key (gid);
create sequence seq_goods
start with 10000000
increment by 10
cache 100;
drop sequence seq_test;
create sequence seq_test
start with 1
increment by 2
minvalue 1
maxvalue 10
cycle nocache;
select seq_goods.nextval from dual;
select seq_goods.currval from dual;
insert into tb_goods values (seq_goods.nextval, '农夫山泉500ml', 1.5, null);
同义词
select * from scott.emp;
create public synonym emp for scott.emp;
select * from emp;
视图
create view vw_emp as
select empno, ename, deptno from emp;
select * from vw_emp;
create or replace;
grant create view to scott;
revoke create view from scott;
索引
create index idx_goods_name on tb_goods (gname) cluster;
自然连接(通过外键关系进行连接)
select ename, dname from emp t1
natural join dept t2;
交叉连接(笛卡尔积)
select ename, dname from emp t1
cross join dept t2;
函数
create or replace function fn_jc(n number)
return number is
result number := 1;
i number := 1;
begin
for i in 1..n loop
result := result * i;
end loop;
return result;
end fn_jc;
select fn_jc(5) from dual;
create or replace function fn_addGoodsData
return number is
i number := 1;
begin
for i in 1..10000 loop
insert into tb_goods values
(seq_goods.nextval, '农夫山泉'||i, 1.5, null);
end loop;
end fn_addGoodsData;
存储过程
create or replace procedure sp_addGoodsData is
i number := 1;
begin
for i in 1..10000 loop
insert into tb_goods values
(seq_goods.nextval, '农夫山泉'||i, 1.5, null);
end loop;
commit;
end sp_addGoodsData;
begin
sp_addGoodsData();
end;
create or replace procedure sp_getAvgSalByDeptNo(
dno emp.deptno%type,
avgSal out emp.sal%type) is
begin
select avg(sal) into avgSal from emp where deptno=dno;
end sp_getAvgSalByDeptNo;
常用命令
sqlplus /nolog
shutdown immediate
desc dba_sys_privs
select * from user_sys_privs
set linesize 180
show linesize
show pagesize
select table_name from tabs;
show recyclebin;
flashback table emp to before drop;
drop table emp purge;
purge recyclebin;
grant create view to scott;
revoke create view from scott;
create user kygo identified by 123456 password expire;
grant create session to kygo;
grant unlimited tablespace to kygo;
grant create table to kygo;
create role normal_user;
grant create session to normal_user;
grant unlimited tablespace to normal_user;
grant create table to normal_user;
create user kygo indetified by 123456 password expire;
grant normal_user to kygo with admin option;
连接oracle数据库
例子1:连接数据库并用已经写好的sql语句执行
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
try (Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger")) {
CallableStatement cs = conn.prepareCall(
"{call sp_getAvgSalByDeptNo(?, ?)}");
cs.setInt(1, 10);
cs.registerOutParameter(2, Types.DOUBLE);
cs.execute();
System.out.println(cs.getDouble(2));
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
