Oracle SQL statements

---------------------------------------------------------------------------
To view SQL statements running in Oracle
Select Last_load_time, First_load_time, Sql_text from V$sql
where RowNum < module= ' W3wp.exe '
ORDER BY last_load_time Desc

---------------------------------------------------------------------------
View data modification Records
Select Object_name, created,last_ddl_time from user_objects order BY last_ddl_time Desc

---------------------------------------------------------------------------
Time formatting
To_date (' 2014/5/22 20:00:35 ', ' yyyy-mm-dd hh24:mi:ss ')

--add (adding views, tables, stored procedures, functions)
CREATE TABLE "Base_managerange" (
"Base_managerangeoid" VARCHAR2 (+) not NULL,
"Rangecode" VARCHAR2 (40),
"Rangename" VARCHAR2 (300),
"Parentrangeid" VARCHAR2 (40),
"Ismultilingual" INT,
"KEYID" VARCHAR2 (+) not NULL,
"Level" INT,
"REMARK" VARCHAR2 (100),
CONSTRAINT pk_base_managerange PRIMARY KEY ("Base_managerangeoid")
);

--update (Modify table structure, data)
ALTER table (table name) ADD (column name data type);
ALTER table (table name) MODIFY (column name data type);
ALTER table (table name) RENAME column (current column name) to (new column name);
ALTER table (table name) DROP column (column name);
ALTER table (current table name) RENAME to (new table name);
e.g. UPDATE bm_buyerdata b SET b.sessionnum= ' n ' WHERE b.sessionnum= ' 117 ';

--delete (delete data, table)
DROP TABLE "Base_managerange"
DELETE from Bm_buyerdata b WHERE b.sessionnum= ' DFASDFSADF ';

--Sorting rules
Schinese_radical_m Sort by radical (first order), stroke (second order)
Schinese_stroke_m Sort by stroke (first order), radical (second order)
Schinese_pinyin_m by phonetic sorting, the default sorting method for the system is phonetic sorting
e.g. select * FROM Dept ORDER by Nlssort (name, ' Nls_sort=schinese_stroke_m ');

--processing of order-time NULL sort
1) without "taking care" of the case, we can think of those null values as the value is the largest in all content, so the ascending sort after the null value at the end, the reverse sort after the null value at the top!
2) in the case of special "care", when the "NULLS first" is specified, the null value is arranged at the top, either in ascending or descending order, and when "NULLS last" is specified, the null value is arranged in the last face, either in ascending or descending order.

--Common functions
1, multi-line consolidation for a column of Wmsys.wm_concat (col1) used under Oracle 10
Replace the default "," Delimiter, using replace (Wmsys.wm_concat (col1), ', ', '; ') ; add other content Wmsys.wm_concat (col1 | | ' Unit (' | | | col2 | | ‘)‘ )

--the problem of rownum greater than
rowID and rownum are called pseudo-columns, but they exist in a different way:
rowID is physically present, representing the unique location ID recorded in the tablespace, which is unique in the db. As long as the record has not been moved, ROWID is unchanged.
Because rownum always starting from 1, the first one does not meet the removal, the second rownum 1. And so on, so there is never a record that satisfies the condition. You can understand this: rownum is a sequence that is the order in which the Oracle database reads data from a data file or buffer.
So select * FROM table1 where rownum > 1 always get no value
The correct approach is select * FROM (select RowNum rownum_, t.* from table1 t) where rownum_ > 1

--The problem of not using order by in subqueries
Subqueries that appear in the FROM statement are called inline view (inline views), and subqueries that appear in the WHERE statement are called nested subquery (nested subqueries).
Test proves that the order BY statement is not allowed in nested subqueries
SELECT * FROM Scott.emp
where ename in (select ename from Scott.emp order by ename)
The error "ORA-00907: Missing closing parenthesis" will be reported.
If you wrap the above nested subquery in one layer, it becomes the inline view of the second-level query.
SELECT * FROM Scott.emp
where ename in (select-Ename from Scott.emp order by ename)
can be executed successfully.

--clob field Insert data
Ora-01704:string literal too long
DECLARE
STR varchar2 (32767);
BEGIN
  str: = ' {' em_taskoid ': ' da8ebe4d-cbd3-498b-9892-d9dd073aa82a ', ' TemplateID ': ' 000814c8-0000-0000-0000-0000d01e4a99 "," ContentTemplate ":" <div style=\ "Overflow:hidden; height:232px; width:700px; margin:0px auto\ "></div><table cellpadding=\" 0\ "cellspacing=\" 0\ "align=\" center \ "Border=\" 0\ "width=\" 700\ "><tbody><tr><td background=\" http://invitation.cantonfair.org.cn/ Images/sendmail/images/115best/left.jpg\ "valign=\" top\ "width=\" 100\ ">" | | ' & ' | | ' NBSP;&LT;/TD&GT;&LT;TD background=\ "http://invitation.cantonfair.org.cn/images/sendmail/images/115best/ Middle.jpg\ "height=\" 600\ "valign=\" top\ "><p style=\" text-align:center\ "><span style=\" font-family: Imitation _gb2312; Font-size:18pt\ "><span style=\" font-size:16px;\ "><span style=\" font-family:arial; Mso-fareast-font-family: Imitation _gb2312\ "><br/&Gt;</span></span></span><span class=\ "style1\" > Welcome to the 115th session of China Import and export trade fair </span></p ><p style=\ "Text-align:center\" > {{content6}}</p><p class=\ "style1\" > honorable {{c Ontent2}}: <br/> Your friend {{Content3}}, from {{CONTENT4}} to recommend China first exhibition-China Import and export trade fair. &LT;BR/> China Import and Export fair, also known as Canton Fair, was founded in the spring of 1957, each spring and autumn two seasons in Guangzhou, is China's current history is the longest, the highest level, the largest scale, the widest range of goods, businessmen to the largest and most widely distributed in the country area, the best deal effect,　 A comprehensive international trade event with the best reputation. <br/> {{content5}} The Canton Fair will be held in Guangzhou China Import and Export Fair Exhibition Hall on April 15, 2014-May 5 in three phases. This Canton Fair Total exhibition area of 1.16 million square meters, booth number 59,000. <br/> Canton Fair exhibitors are excellent brand enterprises in China, the credibility of good, at the same time Canton Fair product range, and good quality and excellent price, but also according to the needs of customers personalized design and production, it is worth your visit to purchase. <br/> Welcome you to participate in the first {{CONTENT5}} Session of Canton Fair! About the Canton Fair Overseas Buyers Online application for registration, check-in, translation services, hotel reservations and transportation information, etc., please visit the official website of Canton Fair <a href=\ "Http://www.cantonfair.org.cn/\" > Www.cantonfair.org.cn</a> or consult the Canton Fair Customer Contact Center, e-mail: [email protected], Tel: 4000888999 (within China), 86-20-28888999 (outside China). <br/> Export Zone: Phase I April 2014 15-19th: electronics and appliances; lighting; vehicles and accessories; machinery; hardware tools; building materials; chemical products. Phase II April 2014 23-27th: Consumer goods, gifts, home decorations. Phase III May 1, 2014-May 5: Textile and apparel, shoes, office, luggage and leisure products, medicine and medical care, food. <br/> Import Zone: The first phase April 2014 15-19th: electronic and electrical appliances, building materials and hardware, machinery and equipment, industrial raw materials and so on. Phase III May 1, 2014 – May 5: Food and agricultural products, health care and beauty care, gifts and decorations. &LT;BR/> Exhibition arrangement information as of March 26, 2014, the latest news please The Canton Fair official website <a href=\ "http://www.cantonfair.org.cn\" > Www.cantonfair.org.cn</a> release is subject to publication. <br/> Please bring this invitation email and valid overseas ID card when you attend the meeting, and you will enjoy the free first entry voucher. </p><span><span style=\ "font-size:16px;\" ><span style=\ "font-family:times New Roman;\" > </span></span></span><p class=\ "msonormal\" style=\ "text-align:left; margin:0cm 0cm 0pt; text-indent:21pt; Mso-margin-top-alt:auto; Mso-margin-bottom-alt:auto; Mso-pagination:widow-orphan; Mso-char-indent-count:2.0\ "align=\" left\ "></p></td><td background=\"/http/ Invitation.cantonfair.org.cn/images/sendmail/images/115best/right.jpg\ "valign=\" top\ "width=\" 103\ ">" | | ' & ' | | ' Nbsp;</td></tr></tbody></table><div class=\ "cc\" style=\ "WIDTH:702PX; margin:0px Auto; Heihgt:128px\ "><div style=\" Height:128px; width:111px; Float:left\ "></div><div class=\" fl\ "style=\" HEIGHT:128PX; width:590px; Float:left\ "><div style=\" height:19px\ "></div><div class=\" cc\ " Style=\ "height:90px\" ><div style=\ "height:90px; width:275px; Background:url ([Basecodebar]); Float:left\ ">{{content1}}</div><div style=\" height:90px; width:314px; Float:left\ "></div></div><div></div></div></div> "," subjecttemplate ":" Old buyers recommend new buyers "," Ifsubmit ": false," MAILSEQ ": null," Maillistno ":" 185539 "," mpid ": null," Mptempid ": null," MPACCID ": null," HTML ": null," FileName ":" 20140725173024.txt "," Mptitle ": null," Replyemail ":" [email protected] "," Replyname ":" [email protected] "," Language ":" ZH "} ';
UPDATE em_emaitask SET sys_last_upd=sysdate, submitresult=str WHERE em_emaitaskoid= ' DA8EBE4D-CBD3-498B-9892-D9DD073AA82A ';
END;
Analysis: Assign a string with a length of more than 4000 to a variable, and then in the update or insert

--&nbsp; for variable nbsp in Oracle; Problem solving
Update table1 Set col1 = ' Text ' | | ' & ' | | ' Test1 '
Analysis: Oracle uses & As a custom variable or ASCII Chr (38)

--processing of single quotation marks
Select ' test ' from dual;
Select ' It ' | | Chr (39) | | ' Fine ' from dual;
Analysis ' is equivalent to a single quote, and Chr (39) is an ASCII code with single quotes

--String Type field, the value if all is a number, when using order by sort, need To_number (Column1)
Your column is a string, not an int, sorted by Ascⅱ code, not converted to a number, then a similar effect is
Column1
----
9
80
700
10000

--Decode (field or Field operation, value 1, value 2, value 3)
The result of this function is that when the value of the field or Field operation is equal to the value 1 o'clock, the function returns a value of 2, otherwise the value 3, the value 1, the value 2, and the value 3 can also be an expression.

--Rebuilding the index
Alter index INDEX_NAME rebuild tablespace tablespace_name
"Tablespace_name" joins the table space name, which moves the specified index into the specified table space.
Note:
The analyze operation is just a statistic, and the statistics are stored for later analysis of SQL use, not for concrete implementations such as rebuilds, so to rebuild the index
Or do you want to use ALTER INDEX INDEX_NAME rebuild
Errors that may be caused:
Unable to delete data, prompting "ORA-01502: Index ' table.index ' or partition of such index is in an unusable state"

--------------------------------------------------------------------------------------------
--use of the plus sign in Oracle
1. OUTER JOIN: Left Outer Association
SELECT E.last_name, e.department_id, D.department_name
From Employees E
Left OUTER JOIN Departments D
On (e.department_id = d.department_id);

Equivalent to
SELECT E.last_name, e.department_id, D.department_name
From Employees e, departments D
WHERE e.department_id=d.department_id (+);

2. Right OUTER JOIN: Left Outer Association

SELECT E.last_name, e.department_id, D.department_name
From Employees E
Right OUTER JOIN Departments D
On (e.department_id = d.department_id);

Equivalent to

SELECT E.last_name, e.department_id, D.department_name
From Employees e, departments D
WHERE e.department_id (+) =d.department_id;

--------------------------------------------------------------------------------------------
The speed of like

Can be replaced with InStr to increase speed

----------------------------------------------------
InStr usage
InStr (string1, string2, Start_position,nth _appearance) [1]  [2]
string1
Source string to look up in this string.
string2
The string to find in string1.
Start_position
Represents the location of the string1 to begin the lookup. This parameter is optional if omitted by default to 1. The string index starts at 1. If this parameter is positive, it is retrieved from left to right, and if this parameter is negative, right-to-left, returns the starting index of the string to find in the source string.
Nth_appearance
Represents the string2 to find the first occurrence. This parameter is optional, and if omitted, the default is 1. If the system is negative, it will give an error.
Note:
The location index number starts at 1.
If String2 is not found in String1, the InStr function returns 0.　　
Example:
Select InStr (' Syranmo ', ' s ') from dual;--return 1
SELECT InStr (' Syranmo ', ' RA ') from dual;--return 3
SELECT InStr (' Syran Mo ', ' a ', up to) from dual; --Returns 0

--------------------------------------------------------------------------------------------
EXISTS and in
1, the use of exists
The subquery result set in EXISTS is not empty, and the value of the EXISTS () clause is true;
The subquery result set in EXISTS is empty, and the value of the EXISTS () clause is false.
e.g. select * from T1 where exists (select 1 from T2 where t1.a=t2.a)
2. Performance analysis
When the data volume is large, use exists when the data is small, in
By using Exists,oracle, you first check the main query and then run the subquery until it finds the first match, which saves time. When Oracle executes an in subquery, it executes the subquery first and stores the resulting list in an indexed temporary table. Before executing a subquery, the system first suspends the primary query, and executes the subquery until it has been executed, and then performs the main query after the query is held in the temporary table. This is why using exists is faster than using in usually queries

Oracle SQL statements