SQL*Plus和PL/SQL

1. SQL*Plus登陆：在cmd中，使用sqlplus [usernam]/[pwd]或者sqlplus [usernam]/[pwd] as sysdba或者sqlplus [usernam]/[pwd]@[netsvr]，netsvr是已定义的网络服务名。

2. 获得帮助索引：help index。获得命令的详细信息：help 命令。查询SQLPlus的所有设置信息：show all。显示某个具体的设置：show 关键字。修改SQLPlus的设置信息：set 参数 参数值

3. 执行SQLPlus缓冲区里的命令：/和run。run在执行前会先显示缓冲区里的内容。

4. 编辑SQLPlus缓冲区里的内容：ed或edit。

5. 显示SQLPlus缓冲区里的内容：l或list。

6. 对操作系统文件进行读写：

• 读文件涉及的命令包含@，get，start等命令。

@：将指定文本文件的内容读到缓冲区并执行，文本文件可以本地文件也可以是服务器上文件，如果是本地文件：@文件完整的路径，默认的扩展名是.sql。如果是远程文件，必须放在一个web服务器上，以http或ftp方式访问：@http://web服务器/文件名。还可以在启动sqlplus时，将指定文件读入缓冲区并执行：sqlplus [usernam]/[pwd] @文件名。

get：将指定文本文件的内容读到缓冲区但是不执行，然后用/执行。

start和@一样。

• 写文件涉及的命令包含save，spool等命令。

save：将当前缓冲区中的内容写入一个操作系统文件，如果不指定路径，则在当前目录下产生。save 文件名 选项     选项有create，append，replace

spool：将命令的执行结果输出到一个操作系统文件。

7. PL/SQL是Oracle开发的一种编程语言，是对sql的补充，可以定义变量，数据类型，函数和过程，它体现了模块化的设计思想。它是一种结构化的编程语言，程序的基本单元是块，主要块的形式有函数，过程和匿名块。一个PL/SQL块由以下三部分组成：

DECLARE

变量声明部分         (定义变量，类型，游标，子程序，触发器，异常等，被声明的元素在本块范围里有效，这一部分是可选的)

BEGIN

可执行部分            (必不可少)

EXCEPTION

异常处理部分         (可选的)

END

如：

begin

　　dbms_output.put_line(‘hello, world!‘);

end;

8. 变量的定义有2种方式：

变量名 类型 [约束] [default 默认值]

变量名 类型 [约束] [:=初始值]

如：

declare

id integer not null default 10;

name varchar2(20):=‘smith‘;

begin

id:=200;

dbms_output.put_line(‘id:‘||id);

dbms_output.put_line(‘name:‘||name);

end;

在输出变量前，确保sqlplus的输出时打开的，不然得不到输出结果。SQL>set serveroutput on; 然后用/执行。

9. 流控制语句：

• if..then..end if
• if..then..elsif..else..end if
• loop..end loop
• while..loop..end loop
• for 循环变量 in [reverse] 起始值..终止值 loop 循环体 end loop     如果使用reverse，则按照相反(先最后一个)的顺序取集合中的元素 

 10. 存储程序包括存储过程，存储函数，触发器和程序包等。

• 存储过程：create or replace procedure 过程名(参数1，参数2...)

authid current_user|definer 规定存储过程执行时的权限

as 声明部分

begin

可执行部分

exception

异常处理部分

end;

存储过程所有者将execute权限授予用户：grant execute on 过程名 to 用户

删除存储过程：drop procedure 过程名

存储过程执行：execute 过程名(实际参数)

• 存储函数：create or replace function 函数名(参数1，参数2...) return 返回类型

authid current_user|definer 规定函数过程执行时的权限

as 声明部分

begin

可执行部分 return 表达式;

exception

异常处理部分

end;

• 程序包：是一种Oracle数据库对象，它是一组逻辑上相关的数据类型，变量，过程，函数和游标等的集合。程序包被创建后，存储在数据库中，用户可以直接使用包中的数据类型和变量，也可以直接调用包中的过程和函数。程序包有2种形式：用户创建的包和系统预定义的包。

程序包包头的命令语法：create [or replace] package 包名

authid current_user|definer 规定包执行时的权限

as

类型的定义；

变量的定义；

子程序的声明；

游标的声明；

异常的声明；

end；

程序包包体的命令语法：create [or replace]package body 包名 as 游标的实现；子程序的实现；end；

使用包中类型，游标，变量，过程和函数的方法：包名.元素名

系统预定义的程序包有：

• DBMS_OUTPUT 实现基本的输入输出操作
• DBMS_SQL 执行DDL语句
• DBMS_PIPE 用于在两个进程间以管道进行通信
• DBMS_STATS
• DBMS_JOB 管理数据库中的作业
• UTL_FILE 对操作系统文件进行读写操作

11. 游标是一种私有的工作区，用于保存sql语句的执行结果。在执行一条sql语句时，数据库服务器会打开一个工作区，将sql语句结果保存在这里。在Oracle里有2种形式的游标：隐式游标和显示游标，隐式游标是由数据库服务器定义的，显示游标是用户自己定义的。

使用游标处理select语句的步骤：1.声明游标2.打开游标3.逐行取出游标中的行，并分别进行处理(也就是fetch过程)4.关闭游标

在用fetch命令取出游标数据时，可以设想有一个指针，指向游标中的一行数据，当游标刚打开时，指针指向第一行，以后每取出一行，指针就自动指向下一行，直到所有数据都取出为止。在for循环中可以不用fetch命令。如下是一个带参数的游标例子：

DECLARE

CURSOR cur_7(d_no scott.emp.deptno%type, min_sal scott.emp.sal%type)

IS

   select ename,sal,hiredate from scott.emp where deptno=d_no and sal>=min_sal;

   e_count integer:=0;

BEGIN

   FOR e IN cur_7(30,100) LOOP

      dbms_output.put_line(‘姓名:‘||e.ename ||‘工资:‘||e.sal ||‘工作时间:‘||e.hiredate);

      e_count:=e_count+1;

   END LOOP;

   dbms_output.put_line(‘员工总数:‘||e_count);

END;

 12. 触发器是一种特殊的存储过程，它在创建后就存储在数据库中，触发器的特殊性在于它是建立在某个具体的表或视图之上的，而且是自动激发执行的，如果用户在这个表上执行了某个DML操作，UPDATE，INSERT，DELETE，触发器就会执行。它常用于自动完成一些数据库维护工作。

触发器定义：create [or replace] trigger 触发器

    before|after|instead of

    delete|insert|update [of 列名]

    on 表名

    [for each row [when 条件]]

    begin PL/SQL语句；END;

触发器删除：drop trigger 触发器

触发器失效和有效：alter trigger 触发器 disable；alter trigger 触发器 enable

13. 与触发器有关的数据字典：

• user_triggers：存储用户所拥有的触发器
• dba_triggers:  存储管理员所拥有的触发器
• all_triggers: 存储所有的触发器

SQL*Plus和PL/SQL,古老的榕树,5-wow.com