Oracle PL/SQL

2019独角兽企业重金招聘Python工程师标准>>>

PL/SQL

-- 输出功能
-- set serveroutput on size 10000

DECLARE
    -- 标量型变量
    v_result                    NUMBER(8, 2);
    -- %TYPE会根据表中字段的类型定义变量
    v_originName                origin.originName%TYPE;
    v_constValue                CONSTANT v_originName%TYPE := '这是一个常量';
    -- 计数
    v_num                       NUMBER(8) := 1;
    v_sum                       NUMBER(8) := 0;

    -- 复合型变量
    -- 1）记录型
    TYPE productinfo_rec IS RECORD
    (
        pro_rec_productId       productinfo.productId%TYPE,
        pro_rec_productName     productinfo.productName%TYPE,
        pro_rec_productprice    NUMBER(8, 2)
    );
    v_productinfo               productinfo_rec;
    -- 2）记录整行类型
    v_productinfo2              productinfo%ROWTYPE;
    -- 3）索引表类型(类似map)
    -- table_name%ROWTYPE
    -- table_name.column_type%TYPE
    -- 标量类型
    -- 相当于value的类型
    TYPE productinfo_tab IS TABLE OF productinfo%ROWTYPE
        -- PLS_INTEGER
        -- BINARY_INTEGER
        -- VARCHAR2(v_size)
        -- 表示数组下标的数据类型，相当于key的类型
        INDEX BY BINARY_INTEGER;
    v_productinfo_row           productinfo_tab;
    -- 索引表类型之NUMBER-VARCHAR2
    TYPE int_string_tab IS TABLE OF NUMBER(5, 2)
        INDEX BY VARCHAR2(20);
    v_int_string                int_string_tab;

BEGIN
    -- 标量型变量
    v_result := 100/6;
    dbms_output.put_line('结果为：' || v_result);
    select originName INTO v_originName
        from origin
        where originId = '1';
    DBMS_OUTPUT.PUT_LINE('产品的地址为：' || v_originName);
    DBMS_OUTPUT.PUT_LINE('v_constValue：' || v_constValue);

    -- 复合型变量
    -- 1）记录型
    select productId, productName, productprice INTO v_productinfo
     from productinfo
     where productId = '1';
    DBMS_OUTPUT.PUT_LINE('产品编号为（记录型输出）：' || v_productinfo.pro_rec_productId);
    DBMS_OUTPUT.PUT_LINE('产品名称为（记录型输出）：' || v_productinfo.pro_rec_productName);
    DBMS_OUTPUT.PUT_LINE('产品价格为（记录型输出）：' || v_productinfo.pro_rec_productprice);
    -- 2）记录整行类型
    select * INTO v_productinfo2
        from productinfo
        where productId = '1';
    DBMS_OUTPUT.PUT_LINE('产品产地为（整行类型输出）：' || v_productinfo2.originId);
    -- 3）索引表类型
    select * INTO v_productinfo_row(1)
        from productinfo
        where productId = '1';
    DBMS_OUTPUT.PUT_LINE('产品名称为(索引表类型输出)：' || v_productinfo_row(1).productName);
    -- 索引表类型之NUMBER-VARCHAR2
    v_int_string('你好') := 3;
    v_int_string('Oracle') := 10;
    DBMS_OUTPUT.PUT_LINE('v_int_string(索引表类型输出)：' || SQRT((v_int_string('Oracle')-v_int_string('你好'))**2));

    -- if判断
    IF v_int_string('你好') > v_int_string('Oracle') THEN
        DBMS_OUTPUT.PUT_LINE('较大值为（if判断输出）：' || v_int_string('你好'));
    ELSIF v_int_string('你好') < v_int_string('Oracle') THEN
        DBMS_OUTPUT.PUT_LINE('较大值为（if判断输出）：' || v_int_string('Oracle'));
    ELSE
        DBMS_OUTPUT.PUT_LINE('较大值为（if判断输出）： 相等');
    END IF;

    -- case
    case v_int_string('Oracle')
    WHEN 3 THEN
        DBMS_OUTPUT.PUT_LINE('CASE -> 3');
    WHEN 10 THEN
        DBMS_OUTPUT.PUT_LINE('CASE -> 10');
    END CASE;

    --     LOOP循环
    <<loop_test>>
    LOOP
        v_num := v_num + 1;
        EXIT loop_test WHEN v_num > 5;
    END LOOP;

    -- 赋值
    v_num := 1;

    <<while_loop>>
    WHILE v_num <= 10 LOOP
        v_num := v_num + 1;
    END LOOP;

    -- 赋值
    v_num := 1;

    <<for_loop>>
    FOR v_num IN 1..100 LOOP
     v_sum := v_sum + v_num;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('1加到100的和为：' || v_sum);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('没有对应的编号！');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('对应的数据过多！');

END;
/

----------------------------------------------------------------------------------------------------------------

PL/SQL DML DDL

DECLARE
    v_createStudent varchar2(1000);

BEGIN
    insert into origin values('1', '刘观良');
    COMMIT;

    v_createStudent := 'create table student
                        (
                            studentId        number(5),
                            studentName      varchar2(20),
                            studentGender    number(1)
                        )';
    EXECUTE IMMEDIATE v_createStudent;
END;
/

----------------------------------------------------------------------------------------------------------------

PL/SQL函数

CREATE OR REPLACE FUNCTION LOW_PRICE(V_PRICE IN NUMBER) 
RETURN VARCHAR2 IS
    V_RETURN VARCHAR2(20);
BEGIN
    select productName into V_RETURN
        from productinfo
        where productprice = V_PRICE;
    RETURN V_RETURN;
END;
/

select LOW_PRICE(9.9) from dual;
drop FUNCTION LOW_PRICE;

----------------------------------------------------------------------------------------------------------------

存储过程

CREATE OR REPLACE PROCEDURE TESTPRO 
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('存储过程！！！');
END;
/

EXEC TESTPRO;
drop PROCEDURE TESTPRO；

----------------------------------------------------------------------------------------------------------------
存储过程与存储函数的区别和联系
相同点：1.创建语法结构相似，都可以携带多个传入参数和传出参数。
        2.都是一次编译，多次执行。

不同点：1.存储过程定义关键字用procedure，函数定义用function。
        2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。

        3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。

总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。

区别和联系来源：https://www.cnblogs.com/zlbx/p/4818007.html

----------------------------------------------------------------------------------------------------------------

转载于:https://my.oschina.net/feistel/blog/3057366