如果你能熟悉并且能够书写以下的sql语句，那么在日常的开发中，一下的SQL已经足够使用了。
--建表示例 create table
CREATE TABLE daimami.com.HIGH_COMM(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
COMM NUMBER(7,2),
HIREDATE DATE DEFAULT SYSDATE,
EMAIL VARCHAR(50)
CONSTRAINT EMAIL01 NOT NULL,
NACOUNTRY VARCHAR2(20),
CONSTRAINT COUNTRY01
CHECK (NACOUNTRY IN ('USA', 'CANADA', 'MEXICO' ))
TABLESPACE TESTSPACE
STORAGE (INITIAL 2048
NEXT 2048
MINEXTENTS 1
MAXEXTENTS 2);
--修改表
--The constraint check_comp ensures that no employee's total compensation exceeds $5000.
--The constraint is disabled, so you can increase an employee's compensation above this limit.
ALTER TABLE employees ADD CONSTRAINT check_comp
CHECK (salary + (commission_pct*salary) <= 5000)
DISABLE;
ALTER TABLE employees
ENABLE ALL TRIGGERS;
--Deallocating Unused Space: Example
ALTER TABLE employees
DEALLOCATE UNUSED;
--Renaming a Column
ALTER TABLE customers
RENAME COLUMN credit_limit TO credit_amount;
--视图示例
CREATE VIEW emp_sal (emp_id, last_name,
email UNIQUE RELY DISABLE NOVALIDATE,
CONSTRAINT id_pk PRIMARY KEY (emp_id) RELY DISABLE NOVALIDATE)
AS SELECT employee_id, last_name, email FROM employees;
--sequence
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
ALTER SEQUENCE customers_seq
CYCLE
CACHE 5;
--index
CREATE INDEX ord_customer_ix
ON orders (customer_id);
--synonym
CREATE SYNONYM offices
FOR hr.locations;
--函数示例
CREATE FUNCTION get_bal(acc_no IN NUMBER) RETURN NUMBER IS
acc_bal NUMBER(11, 2);
BEGIN
SELECT order_total INTO acc_bal FROM orders WHERE customer_id = acc_no;
RETURN(acc_bal);
END;
/
--PL/SQL 过程示例
CREATE TABLE SCOTT.ROOMS(room_id INTEGER,scount INTEGER, cap INTEGER, rname VARCHAR2(20));
INSERT INTO SCOTT.ROOMS
(room_id, scount, cap, rname)
VALUES
(101, 20, 20, 'First Room');
CREATE OR REPLACE PROCEDURE addstudent(roomin IN INTEGER) IS roomname VARCHAR2(20);
studentcount PLS_INTEGER;
capacity PLS_INTEGER;
noroom EXCEPTION;
BEGIN
SELECT scount, cap, rname
INTO studentcount, capacity, roomname
FROM scott.rooms
WHERE room_id = roomin;
IF studentcount > capacity - 1 THEN
RAISE noroom;
ELSE
UPDATE scott.rooms
SET scount = studentcount + 1
WHERE room_id = roomin;
COMMIT;
DBMS_OUTPUT.put_line('Student count now ' || studentcount || ' in ' ||
roomname);
END IF;
EXCEPTION
WHEN noroom THEN
DBMS_OUTPUT.put_line('There is no room in ' || roomname);
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error ' || SQLERRM || ' occurred.');
END;
--游标示例
CREATE OR REPLACE PROCEDURE promotion_review_2
IS
CURSOR cselectjob
IS
SELECT employee_id, start_date, end_date, job_id
FROM hr.job_history;
BEGIN
FOR jh_rec IN cselectjob
LOOP
DBMS_OUTPUT.put_line ( 'Employee '
|| jh_rec.employee_id
|| ' had job '
|| jh_rec.job_id
|| ' for '
|| (jh_rec.end_date - jh_rec.start_date)
|| ' days.'
);
END LOOP;
END;
--触发器示例
CREATE OR REPLACE TRIGGER update_job_history
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;
--获得系统的SCN
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
--Creating Materialized Aggregate Views: Example
CREATE MATERIALIZED VIEW LOG ON times
WITH ROWID, SEQUENCE (time_id, calendar_year)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON products
WITH ROWID, SEQUENCE (prod_id)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;