oracle常用语句（长期更）

1. 建表&＃xff1a;

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.bhis &＃39;编号&＃39;;
comment on column SF_JLSF_T.yhbhis &＃39;用户编号&＃39;;
comment on column SF_JLSF_T.jssjis &＃39;结算时间&＃39;;
comment on column SF_JLSF_T.fylbis &＃39;费用类别&＃39;;
comment on column SF_JLSF_T.jfrqis &＃39;交费日期&＃39;;
comment on column SF_JLSF_T.jfjeis &＃39;交费金额&＃39;;

 2. 添加主键&＃xff1a;

alter table SF_JLSF_Tadd primary key (BH);

3. 创建序列&＃xff1a;

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

4. 触发器&＃xff1a;

CREATE OR REPLACE TRIGGER SF_ZKLBH_TRBEFORE INSERT ON SF_ZKL_TFOR EACH ROW
DECLARE// local variables here
BEGINSELECT SF_ZKL_S.NEXTVAL INTO :NEW.BH FROM DUAL;
END SF_ZKLBH_TR;

5. 给已经存在的表增加一列&＃xff1a;

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

6.  给已经存在的表修改属性&＃xff1a;

alter table SF_YHZK_T modify zkzt varchar(10) default &＃39;启用&＃39;;


7. 创建DBLink&＃xff1a;

&＃xff08;192.168.1.5 是其他服务器所在的IP&＃xff09;       

-- Create database link
create database link QDDYconnect to username identified by password using &＃39;192.168.1.5:1521/oracle&＃39;;

8. Oracle密码过期&＃xff1a;

--将密码有效期由默认的180天修改成“无限制”&＃xff1a;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
--修改之后不需要重启动数据库&＃xff0c;会立即生效。
--修改后&＃xff0c;还没有被提示ORA-28002警告的帐户不会再碰到同样的提示&＃xff1b;已经被提示的帐户必须再改一次密码&＃xff0c;举例如下
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. 存储过程单步调试无法进入的时候&＃xff1a;
右键存储过程&＃xff0c;选择ADD DEBUG INFORMATION(添加调试信息)
11. oracle用户被锁定the account is locked的解决办法&＃xff1a;

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

12. Oracle的SQL中

<>
!&＃61;
~&＃61;
^&＃61;

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

13. SQLCODE和SQLERRM关键词&＃xff1a;

EXCEPTIONwhen errorException thenerrorCode :&＃61; SQLCODE; errorMsg :&＃61; SUBSTR(SQLERRM, 1, 200);dbms_output.put_line(errorCode || &＃39;,&＃39; || errorMsg); WHEN OTHERS THEN errorCode :&＃61; SQLCODE; errorMsg :&＃61; SUBSTR(SQLERRM, 1, 200); dbms_output.put_line(errorCode || &＃39;,&＃39; || errorMsg); V_RESULT :&＃61; -1;V_RESULT_MSG :&＃61;errorMsg;-- &＃39;调用存储过程发生异常&＃39;;ROLLBACK;
END;

14. oracle中函数编译无报错信息时&＃xff0c;可通过以下语句查询&＃xff1a;

select * from SYS.USER_ERRORS where NAME &＃61; and type &＃61;
--记住这里的参数都要大写即可&＃xff0c;如&＃xff1a;
select * from SYS.USER_ERRORS where NAME &＃61; &＃39;SF_GETYHBM_DY_F&＃39;;

15.存储过程循环&＃xff1a;

V_YEAR :&＃61; TO_NUMBER(SUBSTR(V_CNQ,0,4));LOOPI:&＃61;1&＃43;I;EXIT WHEN I >&＃61; V_NX;END LOOP;    

 
16.存储过程输出&＃xff1a;

dbms_output.put_line(sqlerrm); 

17.Oracle中文排序&＃xff1a;
    当然&＃xff0c;Oracle也提供了按照中文拼音排序&＃xff0c;按照部首或笔画排序&＃xff0c;方式如下&＃xff1a;
    

按照拼音顺序:ORDER BY nlssort(NAME, &＃39;NLS_SORT&＃61;SCHINESE_PINYIN_M&＃39;)
按照部首顺序:ORDER BY nlssort(NAME, &＃39;NLS_SORT&＃61;SCHINESE_RADICAL_M&＃39;)
按照笔画顺序:ORDER BY nlssort(NAME, &＃39;NLS_SORT&＃61;SCHINESE_STROKE_M&＃39;)

18. Oracle查询小数位数是两位数以上的数据&＃xff1a;

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

19.Oracle函数格式&＃xff1a;

&＃xff08;1&＃xff09;有参数&＃xff1a;

create or replace function GET_JBCNFL( -- 只查当前采暖期,目前只考虑了两种 供暖方式,如果有增减要改代码V_MJBHS VARCHAR2
) return varchar2 isResult SF_CS_T.CSZ%TYPE; -- SF_CS_T.CSZ%TYPEV_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;
beginResult :&＃61; &＃39;&＃39;;V_SQL :&＃61; &＃39;SELECT DISTINCT CSZ,REPLACE(CSMC,&＃39;&＃39;基本采暖费率-&＃39;&＃39;,&＃39;&＃39;&＃39;&＃39;) FROM SF_CS_T WHERE CSMC IN(&＃39;;V_SQL :&＃61; V_SQL || &＃39; SELECT DISTINCT REPLACE(&＃39; || &＃39;&＃39;&＃39;基本采暖费率-&＃39;&＃39;&＃39; || &＃39;|&＃39; || &＃39;|&＃39; || &＃39;GNFS&＃39; || &＃39;,&＃39; || &＃39;&＃39;&＃39;-普通方式&＃39;&＃39;&＃39; || &＃39;,&＃39;&＃39;&＃39;&＃39;) FROM SF_JMMJ_T &＃39;;V_SQL :&＃61; V_SQL || &＃39; WHERE BH IN (&＃39; || V_MJBHS || &＃39;))&＃39;;Open cur for V_SQL;loopfetch cur into V_CSZ,V_GNFS;exit when cur%notfound;IF V_GNFS&＃61;&＃39;地热方式&＃39; THENResult :&＃61; Result || &＃39;地热方式:&＃39; || V_CSZ;ELSEResult :&＃61; Result || &＃39;普通方式:&＃39; || V_CSZ;END IF;Result :&＃61; Result || &＃39;,&＃39;;end loop;close cur;return(Result);
end GET_JBCNFL;

&＃xff08;2&＃xff09;无参数&＃xff1a;

CREATE OR REPLACE FUNCTION SF_DQCNQ_F
RETURN VARCHAR2
ISV_CNQ VARCHAR2(10);
BEGINSELECT MAX(CNQ) MAXCNQ INTO V_CNQ FROM SF_CS_T WHERE ZF &＃61; 0;RETURN V_CNQ;
END SF_DQCNQ_F;

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