哈哈__哈哈的专栏

[code="java"]CREATE TABLESPACE "CRM_DB" LOGGING DATAFILE 'D:\oracle\product\10.2.0\oradata\crm\crm_db.ora' SIZE 1024M;CREATE USER "CRM" PROFILE "DEFAULT" IDENTIFIED BY "P" D...

第一个简单的存储过程：create or replace procedure update_sal(id number, name varchar2) isv_sqlcode number;v_sqlerrm varchar(1000);begin insert into testa (id, name) values (id, name); commit;exce...

  select AUNAME from CLUB_ADMIN_USER group by AUNAME having count(AUNAME) > 2;  

软件环境： 1、Windows XP + Oracle 10g 2、Oracle安装路径为：d:\Oracle实现方法：1、开始－＞设置－＞控制面板－＞管理工具－＞服务停止所有Oracle服务； 2、开始－＞程序－＞Oracle – OraDb10g_home1＞Oracle Installation Products－＞ Universal Installer 卸装所有Ora...

 11g导出数据的时候，空表导不出来，所以要检查空表 select table_name from user_tables where NUM_ROWS=0;  

 SELECT * FROM t aWHERE length(translate(a.rn,'0123456789'||a.rn,'0123456789'))=length(a.rn)  

用SQL命令行：job:variable job number;begin sys.dbms_job.submit(job => :job, what => 'clearseq(''SEQ_SN_ID'');', next_date => to_date('16-10-2...

其中,data.*是一张表，然后把其他表中的字段也加到sal_data的游标中 cursor sal_data(cp_center_temp_id varchar2) is select data.*, post.id emp_post_id, doc.Salary_Tax_Bd sa...

WITH t AS( SELECT 1 AS sno, 1 AS cno, 5 AS grade FROM dual UNION ALL SELECT 1, 2, 6 FROM dual UNION ALL SELECT 1, 3, 10 FROM dual UNION ALL SELECT 2, 3, 10 FRO...

直接上代码： 是在包里写的存储过程，要测试的话，要先写声明  声明procedure test1(p_RETCODE in out varchar2, p_retInfo in out varchar2); procedure test2(p_RETCODE in out varchar2, p_retInfo in out varchar2); procedure t...

转的：  今天上班，发现一个同事用oracle的sys_guid()来做从一个表中随机获取6条记录：select * from (select * from tablename order by sys_guid()) where rownum < 7;看得出来是先创建guid，然后将表记录按照这个guid排序，再从中取头6条。数据库主键用guid我倒是见过，这样用作...

 oracle添加某个字段，并添加注释： alter table CLUB_HOT_LEADS drop column CHLACTIVITYTYPE1;  ALTER TABLE T1 ADD (A1 VARCHAR(600));comment on column T1.A1 is '我是A1A1A1'; ...

解锁scott账户先用DBA进入SQLPLUS[code="java"]sqlplus sys/orcl as sysdba[/code]然后[code="java"]alert user scott account unlock;conn scott/tiger[/code]这个时候就用scott账户进入了orcl数据库了...

oracle获取随机记录[code="java"]select * from (select * from ford_car order by dbms_random.value ) where rownum < 1001[/code]

[code="sql"]insert into test1 (TID, TNAME)select * from (select 1,11 from dual union allselect 2,22 from dual union allselect 3,33 from dual)[/code]这段代码会出错，因为有2个相同的值在里面，必须加别名 as **[cod...

有的时候要分析别人的数据库但看表不能很好的体现业务逻辑所以可以用PowerDesigner的反向工程来解决 这里用mysql 来举例子，因为pd中没有mysql的odbc，得用Connection Profiles来解决。 新建一个工作空间-&gt;数据库-&gt;Configure Connections-&gt;Connection Profiles-&gt;Add D...

TO_DATE('2011-02-22 10:38:50','YY-MM-DD HH24:MI:SS')

[code="java"]oracle中重复记录只取其中一条select * from table1 where rowid=any(select max(rowid) from table1 group by field1)或者select * from table1 where rowid in (select max(rowid) from table1 group ...

[color=red]转的[/color]exp本地导出与imp本地导入exp命令：1 exp username/psw@TEST file=d:test.dmp full=y2 exp username/psw@TEST file=d:test.dmp owner=(ly)3 exp username/psw@TEST file= d:test.dmp tables=...

如果a.Num为null，那么就取后面的0nvl(a.Num_Assign, 0) tot_dis 百分比，并取2位小数点百分比，并取2位小数点to_char(round(sum(total.tot_followup) / sum(total.tot_dis) * 100, 2)) || '%' sum_tot_rete   ...

上个月做的报表记下这个SQL文，以后还要用。select a.chldate, nvl(a.Num_Assign, 0) tot_dis, --累计分配量 nvl(b.Num_Followup, 0) tot_followup, --累计跟进量 to_char(round((nvl(c.sthours, 0) + nvl(d.tfhour...

没研究出来oracle exception的传播  procedure test1(p_RETCODE in out varchar2, p_retInfo in out varchar2); procedure test2(p_RETCODE in out varchar2, p_retInfo in out varchar2); procedure test3(p_RET...