基础应用

1. 查看字符串出现的次数

查询字符串中字符B出现的次数：

SELECT LENGTH('ABBCDE')-LENGTH(REPLACE('ABBCDE','B','')) FROM DUAL;

或：

SELECT LENGTH(TRANSLATE('ABBCDE','B'||'ABBCDE','B')) FROM DUAL;



或使用正则表达式：REGEXP_COUNT：

select regexp_count('ABBCDE', 'B') from dual

2. 表添加检查约束

alter table l1
  add constraint id_chk1
  check (id>10);

3. Java Source

3.1. 创建 JavaSource

create or replace and compile java source named test as
import java.lang.*;
public class test
{
  public static String test1(String cmd){
         return "ab";
  }
}

3.2. 创建函数

CREATE OR REPLACE FUNCTION Encrypt11 (cryptData IN STRING)

RETURN String

IS

LANGUAGE JAVA

NAME 'test.test1(java.lang.String) return String';

3.3. 创建存储过程

create or replace procedure pro_jv_run_extpro(p_cmd varchar2) as

language java name 'test.test1(java.lang.String)';

相关信息

如果 java 函数需要传参，则在存储过程和函数中需要指定参数，二两函数需要匹配

3.4. jar 上传

java版本1.5

3.4.1. 编写 java 文件

public class demo {
    public static void main(String[] args) {
        System.out.println("main");
    }
    public static String demo1(String params){
        return params;
    }
}

3.4.2. 生成 class

$ORACLE_HOME/jdk/bin/javac demo.java

3.4.3. 生成 jar

$ORACLE_HOME/jdk/bin/jar -cvf demo.jar demo.class

3.4.4. 上传资源文件

loadjava -r -f -o -user usscares/usscares@usscares jlha.jar

3.4.5. 解析 JSON 数据

使用 gson-2.2.1.jar

3.5. 用外部 class 文件来装载创建

create or replace directory CLASS_DIR  as 'c:\test';

create or replace java class using bfile(class_dir,'hello.class');

create or replace procedure prc_hello
(
       p_name VARCHAR2
)
as
language java name 'hello.msg(java.lang.String)';

-- 测试结果

SQL> call prc_hello('java');

4. 动态 SQL

4.1. 不传参不赋值

--拷贝emp表
begin
  execute immediate 'create table test1 as select * from emp';
  --字符串语句最后不需要加分号
end;
--创建表
begin
  execute immediate 'create table temp_table2 ' || '( id integer ,name varchar2(20))';
end;

4.2. 将结果集存在变量中动态运行

--拷贝emp表
declare
  sqls varchar2(100) := 'create table test1_emp as select * from emp';
begin
  execute immediate sqls;
end;

4.3. 动态 SQL 传参和赋值

1. using 传参
2. into 赋值
3. 参数格式 [: 参数】

--案例：根据员工编号查询员工薪资
declare
  v_sal emp.sal%type;
begin
  --执行动态SQL
  execute immediate 'select sal from emp where empno=:员工编号'
  --变量赋值
  into v_sal
  --接收参数
  using &请输入员工编号;
  dbms_output.put_line('工资：' || v_sal);
end;

4.4. 动态 SQL 只赋值不传参

--案例：根据员工编号查询员工薪资
declare
  v_sal emp.sal%type;
begin
  --执行动态SQL
  execute immediate 'select sal from emp where empno=7788'
  --变量赋值
  into v_sal;
  dbms_output.put_line('工资：' || v_sal);
end;

5. 行列转换

5.1. 字符串转换成多行

SELECT id,
       REGEXP_SUBSTR(TEMP_VALUE, '[^,]+', 1, LEVEL) AS split_data
FROM SYSTEM_TEMP where NAME='tender'
CONNECT BY REGEXP_SUBSTR(TEMP_VALUE, '[^,]+', 1, LEVEL) IS NOT NULL
  AND PRIOR id = id
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;