Common FAQ-SQL for Oracle

Part 1: SQL & PL/SQL[Q]How to query special characters, such as wildcards % and _
[A] select * from table where name like 'a \ _ % 'escape '\' [Q]How to insert single quotes to database tables
[A] ASCII codes can be used for processing. Other special characters such as &, such as insert into t values ('I' | chr (39) | 'M '); -- chr (39) indicates the character 'or two single quotes are used to represent one or insert into t values (' I 'M '); -- two ''can represent one' [Q]How to Set transaction consistency
[A] set transaction [isolation level] read committed; default statement-level consistency set transaction [isolation level] serializable; read only; transaction-level consistency [Q]How to update data with a cursor
[A] cursor c1 isselect * from tablenamewhere name is null for update [of column]... Update tablename set column = ...... Where current of c1;[Q] How to customize exceptions
[A] pragma_exception_init (exception_name, error_number); if an exception raise_application_error (error_number, error_msg, true | false) is thrown immediately, where number ranges from-20000 to-20999, error message: a maximum of 2048B exception variable SQLCODE error code SQLERRM error message[Q] convert decimal to hexadecimal
[A] 8i and later versions: to_char (100, 'xx') to_number ('4d ', 'xx ') for the conversion between hexadecimal numbers below 8i, refer to the following script create or replace function to_base (p_dec in number, p_base in number) return varchar2isl_str varchar2 (255) default NULL; l_num number default p_dec; l_hex varchar2 (16) default '0123456789abcdef '; beginif (p_dec is null or p_base is null) thenreturn null; end if; if (trunc (p_dec) <> p_dec OR p_dec <0) thenraise PROGRAM_ERROR; end if; loopl_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 numberisl_num number default 0; l_hex varchar2 (16) default '0123456789abcdef '; beginif (p_str is null or p_from_base is null) thenreturn null; end if; for I in 1 .. length (p_str) loopl_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] Can I introduce the detailed usage of SYS_CONTEXT?
[A] using the following query, 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 ('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 ('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 do I obtain the day of the week and use other date functions?
[A] to_char can be used to solve the problem, for example, select to_char (to_date ('2017-08-26 ', 'yyyy-mm-dd'), 'day') from dual; you can SET the date language before obtaining it, for example, alter session set NLS_DATE_LANGUAGE = 'American '. You can also specify select to_char (to_date ('2017-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ DATE_LANGUAGE = American ') from dual; other more methods, see the to_char and to_date functions to obtain the complete time format select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual; just introduce the usage of several other functions: SELECT to_char (last_day (SYSDATE), 'dd') days FROM dual this year's days select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual next Monday date SELECT Next_day (SYSDATE, 'monday') FROM dual[Q] Question about random extraction of the First N records
[A] select * from (select * from tablename order by sys_guid () where rownum <N; select * from (select * from tablename order by dbms_random.value) where rownum <N; Note: The dbms_random package must be manually installed at $ ORACLE_HOME/rdbms/admin/dbmsrand. sqldbms_random.value (100,200) can generate a random number ranging from 100 to 200.[Q] extract records from N rows to M rows, such as records from 20 rows to 30 rows
[A] select * from (select rownum id, t. * from table where ...... And rownum <= 30) where id> 20;[Q] how to extract duplicate records
[A] select * from table t1 where t1.rowed! = (Select max (rowed) from table t2where 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 duplicate records, replace select of the first statement with delete.[Q] How to set autonomous transactions
[A] Later than 8i, does not affect the main transaction pragma autonomous_transaction ;...... Commit | rollback;[Q] How to pause a specified time during the process
[A] sleep process of the DBMS_LOCK package, for example, dbms_lock.sleep (5); indicates that the sleep is paused for 5 seconds.[Q] How to quickly calculate the transaction time and log volume
[A] The following script can be used: DECLAREstart_time NUMBER; end_time NUMBER; start_redo_size NUMBER; end_redo_size NUMBER; BEGINstart_time: = timeout; select value into start_redo_size FROM v $ mystat m, v $ statname sWHERE m. STATISTIC # = s. STATISTIC # AND s. NAME = 'redo size'; -- transaction startINSERT INTO t1SELECT * FROM All_Objects; -- other dml statementCOMMIT; end_time: = dbms_utility.get_time; select value into end_redo_size FROM v $ mystat m, v $ statname sWHERE 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 '); END;[Q] how to create a temporary table
[A] create global temporary tablename (column list) on commit preserve rows in 8i or later versions; -- submit the temporary table on commit delete rows for retaining data sessions; -- The temporary table for submitting and deleting data transactions is relative to the session, and the data of this session cannot be seen in other sessions.[Q] how to execute DDL statements in PL/SQL
[A] 1. dbms_ SQL packages earlier than 8i 2 and later versions can also use execute immediate sql1_dbms_utility.exe c_ddl_statement ('SQL ');[Q] How to get an IP address
[A] server (above 817): utl_inaddr.get_host_address client: sys_context ('userenv', 'IP _ address ')[Q] How to encrypt the Stored Procedure
[A] use the wrap command, for example (assume that your stored procedure is saved as. SQL) wrap iname =. sqlPL/SQL Wrapper: Release 8.1.7.0.0-Production on Tue Nov 27 22:26:48 2001 Copyright (c) Oracle Corporation 1993,200 0. all Rights Reserved. processing. SQL to. plb prompts. SQL to. plb. This is the encrypted script. Execute. plb can generate encrypted stored procedures[Q] How to regularly run stored procedures in ORACLE
[A] You can use the dbms_job package to regularly run jobs, such as executing stored procedures. In A simple example, submit A 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;[Q] How To Get milliseconds from the database
[A] 9i and later versions, there is A timestamp type for obtaining milliseconds, such as SQL> select to_char (systimestamp, 'yyyy-mm-dd hh24: mi: ssxff') time1, to_char (current_timestamp) time2 from dual; TIME1 TIME2 ------------------------------- -------------------------------------------------------------- 10:48:45. 656000 24--0-03 10.48.45.656000 AM + 08:00: the millisecond corresponds to FF in to_char. For 8i or later versions, you can create the following java function SQL> create or replace and compilejava 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: Pay attention to java syntax. Pay attention to case-sensitive 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 ------------------------ --------------- 19:15:59. 688 19:15:59 if you only want to get 1/100 seconds (hsecs), you can also use dbms_utility.get_time[Q] Can I insert an update if it does not exist?
[A] 9i supports Merge, but only select subqueries. If it is A single data record, you can write select ...... From dual subquery. Syntax: merge into tableUSING data_sourceON (condition) when matched then does not matched then insert_clause; e.g. 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] How to Implement left, right, and outer connections
[A] This can be written before 9i: left join: select. id,. name, B. address from a, bwhere. id = B. id (+) Right join: select. id,. name, B. address from a, bwhere. id (+) = B. id outer SELECT. id,. name, B. addressFROM a, bWHERE. id = B. id (+) UNIONSELECT B. id, ''name, B. addressFROM bWHERE not exists (SELECT * FROM aWHERE. id = B. id); above 9i, The SQL99 standard is already supported. Therefore, the preceding statement can be written as: Default internal join: select. id,. name, B. address, c. subjectfrom (a inner join B on. id = B. id) inner join c on B. name = c. namewhere other_clause left join select. id,. name, B. addressfrom a left outer join B on. id = B. idwhere other_clause right join select. id,. name, B. addressfrom a right outer join B on. id = B. idwhere other_clause external select. id,. name, B. addressfrom a full outer join B on. id = B. idwhere other_clauseorselect. id,. name, B. addressfrom a full outer join B using (id) where other_clause[Q] how to insert a record into multiple tables based on conditions
[A] 9i and above can be completed through the Insert all statement, just A statement, such as: insert allwhen (id = 1) THENINTO table_1 (id, name) values (id, name) WHEN (id = 2) THENINTO table_2 (id, name) values (id, name) ELSEINTO table_other (id, name) values (id, name) SELECT id, nameFROM; if no conditions exist, INSERT 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;[Q] how to convert rows and columns
[A] 1. Convert rows and columns with fixed columns such as student subject grade ------------------------- student1 language 80student1 mathematics 70student1 English 60student2 language 90student2 mathematics 80student2 English 100 ...... Convert to Chinese math English student1 80 70 60student2 90 80 100 ...... Statement: select student, sum (decode (subject, 'China', grade, null) "", sum (decode (subject, 'mat', grade, null )) "Mathematics", sum (decode (subject, 'English ', grade, null )) "English" from tablegroup by student2, changing columns to columns such as c1 c2 -------------- 1 I 1 is 1 who 2 knows 2 do not ...... Conversion to 1 who I am 2 know 3 do not this type of conversion must be completed by PL/SQL, here is an example of CREATE OR REPLACE FUNCTION get_c2 (tmp_c1 NUMBER) RETURN VARCHAR2ISCol_c2 VARCHAR2 (4000); BEGINFOR cur IN (SELECT c2 FROM t WHERE c1 = tmp_c1) LOOPCol_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 obtain the first N records in a group?
[A] 8i and later versions, using analysis functions such as getting the top three employees in each department or the top three students in each class. Select * from (select depno, ename, sal, row_number () over (partition by depnoorder by sal desc) rnfrom emp) where rn <= 3[Q] how to merge adjacent records into a record
[A] Later than 8i, the analysis functions lag and lead can extract the last or previous record to this record. 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 obtain the nth value in a column?
[A] select * from (select t. *, dense_rank () over (order by t2 desc) rank from t) where rank = & N;[Q] How to output the query content to text
[A] Use 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 do I execute OS commands in the SQL * PLUS environment?
[A] For example, after entering SQLPLUS and starting the database, I suddenly remembered that the listener has not been started. At this time, I do not need to exit SQLPLUS or start another command line window. I directly enter: SQL> host lsntctl start or SQL on unix/linux platforms>! <OS command> SQL on windows platform >$ <OS command> conclusion: HOST <OS command> can directly execute OS commands. Note: The cd command cannot be correctly executed.[Q] how to set the caller permission for a stored procedure
[A] The General stored procedure is the owner permission. To set the caller permission, see the following statement create or replaceprocedure ...... () AUTHID CURRENT_USERAsbegin ...... End;[Q] How to quickly obtain the number of records for each table or table partition under a user
[A] You can analyze the user and then query the user_tables dictionary, or use the following script to set serveroutput on size 20000 DECLAREmiCount INTEGER; BEGINFOR c_tab IN (SELECT table_name FROM user_tables) loopexecute immediate 'select count (*) from "'| c_tab.table_name |'" 'into miCount; 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 partition WHERE table_name = c_tab.table_name) loopexecute immediate 'select count (*) from '| c_tab.table_name | 'partition (' | c_part.partition_name | ') 'into miCount; partition (''| rpad (c_part.partition_name, 30 ,'. ') | lpad (miCount, 10 ,'. '); end loop; end if; end loop; END;[Q] how to send emails in Oracle
[A] You can use the utl_smtp package to send emails. The following is an example program for sending simple emails.
/*************************************** * *********************************** Parameter: rcpter in varchar2 receiver email Mail_Content in Varchar2 email content desc: · send an email to a specified email address. Only one email address can be specified. If you need to send an email to multiple mailboxes, *********************************** **************************************** */create or replace procedure sp_send_mail (rcpter IN VARCHAR2, mail_content IN VARCHAR2) ISconn utl_smtp.connection; -- write titlePROCEDURE send_header (name in VARCHAR2, header in VARCHAR2) encode (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" <' | rcpter | '> '); send_header ('subobject', 'db info'); -- write mail Merge (conn, utl_tcp.crlf | mail_content); -- close connectutl_smtp.close_data (conn); utl_smtp.quit (conn ); predictionwhen utl_smtp.transient_error OR utl_smtp.permanent_error THENBEGINutl_smtp.quit (conn); predictionwhen others thennull; END; when others thennull; END sp_send_mail;[Q] How to Write OS files in Oracle, such as logs?
[A] The utl_file package can be used. However, before that, note that the Utl_file_dir initialization parameter /******************************** **************************************** ** parameter: textContext in varchar2 log Content desc: · write logs and record the content to the specified directory on the server. The Utl_file_dir initialization parameter must be configured, make sure that the Log Path is consistent with the Utl_file_dir path or that one of them is ***************************** **************************************** * *****/create or replace procedure sp_Write_log (text_context VARCHAR2) ISfile_handle utl_file.file_type; Write_content VARCHAR2 (1024); Write_file_name VARCHAR2 (50); BEGIN -- open filewrite_file_name: = 'db _ alert. log'; file_handle: = utl_file.fopen ('/u01/logs', write_file_name, 'A'); write_content: = to_char (SYSDATE, 'yyyy-mm-dd hh24: mi: ss ') |' | text_context; -- write fileIF utl_file.is_open (file_handle) handle (file_handle, write_content); end if; -- close fileutl_file.fclose (file_handle ); predictionwhen others thenbeginif utl_file.is_open (file_handle) THENutl_file.fclose (file_handle); end if; predictionwhen others thennull; END sp_Write_log;