95992828九五至尊2

常用函数对比,函数备忘记录882828九五至尊手机版

三月 9th, 2019  |  882828九五至尊手机版

正文转自:http://www.cnblogs.com/neru/archive/2011/08/18/2144049.html

Listagg

select
* from
emp

882828九五至尊手机版 1

select
LISTAGG(ename,’-‘) within group (order by deptno desc) from emp;

882828九五至尊手机版 2

能够见见效用类似wm_concat,可以自定义连接符,差别:

LISTAGG 
:
11g2才提供的函数,不帮助distinct,拼接长度不可能抢先五千,函数再次来到为varchar2类型,最大尺寸为陆仟.

和wm_concat相比较,listagg能够推行排序。例如
select deptno, listagg(ename,’;’) within group(order by ename) enames from emp group by deptno;

就好像的语法还有:

 

–总计数值一千在笔录集中的排序值。
select rank(1000) within group(order by sal),dense_rank(1000) within
group(order by sal) from emp;

 cume_dist()、PERCENT_RANK

cume_dist:计算结果为相对地点/总行数。再次回到值(0,1]。

percent_rank:计算方法为 (绝对地点-1)/(总行数-1),

select cume_dist(20, 4000) within group(order by deptno, sal)
cume_dist
from emp;

先前平素用sqlserver,唯有很少的一点oracle的阅历,将来要用oracle做叁个很复杂的蕴藏进度,感觉好别扭~

with table as

 SQL
Code 

with temp as(

select 500 population, ‘China’ nation ,’Guangzhou’ city from dual union all

select 1500 population, ‘China’ nation ,’Shanghai’ city from dual union all

select 500 population, ‘China’ nation ,’Beijing’ city from dual union all

select 1000 population, ‘USA’ nation ,’New York’ city from dual union all

select 500 population, ‘USA’ nation ,’Bostom’ city from dual union all

select 500 population, ‘Japan’ nation ,’Tokyo’ city from dual

)

select population,

nation,

city,

listagg(city,’,’) within GROUP (order by city) over (partition by nation) rank

from temp

With table as
类似创设贰个一时表,只好够查询1遍,之后就被灭绝,同时能够创立八个权且table,比如:

with sql1 as

(select to_char(a) s_name from test_tempa),

sql2
as

(select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum = 1))

select
* from sql1 union all select * from
sql2

缘何就无法用sqlserver呢。。。

pivot unpivot

队列转换,见

Oracle行转列、列转行的Sql语句总结

 

Minus

SQL中有多少个MINUS关键字,它利用在七个SQL语句上,它先找出第叁条SQL语句所产生的结果,然后看这一个结果有没有在其次个SQL语句的结果中。假若有的话,那这一笔记录就被删除,而不会在最终的结果中冒出。假如首个SQL语句所爆发的结果并从未存在于第一个SQL语句所产生的结果内,那那笔资料就被舍弃,其语法如下:
  [SQL Segment 1]
  MINUS


  [SQL Segment 2]

–创建表**1

create table test1

(

name varchar(10),

sex varchar(10),

);

               

insert into test1 values(‘test’,’female’);

insert into test1 values(‘test1′,’female’);

insert into test1 values(‘test1′,’female’);

insert into test1 values(‘test11′,’female’);

insert into test1 values(‘test111′,’female’);

               

–创建表**2

create table test2

(

name varchar(10),

sex varchar(10),

);

insert into test1 values(‘test’,’female’);

insert into test1 values(‘test2′,’female’);

insert into test1 values(‘test2′,’female’);

insert into test1 values(‘test22′,’female’);

insert
into test1 values(‘test222′,’female’);


select
* from test1 minus select * from test2;

结果:

NAME      
SEX            
———- ———- 
test1      female             
test11     female  
test111    female 


select
* from test2 minus select * from test1;

结果:

NAME      
SEX            
———- ———- 
test2      female             
test22     female  
test222    female

敲定:Minus重临的连日左侧表中的多少,它回到的是差集。注意:minus有剃重成效

==========================================================
 上面是本人做的试行,很显明能够看到MINUS的频率,made_order共23万笔记录,charge_detail共17万笔记录

质量相比较:

SELECT order_id FROM made_order

MINUS

SELECT
order_id FROM
charge_detail

1.14
sec

select a.order_id

from made_order a

where not exists

(select order_id from charge_detail where order_id = a.order_id)

18.19
sec

select order_id

from made_order

where order_id not in (select order_id from
charge_detail)

20.05
sec

 

nullslast(first)

排序,遇空排在前(后)

select
* from emp order by comm desc nulls last

882828九五至尊手机版 3

SQL server
与Oracle开发比较

Over

select
ename,max(sal) over() from emp;

882828九五至尊手机版 4

  本文书档案首要从oracle与sql server语法上拓展差别性相比,诸如两者在保管,质量,优化等地点的距离不作比较。

rownum和row_number()

row_number()
over (partition by col1 order by
col2)表示依照col1分组,在分组内部根据col2排序,而此函数总结的值就象征每组内部排序后的次第编号(组内一而再的唯一的)。
与rownum的界别在于:使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再实行排序,而row_number()在含有排序从句后是先排序再总结行号码。

select row_number() over (order by ename) rn,ename from emp ;

882828九五至尊手机版 5

select
rownum,ename from emp order by ename;

882828九五至尊手机版 6

●概念上分别

Partition by

select row_number() over(partition by job order by sal) row_number,

rank() over(partition by job order by sal) rank,

dense_rank()
over(partition by job order by sal) dense_rank,

count(1) over(partition by job order by sal) count,

max(empno) over(partition by job order by sal) max,

sum(sal) over(partition by job order by sal) sum,

lag(ename) over(partition by job order by sal) lag,

lead(ename) over(partition by job order by sal) lead,

sum(sal) over(order by sal range between unbounded preceding and current row)
range_unbound_sum,

sum(sal) over(order by sal rows between unbounded preceding and current row)
rows_unbound_sum,

sum(sal) over(order by sal range between 1 preceding and 2 following) range_sum,

sum(sal) over(order by sal rows between 1 preceding and 2 following) rows_sum,

sal, mgr, job, empno, ename

from emp

882828九五至尊手机版 7

注意:
rank、dense_rank的区别,count的变化。

   1.Oracle 是一种对象关周全据库管理体系(OENVISIONDBMS),而Sql server 只是关系型数据库管

Dump

DUMP(expr[,return_fmt[,start_position][,length]])

基本参数时四个,最少可以填的参数是0个。当完全没有参数时,直接重临null。其它三个参数也都有些的私下认可值:

expr:那一个参数是要进行辨析的表达式(数字或字符串等,可以是逐一品种的值)

return_fmt:指重返参数的格式,有5种用法:

         
1)8:以8进制重临结果的值

         
2)10:以10进制重临结果的值(暗中同意)

         
3)16:以16进制再次回到结果的值

         
4)17:以单字符的款式重临结果的值

         
5)一千:以上4种丰硕1000,表示在重返值中丰盛当前字符集

start_position:开首开始展览再次回到的字符地方

length:必要重返的字符长度

select
dump(‘abc’) from dual;

882828九五至尊手机版 8

     理系统(RDBMS).

Type

typ表示近日的expr值的类型。如:2意味NUMBER,96意味CHA翼虎。

CODE TYP


1      VARCHAR2
2      NUMBER
8      LONG
12     DATE
23     RAW
24     LONG RAW
69     ROWID
96     CHAR
112    CLOB
113    BLOB
114    BFILE
180    TIMESTAMP
181    TIMESTAMP WITH TIMEZONE
182    INTERVAL YEAR TO MONTH
183    INTERVAL DAY TO SECOND
208    UROWID
231    TIMESTAMP WITH LOCAL TIMEZONE

   2.Oracle选用Internet文件系统,该系统基于Java的应用程序,能够使数据库基于成为

len

len表示该值所占据的字节数。

对此汉字,ZHS16GBK编码七个汉字须要二个字节,UTF8内需二个字节。

SQL> select
dump(‘多多’,1010) from dual;

     Internet的支出平台;Sql
server 是基于windows

DUMP(‘多多’,1010)

Typ=96 Len=6 CharacterSet=UTF8: 229,164,154,229,164,154

SQL> select
dump(‘多多’,1010) from dual;

   3.Orace 重要的三类文件是:数据文件,控制文件,恢复生机日志文件

DUMP(‘多多’,1010)

Typ=96 Len=4 CharacterSet=ZHS16GBK: 182,224,182,224

     Sql server: 主要数据文件(必须有且不得不有四个),次要数据文件以及日志文件

Value

切切实实的积存值。重回的数值正是Oracle在和谐内部对日前的这一个expr值得存款和储蓄格局。对于非汉字的通常字符串,能够领悟为便是它的ASCII码。

   4.两者协理的命令种类差不离,数据定义语言,数据操纵语言,事务处理控制语言,

Reverse

oracle
提供七个reverse函数,可以兑现将1个指标反向转换.

比如:

SQL> select
reverse(‘123456’) from dual;

             

REVERSE(‘123456’)


654321

出于那个函数,是本着数据库内部存款和储蓄的对象编码举行反转的,

因此,在诸如:数字,普通话等.因为完毕存储的并不是直接编码后的结果,而是经过某种内部转换后完成的,因而,在转换完结后,并不一定是协调想要的结果.示例:

SQL> select
reverse(123456) ,reverse(‘中国’) from dual;

             

REVE奇骏SE(123456)
REVE奥迪Q3SE(‘中国’)



-668705000000
?秃补衩巳?兄

在那些地点,三个整数,3个国语内容,就被转移为任何的始最后.

我们dump看看,就明白oracle是如何reverse了

SQL> select
reverse(123456),dump(123456),dump(reverse(123456)) from dual;

             

REVERSE(123456)
DUMP(123456) DUMP(REVERSE(123456))



-668706000000 Typ=2
Len=4: 195,13,35,57 Typ=2 Len=4: 57,35,13,195

     数据控制语言.在Oracle中,在事情控制语言中除去commit,rollback等还多了贰个

九九乘法表

Oracle相关函数计算:

select replace(reverse(sys_connect_by_path(reverse(rownum || ‘*’ || lv || ‘=’ ||

rpad(rownum * lv, 2)),

‘/ ‘)),

‘/’)

from (select level lv from dual connect by level < 10)

where lv = 1

connect
by lv + 1 = prior lv;

882828九五至尊手机版 9

     Savepoint,设置保存点。

Group by

Oracle的GROUP
BY语句除了最宗旨的语法外,还帮忙ROLLUP和CUBE语句。假诺是ROLLUP(A, B,
C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP
BY,然后是(A)举办GROUP BY,最后对全表举行GROUP BY操作。若是是GROUP BY
CUBE(A, B, C),则第②会对(A、B、C)举办GROUP
BY,然后逐一是(A、B),(A、C),(A),(B、C),(B),(C),最终对全表实行GROUP
BY操作。 grouping_id()可以美化效果。

Grouping
sets(A,B)表示先对A普通分组,再对B普通分组,然后合并在同步。

   5.oracle sql的增加叫PL/SQL,主要的结构化查询工具有sql*plus,isql*plus, pl/sql等

Rollup

汇总数据到行尾。

select
job,sum(sal) from emp group by rollup(job)

882828九五至尊手机版 10

select
deptno,job,sum(sal) from emp group by rollup(deptno,job)

882828九五至尊手机版 11

     Ms sql的壮大叫Transact-SQL,主要的结构化查询工具就是自带的查询分析器

Grouping

select
deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by rollup(deptno,job)

882828九五至尊手机版 12

假使显示”1″表示GROUPING函数对应的列(例如JOB字段)是出于ROLLUP函数所发出的空值对应的新闻,即对此列举行集中总计后的结果。

如果呈现”0″表示此行对应的那列参未与ROLLUP函数分组集中活动。

   6.首要数据类型

CUBE

select
deptno,job,grouping(job),sum(sal) from emp group by cube(deptno,job);

882828九五至尊手机版 13

     Orace:主要辅助char
,varchar2,long,number,datetime,raw,long raw,clob,blob,bfie

groupingsets

select
deptno,job,grouping(job),sum(sal) from emp group by grouping sets(deptno,job);

882828九五至尊手机版 14

select
deptno,job,grouping(job),sum(sal) from emp group by grouping sets((deptno,job),deptno);

882828九五至尊手机版 15

     前边四个是Character数据类型,varchar2补助可变长度的字符串,long支持可

Model

select deptno, job,ename, sal

from emp model return updated rows

partition by(deptno)

dimension by(job,ename)

measures(sal)

–rules(sal
[ ‘ANALYST’,’JAMES’] = 1)

rules(sal [ job in(‘ANALYST’,’CLERK’),length(ename)>1] = 1)

882828九五至尊手机版 16

详细见:http://blog.csdn.net/huangyunzeng2008/article/details/5664172

     变长度的字符数据,raw,long
raw用于存款和储蓄二进制数据,long raw
可变长度

Numtoyminterval和numtodsinterval

总括时间距离的函数。

Numtoyminterval常用的单位有’year’,’month’

numtodsinterval常用的单位有
(‘day’,’hour’,’minute’,’second’)

select sysdate,

sysdate + numtoyminterval(3, ‘month’),

sysdate + numtodsinterval(3, ‘second’) as res

from
dual;

882828九五至尊手机版 17

     最终三个是大目的(lob)数据类型,存款和储蓄非结构化的新闻,例如声音剪辑,摄像文件

skip locked

在11g翼虎第11中学,oracle扩张了skip
locked子句。此时,即使探测到了笔录已经被锁,就不会进来等待队列恐怕抛出ORA-00054错误,而是跳过该条记录继续执行。

Select
owner,
table_name from
t_test2 where
owner=’OUTLN’ for update skip locked;

 

–first、last
select max(ename) keep(dense_rank first order by nvl(sal,0) nulls
first) from emp a;
select max(ename) keep(dense_rank first order by nvl(sal,0) nulls
first) over(partition by deptno),a.* from emp a;
–first_value、last_value
select first_value(ename) over(order by sal),a.* from emp a where
deptno=10;

 

     CLOG表示Character Lob,可以储存多量的字符数据,它对于仓库储存非结构化的XML

     文书档案分外实惠。BLOG代表Binary LOG,此数据类型能够储存大型二进制对象,如

     图形、录制剪辑,声音文件等

     支不支持,money,货币

     Sql server 重要匡助的文件类型char
,varchar,nchar,nvarchar,text,ntext,image,货币类型

     Money,二进制
binary,varbinary等等

●开发语法

A.SQL 语法

    两者的SQL语法基本上是如出一辙的,只是一些细节方面包车型地铁难题

一: 表的军管:修改表的布局,如增加,删除列,創建表

       修改表

          1.修改表的列的數據類型,大小的定義不一样,如下边修改數據類型。

            Ms sql是ALTER
TABLE table_name ALTER COLUMN col DECIMAL (5, 2)

               
Oracle是 Alter
TABLE table_name modify(col decimal(5,2))

          2.充实和刪除表的列,兩者是一致的

            Alter tabe table_name add(col definition)

            Alter tabe table_name drop column col_name

          3 .Oracle假设不要用某列,然後又不想刪除,就足以将某些列标记为未用

            Alter tabe table_name set unused(tel_no)

.创立一时半刻表

Oracle 的語法是:

Create global temporary table aa(col number) on commit preserve rows;

近日表只在用户会话时存在的表,或其数量在用户事务处理期间可

穿梭存在的表,创设权且表时能够钦命该表是或不是合宜在用户会话

里面不停存在(使用on commit
preserve rows)

On commit delete rows 代表事务处理实现后删除它的行

            Ms sql的語法是:

               Select * into #temptable from existedtable

二 查询方面:      

1.查表的前N行记录

oracle 是用rownum 如select * from table_name where
rownum<n

             

            Ms sql 是用top 如select top n * from table_name

             

         2.查詢表的結構

Orace 能够通過desc来查看表的结构 语法是:desc table_name

       只怕选拔数据字典表user_tab_coumns也能够查看到

         Select column_name,data_type from user_tab_coumns

         Where table_name=”

           Ms sql能够通過一些系統的存儲過程來看表的結構

             語法是:exec
sp_help table_name

         3.将多少个表的多寡拉长到另外贰个表中

a.新表存在前提下:兩者語法是一樣的,如

                 insert into newtable(col1)select col1 from old_table

b.新表不存在前提下,能够用

oracle 可以用Create tabe new
_table_name as select * from old_table

             ms sql 可以用
Select * into new_table from old_table

   三 操作符

         1.連接操作符

             Oracle是 ‘||’; Ms sql是‘+’

         2. Oracle的可比操作符中不对等除了'<>’之外,还有叁个 ‘!=’

         3. 算术操作符,都以+-*/;逻辑操作符都以and,not,or(相同點)

         4. oracle汇集操作符除了union,union all之外还投入intersect,minus

           Intersect是仅重临四个查询都有行,minus重回第叁个查询有第

           三个查询没有的行

   四   函數

        1.轉換函數

           Oracle 中有to_char(),to_date(),to_number()

           Ms sql 中有cast,convert

        2.系统日期:

Oracle: sysdate

Ms sql:  getdate()

          如

            Select sysdate from
dual 一定要from子句

            Select getdate() 不肯定要from子句

         3.Decode函数一定if
else,只怕ms 中的case语句

           语法是decode(value,if1,then1,if2,then2….)

             如decode(col1,’1′,’true’,’0′,’fase’)

4.常规函数

Nvl 语法是NVL(EXP1,EXP2)
表示借使ex1为空则重临ex2

Nvl2 语法是nvl(exp1,exp2,exp3)表示假如ex1为空,则重返ex3,不然重临ex2

Nullif 语法是nullif(ex1,ex2)
要是那五个表达式相等则赶回空

coalesce语法是coalesce(ex1,ex2,….exn)重临第二个非空说明式

         5.分組函數

Oracle 中的分組函數Rollup,cube

Rollup重回的结果集带有分组行和小计行,cube爆发交叉报表

如:

Select a,b,sum(c) from tabname group by rollup(a,b)

Select a,b,sum(c) from tabname group by cube(a,b)

            Ms sql中的分組函數 compute 和 compute by

              Compute子句为行聚集函数生成汇总值,该汇总值做为2个增大的行展现

            在结果集中。没有GROUP BY 子句的场馆下,也足以运用重要字COMPUTE.

            此关键字选拔MAX,MIN,SUM,COUNT,AVG等函数生成汇总值,而compute by

            则在控制中断时提交该汇总值,compute by
必须归纳在order by 子句中。

         還有很多函數,諸如日期函數,字符串函數等等,就不一一作比較了。

         Oracle中還有很多比較好的剖析函數,也不列舉了。

五.Oracle中的数据库对象

同义词

      作用:简化SQL语句;隐藏对象的名称和主人,提供对象的集体访问等。

      语法:CREATE PUBLIC
SYNONYM seqname FOR OBJECT

序列

     用来扭转唯壹 、边续的平头,它一般用来自动生成主键或唯一值的键。

    創建体系: create
sequence seqname [increment by 1] start with 1 maxvalue 10

     minvalue 1 cycle cache

    访问系列:

                   Select seqname.nextval from dual 将回来种类的开首值

                   Select seqname.currval from dual   重临连串的最近值

       簇(cluster):

            簇是共享相同数据块的一组表,因为这么些表具有同样的列,并且时不时一同行使。

          当四个或七个表的贮存在物理上格外接近时,能够透过簇来增强使用这几个表的

          SQL联接语句的质量。簇是存储表的法子

          应该先创制簇,然后再成立簇中的表,

六.Oracle中的对象类型

  1. 抽象数据类型

    此类型是带有3个或五个子类型的数据类型,并且这一个数据类型并不局限

   于专业的oracle数据类型

     如:create or replace type
t_mm as object(col number(3),

         col2 varchar2(20))/

       此时得以在开创表时利用该类型,如下

         Create table test (aa varchar2(5),bb t_mm, cc number(10))

       插入记录:

          Insert into test values(‘cccc’, t_mm(1,’col’))

       类型注明:

           用户定义的数据类型也能够声明为
final,not final,instantiable,

           not instantiabe

           Not final表示同意项目派生子类型。默许是final

           create or replace type t_mm as object(col number(3),

            col2 varchar2(20)) Not final

           not instantiabe表示项目没有构造函数。

  1. 可变数组

    可变数组有助于在单个行中存款和储蓄和重复记录的习性。

    数据与表中的任何数据是储存在一块儿的,有限数量的行,不能够被索引

    创设可变数组的 语法是:

       create type array1 as varray(5) of varchar2(5)

    向可变数组中插入记录

    Insert into test1 values (‘2ee’, array1(‘1′,’2′,’3′,’4′,’5’) )

    Select * from test1 结果集如下

       2ee

       ‘1’,’2′,’3′,’4′,’5′

    Select col2 from test1 结果集如下

       ‘1’,’2′,’3′,’4′,’5′

    Select * from table(select t.col2 from test1 where col1=’2ee’);
结果集如下

       1

       2

       3

       4

       5

  1. 嵌套表

   它是富含在表中的表,对每行数据项数据没有限定,贰个表在

另一表中是用作一列,主表中的每一行的嵌套表能够包蕴若干行。

    创造嵌套表

       先创造三个连串

       Create type ord_ty as object(itemcode varchar2(5),qty _ord
number(5),

      Qty_held number(5));

      创立另一个抽象数据类型,即嵌套表数据类型

      Create type ord_nt as table of ord_ty

      创制嵌套表

       Create table order_mas (orderno varchar2(5),odate date,

       vencode varchar2(5), dets ord_nt) nested table dets store as
ord_nt_tab;

      向嵌套表中插入数据:

       Insert into order_mas values(‘001’,to_date(’18-07-08′,’DD-MM-YY’),

       ‘V009’,ord_nt( ord_ty(‘i001’,10,5),ord_ty(‘i002’,34,2));

      更新嵌套表的值:

       Update table(select e.dets from order_mas e where
e.orderno=’001′) p

       Set value(p)
=ord_ty(‘i090′,8,9) where p.itemcode=’i001’;

      删除嵌套表的值:

        Delete from table(select e.dets from order_mas e where
e.orderno=’001′) p

        where p.itemcode=’i001′;

  1. 对象表

              在对象表中每一行都以2个行对象,对象表与关系表分歧:

              对象表中的每一行都有四个OID值,即对象标识符值。该值是在成立行时

             分配的。可以使用create table 命令来创设对象表。

               在创建对象类型时,Oracle 中是不允许为属性定义约束原则,然而

             能够在成立对象表时为指标类型的个性钦点约束规范。

               Create table vend_master of vend_ty(vencode constraint
vc_pk primary key);

               创制对象表与关系表语法不一致

               表的使用方法不一致插入数据足以应用抽象数据类型的构造函数,假诺指标

              表所遵照抽象数据类型又依据另一抽象数据类型,则必须多少个构造函数的嵌

              套调用。

                  Insert into vend_master values (vend_ty());

  1. 对象视图

       借助对象视图能够将面向对象的布局(如抽象数据类型)应用于现有已经

     投入使用的表,而不需重建总体应用程序

B.PL/SQL與T_SQL语法

 

一批處理

  SQL的能力畢竟有限,諸如事務處理方面,批處理於是oracle與ms 都把它進行了擴展,oracle 的擴展叫PL/SQL由表明部分,可实施部分,非常处理部分构成顺序如下:   

Declare declarations

Begin

      Executable statements

      Exception

          Handles

882828九五至尊手机版,End

   Ms 的擴展叫
Transact_SQL,簡稱T_SQL.

      批处理:正是二回实施拍卖一组命令的长河。GO关键字樗着批处理的了断。

     如use master

        go

      复杂一点也是由三局地組成:注明部分,可进行部分,卓殊处理部分。

     不一样的是,異常處理部分一般用跳轉語句來實現。    

例如:

         Declare declarations

          Begin

             Excutable statements

             IF @ERROR <>0 GOTO ERROR

         End

         ERROR:

            BEGIN

             RAISERROR(20058, 16,
-1)

             
return (1)

           
END

二逻辑控制语句       

   1. 控制结构:

     Oracle

         If condition then ……..

         End if

         Case selector

          when exp1 then statements

          Else statements

         End case

     SQL SERVER

         IF condition

           Sql statements

         ELSE

           Sql statements

        Case selector

        When ex1 then statements

        Else statements

        End

   2. 迭代结构

       oracle

          Loop statements end loop;

          While condition

          Loop statement end loop;

       Sql server

            While condition

            Begin

              Sql statement

            End

   面象对象编制程序涉及的定义有对象,类,属性和措施,面向对象的三大特征是:

   封装,继承和多态。

   将数据和函数包装到二个单元中的进程称为封装。无法从表面访问数据,只可以包装在

   类中的那个函数才能访问数据

   继承能够是SQL类型的继续和措施的延续。

   多态是多个对象可以表现各类方式的力量,那使得区别的目的能够有所同等的名号

   的主意,那个点子完结的任务相似,但完结方式却不一样。

    三.變量與常量

Pl/sql变量与常量能够具备属性,帮忙的属性类型有

%type,%rowtype

声称引用数据库列或变量的数据类型的变量时,能够动用%type属性。如:

Declare

      Variable_name table_name.col_name %type

        使用这几个优点是,不必要精晓列vencode的标准数据类型

      %rowtype属性提供表示表中行的笔录类型。

     四.過程與函數(Procedure & Function)

       a. 建立存儲過程的語法不相同:

           Ms sql 的語法是:

             Create procedure procedure_name

               (

                   @Id int =null,

                   @name varchar(10) out[put]

               )

as

[變量定義區]

begin sql_statement end

——————————————————————–   

             CREATE
FUNCTION function_name (@DATE datetime)

RETURNS
int

AS
begin sql statement end

面向對象的特点之一是封裝,程序包就是对有关PL/SQL类型,子程序,游标,卓殊,变量,和常量的卷入,它包涵两有的程序包规格表明和次序包主体

在包规格表明书中,能够表明类型,变量,常量,十分,游标,子程序

程序包主体实未来程序包规格中定义的游标、子程序

        包頭語法部分:

Create or replace package package_name is|as

Public type and item declarations

Subprogram specifications

End package_name

實例如下:

Create or replace package pack_me is

Procedure order_proc(orno varchar2);

  Function    order_fun(ornos varchar2) return varchar2;

End pack_me

包體語法部分:

Create or replace package body package_name is|as

Private type and item decarations

Subprogram bodies

End package_name;

         包體具體實例就不寫了,包頭隻是起一個聲明成效,具體實現部分都在包體裡面。

         上边是创办存款和储蓄进度的语法:

          Create [or replace] procedure procedure_name[arg1
in|out|in out]type {is|as}

           [變量定義區]

          Begin

             Execute Sql statement

          Exception handlers

End

         注意:參數列表那裡,oracle是先定義參數是輸入還是輸出參數,然後再定義

參數類型; 而sql server正好相反

         成立函数的语法:

Create function function_name argument

Return datatype is|as

Local decaration

Begin

Excutable statement

Exception

Handles

End;

      b.變量賦值

1.    Oracle里的用法:存款和储蓄进度中边询问边给变量赋值。

select 某一列名 into 变量名 from table where ..;

相当于sql server中的select 变量名=列. From table where ….

注意:select * /某一列名 into 表名

        2.   Oracle 直接赋值的标志是:’ := ‘

  五 觸發器(Trigger)

        Oracle

        Create or replace trigger trigger_name [before/after]

       [insert/update/delete] on table_name

        变量表明

        begin

        Sql statement

        end

Ms sql

Create trigger trigger_name on table

{for |after|instead of} [insert|update|delete] as sql_statements

    六 遊標(Curcor)

        Oracle中提供三种游标类型,它们是静态游标和ref游标

静态游标又分为隐式游标与显式游标

Ref游标,游标变量是一种引用
类型

隐式游标属性包罗%notfound,%found,%rowcount,%isopen

如:

       Begin

                    Delete from ta where ord=’ddd’

               If sql%notfound then

                     Dbms_output.put_line(”未找到值)

              Else

                     Dbms_output.put_line(找到并剔除之)

            End if

End

体现游标:能够用上面语句控制游标

      Open cursorname

       Fetch cursor_name into var_name

       Close cursor_name

        创制游标语法:

     Oracle:

       declare variable

       Cursor test is select * from order

    Sql server

       Declare test cursor for select * from order

    七 .错误处理(Exception & test & debug)

有两体系型的老大:一种预订义,其它一种是用户自定义

預定義的類型有诸多,如No_data_found,Cursor_already_open

       對於Oracle的調試,能够依靠第叁方工具,如toad,Pl.sql
Developer,

       对于sql server采取 变量大概print 方式进行调节

        Oracle格外定义部分的示范:

            Exception

          When <exception_name> then statements

           When others then

               Statements

           End;

         其中<exception_name>是系统预订义的名字。

       Raiser_application_error用于成立用户定义的错误消息的长河,用户定义的荒谬音讯

     能够钦点的老大描述的更详细

      Sql server在错误处理上多使用自定义。或许是用goto跳转的法子

       如:

        Begin

        Sql statements

        if @error<>0 goto error

        end

        Error:

            Return;

      或是直接用raiserror

      if @error<>0

begin

raiserror(‘爆发错误.’,-1,-1)

return (1)

end

好了,两者的严重性的差异就写到那,但愿自身说明清楚了,由于东西相比较多,所以还有众多切实事物没有写

http://www.cnblogs.com/wangxiaohuo/archive/2008/04/20/1162631.html


 

函数
SQLServer和Oracle的常用函数相比较   1.相对值   S:select abs(-1) value
  O:select abs(-1) value from dual
  2.取整(大)   S:select ceiling(-1.001) value   O:select
ceil(-1.001) value from dual
  3.取整(小)   S:select floor(-1.001) value   O:select
floor(-1.001) value from dual
  4.取整(截取)   S:select cast(-1.002 as int) value   O:select
trunc(-1.002) value from dual
  5.四舍五入   S:select round(1.23456,4) value 1.23460   O:select
round(1.23456,4) value from dual 1.2346
  6.e为底的幂   S:select Exp(1) value 2.7182818284590451
  O:select Exp(1) value from dual 2.71828182
  7.取e为底的对数   S:select log(2.7182818284590451) value 1
  O:select ln(2.7182818284590451) value from dual; 1
  8.取10为底对数   S:select log10(10) value 1   O:select
log(10,10) value from dual; 1
  9.取平方   S:select SQUARE(4) value 16   O:select power(4,2)
value from dual 16
  10.取平方根   S:select SQ大切诺基T(4) value 2   O:select SQ锐界T(4) value
from dual 2
  11.求任意数为底的幂   S:select power(3,4) value 81   O:select
power(3,4) value from dual 81
  12.取肆意数   S:select rand() value   O:select
sys.dbms_random.value(0,1) value from dual;
  13.取标志   S:select sign(-8) value -1   O:select sign(-8) value
from dual -1   ———-数学函数
  14.圆周率   S:SELECT PI() value 3.1415926535897931   O:不知道
  15.sin,cos,tan 参数都以弧度为单位   例如:select sin(PI()/2) value
获得1(SQLServer)
  16.Asin,Acos,Atan,Atan2 重临弧度
  17.弧度角度调换(SQLServer,Oracle不领会)   DEGREES:弧度-〉角度
  RADIANS:角度-〉弧度
  ———数值间比较
  18. 求集合最大值   S:select max(value) value from   (select 1
value   union   select -2 value   union   select 4 value
  union   select 3 value)a
  O:select greatest(1,-2,4,3) value from dual
  19. 求聚集最小值   S:select min(value) value from   (select 1
value   union   select -2 value   union   select 4 value
  union   select 3 value)a
  O:select least(1,-2,4,3) value from dual
  20.哪些处理null值(F第22中学的null以10替代)   S:select F1,IsNull(F2,10)
value from Tbl   O:select F1,nvl(F2,10) value from Tbl
  ——–数值间相比较
  21.求字符序号   S:select ascii(‘a’) value   O:select ascii(‘a’)
value from dual
  22.从序号求字符   S:select char(97) value   O:select chr(97)
value from dual
  23.连接   S:select ’11’+’22’+’33’ value   O:select
CONCAT(’11’,’22’)||33 value from dual
  23.子串地点 –重临3   S:select CHALacrosseINDEX(‘s’,’sdsq’,2) value
  O:select INSTLX570(‘sdsq’,’s’,2) value from dual
  23.模糊子串的职分 –再次来到2,参数去掉中间%则赶回7   S:select
patindex(‘%d%q%’,’sdsfasdqe’) value
  O:oracle没觉察,不过instr可以透过第5霾问刂瞥鱿执问?B奔驰M级>  select
INSTOdyssey(‘sdsfasdqe’,’sd’,1,2) value from dual 返回6
  24.求子串   S:select substring(‘abcd’,2,2) value   O:select
substr(‘abcd’,2,2) value from dual
  25.子串代替 重返aijklmnef   S:SELECT STUFF(‘abcdef’, 2, 3,
‘ijklmn’) value   O:SELECT Replace(‘abcdef’, ‘bcd’, ‘ijklmn’) value
from dual
  26.子串全体交替   S:没觉察   O:select
Translate(‘fasdbfasegas’,’fa’,’小编’ ) value from dual
  27.长度   S:len,datalength   O:length
  28.大小写转换 lower,upper
  29.单词首字母大写   S:没察觉   O:select INITCAP(‘abcd dsaf df’)
value from dual
  30.左补空格(LPAD的第九个参数为空格则同space函数)   S:select
space(10)+’abcd’ value   O:select LPAD(‘abcd’,14) value from dual
  31.右补空格(奥迪Q7PAD的首先个参数为空格则同space函数)   S:select
‘abcd’+space(10) value   O:select 奥迪Q3PAD(‘abcd’,14) value from dual
  32.去除空格   S:ltrim,rtrim   O:ltrim,rtrim,trim
  33. 双重字符串   S:select REPLICATE(‘abcd’,2) value   O:没觉察
  34.发音相似性相比(那四个单词再次回到值一样,发音相同)   S:SELECT
SOUNDEX (‘史密斯’), SOUNDEX (‘Smythe’)   O:SELECT SOUNDEX (‘Smith’),
SOUNDEX (‘Smythe’) from dual   SQLServer中用SELECT
DIFFERENCE(‘史密斯ers’, ‘Smythers’) 比较soundex的差
  重回0-4,4为同音,1高高的
  ————–日期函数
  35.系统日子   S:select getdate() value   O:select sysdate value
from dual
  36.左右几日   直接与整数相加减
  37.求日期   S:select convert(char(10),getdate(),20) value
  O:select trunc(sysdate) value from dual   select
to_char(sysdate,’yyyy-mm-dd’) value from dual
  38.求时间   S:select convert(char(8),getdate(),108) value
  O:select to_char(sysdate,’hh24:mm:ss’) value from dual
  39.取日期时间的其余一些   S:DATEPALacrosseT 和 DATENAME 函数
(第一个参数决定)   O:to_char函数 第三个参数决定
  参数———————————下表须要补给   year yy, yyyy
  quarter qq, q (季度)   month mm, m (m O无效)   dayofyear dy, y
(O表星期)   day dd, d (d O无效)   week wk, ww (wk O无效)   weekday
dw (O不精晓)   Hour hh,hh12,hh24 (hh12,hh24 S无效)   minute mi, n (n
O无效)   second ss, s (s O无效)   millisecond ms (O无效)
  ———————————————-
  40.当月最终一天   S:不知底   O:select LAST_DAY(sysdate) value
from dual
  41.本星期的某一天(比如星期三)   S:不领会   O:SELECT
Next_day(sysdate,7) vaule FROM DUAL;
  42.字符串转时间   S:可以一贯转大概select cast(‘二零零一-09-08’as
datetime) value   O:SELECT To_date(‘2004-01-05 22:09:38′,’yyyy-mm-dd
hh24-mi-ss’) vaule FROM DUAL;
  43.求两天期某一局地的差(比如秒)   S:select
datediff(ss,getdate(),getdate()+12.3) value
  O:直接用多个日子相减(比如d1-d2=12.3)   SELECT
(d1-d2)*24*60*60 vaule FROM DUAL;
  44.依照差值求新的日期(比如秒钟)   S:select
dateadd(mi,8,getdate()) value   O:SELECT sysdate+8/60/24 vaule FROM
DUAL;
  45.求不一致时区时间   S:不亮堂   O:SELECT
New_time(sysdate,’ydt’,’gmt’ ) vaule FROM DUAL;
  —–时区参数,新加坡在东8区应有是Ydt——-   AST ADT 太平洋专业时间
  BST BDT 咸海专业时间   CST CDT 中部标准时间   EST EDT
南边标准时间   罗红霉素T 格林尼治标准时间   HST HDT
阿拉斯加—苏梅岛正式时间   MST MDT 山区标准时间   NST 纽芬兰共和国规范时间
  PST PDT 印度洋规范时间   YST YDT YUKON标准时间 
Oracle补助的字符函数和它们的Microsoft SQL Server等价函数:
函数 Oracle Microsoft SQL Server 把字符转换为ASCII ASCII ASCII 字串连接
CONCAT (expression + expression) 把ASCII转换为字符 CH中华V CHA路虎极光重回字符串中的开始字符(左起) INST翼虎 CHA索罗德INDEX 把字符转换为小写 LOWEHavalLOWE奥迪Q3 把字符转换为大写 UPPETucson UPPE奇骏 填充字符串的右侧 LPAD N/A
清除初叶的空白 LTavancierIM LT途乐IM 清除尾部的空白 酷路泽TMuranoIM 君越TLANDIM
字符串中的开头形式(pattern) INST锐界 PATINDEX 数十次重复字符串 SportagePAD
REPLICATE 字符串的语音表示 SOUNDEX SOUNDEX 重复空格的字串 奥德赛PAD SPACE
从数字数据转换为字符数据 TO_CHAPRADO ST奇骏 子串 SUBST卡宴 SUBSTCR-VING 替换字符
REPLACE STUFF 将字串中的种种词首字母大写 INITCAP N/A 翻译字符串
TRANSLATE N/A 字符串长度 LENGTH DATELENGTH or LEN 列表中最大的字符串
GREATEST N/A 列表中型小型小的的字符串 LEAST N/A 假诺为NULL则转移字串 NVL
ISNULL
 
日子函数
函数 Oracle Microsoft SQL Server 日期相加 (date column +/- value) or
ADD_MONTHS DATEADD 八个日子的差 (date column +/- value) or
MONTHS_BETWEEN DATEDIFF 当明日子和岁月 SYSDATE GETDATE()
3个月的最终一天 LAST_DAY N/A 时区转换 NEW_TIME N/A 日期后的率先个周末
NEXT_DAY N/A 代表日期的字符串 TO_CHA牧马人 DATENAME 代表日期的平头
TO_NUMBER (TO_CHAKuga)) DATEPAEscortT 日期舍入 ROUND CONVEPAJEROT 日期截断 TRUNC
CONVE中华VT 字符串转换为日期 TO_DATE CONVE奥迪Q5T 要是为NULL则转移日期 NVL
ISNULL
 
更换函数
    函数          Oracle      Microsoft SQL Server 数字转换为字符  
TO_CHAOdyssey        CONVETucsonT 字符转换为数字   TO_NUMBERubicon      CONVECRUISERT
日期转换为字符   TO_CHALX570        CONVE本田CR-VT 字符转换为日期  
TO_DATE        CONVE奥迪Q5T 16进制转换为2进制 HEX_TO_RAW    CONVELANDT
2进制转换为16进制 RAW_TO_HEX    CONVERT
 
其他行级别的函数
      函数           Oracle       Microsoft SQL Server
再次回到第二个非空表明式 DECODE           COALESCE 当前连串值          
CU帕杰罗CRUISERVAL            N/A 下3个行列值         NEX电视机AL           N/A
要是exp1 = exp2, 重临null DECODE NULLIF 用户登录账号ID数字   
UID            SUSE福睿斯_ID 用户登录名           USEKoleos           
SUSE索罗德_NAME 用户数据库ID数字      UID            USE纳瓦拉_ID
用户数据库名         USE兰德酷路泽            USE君越_NAME 当前用户           
CU福特ExplorerRENT_USER     CURRENT_USEPRADO 用户环境(audit trail) USERENV        N/A
在CONNECT BY子句中的级别 LEVEL N/A
 
共谋函数
函数 Oracle Microsoft SQL Server Average AVG AVG Count COUNT COUNT
Maximum MAX MAX Minimum MIN MIN Standard deviation STDDEV STDEV or
STDEVP Summation SUM SUM Variance VARIANCE VAR or VARP
Oracle还有三个管用的函数EXTRACT,提取并且重临日期时间或时间距离表明式中一定的时间域:
EXTRACT(YEA中华V FROM 日期)

 


 

积存进度
一. 多表连接查询,更新存款和储蓄进度 Sql存款和储蓄进程 ALTE奥德赛 PROCEDURE [dbo].[
GetEvent]   @SCSWId nvarchar(20)= null ,   @ToDate DATETIME,  
@FromDate DATETIME AS   SELECT NOTES.NOTE_ID,   NOTES.NOTE,  
SCSW_CALENDAR.DATE_TIME    FROM SCSW_CALENDAR   LEFT OUTER JOIN NOTES
ON SCSW_CALENDAR.NOTE_ID=notes.note_id   WHERE
SCSW_CALENDAR.SCSW_ID = SCSWId   ORDER BY Patient.PatientId
Oracel存款和储蓄进程
1.查询数据的存款和储蓄进程 PROCEDURE Get伊芙nt(SCSWId IN VA奥迪Q7CHA奥迪Q52, FromDate IN
DATE, ToDate IN DATE, refOut OUT refcursor) IS BEGIN   OPEN refOut FO奥迪Q7  
select NOTES.NOTE_ID, NOTES.NOTE, SCSW_CALENDAR.DATE_TIME   from
SCSW_CALENDAR   left join NOTES on
SCSW_CALENDAR.NOTE_ID=notes.note_id   where SCSW_CALENDAR.SCSW_ID =
SCSWId   AND SCSW_CALENDAR.DATE_TIME >= FromDate   AND
SCSW_CALENDAR.DATE_TIME < ToDate   order by
SCSW_CALENDAR.DATE_TIME; END GetEvent;
2.立异数据的贮存进度: procedure UpdateArticlesubmodel ( ArticleSubID
number, ArticleTitle nvarchar2, ArticleKeyWord nvarchar2, ArticleContent
CLOB, CreatePerson nvarchar2, ChangeDate date, SetTop number,
ArticleSubStyleID number, Checked number ) as begin   update
“阿特icleSubModel”   set “ArticleTitle”=ArticleTitle,  
“ArticleKeyWord”=ArticleKeyWord,   “ArticleContent”=ArticleContent,  
“CreatePerson”=CreatePerson,   “CreateDate”=ChangeDate,  
“SetTop”=SetTop,   “阿特icleSubStyleID”=ArticleSubStyleID,  
“Checked”=Checked   where “ArticleSubID”=ArticleSubID;   commit;  
Exception when others then   rollback; end UpdateArticlesubmodel;
3.删除数据的积存进度 procedure DeleteArticlesubmodel ( ArticleSubID
number ) as begin   delete from “ArticleSubAccessories”   where
“ArticleSubID”=ArticleSubID;   delete from “ArticleSubModel”   where
“ArticleSubID”=ArticleSubID;   commit;   Exception when others then  
rollback; end DeleteArticlesubmodel;

 


 

 

1.  top N 问题 在sql server中,top N
难题很简单消除,如下例:从表stbdbdj中选取排序后的第②行数据实行赋值。

在sql中国化学工业进出口总公司解方法很简短,在select 前边加上:top n 即可,在那之中 n 代表行数。

select top 1 @entrust_date = entrust_date, @entrust_no = entrust_no
from run2k..stbdbdj where entrust_date = @date and entrust_no >
@entrust_no_q and report_status = ‘1’ order by
entrust_date,entrust_no;

在oracle中,没有top
n那些命令,大家运用把两层查询格局缓解:首先,把须要寻找的字段值直接开始展览排序,然后在外边进行第三次查询,并动用rownum决定行数。

select entrust_date,entrust_no into @entrust_date, @entrust_no from
( select entrust_date,entrust_no from stbdbdj where entrust_date =
@date and entrust_no > @entrust_no_q and report_status = ‘1’
order by entrust_date,entrust_no ) where rownumber <=1 ;

  1. 怎么缓解结果集再次回到时,* 和变量同时设有的题材
    上面例子表示,在用游标重回结果集时,同时重返2个变量的值,在 sql server
    中代码如下所示: select a.*,b.organ_id from run2k..stbbp
    a,run2k..stkaccoarg b where a.date = @entrust_date and a.serial_no =
    @serial_no and a.branch_no = b.branch_no and a.exchange_type =
    b.exchange_type;

但在oracle中却尚无那种用法,’*’后边必需跟from。解决方式如下:
1)大家得以把 ‘*’
变成所需求选取的字段,便是说选取表中需求出示的漫天字段表示*。 例如:

open  p_cursor  for select  branch_no,…,organ_id where …
2)若是那个字段可能说变量是从其它一张表中取出来的,同样可以应用上面包车型大巴办法。

open p_cursor for select a.*,b.organ_id; from stkaccoentrust a,
stkaccoarg b where a.branch_no = b.branch_no and a.exchange_type =
b.exchange_type and a.init_date = v_entrust_date and a.serial_no =
v_serial_no;

  1. 外联接难点 sql <—> oracle a = *b <—> a(+)= b a *= b
    <—> a = b(+)

  2. 多条记录求和难点 select sum(A+B+C) into D from … where … group by

单条记录求和 select A+B into C from … where …

  1. case 难题转换 sql: case client_status when ‘0’ then ‘正常’ when ‘1’
    then ‘冻结’ when ‘2’ then ‘挂失’ when ‘3’ then ‘销户’ else ‘未知’ end

oracle:
decode(client_status,’0′,’正常,’1′,’冻结’,’2′,’挂失’,’3′,’销户’,’未知’);

  1. char 和 varchar 类型分化: char 底部补空格,varchar 底部不补空格。

  2. convert转换难点 sql —> oracle convert(char(5),branch_no)
    —> to_char(branch_no,’99999′) convert(char(19),count(*)) —>
    lpad(to_char(count(*)),19) convert(varchar(20),serial_no) —>
    to_char(serial_no,’999…9′ ) 总共20个9 lpad(to_char(serial_no),20)

  3. charindex(substring,string) —> instr(string,substring) 子串 父串
    —> 父串 子串


 

 

 

Oracle中差别 882828九五至尊手机版 18
SQL SE索罗德VE路虎极光中:
本质上没不一致。只是函数有如:只好回到一个变量的范围。而存款和储蓄进度能够重回多少个。而函数是足以停放在sql中应用的,能够在select中调用,而存储进程极度。执行的真面目都一模一样。
    
函数限制比较多,比如无法用一时半刻表,只好用表变量.还有一对函数都不可用等等.而存款和储蓄进度的界定绝对就比较少
      1.   
一般的话,存款和储蓄进程达成的效果要复杂一点,而函数的兑现的作用针对性相比较强。
      2.    对于仓库储存进度来说能够回到参数,而函数只可以再次回到值大概表对象。
      3.   
存款和储蓄进度一般是当做二个独自的一部分来推行(EXEC执行),而函数能够用作查询语句的1个有的来调用(SELECT调用),由于函数能够再次来到七个表对象,因而它能够在查询语句中位居FROM关键字的末尾。
      4.    当存款和储蓄进程和函数被实践的时候,SQL Manager会到procedure
cache中去取相应的询问语句,尽管在procedure
cache里没有对应的查询语句,SQL Manager就会对存款和储蓄进度和函数进行编写翻译。
      Procedure cache中保留的是执行布置 (execution plan)
,当编写翻译好之后就执行procedure cache中的execution plan,之后SQL
SECR-VVE途达会依照各类execution
plan的其实际景况况来设想是还是不是要在cache中保留这么些plan,评判的正儿八经2个是其一execution
plan大概被利用的频率;其次是生成那一个plan的代价,相当于编写翻译的耗时。保存在cache中的plan在下次实施时就无须再编写翻译了。
sql
server中总括的用法当先1/2适用于Oracle,在实际的行使进度中,还要看系统贯彻的复杂程度和事实上情状.有经验的DBA对于那六头的使用应当已经精通于胸了,高手路过还请多多指教!

 

转自:http://www.cnblogs.com/jayhong/archive/2009/08/30/1556642.html

 


 

 

http://blog.163.com/mikelisuper/blog/static/72241886201112745343430/

 

相关文章

Your Comments

近期评论

    功能


    网站地图xml地图