Oracle plsql学习笔记

--匿名PL/SQL语句块的声明
declare
   v_first_name varchar2(35);
   v_last_name varchar2(35);
   c_counter constant number :=0;
  begin
   select first_name,last_name into v_first_name,v_last_name from student where student_id=123;
   DBMS_OUTPUT.put_line('student_name:' || v_first_name || ' ' || v_last_name || '  ' || c_counter );
 end;
 --异常处理情况
 declare
   v_first_name varchar2(35);
   v_last_name varchar2(35);
   c_counter constant number :=0;
  begin
   select first_name,last_name into v_first_name,v_last_name from student where student_id=111;
   DBMS_OUTPUT.put_line('student_name:' || v_first_name || ' ' || v_last_name || '  ' || c_counter );
   EXCEPTION
  WHEN no_data_found THEN DBMS_OUTPUT.put_line('there is no student with ' || 'student_id is 111');
 end;

--替代变量
 DECLARE
   v_student_id NUMBER  := &sv_student_id;
   v_first_name varchar2(35);
   v_last_name varchar2(35);
   c_counter constant number :=0;
  begin
   select first_name,last_name into v_first_name,v_last_name from student where student_id=v_student_id;
   DBMS_OUTPUT.put_line('student_name:' || v_first_name || ' ' || v_last_name || '  ' || c_counter );
  EXCEPTION
        WHEN no_data_found THEN DBMS_OUTPUT.put_line('there is no student with ' || 'student_id is' || v_student_id);
 end;
--SET SERVEROUTPUT OFF 输出功能关闭
--TO_CHAR(SYSDATE,'DAY') 转化为当前日
--使用Anchored数据类型（当基础对象的数据类型变化时，无需更改自己的PL/SQL代码
DECLARE
    v_name student.FIRST_NAME%TYPE;
    v_grade grade.NUMERIC_GRADE%TYPE;
    BEGIN
       DBMS_OUTPUT.put_line(NVL(v_name,'No Name') || ' has grade of '  || NVL(v_grade,0));
    END;
--PL/SQL语句块中往数据库插入数据
DECLARE
    v_zip ZIPCODE.ZIP%TYPE;
    v_user zipcode.CREATE_BY%TYPE;
    v_date zipcode.CREATE_DATE%TYPE;
    BEGIN
       SELECT 43234,USER,SYSDATE INTO v_zip,v_user,v_date FROM dual;
          INSERT INTO zipcode(zip,CREATE_BY,CREATE_DATE,MODIFY_BY,MODIFY_DATE)
          VALUES(v_zip,v_user,v_date,v_user,v_date);
      END;
--从序列获取数字
CREATE TABLE test01(coll NUMBER);
CREATE SEQUENCE test_seq INCREMENT BY 5;
BEGIN
    INSERT INTO test01 VALUES(test_seq.NEXTVAL);
  END;
--PL/SQL语句块中使用COMMIT、ROLLBACK和SAVEPOINT
BEGIN
   INSERT INTO student(student_id,LAST_NAME,ZIP,REGISTRATON_DATE,create_by,CREATE_DATE,modify_by,MODIFY_DATE)
   VALUES(100,'tashi',10015,'01-JAN-99','Studenta','01-jan-99','student','01-jan-99');
   SAVEPOINT A;
  INSERT INTO student(student_id,LAST_NAME,ZIP,REGISTRATON_DATE,create_by,CREATE_DATE,modify_by,MODIFY_DATE)
   VALUES(100,'tashi',10015,'01-JAN-99','Studenta','01-jan-99','student','01-jan-99');
   SAVEPOINT B;
   INSERT INTO student(student_id,LAST_NAME,ZIP,REGISTRATON_DATE,create_by,CREATE_DATE,modify_by,MODIFY_DATE)
   VALUES(100,'tashi',10015,'01-JAN-99','Studenta','01-jan-99','student','01-jan-99');
   SAVEPOINT C;
   INSERT INTO student(student_id,LAST_NAME,ZIP,REGISTRATON_DATE,create_by,CREATE_DATE,modify_by,MODIFY_DATE)
   VALUES(100,'tashi',10015,'01-JAN-99','Studenta','01-jan-99','student','01-jan-99');
   ROLLBACK TO B;
END;
--使用if-then-else语句
DECLARE
    v_total NUMBER;
    BEGIN
       SELECT COUNT(*) INTO v_total FROM enrollment e JOIN SECTION s USING(section_id) WHERE s.COURSE_id=25 ;
      IF v_total >= 15 THEN DBMS_OUTPUT.put_line('section 1 of course 25 is full');
      ELSE DBMS_OUTPUT.put_line('section 1 of course 25 is not full');
      END IF;
      END;
--使用ELSEIF 语句
DECLARE
    v_num NUMBER := &sv_num;
    BEGIN
       IF v_num < 0 THEN DBMS_OUTPUT.put_line(v_num || ' is a negative number');
       ELSIF v_num = 0 THEN DBMS_OUTPUT.put_line(v_num || ' is equal to zero');
       ELSE DBMS_OUTPUT.put_line(v_num || ' is a positive number');
       END IF;
       END;
--使用case语句
DECLARE
    v_num NUMBER := &sv_num;
    v_num_flag NUMBER;
    BEGIN
       v_num_flag := MOD(v_num,2);
       CASE v_num_flag
          WHEN 0 THEN DBMS_OUTPUT.put_line(v_num || ' is even number');
         ELSE
           DBMS_OUTPUT.put_line(v_num || ' is odd number');
       END CASE;
         DBMS_OUTPUT.put_line('done');
       END;

--NULLIF(expression1, expression2)如果expression1=expression2 返回null,否则返回expression1
--COALESCE(expression1,expression2,...,expressionN)每个表达式和NULL比较，并返回第一个非NULL表达式的值
--EXIT WHEN语句的处理逻辑：IF CONDITION THEN EXIT; END IF; 等价于 EXIT WHEN CONDITION;
--使用带有EXIT条件的简单循环
DECLARE
    v_counter BINARY_INTEGER := 0;
    BEGIN
       LOOP
        v_counter := v_counter + 1;
        DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
        IF v_counter = 5 THEN
          EXIT;
        END IF;
      END LOOP;
      DBMS_OUTPUT.put_line('done...');
    END;
--使用带有EXIT条件的简单循环
DECLARE
    v_counter BINARY_INTEGER := 0;
    BEGIN
       LOOP
        v_counter := v_counter + 1;
        DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
        EXIT WHEN v_counter = 5 ;
      END LOOP;
      DBMS_OUTPUT.put_line('done...');
    END;
--使用带有EXIT条件的while简单循环
DECLARE
    v_counter BINARY_INTEGER := 5;
    BEGIN
       WHILE v_counter <= 5 LOOP
        v_counter := v_counter - 1;
        DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
        EXIT WHEN v_counter = 2 ;
      END LOOP;
      DBMS_OUTPUT.put_line('done...');
    END;
--使用for简单循环
DECLARE
   BEGIN
      FOR v_counter IN 1..5 LOOP
         DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
      END LOOP;
END;
--倒序for简单循环
DECLARE
   BEGIN
      FOR v_counter IN REVERSE 1..5 LOOP
         DBMS_OUTPUT.put_line('v_counter = ' || v_counter);
      END LOOP;
END;
--EXIT 和 CONTINUE 条件之间的区别:EXIT条件会终止该循环，而continue条件会终止循环的当前迭代
--使用内置异常
DECLARE
     v_num1 NUMBER := &sv_num1;
    v_num2 NUMBER := &sv_num2;
    v_result NUMBER;
BEGIN
     v_result := v_num1 / v_num2;
    DBMS_OUTPUT.put_line('v_result: ' ||  v_result);
    EXCEPTION
         WHEN zero_divide THEN
           DBMS_OUTPUT.put_line('a number cannot be divided by zero');
   END;
--内置异常2
DECLARE
     v_instructor_id NUMBER := &sv_instructor_id;
    v_instructor_name VARCHAR2(50);
BEGIN
     SELECT first_name || ' ' || last_name INTO v_instructor_name FROM instructor WHERE instructor_id = v_instructor_id;
    DBMS_OUTPUT.put_line('Instructor name is '||v_instructor_name);
EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.put_line('An error has occurred');
END;
--异常的作用范围(在语句块嵌套的情况下，外语句块所定义的任何异常都适用于内语句块）
DECLARE
    v_student_id NUMBER := &sv_student_id;
    v_name VARCHAR2(30);
    v_total NUMBER(1);
    --outer block
BEGIN
   SELECT TRIM(first_name) || ' ' || TRIM(last_name) INTO v_name FROM student WHERE student_id = v_student_id;
   DBMS_OUTPUT.put_line('student name is' || v_name);
   --inner block
   BEGIN
      SELECT COUNT(*) INTO v_total FROM enrollment WHERE student_id = v_student_id;
     DBMS_OUTPUT.put_line('student is registered for ' || v_total || ' course(s)');
   EXCEPTION
     WHEN value_error OR invalid_number THEN dbms_output.put_line('an error has occurred');
   END;
EXCEPTION
     WHEN no_data_found THEN
        DBMS_OUTPUT.put_line('there is no such student');
END;
--用户自定义异常
DECLARE
    v_student_id student.STUDENT_ID%TYPE := &sv_student_id;
    v_total_course NUMBER;
    e_invalid_id EXCEPTION;
BEGIN
      IF v_student_id < 0 THEN
      RAISE e_invalid_id;
    ELSE
      SELECT COUNT(*) INTO v_total_course FROM enrollment WHERE student_id = v_student_id;
    DBMS_OUTPUT.put_line(' The student is registered for ' || v_total_course || ' course');
    END IF;
    DBMS_OUTPUT.put_line('No exception has been raised');
EXCEPTION
     WHEN e_invalid_id THEN
        DBMS_OUTPUT.put_line('An id cannot be negative');
END;
--RAISE_APPLICATION_ERROR适用于用户定义异常 语法: RAISE_APPLICATION_ERROR(error_number,error_message);
--error_number编号范围-20999--20000之间 此异常适用于未命名的用户定义异常
DECLARE
    v_student_id student.student_id%TYPE := &sv_student_id;
    v_total_course NUMBER;
BEGIN
     IF v_student_id < 0 THEN RAISE_APPLICATION_ERROR(-20000, ' An id cannot be negative');
    ELSE
       SELECT COUNT(*) INTO v_total_course FROM enrollment  WHERE student_id = v_student_id;
      DBMS_OUTPUT.put_line('The student is registered for ' || v_total_course || ' course');
      END IF;
END;
--oracle提供了两个内置函数SQLCODE和SQLERRM用于实现OTHERS异常处理程序
DECLARE
    v_zip VARCHAR2(5) := '&sv_zip';
    v_city VARCHAR2(15);
    v_state CHAR(2);
    v_err_code NUMBER;
    v_err_msg VARCHAR2(200);
BEGIN
   SELECT city,state INTO v_city,v_state FROM zipcode WHERE zip = v_zip;
   DBMS_OUTPUT.put_line(v_city || ', ' || v_state);
   EXCEPTION
        WHEN OTHERS THEN
          v_err_code := SQLCODE;
          v_err_msg := substr(SQLERRM,1,200);
          DBMS_OUTPUT.put_line('error code: ' || v_err_code);
          DBMS_OUTPUT.put_line('error message: ' || v_err_msg);
END;

--隐式游标，例如 ROWCOUNT、SQL%ROWCOUNT会返回被更新数据行数
BEGIN
   UPDATE student SET first_name = 'B' WHERE first_name LIKE 'B%';
   DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;

--PL/SQL支持三种类型的记录：基于表的（table-based）、基于游标的（cursor-based）以及程序员自定义的（programmer-defined）
--基于表的和基于游标的可以使用%rowtype属性
DECLARE
    vr_student student%ROWTYPE;
    BEGIN
       SELECT * INTO vr_student FROM student WHERE student_id = 123;
      DBMS_OUTPUT.put_line(vr_student.first_name || ' ' || vr_student.last_name || ' has an ID of 156');
      EXCEPTION
           WHEN no_data_found THEN RAISE_APPLICATION_ERROR(-2001,'The Student ' || ' is not in the database');
END;

--打开游标
DECLARE
    CURSOR c_zip IS SELECT * FROM zipcode;
    vr_zip c_zip%ROWTYPE;
BEGIN
     OPEN c_zip;
    LOOP
       FETCH c_zip INTO vr_zip;
      EXIT WHEN c_zip%NOTFOUND;
      DBMS_OUTPUT.put_line(vr_zip.zip || ' ' || vr_zip.city|| ' ' ||vr_zip.state);
   END LOOP;
   close c_zip;
END;
--使用for循环的游标
 DECLARE
     CURSOR c_student IS
        SELECT student_id,last_name,first_name FROM student WHERE student_id < 110;
    BEGIN
       FOR r_student IN c_student
       LOOP
          INSERT INTO table_log VALUES(r_student.last_name);
       END LOOP;
END;
--before触发器
CREATE OR REPLACE TRIGGER student_bi
    BEFORE INSERT ON student
    FOR EACH ROW
    DECLARE
        v_student_id student.student_id%TYPE;
    BEGIN
       SELECT student_id_seq.NEXTVAL INTO v_student_id FROM dual;
       :NEW.student_id := v_student_id;
       :NEW.created_by := USER;
       :NEW.created_date := SYSDATE;
       :NEW.modified_by := USER;
       :NEW.modified_date := SYSDATE;
END;
--after触发器
CREATE OR REPLACE TRIGGER instructor_aud
AFTER UPDATE OR DELETE ON instructor
DECLARE
    v_type VARCHAR2(10);
    BEGIN
       IF updating THEN v_type := 'update';
      ELSIF deleting THEN v_type := 'delete';
      END IF;
   UPDATE STATISTICS
       SET TRANSACTION_user = USER,TRANSACTION_date = SYSDATE
       WHERE table_name = 'instructor' AND transation_name = v_type;
   IF SQL%NOTFOUND THEN INSERT INTO STATISTICS VALUES('instructor',v_type,USER,SYSDATE);
   END IF;
END;
--after触发器
CREATE OR REPLACE TRIGGER instructor_aud
AFTER UPDATE OR DELETE ON instructor
DECLARE
    v_type VARCHAR2(10);
    BEGIN
       IF updating THEN v_type := 'update';
      ELSIF deleting THEN v_type := 'delete';
      END IF;
   UPDATE STATISTICS
       SET TRANSACTION_user = USER,TRANSACTION_date = SYSDATE
       WHERE table_name = 'instructor' AND transation_name = v_type;
   IF SQL%NOTFOUND THEN INSERT INTO STATISTICS VALUES('instructor',v_type,USER,SYSDATE);
   END IF;
END;