oracle 存储过程中，定义变量之后，使用变量进行 in 条件查询时，会出现查询条件无效的问题表结构表数据解决方法create or replace type strsplit_type is table of varchar2(30000);```sql```sqlcreate or replace function strsplit(para_str varchar2, para_split varchar2 := ',')return strsplit_typep
select * from (select temp1.*, rownum temp2 --查询sql和条件 from (select * from emp t where 1 = 1) temp1 --结束行数=每页数量 * 当
基表数据：创建存储过程：create or replace procedure PRO_LOOP_EMP is --声明游标 v_emp emp%rowtype; --让
select * from table (select str_split('张三,李四', ',') from dual);
创建存储过程：create or replace procedure PRO_TEST_EXCEPTION is --声明游标 V_Result NUMBER;begin V_Result := 2/0; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('除数不能为0');
insert:create or replace trigger TRI_ADD_EMP after insert on emp for each rowdeclare pragma autonomous_transaction;begin insert into emp_copy select :new.empno, :new.ename, :new.job, :new.mgr, :new.hiredate, :new.sal, :new.comm, :new.deptno
复制表结构和数据：create table emp2 as select * from emp;复制表结构：create table emp2 as select * from emp where 1 = 2;
select * from emp t where rownum <= 5 order by dbms_random.value;查询五次的结果：
select str_split('Jack,Andy', ',') from dual;select column_value name from table(str_split('Jack,Andy', ','));
ceil(n) 取大于等于数值n的最小整数floor(n)取小于等于数值n的最大整数
--目标表，更新或者插入此表merge into emp2 a using (select * from emp) b --匹配条件 on (a.empno = b.empno) --匹配时更新目标表 when matched then update set a.sal = b.sal --不匹配时插入到目标表 when not matched
表数据将group by 后重复的数据拼接起来SELECT t.job, wmsys.wm_concat(t.ename) as naems, count(1) FROM emp t GROUP BY t.job;将多行数据转为一行数据select wm_concat(t.ename) names from emp t;
CREATE OR REPLACE TYPE "TYPE_STR_SPLIT" IS TABLE OF VARCHAR2 (4000)CREATE OR REPLACE FUNCTION "STR_SPLIT" (original_str IN VARCHAR2, keyword_str IN VARCHAR2) RETURN type_str_splitIS j INT := 0; i INT := 1; len_one INT := 0; len_two INT := 0;.
Oracle截取字符串进行循环存储过程create or replace procedure PRO_FOR_TEST is v_temp_name varchar2(4000);begin v_temp_name := '杰克、克拉克、托尼、莉莉'; --循环开始 for e in (select column_value from table(strsplit(v_temp_name, '、'))) loop
Copyright © 2005-2024 51CTO.COM 版权所有 京ICP证060544号
