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;
 
 

 
 
1
2

使用可变数组：

DECLARE
    type namesarray IS VARRAY(5) OF VARCHAR2(10);
    type grades IS VARRAY(5) OF INTEGER;
    names namesarray; --拿到数组的引用，下面就可以变长使用该数组
    marks grades;
    total integer;
BEGIN
    names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
    marks:= grades(98, 97, 78, 87, 92);
    total := names.count; --求数组的长度
    dbms_output.put_line('Total '|| total || ' Students');
    FOR i in 1 .. total LOOP
        dbms_output.put_line('Student: ' || names(i) || ' Marks: ' || marks(i));
    END LOOP;
END;
 
 

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

求数组的长度 array_name.count

注意： 在Oracle的环境下，数组的起始索引从1开始。

PL/SQL子程序被命名之后，可使用一组参数来调用PL/SQL块。 PL/SQL提供两种子程序：
1. 函数：这些子程序返回一个值，主要用于计算并返回一个值。
2. 过程：这些子程序没有直接返回值，主要用于执行操作。

存储过程定义：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
    （IS，AS）
BEGIN
    < procedure_body >
END[procedure_name];
 
 

 
 
1
2
3
4
5
6

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;
 
 

 
 
1
2
3
4
5

创建好的存储过程只需要运行一下就会自动保存。可以通过选中存储过程右键，进行测试。

删除存储过程 ： drop procedure procedure_name;

举例说明：

Create PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
    IF x < y THEN
        z:= x;
    ELSE
        z:= y;
    END IF;
END;
 
 

 
 
1
2
3
4
5
6
7
8

create PROCEDURE squareNum(x IN OUT number) IS
BEGIN
    x := x * x;
END;
 
 

 
 
1
2
3
4

PL/SQL 函数：
创建函数语法：

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])] RETURN return_datatype
    {IS | AS}
BEGIN
    < function_body >
END [function_name];
 
 

 
 
1
2
3
4
5
6

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;
 
 

 
 
1
2
3
4
5
6
7
8
9

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;
 
 

 
 
1
2
3
4
5
6
7
8
9
10
11
12

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;
 
 

 
 
1
2
3
4
5
6
7
8
9
10
11

显示游标：
显式游标是程序员定义游标获得更多的控制权的上下文的区域。显式游标应在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;
 
 

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13

PL/SQL 异常处理:
语法格式如下：

DECLARE
   <declarations section>BEGIN
   <executable command(s)>
EXCEPTION
   <exception handling goes here >
   WHEN exception1 THEN
       exception1-handling-statements
   WHEN exception2  THEN
      exception2-handling-statements
   WHEN exception3 THEN
      exception3-handling-statements
   ........
   WHEN others THEN
      exception3-handling-statements
END;
 
 

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

declare
  c_id customers.id%type := 8;
  c_name customers.name%type;
  c_addr customers.address%type;

begin
  select name,address into c_name,c_addr from customers where id = c_id;
  dbms_output.put_line(c_name || ' ' || c_addr);

exception
  when no_data_found then --这里的no_data_found是系统预定义的异常类型。
    dbms_output.put_line('No such customers !');
  when others then
    dbms_output.put_line('Error ! ');

end;
 
 

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

当系统预定义的异常不能满足条件时，可以使用自定义异常类型。
异常声明 ： exception_name exception;
抛出异常 ： raise exception_name;
捕获异常 ： when exception_name then…

DECLARE
   c_id customers.id%type := &c_id; -- 接收用户输入的值，运行的时候会弹出一个输入框。
   c_name  customers.name%type;
   c_addr customers.address%type;

   -- user defined exception
   ex_invalid_id  EXCEPTION;
BEGIN
   IF c_id <= 0 THEN
      RAISE ex_invalid_id;
   ELSE
      SELECT  name, address INTO  c_name, c_addr
      FROM customers
      WHERE id = c_id;

      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);
      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
   END IF;
EXCEPTION
   WHEN ex_invalid_id THEN
      dbms_output.put_line('ID must be greater than zero!');
   WHEN no_data_found THEN
      dbms_output.put_line('No such customer!');
   WHEN others THEN
      dbms_output.put_line('Error!');
END;
 
 

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

PL/SQL 中的日期和时间转化

select sysdate from dual;
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
 
 

 
 
1
2

DBMS_OUTPUT是一个内置的软件包，能够显示输出显示调试信息 。其中包含许多子程序，put_line( ) 就是其中的一个。

还有一些知识点比方说 PL/SQL 记录，触发器，包，面向对象等，在此我就不看了。PL/SQL over ！

PS.以上资料来自于易百教程https://www.yiibai.com/plsql/