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

相关文章

HarmonyOS学习(七)——UI(五)常用布局总结

自适应布局 1.1、线性布局(LinearLayout) 通过线性容器Row和Column实现线性布局。Column容器内的子组件按照垂直方向排列,Row组件中的子组件按照水平方向排列。 属性说明space通过space参数设置主轴上子组件的间距,达到各子组件在排列上的等间距效果alignItems设置子组件在交叉轴上的对齐方式,且在各类尺寸屏幕上表现一致,其中交叉轴为垂直时,取值为Vert

JS常用组件收集

收集了一些平时遇到的前端比较优秀的组件,方便以后开发的时候查找!!! 函数工具: Lodash 页面固定: stickUp、jQuery.Pin 轮播: unslider、swiper 开关: switch 复选框: icheck 气泡: grumble 隐藏元素: Headroom

【C++】_list常用方法解析及模拟实现

相信自己的力量,只要对自己始终保持信心,尽自己最大努力去完成任何事,就算事情最终结果是失败了,努力了也不留遗憾。💓💓💓 目录   ✨说在前面 🍋知识点一:什么是list? •🌰1.list的定义 •🌰2.list的基本特性 •🌰3.常用接口介绍 🍋知识点二:list常用接口 •🌰1.默认成员函数 🔥构造函数(⭐) 🔥析构函数 •🌰2.list对象

常用的jdk下载地址

jdk下载地址 安装方式可以看之前的博客: mac安装jdk oracle 版本:https://www.oracle.com/java/technologies/downloads/ Eclipse Temurin版本:https://adoptium.net/zh-CN/temurin/releases/ 阿里版本: github:https://github.com/

30常用 Maven 命令

Maven 是一个强大的项目管理和构建工具,它广泛用于 Java 项目的依赖管理、构建流程和插件集成。Maven 的命令行工具提供了大量的命令来帮助开发人员管理项目的生命周期、依赖和插件。以下是 常用 Maven 命令的使用场景及其详细解释。 1. mvn clean 使用场景:清理项目的生成目录,通常用于删除项目中自动生成的文件(如 target/ 目录)。共性规律:清理操作

uva 11178 计算集合模板题

题意: 求三角形行三个角三等分点射线交出的内三角形坐标。 代码: #include <iostream>#include <cstdio>#include <cstdlib>#include <algorithm>#include <cstring>#include <cmath>#include <stack>#include <vector>#include <

019、JOptionPane类的常用静态方法详解

目录 JOptionPane类的常用静态方法详解 1. showInputDialog()方法 1.1基本用法 1.2带有默认值的输入框 1.3带有选项的输入对话框 1.4自定义图标的输入对话框 2. showConfirmDialog()方法 2.1基本用法 2.2自定义按钮和图标 2.3带有自定义组件的确认对话框 3. showMessageDialog()方法 3.1

工作常用指令与快捷键

Git提交代码 git fetch  git add .  git commit -m “desc”  git pull  git push Git查看当前分支 git symbolic-ref --short -q HEAD Git创建新的分支并切换 git checkout -b XXXXXXXXXXXXXX git push origin XXXXXXXXXXXXXX

Oracle type (自定义类型的使用)

oracle - type   type定义: oracle中自定义数据类型 oracle中有基本的数据类型,如number,varchar2,date,numeric,float....但有时候我们需要特殊的格式, 如将name定义为(firstname,lastname)的形式,我们想把这个作为一个表的一列看待,这时候就要我们自己定义一个数据类型 格式 :create or repla

ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

在win7 64位系统下安装oracle11g,在使用Database configuration Assistant创建数据库时,在创建到85%的时候报错,错误如下: 解决办法: 在listener.ora中增加对BlueAeri-PC或ip地址的侦听,具体步骤如下: 1.启动Net Manager,在“监听程序”--Listener下添加一个地址,主机名写计