Oracel数据库PL/SQL编程

将以下程序考到pl/sql中，逐个研究吧，总有一个你会用到

--块的概念

DECLARE
  ROW_ID UROWID;
  INFO   VARCHAR2(400);
BEGIN
  INSERT INTO MYTEST
  VALUES
    (4, 'SSS', 18, 'MEN', 'WUEOQWUJJDSADAS')
  RETURNING rowid, MY_ID || ':' || MY_NAME || ':' || MY_TEXT INTO ROW_ID, INFO;
  DBMS_OUTPUT.PUT_LINE('ROWID:' || row_id);
  DBMS_OUTPUT.PUT_LINE(INFO);
END;
--结构体的概念
DECLARE
  TYPE test_rec IS RECORD(
    V_ID   NUMBER(10) NOT NULL := 1,
    V_NAME VARCHAR2(32) := 'LIMING');
  V_RECORD test_rec;
BEGIN
  V_RECORD.V_ID   := 123;
  V_RECORD.V_NAME := 'XIAOMING';
  DBMS_OUTPUT.PUT_LINE(V_RECORD.V_ID || '---' || V_RECORD.V_NAME);
END;
--引用类型
DECLARE
  V_ID   MYTEST.MY_ID%TYPE;
  V_NAME MYTEST.MY_NAME%TYPE;
BEGIN
  SELECT A.MY_ID, A.MY_NAME
    INTO V_ID, V_NAME
    FROM MYTEST A
   WHERE A.MY_ID = 2;
  DBMS_OUTPUT.PUT_LINE(V_ID || '----' || V_NAME);
END;


DECLARE
  TYPE test_rec IS RECORD(
    V_ID   MYTEST.MY_ID%TYPE,
    V_NAME MYTEST.MY_NAME%TYPE);
  V_RECORD test_rec;
  V_INFO   VARCHAR2(400);
BEGIN
  SELECT A.MY_ID, A.MY_NAME
    INTO V_RECORD.V_ID, V_RECORD.V_NAME
    FROM MYTEST A
   WHERE A.MY_ID = 1;
  V_INFO := V_RECORD.V_ID || '------' || V_RECORD.V_NAME;
  DBMS_OUTPUT.put_line(V_INFO);
END;
--引用行类型
DECLARE
  V_ID  MYTEST.MY_ID%TYPE := 1;
  V_ROW MYTEST%ROWTYPE;
BEGIN
  SELECT * INTO V_ROW FROM MYTEST A WHERE A.MY_ID = V_ID;
  DBMS_OUTPUT.put_line(V_ROW.MY_ID || '---' || V_ROW.MY_NAME || '---' ||
                       V_ROW.MY_AGE || '---' || V_ROW.MY_TEXT);
END;
--自定义类型
DECLARE
  SUBTYPE I_TYPE IS NUMBER(1, 0);
  V_PARE I_TYPE;
BEGIN
  V_PARE := 2;
END;
--类型转换
DECLARE
  V_CHAR VARCHAR(40);
BEGIN
  V_CHAR := TO_CHAR(SYSDATE, 'YYYYMMDD HH:mi:ss');
  DBMS_OUTPUT.put_line(V_CHAR);
END;
--IF流程控制语句
DECLARE
  V_PARA1  MYTEST.MY_SEX%TYPE;
  V_COMMIT VARCHAR2(200);
  V_Emess  VARCHAR2(200);
BEGIN
  SELECT A.MY_SEX INTO V_PARA1 FROM MYTEST A WHERE A.MY_ID = 8;
  IF V_PARA1 = 'MEN' THEN
    V_COMMIT := '我是个男生';
  ELSIF V_PARA1 = 'WOMEN' THEN
    V_COMMIT := '我是个女生';
  ELSE
    V_COMMIT := '我是个变态';
  END IF;
  DBMS_OUTPUT.put_line(V_COMMIT);
EXCEPTION
  WHEN OTHERS THEN
    V_Emess := substr(SQLERRM, 1, 80);
    DBMS_OUTPUT.PUT_LINE(V_Emess);
END;
--LOOP流程控制语句
DECLARE
  V_PARA1 NUMBER(1, 0) := 0;
BEGIN
  LOOP
    V_PARA1 := V_PARA1 + 1;
    DBMS_OUTPUT.put_line(V_PARA1);
    EXIT WHEN V_PARA1 = 9;
  END LOOP;
END;
--WHILE流程控制语句
DECLARE
  V_PARA1 NUMBER(1, 0) := 0;
BEGIN
  WHILE V_PARA1 < 9 LOOP
    V_PARA1 := V_PARA1 + 1;
    DBMS_OUTPUT.put_line(V_PARA1);
  END LOOP;
END;
--FOR IN流程控制语句
DECLARE
  V_PARA1 NUMBER(1, 0) := 0;
BEGIN
  FOR I IN 1 .. 9 LOOP
    DBMS_OUTPUT.put_line(I);
  END LOOP;
END;
--GOTO流程控制语句
DECLARE
  V_counter NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:' || V_counter);
    V_counter := v_counter + 1;
    IF v_counter > 10 THEN
      GOTO l_ENDofLOOP;
    END IF;
  END LOOP;
  <<l_ENDofLOOP>>
  DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:' || V_counter);
END;
--游标的使用
DECLARE
  --定义游标
  CURSOR CUR_TEMP IS SELECT A.MY_ID,A.MY_NAME FROM MYTEST A;
  V_ID MYTEST.MY_ID%TYPE;
  V_NAME MYTEST.MY_NAME%TYPE;
BEGIN
  --打开游标
  OPEN CUR_TEMP;
  --循环遍历游标
  FETCH CUR_TEMP INTO V_ID,V_NAME;
  WHILE CUR_TEMP%FOUND LOOP
    DBMS_OUTPUT.put_line(V_ID||'----'||V_NAME);
    FETCH CUR_TEMP INTO V_ID,V_NAME;
  END LOOP;
  --关闭游标
  CLOSE CUR_TEMP;
END;




DECLARE
  --定义游标
  CURSOR CUR_TEMP IS SELECT * FROM MYTEST A;
  V_PARA MYTEST%ROWTYPE;
BEGIN
  --打开游标
  OPEN CUR_TEMP;
  --循环遍历游标
  FETCH CUR_TEMP INTO V_PARA;
  WHILE CUR_TEMP%FOUND LOOP
    DBMS_OUTPUT.put_line(V_PARA.MY_ID||'----'||V_PARA.MY_NAME||'----'||V_PARA.MY_SEX);
    FETCH CUR_TEMP INTO V_PARA;
  END LOOP;
  --关闭游标
  CLOSE CUR_TEMP;
END;
--%FOUND       布尔型属性，当最近一次读记录时成功返回,则值为TRUE；
--%NOTFOUND   布尔型属性，与%FOUND相反；
--%ISOPEN       布尔型属性，当游标已打开时返回 TRUE；
--%ROWCOUNT   数字型属性，返回已从游标中读取的记录数。
--隐式游标SQL%
DECLARE
V_ID MYTEST.MY_ID%TYPE :=4;
BEGIN
DELETE FROM MYTEST A WHERE A.MY_ID=V_ID;COMMIT;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.put_line('没有找到要删除的信息');
END IF;
END;
--游标修改删除记录
DECLARE
  V_EMP MYTEST%ROWTYPE;
  CURSOR CUR_EMP IS
    SELECT * FROM MYTEST A FOR UPDATE;
BEGIN
  OPEN CUR_EMP;


  LOOP
    FETCH CUR_EMP
      INTO V_EMP;
    EXIT WHEN CUR_EMP%NOTFOUND;
    IF V_EMP.MY_AGE > 12 THEN
      UPDATE MYTEST B SET B.MY_AGE = 88 WHERE CURRENT OF CUR_EMP;
      
    END IF;
  END LOOP;
  COMMIT;
  CLOSE CUR_EMP;
END;
--游标变量
DECLARE
  --定义变量类型
  V_EMP MYTEST%ROWTYPE;
  --定义强类型游标变量
  TYPE MYTESTCURSORONE IS REF CURSOR RETURN MYTEST%ROWTYPE;
  --定义弱类型游标变量
  TYPE MYTESTCURSORTWO IS REF CURSOR;
  
  CURSORONE MYTESTCURSORONE;
  
  CIRSORTWO MYTESTCURSORTWO;
BEGIN
  --打开游标变量
  OPEN CURSORONE FOR SELECT * FROM MYTEST A WHERE A.MY_ID=1;
  --提取游标变量数据
  FETCH CURSORONE INTO V_EMP;
  --关闭游标
  CLOSE CURSORONE;
  
  DBMS_OUTPUT.put_line(V_EMP.MY_NAME);
END;
--异常处理
--预定义异常
DECLARE
  V_AGE MYTEST.MY_AGE%TYPE;
BEGIN
  SELECT A.MY_AGE INTO V_AGE FROM MYTEST A WHERE A.MY_ID=2;
  IF V_AGE>18 THEN
    UPDATE MYTEST B SET B.MY_AGE=B.MY_AGE+1 WHERE B.MY_ID=2;
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line('年龄都小于18岁');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('数据库中没有编码为4的员工');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;
--非预定义异常
DECLARE
  V_NAME MYTEST.MY_NAME%TYPE := 'SSS';
  --定义异常
  E_MYEXCEPTION EXCEPTION;
  --异常初始化
  PRAGMA EXCEPTION_INIT(E_MYEXCEPTION, -2292);
BEGIN
  DELETE FROM MYTEST A WHERE A.MY_NAME = V_NAME;
  COMMIT;
EXCEPTION
  WHEN E_MYEXCEPTION THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;
--用户自定义异常
DECLARE
  V_NO MYTEST.MY_ID%TYPE;
  --定义异常
  NO_RESULT EXCEPTION;
BEGIN
  UPDATE MYTEST A SET A.MY_AGE = 108 WHERE A.MY_ID = V_NO;
  IF SQL%NOTFOUND THEN
    --触发异常
    RAISE NO_RESULT;
  END IF;
EXCEPTION
  WHEN NO_RESULT THEN
    DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;
--使用 SQLCODE, SQLERRM
DECLARE
   err_msg  VARCHAR2(100);
BEGIN
   /*  得到所有 ORACLE 错误信息  */
   FOR err_num IN -100 .. 0 LOOP
      err_msg := SQLERRM(err_num);
      INSERT INTO errors VALUES(err_num, err_msg);
   END LOOP;
END;
--建立错误日志表将错误信息存入到日志表，维护系统时查询
--日志表既可以查询出哪里出错啦