PL/SQL

PL：Procedure Language

SQL本身是操作结果集的，PL/SQL可以对数据流过程进行控制

• 匿名PL/SQL块

  动态构造，只能执行一次

• 存储过程，函数，触发器

  存储在数据库中编译过的代码，可以反复执行

匿名PL/SQL块

示例：

begin开始，end结束，中间是业务代码。

begin
    for i in 1..10 loop
        null;
    end loop;
end;
/

打印语句

dbms_output.put_line('hello world');

tips:

在plsql中，屏幕上需要先打开开关才能看到打印的内容：

set serveroutput on;

Loop循环

循环插入示例：

begin
    for i in 1..100 loop  -- 循环开始
        insert into t values(i, 'x');
    end loop;  -- 循环结束 
end;
/

定义变量

declare
    x varchar2(40):='test';
begin
    dbms_output.put_line(x);
end;
/

游标

游标是一个指向上下文的句柄（handle）或指针。通过游标，pl/sql可以控制上下文区和处理语句时上下文区会发生些什么事情。

declare
    x t.id%type;  -- x变量使用t表的id字段的类型
    cursor c is select * from t;
begin
    open c;
    loop
        fetch c into x;
        exit when c%notfound;
        dbms_output.put_line('id is ' || x);
    end loop;
    close c;
end;
/

存储过程

编译好的PL/SQL块，有自己的名称，保存在数据库中可以被调用执行。

可以输出参数。

创建存储过程

create [or replace] procedure Procedure_name
    [(argument [{ IN | IN OUT}] type,       -- IN 类型的变量可以把自己的值赋给别的变量，但是不允许给他自己重新赋值
    argument [{ IN | OUT | IN OUT}] type)]    -- OUT 类型的变量可以被赋值，但是不可以把自己的值赋给别的变量
    (is | as)                                 -- IN OUT 类型变量，既可以把自己的值赋给别的变量，也可以给该变量重新赋值
    <类型, 变量说明> 
begin
    <执行部分>
exception
    <可选的异常错误处理程序>
end;
/

Tips:

在plsql命令行中，如果创建存储过程报错，默认只会提示”创建的过程带有编译错误“。如果要查看完整错误信息，可以使用：

show error;

执行存储过程：

-- 方式1：使用exec执行
exec Procedure_name(argument);  
-- 方式2：是要用匿名pl/sql块执行
begin
    Procedure_name(argument);
end;

动态SQL

如果想要在存储过程中执行ddl语句，直接写ddl语句会报错：

-- 此时会报错，不允许在存储过程中出现drop等ddl语句
create or replace procedure drop_table is
begin
    drop table t purge;
end drop_table;

可以使用动态SQL：

create or replace procedure drop_table is
begin
-- 动态sql：数据库在编译时不会校验字符串中的sql语句是否正确，只会在执行时才运行该语句
    execute immediate 'drop table emp purge';
end drop_table;
/

函数

函数要返回一个结果；

函数可以在SQL语句中调用；

创建语句：

create [or replace] function function_name
    [(argument [{ IN | IN OUT}] type,
      argument [{ IN | IN OUT}] type)]
   return return_type
   { IS | AS }
   <类型, 变量说明>
begin
    function_body
exceptioin
    其他语句
end;
/