1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| SQL> select * from emp2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NEW_COL COL2
---------- -------------------- --------- ---------- -------- ---------- ---------- ---------- -------------------- --------------------
7369 SMITH CLERK 7902 17/12/80 800 20 SMITH SMITH
7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30 ALLEN ALLEN
7521 WARD SALESMAN 7698 22/02/81 1250 500 30 WARD WARD
7566 JONES MANAGER 7839 02/04/81 2975 20 JONES JONES
7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30 MARTIN MARTIN
7698 BLAKE MANAGER 7839 01/05/81 2850 30 BLAKE BLAKE
7782 CLARK MANAGER 7839 09/06/81 2450 10 CLARK CLARK
7788 SCOTT ANALYST 7566 09/12/82 3000 20 SCOTT SCOTT
7839 KING PRESIDENT 17/11/81 5000 10 KING KING
7844 TURNER SALESMAN 7698 08/09/81 1500 0 30 TURNER TURNER
7876 ADAMS CLERK 7788 12/01/83 1100 20 ADAMS ADAMS
7900 JAMES CLERK 7698 03/12/81 950 30 JAMES JAMES
7902 FORD ANALYST 7566 03/12/81 3000 20 FORD FORD
7934 MILLER CLERK 7782 23/01/82 1300 10 MILLER MILLER
14 rows selected.
SQL>
SQL> create or replace procedure search_val (p_owner varchar2, p_table_name varchar2, p_val varchar2, p_cur out sys_refcursor) as
2 begin
3 open p_cur for
4 WITH t AS (
5 SELECT owner,table_name, column_name
6 FROM ALL_TAB_COLUMNS
7 WHERE owner = p_owner
8 AND table_name = p_table_name
9 )
10 SELECT table_name, column_name,
11 nvl(to_number(extractvalue(
12 xmltype(
13 dbms_xmlgen.getxml ('SELECT count(*) as cnt
14 FROM '|| owner ||'.'|| table_name ||
15 ' WHERE to_char('||column_name||') = ''' || p_val || '''')),
16 '/ROWSET/ROW/CNT'))
17 ,0) AS nb_val_par_colonne
18 FROM T;
19 end;
20 /
Procedure created.
SQL> var c refcursor
SQL> exec search_val('SKUATAMAD','EMP2','SCOTT',:c);
PL/SQL procedure successfully completed.
SQL> print c;
TABLE_NAME COLUMN_NAME NB_VAL_PAR_COLONNE
------------------------------ ------------------------------ ------------------
EMP2 EMPNO 0
EMP2 ENAME 1
EMP2 JOB 0
EMP2 MGR 0
EMP2 HIREDATE 0
EMP2 SAL 0
EMP2 COMM 0
EMP2 DEPTNO 0
EMP2 NEW_COL 1
EMP2 COL2 1
10 rows selected.
SQL> |
Partager