PL/SQL基础1

来源：互联网发布：网络电影发行流程编辑：程序博客网时间：2024/05/16 19:39

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

SQL> alter session2  set nls_date_language = 'AMERICAN';Session alteredSQL> 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               207499 ALLEN    SALESMAN   7698 1981-2-20         1600.00    300.00     307521 WARD     SALESMAN   7698 1981-2-22         1250.00    500.00     307566 JONES    MANAGER    7839 1981-4-2          2975.00               207654 MARTIN   SALESMAN   7698 1981-9-28         1250.00   1400.00     307698 BLAKE    MANAGER    7839 1981-5-1          2850.00               307782 CLARK    MANAGER    7839 1981-6-9          2450.00               107788 SCOTT    ANALYST    7566 1982-12-9         3000.00               207839 KING     PRESIDENT       1981-11-17        5000.00               107844 TURNER   SALESMAN   7698 1981-9-8          1500.00      0.00     307876 ADAMS    CLERK      7788 1983-1-12         1100.00               207900 JAMES    CLERK      7698 1981-12-3          950.00               307902 FORD     ANALYST    7566 1981-12-3         3000.00               207934 MILLER   CLERK      7782 1982-1-23         1300.00               101234 ASK      MANAGER       1 2009-8-31           10.00     10.00     1015 rows selected

2.连接操作符

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

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

SQL> select ename || job as "Employee"2  from emp;Employee-------------------SMITHCLERKALLENSALESMANWARDSALESMANJONESMANAGERMARTINSALESMANBLAKEMANAGERCLARKMANAGERSCOTTANALYSTKINGPRESIDENTTURNERSALESMANADAMSCLERKJAMESCLERKFORDANALYSTMILLERCLERKASKMANAGER15 rows selected

3.原义字符串

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

SQL> select ename || ' is a ' || job2  as "Employee Details"3  from emp;Employee Details-------------------------SMITH is a CLERKALLEN is a SALESMANWARD is a SALESMANJONES is a MANAGERMARTIN is a SALESMANBLAKE is a MANAGERCLARK is a MANAGERSCOTT is a ANALYSTKING is a PRESIDENTTURNER is a SALESMANADAMS is a CLERKJAMES is a CLERKFORD is a ANALYSTMILLER is a CLERKASK is a MANAGER15 rows selected

4.显示表结构

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

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

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

0 0