Oracle 10g学习点滴

安装完Oracle 10g，sql*plus登陆

  "用户名称（U）:"中输入'system'

  "口令（P）:"中输入'manager'

  "主机字符串（H）:" tnsname.ora中配置的服务名(如果是系统默认数据库可以不输入)

  (注1：这个用户名/密码是在安装过程中自己设定的)

  (注2:  如果上述操作抛出没有监听器，则需看自己有没添加监听器，如果添加了在服务中看有没启动)

 

更改scott/tiger权限

1）以sys或system登陆

       sys登陆: conn / as sysdba

       system登陆： conn system/manager

 

      //unlock scott

2)      alter user scott account unlock;

3)  清屏

      clear screen

4）内联接和外联接 表t_user，t_salary

内联接

select t_user.user_name t_salary.salary

from t_user,t_salary

where t_user.user_id = t_salary.user_id

外联接 t_user外连接t_salary

select t_user.user_name t_salary.salary

from t_user,t_salary

where t_user.user_id = t_salary.user_id（+）

5)视图

create or replace view usview

as select u.user u, s.salary

from t_user u, t_salary s

where u.user_id = s.user_id

with read only;

注: 对于使用INSERT, UPDATE,DELETE 这样的DML语句存在一些限制。即使不定义with read only;执行这些操作时也

不一定成功。能否在视图上成功执行INSERT,UPDATE,DELETE语句受视图定义及基表的限制。如视图定义在一个表还是多

个表上，被引用基表列的性质(NULL，NOT NULL）等。

执行drop view usview可删除视图

 

6）spool输出

spool d:/tst.sql

select * from t_user

spool off

 

7）SET TERMOUT ON/OFF 控制是否显示执行SQL语句的输出结果。默认是ON（显示）

     edit d:/tst.sql 会打开d:/tst.sql 能进行编辑

     @d:/tst.sql会执行 tst.sql中的内容

8）如何从sql*blus中退出

     输入 . 即可

     /表示执行完成。

 

9）声明和使用游标

在PL/SQL程序内使用显示游标的步骤:

1）声明游标

2）打开游标

3）从游标中取出行

4）关闭游标

例子：

SET SERVEROUT ON

DECLARE

     --步骤1：声明游标

     CURSOR nextSalaryRow IS

            SELECT * FROM salary

                            ORDER BY xm;

     currentSalary salary%ROWTYPE; --与游标相配的记录变量

BEGIN

     -- 步骤2: 打开游标

     OPEN nextSalaryRow;

 

    -- 步骤3: 取数据

     FETCH nextSalaryRow INTO currentSalary;    

    WHILE nextSalaryRow%FOUND LOOP

         DBMS_OUTPUT.PUT_LINE(currentSalary.xm || '' || currentSalary.jb);

         FETCH nextSalary INTO currentSalary;

    END LOOP;

    --  步骤4: 关闭游标

    CLOSE nextSalaryRow;

END;

/

SET SERVEROUT OFF

显示声明的游标属性

%FOUND FETCH语句至少对应数据库中的一行时，%FOUND属性值为TRUE;否则，%FOUND为FALSE

%NOTFOUND 与上相反

%ISOPEN 在游标打开时，%ISOPEN属性值为TRUE;否则,为FALSE

%ROWCOUNT  显示声明的游标中所取出的行数

 

 使用游标FOR循环例子 

 SET SERVEROUT ON

 DECLARE

       CURSOR salaryRow IS

              SELECT * FROM salary ORDER BY xm;

 BEGIN

 FOR currentSalary IN salaryRow LOOP

        DBMS_OUTPUT.PUT_LINE(currentSalary.xm);

END LOOP;

END;

/

SET SERVEROUT OFF

 

带参数的游标

SET SERVEROUT ON

DECLARE

      CURSOR salaryRow(jbw INTEGER) IS

           SELECT xm,jb FROM salary

      WHERE jb=jbw;

BEGIN

      FOR currentSalary IN salaryRow(1) LOOP

      END LOOP;

END;

/

SET SERVEROUT OFF

 

处理游标的当前行

PL/SQL程序可以使用利用UPDATE或DELETE语句的WHERE条件中特殊的CURRENT OF 子句处理游标当前行。

例：

DECLARE

     CURSOR salaryRow (jbw INTEGER) IS

            SELECT * FROM salary

            WHERE jb = jbw

            FOR UPDATE;

      BEGIN

            FOR currentSalary IN salaryRow(2) LOOP

                   DELETE FROM salary

                   WHERE CURRENT OF salaryRow;

            END LOOP;

       END;

/

注: 必须用FOR UPDATE关键字声明该游标的定义查询，这将迫使Oracle锁定游标结果集的行，直到本事务处理提交或回退为止。

这样可防止其他事务处理更新或删除这些行。

 

10)嵌套表

SET SERVEROUT ON

DECLARE

    --声明INTEGER的一个嵌套表类型

    TYPE integerTable IS TABLE OF INTEGER;

  

    --构造器声明和初始化一个集合

    tempIntegers integerTable:= integerTable(1,50,222);

BEGIN

     FOR i IN 1..3 LOOP

            DBMS_OUTPUT.PUT_LINE('DF'||i||tempIntegers(i));

     END LOOP;

END;

/

SET SERVEROUT OFF

集合方法：

COUNT                                      返回集合中当前元素的数目

DELETE[(x[,y]....)]                      删除一些或所有的集合元素，但不释放这些元素占有的空间

EXISTS(x)                                  如果集合中下标为x的元素存在，则返回TRUE,否则返回FALSE

EXTEND[(x[,y])]                         追加下标为y元素的x个拷贝到集合的末尾。如果不给出y,则追加x个空元素到集合中，如果X和y  

                                                 都不给出，则追加一个空元素

FIRST                                        返回集合中第一个元素的下标

LAST                                         返回集合中最后一个元素下标

LIMIT                                        返回一个可变集合能包含的最大元素数目

NEXT(x)                                    返回集合中下标为x的元素后的元素下标

PRIOR(x)                                  返回集合中下标为x的元素前的元素下标

TRIM(x)                                     去掉集合末尾的x-1个元素

使用如例：

SET SERVEROUT ON

DECLARE

    -- 声明部门表的一个嵌套表类型

    TYPE deptTable IS TABLE OF dept%ROWTYPE

   

    -- 用其构造声明和初始化一个集合

    tempDept:deptTable := deptTable();

 

    --从部门表中取出游标

   CURSOR nextDeptRow IS

          SELECT * FROM dept ORDER BY bm;

 

    -- 定义当前变量

    currentElem INTEGER;

BEGIN

    tempDept.EXTEND(16); --追加16个空元素

    

    --用一个游标来填充tempDept

    FOR currentDept IN nextDeptRow LOOP

           tempDept(nextDeptRow%ROWCOUNT*2):=currentDept;

    END LOOP;

  

    --遍历集合元素

    currentElem:=tempDept.FIRST;

    FOR i IN 1..tempDept.COUNT LOOP

         DBMS_OUTPUT.PUT_LINE(tempDept(currentElem).bm);

         currentElem := tempDept.NEXT(currentElem);

    END LOOP;

 

    --删除空集合元素

    FOR i IN 1..tempDetp.COUNT LOOP

           IF tempDept.(i).bm IS NULL THEN

               tempDept.DELETE(i);

           END IF;

    END LOOP;

END;

/

SET SERVEROUT OFF

 

11)

select   *   from   tab;     --   显示该用户的   表,视图,同义词
select   *   from   all_tables;   --   整个库的