PL/SQL基础1

1.列标题和数据的默认显示格式

SQL> alter session
2  set nls_date_language = 'AMERICAN';
Session altered
SQL> col hiredate for a15;
SQL> col ename for a8;

例：观察不同数据类型的显示方式。

 

SQL> select *
2  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-2-20         1600.00    300.00     30
7521 WARD     SALESMAN   7698 1981-2-22         1250.00    500.00     30
7566 JONES    MANAGER    7839 1981-4-2          2975.00               20
7654 MARTIN   SALESMAN   7698 1981-9-28         1250.00   1400.00     30
7698 BLAKE    MANAGER    7839 1981-5-1          2850.00               30
7782 CLARK    MANAGER    7839 1981-6-9          2450.00               10
7788 SCOTT    ANALYST    7566 1982-12-9         3000.00               20
7839 KING     PRESIDENT       1981-11-17        5000.00               10
7844 TURNER   SALESMAN   7698 1981-9-8          1500.00      0.00     30
7876 ADAMS    CLERK      7788 1983-1-12         1100.00               20
7900 JAMES    CLERK      7698 1981-12-3          950.00               30
7902 FORD     ANALYST    7566 1981-12-3         3000.00               20
7934 MILLER   CLERK      7782 1982-1-23         1300.00               10
1234 ASK      MANAGER       1 2009-8-31           10.00     10.00     10
15 rows selected

2.连接操作符

可使用连接符“||”，用于列与列、列与算术表达式或列与常理之间创建一个字符表达式。

任务：将姓名和工作连接作为整体输出

SQL> select ename || job as "Employee"
2  from emp;
Employee
-------------------
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK
ASKMANAGER
15 rows selected

3.原义字符串

任务：使用原义字符串"is a"连接ename和job，可增强输出的可读性。

SQL> select ename || ' is a ' || job
2  as "Employee Details"
3  from emp;
Employee Details
-------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
WARD is a SALESMAN
JONES is a MANAGER
MARTIN is a SALESMAN
BLAKE is a MANAGER
CLARK is a MANAGER
SCOTT is a ANALYST
KING is a PRESIDENT
TURNER is a SALESMAN
ADAMS is a CLERK
JAMES is a CLERK
FORD is a ANALYST
MILLER is a CLERK
ASK is a MANAGER
15 rows selected

4.显示表结构

可以使用describe命令显示一个表的结构。（列名、数据类型以及是否为空）

SQL> describe dept
Name   Type         Nullable Default Comments 
------ ------------ -------- ------- -------- 
DEPTNO NUMBER(2)                              
DNAME  VARCHAR2(14) Y                         
LOC    VARCHAR2(13) Y                         
 

下午难得有时间看看基础知识，内容虽简单，可刚接触Oracle，认真才是实在，实在才是根本！