Oracle_常用SQL

常用SQL[ORACLE]


1.常用系统函数

2.常用sql语句

3.一些定义和关键字

4.需要注意点

1.常用系统函数 ↑
--decode

decode(column,if_value,value,elseif_value,value,default_value);

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

好比一张表：

select decode(score,'A','优秀','B','良好','C','及格','不及格') from t_table
--sum

求和函数
--wm_concat

该函数可以把列值以","号分隔起来,并显示成一行

2.常用sql语句 ↑
--新建用户

create user zhaopei identified by zhaopei;

--给用户权限

grant dba to zhaopei ;

--删除表字段

alter table Tbl_Survey_Human_New DROP COLUMN 字段名;
alter table Tbl_Survey_Human_New DROP COLUMN n_oa_report_FLAG;
--新增表字段

alter table Tbl_Survey_Human_New add(字段名 字段类型);
alter table Tbl_Survey_Human_New add(n_protocaryon_report_FLAG varchar2(2));
--修改表字段

alter table Tbl_Survey_Human_New modify(字段名 字段类型);
alter table Tbl_Survey_Human_New modify(c_caseisreal_type varchar2(30));
--添加字段备注

comment on column 表名.字段名 is '注释';
comment on column Tbl_Report_Caseseat.BLOCKED is '派查勘员受阻';

--创建序列

create sequence mysq
minvalue 1 --最小值
maxvalue 99999999999999999 --最大值
start with 1 --开始值
increment by 1 --每次增长值
cache 20; --缓存大小
View Code
--Union 并集操作 .

对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
--Union All 并集操作

对两个结果集进行并集操作，包括重复行，不进行排序；
-- CASE WHEN 表达式

--简单Case函数

CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

--Case搜索函数

CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

 

--查询相同条件的其中一条数据

select * from tb k_id in (select min(k_id) from tb group by d_id)
--动态SQL.

--动态sql execute immediate
begin
execute immediate 'select * from scott.emp';
execute immediate 'create table emp1121(id number,name varchar2(10),salary number )';
end;
View Code
--异常处理1

1 declare
2 num1 number := 1;
3 num2 number := 0;
4 my_exce exception; --定义错误类型
5 begin
6 if num2 = 0 then
7 raise my_exce; --自定义错误
8 end if;
9 num1 := num1 / num2;
10 exception
11 when my_exce then --自定义错误处理
12 dbms_output.put_line(sqlcode || '===' || sqlerrm);
13 dbms_output.put_line('自定义 错误');
14 when others then
15 dbms_output.put_line(sqlcode || '===' || sqlerrm);
16 dbms_output.put_line('除零错误');
17 end;
View Code
--异常处理2

1 declare
2 num1 number := 1;
3 num2 number := 0;
4 begin
5 if num2 = 0 then
6 raise_application_error(-20991, '部门代码为空'); --自定义异常错误消息
7 --RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] );--keep_errors 为可选
8 --error_number 是从 –20,000 到 –20,999 之间的参数
9 --error_message 是相应的提示信息(< 2048 字节)
10 end if;
11 num1 := num1 / num2;
12 exception
13 when others then
14 dbms_output.put_line(sqlcode || '===' || sqlerrm);
15 dbms_output.put_line('除零错误');
16 end;
View Code

3.一些定义和关键字 ↑
--DDL和DML

DDL：如drop，alter，truncate。
DML：如insert，update，delete，merge。

--DECLARE

declare 声明变量
--DEFAULT

default 默认值

--数组类型.

1 DECLARE
2 --定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型
3 TYPE REG_VARRAY_TYPE IS VARRAY(5) OF VARCHAR(25);
4 --声明一个该VARRAY数据类型的变量
5 V_REG_VARRAY REG_VARRAY_TYPE;
6
7 BEGIN
8 --用构造函数语法赋予初值
9 v_reg_varray := reg_varray_type
10 ('中国', '美国', '英国', '日本', '法国');
11
12 DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_varray(1)||'、'
13 ||v_reg_varray(2)||'、'
14 ||v_reg_varray(3)||'、'
15 ||v_reg_varray(4));
16 DBMS_OUTPUT.PUT_LINE('赋予初值NULL的第5个成员的值：'||v_reg_varray(5)||' count:'||v_reg_varray.count);--count可查数组的长度
17 --用构造函数语法赋予初值后就可以这样对成员赋值
18 v_reg_varray(5) := '国';
19 DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_varray(5));
20 END;
View Code

--记录类型

1 declare
2 type test_type is record(
3 name varchar2(10) not null := 'zhangsan',
4 id varchar2(10));
5 test_v test_type;
6 begin
7 test_v.name := '李四';
8 test_v.id := '111';
9 dbms_output.put_line(test_v.name || ' ' || test_v.id);
10 end;
View Code
4.需要注意的 ↑
存储过程中is和as的区别

在存储过程（PROCEDURE）和函数(FUNCTION)中没有区别，在视图（VIEW）中只能用AS不能用IS，在游标(CURSOR)中只能用IS不能用AS。
赋值

str1 := 'str'; --数据库中的赋值 是 :=
比较

str1=str2 --数据库中的比较符 是单等号
LENGTH,LENGTHB,LENGTHC,LENGTH2,LENGTH4的区别

LENGTH(string1) 返回以字符为单位的长度.
LENGTHB(string1) 返回以字节为单位的长度.
LENGTHC(string1) 返回以Unicode完全字符为单位的长度.
LENGTH2(string1) 返回以UCS2代码点为单位的长度.
LENGTH4(string1) 返回以UCS4代码点为单位的长度.
View Code
oracle中 substrb() substrc() substr2() substr4()的区别

substr 按字符截取，单位字符
substrb 按字节，单位字节
substrc unicode字符为单位
substr2 ucs2代码点为单位
substr4 ucs4代码点为单位

substr是按字符来计算，一个字母或汉字都按一个字符计算如：
substr('智能ABC',2,2)='能A'
如果想要按字节来计算则可以采用substrb函数，用法一样
substrb('智能ABC',3,4)='能AB'

当然还有另外几个按不同编码计算的函数
substrc:按Unicode编码,
substr2:按UCS2编码,
substr4:按UCS4编码。
View Code