SQL query Statements

Select COUNT (VIRTUALACC) into V_count from t_atmmonitor WHERE VIRTUALACC = v_number;

For the stored procedure, the value of the count (VIRTUALACC) statistic is assigned to the variable v_count, and the V_count variable is called in subsequent stored procedures.

In the SQL statement

Select into from and insert into select differ

Grammar:

Insert into table2 (field1,field2, ... ) Select Value1,value2, ... From table1

Requirements table2 must exist

The difference between the three Truncate,delete,drop

1.truncate deletes the contents of all rows, as does the delete meaning without a where condition

2.truncate Delete table contents, free up space, execute fast, keep table structure

Delete Deletes table contents, does not free space, preserves table structure

Drop Delete table contents and table mechanism to free up space

Table2 exists, you can add content that conforms to the column type to table2, including constants, and note that all values of the column are constants after the constant is imported.

INSERT INTO T_atmmonitor (
Atmnum
, VIRTUALACC
, Corpid
, Invmoney
, Daymoney
, Datagettime
, Cutoffmoney
, Cutofftime
) Select Atmnum, V_number, Corpid, V_now_money, V_out_money, current TIMESTAMP, 0, current TIMESTAMP
From t_atminfo where virtualacc = V_number;

Note that field values such as v_now_money,v_out_money,0 do not exist in the T_atminfo table, and these can be inserted as constants into the T_atmmonitor

SELECT INTO from statement

Grammar

Select Value1,value2 to table2 from table1

Select Id,name to table1 from table2

Requires that table2 does not exist because the system automatically creates table2 when inserted, and if the previous table2 already exists, the system hint table already exists

So if I want the Name column in MyTable2, then I just need to select the Name column and add it to the new table.

Replace function

Replace (C1,C2[,C3])

Find the string C2 in the string C1, replacing it with C3.

If C3 is empty, all C2 are removed from the C1.

Replace (' 123123tech ', ' 123 '); would return ' tech '
Replace (' 123tech123 ', ' 123 '); would return ' tech '
Replace (' 222tech ', ' 2 ', ' 3 '); would return ' 333tech '
Replace (' 0000123 ', ' 0 '); would return ' 123 '
Replace (' 0000123 ', ' 0 ', '); would return '    123 '
NVL function

If else similar

NVL (str,1)

If Str ==null
return 1;
else return str;
COALESCE function

Syntax coalesce (expression [,... n])



SELECT COALESCE (null,null,3,4,5) from dual
Its return result is: 3
Use of case

What does this sentence mean during the storage process sum (case when ISNULL (a.receiveqty,0) >0 then 1 ELSE 0 END) as Sumreceiverolls,

A table receiveqty is greater than 0, the value is 1, otherwise 0,
Then add up these 1,0 as a column (sumreceiverolls) to query them out.
Case COALESCE (C.limit, 1) while 0 then 1 else c.limit end
The COALESCE function is de-null if the C.limit value is not NULL, then COALESCE (c.limit,1) is the C.limit itself value
DB2 Top 10 records in the query table
SELECT * from T_user where corpid= ' 901010300 ' ORDER BY userid desc Fetch first N rows only
Oracle database queries
SELECT * FROM T_user where rownum<=10
Timestamp data type query statement
SELECT * from T_authlog where To_char (LogTime, ' yyyymmdd hh24:mi:ss ') > ' 20150316 23:59:59 '
Stored Procedure Parsing

SELECT count (plannum) into V_count from T_cashrepnplan where corpid = v_corpid and Plannum = v_plannum and planstate = ' 04 ‘;

Note: Count (Plannum) converts to into V_count variable

If V_count = 0 Then note: If this variable is counted as 0, display 97
Set V_ret = ' 97 ';
Goto Goto_ret;
End If;

The database field is the timestamp type, and the data for the query interval
SELECT * from T_authlog where logtime between timestamp (' 2015-3-18 10:10:10 ') and timestamp (' 2015-3-19 15:40:20 ')

Add columns to the table and limit the values of the default fields
ALTER TABLE t_warning add column Overdue char (1) with default ' 0 ';
Querying data for the current date
DB2 = SELECT * FROM t_compinvmonitor where date (gettime) = Current date

SQL query Statements