3,491
社区成员
发帖
与我相关
我的任务
分享
SQL> set serveroutput on;
SQL>
SQL> DECLARE
2 v_sql VARCHAR2(2000);
3 v_col VARCHAR2(2000);
4 BEGIN
5 v_sql := 'select col1,col2,col3 from test';
6 v_sql := trim(v_sql);
7 v_col := regexp_replace(regexp_replace(regexp_substr(
8 'select col1,col2,col3 from test','^select.+from',1,1,'i')
9 ,'select','',1,1,'i'),'from','',1,1,'i');
10 dbms_output.put_line(v_col);
11 END;
12 /
col1,col2,col3
PL/SQL procedure successfully completed
SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
--构造一个表,和emp表的部分字段相同,但是顺序不同
SQL> create table t_emp as
2 select ename,empno,deptno,sal
3 from emp
4 where 1=0
5 /
Table created
--添加数据
SQL> insert into t_emp(ename,empno,deptno,sal)
2 select ename,empno,deptno,sal
3 from emp
4 where sal >= 2500
5 /
5 rows inserted
SQL> select * from t_emp;
ENAME EMPNO DEPTNO SAL
---------- ----- ------ ---------
JONES 7566 20 2975.00
BLAKE 7698 30 2850.00
SCOTT 7788 20 3100.00
KING 7839 10 5000.00
FORD 7902 20 3000.00
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 3100.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 950.00 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 10
14 rows selected