PL/SQL - 基础知识(1)

-- PL/SQL 块结构
DECLARE --- 可选    变量声明定义
BEGIN ---- 必选     SQL 和PLSQL 语句
EXCEPTION ---- 可选    错误处理
END；---- 必选         

declare
    vjob varchar(9);
    v_count number:=0;
    vtotal date:=sysdate +7;
    c_tax constant number(3,2):=8.25;
    v_valid boolean not null:=true;
begin
    select sysdate into vtotal from dual;
end;
/

-- %TYPE的属性：声明一个变量使之与数据库某个列的定义相同或与另一个已经定义过的变量相同
-- 所以%TYPE要作为列名的后缀

-- 只有TRUE、FALSE、NULL可以赋值给BOOLEAN变量
-- 此变量可以接逻辑运算符NOT、AND、OR
-- 变量只能产生TRUE、FALSE、NULL

-- 使用HOST VARIABLES
variable n number
print n
-- :n这个加了：前缀的变量不是PLSQL变量，而是HOST
:n=v_sal /12;

BEGIN
    SELECT TO_CHAR(HIREDATE,'MON,DD,YYYY') FROM EMP;
END;

V_comment:=user||':'||sysdate; -- 会编译出错
V_comment:=user||':'||to_char(sysdate); --正确
-- 如果有可能，PLSQL都会进行数据一致性的转换，但ORACLE推荐你应该进行显示的转换，因为这样会提高性能。

-- 主块中的变量，如果子块中没有同名变量声明，则继承主块中的声明和初始化值；
-- 子块中声明的变量主块中并不知晓
declare
    v_weight number(3):=600;
    v_message varchar2(255):='product10000';
begin
    declare
        --sub-block
        v_weight number(3):=1;
        v_message varchar2(255):='pro300';
    begin
        v_weight:=v_weight +1;
        dbms_output.put_line('subblock value is '||v_weight);
    end;
    v_weight:=v_weight +1;
    v_message:=v_message || 'my name';
    -- 我们发现MAINBLOCK中V_WEIGHT为 601
    dbms_output.put_line('main value is '||v_weight);
end;
/        

-- 可以在未知其他字段大小和类型的时候定义变量，提高兼容性
declare
    v_deptno dept.deptno%type;
    v_loc dept.loc%type;
begin
    select deptno,loc into v_deptno,v_loc
        from dept
        where dname='SALES';
    DBMS_OUTPUT.PUT_LINE (V_deptno ||' and '||v_loc);
end;
/

-- 游标是一个独立SQL工作区，有两种性质的游标：
--    隐式游标： 当PARSE 和EXECUTE 时使用隐式游标。
--    显式游标： 是由程序员显式声明的。

-- 游标的属性：
--    SQL%ROWCOUNT：一个整数值，最近SQL语句影响的行数
--    SQL%FOUND    BOOLEAN属性，如果为TRUE，说明最近的SQL STATEMENT有返回值
--    SQL%NOTFOUND 与SQL%FOUND相反
--    SQL%ISOPEN   在隐式游标中经常是FALSE，因为执行后立即自动关闭了

variable row_de number

declare
    v_deptno number:=10;
begin
    delete from emp where deptno=v_deptno;
    :row_de:=sql%rowcount;
end;

print row_de --这是一个SQL PLUS变量
ROW_DE
----------
    4

-- 这时其实并没有真正的删除，而是需要 COMMIT或ROLLBACK，来完成事务

-- 可以是分支和循环：IF THEN END IF 
IF condition THEN
    statements;
[ELSIF condition THEN
    statements;]
[ELSE
    statements;]
END IF;

-- 这里我们可以注意，PLSQL和C语言或JAVA在条件上的不同，=代表关系运算，而:=代表赋值
IF V_ENAME='OSBORNE' THEN
    V_MGR:=22;
END IF;

create FUNCTION calc_val(v_start IN NUMBER)
    RETURN NUMBER
IS
BEGIN
    IF v_start > 100 THEN
        RETURN (2 * v_start);
    ELSIF v_start >= 50 THEN
        RETURN (.5 * v_start);
    ELSE
        RETURN (.1 * v_start);
    END IF;
END calc_val;

 

-- ==========LOOP============
LOOP
    statement1;
    statement2;
    . . .
EXIT [WHEN condition];
END LOOP;

LOOP
    INSERT INTO s_item (ord_id, item_id) VALUES (v_ord_id, v_counter);
    v_counter := v_counter + 1;
EXIT WHEN v_counter > 10;
END LOOP;

-- ==========FOR============
FOR index IN [REVERSE] lower_bound..upper_bound LOOP
    statement1;
    statement2;
    . . .
END LOOP;

DECLARE
    V_LOWER NUMBER:=1;
    V_UPPER NUMBER:=23;
BEGIN
    DBMS_OUTPUT.PUT_LINE('');
    FOR I IN V_LOWER..V_UPPER LOOP
            DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/

-- ==========WHILE============
WHILE condition LOOP
    statement1;
    statement2;
    . . .
END LOOP;