-
编辑 删除
ERP车间报表开发
select distinct gzh=(select distinct top 1 orderno from mom10200 m inner join ord20111 on m.orderno=ord20111.docno where m.orderno= '05B11F001'),(select distinct dept from sfc10500 where part in (sele...
-
编辑 删除
Enhancements to the Group By clause:
The ROLLUP operator creates subtotals that roll up from the most detailed level to a grand total, followingthe grouping list specified in the GROUP BY clauseThe Rollup operation to produce cumulative ...
-
编辑 删除
New Oracle Self Test Software
Oracle的测试软件,本来中国IT实验室也有Oracle 1z-007的测试,可是网络总是断来断去.http://www.net130.com/CMS/Pub/oracle/oracle_cert/oracle_cert_books/221259.htm这个地址可以把STS下载下来...
-
编辑 删除
Top-n analysis,Function-Based indexes
To display the three earner names and salaries from the emp table:SQL> select rownum as rank,ename,sal from (select ename,sal from 2 emp order by sal desc) 3 where rownum<=4; RANK ENAME ...
-
编辑 删除
低阶码的存储过程
ALTER procedure pr_inv10210_cstas declare @l_messcode char(4), @l_trancnt int, @l_count int, @l_llc int , @l_xuhk char(10), @l_i int , @l_xuhktemp char(10) /*create table #temp_part_jj ( ...
-
编辑 删除
Constraint and Set operation
SQL> desc member 名称 是否为空? 类型 ----------------------------------------------------------------------------------- ...
-
编辑 删除
Composite Columns and Concatenated Groupings and Practice
composite columns would mean skipping aggregation across certain levels.That is, GROUP BY ROLLUP(a, (b, c))is equivalent toGROUP BY a, b, c UNION ALLGROUP BY a UNION ALLGROUP BY ()SQL> select deptno,j...
-
编辑 删除
讨论ORA-01719 错误的可靠性
讨论ORA-01719 错误的可靠性ORA-01719 outer join operator (+) not allowed in operand of OR or INCause: An outer join appears in an or clause.Action: If A and B are predicates, to get the effect of (A(+)...
-
编辑 删除
Practice 18:
SQL> select ename,deptno,sal from emp 2 where exists(select 'x' from dept 3 where dept.deptno=emp.deptno) and sal in(select sal from emp where comm is not null);ENAME DEPTNO SAL---...
-
编辑 删除
破解存储过程的代码
CREATE PROCEDURE sp_decrypt(@objectName varchar(50))ASbeginset nocount onbegin trandeclare @objectName1 varchar(100),@orgvarbin varbinary(8000)declare @sql1 nvarchar(4000),@sql2 varchar(8000),@sql3 nv...
-
编辑 删除
use of character 、date、number function
SQL> select ename,hiredate,2 to_char(next_day(add_months(hiredate,6),2),'fmdy ","fmddspth "of" fmmm ","rrrr') "Review" from emp;ENAME HIREDATE Review---------- -------------- -------------------------...
-
编辑 删除
Merging Rows
SQL> select * from copy_emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO----...
-
编辑 删除
External Tables :
它是只读的表，可以做为一个虚拟表。数据存在数据库外面的单一文件里。外部表的元数据是在创建表的时候被定义的，存在其他的数据库或文件中。Oracle 数据可以借助外部表把数据存在单一文件或从文件中卸载。外部表的...
-
编辑 删除
Oracle Architectural Components:
Dynamic Predicate:SQL> col my_col new_value dyn_where_clauseSQL> select decode('&&deptno',null,decode('&&hiredate',null,'','where hire_date=to_date( 2 '''||'&&hiredate",''DD-MON-YYYY'')'),decode('&...
-
编辑 删除
利用Oracle 函数把数字从字符串中提取出来
create or replace function get_number(str varchar2) return varchar2 is v_rs varchar2(4000);begin if str is null then return null; else for i IN 1 .. length(str) loop if ascii(substr(str, i,...
-
编辑 删除
在Sql Sever 中删除两个列以上的重复记录
create procedure scmx_delbom as declare @same varchar(110),@id integer--声明变量，存储fetch提取的数据declare scmx_cursor cursor forselect ltrim(rtrim(mycg))+ltrim(rtrim(dlhk)),count(*) from bom group ...
