-
编辑 删除
Recipe 6.10. Creating a Delimited List from Table Rows
SELECT LPAD(' ', 1*level-1)||SYS_CONNECT_BY_PATH(ename, '/') "Path" FROM emp START WITH ename = 'SCOTT' CONNECT BY PRIOR empno = mgr;
-
编辑 删除
Recipe 6.7. Extracting Initials from a Name
1. translate把要改的挑出2.replace掉You want convert a full name into initials. Consider the following name: Stewie Griffin You would like to return: S.G. select replace ( transl...
-
编辑 删除
Recipe 6.1. Walking a String
Recipe 6.1. Walking a StringProblemYou want to traverse a string to return each character as a row, but SQL lacks a loop operation. For example, you want to display the ENAME "KING" fro...
-
编辑 删除
Recipe 5.6. Using SQL to Generate SQL
select 'select count(*) from '||table_name||';' cnts from user_tables;CNTS----------------------------------------select count(*) from ANT;select count(*) from BONUS;select count(*) ...
-
编辑 删除
Recipe 5.2. Listing a Table's Columns
OracleQuery ALL_TAB_COLUMNS:1 select column_name, data_type, column_id2 from all_tab_columns3 where owner = 'SMEAGOL'4 and table_name = 'EMP'
-
编辑 删除
Recipe 4.11. Merging Records
1 merge into emp_commission ec2 using (select * from emp) emp3 on (ec.empno=emp.empno)4 when matched then5 update set ec.comm = 10006 delete where (sal < 2000)7 wh...
-
编辑 删除
Recipe 4.10. Updating with Values from Another Table
Problemselect * from new_salDEPTNO SAL------ ---------- 10 4000Column DEPTNO is the primary...
-
编辑 删除
Recipe 4.7. Blocking Inserts to Certain Columns
ProblemYou wish to prevent users, or an errant software application, from inserting values into certain table columns. For example, you wish to allow a program to insert into EMP, but only into the E...
-
编辑 删除
Recipe 3.1. Stacking One Rowset atop Another
ENAME_AND_DNAME DEPTNO--------------- ----------CLARK 10KING 10MILLER 10----------ACCOUNTING 10RESEARCH ...
-
编辑 删除
Recipe 1.10. Returning n Random Records
select * from ( select ename, job from emp order by dbms_random.value() )&...
-
编辑 删除
Recipe 2.6. Sorting on a Data Dependent Key
select ename,sal,job,commfrom emporder by case when job = 'SALESMAN' then comm else sal end
-
编辑 删除
Recipe 2.4. Sorting Mixed Alphanumeric Data
translate and replace
