-
编辑 删除
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...
-
编辑 删除
动态表名
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...
-
编辑 删除
record
Recordsa record is composed of one or more fields. There are three different ways to define a record:1. Table-based recordCREATE TABLE books ( book_id &n...
-
编辑 删除
string
单引号两种转义select 'Aren''t you glad you''re learning PL/SQL?'from dualselect q'!Aren't you glad you're learning PL/SQL?!'from dual--! @ 等select q'!Aren''t you glad you're learning PL/SQL?!'from du...
