pl/sql小结

/*
 * PL/SQL - 
 *  是Oracle数据库特定的数据库编程语法.
 * 所有的数据库存储过程/函数/触发器开发,都是数据库特定的语法.
 * 第一代: 机器语言
 * 第二代: 面向过程的语言
 * 第三代: 面向对象的语言
 * 第四代语言: 面向结果的语言. 语言编写完毕后,只要求结果正确.
 * 
 * PL/SQL是在Oracle数据库管理系统中开发的语法.
 * 是将有逻辑的SQL语句,固化在Oracle的DBMS中,可以重复调用的方式.
 * 如: to_date()  to_char()  replace()  substring()  max()  min()  avg()  upper()
 */

/*
 * Oracle数据库为了提高执行效率.默认是关闭手工输出流的.
 * 开启手工输出:
 * set serveroutput on;
 * 关闭手工输出:
 * set serveroutput off;
 */
-- hello world
/*
 * 执行存储过程/函数的方式
 * 1. execute - 执行
 * 2. call - 调用, call命令可能有缓存. 结果输出不是每次都能看到的.
 *  必须刷新缓存或缓存空间不足的时候,才能看到结果
 * 3. 匿名代码块, 只能执行唯一一次的存储过程/函数.
 */
execute dbms_output.put_line('hello world');
call dbms_output.put_line('hello world with call');
begin
    dbms_output.put_line('hello world with block');
end;
/

/*
 * 匿名代码块结构及常用语法
 * declare 
 *     定义变量
 *     变量定义语法
 *     变量名 变量类型;
 *     变量定义并初始化语法  , := 赋值, = 比较等值  , > < >= <= != <>
 *     变量名 变量类型 := 变量数据;
 * begin
 *     定义代码逻辑
 *     变量赋值 -   变量名 := 值;
 *     执行语法 -   语法;
 * end;
 */
-- 循环 loop
-- 1. loop循环, 天生就是无限循环
declare
    v_i number := 1;
begin
    loop -- 开始循环
        dbms_output.put_line(v_i);
        v_i := v_i + 1;
        exit when v_i > 10; -- 当条件满足的时候,执行exit命令. 
    end loop; -- 结束循环
end ;
/

-- while loop循环
declare
    v_i number := 1;
begin
    while v_i < 11 loop -- 当条件满足的时候,循环
        dbms_output.put_line(v_i);
        v_i := v_i + 1;
    end loop; -- 结束循环
end ;
/

-- for循环
begin
    for v_i in reverse 1..10 loop -- 当变量v_i在范围1到10之间的时候,循环. for循环数据自增1
        dbms_output.put_line(v_i);
    end loop;
end ;
/

/*
 * PL/SQL中的CRUD
 */
-- insert
declare
    v_id number := 2;
    str varchar(32) := 'bbb';
    age number := 30;
begin
    -- insert into tb_temp(id, v_str, v_age) values(1, 'aaa', 20);
    insert into tb_temp(id, v_str, v_age) values(v_id, str, age);
    commit;
end ;
/

-- update
begin
    update tb_temp
    set v_str = 'ccc', v_age = 40
    where id = 1;
    commit;
end ;
/

-- delete
begin
    delete from tb_temp where id = 2;
    commit;
end ;
/

-- select, 要求将查询结果保存在变量中. 因为PL/SQL没有自动输出. 查询结果无保存单元.
-- 限制为只能查询一条数据
declare
    v_id number;
    str varchar2(32);
    age number;
begin
    select id, v_str, v_age into v_id, str, age from tb_temp;
    dbms_output.put_line('id = ' || v_id || ', str = ' || str || ', age = ' || age);
end ;
/

-- cursor 游标, 用于处理查询的PL/SQL语法结构.
-- 类似java中的ResultSet. 在Oracle中是用来定位查询语句结果内存的一个引用.
/*
 * 游标定义方式
 *  cursor 游标命名 is 查询语法;
 * 游标属性
 *  游标命名%found -- 最近一次fetch是否有结果, 无结果返回false,有结果返回true
 *  游标命名%notfound -- 最近一次fetch是否有结果, 有结果返回false,无结果返回true
 *  游标命名%isopen -- 是否开启. 已开启返回true
 */
declare
    cursor c_temp is select * from tb_temp;
begin
    for v_temp in c_temp loop -- 变量v_temp的类型就是游标中的一行数据的类型.
        dbms_output.put_line(v_temp.id || ' , ' || v_temp.v_str || ' , ' || v_temp.v_age);
    end loop;
end ;
/

declare
    cursor c_temp is select * from tb_temp;
    v_temp tb_temp%rowtype; -- 变量v_temp和表格tb_temp中的一行数据的类型一样
begin
    -- 开启游标
    open c_temp;
    
    -- 循环游标
    loop
        fetch c_temp into v_temp; -- 迭代游标中的一行数据到变量v_temp中.
        exit when c_temp%notfound; -- %notfound是游标的属性,代表最近一次fetch是否有新的数据.
        dbms_output.put_line(v_temp.id || ' , ' || v_temp.v_str || ' , ' || v_temp.v_age);
    end loop;
    
    -- 关闭游标
    close c_temp;
end ;
/

declare
    cursor c_temp is select * from tb_temp;
    v_temp tb_temp%rowtype; -- 变量v_temp和表格tb_temp中的一行数据的类型一样
begin
    -- 开启游标
    open c_temp;
        
    fetch c_temp into v_temp; -- 迭代游标中的一行数据到变量v_temp中.
    
    -- 循环游标
    while c_temp%found loop
        dbms_output.put_line(v_temp.id || ' , ' || v_temp.v_str || ' , ' || v_temp.v_age);
        fetch c_temp into v_temp; -- 迭代游标中的一行数据到变量v_temp中.
    end loop;
    
    -- 关闭游标
    close c_temp;
end ;
/

/*
 * 判断语法
 * if xxx then
 * end if;
 * if xxx then
 * end if;
 * if xxx then
 * elsif xxx then
 * else
 * end if;
 */
declare
    v_i number := 0;
begin
    -- v_i > 0 输出正数, <0 输出负数, == 0输出零
    if v_i > 0 then
        dbms_output.put_line('正数');
    elsif v_i < 0 then
        dbms_output.put_line('负数');
    else
        dbms_output.put_line('零');
    end if;
end ;
/

/*
 * 存储过程 - procedure
 * 创建存储过程,语法和创建表类似
 * 存储定义逻辑和匿名代码块类似
 * 没有返回值.
 */
-- 定义
create procedure print(v_str varchar2) -- ()中定义参数, 参数只定义类型,不定义长度.
as -- 代替declare, as或is关键字不可少.
begin
    dbms_output.put_line(v_str);
end;
/

-- 定义+重置
create or replace procedure print(v_str varchar2) -- ()中定义参数, 参数只定义类型,不定义长度.
as -- 代替declare, as或is关键字不可少.
begin
    dbms_output.put_line(v_str);
end;
/

/*
 * 查看存储过程定义的编译错误: show errors;
 * 
 * 存储过程中的参数
 * 在存储过程中,参数除有类型外,还有传递方向.
 * 方向分为三种
 * 1. 输入参数 - 默认参数方向, 代表参数数据只能读,不能写. 定义语法 : 参数名 [in] 参数类型
 * 2. 输出参数 - 可以作为返回值的参数. 可写. 特性为, 第一次在存储过程中使用参数,格式化. 在使用的时候有要求
 *   要求是,传入的输出参数必须是一个可复制的变量.不能是常量. 定义语法: 参数名 out 参数类型
 * 3. 输入输出参数 - 可读可写. 不会格式化.语法: 参数名 in out 参数类型. 最不常用的参数方向.
 *   数据库是一个弱类型的数据存储管理工具. 容易造成参数转化错误
 */

create or replace procedure helloWorld(v_name in out varchar2)
as
begin
    v_name := 'hello ' || v_name;
    print(v_name);
end ;
/

create or replace procedure helloWorld(v_name out varchar2)
as
begin
    v_name := 'hello ' || v_name;
    print(v_name);
end ;
/

declare
    v_name date := sysdate;
begin
    print('调用helloWorld之前: ' || v_name);
    helloWorld(v_name);
    print('调用helloWorld之后: ' || v_name);
end ;
/

declare
    v_name varchar2(32) := 'world';
begin
    print('调用helloWorld之前: ' || v_name);
    helloWorld(v_name);
    print('调用helloWorld之后: ' || v_name);
end ;
/

create or replace procedure print(v_str in varchar2) -- ()中定义参数, 参数只定义类型,不定义长度.
as -- 代替declare, as或is关键字不可少.
begin
    -- v_str := 'hello ' || v_str;
    dbms_output.put_line(v_str);
end;
/


/*
 * 函数 function
 * 和存储过程之间的区别有:
 * 1. 关键字不同
 * 2. 有返回值
 * 3. 调用函数和调用存储过程方式不同.
 *    调用函数可以在SQL语法的DQL语句中使用.存储过程不能在DQL语句中使用.
 *    函数调用的时候,可以使用变量接收函数的返回值. 存储过程不能.
 */
create or replace function f_test(v_str varchar2)
return varchar2
as
begin
    return 'hello ' || v_str;
end ;
/

declare
    v_str varchar2(32);
begin
    -- v_str := f_test('zhangsan');
    -- print(v_str);
    print(f_test('lisi'));
end ;
/

/*
 * 触发器, trigger. 不推荐使用. 影响数据库管理系统的执行效率
 * 触发器都是为表格提供的. 类似java中的监听器. 当某条件满足的时候,自动执行.
 * 对表格的CUD操作.
 * 通常触发器用于表中数据的保护.
 * 如: ERP系统中的用户数据保护. 用户为系统持有者. 如: 京东.京东用户是京东的员工. 
 */
    
create or replace trigger ti_test1 before update on tb_temp
for each row -- 行级触发器. 当表中每行数据有CUD操作时触发.
when (old.id < 100) -- 当更新数据的id小于100的时候,触发器执行.
begin
    dbms_output.put_line('before update触发器执行');
    
    -- :old : 旧的数据.在更新和删除的时候存在.
    dbms_output.put_line(:old.id || ' , ' || :old.v_str || ' , ' || :old.v_age);
    
    -- :new : 新的数据.在新增和更新的时候存在.
    dbms_output.put_line(:new.id || ' , ' || :new.v_str || ' , ' || :new.v_age);
end ;
/

create or replace trigger ti_test2 after update on tb_temp
begin
    dbms_output.put_line('after update触发器执行');
end ;
/


-- 网络查询JDBC中的CallableStatement  call xxxx();










create table tb_temp(
    id number(8) primary key,
    v_str varchar2(32),
    v_age number(3)
);