oracle,mysql存储过程代码模板大全

创建存储过程

Oracle

CREATE [OR REPLACE] PROCEDURE 过程名 [ (参数 [,参数]) ]  

IS/AS  

    [declaration_section]  

BEGIN  

    executable_section  

[EXCEPTION  

exception_section]  

END

Mysql

DELIMITER //

CREATE  PROCEDURE 过程名 [ (参数 [,参数]) ]  UNSIGNED [characteristics]

BEGIN  

    [declaration_section]

    executable_section  

END

//

characteristics： 指定存储过程特性，包含5种

1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

参数

3种参数类型：

• IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
• OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

参数规范：

输入参数:  i_name

输出参数:  o_name

输入输出参数:  io_name

Oracle:

CREATE OR REPLACE PROCEDURE 过程名 (i_name in varchar2,

i_id in int,

o_resoult out varchar2 ,

io_resout in out  varchar2

)

Mysql :

CREATE  PROCEDURE `mytest`.`p_test1`(in i_time  time,

                                  out o_result  varchar(20),

                                  inout io_result varchar(20))

变量

Oracle:

create or replace procedure p_test_sql(i_owner varchar2,i_table varchar2,io_result in out varchar2)

is

   v_sql   varchar2(200);

   v_owner varchar2(200);

   v_table varchar2(200);

begin

  DBMS_OUTPUT.ENABLE(buffer_size => null);

  v_owner :=’121’;

v_table :=’121’;

  select i_owner,i_table,i_owner||i_table into v_owner , v_table,io_result from dual;

end;

Mysql:

CREATE  PROCEDURE `mytest`.`p_test2`(in i_name  varchar(20),

                                    out o_result  varchar(20),

                                    inout io_result varchar(20))

begin

declare v_name varchar(20);

set v_name='adf';

set io_result='sdf';

set o_result='sd';

select i_name into v_name from dual;

select v_name;

end

动态sql

Oracle:

在PL/SQL运行DDL语句

execute immediate 'set role all';

v_sql='set role all';

execute immediate v_sql;

给动态语句传值(USING 子句)

declare

v_depnam varchar2(20) := 'testing';

v_loc varchar2(10) := 'Dubai';

Begin

-- 方法一

execute immediate 'insert into dept values (:1, :2, :3)'

using 50,v_depnam, v_loc;

-- 方法二

execute immediate 'insert into dept values ('||50||','''|| v_depnam||''','''||v_loc||''')';

commit;

end;

从动态语句检索值(INTO子句)

declare

v_cnt varchar2(20);

begin

execute immediate 'select count(1) from emp'

into v_cnt;

dbms_output.put_line(v_cnt);

end;

注意单引号的处理，在字符串的’’为’.

Mysql:

  set @v_sql=concat('select\ '',i_name,''' into @v_name from dual;');

  set o_result=@v_name;

  PREPARE STMT FROM @v_sql;

  EXECUTE STMT;

  DEALLOCATE PREPARE STMT;

异常

Oracle

declare

  v_n1        number(2) := 9;

  v_n2        number(2) := 0;

  v_ErrorCode NUMBER;

  v_ErrorText VARCHAR2(200);

  result      number;

begin

  result := v_n1 / v_n2;

  dbms_output.put_line(result);

exception

  when zero_divide then

    v_ErrorCode := SQLCODE;

    v_ErrorText := SUBSTR(SQLERRM, 1, 200);

    dbms_output.put_line(v_ErrorCode || '::' || v_ErrorText);

end;

常见的异常：

No_data_found

Too_many_rows

Dup_val_on_index

zero_divide

Value_error

Storage_error

Case_not_found

如果不知道是什么异常就是others

Mysql

BEGIN

    DECLARE i INT DEFAULT 3;

    DECLARE done INT DEFAULT FALSE;

   

    retry:

     REPEAT

          BEGIN

            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

              BEGIN

                SET done = TRUE;

                SET o_error = '表不存在';

              END;

            IF done OR i < 0 then

            LEAVE retry;

            END IF;

            SET i = i - 1;

            INSERT INTO t1 (int_col) VALUES(value);-- 有问题的sql

            select i;

         END;

     UNTIL FALSE END REPEAT;END

常见的异常

SQLWARNING

NOT FOUND

SQLEXCEPTION

注释

create or replace procedure p_test_sql(i_owner varchar2,-- 参数描述

                                i_table varchar2,-- 参数描述

                                io_result in out varchar2-- 参数描述

                                )

/*

作者：

创建时间:

存储过程描述:

版本:

修改时间：

修改内容描述:

*/

is

   v_sql   varchar2(200); --变量描述

   v_owner varchar2(200); --变量描述

   v_table varchar2(200); --变量描述

begin

  -- sql功能描述

  select i_owner,i_table,i_owner||i_table into v_owner , v_table,io_result from dual;

  

end;

循环

Oracle

关键字：

loop

end loop;

基础循环

declare

  i int;

begin

  i:=1;

  loop

    dbms_output.put_line(i);

    i:=i+1;

    exit when i>3;

  end loop;

end;

For 循环

declare

  i int;

begin

for i in 1..3

loop

  dbms_output.put_line(i);

end loop;

end;

while循环

declare

  i int;

begin

  i:=1;

while i<=3

loop

  dbms_output.put_line(i);

  i:=i+1;

end loop;

end;

Mysql

while循环

declare i INT;

  SET   i = 0;

  WHILE i < 5 DO

    SELECT i;

    SET   i = i + 1;

  END WHILE;

repeat循环

DECLARE i INT;

  SET i = 0;

  REPEAT

    SELECT i;

SET i = i + 1;

UNTIL i > 10

  END REPEAT;

loop循环

DECLARE i INT;

  SET i = 0;

  lp :

  LOOP

    SELECT i;

    SET i = i + 1;

    IF i > 10 THEN

      LEAVE lp;

    END IF;

  END LOOP;

游标

在数据库中，游标是一种机制，是通过关键字CURSOR的来定义一组查询出来的数据集，可以把查询的数据集存储在内存当中，然后通过游标指向其中一条记录，通过循环游标达到循环数据集的目的。

Oracle

显示游标

游标的状态是通过属性来表示。

%Found ：Fetch语句（获取记录）执行情况True or False。

%NotFound : 最后一条记录是否提取出True or False。

%ISOpen ： 游标是否打开True or False。

%RowCount ：游标当前提取的行数 。

loop循环

declare

  cursor c_row is select level as id, 0 as n from dual connect by level <= 3; --声明游标

  v_n int;

  v_id int;

begin

  DBMS_OUTPUT.ENABLE(buffer_size => null); --设置输出缓存不受限制

  open c_row;

  loop

  fetch c_row into v_n,v_id;

    exit when c_row%notfound;

    dbms_output.put_line('id is:'||v_n||'  n is:'||v_id);

  end loop;

  dbms_output.put_line('count is :'||c_row%RowCount);

  close c_row;

end;

隐式游标

declare

  cursor c_row is select level as id, 0 as n from dual connect by level <= 3; --声明游标

  c c_row%ROWTYPE; --声明游标引用类型

begin

  DBMS_OUTPUT.ENABLE(buffer_size => null); --设置输出缓存不受限制

  for c in c_row loop

    begin

      dbms_output.put_line('id is:'||c.id||'  n is:'||c.n);

      --v_n := c.id / c.n;

    EXCEPTION

      WHEN others THEN

        dbms_output.put_line(SQLCODE || '::' || SQLERRM);

    end;

  end loop;

end;

declare

begin

  for c in (select level as id, 0 as n from dual connect by level <= 3) loop

    begin

      dbms_output.put_line('id is:'||c.id||'  n is:'||c.n);

      --v_n := c.id / c.n;

    EXCEPTION

      WHEN others THEN

        dbms_output.put_line(SQLCODE || '::' || SQLERRM);

    end;

  end loop;

end;

Mysql

loop循环游标

CREATE PROCEDURE mytest.p_test_cur()

BEGIN

declare v_id int;

declare done int default false;

declare c_cur CURSOR for select help_topic_id from mysql.help_topic where help_topic_id<=3 and help_topic_id>0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

open c_cur;

  posLoop:LOOP

  FETCH c_cur into v_id;

    IF  done THEN

      LEAVE posLoop;

    END IF;

    select v_id;

  END LOOP posLoop;

CLOSE c_cur;

END

while循环游标

CREATE PROCEDURE mytest.p_test_cur_1()

begin

declare v_id int;

declare done int default false;

declare c_cur cursor for select help_topic_id from mysql.help_topic where help_topic_id<=3 and help_topic_id>0;

declare continue HANDLER for not found set done = true;

open c_cur;

  fetch c_cur into v_id;

  while(not done) do

    fetch c_cur into v_id;

    select v_id;

  end while;

close c_cur;

END

repeat循环游标

CREATE DEFINER=`root`@`%` PROCEDURE `mytest`.`p_test_cur_2`()

BEGIN

declare v_id int;

declare done int default false;

declare c_cur cursor for select help_topic_id from mysql.help_topic where help_topic_id<=3 and help_topic_id>0;

declare continue HANDLER for not found set done = true;

open c_cur;

  repeat

    fetch c_cur into v_id;

    if not done then

      select v_id;

    end if;

  until done end repeat;

  close c_cur;

END

临时表

Oracle

on commit delete rows; 说明数据行只有在当前事务中可见，也是默认值，事务提交后数据行将消失；创建的是事务级临时表。

on commit preserve rows; 说明数据行仅在当前会话中可见；创建的是会话级临时表。

declare

v_table_n int;

v_sql varchar2(1000);

c_cur SYS_REFCURSOR;

v_n int;

v_id VARCHAR2(100);

begin

  select count(*) into v_table_n from   user_tables  where table_name =upper('t_temp');

  if v_table_n=0 then

    EXECUTE IMMEDIATE 'create global temporary  table t_temp (

      id NUMBER,

      name  VARCHAR2(100)

      ) ';

  end if;

  EXECUTE IMMEDIATE 'insert into t_temp values(1,''a'')';

  EXECUTE IMMEDIATE 'insert into t_temp values(2,''b'')';

  EXECUTE IMMEDIATE 'insert into t_temp values(3,''4'')';

  --commit;

  v_sql:='select * from t_temp';

  OPEN c_cur FOR v_sql;

  loop

  fetch c_cur into v_n,v_id;

    exit when c_cur%notfound;

    dbms_output.put_line('id is:'||v_n||'  n is:'||v_id);

  end loop;

  dbms_output.put_line('count is :'||c_cur%RowCount);

  close c_cur;

  EXECUTE IMMEDIATE 'drop table t_temp';

end;

Mysql

CREATE PROCEDURE mytest.p_temp_table()

begin

   drop table if exists t_temp;

   create temporary table if not exists  `t_temp` (

  `id` int,

  `name` varchar(100)

   ) ;

  

  insert into t_temp values(1,'a');

  insert into t_temp values(2,'b');

  insert into t_temp values(3,'c');

  

  select * from t_temp;

END