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 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 Character ' Expressed with two single quotes ('i''m'); - two '' can represent a '[q] how to set transaction consistency [a] set transaction [isolation level] read committed Default sentence-level consistency set transaction [isolation level] Serializable; Read online; transaction-level consistency [Q] how to use 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 custom exception [A] pragma_exception_init (exception_name, error_number); if immediately thrown raise_application_error (error_number, error_msg, true | false); wherein the number -20000 to -20999, error message Maximum 2048B abnormal variable SQLCode error code SQlerRM error message [q] Ten refinement and hexadecimal conversion [A] 8i or above: to_char (100, 'xx') to_number ('4d', 'XX') between the binary conversion 8i following reference 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 '012345678 9ABCDEF '; 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 ('Userenv', '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 ('Useerenv', 'session_userid') session_userid, sys_context ('useerenv', 'proxy_us ER ') proxy_user, SYS_CONTEXT (' USERENV ',' PROXY_USERID ') proxy_userid, SYS_CONTEXT (' USERENV ',' DB_DOMAIN ') db_domain, SYS_CONTEXT (' USERENV ',' 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 ( '
USERENV ',' NETWORK_PROTOCOL ') network_protocol, SYS_CONTEXT (' USERENV ',' BG_JOB_ID ') bg_job_id, SYS_CONTEXT (' USERENV ',' 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 today is the day, but also about other date functions [A] can be solved with to_char, such as select to_char (to_date ('2002-08-26
',' YYYY-mm-DD '
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGUAGE = American ') from Dual; Other More Use, I can refer to to_Char and TO_DATE functions such as getting a complete time format select to_char (sysdate,' YYYY-mm -dd hh24: mi: ss') from Dual; Jellylys Several Other Functions: This month Select TO_CHAR (Last_Day (Sysdate), '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 extracts the previous N records [A] 8i The above version Select * from (Select * from tablename Order by sys_guid ()) Where rownum
[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_size || 'Bytes'); end; [q] How to create a temporary table [A] 8i or later Create Global Temporary TABLENAME (Column List) On Commit Preserve Rows; - Submit a Reserved Data Session Temporary Table On Commit Delete Rows; [Q] How to perform DDL statement 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 IP address [A] server (817 or more): UTL_INADDR.GET_HOST_ADDRESS Client: Sys_Context ('Userenv', 'ip_address') [q] How to encrypt the stored procedure [A] Use the wrap command, such as (assuming your stored procedure save A.sql) Wrap iname = a.sqlpl / sql wrapper: release8.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 encrypted Script, execute a.plb, generate encrypted stored procedures [Q] how to run stored procedures during Oracle [A] can use the dbms_job package to run jobs, such as performing stored procedures, a simple example, submit one Job: Variable Jobno Number; BegindBMS_Job.Submit (: Jobno, 'UR_PROCEDURE;', SYSDATE, 'SYSDATE 1'); commit; end; then, you can use the following statement to query the submitted job SELECT * from user_jobs; ] How to get milliseconds from the database [A] 9i or more, 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, milliseconds correspond to FF in TO_CHAR. 8i above can create a java function following SQL> create or replace and compile java sourcenamed "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 grammar, note the 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 update, you don't exist that insert can be implemented with a statement [A] 9i has been supported, is MERGE However, only the SELECT sub-query is supported. If it is a single data record, you can write SELECT ... from DUAL subqueries.
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_CLASELECT A.ID, A.Name, B.AddressFrom A Full Outer Join B Using (id) Where other_clause [q] How to implement a record According to the condition multiple table [A] 9i can be completed by the Insert all statement, only Is a statement, such as insert allwhen (id = 1) Teninto Table_1 (ID, Name) Values (ID, Name )1 (id = 2) Theninto 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 the insertion of each table,
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 Chinese 80Student1 Mathematics 70Student1 English 60student2 language 90student2 mathematics 80student2 English 100 ... Convert to language math English Student1 80 70 60student2 90 80 100 ... Sthiography 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 Student2, unproductive row transition, such as C1 C
2 -------------- 1 I 1 is 1 no one know 2 3 not ... Convert to 1 I do I 2 know 3 Don't this type of conversion must be taken to PL / SQL To do it, here is an example Create or Replace function get_c2 (TMP_C1 Number) Return VARCHAR2 IS col_c2 varcha2 (4000); Beginfor Cur in (SELECT C2 from T where c1 = tmp_c1) loop col_c2: = col_c2 || Cur.c2; End LOOP; col_c2: = r r (col_c2, 1); return col_c2; end; / sql> Select Distinct C1, GET_C2 (C1) CC2 from table; [q] can be achieved to achieve the first n-recorded N record [A] 8i The above version, using the analytical function, such as the three employees or the top three of each class's salary. Select * from (Select Depno, ENAME, SAL, ROW_NUMBER () OVER (Partition by DepNoRder by Sal (Partition by DepNoorder By Sal (Partition by Depositer By Sal (Partition by Dep) Where RN <= 3 [q] How to combine adjacent records to a record [a] 8i or more Version, the analysis function LAG and the LEAD can be extracted or recorded to this record before 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 Nth value in a column? [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 Feedback Offspool Temp.txt Select * from Tab; DBMS_OUTPUT.PUT_LINE ('Test'); Spool OffexiteOf [q] How to execute an OS command in a 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 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 (*) "' || C_TAB.TABLE_NAME || '"' Into MiNAMS_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 MiUnt; dbms_output.put_line (' '|| rPAD (c_part.partition_name, 30,'. ') || LPAD (MICOUNT, 10, ')); end loop; end if; end loop; end; [A] How to send mail [Q] in Oracle, you can use UTL_SMTP package, the following is an example of sending simple mail program/************************ ************************ ******** ************************* ** parameter: rcpter in varchar2 Receiver mailbox mail_content in varcha2 mail content desc: · Send mail to the specified mailbox · You can only specify a mailbox, if you need to send multiple mailboxes, additional auxiliary procedures are required ******* *********************************************************** *** **************** / CREATE OR REPLACE PROCEDURE sp_send_mail (rcpter IN VARCHAR2, mail_content IN VARCHAR2) IS conn utl_smtp.connection; - write titlePROCEDURE send_header (NAME IN VARCHAR2, Header in varcha2) asbeginutl_smtp.write_data (conn, name || ':' || header || UTL_TCP.CRLF); end; begin - opne connectConn: = UTL_SMTP.OPEN_CONNECTION ('smtp.com');
