Oracle PLSQL

PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.

本系列笔记可能会遇到的专业词汇有：

procedure, 存储过程
function, 函数
trigger, 触发器
package/package body, 包和包体

Oracle动态SQL

Oracle动态SQL有两种写法：用DBMS_SQL或execute immediate，建议使用后者。

示例

begin  
    execute immediate 'drop table temp_1';  
    execute immediate 'create table temp_1(name varchar2(8))';  
end;  
  　
-- DML  
declare  
    v_1 varchar2(8);  
    v_2 varchar2(10);  
    str varchar2(50);  
begin  
    v_1 := '测试人员';  
    v_2 := '北京';  
    str := 'insert into test(name ,address) values(:1, :2)';  
    execute immediate str using v_1, v_2;  
    commit;  
end;

declare  
    str varchar2(500);  
    c_1 varchar2(10);  
    r_1 test%rowtype;  
begin  
    c_1 := '测试人员';  
    str := 'select * from test where name= :c where rownum = 1';  
    execute immediate str into r_1 using c_1;  
    dbms_output.put_line(r_1.name || r_1.address);  
end;

游标变量

游标变量是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，
游标变量是动态的，而游标是静态的。它可以与不同的查询语句相连接用来只想不同
查询语句的内存处理区只要这些查询语句的返回类型兼容即可。
游标变量类型的声明可以在块、子程序和包的声明区域内定义， 语法：

type 游标变量名 is ref cursor[return 返回值类型];
定义游标变量类型时，可以采用强类型定义和弱类型定义两种。
强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。

示例：创建两个强类型定义游标变量和一个弱类型游标变量：

declare
    type r_emp is record(
        emp_id tbl_emp.id%type,
        emp_fname tbl_emp.name%type,
        emp_lname tbl_emp.lastname%type
    );
    type emp_type1 is ref cursor return tbl_emp%rowtype;
    type emp_type2 is ref cursor return r_emp;
    type cur_type is ref cursor;
    v_emp1 emp_type1;
    v_emp2 emp_type2;
    v_cur curtype;

游标一样，游标变量操作也包括打开、提取和关闭三个步骤， 打开游标变量语法：

open {游标变量 | :PL/SQL主机环境声明的游标变量} for 查询语句;
提取游标变量数据语法：

fetch {游标变量 | :PL/SQL主机环境声明的游标变量} into {普通变量 [, 普通变量]…| 记录变量};
关闭游标变量语法：

close {游标变量 | :PL/SQL主机环境声明的游标变量}
如果关闭一个未打开的游标变量，则将导致invalid_cursor异常错误

示例

declare
  type r_emp is record(
    emp_id tbl_emp.id%type,
    emp_fname tbl_emp.name%type,
    emp_lname tbl_emp.lastname%type
  );
  type emp_cur_type is ref cursor return r_emp;
  type cur_type is ref cursor;
  v_cur_emp emp_cur_type;
  v_emp r_emp;
begin
  open v_cur_emp for select * from tbl_emp;
  fetch v_cur_emp into v_emp;
  while v_cur_emp%found
    loop
      dbms_output.put_line('id: ' || v_emp.emp_id || ',name: ' || v_emp.emp_fname || ',lastname: ' || v_emp.emp_lname);
      fetch v_cur_emp into v_emp;
    end loop;
end;

DECLARE
type ref_cur_t IS REF CURSOR;
ref_cur ref_cur_t;
type sample_rec_type IS RECORD (
    emp_id tbl_emp.id%type,
    emp_fname tbl_emp.name%type,
    emp_lname tbl_emp.lastname%type
);
sample sample_rec_type;
selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
BEGIN
    IF selection='D' THEN
        OPEN ref_cur FOR 
    SELECT * from tbl_emp where id = 1;
    ELSIF selection='E' THEN
        OPEN ref_cur FOR 
    SELECT * from tbl_emp where id = 2;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E''');
        RETURN;
    END IF;
    DBMS_OUTPUT.PUT_LINE('----------------------');
    FETCH ref_cur INTO sample;
    WHILE ref_cur%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(sample.emp_id||': '||sample.emp_fname);
        FETCH ref_cur INTO sample;
    END LOOP;
    CLOSE ref_cur;
END;