`
jayghost
  • 浏览: 427734 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

PL/SQL语句块基本语法(ORACLE存储过程,函数,包,游标)

 
阅读更多

转:http://bing-zz.iteye.com/blog/1121357

1、  PL/SQL语句块
PL/SQL语句块只适用于Oracle数据库,使用时临时保存在客户端,而不是保存在数据库。
基本语法:
declare
  变量声明、初始化
begin
  业务处理、逻辑代码
exception
  异常捕获
end;
 
变量声明:<变量名>  <类型及长度>  [:=<初始值>]
            例:v_name varchar2(20):=’张三’;
   例:见第3节
2、  循环语句
loop循环语法:
    loop
     exit  when  表达式
    end loop;
while循环语法:
while 表达式 loop
end loop;
for循环语法:
    for  <变量>  in  <变量取值范围(小值..大值,如1..100)> loop
    end loop;
    for循环的变量可不做声明及初始化。
例:见第3节
3、  if判断语句
基本语法:
if  <表达式>  then
…
else  if  <表达式>  then
…
else
…
end  if;
end  if;
例:
declare
  v_identity number(4):=0;
begin
  loop
    if v_identity=1 then
      dbms_output.put_line('v_identity=1');
    else if v_identity=3 then
      dbms_output.put_line('v_identity=3');
    else if v_identity=6 then
      exit;
    else
      dbms_output.put_line('v_identity is not 1 or 3');
    end if;
    end if;
    end if; -- 注意,有多少个if就要有多少个end if结束标志。
    v_identity:=v_identity+1;
  end loop;
exception
  when others then dbms_output.put_line('error!');
end;
/
4、  分支case
基本语法:
case  <变量>
  when  常量  then
…
when  常量  then
…
      else
      …
end case;
例:
declare
  v_number number(4):=3;
  v_string varchar(20):='abc';
begin
  case v_number
    when 1 then
      dbms_output.put_line('v_number is '||1);
    when 2 then
      dbms_output.put_line('v_number is '||2);
    when 3 then
      dbms_output.put_line('v_number is '||3);
  end case;
  case v_string
    when 'ab' then
      dbms_output.put_line('v_string is '||'ab');
    when 'bc' then
      dbms_output.put_line('v_string is '||'bc');
    else -- 缺省匹配
      dbms_output.put_line('v_string is other value');
  end case;
exception
  when others then dbms_output.put_line('error!');
end;
/
5、  异常(exception)
声明异常语法:<异常名>  exception;
抛出异常语法:raise  <异常名>;
捕获异常语法:when  <异常名>  then  异常处理语句;
例:
declare
  v_input varchar2(1):='&throw';-- 动态输入
  v_exception_1 exception; -- 自定义异常
  v_exception_2 exception;
  others exception; -- 系统异常
begin
  if v_input='1' then
    raise v_exception_1; -- 抛出异常
  else if v_input='2' then
    raise v_exception_2;
  else
    raise others;
  end if;
  end if;
exception
  -- 捕获异常
  when v_exception_1 then dbms_output.put_line('throw exception: v_exception_1');
  when v_exception_2 then dbms_output.put_line('throw exception: v_exception_2');
  when others then dbms_output.put_line('throw exception: others');
end;
/
6、  游标(cursor)
声明游标语法:cursor  <游标名>  is  select语句;
声明ref游标语法:<游标名>  is  ref  cursor;
打开游标语法:open  <游标名>;
移动游标并获取数据语法:fetch  <游标名>  into  <用于保存读取的数据的变量的名>;
关闭游标语法:close  <游标名>;
游标属性(游标的属性必须在关闭游标之前):
 %isopen: 判断游标是否打开
 %notfound: 找不到数据时
 %found:
 %rowcount: 返回当前游标已扫描的数据行数量
游标分类:1、显示游标(自定义游标);2、隐示游标(系统游标);3、REF游标
例:
declare
  v_row t_test%rowtype; -- 匹配t_test表中一行所有的数据类型
  cursor v_cur is select * from t_test;-- 声明游标
begin
  open v_cur;-- 打开游标
  loop
    fetch v_cur into v_row;-- 将游标所在行的数据转存到v_row中
    exit when v_cur%notfound; -- 当游标到最后一行时跳出
    dbms_output.put_line('id = '||v_row.t_id||'  name = '||v_row.t_name||'  msg = '||v_row.t_msg);
  end loop;
  close v_cur;-- 关闭游标
exception
  when others then dbms_output.put_line('throw exception: others');
end;
/
-- REF游标 --
create or replace package upk_select_test
as type uc_test is ref cursor; -- 声明ref游标
end upk_select_test;
/
-- 存储过程中调用ref游标,并将查询结果以游标的方式返回
create or replace procedure up_select_test_2
(uc_result out upk_select_test.uc_test)
is
begin
  open uc_result for select * from t_test;
end up_select_test_2;
/
7、  通配类型操作符
%type: 通配某行某列数据类型,如v_name t_test.t_name%type;通配表t_test中的t_name。
%rowtype: 通配一行所有列的数据类型,如 v_row t_test%rowtype;匹配t_test表中一行
所有的数据类型。
8、  存储过程(procedure)
基本语法:
create  procedure  <过程名>(<参数列表,无参时忽略>)
as|is
  变量声明、初始化
begin
  业务处理、逻辑代码
exception
  异常捕获、容错处理
end  <过程名>;
参数:<参数名> in|out|in out  <参数类型,无长度说明> ,如:v_name  varchar2
in:入参
     out:出参
     in out:出入参
注:as|is表示as或is
调用语法:
1)、exec  <过程名>;
2)、execute  <过程名>;
3)、在PL/SQL语句块中直接调用。
例:
create or replace procedure up_wap(v_param1 in out varchar2,v_param2 in out varchar2)
is
v_temp varchar2(20);
begin
  dbms_output.put_line('交换前参数1:'||v_param1||'  参数2:'||v_param2);
  v_temp:=v_param1;
  v_param1:=v_param2;
  v_param2:=v_temp;
  dbms_output.put_line('交换后参数1:'||v_param1||'  参数2:'||v_param2);
exception
  when others then dbms_output.put_line('There is a error when the procedure up_wap executing!');
end up_wap;
/
-- 调用存储过程
declare
    v_param1 varchar2(20):='param1';
    v_param2 varchar2(20):='param2';
begin
  up_wap(v_param1 => v_param1,v_param2 => v_param2);
end;
/
9、  自定义函数(function)
基本语法:
create  function  <函数名>(<参数列表,无参时忽略>)
return  <返回值类型,无长度说明>
as|is
  变量声明、初始化
begin
  业务处理、逻辑代码
  return  <返回的值>;
exception
  异常捕获、容错处理
end  <函数名>;
参数:in  入参
注:只有入参的类型。
在存储过程和自定义函数中的参数的传递(入参和出参)不能使用%type或%rowtype匹配,不能使用空值null,但是存储过程可以返回空值。
例:
create function uf_select_name_by_id_test(v_id in number)
return varchar2
is
v_name t_test.t_name%type;
begin
  select t_name into v_name from t_test where t_id=v_id;
  return v_name;
exception
  when others then dbms_output.put_line('error');
end uf_select_name_by_id_test;
/
select uf_select_name_by_id_test(1) 姓名 from dual;-- select调用
declare --pl/sql语句块调用
  v_name varchar2(20);
begin
  v_name:=uf_select_name_by_id_test(1);
  dbms_output.put_line('name = '||v_name);
end;
/
10、包(package)
封装,可以封装过程(procedure)、函数(function)和变量。
注意,在包(package)中声明的过程(procedure)和函数(function)必须在包的实现体
(package body)中定义实现。
基本语法:
create  package  <包名>
as|is
  变量声明
  存储过程声明
  自定义函数声明
end  <包名>;
/
create  package  <包名,与声明部分一致>
as|is
  存储过程的代码实现
  自定义函数的代码实现
end  <包名>;
/
例:
-- 创建包upk_hello
create or replace package upk_hello
is
  v_hello_world varchar2(20):='hello world'; -- 声明变量
  procedure up_hello_world(v_name in varchar2);-- 声明过程
  function uf_hello_world(v_name in varchar2) return varchar2;-- 声明函数
end upk_hello;
/
-- 实现包(upk_hello)里声明的方法
create or replace package body upk_hello
is
  procedure up_hello_world(v_name in varchar2)
  is
    v_string varchar2(100);
  begin
    v_string:=v_name||' say hello world!';
    dbms_output.put_line(v_string);
  exception
    when others then dbms_output.put_line('error');
  end up_hello_world;
  function uf_hello_world(v_name in varchar2) return varchar2
  is
    v_string varchar2(100);
  begin
    v_string:=v_name||' say hello world!';
    return v_string;
  exception
    when others then dbms_output.put_line('error');
  end uf_hello_world;
end upk_hello;
/
-- 包的调用
declare
  v_msg varchar2(100);
begin
  upk_hello.up_hello_world('bing');
  v_msg:=upk_hello.uf_hello_world('admin');
  dbms_output.put_line(v_msg);
  dbms_output.put_line(upk_hello.v_hello_world);
end;
/

 

分享到:
评论

相关推荐

    Oracle_PLSQL语言基础

    PL/SQL 不是一个独立的产品,他是一个整合到ORACLE服务器和ORACLE工具中的技术,可以把PL/SQL看作ORACLE服务器内的一个引擎,sql语句执行者处理单个的sql语句,PL/SQL引擎处理PL/SQL程序块。当PL/SQL程序块在PL/SQL...

    PLSQL基础word

    PL/SQL 不是一个独立的产品,他是一个整合到ORACLE服务器和ORACLE工具中的技术,可以把PL/SQL看作ORACLE服务器内的一个引擎,sql语句执行者处理单个的sql语句,PL/SQL引擎处理PL/SQL程序块。当PL/SQL程序块在PL/SQL...

    ORACLE和SQL Server的语法区别

    7. 把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。 8. 用 Transact-SQL 过程代替 PL/SQL 过程、函数和包。 9. 把 PL/SQL 触发器转换为 Transact-SQL 触发器。 10. 使用 SET SHOWPLAN 语句,优化...

    PL/SQL Developer8.04官网程序_keygen_汉化

     函数:Create or replace function funcname(参数列表) return 返回值 as PL/SQL语句块  为便于理解,举例如下:  问题:假设有一张表t1,有f1和f2两个字段,f1为number类型,f2为varchar2类型,要往t1里写两条...

    oracle和SQL的语法区别

    7. 把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。 8. 用 Transact-SQL 过程代替 PL/SQL 过程、函数和包。 9. 把 PL/SQL 触发器转换为 Transact-SQL 触发器。 10. 使用 SET SHOWPLAN 语句,...

    Sql Server与Oracle的区别

    要将 Oracle DML 语句和 PL/SQL 程序迁移到 SQL Server 时,请按下列步骤执行: 1. 验证所有 SELECT、INSERT、UPDATE 和 DELETE 语句的语法是有效的。进行任何必要的修改。 2. 把所有外部联接改为 SQL-92 标准外部...

    PL/SQL 基础.doc

    6) PL/SQL: 存储在数据库内运行, 其他方法为在数据库外对数据库访问,只适合ORACLE; 2. PL/SQL 1) PL/SQL(Procedual language/SQL)是在标准SQL的基础上增加了过程化处理的语言; 2) Oracle客户端工具访问Oracle...

    PLSQLDeveloper下载

    其语法结构为: 过程:Create or replace procedure procname(参数列表) as PL/SQL语句块 函数:Create or replace function funcname(参数列表) return 返回值 as PL/SQL语句块 为便于理解,举例如下: 问题:假设...

    Oracle_PLSQL_编程语法详解

    第一章 PL/SQL程序设计简介 第二章 PL/SQL块结构和组成元素 第三章 PL/SQL流程控制语句 第四章 游标的使用 ...第六章 存储过程和函数 第七章 包的创建和应用 第八章 触发器 第九章 ORACLE提供的常用包

    oracle教案(doc)+SQL Reference 10g(chm).rar

    7.3 简单的PL/SQL语句块 114 7.4 语句块的组成 114 7.5 Sql/plus中的变量 114 7.5.1 在变量声明时需要遵守一些基本的规则: 114 7.5.2 PL/SQL中常用的变量类型: 114 7.5.3 变量声明 114 7.5.4 简单变量赋值 114 ...

    精通SQL 结构化查询语言详解

    15.5.2 Oracle中存储过程和函数的管理 第16章 SQL触发器  16.1 触发器的基本概念  16.1.1 触发器简介  16.1.2 触发器执行环境 16.2 SQL Server中的触发器  16.2.1 SQL Server触发器的种类  16.2.2 ...

    精通SQL--结构化查询语言详解

    15.5.2 oracle中存储过程和函数的管理 324 第16章 sql触发器 325 16.1 触发器的基本概念 325 16.1.1 触发器简介 325 16.1.2 触发器执行环境 325 16.2 sql server中的触发器 326 16.2.1 sql server触发器的种类...

    SQL21日自学通

    一个简单的PL/SQL 语句块395 又一个程序398 存储过程包和触发机制403 总结406 问与答407 校练场407 练习407 第19 天TRANSACT-SQL 简介408 目标408 TRANSACT-SQL 概貌408 对ANSI SQL 的扩展408 谁需要使用TRANSACT-...

    PL\SQL语法参考实例

    此压缩文件为一文档集,从基本的建表查询SQL语句到存储过程、游标嵌套以及常用函数的语法都用实例表示出来,易学易用,是不错的PLSQL语法参考,希望对大家对数据库的学习有所帮助~~

    Oracle中游标Cursor基本用法详解

     SELECT语句用于从数据库中查询数据,当在PL/SQL中使用SELECT语句时,要与INTO子句一起使用,查询的 返回值被赋予INTO子句中的变量,变量的声明是在DELCARE中。SELECT INTO语法如下:  SELECT [DISTICT|ALL]{*|...

    ORACLE11G宝典.rar 是光盘里面的内容,书太厚咧没法影印啊

     5.4 SQL语句的执行过程  5.4.1 基本阶段  5.4.2 流程图  5.5 在OEM中查询内存参数与数据字典视图  5.5.1 查询内存参数  5.5.2 在OEM中查询初始化参数  5.5.3 查询数据字典视图  5.6 小结  第6章 ...

    Oraclet中的触发器

    在ORACLE系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块,不过有一点不同的是,触发器是隐式调用的,并不能接收参数。 触发器优点 (1)触发器能够实施的检查和操作比主键和外键约束、...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

     数据查询语言 (Data Query Language, DQL) 是SQL语言中,负责进行数据查询而不会对数据本身进行修改的语句,这是最基本的SQL语句。例如:SELECT(查询)  数据控制语言Data Controlling Language(DCL),用来...

Global site tag (gtag.js) - Google Analytics