Oracle basic functions and object examples

Oracle basic functions and object examples

-- ## Character function, case-sensitive processing function select lower ('abcde') FROM dual; -- ABcDe, all converted to LOWER case select upper ('abcde') FROM dual; -- abcde, convert all to uppercase select initcap ('abcde') FROM dual; -- aBcDe, uppercase letter -- # character function, string processing function select substr ('abcde', 2, 2) FROM dual; -- Bc, truncates the string select substr ('abcde', 2) FROM dual; -- BcDe, truncates the string select substr ('abcde',-3, 2) FROM dual; -- cD, truncates the string select instr ('abcdefghddh', 'D',) FROM dual; -- 9, and queries the position of the second character d from the third string. SELECT INSTR ('abcdefddh', 'D') FROM dual; -- 4, query the position of the first character D starting FROM the first string select lpad ('abcde', 10, 'D') FROM dual; -- DDDDDABcDe, left side of the incomplete complement string dselect lpad ('abcde', 3, 'D') FROM dual; -- ABcSELECT RPAD ('abcde', 10, 'D') FROM dual; -- ABcDeDDDDD, less than the complement string dselect rpad ('abcde', 3, 'D') FROM dual; -- ABcSELECT REPLACE ('abcdeee ', 'E', 8) FROM dual; -- ABcD8E8, replace the character select length ('abcde') FROM dual; -- 5, calculate the string length select concat ('hello ', 'World') F ROM 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 the last letter select trim (''FROM 'helloworld') FROM dual; --, hide character -- numeric function select round (245.1234) FROM dual; -- 245 select round (245.1234, 2) FROM dual; -- 245.12 select round (245.6234, 0) FROM dual; -- 246 select round (245.1234,-2) FROM dual; -- 200 select round (255.1234 ,-2) FROM dual; -- 300 select round (-245.1234, 2) FROM dual; ---245.12 select round (-245.6234, 0) FROM dual; ---246 select round (-245.1234,-2) FROM dual; ---200 select round (-255.1234,-2) FROM dual; ---300 select trunc (245.1234, 2) FROM dual; -- 245.12 select trunc (245.6234, 0) FROM dual; -- 245 select trunc (245.1234,-2) FROM dual; -- 200 select trunc (255.1234,-2) FROM dual; -- 200 select mod (1600,300) FROM Dual; -- 100 select mod (1500,300) FROM dual; -- 0 -- Date function select sysdate from dual; -- April 12:50:18, current date SELECT MONTHS_BETWEEN ('15--2012 ', '01-July 22, 2012-3.45161290322581 ') FROM dual; --, SELECT ADD_MONTHS (SYSDATE, 2) FROM dual; -- January 14:23:32, SELECT NEXT_DAY ('15-April-2012 ', 3) FROM dual; --, add the number of days to the date field SELECT NEXT_DAY (SYSDATE, 'monday ') FROM dual; SELECT LAST_DAY (SYSDAT E) FROM dual; -- 14:25:22, select round (SYSDATE, 'month') FROM dual; --, the month is rounded to select round (SYSDATE) FROM dual; -- 2012-05-13SELECT TRUNC (SYSDATE, 'month') FROM dual; -- month truncation select trunc (SYSDATE, 'day') FROM dual; -- Conversion Function SELECT '123456' + 123 FROM dual; -- 246, the string is implicitly converted to SELECT TO_CHAR (123456.6543, 'l099, 100') AS "Number Format" FROM dual; -- if it is a constant Number, add 0 SELECT TO_CHAR (999.00, '$099, 999.00 ') AS "Number Format" FROM dual; SELECT TO_CHAR (SYSDATE, 'day DD-MON-YYYY') AS "Data Format" FROM dual; -- tues15-5-2012 SELECT TO_DATE ('12-5-2012 ', 'dd-MM-YYYY') FROM dual; -- the format string must have. Meaningless? SELECT TO_NUMBER ('2017. 1234 ') FROM dual; SELECT TO_NUMBER ('20140901', '20160901') FROM dual; -- the variable string must be consistent with the format of the format string -- Regular Function select nvl (NULL, 0) FROM dual; -- 0 select nvl (NULL, 'n'/A') FROM dual; -- N/aselect nvl (123) FROM dual; -- select nvl ('studen ', 'N'/A') FROM dual; -- SELECT NVL2 (NULL,) FROM dual; -- 1 SELECT NVL2 (, 1) FROM dual; -- 0 select nullif) FROM dual; -- nullselect nullif (2, 1) FROM dual; -- 2 select nullif ('hao', 'bad') FROM dual; -- good select coalesce (NULL, NULL, 'bad', 'hao ') FROM dual; -- not good, the parameter must be of the same data type -- DECODE example decode select last_name, job_id, salary, DECODE (job_id, 'it _ prog', 1.10 * salary, 'st _ cler', 1.15 * salary, 'sa _ REP ', 1.20 * salary, salary) REVISED_SALARYFROM employees; -- case then example case when then elseendselect last_name, job_id, salary, CASE job_id WHEN 'It _ prog' THEN 1.10 * salary WHEN 'st _ cler' THEN 1.15 * salary WHEN 'sa _ REP 'THEN 1.20 * salary ELSE salary END "REVISED_SALARY" FROM employees; -- ## grouping functions AVGCOUNTMAXMINSUMSTDDEVVARIANCE/* IN, ANY, and ALL operators IN are equal to ANY one IN the list. <ANY means lower than the highest value;> ANY means higher than the lowest value; = ANY is equivalent to IN. <ALL means lower than the lowest value;> ANY means higher than 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 the script example insert into statements ments (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 into a data table. If this row exists, UPDATE is executed. If it is a new row, INSERT example: INSERT or UPDATE rows in the COPY_EMP table to match the EMPLOYEES table with merge into copy_emp c USING employees e ON (c. employee_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_name, 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 statement example alter table dept80ADD (job_id VARCHAR2 (9 )); alter table dept80MODIFY (last_name VARCHAR2 (30); alter table dept80DROP COLUMN job_id; -- set unused to mark all unavailable fields in the TABLE: alter table emp set unused (department_id) alter table emp drop unused columns; -- an unavailable field cannot be deleted. Delete all. -- RENAME object RENAME employees2 TO emp -- COMMENT add field 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 (10) not null, CONSTRAINT emp_emp_id_pk primary key (EMPLOYEE_ID); create table employees (employee_id NUMBER (6), email VARCHAR2 (25 ),... department_id NUMBER (4), CONSTRAINT emp_dept_fk foreign key (department_id) REFERENCES parameters (department_id), CONSTRAINT emp_email_uk UNIQUE (email); create table employees (employee_id NUMBER (6 ), hire_date date not null ,... CONSTRAINT emp_email_uk UNIQUE (email); create table 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; alter table primary key cascade; -- delete the primary key constraint on the orders TABLE and delete employees. the foreign key constraint associated with the department_id column. Alter table employeesDISABLE CONSTRAINT emp_emp_id_pk CASCADE; -- disable constraint alter table employeesENABLE CONSTRAINT emp_emp_id_pk; -- enable CONSTRAINT/* cascade constraint the cascade constraints clause is used together with the drop column cla. The cascade constraints clause deletes all reference integrity CONSTRAINTS related to the primary key or unique keywords defined in the deleted column. The cascade constraints clause also deletes the distinct 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 key (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 use the with check option clause create or replace view empvu20AS SELECT * FROM employeesWHERE department_id = 20 with check option constraint empvu20_ck -- reference CONSTRAINT empvu20_ck -- VIEW rejects DML operations with read only -- Sort TOP-N Analysis: pseudo-column ROWNUM keywords 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 -- slave bit NOCACHE -- NOCYCLE; -- When the bytes are reached, do not re-start the cycle alter sequence into increment by 20 MAXVALUE 999999 nocache nocycle; -- pseudo columns in the SEQUENCE, nextval currvalinsert into orders (department_id, department_name, location_id) VALUES, 'support', 2500); SELECT distinct dual; -- index create index emp_last_name_idxON employees (last_name); -- SYNONYM: create synonym empFOR employees; create public synonym deptFOR attributes