SQL SERVER 存储过程 嵌套事务 数目不对 sqlserver存储过程调用函数

SQLServer和Oracle，存储过程区别，常用函数对比（二）

2012-07-25 17:00

三.變量與常量
Pl/sql变量与常量可以具有属性，支持的属性类型有
%type,%rowtype
声明引用数据库列或变量的数据类型的变量时，可以使用%type属性。如：
 Declare
      Variable_name table_name.col_name %type
使用这个优点是，不需要知道列vencode的准确数据类型
属性提供表示表中行的记录类型。
     四.過程與函數(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;
         包體具體實例就不寫了，包頭隻是起一個聲明作用，具體實現部分都在包體裡面。
下面是创建存储过程的语法：
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 变量名
相当于sql server中的select 变量名=列. From table where ….
注意：select * /某一列名 into 表名
直接赋值的符号是：' := '
五
        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)
中提供两种游标类型，它们是静态游标和ref游标
静态游标又分为隐式游标与显式游标
Ref游标，游标变量是一种引用类型
隐式游标属性包括%notfound,%found,%rowcount,%isopen
如：
       Begin
                    Delete from ta where ord='ddd'
               If sql%notfound then
未找到值)
              Else
（找到并删除之）
            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 形式进行调试
异常定义部分的示例：
            Exception
          When <exception_name> then statements
           When others then
               Statements
           End;
其中<exception_name>是系统预定义的名字。
用于创建用户定义的错误信息的过程，用户定义的错误消息
可以指定的异常描述的更详细
在错误处理上多采用自定义。或者是用goto跳转的方式
如：
        Begin
        Sql statements
        if @error<>0 goto error
        end
        Error:
            Return;
或是直接用raiserror
      if @error<>0
begin
raiserror('发生错误.',-1,-1)
return (1)
end
好了，两者的主要的差异就写到这，但愿我表述清楚了，由于东西比较多，所以还有很多具体东西没有写


 
函数

 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 SQRT(4) value 2
 　　O:select SQRT(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值(F2中的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 CHARINDEX('s','sdsq',2) value  
 　　O:select INSTR('sdsq','s',2) value from dual

 　　23.模糊子串的位置 --返回2,参数去掉中间%则返回7
 　　S:select patindex('%d%q%','sdsfasdqe') value  
 　　O:oracle没发现，但是instr可以通过第四霾问　刂瞥鱿执问?BR>　　select INSTR('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.右补空格（RPAD的第一个参数为空格则同space函数）
 　　S:select 'abcd'+space(10) value
 　　O:select RPAD('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 ('Smith'), SOUNDEX ('Smythe')
 　　O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual
 　　SQLServer中用SELECT DIFFERENCE('Smithers', '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:DATEPART 和 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