Oracle PL/SQL/System Tables Views/USER VIEWS

Содержание

1 Get all views
2 If view exists, drop view
3 List view text from user_views
4 Query user-defined view info from USER_VIEWS table
5 Query user_views

Get all views

   <source lang="sql">

SQL> SQL> set heading off SQL> set feedback off SQL> set linesize 1000 SQL> set trimspool on SQL> set verify off SQL> set termout off SQL> set embedded on SQL> SQL> select "@getaview " || view_name

 2  from user_views
 3  /

@getaview EMP_PUBLIC_DATA @getaview AQ$EMP_CHANGES_S @getaview VIEW_T @getaview DEPT_EMP_VIEW @getaview DEPARTMENT_10 @getaview AQ$_EMP_CHANGES_F @getaview AQ$EMP_CHANGES @getaview AQ$EMP_CHANGES_R SQL> SQL> SQL> select "@" || view_name

 2  from user_views
 3  /

@EMP_PUBLIC_DATA @AQ$EMP_CHANGES_S @VIEW_T @DEPT_EMP_VIEW @DEPARTMENT_10 @AQ$_EMP_CHANGES_F @AQ$EMP_CHANGES @AQ$EMP_CHANGES_R SQL> SQL> set termout on SQL> set heading on SQL> set feedback on SQL> set verify on SQL> SQL>

 </source>

If view exists, drop view

   <source lang="sql">

SQL> SQL> BEGIN

 2    FOR i IN (SELECT null FROM user_views WHERE view_name = "BOOKS_emp") LOOP
 3      EXECUTE IMMEDIATE "DROP VIEW books_emp";
 4    END LOOP;
 5  END;
 6  /

PL/SQL procedure successfully completed.

 </source>

List view text from user_views

   <source lang="sql">

SQL> SQL> set long 999 SQL> column text format a42 word wrapped SQL> SQL> select view_name, text

 2  from   user_views;

VIEW_NAME TEXT

------------------------------------------

EMP_HQ select "ID","FNAME","LNAME","START_DATE","

                              END_DATE","SALARY","CITY","DESCRIPTION" fr
                              om emp
                              where id = "2"

V select "X" from t AVG_SAL select trunc(avg(salary)) avg_sal

                              from emp

EMPDEPT_V select e.empno

                              ,      e.ENAME
                              ,      e.init
                              ,      d.dname
                              ,      d.location
                              ,      m.ENAME    as MANAGER
                              from   employees   e
                                     join
                                     departments d using (deptno)
                                     join

VIEW_NAME TEXT

------------------------------------------

                                     employees   m on (m.empno = d.mgr)

DEPT_SAL select dname,round(avg(sal),2) avgSalary

                                  from   emp,dept
                                  where  emp.deptno = dept.deptno
                                 group by dname

ALL_ORACLE_ERRORS SELECT "ERRNUMBER","MESSAGE" FROM TABLE(Or

                              acleErrorTable())

INVENTORY_VIE SELECT i.item_id, i.reorder_status, i.pric

                              e
                                   FROM inventory_tbl i

TOP_EMP SELECT empno, ename, sal

                                FROM   emp
                                WHERE  sal > 2999

EMP_BONUS SELECT dname, empno, ename, sal, sal * .15 VIEW_NAME TEXT

------------------------------------------

                               bonus
                                  FROM   emp a, dept b
                                  WHERE  a.deptno = b.deptno

SHARED SELECT shared_id

                                ,        shared_text
                                FROM     shared_all
                                WHERE    NVL(striping_id,0) =
                                           NVL(TO_NUMBER(SUBSTR(USERENV(
                              "CLIENT_INFO"),1,10)),0)

PHONE_LIST select emp_id, firstname || " " || midinit

                               || ". " || lastname as name,"(" || area_c
                              ode || ")" || phone as telephone#
                              from emp

11 rows selected. SQL>

 </source>

Query user-defined view info from USER_VIEWS table

   <source lang="sql">

SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /