-
编辑 删除
Oracle Concepts - pfile and spfile
Introducing the PFILE and SPFILEOracle provides two different types of mutually exclusive parameter files that you can use, PFILE and SPFILE. Let’s look at the PFILE and the SPFILE in a bit more det...
-
编辑 删除
查询正常作为条件报错的问题
今天在论坛上看到一个有趣的问题：http://www.itpub.net/683461.html将函数调用放到SELECT列表中可以执行，如果放到WHERE条件中就会报错。查询正常作为条件报错的问题（续）：http://yangtingkun.itpub.net/post/4...
-
编辑 删除
数据库建表原则
数据库建表原则收藏 1. 原始单据与实体之间的关系 可以是一对一、一对多、多对多的关系。在一般情况下，它们是一对一的关系：即一张原始...
-
编辑 删除
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...
-
编辑 删除
ORA-01779
Oracle中试图对一个子查询进行更新时可能会出现ORA-01779错误。该错误的内容为：ORA-01779: cannot modify a column which maps to a non-key-preserved table例如，使用以下的更新查询就会出现该错误。CREATE TABL...
-
编辑 删除
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
-
编辑 删除
sql*plus 常用命令
SQLPLUS> clear screenSQLPLUS> set timing on
-
编辑 删除
1. simple search
1. simple search (only one table)Table 2-1. Search Condition Point Counts for OperatorsOperator Points = 10 > 5 >= 5 < 5 <= 5 LIKE 3 <> 0 Table 2-2. Search C...
-
编辑 删除
where exists
Where exists (subquery)The WHERE EXISTS subquery is used when we want to display all rows where we have a matching column in both tables. In most cases, this type of subquery can be re-written w...
-
编辑 删除
动态表名
declare tablename1 number(3);STRSQL varchar2(30);TABLENAME2 varchar2(20);beginTABLENAME2 := 'dept';STRSQL :='SELECT COUNT(*) FROM '||TABLENAME2;EXECUTE IMMEDIATE STRSQL INTO tablename1;dbms_output.put...
