Some common SQL statements of DB2)

From: http://hi.baidu.com/emilypear/blog/item/6bd1b399bedacf0b6f068cb0.html -- Create a custom single value type
Create distinct type var_newtype
As decimal (5, 2) with comparisons; -- var_newtype type name
-- Decimal (5, 2) actual type -- Delete a custom single value type
Drop distinct type var_newtype; -- Create a custom data type
Create type my_type (
Username varchar (20 ),
Department integer,
Salary decimal (10, 2 ))
Not final
Mode db2sql; -- Modify the Data Type of the custom structure. I have not found any method for deleting attributes.
Alter type my_type
Add attribute hiredate date; -- Delete a Custom Data Type
Drop type my_type; -- Obtain the current system date
Select current date from sysibm. sysdummy1;
Select current time from sysibm. sysdummy1;
Select current timestamp from sysibm. sysdummy1; -- The sysibm. sysdummy1 table is a special table in memory. It can be used to find the value of the DB2 register as shown above. You can also use the keyword values to evaluate registers or expressions.
Values current date;
Values current time;
Values current timestamp; -- More methods of Values Values 2 + 5; values 'Hello lavasoft! '; Values 56
Union all
Values 45; values 1, 2, 4, 5, 6
Union all
Values, 7, 8, 9, 10, 11, 12
Order by 1; -- For more abnormal DB2 SQL statements, anyonetable indicates any existing table. Select 234 from anyonetable;
Select distinct 234 from anyonetable;
Select distinct 234 as 1 from anyonetable; select 'db2 abnormal SQL Haha 'from anyonetable;
Select distinct 'db2 abnormal SQL Haha 'from anyonetable;
Select distinct 'db2 abnormal SQL Haha 'as 1 from anyonetable; -- (hey, you can use any table as sysibm. sysdummy1. however, this is not recommended unless you do not remember sysibm. how to Write sysdummy1? The same is true in Oracle (corresponding to dual! Hahahaha !) -- Defines variables. You can also set default values to assign values to variables.
Declare var1 char (2 );
Declare var2 int default 0; Set var1 = 'a ';
Set var2 = 23; -- Create a dynamic cursor variable
Declare d_cur integer; -- Another method for assigning values to variables
Values expr1, expr2, expr3 into A, B, C;
-- Equivalent
Set a = expr1;
Set B = expr2;
Set c = expr3; -- There is also a way to assign values. Set prodname = (Case
When (name is not null) then name
When (namestr is not null) Then namestr
Else defaultname
End );
-- Equivalent
Set prodname = coalesce (name, namestr, defaultname );
-- Merge the decode () and nvl () functions similar to Oracle. -- Define a cursor
Declare cur1 cursor with return to client for select * From dm_hy;
Declare cur2 cursor for select * From dm_hy; -- Static cursor -- Create a data table, add comments, and insert data.
Create Table tbr_catalog (
Id bigint not null generated by default as identity,
Type smallint not null,
Name varchar (255 ),
Parentid bigint,
Cataloglevel bigint,
Description varchar (255 ),
Primary Key (ID)
);
Comment on table tbr_catalog is 'birt report directory table ';
Comment on column tbr_catalog.id is 'identified ';
Comment on column tbr_catalog.type is 'Directory type ';
Comment on column tbr_catalog.name is 'Directory name ';
Comment on column tbr_catalog.parentid is 'Directory parent identifi ';
Comment on column tbr_catalog.cataloglevel is 'Directory level ';
Comment on column tbr_catalog.description is 'Directory description'; -- insert data to the data table
Insert into tbr_catalog (ID, type, name, parentid, cataloglevel, description)
Values (1, 0, 'System report', 0, 0 ,'');
Insert into tbr_catalog (ID, type, name, parentid, cataloglevel, description)
Values (2, 1, 'user report', 0, 0 ,''); -- Create a foreign key
Alter table tbr_storage
Add constraint fk_tbr_storage
Foreign key (catalogid)
References tbr_catalog (ID ); -- Change the table and add columns
Alter table aaa add sex varchar (1 ); -- Change the table and delete Columns
Alter table aaa drop column sex; -- Remove spaces before and after Parameters
Rtrim (dm_hy.mc ); -- Define a temporary table and create it through an existing person table
Declare global temporary table gbl_temp
Like person
On commit Delete rows -- delete data when submitting
Not Logged -- not recorded in logs
In usr_tbsp -- select tablespace
-- This statement creates a user temporary table named gbl_temp. The names and descriptions of columns used to define this user temporary table are identical to those of the person column. -- Create a temporary table with two fields
-- Define a global temporary table tmp_hy
Declare global temporary table session. tmp_hy
(
DM varchar (10 ),
MC varchar (10)
)
With Replace -- if this temporary table exists, replace
Not Logged; -- not recorded in the log -- insert three pieces of data to the temporary table
Insert into session. tmp_hy values ('1', '1 ');
Insert into session. tmp_hy values ('1', '1 ');
Insert into session. tmp_hy values ('1', '1 '); -- Insert data in batches by querying Inster into tab_bk (Select Code, name from Table Book ); -- Select... usage of Select * into: H1,: H2,: H3,: H4
From EMP
Where empno = '000000 '; -- Statement Process Control If () then
Open cur1
Fetch cur1 into t_equipid;
While (at_end <> 1) do
......
Set t_temp = 0;
End while;
Close cur1;
Else
......
End if; -- External Connection Select empno, deptname, projname
From (emplyoee
Left Outer Join Project
On respemp = empon)
Left outer join Department
On mgrno = empno; -- In, like, order by (... ASC | DESC) Usage
Select * from book t
Where T. name like '% J _ programming %'
And T. Code in ('j565333', 'j565222 '); order by T. Name ASC -- Summary table (complex concept, hard to understand, not commonly used)
Create summary table sumy_stable1
As (select workdept,
Count (*) as reccount,
Sum (salary) as salary,
Sum (bonus) as bonus
From employee group by workdept)
Data initially deferred
Refresh immediate; -- Use SQL to process the semantics of a set at a time
-- (Before optimization) There is a context switch between the process layer and the data flow layer of each line in the SELECT statement.
Declare cur1 cursor for col1, col2 from tab_comp;
Open cur1;
Fetch cur1 into V1, V2;
While sqlcode <> 100 do
If (V1> 20) then
Insert into tab_sel values (20, V1 );
Else
Insert into tab_sel values (V1, V2 );
End if;
Fetch cur1 into V1, V2;
End while; -- (after optimization) There is no context switching between the process layer and the data flow layer
Declare cur1 cursor for col1, col2 from tab_comp;
Open cur1;
Fetch cur1 into V1, V2;
While sqlcode <> 100 do
Insert into tab_sel (select (Case
When col1> 20 then 20
Else col1
End ),
Col2
From tab_comp );
Fetch cur1 into V1, V2;
End while; -- DB2 functions are classified into three types: column functions, scalar functions, and table functions.
-- A column function inputs a set of data and outputs a single result.
-- The scalar function receives a value and returns another value.
-- A table function can only be used in the from clause of an SQL statement. It returns the column of a table, similar to a created regular table. -- The following is an example of a scalar function.
Create Function (salary int, bonus_percent INT)
Returns int
Language SQL contains SQL
Return (
Salary * bonus_percent/100
) -- The following table functions
Create Function get_marks (begin_range int, end_range INT)
Returns table (CID candidate_id,
Number test_id,
Score)
Language SQL reads SQL data
Return
Select CID, number, score
From test_taken
Where salary between (begin_range) and score (end_range) Example 1: Define a scalar function that returns the tangent of a value using the existing sine and Cosine functions. create function Tan (X double)
Returns double
Language SQL
Contains SQL
No external action
Deterministic
Return sin (X)/cos (x) Example 2: Define a transform function for the structured type person.
Create Function fromperson (P Person)
Returns row (name varchar (10), firstname varchar (10 ))
Language SQL
Contains SQL
No external action
Deterministic
Return values (P .. name, P .. firstname) Example 3: Define a table function that returns the employees in a specified department number.
Create Function deptemployees (deptno char (3 ))
Returns table (empno char (6 ),
Lastname varchar (15 ),
Firstname varchar (12 ))
Language SQL
Reads SQL data
No external action
Deterministic
Return
Select empno, lastname, firstnme
From employee
Where employee. workdept = deptemployees. deptnoexample 4: Define a scalar function that reverses a string. create function reverse (instr varchar (4000 ))
Returns varchar (4000)
Deterministic no external action contains SQL
Begin atomic
Declare revstr, reststr varchar (4000) default '';
Declare Len int;
If instr is null then
Return NULL;
End if;
Set (reststr, Len) = (instr, length (instr ));
While Len> 0 do
Set (revstr, reststr, Len)
= (Substr (reststr, 1, 1) Concat revstr,
Substr (reststr, 2, len-1 ),
Len-1 );
End while;
Return revstr;
Endexample 4: Define the table function from example 4 with auditing. create function deptemployees (deptno char (3 ))
Returns table (empno char (6 ),
Lastname varchar (15 ),
Firstname varchar (12 ))
Language SQL
Modifies SQL data
No external action
Deterministic
Begin atomic
Insert into audit
Values (user,
'Table: employee PRD: deptno = 'concat deptno );
Return
Select empno, lastname, firstnme
From employee
Where employee. workdept = deptemployees. deptno
End -- For statement usage Begin atomic
Declare fullname char (40 );
For VL
Select firstnme, midinit, lastname from employee
Do
Set fullname = lastname concat ','
Concat firstnme Concat ''Concat midinit;
Insert into tnames values (fullname );
End
End -- Leave usage Create procedure leave_loop (Out Counter integer)
Language SQL
Begin
Declare v_counter integer;
Declare v_firstnme varchar (12 );
Declare v_midinit char (1 );
Declare v_lastname varchar (15 );
Declare at_end smallint default 0;
Declare not_found condition for sqlstate '201312 ';
Declare C1 cursor
Select firstnme, midinit, lastname
From employee;
Declare continue handler for not_found
Set at_end = 1;
Set v_counter = 0;
Open C1;
Fetch_loop:
Loop
Fetch C1 into v_firstnme, v_midinit, v_lastname;
If at_end <> 0 then leave fetch_loop;
End if;
Set v_counter = v_counter + 1;
End loop fetch_loop;
Set counter = v_counter;
Close C1;
End -- If statement usage Create procedure update_salary_if
(In employee_number char (6), inout rating smallint)
Language SQL
Begin
Declare not_found condition for sqlstate '201312 ';
Declare exit handler for not_found
Set rating =-1;
If rating = 1
Then update employee
Set salary = salary * 1.10, bonus = 1000
Where empno = employee_number;
Elseif rating = 2
Then update employee
Set salary = salary * 1.05, bonus = 500
Where empno = employee_number;
Else update employee
Set salary = salary * 1.03, bonus = 0
Where empno = employee_number;
End if;
End -- Loop usage Create procedure loop_until_space (Out Counter integer)
Language SQL
Begin
Declare v_counter integer default 0;
Declare v_firstnme varchar (12 );
Declare v_midinit char (1 );
Declare v_lastname varchar (15 );
Declare C1 cursor
Select firstnme, midinit, lastname
From employee;
Declare continue handler for not found
Set counter =-1;
Open C1;
Fetch_loop:
Loop
Fetch C1 into v_firstnme, v_midinit, v_lastname;
If v_midinit = ''then
Leave fetch_loop;
End if;
Set v_counter = v_counter + 1;
End loop fetch_loop;
Set counter = v_counter;
Close C1;
End -- Return usage Begin
...
Goto fail
...
Success: Return 0
Fail: Return-200
End