数据库第二章总结

最新推荐文章于 2022-10-07 08:35:44 发布

桑榆207

最新推荐文章于 2022-10-07 08:35:44 发布

阅读量648

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/lanshan1111/article/details/88627464

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一、基础复习
SQL> select * from scott.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 1987/4/19     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 1987/5/23     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
 
14 rows selected
 

 
SQL> 
SQL> declare
  2   EMPNO_1  scott.emp.EMPNO%type;
  3    SAL_1 scott.emp.sal%type;
  4    begin
  5      select scott.emp.empno,scott.emp.sal into  EMPNO_1 , SAL_1  from scott.emp WHERE scott.emp.empno='7369';
  6      dbms_output.put_line('xinzi'||SAL_1);
  7      END;
  8  /
 
PL/SQL procedure successfully completed
 
SQL> SET SERVEROUTPUT ON;
SQL> 
SQL> declare
  2   EMPNO_1  scott.emp.EMPNO%type;
  3    SAL_1 scott.emp.sal%type;
  4    begin
  5      select scott.emp.empno,scott.emp.sal into  EMPNO_1 , SAL_1  from scott.emp WHERE scott.emp.empno='7369';
  6      dbms_output.put_line('xinzi'||SAL_1);
  7      END;
  8  /
 
xinzi800
 
PL/SQL procedure successfully completed
 
SQL> 
SQL>  DECLARE
  2      EMP_1  scott.emp%ROWTYPE;
  3      BEGIN
  4        SELECT * FROM scott.emp INTO EMP_1 FROM  scott.emp;
  5        END;
  6  /
 
DECLARE
    EMP_1  scott.emp%ROWTYPE;
    BEGIN
      SELECT * FROM scott.emp INTO EMP_1 FROM  scott.emp;
      END;
 
ORA-06550: 第 5 行, 第 31 列: 
PL/SQL: ORA-00933: SQL 命令未正确结束
ORA-06550: 第 5 行, 第 7 列: 
PL/SQL: SQL Statement ignored
 
SQL> 
SQL> DECLARE
  2      EMP_1  scott.emp%ROWTYPE;
  3      BEGIN
  4        SELECT *  INTO EMP_1 FROM  scott.emp;
  5        END;
  6  
  7  /
 
DECLARE
    EMP_1  scott.emp%ROWTYPE;
    BEGIN
      SELECT *  INTO EMP_1 FROM  scott.emp;
      END;
 
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在 line 5
 
SQL> 
SQL>  DECLARE
  2      EMP_1  scott.emp%ROWTYPE;
  3      BEGIN
  4        SELECT *  INTO EMP_1 FROM  scott.emp WHERE scott.emp.empno='7369' ;
  5        END;
  6  /
 
PL/SQL procedure successfully completed
 
SQL> SELECT * FROM EMP_1 ;
 
SELECT * FROM EMP_1
 
ORA-00942: 表或视图不存在
 
SQL> 
SQL> declare
  2   EMPNO_1  scott.emp.EMPNO%type;
  3    SAL_1 scott.emp.sal%type;
  4    begin
  5      select scott.emp.empno,scott.emp.sal into  EMPNO_1 , SAL_1  from scott.emp WHERE scott.emp.empno='7369';
  6      dbms_output.put_line('xinzi'||SAL_1);
  7      END;
  8  /
 
xinzi800
 
PL/SQL procedure successfully completed
 
SQL> 
SQL>     DECLARE
  2      EMP_1  scott.emp%ROWTYPE;
  3      BEGIN
  4        SELECT *  INTO EMP_1 FROM  scott.emp WHERE scott.emp.empno='7369' ;
  5        END;
  6  /
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> DECLARE
  2        V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3        V_ZJ NUMBER(6);
  4        v_empno scott.emp.empno%type;
  5        begin
  6           v_empno =&&a1;
  7           select deptno into  V_DEPTNO from scott.emp where empno=v_empno ;
  8           end;
  9  /
 
DECLARE
      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
      V_ZJ NUMBER(6);
      v_empno scott.emp.empno%type;
      begin
         v_empno =7096;
         select deptno into  V_DEPTNO from scott.emp where empno=v_empno ;
         end;
 
ORA-06550: 第 7 行, 第 18 列: 
PLS-00103: 出现符号 "="在需要下列之一时：
 := . ( @ % ;
符号 ":=在 "=" 继续之前已插入。
 
SQL> 
SQL>  DECLARE
  2      EMP_1  scott.emp%ROWTYPE;
  3      BEGIN
  4        SELECT *  INTO EMP_1 FROM  scott.emp WHERE scott.emp.empno='7369' ;
  5        END;
  6  
  7  
  8  
  9        DECLARE
 10        V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
 11        V_ZJ NUMBER(6);
 12        v_empno scott.emp.empno%type;
 13        begin
 14           v_empno :=&&a1;
 15           select deptno into  V_DEPTNO from scott.emp where empno=v_empno ;
 16           end;
 17  
 18  /
 
DECLARE
    EMP_1  scott.emp%ROWTYPE;
    BEGIN
      SELECT *  INTO EMP_1 FROM  scott.emp WHERE scott.emp.empno='7369' ;
      END;



      DECLARE
      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
      V_ZJ NUMBER(6);
      v_empno scott.emp.empno%type;
      begin
         v_empno :=7096;
         select deptno into  V_DEPTNO from scott.emp where empno=v_empno ;
         end;
 
ORA-06550: 第 10 行, 第 7 列: 
PLS-00103: 出现符号 "DECLARE"
 
SQL> 
SQL>  DECLARE
  2        V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3        V_ZJ NUMBER(6);
  4        v_empno scott.emp.empno%type;
  5        begin
  6           v_empno :=&&a1;
  7           select deptno into  V_DEPTNO from scott.emp where empno=v_empno ;
  8           end;
  9  
 10  /
 
DECLARE
      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
      V_ZJ NUMBER(6);
      v_empno scott.emp.empno%type;
      begin
         v_empno :=7096;
         select deptno into  V_DEPTNO from scott.emp where empno=v_empno ;
         end;
 
ORA-01403: 未找到任何数据
ORA-06512: 在 line 8
 
SQL> 
SQL>   DECLARE
  2        V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3        V_ZJ NUMBER(6);
  4        v_empno scott.emp.empno%type;
  5        begin
  6           v_empno :=&&a2;
  7           select deptno into  V_DEPTNO from scott.emp where empno=v_empno ;
  8           end;
  9  /
 
DECLARE
      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
      V_ZJ NUMBER(6);
      v_empno scott.emp.empno%type;
      begin
         v_empno :=7011;
         select deptno into  V_DEPTNO from scott.emp where empno=v_empno ;
         end;
 
ORA-01403: 未找到任何数据
ORA-06512: 在 line 8
 
SQL> 
SQL>   DECLARE
  2        V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3        V_ZJ NUMBER(6);
  4        v_empno scott.emp.empno%type;
  5        begin
  6           v_empno :=&&a3;
  7           select deptno into  V_DEPTNO from scott.emp where empno=v_empno ;
  8           end;
  9  /
 
PL/SQL procedure successfully completed
 
SQL> create public synonym emp for scott.emp;
 
Synonym created
 
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/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 1987/4/19     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 1987/5/23     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
 
14 rows selected
二、IF-ELSE 
SQL> select * from xs;
 
select * from xs
 
ORA-00942: 表或视图不存在
 
SQL> 
SQL> declare
  2  v_sal scott.emp.sal%type;
  3  v_tax scott.emp.sal%type;
  4  begin
  5    select sal into v_sal from scott.emp where empno=7788;
  6    if v_sal>=3000 then v_tax:=v_sal*0.05;
  7    elsif v_sal >=2000 then v_tax:=v_sal*.0.02;
  8    else v_tax:=v_sal*.0.01;
  9    end if;
 10    dbms.output.put_line(v_tax);
 11    end;
 12  /
 
declare
v_sal scott.emp.sal%type;
v_tax scott.emp.sal%type;
begin
  select sal into v_sal from scott.emp where empno=7788;
  if v_sal>=3000 then v_tax:=v_sal*0.05;
  elsif v_sal >=2000 then v_tax:=v_sal*.0.02;
  else v_tax:=v_sal*.0.01;
  end if;
  dbms.output.put_line(v_tax);
  end;
 
ORA-06550: 第 8 行, 第 42 列: 
PLS-00103: 出现符号 ".02"在需要下列之一时：
 * & = - + ; < / > at in is
   mod remainder not rem <an exponent (**)> <> or != or ~= >= <=
   <> and or like LIKE2_ LIKE4_ LIKEC_ between || member
   SUBMULTISET_
符号 "*" 被替换为 ".02" 后继续。
ORA-06550: 第 9 行, 第 23 列: 
PLS-00103: 出现符号 ".01"在需要下列之一时：
 * & = - + ; < / > at in is
   mod remainder not rem <an exponent (**)> <> or != or ~= >= <=
   <> and or like LIKE2_ LIKE4_ LIKEC_ between || member
   SUBMULTISET_
符号 "*" 被替换为 ".01" 后继
 
SQL> 
SQL> declare
  2  v_sal scott.emp.sal%type;
  3  v_tax scott.emp.sal%type;
  4  begin
  5    select sal into v_sal from scott.emp where empno=7788;
  6    if v_sal>=3000 then v_tax:=v_sal*0.05;
  7    elsif v_sal >=2000 then v_tax:=v_sal*0.02;
  8    else v_tax:=v_sal*0.01;
  9    end if;
 10    dbms.output.put_line(v_tax);
 11    end;
 12  /
 
declare
v_sal scott.emp.sal%type;
v_tax scott.emp.sal%type;
begin
  select sal into v_sal from scott.emp where empno=7788;
  if v_sal>=3000 then v_tax:=v_sal*0.05;
  elsif v_sal >=2000 then v_tax:=v_sal*0.02;
  else v_tax:=v_sal*0.01;
  end if;
  dbms.output.put_line(v_tax);
  end;
 
ORA-06550: 第 11 行, 第 3 列: 
PLS-00201: 必须声明标识符 'DBMS.OUTPUT'
ORA-06550: 第 11 行, 第 3 列: 
PL/SQL: Statement ignored
 
SQL> 
SQL> declare
  2  v_sal scott.emp.sal%type;
  3  v_tax scott.emp.sal%type;
  4  begin
  5    select sal into v_sal from scott.emp where empno=7788;
  6    if v_sal>=3000 then v_tax:=v_sal*0.05;
  7    elsif v_sal >=2000 then v_tax:=v_sal*0.02;
  8    else v_tax:=v_sal*0.01;
  9    end if;
 10   DBMS_OUTPUT.PUT_LINE(V_TAX);
 11    end;
 12  /
 
PL/SQL procedure successfully completed
 
SQL> SET STARTSEVER ON;
Cannot SET STARTSEVER
 
SQL> SET SERVEROUTPUT ON;
SQL> 
SQL> declare
  2  v_sal scott.emp.sal%type;
  3  v_tax scott.emp.sal%type;
  4  begin
  5    select sal into v_sal from scott.emp where empno=7788;
  6    if v_sal>=3000 then v_tax:=v_sal*0.05;
  7    elsif v_sal >=2000 then v_tax:=v_sal*0.02;
  8    else v_tax:=v_sal*0.01;
  9    end if;
 10   DBMS_OUTPUT.PUT_LINE(V_TAX);
 11    end;
 12  /
 
150
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> DECLARE
  2  V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  3  V_ZJ NUMBER(4);
  4  V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  5  BEGIN
  6    SELECT SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE
  7    EMPNO=&&V_EMPNO;
  8    IF V_DEPTNO=10 THEN V_ZJ=300;
  9    ELSIF V_DEPTNO=20 THEN V_ZJ=500;
 10    ELSE V_ZJ=700;
 11     END IF;
 12    UPDATE SCOTT.EMP SET SAL=V_ZJ+EMP.SAL WHERE EMPNO=&V_EMPNO;
 13   END;
 14  /
 
DECLARE
V_EMPNO SCOTT.EMP.EMPNO%TYPE;
V_ZJ NUMBER(4);
V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
BEGIN
  SELECT SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE
  EMPNO=10;
  IF V_DEPTNO=10 THEN V_ZJ=300;
  ELSIF V_DEPTNO=20 THEN V_ZJ=500;
  ELSE V_ZJ=700;
   END IF;
  UPDATE SCOTT.EMP SET SAL=V_ZJ+EMP.SAL WHERE EMPNO=10;
 END;
 
ORA-06550: 第 9 行, 第 27 列: 
PLS-00103: 出现符号 "="在需要下列之一时：
 := . ( @ % ;
符号 ":=在 "=" 继续之前已插入。
ORA-06550: 第 10 行, 第 30 列: 
PLS-00103: 出现符号 "="在需要下列之一时：
 := . ( @ % ;
符号 ":=在 "=" 继续之前已插入。
ORA-06550: 第 11 行, 第 12 列: 
PLS-00103: 出现符号 "="在需要下列之一时：
 := . ( @ % ;
符号 ":=在 "=" 继续之前已插入。
 
SQL> 
SQL> DECLARE
  2  V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  3  V_ZJ NUMBER(4);
  4  V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  5  BEGIN
  6    SELECT SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE
  7    EMPNO=&&V_EMPNO;
  8    IF V_DEPTNO=10 THEN V_ZJ:=300;
  9    ELSIF V_DEPTNO=20 THEN V_ZJ:=500;
 10    ELSE V_ZJ=700;
 11     END IF;
 12    UPDATE SCOTT.EMP SET SAL=V_ZJ+EMP.SAL WHERE EMPNO=&V_EMPNO;
 13   END;
 14  /
 
DECLARE
V_EMPNO SCOTT.EMP.EMPNO%TYPE;
V_ZJ NUMBER(4);
V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
BEGIN
  SELECT SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE
  EMPNO=10;
  IF V_DEPTNO=10 THEN V_ZJ:=300;
  ELSIF V_DEPTNO=20 THEN V_ZJ:=500;
  ELSE V_ZJ=700;
   END IF;
  UPDATE SCOTT.EMP SET SAL=V_ZJ+EMP.SAL WHERE EMPNO=10;
 END;
 
ORA-06550: 第 11 行, 第 12 列: 
PLS-00103: 出现符号 "="在需要下列之一时：
 := . ( @ % ;
符号 ":=在 "=" 继续之前已插入。
 
SQL> 
SQL> DECLARE
  2  V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  3  V_ZJ NUMBER(4);
  4  V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  5  BEGIN
  6    SELECT SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE
  7    EMPNO=&&V_EMPNO;
  8    IF V_DEPTNO=10 THEN V_ZJ:=300;
  9    ELSIF V_DEPTNO=20 THEN V_ZJ:=500;
 10    ELSE V_ZJ:=700;
 11     END IF;
 12    UPDATE SCOTT.EMP SET SAL=V_ZJ+EMP.SAL WHERE EMPNO=&V_EMPNO;
 13   END;
 14  
 15  /
 
DECLARE
V_EMPNO SCOTT.EMP.EMPNO%TYPE;
V_ZJ NUMBER(4);
V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
BEGIN
  SELECT SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE
  EMPNO=10;
  IF V_DEPTNO=10 THEN V_ZJ:=300;
  ELSIF V_DEPTNO=20 THEN V_ZJ:=500;
  ELSE V_ZJ:=700;
   END IF;
  UPDATE SCOTT.EMP SET SAL=V_ZJ+EMP.SAL WHERE EMPNO=10;
 END;
 
ORA-01403: 未找到任何数据
ORA-06512: 在 line 7
 
SQL> 
SQL> DECLARE
  2  V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  3  V_ZJ NUMBER(4);
  4  V_DEPTNO1 SCOTT.EMP.DEPTNO%TYPE;
  5  BEGIN
  6    SELECT SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE
  7    EMPNO=&&V_EMPNO1;
  8    IF V_DEPTNO=10 THEN V_ZJ:=300;
  9    ELSIF V_DEPTNO=20 THEN V_ZJ:=500;
 10    ELSE V_ZJ:=700;
 11     END IF;
 12    UPDATE SCOTT.EMP SET SAL=V_ZJ+EMP.SAL WHERE EMPNO=&V_EMPNO1;
 13   END;
 14  
 15  /
 
DECLARE
V_EMPNO SCOTT.EMP.EMPNO%TYPE;
V_ZJ NUMBER(4);
V_DEPTNO1 SCOTT.EMP.DEPTNO%TYPE;
BEGIN
  SELECT SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE
  EMPNO=7788;
  IF V_DEPTNO=10 THEN V_ZJ:=300;
  ELSIF V_DEPTNO=20 THEN V_ZJ:=500;
  ELSE V_ZJ:=700;
   END IF;
  UPDATE SCOTT.EMP SET SAL=V_ZJ+EMP.SAL WHERE EMPNO=7788;
 END;
 
ORA-06550: 第 7 行, 第 32 列: 
PLS-00201: 必须声明标识符 'V_DEPTNO'
ORA-06550: 第 7 行, 第 41 列: 
PL/SQL: ORA-00904: : 标识符无效
ORA-06550: 第 7 行, 第 3 列: 
PL/SQL: SQL Statement ignored
ORA-06550: 第 9 行, 第 6 列: 
PLS-00201: 必须声明标识符 'V_DEPTNO'
ORA-06550: 第 9 行, 第 3 列: 
PL/SQL: Statement ignored
 
SQL> SELECT * FROM SCOTT.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 1987/4/19     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 1987/5/23     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
 
14 rows selected
 
SQL> 
SQL> DECLARE
  2  V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  3  V_ZJ NUMBER(4);
  4  V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  5  BEGIN
  6    SELECT SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE
  7    EMPNO=&&V_EMPNO1;
  8    IF V_DEPTNO=10 THEN V_ZJ:=300;
  9    ELSIF V_DEPTNO=20 THEN V_ZJ:=500;
 10    ELSE V_ZJ:=700;
 11     END IF;
 12    UPDATE SCOTT.EMP SET SAL=V_ZJ+EMP.SAL WHERE EMPNO=&V_EMPNO1;
 13   END;
 14  
 15  /
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> DECLARE
  2  V1 VARCHAR2(10)='HAO';
  3  BEGIN
  4    CASE V1
  5      WHEN 'HAO' THEN DBMS_OUTPUT.put_line('HAOBA');
  6      WHEN 'NO' THEN DBMS_OUTPUT.put_line('DDDD');
  7      ELSE DBMS_OUTPUT.put_line('CHA');
  8      END CASE;
  9      END;
 10  /
 
DECLARE
V1 VARCHAR2(10)='HAO';
BEGIN
  CASE V1
    WHEN 'HAO' THEN DBMS_OUTPUT.put_line('HAOBA');
    WHEN 'NO' THEN DBMS_OUTPUT.put_line('DDDD');
    ELSE DBMS_OUTPUT.put_line('CHA');
    END CASE;
    END;
三、Case
SQL> 
SQL> DECLARE
  2  V1 VARCHAR2(10):='HAO';
  3  BEGIN
  4    CASE V1
  5      WHEN 'HAO' THEN DBMS_OUTPUT.put_line('HAOBA');
  6      WHEN 'NO' THEN DBMS_OUTPUT.put_line('DDDD');
  7      ELSE DBMS_OUTPUT.put_line('CHA');
  8      END CASE;
  9      END;
 10  
 11  /
 
HAOBA
 
PL/SQL procedure successfully completed
 
SQL> DECLARE
  2  
  2     DECLARE
  3      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  4      V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  5      V_ADD  SCOTT.EMP.SAL%TYPE;
  6      BEGIN
  7         V_EMPNO=&V_EMPNO1;
  8         SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
  9         CASE V_DEPTNO
 10           WHEN 10 THEN V_ADD:=100;
 11           WHEN 20 THEN V_ADD:=200;
 12           WHEN 30 THEN V_ADD:=500;
 13           ELSE V_ADD:=600;
 14           END CASE;
 15            UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 16            END;
 17  /
 
DECLARE
   DECLARE
    V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
    V_EMPNO SCOTT.EMP.EMPNO%TYPE;
    V_ADD  SCOTT.EMP.SAL%TYPE;
    BEGIN
       V_EMPNO=7788;
       SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
       CASE V_DEPTNO
         WHEN 10 THEN V_ADD:=100;
         WHEN 20 THEN V_ADD:=200;
         WHEN 30 THEN V_ADD:=500;
         ELSE V_ADD:=600;
         END CASE;
          UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
          END;
 
ORA-06550: 第 3 行, 第 4 列: 
PLS-00103: 出现符号 "DECLARE"在需要下列之一时：
 begin function package
   pragma procedure subtype type use <an identifier>
   <a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "DECLARE" 后继续。
ORA-06550: 第 8 行, 第 15 列: 
PLS-00103: 出现符号 "="在需要下列之一时：
 := . ( @ % ;
 
SQL> 
SQL>  DECLARE
  2      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3      V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  4      V_ADD  SCOTT.EMP.SAL%TYPE;
  5      BEGIN
  6         V_EMPNO=&V_EMPNO1;
  7         SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
  8         CASE V_DEPTNO
  9           WHEN 10 THEN V_ADD:=100;
 10           WHEN 20 THEN V_ADD:=200;
 11           WHEN 30 THEN V_ADD:=500;
 12           ELSE V_ADD:=600;
 13           END CASE;
 14            UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 15            END;
 16  /
 
DECLARE
    V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
    V_EMPNO SCOTT.EMP.EMPNO%TYPE;
    V_ADD  SCOTT.EMP.SAL%TYPE;
    BEGIN
       V_EMPNO=7788;
       SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
       CASE V_DEPTNO
         WHEN 10 THEN V_ADD:=100;
         WHEN 20 THEN V_ADD:=200;
         WHEN 30 THEN V_ADD:=500;
         ELSE V_ADD:=600;
         END CASE;
          UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
          END;
 
ORA-06550: 第 7 行, 第 15 列: 
PLS-00103: 出现符号 "="在需要下列之一时：
 := . ( @ % ;
符号 ":=在 "=" 继续之前已插入。
 
SQL> 
SQL>  DECLARE
  2      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3      V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  4      V_ADD  SCOTT.EMP.SAL%TYPE;
  5      BEGIN
  6         V_EMPNO:=&V_EMPNO1;
  7         SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
  8         CASE V_DEPTNO
  9           WHEN 10 THEN V_ADD:=100;
 10           WHEN 20 THEN V_ADD:=200;
 11           WHEN 30 THEN V_ADD:=500;
 12           ELSE V_ADD:=600;
 13           END CASE;
 14            UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 15            END;
 16  /
 
PL/SQL procedure successfully completed
 
SQL> 
SQL>     DECLARE
  2      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3      V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  4      V_ADD  SCOTT.EMP.SAL%TYPE;
  5      BEGIN
  6         V_EMPNO:=&V_EMPNO1;
  7         SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
  8         CASE V_DEPTNO
  9           WHEN 10 THEN V_ADD:=100;
 10           WHEN 20 THEN V_ADD:=200;
 11           WHEN 30 THEN V_ADD:=500;
 12           ELSE V_ADD:=600;
 13           END CASE;
 14            DBMS_OUTPUT.put_line( SCOTT.EMP.SAL);
 15            UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 16            DBMS_OUTPUT.put_line( SCOTT.EMP.SAL);
 17            END;
 18  /
 
DECLARE
    V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
    V_EMPNO SCOTT.EMP.EMPNO%TYPE;
    V_ADD  SCOTT.EMP.SAL%TYPE;
    BEGIN
       V_EMPNO:=7788;
       SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
       CASE V_DEPTNO
         WHEN 10 THEN V_ADD:=100;
         WHEN 20 THEN V_ADD:=200;
         WHEN 30 THEN V_ADD:=500;
         ELSE V_ADD:=600;
         END CASE;
          DBMS_OUTPUT.put_line( SCOTT.EMP.SAL);
          UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
          DBMS_OUTPUT.put_line( SCOTT.EMP.SAL);
          END;
 
ORA-06550: 第 15 行, 第 43 列: 
PLS-00357: 在此上下文中不允许表, 视图或序列引用 'SCOTT.EMP.SAL'
ORA-06550: 第 15 行, 第 11 列: 
PL/SQL: Statement ignored
ORA-06550: 第 17 行, 第 43 列: 
PLS-00357: 在此上下文中不允许表, 视图或序列引用 'SCOTT.EMP.SAL'
ORA-06550: 第 17 行, 第 11 列: 
PL/SQL: Statement ignored
 
SQL> 
SQL>  DECLARE
  2      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3      V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  4      V_ADD  SCOTT.EMP.SAL%TYPE;
  5      V_SAL  SCOTT.EMP.SAL%TYPE;
  6      BEGIN
  7         V_EMPNO:=&V_EMPNO1;
  8         SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
  9         CASE V_DEPTNO
 10           WHEN 10 THEN V_ADD:=100;
 11           WHEN 20 THEN V_ADD:=200;
 12           WHEN 30 THEN V_ADD:=500;
 13           ELSE V_ADD:=600;
 14           END CASE;
 15  
 16            UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 17           IF SQL%FOUND THEN
 18             DBMS_OUTPUT.put_line('SUC');
 19             SELECT SAL INTO V_SAL FROM  SCOTT.EMP WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 20            END;
 21  /
 
DECLARE
    V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
    V_EMPNO SCOTT.EMP.EMPNO%TYPE;
    V_ADD  SCOTT.EMP.SAL%TYPE;
    V_SAL  SCOTT.EMP.SAL%TYPE;
    BEGIN
       V_EMPNO:=7788;
       SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
       CASE V_DEPTNO
         WHEN 10 THEN V_ADD:=100;
         WHEN 20 THEN V_ADD:=200;
         WHEN 30 THEN V_ADD:=500;
         ELSE V_ADD:=600;
         END CASE;

          UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
         IF SQL%FOUND THEN
           DBMS_OUTPUT.put_line('SUC');
           SELECT SAL INTO V_SAL FROM  SCOTT.EMP WHERE SCOTT.EMP.EMPNO=V_EMPNO;
          END;
 
ORA-06550: 第 21 行, 第 14 列: 
PLS-00103: 出现符号 ";"在需要下列之一时：
 if
 
SQL> 
SQL> DECLARE
  2      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3      V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  4      V_ADD  SCOTT.EMP.SAL%TYPE;
  5      V_SAL  SCOTT.EMP.SAL%TYPE;
  6      BEGIN
  7         V_EMPNO:=&V_EMPNO1;
  8         SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
  9         CASE V_DEPTNO
 10           WHEN 10 THEN V_ADD:=100;
 11           WHEN 20 THEN V_ADD:=200;
 12           WHEN 30 THEN V_ADD:=500;
 13           ELSE V_ADD:=600;
 14           END CASE;
 15  
 16            UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 17           IF SQL%FOUND THEN
 18             DBMS_OUTPUT.put_line('SUC');
 19             SELECT SAL INTO V_SAL FROM  SCOTT.EMP WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 20            END IF;
 21            END;
 22  
 23  /
 
SUC
 
PL/SQL procedure successfully completed
 
SQL> 
SQL>     DECLARE
  2      V_DEPTNO SCOTT.EMP.DEPTNO%TYPE;
  3      V_EMPNO SCOTT.EMP.EMPNO%TYPE;
  4      V_ADD  SCOTT.EMP.SAL%TYPE;
  5      V_SAL  SCOTT.EMP.SAL%TYPE;
  6      BEGIN
  7         V_EMPNO:=&V_EMPNO1;
  8         SELECT  SCOTT.EMP.DEPTNO INTO V_DEPTNO FROM SCOTT.EMP WHERE SCOTT.EMP.EMPNO= V_EMPNO;
  9         CASE V_DEPTNO
 10           WHEN 10 THEN V_ADD:=100;
 11           WHEN 20 THEN V_ADD:=200;
 12           WHEN 30 THEN V_ADD:=500;
 13           ELSE V_ADD:=600;
 14           END CASE;
 15  
 16            UPDATE SCOTT.EMP SET SCOTT.EMP.SAL=SCOTT.EMP.SAL+V_ADD WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 17           IF SQL%FOUND THEN
 18             DBMS_OUTPUT.put_line('SUC');
 19             SELECT SAL INTO V_SAL FROM  SCOTT.EMP WHERE SCOTT.EMP.EMPNO=V_EMPNO;
 20             DBMS_OUTPUT.put_line(V_SAL);
 21            END IF;
 22            END;
 23  /
 
SUC
4100
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> DECLARE
  2            SCORE INT :=100;
  3            BEGIN
  4              CASE
  5                WHEN SCORE>=90 THEN DBMS_OUTPUT.put_line('YOUXIU');
  6                WHEN  SCORE>=80 THEN DBMS_OUTPUT.put_line('LIANGHAO');
  7                ELSE DBMS_OUTPUT.put_line('CHA');
  8                END CASE;
  9                END;
 10  /
 
YOUXIU
 
PL/SQL procedure successfully completed
 
SQL
SQL> 
SQL> SELECT EMPNO,ENAME,HIREDATE ,
  2                (
  3                CASE
  4                  WHEN TRUNK(SYSDATE-HIREDATE)>360 THEN 'GUOQI'
  5                  WHEN HIREDATE IS NULL THEN 'MEI';
 
SELECT EMPNO,ENAME,HIREDATE ,
              (
              CASE
                WHEN TRUNK(SYSDATE-HIREDATE)>360 THEN 'GUOQI'
                WHEN HIREDATE IS NULL THEN 'MEI'
 
ORA-00905: 缺失关键字
SQL>                 ELSE 'MEIGUOQI';
 
ELSE 'MEIGUOQI'
 
ORA-00900: 无效 SQL 语句
SQL>                 END
  2                  )
  3                  AS SHIFOUGUOQI FROM SCOTT.EMP;
 
END
                )
                AS SHIFOUGUOQI FROM SCOTT.EMP
 
ORA-00900: 无效 SQL 语句
SQL>                 END;
 
END
 
ORA-00900: 无效 SQL 语句
 
SQL> 
SQL> SELECT EMPNO,ENAME,HIREDATE ,
  2                (
  3                CASE
  4                  WHEN TRUNK(SYSDATE-HIREDATE)>360 THEN 'GUOQI'
  5                  WHEN HIREDATE IS NULL THEN 'MEI'
  6                  ELSE 'MEIGUOQI'
  7                  END
  8                  )
  9                  AS SHIFOUGUOQI FROM SCOTT.EMP;
 
SELECT EMPNO,ENAME,HIREDATE ,
              (
              CASE
                WHEN TRUNK(SYSDATE-HIREDATE)>360 THEN 'GUOQI'
                WHEN HIREDATE IS NULL THEN 'MEI'
                ELSE 'MEIGUOQI'
                END
                )
                AS SHIFOUGUOQI FROM SCOTT.EMP
 
ORA-00904: "TRUNK": 标识符无效
 
SQL> 
SQL>     SELECT EMPNO,ENAME,HIREDATE ,
  2                (
  3                CASE
  4                  WHEN TRUNC(SYSDATE-HIREDATE)>360 THEN 'GUOQI'
  5                  WHEN HIREDATE IS NULL THEN 'MEI'
  6                  ELSE 'MEIGUOQI'
  7                  END
  8                  )
  9                  AS SHIFOUGUOQI FROM SCOTT.EMP;
 
EMPNO ENAME      HIREDATE    SHIFOUGUOQI
----- ---------- ----------- -----------
 7369 SMITH      1980/12/17  GUOQI
 7499 ALLEN      1981/2/20   GUOQI
 7521 WARD       1981/2/22   GUOQI
 7566 JONES      1981/4/2    GUOQI
 7654 MARTIN     1981/9/28   GUOQI
 7698 BLAKE      1981/5/1    GUOQI
 7782 CLARK      1981/6/9    GUOQI
 7788 SCOTT      1987/4/19   GUOQI
 7839 KING       1981/11/17  GUOQI
 7844 TURNER     1981/9/8    GUOQI
 7876 ADAMS      1987/5/23   GUOQI
 7900 JAMES      1981/12/3   GUOQI
 7902 FORD       1981/12/3   GUOQI
 7934 MILLER     1982/1/23   GUOQI
 
14 rows selected
 四、循环
SQL>   DECLARE
  2           S NUMBER(4):=1;
  3           N NUMBER(4):=2;
  4           BEGIN
  5             LOOP
  6               S:=S*N;
  7               N:=N+1;
  8               EXIT WHEN N>10;
  9               END LOOP;
 10               DBMS_OUTPUT.put_line(TO_CHAR(S));
 11               END;
 12  /
 
DECLARE
         S NUMBER(4):=1;
         N NUMBER(4):=2;
         BEGIN
           LOOP
             S:=S*N;
             N:=N+1;
             EXIT WHEN N>10;
             END LOOP;
             DBMS_OUTPUT.put_line(TO_CHAR(S));
             END;
 
ORA-06502: PL/SQL: 数字或值错误 :  数值精度太高
ORA-06512: 在 line 7
 
SQL> 
SQL>  DECLARE
  2           S NUMBER :=1;
  3           N NUMBER :=2;
  4           BEGIN
  5             LOOP
  6               S:=S*N;
  7               N:=N+1;
  8               EXIT WHEN N>10;
  9               END LOOP;
 10               DBMS_OUTPUT.put_line(TO_CHAR(S));
 11               END;
 12  /
 
3628800
 
PL/SQL procedure successfully completed
 
SQL> 
SQL>          DECLARE
  2           S NUMBER :=1;
  3           N NUMBER :=2;
  4           BEGIN
  5            WHILE(N<=10)
  6            LOOP
  7              S:=S*N;
  8              N:=N+1;
  9              END LOOP;
 10              DBMS_OUTPUT.put_line(TO_CHAR(S));
 11              END
 12  /
 
DECLARE
         S NUMBER :=1;
         N NUMBER :=2;
         BEGIN
          WHILE(N<=10)
          LOOP
            S:=S*N;
            N:=N+1;
            END LOOP;
            DBMS_OUTPUT.put_line(TO_CHAR(S));
            END
 
ORA-06550: 第 14 行, 第 0 列: 
PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
 ; <an identifier>
   <a double-quoted delimited-identifier>
符号 ";" 被替换为 "end-of-file" 后继续。
 
SQL> 
SQL>          DECLARE
  2           S NUMBER :=1;
  3           N NUMBER :=2;
  4           BEGIN
  5            WHILE N<=10
  6            LOOP
  7              S:=S*N;
  8              N:=N+1;
  9              END LOOP;
 10              DBMS_OUTPUT.put_line(TO_CHAR(S));
 11              END;
 12  /
 
3628800
 
PL/SQL procedure successfully completed
 
SQL> 
SQL>          DECLARE
  2           S NUMBER :=1;
  3           N NUMBER :=2;
  4           BEGIN
  5           for n in 2..10
  6             loop
  7               s:=s*n;
  8               end loop;
  9                DBMS_OUTPUT.put_line(TO_CHAR(S));
 10              END;
 11  
 12  
 13  /
 
3628800
 
PL/SQL procedure successfully completed
 
SQL> 
SQL>          DECLARE
  2           S NUMBER :=1;
  3           N NUMBER :=2;
  4           BEGIN
  5           for n in reverse  1..10
  6             loop
  7               s:=s*n;
  8               end loop;
  9                DBMS_OUTPUT.put_line(TO_CHAR(S));
 10              END;
 11  
 12  /
 
3628800
 
PL/SQL procedure successfully completed
 
SQL> 
SQL>  declare
  2               a:int ;
  3               b:int;
  4               c:int;
  5               i:int;
  6               begin
  7                 for i in 100..999
  8                   loop
  9                 a:=trunc(i/100);
 10                 b:=trunc(i/10)mod10;
 11                 c:=i%10;
 12                 if (i=a*a*a+b*b*b+c*c*c)
 13                    DBMS_OUTPUT.put_line(i);
 14                    end if;
 15                    end loop;
 16                    end;
 17  /
 
declare
             a:int ;
             b:int;
             c:int;
             i:int;
             begin
               for i in 100..999
                 loop
               a:=trunc(i/100);
               b:=trunc(i/10)mod10;
               c:=i%10;
               if (i=a*a*a+b*b*b+c*c*c)
                  DBMS_OUTPUT.put_line(i);
                  end if;
                  end loop;
                  end;
 
ORA-06550: 第 3 行, 第 15 列: 
PLS-00103: 出现符号 ""在需要下列之一时：
 constant exception
   <an identifier> <a double-quoted delimited-identifier> table
   LONG_ double ref char time timestamp interval date binary
   national character nchar
 
SQL> 
SQL>   declare
  2               a int ;
  3               b int;
  4               c int;
  5               i int;
  6               begin
  7                 for i in 100..999
  8                   loop
  9                 a:=trunc(i/100);
 10                 b:=trunc(i/10)mod10;
 11                 c:=i%10;
 12                 if (i=a*a*a+b*b*b+c*c*c)
 13                    DBMS_OUTPUT.put_line(i);
 14                    end if;
 15                    end loop;
 16                    end;
 17  /
 
declare
             a int ;
             b int;
             c int;
             i int;
             begin
               for i in 100..999
                 loop
               a:=trunc(i/100);
               b:=trunc(i/10)mod10;
               c:=i%10;
               if (i=a*a*a+b*b*b+c*c*c)
                  DBMS_OUTPUT.put_line(i);
                  end if;
                  end loop;
                  end;
 
ORA-06550: 第 11 行, 第 30 列: 
PLS-00103: 出现符号 "MOD10"在需要下列之一时：
 . ( * % & = - + ; < / >
   at in is mod remainder not rem <an exponent (**)>
   <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
   between || multiset member SUBMULTISET_
 
SQL> 
SQL>             declare
  2               a int ;
  3               b int;
  4               c int;
  5               i int;
  6               begin
  7                 for i in 100..999
  8                   loop
  9                 a:=trunc(i/100);
 10                 b:=trunc(i/10)mod 10;
 11                 c:=i%10;
 12                 if (i=a*a*a+b*b*b+c*c*c)
 13                    DBMS_OUTPUT.put_line(i);
 14                    end if;
 15                    end loop;
 16                    end;
 17  /
 
declare
             a int ;
             b int;
             c int;
             i int;
             begin
               for i in 100..999
                 loop
               a:=trunc(i/100);
               b:=trunc(i/10)mod 10;
               c:=i%10;
               if (i=a*a*a+b*b*b+c*c*c)
                  DBMS_OUTPUT.put_line(i);
                  end if;
                  end loop;
                  end;
 
ORA-06550: 第 12 行, 第 21 列: 
PLS-00103: 出现符号 "10"在需要下列之一时：
 ( type <an identifier>
   <a double-quoted delimited-identifier>
ORA-06550: 第 14 行, 第 19 列: 
PLS-00103: 出现符号 "DBMS_OUTPUT"在需要下列之一时：
 * & = - + < / > at
   in is mod remainder not rem then <an exponent (**)>
   <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
   between overlaps || multiset year DAY_ member S
ORA-06550: 第 14 行, 第 42 列: 
PLS-00103: 出现符号 ";"在需要下列之一时：
 . ( * % & = - + < / > at in
   is mod remainder not rem then <an exponent (**)>
   <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
   between || member SUBMULTISET_
 
SQL> 
SQL>             declare
  2               a int ;
  3               b int;
  4               c int;
  5               i int;
  6               begin
  7                 for i in 100..999
  8                   loop
  9                 a:=trunc(i/100);
 10                 b:=trunc(i/10)mod 10;
 11                 c:=i mod 10;
 12                 if (i=a*a*a+b*b*b+c*c*c)
 13                    DBMS_OUTPUT.put_line(i);
 14                    end if;
 15                    end loop;
 16                    end;
 17  
 18  /
 
declare
             a int ;
             b int;
             c int;
             i int;
             begin
               for i in 100..999
                 loop
               a:=trunc(i/100);
               b:=trunc(i/10)mod 10;
               c:=i mod 10;
               if (i=a*a*a+b*b*b+c*c*c)
                  DBMS_OUTPUT.put_line(i);
                  end if;
                  end loop;
                  end;
 
ORA-06550: 第 14 行, 第 19 列: 
PLS-00103: 出现符号 "DBMS_OUTPUT"在需要下列之一时：
 * & = - + < / > at
   in is mod remainder not rem then <an exponent (**)>
   <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
   between overlaps || multiset year DAY_ member SUBMULTISET_
符号 "*" 被替换为 "DBMS_OUTPUT" 后继续。
ORA-06550: 第 14 行, 第 42 列: 
PLS-00103: 出现符号 ";"在需要下列之一时：
 . ( * % & = - + < / > at in
   is mod remainder not rem then <an exponent (**)>
   <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_
   between || member SUBMULTISET_
 
SQL> 
SQL> declare
  2               a int ;
  3               b int;
  4               c int;
  5               i int;
  6               begin
  7                 for i in 100..999
  8                   loop
  9                 a:=trunc(i/100);
 10                 b:=trunc(i/10)mod 10;
 11                 c:=i mod 10;
 12                 if (i=a*a*a+b*b*b+c*c*c)
 13                    then DBMS_OUTPUT.put_line(i);
 14                    end if;
 15                    end loop;
 16                    end;
 17  
 18  /
 
153
370
371
407
 
PL/SQL procedure successfully completed
 
SQL>

一、数据文件

1、建立表空间并查询数据文件（利用dba_data_files）

SQL> create tablespace t2 datafile 'D:\a2.dbf' size 5m;

Tablespace created


SQL> select * from dba_data_files;

FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
F:\APP\ASUS\ORADATA\ORCL\USERS01.DBF                                                      4 USERS                             5242880        640 AVAILABLE            4 YES            3435972198    4194302          160    4194304         512 ONLINE
F:\APP\ASUS\ORADATA\ORCL\UNDOTBS01.DBF                                                    3 UNDOTBS1                         99614720      12160 AVAILABLE            3 YES            3435972198    4194302          640   98566144       12032 ONLINE
F:\APP\ASUS\ORADATA\ORCL\SYSAUX01.DBF                                                     2 SYSAUX                          723517440      88320 AVAILABLE            2 YES            3435972198    4194302         1280  722468864       88192 ONLINE
F:\APP\ASUS\ORADATA\ORCL\SYSTEM01.DBF                                                     1 SYSTEM                          734003200      89600 AVAILABLE            1 YES            3435972198    4194302         1280  732954624       89472 SYSTEM
F:\APP\ASUS\ORADATA\ORCL\EXAMPLE01.DBF                                                    5 EXAMPLE                         104857600      12800 AVAILABLE            5 YES            3435972198    4194302           80  103809024       12672 ONLINE
D:\T1.DBF                                                                                 6 TS1                               5242880        640 AVAILABLE            6 NO                      0          0            0    4194304         512 ONLINE
D:\T2.DBF                                                                                 7 TS1                              10485760       1280 AVAILABLE            7 NO                      0          0            0    9437184        1152 ONLINE
D:\T3.DBF                                                                                 8 TS2                              12582912       1536 AVAILABLE            8 NO                      0          0            0   11534336        1408 ONLINE
D:\OK.DBF                                                                                 9 ORAC                             10485760       1280 AVAILABLE            9 NO                      0          0            0    9437184        1152 ONLINE
D:\OK2.DBF                                                                               10 BIGTBS                           20971520       2560 AVAILABLE         1024 NO                      0          0            0   19922944        2432 ONLINE
D:\A1.DBF                                                                                11 T1                                5242880        640 AVAILABLE           11 NO                      0          0            0    4194304         512 ONLINE
D:\A2.DBF                                                                                12 T2                                5242880        640 AVAILABLE           12 NO                      0          0            0    4194304         512 ONLINE

12 rows selected

建立表空间时附带多个数据文件写法

SQL> create tablespace t4 datafile  'D:\a4.dbf' size 5m,'D:\a5.dbf' size 5m;

Tablespace created

然后利用V$DATAFILE查询，似乎显示的数据文件信息只是与上面顺序不同罢了

SQL> SELECT * FROM V$DATAFILE;

     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED    CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME   OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             PLUGGED_IN BLOCK1_OFFSET AUX_NAME                                                                         FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME FOREIGN_DBID FOREIGN_CREATION_CHANGE# FOREIGN_CREATION_TIME PLUGGED_READONLY PLUGIN_CHANGE# PLUGIN_RESETLOGS_CHANGE# PLUGIN_RESETLOGS_TIME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- -------------------- ------------ ------------------------ --------------------- ---------------- -------------- ------------------------ ---------------------
         1                7 2010/3/30 10:          0          1 SYSTEM  READ WRITE            1958607 2019/3/17 21:06                     0                                                      947454         947455 2019/3/1 13  734003200      89600            0       8192 F:\APP\ASUS\ORADATA\ORCL\SYSTEM01.DBF                                                     0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
         2             2160 2010/3/30 10:          1          2 ONLINE  READ WRITE            1958607 2019/3/17 21:06                     0                                                      947454         947455 2019/3/1 13  723517440      88320            0       8192 F:\APP\ASUS\ORADATA\ORCL\SYSAUX01.DBF                                                     0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
         3           944668 2010/3/30 11:          2          3 ONLINE  READ WRITE            1958607 2019/3/17 21:06                     0                                                      947454         947455 2019/3/1 13   99614720      12160            0       8192 F:\APP\ASUS\ORADATA\ORCL\UNDOTBS01.DBF                                                    0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
         4            17981 2010/3/30 10:          4          4 ONLINE  READ WRITE            1958607 2019/3/17 21:06                     0                                                      947454         947455 2019/3/1 13    5242880        640            0       8192 F:\APP\ASUS\ORADATA\ORCL\USERS01.DBF                                                      0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
         5           976600 2019/3/1 13:0          6          5 ONLINE  READ WRITE            1958607 2019/3/17 21:06                     0                                                      976600         976605 2019/3/1 13  104857600      12800    104857600       8192 F:\APP\ASUS\ORADATA\ORCL\EXAMPLE01.DBF                                                    0          8192 NONE                                                                                               0                        4043674800                   948026 2010/3/30 11:10:01    NO                       968202                   947455 2019/3/1 13:02:42
         6          1730315 2019/3/14 15:          7          6 ONLINE  READ WRITE            1958607 2019/3/17 21:06                     0                                                           0              0                5242880        640      5242880       8192 D:\T1.DBF                                                                                 0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
         7          1730319 2019/3/14 15:          7          7 ONLINE  READ WRITE            1958607 2019/3/17 21:06                     0                                                           0              0               10485760       1280     10485760       8192 D:\T2.DBF                                                                                 0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
         8          1731995 2019/3/14 16:          8          8 ONLINE  READ WRITE            1958607 2019/3/17 21:06                     0                                                           0              0               12582912       1536     12582912       8192 D:\T3.DBF                                                                                 0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
         9          1736099 2019/3/14 17:          9          9 ONLINE  READ WRITE            1958607 2019/3/17 21:06                     0                                                           0              0               10485760       1280     10485760       8192 D:\OK.DBF                                                                                 0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
        10          1736493 2019/3/14 17:         10       1024 ONLINE  READ WRITE            1958607 2019/3/17 21:06                     0                                                           0              0               20971520       2560     20971520       8192 D:\OK2.DBF                                                                                0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
        11          1959138 2019/3/17 21:         11         11 ONLINE  READ WRITE            1959139 2019/3/17 21:21                     0                                                           0              0                5242880        640      5242880       8192 D:\A1.DBF                                                                                 0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 
        12          1959880 2019/3/17 21:         12         12 ONLINE  READ WRITE            1959881 2019/3/17 21:31                     0                                                           0              0                5242880        640      5242880       8192 D:\A2.DBF                                                                                 0          8192 NONE                                                                                               0                                 0                        0                       NO                            0                        0 

12 rows selected

2、查询表空间及相应数据文件及信息

SQL> select file_name,tablespace_name,file_id from dba_data_files;

FILE_NAME                                                                        TABLESPACE_NAME                   FILE_ID
-------------------------------------------------------------------------------- ------------------------------ ----------
F:\APP\ASUS\ORADATA\ORCL\USERS01.DBF                                             USERS                                   4
F:\APP\ASUS\ORADATA\ORCL\UNDOTBS01.DBF                                           UNDOTBS1                                3
F:\APP\ASUS\ORADATA\ORCL\SYSAUX01.DBF                                            SYSAUX                                  2
F:\APP\ASUS\ORADATA\ORCL\SYSTEM01.DBF                                            SYSTEM                                  1
F:\APP\ASUS\ORADATA\ORCL\EXAMPLE01.DBF                                           EXAMPLE                                 5
D:\T1.DBF                                                                        TS1                                     6
D:\T2.DBF                                                                        TS1                                     7
D:\T3.DBF                                                                        TS2                                     8
D:\OK.DBF                                                                        ORAC                                    9
D:\OK2.DBF                                                                       BIGTBS                                 10
D:\A1.DBF                                                                        T1                                     11
D:\A2.DBF                                                                        T2                                     12

12 rows selected

3、查询检查点、数据文件等信息

SQL> select file#,name,checkpoint_change# from v$datafile;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
         1 F:\APP\ASUS\ORADATA\ORCL\SYSTEM01.DBF                                                       1958607
         2 F:\APP\ASUS\ORADATA\ORCL\SYSAUX01.DBF                                                       1958607
         3 F:\APP\ASUS\ORADATA\ORCL\UNDOTBS01.DBF                                                      1958607
         4 F:\APP\ASUS\ORADATA\ORCL\USERS01.DBF                                                        1958607
         5 F:\APP\ASUS\ORADATA\ORCL\EXAMPLE01.DBF                                                      1958607
         6 D:\T1.DBF                                                                                   1958607
         7 D:\T2.DBF                                                                                   1958607
         8 D:\T3.DBF                                                                                   1958607
         9 D:\OK.DBF                                                                                   1958607
        10 D:\OK2.DBF                                                                                  1958607
        11 D:\A1.DBF                                                                                   1959139
        12 D:\A2.DBF                                                                                   1959881

12 rows selected

二、日志文件

1、查询日志文件及其信息

SQL> select group#,archived, status from V$LOG;

    GROUP# ARCHIVED STATUS
---------- -------- ----------------
         1 NO       INACTIVE
         2 NO       CURRENT
         3 NO       INACTIVE
        10 NO       ACTIVE
        12 NO       INACTIVE

SQL>

2、查看重做日志组及其成员相关信息

SQL> select group#,member,status from V$LOGFILE;

    GROUP# MEMBER                                                                           STATUS
---------- -------------------------------------------------------------------------------- -------
         3 F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG                                              
         2 F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG                                              
         1 F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG                                              
        10 D:\L1.LOG                                                                        
        10 D:\L2.LOG                                                                        
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA                           
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA                           
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO                           

8 rows selected

3、创建重做日志组时指定group

SQL> alter database add logfile('d:\LOG1.RDO','D:\LOG2.RDO') SIZE 6M;

Database altered

也可以不指定，但注意文件后缀是RDO

4、向重做日志组中添加成员

ALTER DATABASE ADD LOGFILE MEMBER 'D:\LOG3.RDO' TO GROUP 10;

Database altered

注意这时不加SIZE

5、查询重做日志组信息

SQL> SELECT GROUP#,MEMBER FROM V$LOGFILE;

    GROUP# MEMBER
---------- --------------------------------------------------------------------------------
         3 F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG
         2 F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG
         1 F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG
        10 D:\L1.LOG
        10 D:\L2.LOG
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO
         4 D:\LOG1.RDO
         4 D:\LOG2.RDO
        10 D:\LOG3.RDO

11 rows selected

6、删除日志组成员再查询一遍

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'D:\LOG3.RDO';

Database altered


SQL> SELECT GROUP#,MEMBER FROM V$LOGFILE;

    GROUP# MEMBER
---------- --------------------------------------------------------------------------------
         3 F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG
         2 F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG
         1 F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG
        10 D:\L1.LOG
        10 D:\L2.LOG
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO
         4 D:\LOG1.RDO
         4 D:\LOG2.RDO

10 rows selected

7、删除编号为x的重做日志组

SQL> ALTER DATABASE DROP LOGFILE MEMBER 'D:\LOG3.RDO';

Database altered


SQL> SELECT GROUP#,MEMBER FROM V$LOGFILE;

    GROUP# MEMBER
---------- --------------------------------------------------------------------------------
         3 F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG
         2 F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG
         1 F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG
        10 D:\L1.LOG
        10 D:\L2.LOG
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA
        12 F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO
         4 D:\LOG1.RDO
         4 D:\LOG2.RDO

10 rows selected

三、归档日志

Enter user-name: sys as sysdba 利用dba身份登录，再输入密码即可

1、显示归档日志及信息

SQL> ARCHIVE LOG LIST;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     72
Current log sequence           76
SQL>

2、修改归档模式

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3373858816 bytes
Fixed Size                  2180424 bytes
Variable Size            2080377528 bytes
Database Buffers         1275068416 bytes
Redo Buffers               16232448 bytes
SQL> alter database mount;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     72
Next log sequence to archive   76
Current log sequence           76
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     72
Next log sequence to archive   76
Current log sequence           76
SQL>

3、归档模式查询

SQL> select name,log_mode from V$DATABASE;

NAME                        LOG_MODE
--------------------------- ------------------------------------
ORCL                        ARCHIVELOG
或者
SQL> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     72
Next log sequence to archive   76
Current log sequence           76
SQL>

4、查询日志信息

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
F:\APP\ASUS\ORADATA\ORCL\REDO03.LOG
F:\APP\ASUS\ORADATA\ORCL\REDO02.LOG
F:\APP\ASUS\ORADATA\ORCL\REDO01.LOG
F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1C.ORA
F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2C.ORA
F:\APP\ASUS\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG3C.RDO
D:\LOG1.RDO
D:\LOG2.RDO

8 rows selected.

5、查询数据库是否为归档模式

SQL> select dbid,name,log_mode from v$database;

      DBID NAME                        LOG_MODE
---------- --------------------------- ------------------------------------
1529549295 ORCL

四、控制文件

后缀CTL

1、查询控制文件

SQL> select dbid,name,log_mode from v$database;

      DBID NAME                        LOG_MODE
---------- --------------------------- ------------------------------------
1529549295 ORCL                        ARCHIVELOG

SQL> select * from v$controlfile;

STATUS
---------------------
NAME
--------------------------------------------------------------------------------
IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS
--------- ---------- --------------

F:\APP\ASUS\ORADATA\ORCL\CONTROL01.CTL
NO             16384            594


F:\APP\ASUS\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL
NO             16384            594

STATUS
---------------------
NAME
--------------------------------------------------------------------------------
IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS
--------- ---------- --------------


SQL>

五、配置文件

*.ORA为其后缀

。。。。。。

桑榆207

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据库第二章总结

一、基础复习SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH ...
复制链接

扫一扫

专栏目录