Oracle basic functions and examples of Object learning Summary

Tag:oracle    basic function     Base Object    

--# #字符函数, Case processing function Select LOWER (' ABcDe ') from dual;  --ABCDE, all converted to lowercase select UPPER (' ABCDE ') from dual; --ABCDE, all converted to uppercase Select Initcap (' ABCDE ') from dual; --ABCDE, capitalized--# #字符函数, string processing function Select SUBSTR (' Abcde ', 2,2) from dual; --Bc, intercept string Select SUBSTR (' ABcDe ', 2) from dual; --BcDe, intercept string Select SUBSTR (' ABcDe ', -3,2) from dual; --CD, intercept string Select INSTR (' Abcdefgddh ', ' D ', 3,2) from dual; --9, starting from the third string query the position of the second character D select INSTR (' Abcdefgddh ', ' d ') from dual; --4, starting from the first string to query the position of the first character D select Lpad (' ABcDe ', ten, ' d ') from dual; --DDDDDABCDE, left insufficient complement string Dselect Lpad (' ABcDe ', 3, ' D ') from dual; --Abcselect Rpad (' ABcDe ', ten, ' D ') from dual; --ABCDEDDDDD, the right insufficient complement string Dselect rpad (' ABcDe ', 3, ' D ') from dual;    --Abcselect REPLACE (' abcdeee ', ' e ', 8) from dual; --abcd8e8, replacement character select LENGTH (' ABcDe ') from dual; --5, calculates the string length of select CONCAT (' Hello ', ' world ') from dual;    --HelloWorld, connection string Select TRIM (' H ' from ' HelloWorld ') from dual;    --elloworld, remove the first letter select TRIM (' d ' from ' HelloWorld ') from dual; --helloworl, remove tail Letter SelectTRIM (' from ' HelloWorld ') from dual; ---Hidden character--number function Select ROUND (245.1234) from dual;  --245select ROUND (245.1234,2) from dual;  --245.12select ROUND (245.6234,0) from dual;  --246select ROUND (245.1234,-2) from dual;  --200select ROUND (255.1234,-2) from dual;  --300select ROUND ( -245.1234,2) from dual;  ---245.12SELECT ROUND ( -245.6234,0) from dual;  ---246SELECT ROUND ( -245.1234,-2) from dual;  ---200SELECT ROUND ( -255.1234,-2) from dual;  ---300SELECT TRUNC (245.1234,2) from dual;  --245.12SELECT TRUNC (245.6234,0) from dual;  --245SELECT TRUNC (245.1234,-2) from dual;  --200SELECT TRUNC (255.1234,-2) from dual;       --200SELECT MOD (1600,300) from dual;       --100SELECT MOD (1500,300) from dual;    --0--Date function select Sysdate from dual;    --2012-05-12 12:50:18, current date Select Months_between (' 1 May-April-2012 ', ' January-January -2012 ') from dual;    --3.45161290322581, number of months between two dates select Add_months (sysdate,2) from dual;   --2012-07-12 14:23:32, add month to Date field in select Next_day (' 1 May-April -2012 ', 3) from dual; --2012-04-17, add days to Date field in select Next_day (sysdate, ' Monday ') from dual;    SELECT Last_day (sysdate) from dual;    --2012-05-31 14:25:22, the last day of the month select ROUND (sysdate, ' month ') from dual;    --2012-05-01, Month rounded select ROUND (sysdate) from dual;    --2012-05-13select TRUNC (sysdate, ' month ') from dual; --month intercept select TRUNC (sysdate, ' Day ') from dual;         --Conversion function Select ' 123 ' + 123 from dual;                                --246, String implicit conversion select To_char (123456.6543, ' l099,999.00 ') as "number Format" from dual; --If the constant number, it is necessary to add 0SELECT to_char (123456.6543, ' $099,999.00 ') as "numbers Format" from dual;                  Select To_char (sysdate, ' Day dd-mon-yyyy ') as "Data Format" from dual;                  --Tuesday 1 May-May -2012select to_date (' 12-5-2012 ', ' dd-mm-yyyy ') from dual; --The format string must have. No point? SELECT to_number (' 5432.1234 ') from dual;                  SELECT to_number (' 15,155,100.56 ', ' 999,999,999.00 ') from dual;     --The variable string must be in the same format as the format string-The general function Select NVL (null,0) from dual; --0SELECT NVL (NULL, ' n/a ') from dual; --N/aseLect NVL (123,0) from dual; --123SELECT NVL (' Student ', ' N/a ') from dual;  --Student Select NVL2 (null,0,1) from dual;  --1SELECT NVL2 (123,0,1) from dual;    --0SELECT Nullif (a) from dual;    --Nullselect Nullif (2,1) from dual;    --2SELECT Nullif (' good ', ' bad ') from dual;     --Good Select COALESCE (Null,null, ' bad ', ' good ') from dual; --Bad, parameters must be the same data type--decode example DECODE SELECT last_name, job_id, Salary,decode (job_id, ' It_prog ', 1.10*salary, ' St_clerk ', 1.15*salary, ' Sa_rep ', 1.20*salary, salary) Revised_salaryfrom Empl Oyees;--case then example case when and then Elseendselect last_name, job_id, salary,case job_id when ' It_prog ' TH EN 1.10*salary when ' St_clerk ' and 1.15*salary when ' sa_rep and then 1.20*salary ELSE salary END ' Revi    Sed_salary "from employees;--# #分组函数AVGCOUNTMAXMINSUMSTDDEVVARIANCE/*in, any, all operator in equals any of the list.    < any means that;> any above the highest value is above the lowest value; = any equals in. < all meansBelow the minimum value of;> any means above the highest low value.  Example: Select employee_id, last_name, job_id, Salaryfrom employeeswhere Salary < Any (select Salaryfrom employeeswhere    job_id = ' It_prog ') and job_id <> ' It_prog '; */--Create script sample INSERT into departments (department_id, Department_name, location_id) VALUES (& "Department id", ' & "    Department Name "', &location_id); The/*merge statement provides the ability to conditionally update or insert data in a data table. If the row exists, perform an update, and if it is a new row, execute the Insert example: INSERT or update rows in the Copy_emp table to match the Employees table merge into Copy_emp C USING employees E on (c.emp loyee_id = e.employee_id) When matched then UPDATE SET c.first_name = e.first_name, C.last_name = E.last_ Name, ... c.department_id = E.department_idwhen not matched then INSERT VALUES (e.employee_id, E.first_na         Me, E.last_name, E.email, E.phone_number, E.hire_date, e.job_id, E.salary, e.commission_pct, e.manager_id, E.DEPARTMENT_ID); */--explicit transaction control statement Commitrollbacksavepoint name_valuesrollback to savepoint name_values--alter table languageExample of a sentence alter TABLE Dept80add (job_id VARCHAR2 (9)); ALTER TABLE dept80modify (last_name VARCHAR2 (30)); ALTER TABLE Dept80drop COLUMN job_id;--set UNUSED tag unavailable with Delete table all unavailable field ALTER TABLE EMP SET UNUSED (department_id) Alter TAB       LE emp DROP UNUSED COLUMNS; --You cannot delete an unavailable field, and delete will delete all. --rename renaming an object rename Employees2 to emp--comment adding fields to the table comment on table employeesis ' Employee information ';--constraint type not   Nulluniqueprimary keyforeign keycheckcreate TABLE Employees (employee_id number (6), first_name VARCHAR2 (20), ... job_id VARCHAR2 (Ten) not null,constraint emp_emp_id_pk PRIMARY KEY (employee_id)); CREATE TABLE Employees (employee_id number (6), email VARCHAR2 (+), ... department_id number (4), CONSTRAINT EMP_DEPT_FK FOREIGN KEY (department_id) REFERENCES departments (department_id), CONSTRAINT emp_email_uk U Nique (email));  CREATE TABLE Employees (employee_id number (6), hire_date date not NULL, ... CONSTRAINT emp_email_uk UNIQUE (email)); CREATETABLE Employees (employee_id number (6), ... salary number (2) CONSTRAINT emp_salary_min CHECK (Salary > 0),.. . ALTER TABLE employeesadd CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id) REFERENCES employees (EMPLOYEE_ID); ALTER TABLE employeesdrop CONSTRAINT emp_manager_fk; ALTER TABLE departmentsdrop PRIMARY KEY CASCADE; --Delete the primary KEY constraint on the Departments table and delete the associated foreign KEY constraint on the employees.department_id column.   ALTER TABLE employeesdisable CONSTRAINT emp_emp_id_pk CASCADE; --Disable the constraint alter TABLE employeesenable CONSTRAINT EMP_EMP_ID_PK;    --Enable constraint/* cascading constraint CASCADE the constraints clause is used with the DROP column clause.    The CASCADE constraints clause removes all referential integrity constraints that involve a primary key or unique keyword defined on a deleted column. The CASCADE constraints clause also deletes the 鄋 multi-column constraint defined on the deleted column. CREATE TABLE test1 (PK number PRIMARY KEY, FK number, col1 number, col2 number,constraint fk_constraint FOREIGN K EY (FK) REFERENCES test1,constraint ck1 Check (pk > 0 and col1 > 0), CONSTRAINT ck2 check (col2 > 0)); ALTER TABLE test1 DROP (PK) CASCADE CONSTRAINTS; ALTER TABLE test1 DROP(PK, FK, col1) CASCADE constraints;*/--view using the WITH CHECK option clause create OR REPLACE view empvu20as SELECT * from Employeeswhere departmen t_id = 20WITH CHECK OPTION CONSTRAINT empvu20_ck--referential constraint empvu20_ck--view deny DML operation with READ only--sort Top-n Parse: pseudo-column rownum keyword select ROWNUM as RANK, last_name, salary from (SELECT last_name,salary from Employeesorder by salary DESC) WHERE ROWNUM <= 3;-                -Sequence Create SEQUENCE dept_deptid_seq INCREMENT by 10--step start with 120--Start bit            MAXVALUE 9999--end bit NOCACHE--nocycle; --reach end bit does not restart cycle alter SEQUENCE DEPT_DEPTID_SEQ INCREMENT by MAXVALUE 999999 NOC ACHE pseudo-Columns in nocycle;--sequences, Nextval Currvalinsert into departments (department_id,department_name,location_id) VALUES (Dept _deptid_seq. Nextval, ' support ', 2500); SELECT Dept_deptid_seq. Currvalfrom dual;--index CREATE INDEX Emp_last_name_idxon employees (last_name);--Synonyms Create Synonym Empfor employees; CREATE public synonym Deptfor departments

Oracle basic functions and examples of Object learning Summary