Oracle 存储过程

1. 结构

CREATE OR REPLACE PROCEDURE 存储过程名称
(
     参数1 IN\OUT  参数1类型,
     参数2 IN\OUT  参数2类型
)
    变量1  变量1类型;
    变量2  变量2类型;
BEGIN
     .....
     .....
END;

2. 循环、判断

-- 循环
WHILE V_TEST = 1 LOOP
    BEGIN
        do somethig
    END;
END LOOP;

-- 判断
IF V_TEST = 1 THEN
    BEGIN
        do somethin
    END;
END IF;

3. 查询结果赋值

-- 将select查询到的结构存入变量中，可以同时把多个列存储多量中
-- 必须有一条数据，否者抛出异常（如果没有记录抛出NO_DATA_FOUND）

BEGIN
    SELECT COL1, COL2 INTO 变量1，变量2 FROM ......
EXCEPTION
    WHERE NO_DATA_FOUND THEN
        XXXX
END;

4. 游标

-- for .... in
CREATE OR REPLACE PROCEDURE 存储过程名称
(
     参数1 IN\OUT  参数1类型,
     参数2 IN\OUT  参数2类型
)
   CURSOR cur IS SELECT ..........
BEGIN
     FOR cur_result in cur LOOP:
         BEGIN:
              V_SUM := cur_result.column1 + cur_result.column2
         END;
      END LOOP;
END

-- 使用带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT .....
  OPEN C_USER(变量值）;
  LOOP
     FETCH C_USER INTO V_NAME;
  EXIT FETCH C_USER%NOTFOUND;
    do something
  END LOOP;
  CLOSE C_USER;

5. 打印输出 dbms_output

命令	作用	备注
enable	在serveroutput on的情况下，用来使dbms_output生效(默认即打开)	启用serveroutput : set serveroutput on
disable	在serveroutput on的情况下，用来使dbms_output失效	关闭serveroutput : set serveroutput off
put	将内容写到内存，等到put_line/new_line时一起输出
put_line(value)	输出字符（如果缓存中有内容，同时输出）
new_line	换行（如果缓存中有内容，同时输出）
get_line(value, status)	获取缓冲区的单行信息（返回的内容被存在value中，返回的状态会被存在status）	status=0 ： 返回成功；status=1 ： 返回空（没有更多行）
get_lines(array, status)	以数组形式来获取缓冲区的多行信息	status 同上

6. 立即执行sql execute immediate

begin
    -- execute immediate用法1：立刻执行sql语句
    v_sql := 'create or replace view myview as select id,name from student';
    execute immediate v_sql;
    
    --- execute immediate用法2：立刻执行sql语句，并赋值给某个变量
    v_sql := 'select count(1) from student';
    execute immediate v_sql into v_num;
    
    -- execute immediate用法3：带参数的sql
    v_sql:='select * from student t where t.name=:1 and t.age=:2'; 
    execute immediate v_sql using 'ZhangSan',23;
end;