常用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 mysqminvalue 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搜索函数  CASEWHEN 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 immediatebegin  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 exception11   when my_exce then    --自定义错误处理12     dbms_output.put_line(sqlcode || '===' || sqlerrm);13     dbms_output.put_line('自定义 错误');14   when others then15     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 exception13   when others then14     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_type10          ('中国', '美国', '英国', '日本', '法国');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