Oracle常用常考集合

2024-09-03 05:38
文章标签 oracle 常用 集合 常考

本文主要是介绍Oracle常用常考集合,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

登陆远程服务器

sqlplus scott/tiger@192.168.2.1[:port]/sid [as sysdba]


简单查询

select  table_name from user_tables;
select * from tab;
select * from dept;

创建表
create table t_user(id int primary key,username varchar2(20),password varchar2(20));

创建序列(用户主键增长策略)
create sequence seq_user increment by 1 start with 1 nomaxvalue nocache;

查看序列
select *  from user_sequences;

建表空间 临时表空间 (默认表空间是user)
create tablespace test_ts datafile 'E:\oracledata\fcs.dbf' size 10M;
create temporary tablespace test_temp_ts tempfile 'E:\oracledata\fcs_temp.dbf' nsize 5M;

创建用户
create user fcs identified by 123 default tablespace test_ts temporary tablespace test_temp_ts;

给用户授权
grant create session,resource to fcs;

删除用户必须级联删除
drop user_name cascade;

每页显示50条记录
set line 100 pagesize 50;

对scott用户下的表恢复(查找脚本  复制语句  执行)
E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN

内连接、外连接,全连接
内 select * from student s ,class c where s.classid = c.id;
左 select * from student s left outer join class c on s.classid = c.id;
全 select * from student s full outer join class c on s.classid = c.id;

函数
字符函数 (to_char) ------- select tochar(sysdate,'yyyy-MM-dd') from dual;
数值函数 ----- 跟Java大同小异

获取当前登陆用户拥有的表
select table_name from user_tables;
select * from tab/tabs;

复制一张表
create table emp2 as select * from emp;

复制表结构而不要数据
create table emp3 as select empno,ename from emp where 1=0;

取一张表中的信息插入另一张表
insert into emp3 select  empno,ename from emp where job='MANAGER';N

标识列(自增长的列)
MYSQL-----create table demo(id int primary key auto_increment);

SQLServer----create table demo(id int primary key identity(1,1));

Oracle----1.创建序列  2.创建触发器
1.create sequence seq_emp increment by 1 start with 1 nomxavalue(mxavalue 99999)[nocache];

2.create or replace trigger tri_emp
   before insert on emp
   for each row
   begin
          select seq_emp.nextval into :new.empno from dual;
   end;
    /

执行脚本---@E:\fcs\oracle\tri_emp.sal;

插入时间字段和空字段
空字段  null或者不写
insert into emp(empno,job,hiredate) values(111,NULL,to_date('2011-1-2 09:10:11','yyy-MM-dd hh24:mi:ss'));
注意提交事物

更新
update emp3 set(empno,ename)=(select empno,ename from emp where empno =1121) where empno = 11;

删除(DML) or 还原 and  事物
 delete from emp3;/truncate table emp3;(不进回收站 直接删除)
 还原
 rollback;

savepoint t;
xxxxxxxxxxx
rollback to t;(只能回退xxx的操作)

set autocommit on/off;

表的锁定
*隐式锁 update(一边未提交另一边无法再更新) delete(insert不会) 快速提交
锁定表 lock table manager2 in exclusive mode;
释放锁 commit/rollback;

表的操作
创建表  create table tablename..
查看表  decs tablename;
*修改表的结构  alter table test add age int [default 123]; alter table test drop column age;
  修改字段大小  alter table test modify addr varcahr2(50);
  表重命名 rename test to t_test;  

完整性 数据完整性  参照完整性
约束:
(非空约束) not null
(默认约束) default
检查约束   check
唯一约束   unique
主键约束   primary key
外检约束   foreign key

补充:char和varchar的区别 可变/不可变长  时间(不需计算空间) 空间

alter table table_name modify stuaddress default '地址不详';

alter table table_name modify stuaddress not null;

alter table table_name add constraint uq_stuID unique(stuID);

alter table table_name add constraint ck_stuAge check(stuAge between 10 and 30);

alter table table_name add constraint pk_stuNo primary key(stuNo);

alter table stumark add contraints fk_stuno foregin key(stuNo) reference stuInfo(stuNo)[on delete cascade][on delete set null];

drop table stuInfo; 无法删除  因为有其他表参照它
-----》drop table stuInfo cascade constraints;


###################################### oracle特性----pl/sql  #####################################

进行DML操作    用来编写过程 函数 包及数据库触发器  过程和函数也称为子程序
一个块包括三个部分  每个部分由一个关键字标识

一个简单的字符串输出

set serveroutput on
begin
dbms_output.put_line('hello');
end;
/

变量

*普通变量      *列变量    全局变量    *记录变量

1.普通变量

set serveroutput on
declare
 greeting varchar2(20):='fcs';
begin
 greeting:='google';
dbms_output.put_line('greeting:'||greeting);
end;
/

2.列变量

set serveroutput on
declare
 greeting varchar2(20):='fcs';
 myname class.name%type;
begin
 greeting:='google';
 dbms_output.put_line('greeting:'||greeting);
 select name into myname from class where id=1;
  dbms_output.put_line('name:'||myname);
end;
/

3.行变量  记录变量

set serveroutput on
declare
 greeting varchar2(20):='fcs';
 myname class.name%type;
 myrow class%rowtype;
begin
 greeting:='google';
 dbms_output.put_line('greeting:'||greeting);
 select * into myrow from class where id=1;
  dbms_output.put_line('name:'||myrow.name||' classid:'||myrow.id);
end;
/

流程控制

1).选择判断

declare
  score number(3):=56;
begin
 if score>=90 and score<=100 then
    dbms_output.put_line('您的成绩为:优');
 else
    dbms_output.put_line('您的成绩为:差');
 end if;
end;
/

declare
  score number(3):=85;
begin
 if score>=90 and score<=100 then
    dbms_output.put_line('您的成绩为:优');
 elsif score>=80 and score<90 then
    dbms_output.put_line('您的成绩为:良');
 else
    dbms_output.put_line('您的成绩为:差');
 end if;
end;
/

2).多路分支(case-when-then-else-end)

1-基本结构
declare
  score number(3):=65;
  degree number(1):=0;
begin
 if
   score>=90 and score<=100 then
     degree:=1;
 elsif score>=80 and score<90 then
     degree:=2;
 else
     degree:=3;
 end if;
 case degree
      when 1 then dbms_output.put_line('您的成绩为:优');
      when 2 then dbms_output.put_line('您的成绩为:良');
      else dbms_output.put_line('您的成绩为:差');
 end case;
end;
/

2-表达式结构
declare
  score number(3):=65;
  degree number(1):=0;
  level char(2);
begin
 if
   score>=90 and score<=100 then
     degree:=1;
 elsif score>=80 and score<90 then
     degree:=2;
 else
     degree:=3;
 end if;
 level:=case degree
      when 1 then '优'
      when 2 then '良'
      else '差'
      end;
  dbms_output.put_line('您的成绩为:'||level);
end;
/

3-搜索结构(case后面没有变量 when中可以写表达式)
declare
  score number(3):=77;
begin
 case when  score>=90 and score<=100 then
       dbms_output.put_line('您的成绩为:优');
      when score>=80 and score<90 then
       dbms_output.put_line('您的成绩为:良');
      else
       dbms_output.put_line('您的成绩为:差');
 end case;
end;
/

3).循环

set serveroutput on
declare
 i number(5):=1;
 v_sum number(5):=0;
begin
  loop
     v_sum:=v_sum+i;
     i:=i+1;
     exit when i>=10;
  end loop;
  dbms_output.put_line('sum:'||v_sum);
end;
---while---
declare
 i number(5):=1;
 v_sum number(5):=0;
begin
  while i<10
  loop
     i:=i+1;
     v_sum:=v_sum+i;
  end loop;
  dbms_output.put_line('sum:'||v_sum);
end;
/
----for-----
declare
 v_sum number(5):=0;
begin
  for i in 1..10
  loop
     v_sum:=v_sum+i;
  end loop;
  dbms_output.put_line('sum:'||v_sum);
end;
/

游标(用于临时存储):显式/隐式

select into...

1.隐式游标

begin
 update class set name='wpj1403' where id=2;
 if SQL%ISOPEN then
    dbms_output.put_line('ok');
 end if;
end;
/

2.显示游标

声明游标--->打开游标--->提取游标--->关闭游标
---标准写法---
declare
 cursor cur_f is select * from class where id=1;
 v_record class%rowtype;
begin
 open cur_f;
 fetch cur_f into v_record;
 dbms_output.put_line('id='||v_record.id||',name:'||v_record.name);
 close cur_f;
end;
/
---简单写法---
declare
 cursor cur_f is select * from class;
begin
 for v_record in cur_f
 loop
    dbms_output.put_line('id='||v_record.id||',name:'||v_record.name);
 end loop;
end;
/

begin
 for v_record in (select * from class)
 loop
    dbms_output.put_line('id='||v_record.id||',name:'||v_record.name);
 end loop;
end;
/

存储过程

存储过程和函数都是以命名的数据库对象形式存储在数据库当中。
只有被授权的用户或者创建者本身才能执行存储过程或调用函数。

一个重复使用的功能 可以设计成为存储过程:一个经常调用的计算,可以设计为存储函数;

存储过程的返回值必须通过参数带回

create[or replace] procedure


创建一个显示雇员总人数的存储过程
create or replace procedure my_pro
is
v_num number;
begin
  select count(*) into v_num from scott.emp;
  dbms_output.put_line(v_num);
end;
/

调用
1).execute my_pro;

2).begin
 scott.my_pro;
end;
/

删除
drop procedure my_pro;

编写显示雇员信息的存储过程emp_list 并引用emp_count存储过程
create or replace procedure emp_count
is
v_num number;
begin
  select count(*) into v_num from scott.emp;
  dbms_output.put_line(v_num);
end;
/

create or replace procedure emp_list
is
cursor emp_cur is select * from emp;
begin
 for emp_rec in emp_cur
 loop
   dbms_output.put_line(emp_rec.ename||' '||emp_rec.sal);
 end loop;
end;
/

参数传递
in   输入参数变量  用于传递参数给存储过程
out     出             从存储过程获取数据
inout  定义一个输入.输出参数变量

create or replace procedure change_salary(pno in number,psal in number)
as
begin
 update emp set sal=sal+psal where empno=pno;
 if SQL%FOUND then
 dbms_output.put_line('salary update success!');
 commit;
end if;
exception
 when others then
 dbms_output.put_line('salary update failure!');
 rollback;
end;
/

execute change_salary(7788,500);

********************** Oracle 常考 ********************

如何输出前10条记录
select * from emp where rownum<=10;

输出第10到20条的数据
select * from (select e.*,rownum r from emp e) where r>=10 and r<=20;
注:这条语句即是输出第10到第20条纪录,这里之所以用rownum rn,是把rownum转成实例,因为rownum本身只能用 <=的比较方式,只有转成实列,这样就可做 >=的比较了。


按工资和工作月份的乘积排序(按计算结果排序)
select ename, sal*months_between(sysdate,hiredate) "total" from emp order by "total";

统计各部门的最高工资,排除最高工资小于3000的部门。(having 后不可用别名)
 select deptno,max(sal) from emp group by deptno having max(sal)>3000;
 select dname ,max(sal) from emp e,dept d where e.deptno=d.deptno group by dname having max(sal)>3000;

这篇关于Oracle常用常考集合的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1132176

相关文章

Java中的数组与集合基本用法详解

《Java中的数组与集合基本用法详解》本文介绍了Java数组和集合框架的基础知识,数组部分涵盖了一维、二维及多维数组的声明、初始化、访问与遍历方法,以及Arrays类的常用操作,对Java数组与集合相... 目录一、Java数组基础1.1 数组结构概述1.2 一维数组1.2.1 声明与初始化1.2.2 访问

Spring Boot中WebSocket常用使用方法详解

《SpringBoot中WebSocket常用使用方法详解》本文从WebSocket的基础概念出发,详细介绍了SpringBoot集成WebSocket的步骤,并重点讲解了常用的使用方法,包括简单消... 目录一、WebSocket基础概念1.1 什么是WebSocket1.2 WebSocket与HTTP

golang中reflect包的常用方法

《golang中reflect包的常用方法》Go反射reflect包提供类型和值方法,用于获取类型信息、访问字段、调用方法等,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值... 目录reflect包方法总结类型 (Type) 方法值 (Value) 方法reflect包方法总结

C# 比较两个list 之间元素差异的常用方法

《C#比较两个list之间元素差异的常用方法》:本文主要介绍C#比较两个list之间元素差异,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录1. 使用Except方法2. 使用Except的逆操作3. 使用LINQ的Join,GroupJoin

关于集合与数组转换实现方法

《关于集合与数组转换实现方法》:本文主要介绍关于集合与数组转换实现方法,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、Arrays.asList()1.1、方法作用1.2、内部实现1.3、修改元素的影响1.4、注意事项2、list.toArray()2.1、方

python常用的正则表达式及作用

《python常用的正则表达式及作用》正则表达式是处理字符串的强大工具,Python通过re模块提供正则表达式支持,本文给大家介绍python常用的正则表达式及作用详解,感兴趣的朋友跟随小编一起看看吧... 目录python常用正则表达式及作用基本匹配模式常用正则表达式示例常用量词边界匹配分组和捕获常用re

Oracle 数据库数据操作如何精通 INSERT, UPDATE, DELETE

《Oracle数据库数据操作如何精通INSERT,UPDATE,DELETE》在Oracle数据库中,对表内数据进行增加、修改和删除操作是通过数据操作语言来完成的,下面给大家介绍Oracle数... 目录思维导图一、插入数据 (INSERT)1.1 插入单行数据,指定所有列的值语法:1.2 插入单行数据,指

gitlab安装及邮箱配置和常用使用方式

《gitlab安装及邮箱配置和常用使用方式》:本文主要介绍gitlab安装及邮箱配置和常用使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1.安装GitLab2.配置GitLab邮件服务3.GitLab的账号注册邮箱验证及其分组4.gitlab分支和标签的

Python常用命令提示符使用方法详解

《Python常用命令提示符使用方法详解》在学习python的过程中,我们需要用到命令提示符(CMD)进行环境的配置,:本文主要介绍Python常用命令提示符使用方法的相关资料,文中通过代码介绍的... 目录一、python环境基础命令【Windows】1、检查Python是否安装2、 查看Python的安

python判断文件是否存在常用的几种方式

《python判断文件是否存在常用的几种方式》在Python中我们在读写文件之前,首先要做的事情就是判断文件是否存在,否则很容易发生错误的情况,:本文主要介绍python判断文件是否存在常用的几种... 目录1. 使用 os.path.exists()2. 使用 os.path.isfile()3. 使用