The first part, SQL & PL / SQL [Q] how to query special characters, such as wildcard% and _ [a] select * from table where name like 'A / _%' escape '/'
[Q] How to insert single quotes to the database table [A] can be processed with ASCII code, other special characters such as & also, such as Insert Into T Values ('I' || CHR (39) || 'M'); - chr (39) represents characters' or use two single quotes to represent an or insert Into T Values ('I''m '); - two' 'can represent one'
[Q] How to set a transaction consistency [a] set transaction [isolation level] read committed; default sentence level consistency set transaction [isolation level] serializable; read only; transaction-level consistency
[Q] How to use the cursor update data [a] Cursor C1 Is Select * from tablename where name is null for update [of column] ... Update Tablename set column = ... WHERE CURRENT OF C1;
[Q] How do custom exception [A] pragma_exception_init (exception_name, error_number); if immediately thrown raise_application_error (error_number, error_msg, true | false); exception variable SQLCODE error which number from -20,000 to -20,999, an error message largest 2048B Code SQlerRM error message
[Q] Transformation of decimal and hexadecimal [A] 8i or above: TO_CHAR (100, 'xx') TO_NUMBER ('4D', 'XX') 8i The transformation of the following to the following script Create OR replace function to_base (p_dec in number, p_base in number) return varchar2 is l_str varchar2 (255) default nULL; l_num number default p_dec; l_hex varchar2 (16) default '0123456789ABCDEF'; begin if (p_dec is null or p_base is null) then Return NULL; END IF; IF (Trunc (p_dec) <> p_dec or p_dec <0) THEN RAISE Program_ERROR; END IF; LOOP L_STR: = SUBSTR (L_HEX, MOD (L_Num, P_Base) 1, 1) || L_STR; l_num: = trunc (l_num / p_base); exit when (l_num = 0); end loop; return l_str; end to_base; / create or replace function to_dec (p_str in varchar2, p_from_base in number default 16) return number is l_num number default 0; l_hex varchar2 (16) default '0123456789ABCDEF'; begin if (p_str is null or p_from_base is null) then return null; end if; for i in 1 .. length (p_str) loop l_num: = l_num * p_from_base instr ( L_HEX, Upper (Substr (p_str, i, 1))) - 1; end loop; return l_num; end to_DEC; /
[Q] Cannot introduce the detailed usage of sys_context [a] You will understand SelectSys_Context ('useerenv', 'Terminal') Terminal, Sys_Context ('Userenv', 'Language') Language, Sys_Context ('Userenv ',' SESSIONID ') sessionid, SYS_CONTEXT (' USERENV ',' iNSTANCE ') instance, SYS_CONTEXT (' USERENV ',' ENTRYID ') entryid, SYS_CONTEXT (' USERENV ',' ISDBA ') isdba, SYS_CONTEXT (' USERENV ', 'NLS_TERRITORY') nls_territory, SYS_CONTEXT ( 'USERENV', 'NLS_CURRENCY') nls_currency, SYS_CONTEXT ( 'USERENV', 'NLS_CALENDAR') nls_calendar, SYS_CONTEXT ( 'USERENV', 'NLS_DATE_FORMAT') nls_date_format, SYS_CONTEXT ( 'USERENV', 'NLS_DATE_LANGUAGE ') nls_date_language, SYS_CONTEXT (' USERENV ',' NLS_SORT ') nls_sort, SYS_CONTEXT (' USERENV ',' CURRENT_USER ') current_user, SYS_CONTEXT (' USERENV ',' cURRENT_USERID ') current_userid, SYS_CONTEXT (' USERENV ',' SESSION_USER ') session_user, SYS_CONTEXT ( 'USERENV', 'sESSION_USERID') session_userid, SYS_CONTEXT ( 'USERENV', 'pROXY_USER') proxy_user, SYS_CONTEXT ( 'USERENV', 'PROXY_USERID') proxy_userid, SYS_CONTEXT ( 'USERENV', 'dB_DOMAIN') db_domain, SYS_CONText ('useerenv', 'db_name " ) Db_name, SYS_CONTEXT ( 'USERENV', 'HOST') host, SYS_CONTEXT ( 'USERENV', 'OS_USER') os_user, SYS_CONTEXT ( 'USERENV', 'EXTERNAL_NAME') external_name, SYS_CONTEXT ( 'USERENV', 'IP_ADDRESS') ip_address , Sys_context ('useerenv', 'network_protocol') network_protocol, sys_context ('useerenv', 'bg_job_id') bg_job_id, sys_context ('useerenv', '
FG_JOB_ID ') fg_job_id, SYS_CONTEXT (' USERENV ',' AUTHENTICATION_TYPE ') authentication_type, SYS_CONTEXT (' USERENV ',' AUTHENTICATION_DATA ') authentication_datafrom dual [Q] how to get day of the week, but also on the other date functions use [A] can be used To_CHAR to solve, such as select to_char (to_date ('2002-08-26', 'YYYY-mm-DD'), 'day') from Dual; set the date language before getting, such as Alter Session Set NLS_DATE_LANGUAGE = 'American '; You can also specify select to_char in the function (to_date (' 2002-08-26 ',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGE = American ') from dual; other more usage, you can refer to To_char and to_date functions such as getting a complete time format Select to_char (sysdate, 'yyyy-mm-mm-dd hh24: mi: ss') from DUAL; ), 'dd') Days from dual This year SELECT Add_Months (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from Dual Next Monday SELECT NEXT_DAY (Sysdate, 'Monday ') From Dual
[Q] Randomly draws the previous N record problem [A] 8i or above version Select * from (Select * from tablename ORDER BY SYS_GUID ()) Where rownum [Q] Patted records from N rows to M row, such as record from 20 lines to 30 lines [A] Select * from (SELECT ROWNUM ID, T. * From table where ... and rownum <= 30) Where id> 20; [Q] How to extract repeated records [a] Select * from table t1 where where t1.rowed! = (SELECT MAX (Rowed) from table t2 where t1.id = t2.id and t1.name = t2.name) or Select count (*), t.col_a, t.col_b from table tgroup by col_a, col_bhaving count (*)> 1 If you want to delete your duplicate record, you can replace the SELECT of the first statement to delete [Q] How to set up autonomous affairs [A] 8i or later, does not affect the main transaction Pragma Autonomous_Transaction; ... Commit | rollback; [Q] How to suspend the specified time during the process [a] DBMS_LOCK package Sleep process such as: dbms_lock.sleep (5); indicates that the pause is 5 seconds. [Q] and log time how to quickly calculate the amount of the transaction [A] may be used such as the following script DECLARE start_time NUMBER; end_time NUMBER; start_redo_size NUMBER; end_redo_size NUMBER; BEGINstart_time: = dbms_utility.get_time; SELECT VALUE INTO start_redo_size FROM v $ mystat m, v $ statname s WHERE m.STATISTIC # = s.STATISTIC # AND s.NAME = 'redo size'; - transaction startINSERT INTO t1 SELECT * FROM all_Objects; --other dml statementCOMMIT; end_time: = dbms_utility.get_time; SELECT VALUE INTO end_redo_size FROM v $ mystat m, v $ statname s WHERE m.STATISTIC # = s.STATISTIC # AND s.NAME = 'redo size'; dbms_output.put_line ( 'Escape Time:' || to_char (end_time-start_time ) || 'CENTISECONDS'); dbms_output.put_line ('redo size:' || to_char (end_redo_size-start_redo_size) || 'bytes'; [Q] How to create a temporary table [A] 8i or more version of Create Global Temporary Tablename (Column List) on Commit Preserve Rows; - Submit the Reserved Data Session Tempory Timetime On Commit Delete Rows; - Submit Delete Data Transaction Temporary Temporary Table For sessions, other sessions do not see the data of the session. [Q] How to perform DDL statements in PL / SQL [A] 1,8i The following version of the DBMS_SQL package 2,8i or more version can also use Execute Immediate SQL; DBMS_UTILITY.EXEC_DDL_STATEMENT ('SQL'); [Q] How to get an IP address [A] server (817 or more): UTL_INADDR.GET_HOST_ADDRESS client: sys_context ('useerenv', 'ip_address') [Q] How to encrypt the stored procedure [A] Use the wrap command, such as (assuming your stored procedure saved as a.sql) Wrap iname = a.sqlpl / sql wrapper: release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001copyright (c) Oracle Corporation 1993, 2000. All rights reserved.processing a.sql to a.plb prompt A.sql converted to A.PLB, this is the encrypted script, execute A.PLB Generate encrypted stored procedures [Q] How to run the stored procedure in Oracle [A] can use the dbms_job package to run the job, such as performing a stored procedure, a simple example, submit a job: variable jobno number; begindbms_job.submit (: Jobno, ' UR_Procedure; ', sysdate,' sysdate 1 '); commit; end; after you can use the following statement select * from user_jobs; [q] How to get milliseconds from the database [A] 9i or above There is a TIMESTAMP type to get milliseconds, such as SQL> Select TO_CHAR (SYSTIMESTAMP, 'YYYY-MM-DD HH24: MI: SSXFF') TIME1, TO_CHAR (CURRENT_TIMESTAMP) Time2 from Dual; Time1 Time2 ------------------------------------------------- --------------------------------------------- 2003-10-24 10: 48: 45.656000 24-OCT-03 10.48.45.656000 AM 08: 00 can be seen that in milliseconds correspond to FF. 8i or more version can create a Java function SQL> Create or Replace and compile java source, "MyTimeStamp" asimport java.lang.string; import java.sql.timestamp; Public class myTimeStamp {public static string getTimeStamp () {return (new timestamp (system.currenttimemillis ())))))). Tostring ();}}; sql> java created. Note: Note Java's grammar, pay attention to case SQL> Create or replace function my_timestamp return varchar2as language javaname 'MyTimestamp.getTimestamp () return java.lang.String'; / SQL> function created.SQL> select my_timestamp, to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') ORACLE_TIME From dual; my_timestamp oracle_time --------------------------------------------- 2003- 03-17 19: 15: 59.688 2003-03-17 19:15:59 If you just want to get 1/100 seconds (HSECs), you can also use dbms_utility.get_time [Q] If there is an update, you don't exist if you can use a statement to implement it. [A] 9i has been supported, it is MERGE, but only support SELECT sub-query, if it is a single data record, you can write SELECT ... from DUAL Subproof. The syntax is: MERGE INTO tableUSING data_sourceON (condition) WHEN MATCHED THEN update_clauseWHEN NOT MATCHED THEN insert_clause; The MERGE INTO course cUSING (SELECT course_name, period, course_hoursFROM course_updates) cuON (c.course_name = cu.course_nameAND c.period = cu.period) WHEN MATCHED THENUPDATESET c.course_hours = cu.course_hoursWHEN NOT MATCHED THENINSERT (c.course_name, c.period, c.course_hours) VALUES (cu.course_name, cu.period, cu.course_hours); [Q] ZuoLian how to achieve right Union with outreach [A] Before 9i, you can write this: Zuo: SELECT A.ID, A.Name, B.Address from a, b where A.Id = B.ID ( ) right: select a. ID, A.Name, B.Address from A, B WHERE A.ID ( ) = B.ID Outer Select A.ID, A.Name, B.AddressFrom A, Bwhere A.Id = B.ID ( UnionSelect B.ID, '' Name, B.AddressFrom Bwhere Not Exists (Select * from awhere a.id = B.ID); above 9i, SQL99 standard has been supported, so the above statement can be written: default internal connection : Select A.ID, A.Name, B.Address, C. SubjectFrom (a inner join b on a.id = B.ID) Inner Join C on B.Name = C.Namewhere Other_clause Zuo SELECT A.ID, A.Name, B.AddressFrom A Left Outer Join B on a.id = B.ID where other_clause right SELECT AI D, A.Name, B.AddressFrom A Right Outer Join B on A.Id = B.ID where other_clause outlocada Select A.ID, A.Name, B.AddressFrom A Full Outer Join B on A.ID = B. ID where other_clauseorselect A.ID, A.Name, B.AddressFrom A Full Outer Join B Using (id) Where other_clause [Q] How to implement a record inserted according to the conditions [A] 9i, can be done by the Insert all statement, just a statement, such as: INSERT Allwhen (ID = 1) Teninto Table_1 (ID, Name) VALUES (ID, Name) WhenInto Table_2 (ID, Name) VALUES (ID, NAME) Elseinto Table_other (ID, Name) VALUES (ID, NAME) SELECT ID, NAMEFROM A; if there is no condition, complete each table Insert, such as Insert Allinto Table_1 (ID, Name) VALUES (ID, NAME) INTO TABLE_2 (ID, NAME) VALUES (ID, NAME) INTO TABLE_OTHER (ID, NAME) VALUES (ID, NAME) SELECT ID, NAMEFROM A; Q] How to implement ranked conversion [A] 1, the ranks of fixed columns, such as Student Subject grade ----------------------------------------------------------------- Student1 language 80student1 mathematics 70student1 English 60student2 English language 90student2 mathematics 80student2 100 ...... converted into mathematical language English student1 80 70 60student2 90 80 100 ...... statement is as follows: select student, sum (decode (subject, 'language', grade, null)) "language ", SUM (Decode (Subject, 'Mathematics', Grade, NULL)" Mathematics ", SUM (Decode (Subject, 'English', Grade, Null)" English "from TableGroup by Student 2, unprofite race transformation, such as C1 C2 -------------- 1 I 1 is 1 who 2 know 2 3 not ... Convert to 1, who 2 know 3 not this type The conversion must be done by means of PL / SQL. Here it gives an example Create or Replace Function GET_C2 (TMP_C1 Number) Return Varchar2 IS col_c2 varchar2 (4000); Beginfor Cur in (SELECT C2 from T where C1 = TMP_C1) loop col_c2: = Col_c2 || cur.c2; end loop; col_c2: = RTRIM (col_c2, 1); return col_c2; end; / sql> SELECT DISTINCT C1, GET_C2 (C1) CC2 from table; [Q] How to achieve the first N records of the packet [A] 8i or more, using analytical functions, such as the three employees or each of the three employees or each class of students. Select * from (Select Depno, ENAME, SAL, ROW_NUMBER () over Sal Desc) RN from EMP) Where rn <= 3 [Q] How to combine adjacent records into a record [A] 8i or later, the analysis function LAG and the LEAD can be extracted or recorded to this record before one or the previous day. Select Deptno, Ename, Hiredate, Lag (Hiredate, 1, Null) over (partition by deptno order by hiredate, ename) Last_hirefrom Emporder by DepNo, HiRedate [Q] How to get the value of the Northern Number N? [A] SELECT *, DENSE_RANK () over (Order By T2 DESC) Rank From T) Where rank = & n; [q] how to output query content to text [a] Spool such as SQLPLUS -S "/ as sysdba" << EOFSET Heading Offset Select * from Tab; DBMS_OUTPUT.PUT_LINE ('Test'); spool offexiteof [Q] How do I execute an OS command in the SQL * PLUS environment? [A] For example, I entered SQLUS, launched the database, and suddenly remembered that the listening has not started, there is no exiting SQLPLUS, nor does it need to have another command line window, enter directly: SQL> Host Lsntctl Start or UNIX / Linux platform SQL> ! [Q] How to set the calling process called the memory process [A] The normal stored procedure is owner privilege, if you want to set the caller permissions, please refer to the following statement crete or replaceProcedure ... () AuthiD current_userasbegin ... End; [Q] How to quickly get the number of records of each table or the table partition [A] can analyze the user, then query the USER_TABLES dictionary, or use the following script to set serveroutput on size 20000Declaremicount INTEGER; beginfor c_tab in (SELECT TABLE_NAME FROM User_tables) Loopexecute Immediate 'Select Count (*) from "' || C_TAB.TABLE_NAME || '"' Into MiNT; DBMS_OUTPUT.PUT_LINE (RPAD (c_tab.table_name, 30, '.') || LPAD (MICOUNT, 10, )) '.'; - if it is partition tableSELECT COUNT (*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name; IF miCount> 0 THENFOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOPEXECUTE IMMEDIATE ' Select count (*) from '|| c_tab.table_name ||' Partition ('|| c_part.partition_name ||') ' INTO MICOUNT; DBMS_OUTPUT.PUT_LINE ('' || RPAD (c_part.partition_name, 30, '.') || LPAD (MICOUNT, 10, '.'); End loop; endiff; end; [A] How to send mail [Q] in Oracle [q] can use UTL_SMTP package, the following is an example program that sends simple mail / ********************* *********************************************************** ***** Parameter: RCPTER in varchameter: RCPTER IN VARCHAR2 Receiver mailbox mail_content in varcha2 mail content DESC: · Send mail to the specified mailbox · You can specify a mailbox, if you need to send multiple mailboxes, additional auxiliary procedures **** *********************************************************** ********************** / CREATE OR REPLACEDURE SP_SEND_MAIL (RCPTER IN VARCHAR2, MAIL_CONTENT IN VARCHAR2) IS Conn UTL_SMTP.CONNECTION; - WRITE TITLEPROECEDURE Send_Header (Name IN VARCHAR2, HEADER IN VARCHAR2) ASBEGINutl_smtp.write_data (conn, NAME || ':' || HEADER || utl_tcp.CRLF); END; BEGIN - opne connectconn: = utl_smtp.open_connection ( 'smtp.com'); utl_smtp .helo (conn, 'oracle'); UTL_SMTP.MAIL (CONN, 'ORACLE INFO'); UTL_SMTP.RCPT (CONN, RCPTER); UTL_SMTP.OPEN_DATA (CONN); - Write Titlesend_Header ('from', 'Oracle Database '); send_header (' to ',' "Recipient" <'|| rpter || "); Send_Header (' Subject ',' DB INFO '); - Write Mail Contentutl_smtp.write_data (conn, utl_tcp.crlf || mail_content; - Close Connect UTL_SMTP.CLOSE_D ata (conn); utl_smtp.quit (conn); EXCEPTIONWHEN utl_smtp.transient_error OR utl_smtp.permanent_error THENBEGINutl_smtp.quit (conn); EXCEPTIONWHEN OTHERS THENNULL; END; WHEN OTHERS THENNULL; END sp_send_mail;
