Oracle数据库常见操作

作者: JasonGofen | 来源:发表于2019-10-23 19:50 被阅读0次

一篇详尽的Oracle常见语句记录~~不断补充

数据导出

导出库

exp account/password@dbname file=D:\your_path.dmp

导入库

数据泵导出

数据泵导入

基操

# 系统管理员身份登录
sys as sysdba
# 删除用户下所有数据  
drop user 用户名 cascade;
# 创建用户    
create user 用户名 identified by 密码;
# 用户授权    
grant dba to 用户名; 
# 修改密码     
alter user 用户名 identified by 新密码;
# 创建表空间：
CREATE TABLESPACE 表空间名称 LOGGING DATAFILE '路径\表空间文件名称.dbf' SIZE 6114M AUTOEXTEND ON NEXT 32M MAXSIZE 8192M EXTENT MANAGEMENT LOCAL;
# 将空间分配给用户：
alter user 用户名 default tablespace 表空间名称;
# 把表空间授权给用户：
grant create session,create table,unlimited tablespace to 用户名;
# 查找用户
select * from dba_users;
# 查找工作空间的路径
select * from dba_data_files; 
# 查看当前数据库连接数
select count(*) from v$process;
# 查看数据库最大连接数
select value from v$parameter where name = 'processes' 
# 修改数据库连接数【修改完后需要重启数据库服务使其生效】
alter system set processes = 300 scope = spfile;
# 查看表结构
desc tablename;

导出数据库表Excel

SELECT t1.Table_Name || chr(13)                     AS "表名称及说明",
       --t3.comments                                 AS "表说明",
       t1.Column_Name                               AS "字段名称",
       t1.DATA_TYPE || '(' || t1.DATA_LENGTH || ')' AS "数据类型",
       t1.NullAble                                  AS "是否为空",
       t2.Comments                                  AS "字段说明",
       t1.Data_Default                                 "默认值"
       --t4.created                                  AS "建表时间"
FROM cols t1
         LEFT JOIN user_col_comments t2
                   ON t1.Table_name = t2.Table_name
                       AND t1.Column_Name = t2.Column_Name
         LEFT JOIN user_tab_comments t3
                   ON t1.Table_name = t3.Table_name
         LEFT JOIN user_objects t4
                   ON t1.table_name = t4.OBJECT_NAME
WHERE NOT EXISTS(SELECT t4.Object_Name
                 FROM User_objects t4
                 WHERE t4.Object_Type = 'TABLE'
                   AND t4.Temporary = 'Y'
                   AND t4.Object_Name = t1.Table_Name)
ORDER BY t1.Table_Name, t1.Column_ID;

常见函数

日期函数

函数	说明
ADD_MONTHS(date, n)	用于从一个日期值增加或减少
MONTHS_BETWEEN(date1, date2)	判断两个日期之间相差的月份
LAST_DAY(date)	函数返回包含日期的月份的最后一天
ROUND(date[,'fmt'])	将日期d按照fmt指定的格式舍入，fmt为字符串
TRUNC(date[,'fmt'])	返回由fmt指定格式的日期
NEXT_DAY(date,'char')	找到下一个星期几
extract(date_field from [datetime_value])	找出日期或间隔值的字段值

日期函数示例

SELECT ADD_MONTHS(SYSDATE, 12) AS NEXT_YEAR FROM DUAL;
>> 2020/10/23 11:39:11
SELECT MONTHS_BETWEEN(TO_DATE('2020/10/23', 'yyyy/mm/dd'), SYSDATE) AS MONTHS FROM DUAL;
>> 12
SELECT LAST_DAY(SYSDATE) AS LAST_DAY FROM DUAL;
>> 2019/10/31 11:39:11
SELECT ROUND(SYSDATE, 'MONTH') AS MONTH FROM DUAL;
>> 2019/11/1
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL; 
>> 2019/1/1
SELECT NEXT_DAY(SYSDATE, '星期五') FROM DUAL;
>> 2019/10/25 11:39:11
SELECT EXTRACT(MONTH FROM SYSDATE) AS MONTH FROM DUAL;
>> 10

字符函数

函数	说明
INITCAP('char')	字符串首字母大写
LOWER('char')	字符串小写
UPPER('char')	字符串大写
LTRIM('char',set)	从左边开始去掉与set相同的字符
RTRIM('char',set)	从右边开始去掉与set相同的字符
TRANSLATE(str, from_str,to_str)	字符替换，from及to字符个数要相等
REPLACE(str,from_str,to_str)	字符替换，from及to字符个数可不相等
INSTR(str,char,m,n)	从m位置开始寻找第n个str字符(串)(m,n缺省为1)
SUBSTR(str,m,n)	截取从m位置开始至第n之间的字符串
CONCAT(str1,str2)	拼接str1与str2字符串

字符函数示例

SELECT INITCAP('hello') FROM DUAL;
>> Hello
SELECT LOWER('HELLO') FROM DUAL;
>> hello
SELECT UPPER('hello') FROM DUAL;
>> HELLO
SELECT LTRIM('HELLO WORLD', 'HELLPO') FROM DUAL; 
>>  WORLD
SELECT RTRIM('HELLO WORLD', 'WORLD') FROM DUAL;
>> HELLO 
SELECT TRANSLATE('HELLO BILL', 'B', 'TI') FROM DUAL;
>> HELLO TILL
SELECT REPLACE('HELLO BILL', 'B', 'TI') FROM DUAL;
>> HELLO TIILL
SELECT INSTR('HELLO','E') FROM DUAL;
>> 2
SELECT SUBSTR('HELLO WORLD', 7, 5) FROM DUAL;
>> WORLD
SELECT CONCAT('HELLO',' WORLD') FROM DUAL;
>> HELLO WORLD

其他字符函数

函数	说明
CHR(int)	输出对应ascii码的字符
ASCII(char)	输出对应字符的ascii码
LPAD(str1,int,str2)	整个字符串长度等于int的情况下，在str1左边插入str2
RPAD(str1,int,str2)	整个字符串长度等于int的情况下，在str1右边插入str2
TRIM([leading/trailing/both] [匹配字符串或数值] FROM [待处理字符串或数值])	去掉符合要求的字符
LENGTH(str)	取字符串长度
DECODE	当指定字段的值满足条件时，输出对应结果

其他字符函数示例

SELECT CHR(65) FROM DUAL;
>> A
SELECT ASCII('A') FROM DUAL;
>> 65
SELECT LPAD('ABCDE', 10, 'W') FROM DUAL;
>> WWWWWABCDE
SELECT RPAD('ABCDE', 10, 'W') FROM DUAL;
>> ABCDEWWWWW
SELECT TRIM('A' FROM 'ABCDEA') FROM DUAL;
>> BCDE
SELECT TRIM(LEADING 'A' FROM 'ABCADEA') FROM DUAL;
>> BCADEA
SELECT TRIM(TRAILING 'A' FROM 'ABCADEA') FROM DUAL;
>> ABCADE
SELECT TRIM(BOTH 'A' FROM 'ABCADEA') FROM DUAL;
>> BCADE
SELECT LENGTH('ABCDE') FROM DUAL;
>> 5
SELECT TU.ID,TU.NAME,TU.AGE,
       DECODE(TU.AGE, 20, '青年',
                      30, '中年',
                      40, '中老年') AS AGE_STAGE
FROM T_USER TU;
>>  ID  NAME AGE AGE_STAGE 
    1   张三  20  青年
    2   李四  30  中年
    3   王五  40  中老年

数字函数

函数	说明
ABS(x)	此函数用来返回一个数的绝对值
CEIL(x)	用来返回大于或等于X的最小整数
COS(x)	返回x的余弦值。x是以弧度表示的角度
COSH(x)	返回X的双曲余弦
FLOOR(x)	用来返回小于或等于X的最大整数
POWER(x,y)	返回X的Y次幂。底X和指数Y都不必是正整数，但如果X是负数的话，Y必须是整数
MOD(被除数，除数)求余	如果除数为0，则返回被除数
ROUND(x[,y])	返回舍入到小数点右边Y位的X值。Y缺省为0，这将X舍入为最接近的整数。如果Y是负数，那么舍入到小数点左边相应的位上，Y必须为整数
TRUNC(x[,y])截取值	Y缺省为0，这样X被截取成一个整数。如果Y为负数，那么截取到小数点左边相应位置
SQRT(x)	返回x的平方根，x不能是负数
SIGN(x)	此函数用来返回一个数的正负值，若为一个正数则返回1，若为一个负数则返回-1，若为0则仍返回0，有点像把模拟量数字化的意思

数字函数示例

SELECT ABS(-100) AS RESULT FROM DUAL;
>> 100
SELECT CEIL(100.001) AS RESULT FROM DUAL;
>> 101
SELECT COS(180) AS RESULT FROM DUAL; 
>> -0.598460069057858
SELECT COSH(180) AS RESULT FROM DUAL;
>> 7.44692100390919E77
SELECT FLOOR(100.001) AS RESULT FROM DUAL;
>> 100
SELECT POWER(2,3) AS RESULT FROM DUAL;
>> 8
SELECT MOD(10,3) AS RESULT FROM DUAL;
>> 1
SELECT ROUND(100.0456, 3) AS RESULT FROM DUAL;
>> 100.046
SELECT TRUNC(100.0456, -1) AS RESULT FROM DUAL;
>> 100
SELECT SQRT(100) AS RESULT FROM DUAL;
>> 10
SELECT SIGN(-100) AS RESULT FROM DUAL;
>> -1

转换函数

函数	说明
TO_CHAR	转换字符
TO_DATE	转换日期
TO_NUMBER	转换数字

转换函数示例

SELECT TO_CHAR(10000, 'C99,999') AS RESULT FROM DUAL;
>> CNY10,000
SELECT TO_CHAR(10000, 'L99,999') AS RESULT FROM DUAL;
>> ￥10,000
SELECT TO_CHAR(10000, 'S99,999') AS RESULT FROM DUAL;
>> +10,000
SELECT TO_CHAR(10000, 'U99,999') AS RESULT FROM DUAL;
>> ￥10,000
SELECT TO_CHAR(10000, '$99,999') AS RESULT FROM DUAL;
>> $10,000
SELECT TO_DATE('2019-10-23', 'YYYY-MM-DD') AS RESULT FROM DUAL;
>> 2019/10/23
SELECT TO_DATE('2019-10-23 15:00:01', 'YYYY-MM-DD HH24:MI:SS') AS RESULT FROM DUAL;
>> 2019/10/23 15:00:01
SELECT TO_NUMBER('10000') AS RESULT FROM DUAL;
>> 10000

其他函数

函数	说明
NVL(exp1,exp2)	如果exp1对应的值为空，用exp2值替代
NVL2(exp1,exp2,exp3)	如果exp1为空，取exp2；如果exp1不为空，取exp3
NULLIF(exp1,exp2)	如果exp1与exp2相等，相等返回null，不相等返回exp1

其他函数示例

SELECT ID,NAME,SAL,NVL(COMM,0) AS COMM,NVL(COMM,0)+SAL AS INCOME FROM T_EMP;
>> 1    张三  1000    500    1500
   2    王五  2000    2000   4000
   3    张三  3000    500    3500
   4    李娜  4000    0      4000
   5    刘二  5000    1000   6000
SELECT ID,NAME,SAL,NVL(COMM,0) AS COMM,NVL2(COMM,COMM+SAL,SAL) AS INCOME FROM T_EMP;
>> 1    张三  1000    500    1500
   2    王五  2000    2000   4000
   3    张三  3000    500    3500
   4    李娜  4000    0      4000
   5    刘二  5000    1000   6000
SELECT ID,NAME,SAL,NVL(COMM,0) AS COMM,NULLIF(SAL,COMM) AS RESULT FROM T_EMP;
>> 1    张三  1000    500    1000
   2    王五  2000    2000    
   3    张三  3000    500    3000
   4    李娜  4000    0      4000
   5    刘二  5000    1000   5000

分组函数

函数	说明
AVG	求平均数
SUM	求和
MIN	求最小数
MAX	求最大数
COUNT	求行数
GROUP BY	分组函数
HAVING	分组时的过滤条件

分组函数示例

SELECT AVG(SAL) AS SAL_AVG FROM T_EMP;
>> 3000
SELECT SUM(SAL) AS SAL_SUM FROM T_EMP;
>> 15000
SELECT MIN(SAL) AS SAL_MIN FROM T_EMP;
>> 1000
SELECT MAX(SAL) AS SAL_MAX FROM T_EMP;
>> 5000
SELECT COUNT(*) AS EMP_COUNT FROM T_EMP;
>> 5
SELECT COMM,COUNT(COMM) FROM T_EMP GROUP BY COMM;
>>      0
   1000 1
   500  2
   2000 1
SELECT COMM,COUNT(COMM) FROM T_EMP GROUP BY COMM HAVING COMM > 0;
>> 1000 1
   500  2
   2000 1

分析函数

函数	说明
ROW_NUMBER	返回连续的排位，不论值是否相等
RANK	具有相等值的行排位相同，序数随后跳跃
DENSE_RANK	具有相等值的行排位相同，序号是连续的

分析函数示例

SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) AS SEAIL,NAME,SAL,COMM FROM T_EMP;
>> SEAIL NAME  SAL   COMM
    1    刘二  5000   1000
    2    李娜  4000   
    3    王五  2000   2000
    4    张三  2000   500
    5    张三  1000   500
SELECT RANK() OVER (PARTITION BY NAME ORDER BY SAL DESC,COMM) AS SEAIL,NAME,SAL,COMM FROM T_EMP;
>> SEAIL NAME  SAL   COMM
    1    刘二  5000   1000
    1    张三  2000   500
    2    张三  1000   500
    1    李娜  4000   
    1    王五  2000   2000
SELECT DENSE_RANK() OVER (PARTITION BY NAME ORDER BY SAL DESC,COMM) AS SEAIL,NAME,SAL,COMM FROM T_EMP;

PL/SQL编程

PL/SQL块

无名块:Anonymous

DECLARE
  code
BEGIN
  -- statements
EXCEPITON
  code
END;

存储过程：Procedure

PROCEDURE name IS

BEGIN
  -- statements
EXCEPTION
  code
END;

函数：Function

FUNCTION name RETURN datatype IS

BEGIN
  -- statements
  RETURN value;
EXCEPTION
  code
END;

控制结构

条件控制

语句	说明
IF-THEN	一种情况，条件满足就执行
IF-THEN-ELSE	两种情况，条件满不满足都能执行对应的部分
IF-THEN-ELSIF	多种情况，执行满足条件的部分
CASE-WHEN-ELSE	多种情况，执行满足条件的部分

条件控制示例

-- 查询comm字段值为空的sal，当sal=4000时，设置comm为1000
declare 
  -- 定义number类型的变量V_SAL
  V_SAL NUMBER;
begin
  -- 查询COMM为空的SAL，赋值给V_SAL
  SELECT T.SAL INTO V_SAL FROM T_EMP T WHERE T.COMM IS NULL;
  -- 判断V_SAL的值为4000时
  IF V_SAL = 4000 THEN
    -- 更新COMM的值为1000
    UPDATE T_EMP T SET T.COMM = 1000 WHERE T.SAL = V_SAL;
    -- 提交事务
    COMMIT;
    -- 打印提示
    DBMS_OUTPUT.put_line('奖金已设置成：1000');  
  ELSE
    UPDATE T_EMP T SET T.COMM = 2000 WHERE T.SAL = V_SAL;
    DBMS_OUTPUT.put_line('奖金已设置成：2000'); 
  END IF;
end;

循环控制

语句	说明
LOOP	无条件循环
WHILE	根据条件循环
FOR	循环固定次数

循环控制示例

-- LOOP示例：循环插入初始化数据
DECLARE
  -- 定义初始化值得变量
  I INT:=5;
BEGIN
  -- 开始循环
  LOOP
    -- 插入数据
    INSERT INTO T_EMP(ID,NAME,SAL,COMM) VALUES (I, 'NAME'||I, I*1000, I*500);
    -- 当i=20时退出循环
    EXIT WHEN I = 20;
    -- 每次循环完成后i+1
    I:=I+1;
  -- 结束循环
  END LOOP;
END;
--------------------------------------------------------------------------------------------------
-- WHILE示例：循环插入初始化数据
DECLARE
  -- 定义初始化值得变量
  I INT:=1;
BEGIN
  -- 开始循环,到20结束
  WHILE I < 21 LOOP
    -- 插入数据
    INSERT INTO T_EMP(ID,NAME,SAL,COMM) VALUES (I, 'NAME'||I, I*1000, I*500);
    -- 每次循环完成后i+1
    I:=I+1;
  -- 结束循环
  END LOOP;
END;
--------------------------------------------------------------------------------------------------
-- FOR示例：循环插入初始化数据
DECLARE
  -- 定义初始化值得变量
  I INT:=1;
BEGIN
  -- 开始循环,1到20结束（正序）
  FOR I IN 1..20 LOOP
    -- 插入数据
    INSERT INTO T_EMP(ID,NAME,SAL,COMM) VALUES (I, 'NAME'||I, I*1000, I*500);
  -- 结束循环
  END LOOP;
END;
--------------------------------------------------------------------------------------------------
-- FOR示例：循环插入初始化数据
BEGIN
  -- 开始循环,到20结束
  FOR I IN REVERSE 1..20 LOOP
    -- 插入数据
    INSERT INTO T_EMP(ID,NAME,SAL,COMM) VALUES (I, 'NAME'||I, I*1000, I*500);
  -- 结束循环
  END LOOP;
END;

顺序控制

语句	说明
GOTO	无条件的转到标签指定的语句
NULL	什么也不做的空语句

顺序控制示例

-- GOTO & NULL示例：如果表数据为空的话，循环插入初始化数据
DECLARE
   V_COUNT INT;
BEGIN
  SELECT COUNT(*) INTO V_COUNT FROM T_EMP;
  IF V_COUNT = 0 THEN
    -- 跳转到INSERT_DATA标签下的代码块
    GOTO INSERT_DATA;
  ELSE
    -- 跳转到INSERT_END标签下的代码块
    GOTO INSERT_END;
  END IF;
  -- 定义标签 INSERT_DATA
  <<INSERT_DATA>>
  -- 开始循环,到20结束
  FOR I IN REVERSE 1..20 LOOP
    -- 插入数据
    INSERT INTO T_EMP(ID,NAME,SAL,COMM) VALUES (I, 'NAME'||I, I*1000, I*500);
  -- 结束循环
  END LOOP;
  -- 定义标签 INSERT_END
  <<INSERT_END>>
  NULL;
END;

异常处理

预处理异常

异常名	说明
NO_DATA_FOUND	执行查询无数据、引用一个末初使化的表、通过UTL_FILE包调用到尾的文件
TOO_MANY_ROWS	采用SELECT INTO语句，但返回的记录超过了1条
DUP_VAL_ON_INDEX	插入或者更新语句，与唯一索引相冲突
TIMEOUT_ON_RESOURCE	等待资源超时
TRANSACTION_BACKED_OUT	远程交易的部份交易已经回滚
INVALID_CURSOR	引用一个不存在的游标，如FETCH或者是CLOSE在其OPEN之前等
NOT_LOGGED_ON	在登陆ORACLE之前执行调用错误
LOGIN_DENIED	登陆时用户名或者密码非法
ZERO_DIVIDE	0为除数
INVALID_NUMBER	将字符串转换成数字，但是转换失败
STORAGE_ERROR	内存不足
PROGRAM_ERROR	系统自身程序错误
VALUE_ERROR	在执行转换、截断、非法转换数据到文本出错
CURSOR_ALREADY_OPEN	打开一个已经打开的游标

预处理异常示例

-- 异常处理
DECLARE
  -- 定义变量
  V_NAME T_EMP.NAME%TYPE;
BEGIN
  -- 查询符合条件的name
  SELECT T.NAME INTO V_NAME FROM T_EMP T WHERE T.NAME = '张三';
  -- 输出结果
  DBMS_OUTPUT.put_line(V_NAME);
  -- 异常处理
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.put_line('用户不存在');
END;

自定义处理异常示例

-- 异常处理
DECLARE
  -- 定义变量
  V_NAME T_EMP.NAME%TYPE;
  ERR EXCEPTION;
BEGIN
  -- 查询符合条件的name
  SELECT DISTINCT T.NAME INTO V_NAME FROM T_EMP T WHERE T.SAL = 10000;
  IF V_NAME = 'NAME10' THEN
    -- 跳转
    RAISE ERR;
  END IF;
  -- 输出结果
  DBMS_OUTPUT.put_line(V_NAME);
  -- 异常处理
  EXCEPTION
    WHEN ERR THEN
      DBMS_OUTPUT.put_line('正确进入到了异常处理块中！');
END;

游标

游标说明图

游标类型
隐式游标：任何SQL都是，PLSQL程序中执行DML语句时自动创建。
显式游标：手动创建，用于处理返回多行查询。
REF游标：用户处理运行时才能确定的动态SQL查询的结果。

隐式游标

属性	说明
SQL%FOUND	SQL语句影响了一行或多行时为true
SQL%NOTFOUND	SQL语句没有影响任何行为时为true
SQL%ROWCOUNT	SQL语句影响的行数
SQL%ISOPEN	游标是否打开，始终为false

隐式游标示例

/* NOTFOUND使用： */
DECLARE
  FLAG CHAR:='U';
BEGIN
  UPDATE T_EMP T SET T.NAME = '张三' WHERE T.SAL = 2000;
  /* 隐式游标 */
  IF SQL%NOTFOUND THEN
    FLAG := 'Z';
    INSERT INTO T_EMP (ID, NAME, SAL, COMM) VALUES ('21', '张三', 10000, 0);
  END IF;
  IF FLAG = 'U' THEN
    DBMS_OUTPUT.PUT_LINE('记录已更改！');
  ELSIF FLAG = 'Z' THEN
    DBMS_OUTPUT.PUT_LINE('记录未找到，新插入成功！');
  ELSE
    DBMS_OUTPUT.PUT_LINE('SQL执行失败！');
  END IF; 
END;
-------------------------------------------------------------------------------------
/* ROWCOUNT使用： */
create or replace FUNCTION MYTESTFUNCTION 
(
  V_COMM IN NUMBER 
) RETURN VARCHAR2 AS 
BEGIN
  IF V_COMM IS NULL THEN RETURN '请输入奖金';
     ELSE 
        UPDATE T_EMP T SET T.SAL = 20000 WHERE T.COMM = V_COMM;
        IF SQL%ROWCOUNT > 0 THEN
            RETURN '修改了'||SQL%ROWCOUNT||'条数据';
        ELSE 
            RETURN '没有修改数据！';
        END IF;
     END IF;
  RETURN NULL;
END MYTESTFUNCTION;

显示游标

使用方式	说明
CURSOR…IS SELECT…	声明游标
OPEN	打开游标
FETCH...INTO...	结果集控制
CLOSE	关闭游标

显示游标示例

DECLARE
    -- 定义游标 MYCUR
    CURSOR MYCUR IS SELECT * FROM T_EMP;
    V_EMP T_EMP%ROWTYPE;
BEGIN
    -- 打开游标
    OPEN MYCUR;
    -- 提取游标
    FETCH MYCUR INTO V_EMP;
    -- 循环
    WHILE MYCUR%FOUND LOOP
        -- 输出内容
        DBMS_OUTPUT.PUT_LINE(V_EMP.NAME||' '||V_EMP.SAL||' '||V_EMP.COMM);
        -- 提取游标下一个内容
        FETCH MYCUR INTO V_EMP;
    END LOOP;
    -- 关闭游标
    CLOSE MYCUR;
END;

带参数显示游标示例

DECLARE
    CURSOR MYCUR(V_COMM NUMBER) IS SELECT * FROM T_EMP WHERE COMM = V_COMM;
BEGIN
    -- 循环游标
    FOR V_EMP IN MYCUR('2000') LOOP
        DBMS_OUTPUT.PUT_LINE(V_EMP.NAME);
    END LOOP;
END;

REF游标

类型	语法
强类型	`TYPE my_curtype IS REF CURSOR RETURN stu_det%ROWTYPE; my_cur my_curtype;`
弱类型	`TYPE my_curtype IS REF CURSOR; my_cur my_curtype;`

REF游标打开

OPEN cursor_name FOR select_statement;

REF游标示例

DECLARE
    -- 定义弱类型游标
    TYPE cursor_type IS REF CURSOR;
    stu_cursor cursor_type;
    v_emp t_emp%rowtype;
BEGIN
    -- 打开游标，指定数据集
    OPEN stu_cursor FOR SELECT * FROM t_emp;
    -- 循环游标
    LOOP
        FETCH stu_cursor INTO v_emp;
        EXIT WHEN stu_cursor%notfound;
        dbms_output.put_line(v_emp.NAME || ' ' || v_emp.comm);
    END LOOP;
    -- 关闭游标
    CLOSE stu_cursor;
END;

使用游标变量执行动态SQL

语法
`OPEN cursor_name FOR dynamic_sqlstring [USING bind_arg_list];`

使用游标变量执行动态SQL示例

-- 查找薪水大于5000的员工信息
DECLARE
    TYPE cursor_type IS REF CURSOR;
    emp_cursor cursor_type;
    v_emp t_emp%rowtype;
    -- 定义动态变量
    v_sal t_emp.sal%TYPE;
BEGIN
    -- 给变量赋值
    v_sal := 5000;
    -- 在SQL中定义 :1 ，使用using插入值
    OPEN emp_cursor FOR 
        'select * from t_emp where sal>:1 order by sal desc'
    USING v_sal;
    dbms_output.put_line('姓名  薪水  奖金');
    LOOP
        FETCH emp_cursor INTO v_emp;
        EXIT WHEN emp_cursor%notfound;
        dbms_output.put_line(v_emp.NAME || ' ' || v_emp.sal || ' ' || v_emp.comm);
    END LOOP;
    CLOSE emp_cursor;   
END;
--------------------------------------------------------------------------------------
-- 查找薪水大于5000的员工信息
DECLARE
    TYPE cursor_type IS REF CURSOR;
    emp_cursor cursor_type;
    v_name t_emp.NAME%TYPE;
    v_sal t_emp.sal%TYPE;
    v_comm t_emp.sal%TYPE;
    -- 定义动态变量
    s_sal t_emp.sal%TYPE;
BEGIN
    -- 给变量赋值
    s_sal := 5000;
    -- 在SQL中定义 :1 ，使用using插入值
    OPEN emp_cursor FOR 
        'select name,sal,comm from t_emp where sal>:1 order by sal desc'
    USING s_sal;
    dbms_output.put_line('姓名  薪水  奖金');
    LOOP
        FETCH emp_cursor INTO v_name,v_sal,v_comm;
        EXIT WHEN emp_cursor%notfound;
        dbms_output.put_line(v_name || ' ' || v_sal || ' ' || v_comm);
    END LOOP;
    CLOSE emp_cursor;   
END;

BULK

bulk语句语句可以提交insert、update、delete语句的执行速度。
通过bulk collect减少loop处理的开销。
采用bulk collect可以将查询结果一次性地加载到collections中，而不是通过cursor一条一条地处理。
可以在select into,fetch into,returning into语句使用bulk collect。
注意在使用bulk collect时，所有的into变量都必须是collections。

BULK示例

DECLARE
    -- 定义REF游标
    TYPE my_cur IS REF CURSOR;
    emp_cur my_cur;
    -- 定义集合
    TYPE namelist IS TABLE OF t_emp.NAME%TYPE;
    TYPE sallist IS TABLE OF t_emp.sal%TYPE;
    TYPE commlist IS TABLE OF t_emp.comm%TYPE;
    names namelist;
    sals sallist;
    comms commlist;
BEGIN
    -- 打开游标，指定数据集
    OPEN emp_cur FOR 
        'select name,sal,comm from t_emp where sal>:1 order by sal desc'
    USING 6000;
    -- 通过使用bulk直接把集合数据放入声明好的集合中，减少loop的开销
    FETCH emp_cur BULK COLLECT INTO names,sals,comms;
    CLOSE emp_cur;
    dbms_output.put_line('姓名  薪水  奖金');
    FOR K IN REVERSE names.FIRST..names.LAST LOOP
        dbms_output.put_line(names(K) || ' ' || sals(K) || ' ' || comms(K));
    END LOOP;
END;

总结：Cursor与Ref Cursor区别

从技术底层看，两者是相同的。普通plsql cursor在定义时是“静态”的。而Ref cursors 可以动态打开。
Ref cursor根据逻辑动态打开;而游标cursor定义好了就无法修改了。
ref cursor可以返回给客户端，cursor则不行。
cursor可以是全局的global，ref cursor则必须定义在过程或函数中。
ref cursor可以在子程序间传递，cursor则不行。
cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用在:向客户端返回结果集。

Oracle数据库常见操作

数据导出

基操

导出数据库表Excel

常见函数

日期函数

日期函数示例

字符函数

字符函数示例

其他字符函数

其他字符函数示例

数字函数

数字函数示例

转换函数

转换函数示例

其他函数

其他函数示例

分组函数

分组函数示例

分析函数

分析函数示例

PL/SQL编程

PL/SQL块

控制结构

异常处理

游标

总结：Cursor与Ref Cursor区别

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读