sql学习总结

事务的四大特性(简称ACID)
⑴ 原子性（Atomicity）
　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

⑵ 一致性（Consistency）
　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

　　拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

⑶ 隔离性（Isolation）
　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

　　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
　　关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。
⑷ 持久性（Durability）
　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
　　例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。
　　1. 函数的使用

(1)大小写转换函数：

LOWER(‘SQL Course’) sql course
UPPER(‘SQL Course’) SQL COURSE
INITCAP(‘SQL course’) Sql Course

(2)字符串操作函数：

函数 结果
CONCAT(‘Hello’, ‘World’) HelloWorld
SUBSTR(‘HelloWorld’,1,5) Hello
LENGTH(‘HelloWorld’) 10
INSTR(‘HelloWorld’, ‘W’) 6
LPAD(salary,10,’*’) *****24000
RPAD(salary, 10, ‘‘) 24000****
TRIM(‘H’ FROM ‘HelloWorld’) elloWorld
TRIM(’ HelloWorld’) HelloWorld
TRIM(‘Hello World’) Hello World

(3)数字操作函数：

  函数 结果

ROUND(45.926, 2) 45.93
TRUNC(45.926, 2) 45.92
MOD(1600, 300) 100

(4)日期操作函数：

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194 

ADD_MONTHS (‘11-JAN-94’,6) 11-Jul-94
NEXT_DAY (‘01-SEP-95’,’FRIDAY’) 8-Sep-95
NEXT_DAY (‘01-SEP-95’,1) 3-Sep-95
NEXT_DAY (‘1995-09-01’,1)
NEXT_DAY (to_date(‘1995-09-01’,’YYYY-MM-DD’),1) 3-Sep-95
LAST_DAY(‘01-FEB-95’) 28-Feb-95
ROUND(‘25-JUL-95’,’MONTH’) 1-Aug-95
ROUND(‘25-JUL-95’ ,’YEAR’) 1-Jan-96
TRUNC(‘25-JUL-95’ ,’MONTH’) 1-Jul-95
TRUNC(‘25-JUL-95’,’YEAR’) 1-Jan-95

(5)其他常用单行函数：

NVL (expr1, expr2) 如果expr1为空，这返回expr2
NVL2 (expr1, expr2, expr3) 如果expr1为空，这返回expr3（第2个结果）否则返回expr2
NULLIF (expr1, expr2) 如果expr1和expr2相等，则返回空
COALESCE (expr1, expr2, …, exprn) 如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到 一个不为NULL的值 或者如果全部为NULL，也只能返回NULL 了

(6)典型使用

1.SELECT COUNT(1)
INTO l_customer_flag
FROM cux_om_customers_1 coc
WHERE coc.customer_id = g_om_headers_rec.customer_id
AND trunc(SYSDATE) BETWEEN coc.start_date_active AND nvl(coc.end_date_active,trunc(SYSDATE));
**

2.条件表达式：

**
实现方法： CASE 语句 或者DECODE函数，两者均可实现 IF-THEN-ELSE 的逻辑，相比较 而言，DECODE 更加简洁。
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END
DECODE语句：
DECODE(col|expression, search1, result1 [, search2, result2,…,] [, default])

1.典型使用：

SELECT (SUM(sums.sum)) / COUNT(sums.customer_id) AS avgscore
        INTO g_avg
        FROM (SELECT coh.customer_id,
                     SUM(decode(coh.order_status,
                                'NEW',
                                '10',
                                'UNAPPROVED',
                                '5',
                                'APPROVED',
                                '5',
                                'CANCELLED',
                                '-20',
                                'CLOSED',
                                '10',
                                'SUBMIT',
                                '5',
                                'REJECTED')) AS SUM
                FROM cux_om_headers_1 coh
               GROUP BY coh.customer_id) sums;

3.游标的使用和遍历

DECLARE
  CURSOR cur_cursor IS
    SELECT dts.department_id,
           dts.department_name,
           dts.manager_id,
           els.last_name,
           dts.location_id,
           lts.street_address

      FROM departments_1 dts,
           employees_1   els,
           locations_1   lts
     WHERE dts.manager_id = els.employee_id
       AND dts.location_id = lts.location_id
       and dts.department_id = &l_department_id
     ORDER BY dts.department_id;
BEGIN
  dbms_output.put_line('DEPARTMENT_ID' || chr(9) || 'DEPARTMENT_NAME' || chr(9) || 'MANAGER_ID' || chr(9) || 'MANAGER_NAME' || 'LOCATION_ID' ||
                       'STREET_ADDRESS');
  FOR l_dept_line IN cur_cursor
  LOOP
    dbms_output.put_line(l_dept_line.department_id || chr(9) || l_dept_line.department_name || chr(9) || l_dept_line.manager_id || chr(9) ||
                         l_dept_line.last_name || chr(9) || l_dept_line.location_id || chr(9) || l_dept_line.street_address || chr(9));
  END LOOP;
END;

– NO.04
/*
分别编写函数与存储过程，实现
输入参数p_department_id,输出参数为cux_ departments_xxxx
记录变量，当出现找不到值时，报错：
输入参数p_department_id有误，找不到相应记录，请检查
*/
– 创建函数

CREATE OR REPLACE FUNCTION cux_get_dept_infor_1(p_department_id NUMBER) 
RETURN departments_1%ROWTYPE IS
  l_departments_rec departments_1%ROWTYPE;
BEGIN
  SELECT *
    INTO l_departments_rec
    FROM departments_1 cds
   WHERE cds.department_id = p_department_id;

  RETURN l_departments_rec;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('输入参数'||p_department_id||'有误，找不到相应记录，请检查');
    RETURN NULL;
  WHEN OTHERS THEN
    dbms_output.put_line('erroy' || SQLERRM);
    RETURN NULL;
END;

– 使用函数

DECLARE
  l_departments_rec departments_1%ROWTYPE;
BEGIN
  l_departments_rec := cux_get_dept_infor_1(p_department_id => 10);
  dbms_output.put_line('Department Name:' || l_departments_rec.department_name);
END;

最简单的运用，遍历所有数据输出

DECLARE
--l_dept_line departments_1%ROWTYPE;
  CURSOR dept_cursor IS
  SELECT *
    FROM departments_1 d;
BEGIN
  DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID' || CHR(9) || 'DEPARTMENT_NAME' || CHR(9) || 'MANAGER_ID' || CHR(9) || 'LOCATION_ID');
  FOR l_dept_line IN dept_cursor LOOP
    DBMS_OUTPUT.PUT_LINE(l_dept_line.department_id || CHR(9) || l_dept_line.department_name || CHR(9) || l_dept_line.manager_id || CHR(9) || l_dept_line.location_id);
  END LOOP;
END;

4.异常处理
1.自定义异常：

DECLARE
  p_department_id   NUMBER;
  x_departments_rec departments_3%ROWTYPE;

  CURSOR csr_departments(p_department_id IN NUMBER) IS
    SELECT *
      FROM departments_3 dts
     WHERE dts.department_id = p_department_id;

  e_noreason_exception EXCEPTION;
  l_error_message VARCHAR2(300);
BEGIN
  p_department_id := 10;

  BEGIN
    SELECT *
      INTO x_departments_rec
      FROM departments_3 dts
     WHERE dts.department_id = p_department_id;

    raise_application_error(-20000,
                            'XXXXXXXXXXX错误');
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := 'XXX错误';
      dbms_output.put_line('1' || l_error_message);
      RAISE e_noreason_exception;
  END;

  BEGIN
    SELECT *
      INTO x_departments_rec
      FROM departments_3 dts
     WHERE dts.department_id = p_department_id + 9999;
  EXCEPTION
    WHEN OTHERS THEN
      l_error_message := l_error_message || 'XXX错误';
      dbms_output.put_line('2' || l_error_message);
  END;

  IF l_error_message IS NOT NULL THEN
    RAISE e_noreason_exception;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('error' || SQLERRM);
  WHEN e_noreason_exception THEN
    dbms_output.put_line('3' || l_error_message);
    dbms_output.put_line('No Reason Error' || l_error_message);
  WHEN OTHERS THEN
    dbms_output.put_line('error' || SQLERRM);
END; 

5．函数
1.最简单的案例（无传值）：

CREATE OR REPLACE FUNCTION cux_get_dept_name1001_func RETURN VARCHAR2 AS
  l_department_id NUMBER;
  l_dept_name     VARCHAR2(30);
  l_manager_id NUMBER;
BEGIN
  l_department_id := 20;

  SELECT cds.department_name,cds.manager_id
    INTO l_dept_name,l_manager_id
    FROM cux_departments_21083 cds
   WHERE cds.department_id = l_department_id;

  dbms_output.put_line('Dept Name:' || l_dept_name);
  dbms_output.put_line('Manager Id:' || l_manager_id);

  RETURN l_dept_name;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception:' || SQLERRM);
END; 

1. 进一点点阶：

CREATE OR REPLACE FUNCTION cux_get_dept_infor_1(p_department_id NUMBER) 
RETURN departments_1%ROWTYPE IS
  l_departments_rec departments_1%ROWTYPE;
BEGIN
  SELECT *
    INTO l_departments_rec
    FROM departments_1 cds
   WHERE cds.department_id = p_department_id;

  RETURN l_departments_rec;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('输入参数'||p_department_id||'有误，找不到相应记录，请检查');
    RETURN NULL;
  WHEN OTHERS THEN
    dbms_output.put_line('erroy' || SQLERRM);
    RETURN NULL;
END;

– 使用函数

DECLARE
  l_departments_rec departments_1%ROWTYPE;
BEGIN
  l_departments_rec := cux_get_dept_infor_1(p_department_id => 10);
  dbms_output.put_line('Department Name:' || l_departments_rec.department_name);
END;

6.存储过程
1.最简单的案例：

CREATE OR REPLACE PROCEDURE cux_get_dept_name1001 AS
  l_department_id NUMBER;
  l_dept_name     VARCHAR2(30);
  l_manager_id NUMBER;
BEGIN
l_department_id := 20;

  SELECT cds.department_name,cds.manager_id
    INTO l_dept_name,l_manager_id
    FROM departments_1 cds
   WHERE cds.department_id = l_department_id;
  dbms_output.put_line('Dept Name:' || l_dept_name);
  dbms_output.put_line('Manager Id:' || l_manager_id);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception:' || SQLERRM);
END;

调用：

DECLARE

BEGIN
  cux_get_dept_name1001;
END;

2进一点点阶：
– 创建存储过程

CREATE OR REPLACE PROCEDURE cux_prod_dept_infor_1(p_department_id   IN NUMBER,
                                                x_departments_rec OUT departments_1%ROWTYPE) IS
BEGIN
  SELECT *
    INTO x_departments_rec
    FROM departments_1 cds
   WHERE cds.department_id = p_department_id;

EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('输入参数'||p_department_id||'有误，找不到相应记录，请检查');
  WHEN OTHERS THEN
    dbms_output.put_line('erroy' || SQLERRM);
END;

--prcedure
DECLARE
  x_departments_rec departments_1%ROWTYPE;
BEGIN
  cux_prod_dept_infor_1( 20,x_departments_rec);
  dbms_output.put_line('Department Name:' || x_departments_rec.department_name);
END;

7.匿名块
1.案例
–1.使用匿名块，实现输入department_id 或location_id，通过dbms_output输出如下部门信息

DECLARE
  l_department_name VARCHAR(30);
BEGIN
  SELECT dts.department_name
    INTO l_department_name
    FROM departments_1 dts
   WHERE dts.department_id = &l_department_id;
  dbms_output.put_line('Department Name:' || l_department_name);
END;

--2.使用匿名块，实现输入department_id_from与department_id_to的值，通过dbms_output输出批量部门信息
DECLARE
  l_department_name VARCHAR(30);
  CURSOR cur_cursor IS
    SELECT dts.department_name
      INTO l_department_name
      FROM departments_1 dts
     WHERE dts.department_id BETWEEN &l_department_id_from AND &l_department_id_to;
BEGIN
  FOR l_dept_line IN cur_cursor
  LOOP
    dbms_output.put_line('Department Name:' || l_dept_line.department_name);
  END LOOP;
END;

8.触发器
1.创建触发器案例：

CREATE OR REPLACE TRIGGER cux_om_headers_rafiud_1
 after INSERT OR UPDATE OR DELETE ON cux_om_headers_1
--还有before
  FOR EACH ROW
BEGIN

  IF inserting THEN
    INSERT INTO cux_fnd_logs_1
      (log_id,
       action_code,
       object_name,
       object_id,
       action_by,
       action_date)
    VALUES
      (cux_fnd_logs_1_s.nextval,
       'INSERT',
       'CUX_OM_HEADERS_1',
       :new.HEADER_ID,
       FND_GLOBAL.USER_ID,
       SYSDATE);

  ELSIF deleting THEN
    INSERT INTO cux_fnd_logs_1
      (log_id,
       action_code,
       object_name,
       object_id,
       action_by,
       action_date)
    VALUES
      (cux_fnd_logs_1_s.nextval,
       'DELETE',
       'CUX_OM_HEADERS_1',
       :old.HEADER_ID,
       FND_GLOBAL.USER_ID,
       SYSDATE);

  ELSIF updating THEN
    IF :new.order_status = 'APPROVED'
       OR :new.order_status = 'CLOSED' THEN
      INSERT INTO cux_fnd_logs_1
        (log_id,
         action_code,
         object_name,
         object_id,
         action_by,
         action_date,
         comments)
      VALUES
        (cux_fnd_logs_1_s.nextval,
         'UPDATE',
         'CUX_OM_HEADERS_1',
         :new.HEADER_ID,
         FND_GLOBAL.USER_ID,
         SYSDATE,
         'ORDER_STATUS='||:new.ORDER_STATUS);

    END IF;
  END IF;
END;

2.使用OLD 和NEW修饰词

CREATE OR REPLACE TRIGGER audit_emp_values 
AFTER DELETE OR INSERT OR UPDATE ON employees
 FOR EACH ROW 
BEGIN 
INSERT INTO audit_emp_table(user_name, timestamp, id, old_last_name, new_last_name, old_title, new_title, old_salary, new_salary) 
VALUES(USER,SYSDATE, :OLD.employee_id, :OLD.last_name, :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary); 
END;

快捷操作：
2.复制表

CREATE TABLE CUX_PO_QUOTES_1_SYNC 
AS SELECT * FROM CUX_PO_QUOTES_1;

3，复制一行数据
1.特制方法复制一行数据

DECLARE
cux_om_headers_rec cux_om_headers_1%ROWTYPE;
BEGIN
  select *
  into cux_om_headers_rec
  from cux_om_headers_1 coh
  where coh.header_id=10001;
  cux_om_headers_rec.header_id:=10050;
  cux_om_headers_rec.order_number:=100000232;
CUX_OM_HEADERS_1_PKG.insert_row(cux_om_headers_rec);
END;

2.快速复制一行数据

INSERT INTO cux_om_headers_1
  SELECT cux_om_headers_1_s.nextval,
         org_id,
         1000003434,
         customer_id,
         order_date,
         order_status,
         description,
         created_by,
         creation_date,
         last_updated_by,
         last_update_date,
         last_update_login,
         attribute_category,
         attribute1,
         attribute2,
         attribute3,
         attribute4,
         attribute5,
         attribute6,
         attribute7,
         attribute8,
         attribute9,
         attribute10,
         attribute11,
         attribute12,
         attribute13,
         attribute14,
         attribute15
    FROM cux_om_headers_1 coh
   WHERE coh.header_id = 10001;

4.快码的使用
例：一般作为子查询

  (SELECT flv.meaning
          FROM fnd_lookup_values_vl flv
         WHERE flv.lookup_type = 'POS_ORDER_STATUS'----按情况修改
           AND flv.lookup_code = pha.document_status---按情况修改
           AND flv.enabled_flag = 'Y'
           AND SYSDATE BETWEEN nvl(flv.start_date_active,
                                   SYSDATE - 1) AND nvl(flv.end_date_active,SYSDATE)) AS header_status,