PL/SQL 知识总结

PL/SQL总结

PL/SQL 数组

数组的定义 TYPE varray_type_name IS VARRAY(n)
OF ELEMENT_TYPE

举例：

TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
TYPE grades IS VARRAY(5) OF INTEGER;

使用可变数组：

declare --声明
 type namearray is varray(5) of varchar2(10);
 type grades is varray(5) of integer;
  names namearray; --拿到数组的引用
  marks grades;
  total integer;
 begin 
  --给数组赋值
  names:=namearray('tom','body','ayan','faker','kids');
  marks:=grades(98,96,80,60,85);
  total:=names.count; --获取数组的长度
  dbms_output.put_line('共有:'|| total || '名学生');
  for i in 1..total loop --循环输出数组
   dbms_output.put_line('Student: ' || names(i) || ' Marks: ' || marks(i));
   end loop;
 end;

注意：在Oracle的环境下，数组的起始索引从1开始。
PL/SQL子程序被命名之后，可使用一组参数来调用PL/SQL块。 PL/SQL提供两种子程序：

1. 函数：这些子程序返回一个值，主要用于计算并返回一个值。
2. 过程：这些子程序没有直接返回值，主要用于执行操作。

存储过程的定义：

CREAT [OR REPLACE] PROCEDURE procedure_name
[(parameter_name[in|out|in out]type[,...])]
(is,as)
begin 
<procedure_body>
end[procedure_name];

1. procedure_name 指定的程序的名称
2. [or replace] 选项允许修改现有的程序
3. 可选的参数列表中包含的名称，模式和类型的参数。IN表示该值将被从外部传递，OUT表示该参数将被用于从过程返回一个值到外面
4. procedure_body 包含可执行的部分
5. AS 关键字来代替了IS关键字用于创建一个独立的程序

举例：

create or replace procedure greetings
as
begin
dbms_output.put_line('Hello World');
end;

创建好的存储过程只需要运行一下就会自动保存。可以通过选中存储过程右键，进行测试。
如果没有or replace语句，那只是新建一个存储过程，如果系统中存在相同的存储过程，则会报错，Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。
存储过程名定义：包括存储过程名和参数列表、参数名和参数类型。参数列表可不写，如例子所示。参数名不能重复，并且每个参数之间用分号“ ；” 隔开， 参数传递方式:IN, OUT, IN OUT。如下面例子所示：

下面说明一下参数传递方式：
in：表示输入参数，调用存储过程时从外面传进来的，它的值不能修改。
out：表示输出参数，当一个参数被指定为OUT类型时，如果还未调用存储过程之前对该参数进行了赋值，那么在存储过程中该参数的值仍然是null，但是如果在调用过程中对该参数进行赋值，那么值不为null。
in out：表示输入输出参数，它的值可以修改。
参数的数据类型只需要指明类型名即可，不需要指定宽度。参数的宽度由外部调用者决定。过程可以有参数，也可以没有参数。
我们看到例子中存在一个“”as“”，它表示变量声明块，可以理解为plsql中的declare关键字，用于声明变量。除了as外，还有is。变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。
其中，as和is的区别：在视图(VIEW)中只能用AS不能用IS；
在游标(CURSOR)中只能用IS不能用AS。
过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选
结束块：由end关键字结果。
删除存储过程 ： drop procedure procedure_name;
PL/SQL函数：
创建函数语法：

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name[in||out||in out]type[,..])]
Return return_datetype {is||as}
begin
 <function_body>
end [function_name];

1. function-name 指定函数的名称
2. [OR REPLACE] 选项允许修改现有的函数
3. 可选的参数列表中包含的名称，模式和类型的参数。IN表示该值将被从外部传递和OUT表示该参数将被用于过程外面返回一个值
4. 函数必须包含一个return语句
5. return子句指定要带函数返回的数据类型
6. function-body 包含可执行部分
7. AS关键字来代替了IS关键字用于创建一个独立的函数
   举例：

CREATE OR REPLACE FUNCTION totalCustomers
   RETURN number IS
    total number(2) := 0;
    BEGIN
    SELECT count(*) into total
    FROM customers;
    RETURN total;
END;

create or replace function findMax(x in number,y in number)
return number as
       z number;
begin
  if x > y then
    z := x;
  else
    z := y;
  end if;
  return z;
end;       

PL/SQL 游标：
Oracle会创建一个存储区域，被称为上下文区域，用于处理SQL语句，其中包含需要处理的语句，例如所有的信息，行数处理，等等。
游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。

举例：

declare
  total_rows number(2);
begin
  update customers set salary = salary + 500;
  if sql%notfound then
    dbms_output.put_line('no customers selected ! ');
  elsif sql%found then
    total_rows := sql%rowcount;
    dbms_output.put_line(total_rows || ' customers selected !');
  end if;
end;

显示游标
显式游标是程序员定义游标获得更多的控制权的上下文的区域。显式游标应在PL/SQL块的声明部分中定义。这是创建一个SELECT语句返回多行。
创建显示游标的语法是： CURSOR cursor_name IS select_statement;
显示游标使用的四个步骤：

声明游标 cursor c_customer is select * from customers;
打开游标 open c_customer;
获取游标 fetch c_customers into c_addr,c_name,c_salary;
关闭游标 close c_customers;

declare  c_id customers.id%type;  
    c_name customers.name%type;  
    c_addr customers.address%type;   
    cursor c_customers is select id,name,address from customers;
begin  
    open c_customers;  
    loop    
        fetch c_customers into c_id,c_name,c_addr;    
        exit when c_customers%notfound;    
        dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);  
    end loop;  
    close c_customers;
end;