PL/SQL by example 看完小结下


--PL/SQL 代码块基本结构DECLARE
   Declaration statements
BEGIN
   Executable statements
EXCEPTION
   Exception-handling statements
END;--变量声明
Syntax : <variable-name> <data type> [optional default assignment]--嵌套代码块
BEGIN    -- outer block
         BEGIN -- inner block
                    ...;
         END;  -- end of inner block
END;     -- end of outer block--SELECT INTO syntax
SELECT item_name
   INTO variable_name
   FROM table_name;
--编程时需注意返回记录是单条还是多条，各自采用不同的语法处理，否则可能报错--sequence语法
CREATE SEQUENCE seq_name INCREMENT BY 10;--事务控制
COMMIT [WORK];
ROLLBACK [WORK];
SAVEPOINT name;
ROLLBACK [WORK] to SAVEPOINT name;--IF-THEN语句
IF CONDITION THEN
   STATEMENT 1;
   ...
   STATEMENT N;
END IF;--IF-THEN-ELSE语句
IF CONDITION THEN
   STATEMENT 1;
ELSE
   STATEMENT 2;
END IF;
STATEMENT 3;--ELSIF语句
IF CONDITION 1 THEN
   STATEMENT 1;
ELSIF CONDITION 2 THEN
   STATEMENT 2;
ELSIF CONDITION 3 THEN
   STATEMENT 3;
...
ELSE
   STATEMENT N;
END IF;--CASE Statements
CASE SELECTOR
   WHEN EXPRESSION 1 THEN STATEMENT 1;
   WHEN EXPRESSION 2 THEN STATEMENT 2;
   ...
   WHEN EXPRESSION N THEN STATEMENT N;
   ELSE STATEMENT N+1;
END CASE;--Searched CASE Statements
CASE
   WHEN SEARCH CONDITION 1 THEN STATEMENT 1;
   WHEN SEARCH CONDITION 2 THEN STATEMENT 2;
   ...
   WHEN SEARCH CONDITION N THEN STATEMENT N;
   ELSE STATEMENT N+1;
END CASE;
----注意区分case语句和case表达式的区别，--几个函数
NULLIF (expression1, expression2)
COALESCE (expression1, expression2, …, expressionN)--异常处理
DECLARE
   exception_name EXCEPTION;
BEGIN
   ...
   IF CONDITION THEN
      RAISE exception_name;
   ELSE
      ...
   END IF;
EXCEPTION
   WHEN exception_name THEN
      ERROR-PROCESSING STATEMENTS;
END;RAISE_APPLICATION_ERROR(error_number, error_message, keep_errors);
--第三个参数为推入error栈还是取代的控制--自定义oracle内部异常
DECLARE
  exception_name EXCEPTION;
  PRAGMA EXCEPTION_INIT(exception_name, error_code);--请自己分析
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Error code: '||SQLCODE);
   DBMS_OUTPUT.PUT_LINE ('Error message1: '||SQLERRM(SQLCODE));
   DBMS_OUTPUT.PUT_LINE ('Error message2: '||SQLERRM(100));
   DBMS_OUTPUT.PUT_LINE ('Error message3: '||SQLERRM(200));
   DBMS_OUTPUT.PUT_LINE ('Error message4: '||SQLERRM(-20000));
END;--循环语句
LOOP
   STATEMENT 1;
   STATEMENT 2;
   ...
   STATEMENT N;
END LOOP;LOOP
   STATEMENT 1;
   STATEMENT 2;
   IF CONDITION THEN
      EXIT;
   END IF;
END LOOP;
STATEMENT 3;LOOP
   STATEMENT 1;
   STATEMENT 2;
   EXIT WHEN CONDITION;
END LOOP;
STATEMENT 3;WHILE CONDITION LOOP
   STATEMENT 1;
   STATEMENT 2;
   ...
   STATEMENT N;
END LOOP;FOR loop_counter IN[REVERSE] lower_limit..upper_limit LOOP
   STATEMENT 1;
   STATEMENT 2;
   ...
   STATEMENT N;
END LOOP;<<label_name>>
FOR LOOP_COUNTER IN LOWER_LIMIT..UPPER_LIMIT LOOP
   STATEMENT 1;
   ...
   STATEMENT N;
END LOOP label_name;--Procedures， IN OUT (IN OUT) (Syntax: formal_parameter_name => argument_value)良好的编程习惯就不用管这些细节了
CREATE OR REPLACE PROCEDURE name
   [(parameter[, parameter, ...])]
AS
   [local declarations]
BEGIN
   executable statements
[EXCEPTION
   exception handlers]
END [name];--Function Syntax
CREATE [OR REPLACE] FUNCTION function_name
  (parameter list)
   RETURN datatype
IS
BEGIN
   <body>
   RETURN (return_value);
END;--package略 
--游标声明
CURSOR c_cursor_name IS select statement--记录类型声明
<record_name>   <table_name or cursor_name>%ROWTYPE--游标生命周期(FOR LOOP简化不提）
CURSOR c_cursor_name IS select statement；
OPEN cursor_name;
FETCH cursor_name INTO PL/SQL record(or variables);
CLOSE cursor_name;--游标带参数
CURSOR c_zip (p_state IN zipcode.state%TYPE) IS
   SELECT zip, city, state
     FROM zipcode
    WHERE state = p_state;OPEN c_zip (parameter_value)
--or
FOR r_zip IN c_zip('NY')
LOOP  ...
--指定参数是用在记录选择时的，有啥好处呢？ 
--FOR UPDATE Cursor
FOR UPDATE OF <item_name>
--example
DECLARE
   CURSOR c_stud_zip IS
      SELECT s.student_id, z.city
        FROM student s, zipcode z
       WHERE z.city = 'Brooklyn'
         AND s.zip = z.zip
       FOR UPDATE OF phone;
BEGIN
  FOR r_stud_zip IN c_stud_zip
  LOOP
     UPDATE student
        SET phone = '718'||SUBSTR(phone,4)
      WHERE student_id = r_stud_zip.student_id;
  END LOOP;
END;
--用于锁定表或者列用于更新，直到commit或者rollback
--The FOR UPDATE and WHERE CURRENT OF syntax can be used with cursors that are performing a delete as well as an update.--Cursor Variables
TYPE ref_type_name is REF CURSOR [RETURN return_type];
--通常指向某一种记录类型--触发器
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER} triggering_event ON table_name
[FOR EACH ROW]
[WHEN condition]
DECLARE
   Declaration statements
BEGIN
   Executable statements
EXCEPTION
   Exception-handling statements
END;
--触发器的分类：[FOR EACH ROW]决定，选用原则是更新表时启动一次还是每行更新都要启动？
--当试图由多表合成时，更新视图不改变源表，可以用Instead of Triggers
--由各表有外键引用时应注意各表的逻辑。比较复杂，请查看原文--Index-By Tables  隐式分配空间，直接用下标调用
TYPE type_name IS TABLE OF element_type [NOT NULL]
   INDEX BY BINARY_INTEGER;
table_name TYPE_NAME;
--Nested Tables  EXTEND扩展空间，直接用下标调用
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name TYPE_NAME;
--集合的方法： EXISTS，COUNT，EXTEND，DELETE，FIRST and LAST，PRIOR and NEXT，TRIM
--Varray， 可以定义多重数组
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
varray_name TYPE_NAME;--Records，来自表，来自游标，也可以自己定义
TYPE type_name IS RECORD
   (field_name1 datatype1 [NOT NULL] [ := DEFAULT EXPRESSION],
    field_name2 datatype2 [NOT NULL] [ := DEFAULT EXPRESSION],
    ...
    field_nameN datatypeN [NOT NULL] [ := DEFAULT EXPRESSION]);
record_name TYPE_NAME;
--用户定义的记录不匹配前两者，反之则可以
--记录可以嵌套，可以组成数组--EXECUTE IMMEDIATE语法
EXECUTE IMMEDIATE dynamic_SQL_string
[INTO defined_variable1, defined_variable2, ...]
[USING [IN | OUT | IN OUT] bind_argument1, bind_argument2, ...]
[RETURNING INTO | RETURN bind_argument1, bind_argument2, ...]--dynamic_SQL_string： 给参数分配空间，运行时决定具体值
--编程时，权衡动态输入的值由语句块的参数传入还是using传入？--UTL_FILE 访问本地文件，详情Google，注意
CREATE OR REPLACE DIRECTORY tmp AS '/tmp'; 
grant read or write on directory to sb;--DBMS_JOB package更像管理员的工具
--Creating Web Pages with the Oracle Web Toolkit 这个有些琐碎，现在web技术那么多，数据库的这个功能不强求了，没看