【SQL】Oracle的PL/SQL语法及其拓展数据类型总结

PL/SQL语法

PL/SQL程序由三部分组成，声明部分、执行部分、异常处理部分。

模板：

[sql] 
DECLARE  
    /*变量声明*/  
BEGIN  
    /*程序主体*/  
EXCEPTION  
    /*异常处理部分*/  
END;

接收用户的输入信息

PL/SQL 块还可以接收用户的输入信息，例如：现在要求用户输入一个雇员编号，之后根据输入的内容进行查询，查询雇员的姓名。

用户的输入信息使用“&”完成。

范例：

[sql] 
DECLARE  
    t_eno NUMBER ;  
    t_ename VARCHAR2(30) ;   
BEGIN  
    --  输入的信息保存在 eno 里  
    t_eno := &no;  
    --  之后根据 eno 的值，对数据库进行查询操作  
    SELECT ename INTO t_ename FROM emp WHERE empno=t_eno ;   
    DBMS_OUTPUT.put_line('编号为：'||t_eno||'雇员的姓名为：'||t_ename) ;  
EXCEPTION  
    WHEN no_data_found THEN DBMS_OUTPUT.put_line('没有此雇员') ;   
END ;

自定义数据类型之%type

定义一个变量，可以将其与表中某个列的数据类型一致。

如上例修改为以下也可运行。

[sql] 
DECLARE  
t_eno emp.empno%type ;  
t_ename emp.ename%type;   
BEGIN  
  --  输入的信息保存在 eno 里  
  t_eno := &no;  
  --  之后根据 eno 的值，对数据库进行查询操作  
  SELECT ename INTO t_ename FROM emp WHERE empno=t_eno ;   
  DBMS_OUTPUT.put_line('编号为：'||t_eno||'雇员的姓名为：'||t_ename) ;  
EXCEPTION  
     WHEN no_data_found THEN DBMS_OUTPUT.put_line('没有此雇员') ;   
END ;  
[sql] 
DECLARE  
t_eno emp.empno%type ;  
t_ename emp.ename%type;   
BEGIN  
  --  输入的信息保存在 eno 里  
  t_eno := &no;  
  --  之后根据 eno 的值，对数据库进行查询操作  
  SELECT ename INTO t_ename FROM emp WHERE empno=t_eno ;   
  DBMS_OUTPUT.put_line('编号为：'||t_eno||'雇员的姓名为：'||t_ename) ;  
EXCEPTION  
     WHEN no_data_found THEN DBMS_OUTPUT.put_line('没有此雇员') ;   
END ;

复合数据类型之record

将一个或多个标量封装成一个对象，由单行多列的标量构成的复合结构，组成类似于一维数组。

如下例查询表中两列将其放入记录类型并输出显示。

[sql] 
DECLARE  
    type rec_test1 is record(  
    t_empno emp.empno%type,  
    t_ename emp.ename%type  
    ); --定义一个record类型  
    rt rec_test1;--使用你刚才定义的record类型，定义了一个变量rt.  
BEGIN  
    select empno,ename into rt.t_empno,rt.t_ename from emp where empno=1001;  
    dbms_output.put_line('empno:'||rt.t_empno);  
END ;  

[sql] 
DECLARE  
  type rec_test1 is record(  
  a1 number,  
  a2 varchar2(10)  
  );  
  rt1 rec_test1;  
  rt2 rec_test1;  
BEGIN  
  rt1.a1:=1;  
  rt1.a2:='china';  
  rt2.a1:=2;  
  rt2.a2:='usa';  
  dbms_output.put_line('rt1.a2:'||rt1.a2);  
  dbms_output.put_line('rt2.a2:'||rt2.a2);  
END ;

复合数据类型之%rowtype

%ROWTYPE可以说是record的升级简化版。

区别在与前者结构为表结构，后者为自定义结构。

二者在使用上没有很大区别。前者方便，后者灵活。

在实际中根据情况来具体决定使用。

使用记录更新数据，如果用记录(RECORD)更新数据，那么只能使用记录成员；如果用%ROWTYPE更新数据,可以直接使用%ROWTYPE 。如:

[sql] 
DECLARE  
  vEmp emp%RowType;  
Begin  
  select * InTo vEmp from empa where empa.EMPNO = '7934';  
  update  empa set ROW = vEmp where EMPNO ='1111';   
  commit;  
End;

【注】在进行如上操作前，需要复制表emp到empa。

复制表的结构

[sql] 
CREATE TABLE empa as SELECT * FROM emp WHERE 1=2;  
复制表的数据
[sql] 
INSERT INTO empa SELECT * FROM emp;  

Oracle扩展数据类型小结
%type:可让变量的类型与指定表的某列保持一致;
t_empno 表名.列名%type;

%record:单行多列，类似于一位数组;
1.先创建类型 2.再创建该类型的变量
type XXX is record(
列1 数据类型(长度),列2 数据类型(长度)…
);
x XXX; --x是变量, XXX是它的类型
如何引用某列？ x.列名 即可

%rowtype: 是%record的升级简化版，可让其与某张表的结构完全一致.
x 表名%rowtype; --x是变量名