oracle-PLSQL的一些基本语句使用

--PL/SQL实例
/*
declare
    声明变量，初始化变量，声明游标
begin
    执行部分
exception
    异常捕获部分
end;


*/
declare
    --声明变量
    out_text varchar2(20);
    i integer;
begin
    out_text := '程序输出222';
    i := 1/0;
    dbms_output.put_line(out_text);
    exception
        --出现异常之后执行
        when others then
            dbms_output.put_line('出现了一个异常');
end;


select * from emp;


--嵌套执行PL/SQL块
declare
    out_text varchar2(20);
begin
    out_text := '外层PL/SQL块';
    dbms_output.put_line(out_text);
    --嵌套
    declare
        inner_text varchar2(20) := '内层PL/SQL块';
    begin
        dbms_output.put_line(inner_text);
    end;
end;


--常用的运算符
declare
    x number := 10;
    y number := 20;
    z number;
begin
    z := x*y;
    dbms_output.put_line('x*y='||z);
end;


declare
    ename varchar2(20) := 'tom';
begin
    ename := ename||'cat';
    dbms_output.put_line(ename);
end;


--通过select ... into 语句对变量进行赋值,使用该语句对变量进行赋值时，语句查询结果不能是零行或多行（有且仅有一行）
declare
    v_ename varchar2(20);
begin
    select ename into v_ename from emp where empno=7943;
    dbms_output.put_line(v_ename);
end;


--%rowtype和%type的使用


--%rowtype：将数据库表中的一行数据作为类型，在赋值时，需要查询出对应列数的数据进行赋值
declare
    v_emp emp%rowtype;
begin
    select * into v_emp from emp where empno=7943;
    dbms_output.put_line('员工姓名:'||v_emp.ename||',职位：'||v_emp.job);
end;


--%type:将表中一列作为类型
declare
    v_ename emp.ename%type;
begin
    select ename into v_ename from emp where empno=7369;
    dbms_output.put_line(v_ename);
end;




--记录类型
/*
type 类型名 is record(
type1 数据类型,
type2 数据类型,
...
);
*/
declare
    --定义类型
    type emp_type is record(
        empno emp.empno%type,
        ename emp.ename%type,
        sal emp.sal%type,
        job emp.job%type
    );
    --定义该类型的变量
    employee emp_type;
begin
    select empno,ename,sal,job into employee from emp where empno=7943;
    dbms_output.put_line(employee.empno||'  '||employee.ename||'  '||employee.sal||'  '||employee.job);
end;


/*
PL/SQL中的流程控制语句
    顺序结构
    选择结构
    循环结构
*/
--IF ... THEN  end if;
declare
    v_sal emp.sal%type;
begin
    select sal into v_sal from emp where empno=7934;
    --对薪资进行判断，然后更新奖金列
    if v_sal>4000 then
        update emp set comm=100 where empno=7370;
    elsif v_sal <2000 then
        update emp set comm=500 where empno=7370;
    else 
        update emp set comm=200 where empno=7370;
    end if;
    commit;
end;


-- case when then 类似于java中的switch
declare
    v_grade varchar2(2);
begin
    v_grade := upper('&asd');
    case v_grade
        when 'A' then
            dbms_output.put_line('优');
        when 'B' THEN
            dbms_output.put_line('中');
        when 'C' THEN
            dbms_output.put_line('差');
        else 
            dbms_output.put_line('滚出去');
    end case;
end;


--PL/SQL循环结构
--loop ... end loop
-- 1+2+3....+100
declare 
    i number := 0;
    v_sum number := 0;
begin
    --无限循环
    loop 
        v_sum := v_sum + i;
        i := i + 1;
        --给无限循环指定出口
        if i>100 then
            exit;
        end if;
        dbms_output.put_line(i);
    end loop;
    dbms_output.put_line(v_sum);
end;


--while循环
declare
    i number := 0;
    v_sum number := 0;
begin
    while i<=100 loop
        v_sum := v_sum + i;
        i := i + 1;
    end loop;
    dbms_output.put_line(v_sum);
end;


--for
declare
    i number := 1;
    v_sum number := 0;
begin
    -- 1..100 会自动增长，默认情况是从小到大，如果需要从大到小需指定reverse属性
    for i in reverse 1..100 loop
        v_sum := v_sum + i;
        dbms_output.put_line(i);
    end loop;
    dbms_output.put_line(v_sum);
end;


--动态sql
/*
execute immediate 动态sql字符串 [into 变量名] [using 参数列表]
*/
declare
    v_ename varchar2(20) := 'tom';
    v_job varchar2(20) := 'dba';
    v_empno number := 7942;
begin
    --相当于jdbc  update emp set ename=:name 
    execute immediate 'update emp set ename=:1,job=:2 where empno=7943' using v_ename,v_job;
    
    execute immediate 'select ename from emp where empno=:1' into v_ename using v_empno;
    dbms_output.put_line(v_ename);
    commit;
end;


--游标的使用
/*
使用游标的步骤：1.定义  2.打开  3.使用  4.关闭
*/
--静态游标
declare
    cursor my_cursor is select empno,ename from emp;
    --定义用于接收游标数据的变量
    v_empno emp.empno%type;
    v_ename emp.ename%type;
begin
    --使用游标的%isopen属性判断游标是否打开
    if not my_cursor%isopen then
        --使用open关键字打开游标
        open my_cursor;
    end if;
    --获取到游标中的第一行数据
    loop 
        fetch my_cursor into v_empno,v_ename;
        --数据取完之后需要退出循环
--        if my_cursor%notfound then
--            exit;
--        end if;
        exit when my_cursor%notfound;
        dbms_output.put_line(v_empno||'  '||v_ename);
    end loop;
    --关闭游标
    close my_cursor;
end;


--oracle为了简化游标的使用提供了游标的for循环
--隐式的打开了游标，使用fetch获取游标中的数据，关闭了游标
declare
    cursor my_cursor is select * from emp;
begin
    for employee in my_cursor loop
        dbms_output.put_line(employee.empno||' '||employee.ename||' '||employee.sal||' '||employee.deptno);
    end loop;
end;




--动态游标的使用
/*
语法：type 游标类型名 is ref cursor [return 返回值]
*/
declare
    --定义一个强游标类型
    type my_cursor_type is ref cursor return emp%rowtype;
    --定义该游标类型的游标变量
    my_cursor my_cursor_type;
    --定义用于存储游标数据的变量
    v_emp emp%rowtype;
begin
    --打开游标
    open my_cursor for select * from emp;
    loop 
        fetch my_cursor into v_emp;
        exit when my_cursor%notfound;
        dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.sal);
    end loop;
end;