Oracle 基础笔记4

Oracle 基础笔记 4(PL/SQL 2)

PL/SQL表

pl/sql表只有两列，其中第一列为序号列为integer类型，第二列为用户自定义列。

定义：TYPE <类型名> IS TABLE OF <列的类型> [NOT NULL] INDEX BY BINARY_INTEGER;

<列的类型>可以为Oracle的数据行以及自定义类型;

属性方法：

.count --返回pl/sql表的总行数

.delete --删除pl/sql表的所有内容

.delct(行数) --删除pl/sql表的指定的行

.first --返回表的第一个index;

.next(行数) --这个行数的下一条的index;

.last --返回表的最后一个index;

使用

eg:

DECLARE
     TYPE mytable IS TABLE OF VARCHAR2(20) index by binary_integer; --定义一个名为mytable的PL/sql表类型;
     cursor c_1 is select ename from emp; 
     n number:=1;
     tab_1 mytable; --为mytable类型实例化一个tab_1对象;
BEGIN
     for i in c_1
     loop
          tab_1(n):=i.ename; --将得到的值输入pl/sql表
          n:=n+1; 
    end loop;
     n:=1;
     tab_1.delete(&要删除的行数); --删除pl/sql表的指定行
     for i in tab_1.first..tab_1.count
     loop
          dbms_output.put_line(n||' '||tab_1(n)); --打印pl/sql表的内容
          n:=tab_1.next(n);
     end loop;
EXCEPTION 
     WHEN NO_DATA_FOUND THEN                    --由于删除了一行，会发生异常，下面语句可以接着删除的行后显示
          for i in n..tab_1.count+1
     loop
          dbms_output.put_line(n||' '||tab_1(n));
          n:=tab_1.next(n);
     end loop; 
END; 

PL/SQL记录

pl/sql表只有一行，但是有多列

定义：type <类型名> is record <列名1 类型1,列名2 类型2,…,列名n 类型n> [not null]

<列的类型>可以为Oracle的数据类行以及用户自定义类型;可以是记录类型的嵌套

使用

eg:

DECLARE
     TYPE myrecord IS RECORD(id emp.empno%type,
     name emp.ename%type,sal emp.sal%type);     --定义一个名为myrecoed的PL/sql记录类型;
     rec_1 myrecord; --为myrecord类型实例化一个rec_1对象;
BEGIN
    select empno,ename,sal into rec_1.id,rec_1.name,rec_1.sal
    from emp where empno=7788;        --将得到的值输入pl/sql记录
     dbms_output.put_line(rec_1.id||' '||rec_1.name||' '||rec_1.sal); --打印pl/sql记录的内容
END; 

结合使用PL/SQL表和PL/SQL记录

eg:

DECLARE
     CURSOR c_1 is select empno,ename,job,sal from emp;
     TYPE myrecord IS RECORD(empno emp.empno%type,ename emp.ename%type,
job emp.job%type,sal emp.sal%type);     --定义一个名为myrecoed的PL/sql记录类型;
     TYPE mytable IS TABLE OF myrecord index by binary_integer;    --定义一个名为mytable的PL/sql表类型;字段类型为PL/sql记录类型；

     n number:=1;
     tab_1 mytable; --为mytable类型实例化一个tab_1对象;
BEGIN
          --赋值
          for i in c_1
          loop
               tab_1(n).empno:=i.empno;
               tab_1(n).ename:=i.ename;                     
               tab_1(n).job:=i.job;
               tab_1(n).sal:=i.sal;
               n:=n+1;
          end loop;
          n:=1;
          --输出
          for i in n..tab_1.count
          loop
                dbms_output.put_line(i||' '||tab_1(i).empno
                ||' '||tab_1(i).ename||' '||tab_1(i).job||' '||tab_1(i).sal);
          end loop;
END; 

强型REF游标

定义：type <游标名> is ref cursor return <返回类型>;

操作:

open <游标名> for <select 语句> --打开游标

fetch <游标名> into 变量1,变量2,变量3,…,变量n;

或者fetch <游标名> into 行对象; --取出游标当前位置的值

属性：

%notfound

%found

%rowcount

%isopen

使用:

eg:

DECLARE 
     type c_type is ref cursor return emp%rowtype;     --定义游标
     c_1 c_type;      --实例化这个游标类型
     r emp%rowtype;
BEGIN
     dbms_output.put_line('行号 姓名 薪水');
     open c_1 for select * from emp;
     loop 
     fetch c_1 into r;
     exit when c_1%notfound;
     dbms_output.put_line(c_1%rowcount||' '||r.ename||' '||r.sal);    --输出结果,需要 set serverout on 才能显示.
     END LOOP;
close c_1;
END;

 

弱型REF游标

定义：type <游标名> is ref cursor;

操作:

open <游标名> for <select 语句> --打开游标

fetch <游标名> into 变量1,变量2,变量3,…,变量n;

或者fetch <游标名> into 行对象; --取出游标当前位置的值

属性：

%notfound

%found

%rowcount

%isopen

使用：

eg:

set autoprint on;
var c_1 refcursor;
DECLARE
   n number;
BEGIN
   n:=&请输入;
   if n=1 then
         open :c_1 for select * from emp;
   else 
         open :c_1 for select * from dept;
   end if;
END; 

 

过程

定义：create [or replace] procedure <过程名> [(参数列表)] is

[局部变量声明]

begin

     可执行语句;

exception

      异常处理语句;

end [<过程名>];

变量的为类型:

in 为默认类型，表示输入;

out 表示只输出;

in out 表示即输入又输出;

操作已有的的过程：

在PL/SQL块中直接使用过程名;

在程序外使用 execute <过程名>[(参数列表)]

使用：

eg:

创建过程

create or replace procedure p_1(n in out number) is
    r emp%rowtype;
BEGIN
     dbms_output.put_line('姓名 薪水');
     select * into r from emp where empno=n;
     dbms_output.put_line(r.ename||' '||r.sal);    --输出结果,需要 set serverout on 才能显示.
    n:=r.sal; 
END;
使用过程:
declare
    n number;
begin
    n:=&请输入员工号;
    p_1(n);
    dbms_output.put_line('n的值为 '||n);
end; 

删除过程：

drop procedure <过程名>;

函数

定义：

create [or replace] function <函数名> [(参数列表)] return 数据类型 is

[局部变量声明]

begin

     可执行语句;

exception

      异常处理语句;

end [<过程名>];

变量类型：

in 为默认类型，表示输入;

out表示只输出

in out 表示即可输入又输出;

使用：

eg:

创建函数：

create or replace function f_1(n number)
return number is
       r emp%rowtype;
 begin
       dbms_output.put_line('姓名 薪水');
       select * into r from where empno = n;
       dbms_output.put_line(r.ename || '' ||r.sal); --输出结果,需要set serverout on才能显示.
       retrun r.sal;
 end;

使用函数

declare
    n number;
     m number;
begin
    n:=&请输入员工号;
    m:=f_1(n);
    dbms_output.put_line('m的值为 '||m);
end; 

删除函数:

drop function <函数名>;