Oracle Common FAQ V1.0 (1)

Oracle 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 into a database table
[A] can be handled with ASCII code, other special characters Furu & also, such as
INSERT into t values (' I ' | | Chr (39) | | M '); --Chr (39) stands for characters '
or use two single quotes to indicate a
or INSERT into t values (' I ' m '); --Two "can represent a '
[Q] How to set transactional 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 cursors
[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 to customize exceptions
[A] Pragma_exception_init (exception_name,error_number);
If an exception is thrown immediately
Raise_application_error (Error_number,error_msg,true|false);
Where number from-20000 to 20999, error message Max 2048B
Exception variable
SQLCODE error code
SQLERRM error message
[Q] decimal and hexadecimal conversions
[A]8i above version:
To_char (+, ' XX ')
To_number (' 4D ', ' XX ')
The conversions between the 8i and below refer 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 () 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 20;
[Q] How to extract duplicate 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 t
GROUP BY Col_a,col_b
Having Count (*) >1
If you want to delete duplicate records, you can replace the select of the first statement with the delete
[Q] How to set up autonomous transactions
[a]8i above version, does not affect the main transaction
pragma autonomous_transaction;
......
Commit|rollback;
[Q] How to pause a specified time in a procedure
A The sleep process of the Dbms_lock packet
For example: Dbms_lock.sleep (5); 5 Seconds to suspend.
[Q] How to calculate the transaction time and log quantity quickly
[A] You can use a script similar to the following
DECLARE
Start_time number;
End_time number;
Start_redo_size number;
End_redo_size number;
BEGIN
Start_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 start
INSERT into T1
SELECT * from All_objects;
--other DML Statement
COMMIT;
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 ');
End;
[Q] How to create a temporary table
[A]8i above version
Create global temporary tablename (column list)
on commit preserve rows; --Submit a reserved Data Session temp Table
on commit delete rows; --Submit Delete data Transaction temporary table
A temporary table is relative to a session, and other sessions do not see the data for that session.
[Q] How to execute DDL statements in Pl/sql
[A]1, 8i the following version Dbms_sql package
2, 8i above version can also be used
Execute immediate SQL;
Dbms_utility.exec_ddl_statement (' SQL ');
[Q] How to get the IP address
[A] Server (more than 817): utl_inaddr.get_host_address
Client: Sys_context (' Userenv ', ' ip_address ')
[Q] How to encrypt stored procedures
[A] Use the Wrap command, such as (assuming that your stored procedure is saved as a a.sql)
Wrap Iname=a.sql
Pl/sql wrapper:release 8.1.7.0.0-production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing A.sql to A.PLB
Prompt A.sql conversion to A.PLB, this is the encrypted script, execute A.PLB can generate encrypted stored procedure
[Q] How to run a stored procedure periodically in Oracle
[A] You can use the Dbms_job package to run the job at timed intervals, such as executing a stored procedure, a simple example of submitting a job:
VARIABLE jobno number;
BEGIN
Dbms_job. SUBMIT (: Jobno, ' ur_procedure; ', Sysdate, ' sysdate + 1 ');
Commit
End;
You can then use the following statement to query for jobs that have been submitted
SELECT * from User_jobs;
[Q] How to get milliseconds from the database
[A]9i above, there is a timestamp type that obtains 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
As you can see, the millisecond corresponds to FF in To_char.
The above 8i version can create a Java function as follows
Sql>create or replace and compile
Java source
Named "Mytimestamp"
As
Import 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 the Java syntax, note the case
Sql>create or Replace function My_timestamp return VARCHAR2
As language Java
Name ' 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 only want to get 1/100 seconds (hsecs), you can also use the Dbms_utility.get_time
[Q] If there is an update, there is no insert can be implemented with a statement
[a]9i has been supported, is the merge, but only select subqueries are supported,
If it is a single data record, you can write a select ... a subquery from dual.
The syntax is:
MERGE into table
USING Data_source
On (condition)
When matched THEN Update_clause
When not matched THEN insert_clause;
Such as
MERGE into Course C
USING (SELECT course_name, period,
Course_hours
From Course_updates) CU
On (C.course_name = Cu.course_name
and c.period = cu.period)
When matched THEN
UPDATE
SET c.course_hours = cu.course_hours
When not matched THEN
INSERT (C.course_name, C.period,
c.course_hours)
VALUES (Cu.course_name, Cu.period,
Cu.course_hours);
[Q] How to achieve leftist, right and outer union
[A] This can be written before 9i:
Leftist
Select A.id,a.name,b.address from A,b
where A.id=b.id (+)
Right-union:
Select A.id,a.name,b.address from A,b
where a.ID (+) =b.id
Outreach
SELECT a.id,a.name,b.address
From A,b
WHERE a.id = b.id (+)
UNION
SELECT b.id, ' name,b.address
From b
WHERE not EXISTS (
SELECT * from a
WHERE a.id = b.id);
Above 9i, the SQL99 standard has already been supported, so the above statement can be written as:
Default internal join:
Select A.id,a.name,b.address,c.subject
From (a INNER join B on a.id=b.id)
INNER JOIN C on b.name = C.name
where Other_clause
Leftist
Select A.id,a.name,b.address
From a LEFT outer join B on a.id=b.id
where Other_clause
Right-linked
Select A.id,a.name,b.address
From a right outer join B on a.id=b.id
where Other_clause
Outreach
Select A.id,a.name,b.address
From a full outer join B on a.id=b.id
where Other_clause
Or
Select A.id,a.name,b.address
From a full outer join B using (ID)
where Other_clause
[Q] How to implement a record inserts according to the Condition multiple table
[a]9i above can be done through the INSERT all statement, just a statement, such as:
INSERT All
When (id=1) THEN
into Table_1 (ID, name)
VALUES (Id,name)
When (id=2) THEN
into table_2 (ID, name)
VALUES (Id,name)
ELSE
into Table_other (ID, name)
VALUES (ID, name)
SELECT Id,name
from A;
If there is no condition, the insertion of each table is completed, as
INSERT All
into 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,name
from A;
[Q] How to implement row and column conversions
[A]1, row and column conversions of fixed number of columns
Such as
Student Subject Grade
---------------------------
Student1 Language 80
Student1 Math 70
Student1 English 60
Student2 Language 90
Student2 Math 80
Student2 English 100
......
Converted to
English for Chinese mathematics
STUDENT1 80 70 60
STUDENT2 90 80 100
......
Statement as follows:
Select Student,sum (Decode (subject, ' language ', grade,null)) "Language",
SUM (decode (subject, ' math ', grade,null)) "Mathematics",
SUM (decode (subject, ' English ', Grade,null)) "English"
From table
GROUP BY Student
2. Indefinite column and row conversion
Such as
C1 C2
--------------
1 I
1 is
1 Who
2 know
2-Way
3 does not
......
Converted to
1 Who am I
2 know
3 does not
This type of conversion must be done with the help of Pl/sql, which gives an example
CREATE OR REPLACE FUNCTION get_c2 (tmp_c1 number)
Return VARCHAR2
Is
COL_C2 VARCHAR2 (4000);
BEGIN
For 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 implement the group to take the first N records
[a]8i above version, using analytic functions
For example, obtain 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 Depno
ORDER BY sal Desc) rn
From EMP)
Where RN host Lsntctl start
or under the Unix/linux platform.
sql>!
Under Windows platform
sql>$
Summary: HOST can execute OS commands directly.
Note: The CD command does not execute correctly.
[Q] How to set caller permissions for a stored procedure
[A] Normal stored procedures are owner permissions, and if you want to set caller permissions, refer to the following statement
Create or replace
Procedure ... ()
Authid Current_User
As
Begin
......
End
[Q] How to quickly get the number of records per table or table partition under the 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
DECLARE
Micount INTEGER;
BEGIN
For C_tab in (SELECT table_name from user_tables) loop
EXECUTE 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 table
SELECT COUNT (*) into the micount from user_part_tables WHERE table_name = c_tab.table_name;
IF Micount >0 THEN
For C_part in (SELECT partition_name from user_tab_partitions WHERE table_name = c_tab.table_name) loop
EXECUTE 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;
End IF;
End LOOP;
End;
[A] How to send an email in Oracle
[Q] You can use the UTL_SMTP package to send mail, the following is a simple message to send an example program
/****************************************************************************
Parameter:rcpter in VARCHAR2 recipient mailbox
Mail_content in VARCHAR2 message content
Desc: Send mail to a specified mailbox
• You can only specify one mailbox, if you need to send to multiple mailboxes, you need additional auxiliary programs
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_send_mail (rcpter in VARCHAR2,
Mail_content in VARCHAR2)
Is
Conn Utl_smtp.connection;
--write Title
PROCEDURE Send_header (NAME in VARCHAR2, header in VARCHAR2) as
BEGIN
Utl_smtp.write_data (conn, name| | ': ' | | header| | Utl_tcp. CRLF);
End;
BEGIN
--opne Connect
Conn: = 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 Title
Send_header (' From ', ' Oracle Database ');
Send_header (' to ', ' "Recipient");
Send_header (' Subject ', ' DB Info ');
--write Mail Content
Utl_smtp.write_data (conn, UTL_TCP.CRLF | | mail_content);
--close Connect
UTL_SMTP.CLOSE_DATA (conn);
Utl_smtp.quit (conn);
EXCEPTION
When Utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
Utl_smtp.quit (conn);
EXCEPTION
When others THEN
NULL;
End;
When others THEN
NULL;
End Sp_send_mail;
[A] How to write operating system files in Oracle, such as writing a log
[Q] You can take advantage of the Utl_file package, but before you do this, be careful to set the Utl_file_dir initialization parameters
/**************************************************************************
Parameter:textcontext in VARCHAR2 log contents
Desc: Write log, the content to the server specified directory
• You must configure the Utl_file_dir initialization parameters and ensure that the log path is consistent with the Utl_file_dir path or one of the
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_write_log (Text_context VARCHAR2)
Is
File_handle Utl_file.file_type;
Write_content VARCHAR2 (1024);
Write_file_name VARCHAR2 (50);
BEGIN
--open file
Write_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 file
IF Utl_file.is_open (File_handle) THEN
Utl_file.put_line (file_handle,write_content);
End IF;
--close file
Utl_file.fclose (File_handle);
EXCEPTION
When others THEN
BEGIN
IF Utl_file.is_open (File_handle) THEN
Utl_file.fclose (File_handle);
End IF;
EXCEPTION
When others THEN
NULL;
End;
End Sp_write_log;