Home>Article> How to write oracle stored procedures

How to write oracle stored procedures

angryTom
angryTom Original
2019-07-24 15:49:48 43132browse

oracle存储过程的写法是【CREATE OR REPLACE PROCEDURE 存储过程名(param1 in type,param2 out type)IS 变量1 类型(值范围);BEGIN select count...】。

How to write oracle stored procedures

推荐教程:oracle教程

1、存储过程的基本语法:

 CREATE OR REPLACE PROCEDURE 存储过程名(param1 in type,param2 out type)   IS   变量1 类型(值范围);   变量2 类型(值范围);     BEGIN     select count(*) into 变量1 from 表名 where 列名=param1;     if (判断条件) then       select 列名 into 变量2 from 表名 where 列名=param1;       DBMS_OUTPUT.put_line('打印信息');     Elsif (判断条件) then       dbms_output.put_line('打印信息');     Else       Raise 异常名 (NO_DATA_FOUND);     End if;   Exception       When others then         Rollback;      END;

2、已命名的异常:

  命名的系统异常               产生原因   ACCESS_INTO_NULL           未定义对象   CASE_NOT_FOUND             CASE中若未包含相应的WHEN,并且没有设置ELSE时   COLLECTION_IS_NULL           集合元素未初始化   CURSER_ALREDAY_OPEN         游标已经打开   DUP_VAL_NO_INDEX           唯一索引对应的列上有重复的列   INVALID_CUSER              在不合法的游标上操作   INVALID_NUMBER             内嵌的SQL语句不能将字符转换为数字   NO_DATA_FOUND             使用select into 未返回行,或应用索引表未初始化   TOO_MANY_ROWS            执行select into 时,结果集超过一行   ZERO_DIVIDE               除数为0   SUBSCRIPT_BEYOND_COUNT       元素下标超过嵌套表或VARRAY的最大值   SUBSCRIPT_OUTSIDE_LIMIT        使用嵌套表或VARRAY时,将下标指定为负数   VALUE_ERROR               赋值时,变量长度不足以容纳实际数量   LOGIN_DENIED               PL/SQL应用程序连接到ORACLE数据库时,提供了不正确的用户名和密码   NO_LOGGED_ON             PL/SQL应用程序在没有连接ORACLE数据库的情况下访问数据   PROGRAM_ERROR             PL/SQL内部问题,可能需要重装数据字典& PL/SQL系统包   ROWTYPE_MISMATCH           宿主游标变量与PL/SQL游标变量的返回类型不兼容   SELF_IS_NULL               使用对象类型时,在NULL对象上调用对象方法   STORAGE_ERROR             运行PL/SQL时,超出内存空间   SYS_INVALID_ID              无效的ROWID字符串   TIMEOUT_ON_RESOURCE         ORACLE在等待资源时超时

3、实际例子

2.1 没有参数的过程

create or replace procedure test_count   is    v_total int;   v_date varchar(20);  begin     select count(*) into v_total from dual;    select to_char(sysdate,'yyyy-mm-dd') into v_date from dual;       DBMS_OUTPUT.put_line('总人数:'||v_total);      DBMS_OUTPUT.put_line('当前时间:'||v_date);     end;

调用方法:

begin     test_count; end;

2.2 仅带传入参数的过程

 create or replace procedure test_count1(v_id in varchar2) as v_name varchar(100); begin   select c_name into v_name from tb_store where c_stono=v_id;   DBMS_OUTPUT.put_line(v_id||'店的名称为:'||v_name);  exception   when no_data_found then dbms_output.put_line('no_data_found'); end;

调用方法:

begin test_count1(11910); end;

2.3 仅带输出参数的过程

create or replace procedure test_count2(v_name out varchar2)   is   begin    select c_name into v_name from tb_store where c_stono='1101';   exception    when no_data_found then dbms_output.put_line('no_data_found');   end;

调用方法:

declare   v_name varchar(200);   begin   test_count2(v_name);   dbms_output.put_line(v_name);   end;

2.4 带输入参数和输出参数的存储过程

  create or replace procedure test_count3(v_id in int,v_name out varchar2)     is      begin        select c_name into v_name from tb_store where c_stono=v_id;      dbms_output.put_line(v_name);     exception      when no_data_found then dbms_output.put_line('no_data_found');   end;

调用方法:

  declare   v_name varchar(200);   begin   test_count3('1101',v_name);   end;

The above is the detailed content of How to write oracle stored procedures. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn