TSQL与PLSQL的比较.doc

TSQL与PL/SQL的比较1) 数据类型TSQLPL/SQLnumeric(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)datetimedate记录Record表字段%type表记录%rowtype表Table自动增长变量AUTOINCREMENT2) 变量声明、赋值与引用TSQLPL/SQL声明declare ls_casher char(1), ln_payAmt decimal(14,4)declare on_hand INTEGER; ls_casher char(1);赋值select ls_casher = Als_casher:=A;引用if ls_casher = Aif 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) 函数与操作符字符串TSQLPL/SQL连接+|TRIMLTRIM、RTRIMSUBSTRINGSUBSTR、SUBSTRBINSTR、INSTRBright(str,n)substr(str,-n)日期TSQLPL/SQL系统日期getdate()SYSDATE数值TSQLPL/SQL空值判断与处理TSQLPL/SQL判断IS NULLIS NULL空值替换Isnull(para,0)NVL(para,0)REPLACE(old_string, NULL, my_string) 转换TSQLPL/SQL字符-日期Convert(datetime, expr, style)To_Date(format, expr)字符-日期、数值Convert(char(n), expr, style)To_char(expr,format)数值To_Number() 其它TSQLPL/SQL4) 语句TSQLPL/SQLstatement blockBEGIN.END BEGIN.END;conditional1) IFELSE2) IFELSE IFelse3) CASE1) IF.then.ELSEend if;2) Ifthen elsifelseendif3)decodeRepeatWHILE Boolean_expression statement_block BREAK statement_block CONTINUE1)Loop exit;end loop;2)loopexit whenend loop;3)WHILE condition LOOP sequence_of_statements; EXIT WHEN boolean_expression; END LOOP;3) forin reverseloop end loop;GOTOGOTO labellabel:GOTO label;Exits unconditionallyRETURNReturn;Sets a delay for statement executionWAITFORComment-/*/-/*/PRINTPRINT stringSet serveroutput ondbms_output.put_line(string);RAISERRORRAISERROREXECUTEEXECUTENULL statementNULL;5) cursorTSQLPL/SQLDECLAREDECLARE cursor_name CURSORLOCAL | GLOBALFORWARD_ONLY | SCROLLSTATIC | KEYSET | DYNAMIC | FAST_FORWARDREAD_ONLY | SCROLL_LOCKS | OPTIMISTICTYPE_WARNINGFOR select_statementFOR UPDATE OF column_name ,.nDECLARE CURSOR cursor_name IS SELECT_statement;openOpen cursor_nameOpen cursor_name;FetchFetch cursor_name into var1,var2Fetch cursor_name into var1,var2| %rowtype_var;CloseClose cursor_nameClose cursor_name;AttributeFETCH_STATUSCURSOR_ROWSCURSOR_STATUS%found%notfound%isopen%rowcountDEALLOCATEDEALLOCATE cursor_name隐式cursorSelectinto (仅可处理单行记录)6) triggerTSQLPL/SQL创建CREATE TRIGGER trigger_nameON tableWITH ENCRYPTIONFOR DELETE,INSERT, UPDATE AS sql_statement .n Create or replace trigger t_name before|afterinsert|update|delete on table_name for each row when conditional 类型(按触发级别和时序)语句after行或语句before or after访问数据操纵行的值通过表Inserted、Deleted访问通过记录 :New、 :Old访问，仅可用于行级触发器谓词/函数/属性Inserting、updating、deleteingUpdating(col)Update(col)使能Alter table tabname disable|enable trigger t_name|allAlter trigger t_name disable|enable限制作为触发语句的一部分，不可用事务控制命令不能声明和使用LONG、LONG RAW变量和列删除Drop trigger t_nameDrop trigger t_name;7) procedureTSQLPL/SQL创建CREATE PROCEDURE p_name parameter data_type VARYING = default OUTPUT ,.nWITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION AS sql_statement .nCreate or replace procedure p_name Para1 in|out|inout datatype, :=|default default_value IS|AS查询删除DROP PROCEDURE p_nameDROP PROCEDURE p_name;调用EXEC p_name para1,P_name(para1,);参数按位置传递1)按位置传递2)带名传递 P_name(para1=var1);debit_account(amount = 500, acct_id = 10261);8) 数据字典/系统表TSQLPL/SQL系统对象表Dbo.sysobjectsUser_source、User_objects(OBJ)、User_tables(TABS)、User_triggers、ALL_tables、All_View、All_catalog、All_objects对象脚本sp_helptextDESC、ALL_source用户表SysusersAll_users表列All_tab_columns依赖All_dependencies字典表说明DICT9) SQLTSQLPL/SQLSelectSelect var=Select value into var from dualInsertinsert / insert intoinsert intoDelete比较Any, some, all集合Union、Union all、Intersect、Minus、10) 全局变量TSQLPL/SQL语句执行成功errorSQLCODEselect 是否有结果o + SQL%FOUND11) 命令行查询工具ISQLSQL PLUS读取、执行SQL文件Isql Usa Ppass Shost ifilesqlplus -s user/passdb -filename12) 杂项TSQLPL/SQL锁在SQL语句中InsertWith tablockInsertWith TablockxSelectfor updateSelectfor readonly独立语句set transcation isolation level to Read uncommited在SQL语句中select for update of;独立语句lock table tabname in row share mode;lock table tabname in share exclusive mode;用户连接数数据库文件DeviceTablespaceCREATE TABLESPACE testdb DATAFILE C:ORANTDATABASEtestdb.ORA SIZE 20M AUTOEXTEND ON NEXT 2M;CREATE ROLLBACK SEGMENT RB_TESTDB TABLESPACE TESTDB;ALTER ROLLBACK SEGMENT RB_TESTDB ONLINE;显示DML执行计划Show planExplain plan保留点 Save transcation Sp_name ROLLBACK TRANSACTION percentchanged Savepoint Sp_name Rollback to savepoint sp_name对模式对象改名Rename分析对象AnalyzeSp_help?13)对于TSQL autoincremental identity 字段的转换drop table x cascade constraints/create table x( xxx number(10,0) not null, yy varchar2(10) null , constraint pk_tab_xxxxxxx primary key (cc)/CREATE SEQUENCE ISD.TEST_SEQ INCREMENT BY 1 ST