游标

commit的提交针对的是:DML

Data Manipulation Language(DML) 需要提交，这部分是对数据管理操作，比如Insert（插入）、Update（修改）、Delete（删除），
Data Definition Language(DDL) 不需要提交，这部分是对数据结构定义，比如 Create（创建）、Alter（修改)、Drop（删除）

 rollback 回滚

SQL> desc table 查看表属性

 

游标：当PL/Sql执行一条SQL语句时候，oracle为之分配一个私有的工作区，称之为游标。它存储了该SQL语句，游标属性以及该语句的执行结果。

可分为显式游标和隐式游标， 显示游标分为：动态游标和静态游标。

 

游标有四个属性

隐式游标【%found (如果操作到数据行，则为ture。如果加commit，则为false ） %notfound （与%found相反） %rowcount （操作的数据行数，如果为commit，结果为0.）%isopen（布尔型  执行后自动关闭为false）】  

显式游标【%found (最近一次fetch到数据行，则为true，初始值为null ），%notfound(与%found相反，初始值为null) %rowcount （已提取的数据行数，初始值为0）%isopen （游标开启时候，为true）】

--插入到顾客表中数据

DECLARE
  C_CID   NUMBER(5) := 1010;
  C_CNAME VARCHAR2(18) := '布鲁斯';
  C_CSEX  VARCHAR2(10) := '男';
  C_CARD  VARCHAR2(18) := '462456455878936415';
  C_CTEL  VARCHAR2(11) := '15878966521';
  C_CTYPE INTEGER := 2;
  C_EMAIL VARCHAR2(20) := '1112.qq.com';
  C_STATE INTEGER := 1;
BEGIN
  INSERT INTO CUSTOMER
  VALUES
    (C_CID, C_CNAME, C_CSEX, C_CARD, C_CTEL, C_CTYPE, C_EMAIL, C_STATE);
  COMMIT;
END;
---- 
SELECT * FROM CUSTOMER;
----更改数据 顾客表
----隐式游标
DECLARE
  C_CSEX  VARCHAR2(10) := '女';
  C_CTYPE INTEGER := 3;
BEGIN
  UPDATE CUSTOMER C
     SET C.CSEX = C_CSEX, C.CTYPE = C_CTYPE
   WHERE C.CID = 1010;
  COMMIT;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('not found');
  END IF;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE(' found');
  END IF;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

  IF NOT SQL%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('-----');
  END IF;
END;

--显式游标
DECLARE
  CURSOR YB IS
    SELECT C.CID, C.CNAME FROM CUSTOMER C WHERE C.STATE = 1;
  C_CID  CUSTOMER.CID%TYPE;
  C_NAME VARCHAR2(30);

BEGIN
  OPEN YB;
  --found和not found初始值
  IF YB%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('found');
  ELSIF YB% NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('not found');
  END IF;

  LOOP
    FETCH YB
      INTO C_CID, C_NAME;
  
    EXIT WHEN YB%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(C_CID || ' ' || C_NAME);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('共查有：' || YB%ROWCOUNT || '行'); --返回提取多少行
  IF YB%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('is open');
  END IF;

  CLOSE YB;
  IF NOT YB%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('is  close');
  END IF;

END;

--自定义record类型
DECLARE
  -- TYPE cc IS Record(c_id NUMBER(8) ,C_NAME VARCHAR2(8));
  TYPE CC IS RECORD(
    C_CID  CUSTOMER.CID%TYPE,
    C_NAME VARCHAR2(8));
  CS CC;
  --CS (TYPE cc IS Record( C_CID  CUSTOMER.CID%TYPE ,C_NAME VARCHAR2(8)))%TYPE;

  CURSOR YB IS
    SELECT C.CID, C.CNAME FROM CUSTOMER C WHERE C.STATE = 1;
BEGIN
  OPEN YB;
  LOOP
    FETCH YB
      INTO CS;
    EXIT WHEN YB%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CS.C_CID || ' ' || CS.C_NAME);
  END LOOP;
  CLOSE YB;
END;

SELECT * FROM EMP FOR UPDATE;
--更新多组数据
DECLARE
  DT EMP.DEPTNO%TYPE := 10;
BEGIN
  UPDATE EMP E SET E.DEPTNO = DT WHERE E.HIREDATE < SYSDATE;
  COMMIT;
END;

---游标for循环 自动定义record变量，自动打开关闭游标，自动提取数据至record，自动终止循环
DECLARE
  CURSOR YB IS
    SELECT C.CID, C.CNAME FROM CUSTOMER C WHERE C.STATE = 1;

BEGIN
  FOR NB IN YB LOOP
    DBMS_OUTPUT.PUT_LINE(NB.CID || ' ' || NB.CNAME);
  END LOOP;
END;

--子查询的游标for循环 (不同显式定义游标)

BEGIN
  FOR SS IN (SELECT C.CID, C.CNAME FROM CUSTOMER C WHERE C.STATE = 1) LOOP
    DBMS_OUTPUT.PUT_LINE(SS.CID || ' ' || SS.CNAME);
  END LOOP;
END;

--Select .... for update    
SELECT * FROM CUSTOMER  c  WHERE  c.csex='男'   FOR UPDATE nowait;
SELECT * FROM CUSTOMER  c  WHERE  c.csex='男'  for update wait 3;
ROLLBACK;--回滚
COMMIT;--提交
---Select .... for update 游标
DECLARE
  CURSOR CYB IS
    SELECT * FROM CUSTOMER C WHERE C.STATE = 0 FOR UPDATE;
BEGIN
    FOR xh IN cyb LOOP
			
		IF xh.ctype IS NULL THEN
			UPDATE customer c SET c.ctype=1 WHERE current OF cyb;
			END IF;
			
			END LOOP;
			COMMIT;
END;


--删除顾客号为1010
DECLARE
  C_CID NUMBER(5) := 1010;
BEGIN
  DELETE CUSTOMER C WHERE C.CID = C_CID;
  COMMIT;
END;