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_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1]下NLS_LANG
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0]下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;
以上可以看出,可以放在跟踪最后