Sign function and decode function __c language in Oracle

Compare size function Sign

function Syntax:
Sign (n)

Function Description:
Take the symbol of number n, greater than 0 to return 1, less than 0 to return-1, equal to 0 to return 0

Example:
One, select sign (m), sign (-M), sign (0) from dual;

SIGN (+) SIGN ( -100) SIGN (0)
———- ———- ———-
1-1 02, a=10,b=20
Then sign (a-b) returns-1 flow control function DECODE

Introduction to Functions

The Decode function is one of the powerful functions of Oracle Pl/sql, and only Oracle's SQL provides this function, and the SQL implementations of other database vendors do not yet have this functionality. What is the use of decode? To construct an example, suppose we want to add a salary to the intelligence Star staff, the standard is: The salary is under 8000 yuan plus 20%, the salary is 8000 yuan or above plus 15%, the usual practice is to first select the record in the wage field value? Select salary into Var-salary from employee, and then the variable var-salary is judged by a flow control statement such as If-then-else or choose case. If you use the Decode function, then we can omit these flow control statements, through the SQL statement can be directly completed. As follows: Select Decode (sign (salary-8000), >=0,salary*1.15,<0,salary*1.2,salary) from employee is not very concise. DECODE syntax: DECODE (value,if1,then1,if2,then2,if3,then3,..., else), indicating that if value equals IF1, the result of the DECODE function returns to Then1,..., Returns else if it is not equal to any one if value. At first glance, DECODE can only do equals test, but just also saw that we use some function or calculate substitution value, is can make DECODE function have greater than, less than or equal to function.about decodeDecode is an exclusive feature provided by Oracle, and it is a powerful function. Although it is not a standard for SQL, it is useful for performance. To date, other database vendors have not been able to provide functionality similar to decode, and even some database vendors have criticized Oracle's SQL for not being standard. In fact, this criticism is somewhat one-sided or not level enough. It's like some wagon makers complaining about Henry. Ford's "Wagon" is not standard.the If-then-else logic in the 1 DECODEIn logic programming, If–then–else is often used to make logical judgments. In Decode's syntax, this is actually the logical process. Its syntax is as follows: DECODE (value, IF1, Then1, If2,then2, If3,then3, ... else) value represents any column of any type of table or any result that is computed. When each value is tested, if value is the result of the If1,decode function is then1, and if value equals If2,decode function The result is then2; In fact, multiple if/then pairs can be given.   　　If the value result is not equal to any pairing given, the Decode result returns else. Note that the IF, then, and else here can all be functions or calculation expressions.2 simple examples of DECODEThere are many data dictionaries in Oracle systems that are designed using decode ideas, such as the v$session data dictionary view that records session information. We learned from the "oracle8i/9i Reference" material that when the user login successfully in V$session there is a corresponding record of the user, but the user's command operation in this view only record the command code (0-no operation, 2-insert ... Instead of the specific command keyword. So we need to know the names of the current users and what they are doing, and use the following command to get detailed results: Select Sid,serial#,username, DECODE (command, 0, ' None ', 2, ' in Sert ', 3, ' Select ', 6, ' Update ', 7, ' Delete ', 8, ' Drop ', ' other '-Cmmand from v$session where username is isn't null;3 decode to realize table transpose 　　A table in a database is a two-dimensional table consisting of columns and rows. A general column is a limited number in any database, and the row changes a lot, and if the table is large, the number of rows can be tens of millions of rows. Different rows of the same column may have different values, and are not predefined.   Example: Housing Provident Fund Report Replacement Example:   1. Each unit in the local handling bank to open an account, the account is the unit's basic information and staff information of the registration;   2. The monthly accounting of each unit to the handling bank to pay the units of all employees of the Housing fund, system records have every The payment details of a worker and the code of the handling line are recorded on each record;   3. Monthly, quarterly, Half-year and year-end requirements will be changed to "column" to give a detailed report of the month:   Handling line: Chengxi District dongqu   Month:   2001.01 xxxx1.xx xxxxx2.xx   2001.02 xxxx3.xx xxxxx4.xx   ...   The original data order is:   Chengxi District 2001.01 xxxxx1.xx   dongqu 2001.01 xxxxx2.xx   Chengxi District 2001.02 xxxxx3.xx   Dongqu 2 001.02 xxxxx4.xx   Housing Provident Fund System records the monthly payment of employees ' PAY_LST table structure is:   Bank_code varchar2 (6) Not NULL,--handling line code   ACC_NO VARCHAR2 NOT NULL,--Unit Code (unit account number)   Emp_acc_no varchar2 () NOT NULL,-employee account   Tran_date date not NULL, --Payment date   Tran_val number (7,2) not NULL,--payment   sys_date date default Sysdate,--system date   oper_id VA RCHAR2 (10)--operator code   Such a table structure, generally based on the handling of rows as rows (row) is easy, but if you want to change the handling line into columns (column) such a format to output is difficult. If you use deThe code function is simple to handle:   We create a view to query the current PAY_LST table. The handling line code into a number of specific handling line name can:   CREATE OR REPLACE VIEW bank_date_lst as   Select to_char (tran_date, ' yyyy.mm '), &nbsp 　　; Sum (DECODE (Bank_code, ' 001 ', tran_val,0)) Chengxi District,   sum (DECODE (Bank_code, ' 002 ', tran_val,0)) city South,   sum ( DECODE (Bank_code, ' 003 ', tran_val,0)) Dongqu   from Pay_lst   GROUP by To_char (tran_date, ' yyyy.mm ');   When you create a view, you can query the view directly to show the results in columns.