Sqlserver 转oracle 那点事....

Sql Server数据类型               Oracle数据类型
Char,Varchar                         Varchar2
Smalllint,int,bigint,bit             int
float/dec/decimal/numeric      NUMBER
Text,image,binary                  blob
Date,time,datetime                 Date

sqlserver	oracle	说明
SELECT NAME FROM sysobjects	select TABLE_NAME from user_tables	查看表名是否存在
Year（）	to_char(sysdate,'yyyy')	取年
Month（）	to_char(sysdate,'mm')	取月
dateadd(mm,1,@begindate)	SELECT add_months(to_date('2008/10/31 17:22:49 ','YYYY/MM/DD HH24:MI:SS'),1) FROM dual	加一个月
datepart(week,@SDate-1);	select to_char(sysdate - 1,'d')  from dual;	返回当前输入的日期是周几，-1代表--中国人的习惯是星期一是每周的第一天
raiserror	raise_application_error(-20003,'ddd');	抛出一个错误
substring	SUBSTR	截取字符串
Convert	CAST(a.id AS VARCHAR);	转换变量类型
Set NOCOUNT ON	set feedback off	关闭影响行提示
SELECT A.NAME      FROM syscolumns A,    sysobjects B       WHERE LOWER(B.NAME) = LOWER(v_TableName)      AND B.xtype = 'U'      AND A.id = B.ID      ORDER BY a.colid;	select * from user_tab_columns where table_name='大写表名'	索表结构，列名检
create table #ForeignTableList 每次在过程中建立	CREATE GLOBAL TEMPORARY TABLE Tmp_ForeignTableList 会话级临时表，建立一次可重复使用表结构，但数据时会话级的，一旦断开于数据库的链接，表存在，表中数据消失	临时表
SELECT object_name(b.fkeyid),(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)  FROM sysobjects a  join sysforeignkeys b on a.id=b.constid  join sysobjects c on a.parent_obj=c.id  where a.xtype='f' AND c.xtype='U';	SELECT USER_TAB_COLS.COLUMN_NAME as 列名 , USER_TAB_COLS.DATA_TYPE as 数据类型, USER_TAB_COLS.DATA_LENGTH as 长度, USER_TAB_COLS.NULLABLE as 是否为空, case when (SELECT col.column_name FROM user_constraints con, user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = '表名' and col.column_name = USER_TAB_COLS.COLUMN_NAME) = USER_TAB_COLS.COLUMN_NAME THEN 'Y' ELSE 'N' END as 是否主键, case when (select count(*) from user_constraints con, user_cons_columns col, (select t2.table_name,t2.column_name,t1.r_constraint_name from user_constraints t1,user_cons_columns t2 where t1.r_constraint_name=t2.constraint_name and t1.table_name='表名') r where con.constraint_name=col.constraint_name and con.r_constraint_name=r.r_constraint_name and con.table_name='表名' and col.column_name = USER_TAB_COLS.COLUMN_NAME) > 0 then 'Y' else 'N' END as 是否外键 , user_col_comments.comments as 备注 FROM USER_TAB_COLS inner join user_col_comments on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME and user_col_comments.COLUMN_NAME=USER_TAB_COLS.COLUMN_NAME and USER_TAB_COLS.TABLE_NAME = '表名' ORDER BY USER_TAB_COLS.COLUMN_ID;	查询主外键
select * from sysindexes where name=ltrim(rtrim(@IndexName))	select * from user_indexes where table='表名'	查询索引

 注意事项

1语句后一定要加分号[;]

2批量运行过程时,过程结束后要使用[/]

3 select *,0 t ,0 as s from t1;
  　一定要改成[select a.*,0 t ,0 as s from t1  a;]这种方式,使用别名.

4对于字符串(原有类似char(2)这样的字段时要注意)进行比较时最好加rtrim()进行
    where rtrim(char)=rtrim(char)

5 insert tablename(field) select 'value' 应改为 insert into tablename(field) select 'value' from dual;

6 select top 1 * from table where ... 改成 select * from table where rownum=1;

7 动态调用SQL:EXECUTE IMMEDIATE 'select * from table';

8 cast(sum(pricevalue) as dec(12,2)) 转换成 round(to_number('12.3456'),2)

9 格式化字符串   TO_CHAR('345','000000'); --输出000345;

10 CASE语句的注意  不能像sqlserver[case fieldname when value1 then ... when value2 then ... else ... end ]
      而是要[case when fieldname=value1 then ... when fieldname=value2 then ...else ... end ]

11日期格式化：原理得到年月自己拼
11.1 to_char得到年月日。拼成yyy.mm.dd
11.2 .to_date，在转换成日期格式 to_date(to_char(v_CheckDate,'yyyy')||'.'||to_char(v_CheckDate,'mm')||'.'||to_char(v_CheckDate,'dd'),'YYYY.MM.DD')

13测试时打印输出
begin
dbms_output.put_line(to_char(sysdate,'yyyy'));
end;

14判断有无数据的方式

declare
 v_temp     NUMBER(1, 0) := 0;
 v_strTableName VARCHAR2(32):='C2';
begin
  select 1 into  v_temp from dual
   where exists ( SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME = v_strTableName);
    EXCEPTION WHEN OTHERS-- NO_DATA_FOUND
       THEN NULL;
  dbms_output.put_line( v_temp);
end;

 15for循环

begin
    for x in reverse 1..10 loop -- reverse大到小
        DBMS_OUTPUT.PUT_LINE('内:x='||x);
    end loop;
   
    DBMS_OUTPUT.PUT_LINE('endget');
end;
 

 16PLSQL乱码 修改两个注册表的字符集

---查看服务器 ---字符集 select * from V$NLS_PARAMETERS

select userenv('language') from dual;

HKEY_LOCAL_MACHINESOFTWAREORACLEKEY_OraDb10g_home1]下NLS_LANG
HKEY_LOCAL_MACHINESOFTWAREORACLEHOME0]下NLS_LANG

---如果修改了还不行那么看看系统变量里面时候有写

17异常处理的 

 异常错误信息名称                说明
 
ORA-0001 Dup_val_on_index 违反了唯一性限制
 
ORA-0051 Timeout-on-resource 在等待资源时发生超时
 
ORA-0061 Transaction-backed-out 由于发生死锁事务被撤消
 
ORA-1001 Invalid-CURSOR 试图使用一个无效的游标
 
ORA-1012 Not-logged-on 没有连接到ORACLE
 
ORA-1017  Login-denied 无效的用户名/口令
 
ORA-1403 No_data_found SELECT INTO没有找到数据
 
ORA-1422 Too_many_rows SELECT INTO 返回多行
 
ORA-1476 Zero-divide 试图被零除
 
ORA-1722 Invalid-NUMBER 转换一个数字失败
 
ORA-6500 Storage-error 内存不够引发的内部错误
 
ORA-6501 Program-error 内部错误
 
ORA-6502 Value-error 转换或截断错误
 
ORA-6504 Rowtype-mismatch 宿主游标变量与 PL/SQL变量有不兼容行类型
 
ORA-6511 CURSOR-already-OPEN 试图打开一个已处于打开状态的游标
 
ORA-6530 Access-INTO-null 试图为null 对象的属性赋值
 
ORA-6531 Collection-is-null 试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上
 
ORA-6532 Subscript-outside-limit 对嵌套或varray索引得引用超出声明范围以外
 
ORA-6533 Subscript-beyond-count 对嵌套或varray 索引得引用大于集合中元素的个数.
 


EXCEPTION
   WHEN first_exception THEN  <code to handle first exception >
   WHEN second_exception THEN  <code to handle second exception >
   WHEN OTHERS THEN  <code to handle others exception >
END;

标准的错误处理
DECLARE
   v_empno employees.employee_id%TYPE := &empno;
   v_sal   employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno;
   IF v_sal<=1500 THEN 
        UPDATE employees SET salary = salary + 100 WHERE employee_id=v_empno; 
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');     
   ELSE
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN  
      DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END; 
以上可以看出，可以放在跟踪最后