sql oracle case statement in where clause

1. Authorize a user to view sys. v $ SQL _plan permission 2. create a plan_table-like view SQL> create or replace view dynamic_plan_table 2 as 3 select rawtohex (address) | '_' | child_number statement_id, 4 sysdate timestamp, operation, options, object_node, 5 object_owner, object_name, 0 object_instance, 6 optimizer, search_columns, id, parent_id, position, 7 cost, cardinality, bytes, other_tag, region, 8

Tags: bracket ROM query SQL statement from Ora reason here sqlFor example: SELECT ename, Job, Deptno from emp WHERE Deptno in (Ten) NB sp; and (ename like '%I ' or job like '%er '); The executes the SQL with the following result set: 1 jones manager NBS P 2 clark manager Remove the upper sq l parentheses in the query condition, SELE

1. SQL statement 1: -- 1. view the table space name and size 2. SQL statement 2:-- 1G = 1024 MB

Copy Code code as follows: -Oracle takes the SQL from Monday to the weekend This takes the first days of the week, starting with Sunday. Select To_char (to_date (' 20130906 ', ' yyyymmdd '), ' d ') from dual; --Result: 6 Note: 2013.09.06 is Friday, the sixth day of the week Select To_char (sysdate+ (2-to_char (sysdate, ' d '))-7, ' YYYYMMDD ') from dual;---Monday Select To_char (sysdate+ (2-t

Today want to be a staff birthday reminder module, suddenly found that the statement is not good to write, so Baidu summed up a bit, hehe.select * from table name where humanstate= ' normal incumbency ' and (To_number (To_char ((dateOfBirth), ' mm ')) = To_number (To_char ((sysdate), ' mm ')) and (To_number (To_char (dateOfBirth ), ' DD ') between To_number (To_char ((sysdate), ' DD ')) and To_number (To_char ((sysdate), ' DD ')) +7) The red part is

1. According to rowID to divideSELECT * from T_xiaoxi where rowID in (the Select Rid from (select RowNum rn,rid from, select rowID rid,cid fromT_xiaoxi ORDER BY cid Desc) where rownumExecution time 0.03 seconds2. According to the analysis function to divideSELECT * FROM (select T.*,row_number () over (order BY CID Desc) rk from T_xiaoxi T) where rkExecution time 1.01 seconds3. Press RowNum to divideSELECT * FROM (select T.*,rownum rn from (SELECT * from T_xiaoxi ORDER BY cid Desc) t where rownum

JOIN EMP E on (D.DEPTNO=E.DEPTNO)　　GROUP BY D.deptno,dname　　--14, list employee names, department names, and salaries for all employees　　Select e.ename,d.dname,e.sal from EMP e left joins dept D on (D.DEPTNO=E.DEPTNO)　　--15, list of different combinations of employees engaged in the same job but belonging to different departments　　Select Tba.ename,tbb.ename,tba.job,tbb.job,tba.deptno,tba.deptno　　From EMP tba,emp TBB　　Where Tba.job=tbb.job and Tba.deptno　　--16, listing details of all departments

is declared and needs to be inserted before the BEGIN keyword: dynamic RESULT sets 1LANGUAGE sql*/create PROCEDURE a_test2 ( V_c VARCHAR (()) DYNAMIC RESULT sets 1LANGUAGE sqlbegin DECLARE t_sql VARCHAR2 (a); DECLARE t_a VARCHAR2 (); DECLARE t_b VARCHAR2 (); DECLARE t_c VARCHAR2 (); DECLARE t_d VARCHAR2 (); --The t_stmt behind the for is consistent with the following prepare variable, and its type is

characters In fact, if the SQL statement is also a WHERE clause, the two tables follow-up will be detailed. Example 9: Querying employee numbers and names for wages greater than $1500 Select Empno,ename from emp where sal > 1500; Example : Query position is not "clerk" employee number and name Select Empno,ename from emp where job Example : Query employe

Oracle Learning NotesAnalysis of SQL statement execution Process lecturesThis lesson goes into the database by telling a SQL statementand its entire execution process in the databaseString The architecture inside the database.Let's take a closer look at the relationships between the various processes, storage structure

Tags: Gets SQL Server graphics create change COM single column note let Although the ORM mapping is used in the project, the optimization of the Oracle system is very valuable, and here is a summary of the frequently used statements from the perspective of the SQL statement. 1. Optimizations in the From field:Oracle Ya

TableinWhere NOT EXISTS (SELECT * from Tablein where Tablein.aid = 7)The use of exists and in efficiency, usually using exists is higher than in efficiency, because in does not walk the index, but depends on the actual use of:In the case of large appearance and small inner table, exists is suitable for small appearance and large inner table. --------------------------------------------------------------------------------------------------------------

The Oracle tutorial you are looking at is an introductory overview of Oracle Pl/sql. First, the purpose of pl/sql appearanceStructured Query Language (structured query Language, referred to as SQL) is used to access relational database a common language, which belongs to the

also cause a full table scan:Select ID from t where name like '%abc% 'To be more efficient, consider full-text indexing.7. If you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at c

: Network Disk DownloadContent IntroductionEditing the volume of information contained in this book can improve your programming skills to a new level. You'll learn how to write dynamic PL/SQL programs and Oracle database interfaces, perform complex computations, and use advanced techniques to control error conditions. In addition, you will receive up-to-date information on Pl/sqltoolkit,java integration an

ROWNUM WHERE InStr ('; ' | | a.q, '; ', 1, RN) > 0) tORDER by 1, 2;--Implement a record based on conditional multiple table insertionsDROP TABLE t_ia_src;CREATE TABLE t_ia_src as SELECT ' a ' | | ROWNUMC1, ' B ' | | ROWNUM C2 from dual Connectby rownumDROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1 (Flag VARCHAR2 (Ten), CVARCHAR2 (10));DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2 (Flag VARCHAR2 (Ten), CVARCHAR2 (10));DROP TABLE T_ia_dest_3;CREATE TABLE t_ia_dest_3 (Flag VARCHAR2 (Ten), CVARC

)) whenT.price like 'Price:%' ThenSUBSTR (T.price, -, LENGTH (T.price)- *)ELSE '\ n' ENDPrice , Case whenT.sellername like 'linkman%" " Thensubstr (T.sellername,Ten, Length (T.sellername)-Ten) whenT.sellername like 'linkman%>' Thensubstr (T.sellername,Ten, Length (T.sellername)-185)Else '\ n' EndSellername fromTABLENAME TThe resulting data is more normative:Thus, we can summarize the following case-when

I got a colleague asking for help today, saying that there is a select query that runs for more than a minute on Oracle, and he hopes to produce results within 5s, as follows: Select/*+ Parallel (SRC, 8) */distinct src.systemname as SystemName, src.databasename as DatabaseName, Src.tablename as TableName, src.username as username from A simple analysis of SQL In a way, this

buffer[note] The V$SGA view can be used to view the size of the SGA chunks.This article describes the execution flow of the SQL statement, so it mainly explains the 3 chunks: share buffer (shared pool)Database buffering cache (db buffer cache)Redo log buffers (redo log buffer)Such as:2.2 Server ProcessesWhen a user (client) wants to connect to an Oracle database

oracle| Process | statement Starting in 07, a new company, the database using oracle10g, has been using Informix and Msserver, feel the Oracle function is really strong, more than Informi and Msserver are better, the architecture and management of a lot of changes , but the use of two months down, in fact, the basic principle of the database is the same, now comb