oracle常用语句（长期更）

1. 建表：

create table SF_JLSF_T
(
  bh     NUMBER not null,(实体类中是private Long bh;)
  yhbh   NUMBER not null,
  jssj   VARCHAR2(20),
  fylb   VARCHAR2(60),
  jfrq   DATE default SYSDATE not null,
  jfje   NUMBER default 0 not null,
)
// Add comments to the columns 
comment on column SF_JLSF_T.bh
  is '编号';
comment on column SF_JLSF_T.yhbh
  is '用户编号';
comment on column SF_JLSF_T.jssj
  is '结算时间';
comment on column SF_JLSF_T.fylb
  is '费用类别';
comment on column SF_JLSF_T.jfrq
  is '交费日期';
comment on column SF_JLSF_T.jfje
  is '交费金额';

 2. 添加主键：

alter table SF_JLSF_T
  add primary key (BH);

3. 创建序列：

create sequence SF_JMSF_S
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

4. 触发器：

CREATE OR REPLACE TRIGGER SF_ZKLBH_TR
    BEFORE INSERT ON SF_ZKL_T
    FOR EACH ROW
DECLARE
    // local variables here
BEGIN
    SELECT SF_ZKL_S.NEXTVAL INTO :NEW.BH FROM DUAL;
END SF_ZKLBH_TR;

5. 给已经存在的表增加一列：

alter table 表名 add 列名 varchar(20) ;
alter table a1 add age number;

6.  给已经存在的表修改属性：

alter table SF_YHZK_T modify zkzt varchar(10) default '启用';

7. 创建DBLink：

（192.168.1.5 是其他服务器所在的IP）       

-- Create database link 
create database link QDDY
  connect to  username  identified by password 
  using '192.168.1.5:1521/oracle';

8. Oracle密码过期：

--将密码有效期由默认的180天修改成“无限制”：
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 
--修改之后不需要重启动数据库，会立即生效。
--修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示；已经被提示的帐户必须再改一次密码，举例如下
sqlplus / as sysdba 
sql> alter user smsc identified by <原来的密码> ----不用换新密码 
sql> alter user system identified by root; 
sql> alter user scott identified by tiger; 

9. 存储过程调试的时候日期类型填入2017/7/29形式即可(主要是和操作系统日期右下角的格式对应)。
10. 存储过程单步调试无法进入的时候：
右键存储过程，选择ADD DEBUG INFORMATION(添加调试信息)
11. oracle用户被锁定the account is locked的解决办法：

ALTER USER CHARGE_TAIDA ACCOUNT UNLOCK;
ALTER USER CHARGE_TAIDA IDENTIFIED BY CHARGE_TAIDA;

12. Oracle的SQL中

<> 
!= 
~= 
^=

都是不等于的意思。但是记住：null只能通过is null或者is not null来判断，其它操作符都会过滤掉null的数据。 

13. SQLCODE和SQLERRM关键词：

EXCEPTION
  when errorException then
       errorCode := SQLCODE;  
       errorMsg := SUBSTR(SQLERRM, 1, 200);
       dbms_output.put_line(errorCode || ',' || errorMsg);   
  WHEN OTHERS THEN    
    errorCode := SQLCODE;  
    errorMsg := SUBSTR(SQLERRM, 1, 200); 
    dbms_output.put_line(errorCode || ',' || errorMsg); 
    V_RESULT     := -1;
    V_RESULT_MSG :=errorMsg;-- '调用存储过程发生异常';
    ROLLBACK;
END;

14. oracle中函数编译无报错信息时，可通过以下语句查询：

select * from SYS.USER_ERRORS where NAME = <object_name> and type = <object_type>
--记住这里的参数都要大写即可，如：
select * from SYS.USER_ERRORS where NAME = 'SF_GETYHBM_DY_F';

15.存储过程循环：

V_YEAR := TO_NUMBER(SUBSTR(V_CNQ,0,4));
  LOOP
     I:=1+I;
     EXIT WHEN I >= V_NX;
   END LOOP;    

 
16.存储过程输出：

dbms_output.put_line(sqlerrm); 

17.Oracle中文排序：
    当然，Oracle也提供了按照中文拼音排序，按照部首或笔画排序，方式如下：
    

按照拼音顺序:
    ORDER BY nlssort(NAME, 'NLS_SORT=SCHINESE_PINYIN_M')
按照部首顺序:
    ORDER BY nlssort(NAME, 'NLS_SORT=SCHINESE_RADICAL_M')
按照笔画顺序:
    ORDER BY nlssort(NAME, 'NLS_SORT=SCHINESE_STROKE_M')

18. Oracle查询小数位数是两位数以上的数据：

select * from tab where col <> trunc(col * 100) / 100;  

19.Oracle函数格式：

（1）有参数：

create or replace function GET_JBCNFL(  -- 只查当前采暖期,目前只考虑了两种 供暖方式,如果有增减要改代码
       V_MJBHS     VARCHAR2
) return varchar2 is
  Result SF_CS_T.CSZ%TYPE; -- SF_CS_T.CSZ%TYPE
  V_GNFS SF_JMMJ_T.GNFS%TYPE;
  V_CSMC SF_CS_T.CSMC%TYPE;
  V_CSZ  SF_CS_T.CSZ%TYPE;
  V_SQL    VARCHAR2(2000);
  TYPE cur_type IS REF CURSOR;
  cur cur_type;
begin
  Result := '';
  V_SQL := 'SELECT DISTINCT CSZ,REPLACE(CSMC,''基本采暖费率-'','''') FROM SF_CS_T WHERE CSMC IN(';
  V_SQL := V_SQL || ' SELECT DISTINCT REPLACE(' || '''基本采暖费率-''' || '|' || '|' || 'GNFS' || ',' || '''-普通方式''' || ','''') FROM SF_JMMJ_T ';
  V_SQL := V_SQL || ' WHERE BH IN (' || V_MJBHS || '))';

  Open cur for V_SQL;
  loop
       fetch cur into V_CSZ,V_GNFS;
       exit when cur%notfound;
       IF V_GNFS='地热方式' THEN
         Result := Result || '地热方式:' || V_CSZ;
       ELSE
         Result := Result || '普通方式:' || V_CSZ;
       END IF;
       Result := Result || ',';
  end loop;
  close cur;


  return(Result);
end GET_JBCNFL;

（2）无参数：

CREATE OR REPLACE FUNCTION SF_DQCNQ_F
RETURN VARCHAR2
IS
  V_CNQ  VARCHAR2(10);
BEGIN
  SELECT MAX(CNQ) MAXCNQ INTO V_CNQ FROM SF_CS_T WHERE ZF = 0;
  RETURN V_CNQ;
END SF_DQCNQ_F;

20.PLSQL Developer打开oralce字段时报“无效的窗口句柄”的问题，与系统的打印服务有关，只要将“Print Spooler"服务启动即可