全面对比T-SQL与PL SQL.doc

全面对比T-SQL与PL/SQL 1)数据类型 TSQL PL/SQL numeric(p,s) numeric(p,s) or NUMBER(p,s) decimal(p,s) decimal(p,s) or NUMBER(p,s) char(m) Char(m) varchar(m) varchar2(m) datetime date 记录 Record 表字段 %type 表记录 %rowtype 表 Table 自动增长变量 AUTOINCREMENT 2)变量声明、赋值与引用 TSQL PL/SQL 声明 declare @ls_casher char(1), @ln_payAmt decimal(14,4) declare on_hand INTEGER; ls_casher char(1); 赋值 select @ls_casher = A ls_casher:=’A’; 引用 if @ ls_casher = A … if ls_casher = A then … 在SQL语句中赋值 SELECT @ls_casher=sal FROM emp WHERE empno = emp_id; SELECT sal INTO ls_casher FROM emp WHERE empno = emp_id; 在SQL语句中引用 SELECT * FROM emp WHERE sal = @ls_casher; SELECT * FROM emp WHERE sal = ls_casher; 3)函数与操作符 字符串 TSQL PL/SQL 连接 + || TRIM LTRIM、RTRIM SUBSTRING SUBSTR、SUBSTRB INSTR、INSTRB right(str,n) substr(str,-n) 日期 TSQL PL/SQL 系统日期 getdate() SYSDATE 空值判断与处理 TSQL PL/SQL 判断 IS NULL IS NULL 空值替换 Isnull(para,0) NVL(para,0) REPLACE(old_string, NULL, my_string) 转换 TSQL PL/SQL 字符-日期 Convert(datetime, expr, style) To_Date(format, expr) 字符-日期、数值 Convert(char(n), expr, style) To_char(expr,format) 数值 To_Number() 语句 TSQL PL/SQL statement block BEGIN...END BEGIN...END; conditional 1) IF…ELSE… 2) IF…ELSE IF…else… 3) CASE 1)IF..then...ELSE…end if; 2)If…then… elsif…else…endif 3)decode Repeat WHILE Boolean_expression {statement_block} [BREAK] {statement_block} [CONTINUE] 1)Loop …exit;…end loop; 2)loop…exit when…end loop; 3)WHILE condition LOOP sequence_of_statements; EXIT WHEN boolean_expression; END LOOP; 3)for…in [reverse]…loop … end loop; GOTO GOTO label … label: … GOTO label; … label … Exits unconditionally RETURN Return; Sets a delay for statement execution WAITFOR Comment -- /*…*/ -- /*…*/ PRINT PRINT string Set serveroutput on dbms_output.put_line(string); RAISERROR RAISERROR EXECUTE EXECUTE NULL statement NULL; 4)游标 TSQL PL/SQL DECLARE DECLARE cursor_name CURSOR [LOCAL | G