Oracle 存储过程语法

已于 2023-05-18 09:10:37 修改
阅读量866 收藏 3
点赞数 1
分类专栏： Oracle 存储过程 文章标签： oracle 数据库 dba
于 2023-05-16 11:36:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhu_fangyuan/article/details/130702104
版权

Oracle 存储过程语法

1. 创建表（测试数据准备）
-- 创建用户表
create table TT_USER
(
  USERID   NUMBER(10),
  USERNAME VARCHAR2(255),
  PASSWORD VARCHAR2(255),
  SEX      VARCHAR2(1)
);

INSERT INTO TT_USER VALUES (101, 'zhang', '111', '1');
INSERT INTO TT_USER VALUES (102, 'han', '222', '2');
INSERT INTO TT_USER VALUES (103, 'hui', '333', '2');
INSERT INTO TT_USER VALUES (104, 'fang', '444', '1');
INSERT INTO TT_USER VALUES (105, 'li', '555', '2');
INSERT INTO TT_USER VALUES (106, 'cheng', '666', '1');
INSERT INTO TT_USER VALUES (107, 'zhao', '777', '0');

select * from TT_USER;


-- 创建学生表
CREATE TABLE student(sids number(11) NOT NULL,
                     sname varchar(10),
                     score float,
                     sex char(1),
                     addr varchar(10));

INSERT INTO student VALUES (101, 'zhang', 98, '2', '苏州');
INSERT INTO student VALUES (102, 'han', 69, '1', '西安');
INSERT INTO student VALUES (103, 'hui', 72, '1', '苏州');
INSERT INTO student VALUES (104, 'fang', 100, '2', '苏州');
INSERT INTO student VALUES (105, 'li', 88, '1', '北京');
INSERT INTO student VALUES (106, 'cheng', NULL, '1', '北京');
INSERT INTO student VALUES (107, 'zhao', NULL, '3', '北京');

select * from student;

-- 创建学生表1
CREATE TABLE student1(sids number(11) NOT NULL,
                      sname varchar(10),
                      score float,
                      sex char(1),
                      sage number(10),
                      addr varchar(10));

INSERT INTO student1 VALUES (101, 'zhang', 98, '2', 15, '苏州');
INSERT INTO student1 VALUES (102, 'han', 69, '1', 30, '西安');
INSERT INTO student1 VALUES (103, 'hui', 72, '1', 25, '苏州');
INSERT INTO student1 VALUES (104, 'fang', 100, '2', 35, '苏州');
INSERT INTO student1 VALUES (105, 'li', 88, '1', 45, '北京');
INSERT INTO student1 VALUES (106, 'cheng', NULL, '1', 12, '北京');
INSERT INTO student1 VALUES (107, 'zhao', NULL, '3', 23, '北京');
select * from student1;

-- 创建用户表
CREATE TABLE user_info(
       user_id number(11) NOT NULL,
       user_name varchar(10),
       user_password varchar(20),
       date_time date,
       create_time timestamp,
       update_time timestamp
      );
-- drop table user_info;
                      
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(8,'李一','123456',to_date('2023-01-02','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(2,'王五','123456',to_date('2015-12-20','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(3,'田七','123456',to_date('2018-09-22','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(4,'赵八','123456',to_date('2021-10-21','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(5,'王二','123456',to_date('2020-05-20','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(6,'李四','123456',to_date('2022-05-16','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(7,'赵云','123456',to_date('2023-12-23','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);
insert into USER_INFO (user_id,user_name,user_password,date_time,create_time,update_time) values(8,'黄忠','123456',to_date('2017-12-28','yyyy-mm-dd'),SYSTIMESTAMP,SYSTIMESTAMP);

select * from user_info;
2. 创建存储过程
2.1 MyDemo01 - 无参传递实例

-- 无参数传递，打印信息
create or replace procedure MyDemo01 is
begin
  dbms_output.put_line('hello word, my name is stored procedure');
end;

-- 调用存储过程操作(sql窗口)
call MyDemo01();
2.2 myDemo02 - 有参传递实例1

-- 有参数传递 输入参数 in
create or replace procedure myDemo02(name in varchar,age in int) is
begin
  dbms_output.put_line('name='||name||', age='||age);
end MyDemo02;

-- 调用存储过程操作
call MyDemo02('zhu',20);
2.3 myDemo03 - 有参传递实例2

-- 有参数传递 输出参数 out
create or replace procedure myDemo03(name out varchar,age in int) is
begin
    dbms_output.put_line('age='||age);
    select 'zhu' into name from dual;
end;

-- 调用存储过程操作
declare
   name varchar(10);
   age int;
begin
   myDemo03(name=>name,age=>25);
   dbms_output.put_line('name='||name);
end;

myDemo03 输出结果：

在这里插入图片描述

注：in 表示输入参数；out 表示输出参数

2.4 myDemo04 - 异常处理存储过程

-- 异常处理 exception
create or replace procedure myDemo04
as
age int;
begin
  age:=1/0;
  dbms_output.put_line(age);
  --异常
  exception when others then
    dbms_output.put_line('error');
end;

-- 调用存储过程操作
call myDemo04();
-- 程序分析：程序出现异常，将打印 error
2.5 myDemo05 - while 循环存储过程实例

-- while 循环
create or replace procedure myDemo05
as
  n_count number := 0;
begin
  while n_count < 5 loop
    dbms_output.put_line(n_count);
    n_count := n_count + 1;
  end loop;
end;

-- 调用存储过程操作
call myDemo05();

输出结果：

​ 0
​ 1
​ 2
​ 3
​ 4

2.6 myDemo06 - for 循环存储过程实例
-- for 循环
create or replace procedure myDemo06
as
begin
 FOR USE in (select * from student1) loop
     if (USE.sids<108) then
        dbms_output.put_line(USE.sname);
      end if;
  end loop;
end;

-- 调用存储过程操作
begin
  myDemo06;
end;

输出结果：

​ aaa
​ 李太白
​ 战三
​ 张三
​ 李四
​ 王五
​ 赵六
​ 朱八

2.7 mydemo07 - 增删改查存储过程实例
-- 增删改
create or replace procedure mydemo07(sidss in int, sname in varchar,score in int, sex in char,sage in int,addr in varchar)
as
begin
     --insert into student1 VALUES(sids,sname,score,sex,sage,addr);
     UPDATE  student1 t SET t.sname='狗蛋' WHERE t.sids=sidss ;
     --DELETE  student1 t WHERE t.sids=sids ; 
    commit; --提交
end MyDemo07;

-- 调用存储过程操作
begin
mydemo07(101, '', 120, '1', 20, '');
end;
2.8 myDemo08 - if 语句
-- 根据用户id判断用户是否存在
create or replace procedure myDemo08(pid in number,ret out number)
as
flag number;
begin
  select count(1) into flag from student1 where sids = pid;
  if flag = 1 then
    dbms_output.put_line(pid||'用户存在');
    ret:=1; -- 如果用户存在就把ret设为1
  else
    dbms_output.put_line(pid||'用户不存在');
    ret:=0; -- 如果用户不存在就把ret设为0
  end if;
end;

-- 测试 myDemo08 存储过程
declare
 input number(11):=101;
 ret number; -- 声明一个变量ret，类型是number，用来存储过程的输出值
begin
  MyDemo08(input,ret); -- 获取到过程的输出值存储在ret中
  dbms_output.put_line('存储过程返回值：' ||ret);
end;

输出结果：

​ 101用户存在
​ 存储过程返回值：1

3. 存储过程常用语法实例
-- when
declare 
  i int:= 0;
begin
  loop
    i:=i+1;
    dbms_output.put_line(i);
    EXIT WHEN i > 3;
  END LOOP;
end;
-- 输出结果：
            1
            2
            3
            4
        
-- 使用Loop+游标的时候，取游标当中的值，必须重新赋值一遍，要不然会报错。
declare
  cursor user is
    select * from user_info;
  user1 user_info%rowtype;
begin
  open user;
  loop
    fetch user into user1;
    exit when user%notfound;
    dbms_output.put_line('用户名称：' || user1.USER_NAME);
    dbms_output.put_line('用户年龄：' || user1.user_id);
  end loop;
  close user; --关闭游标
end;
-- 输出结果：
            用户名称：李六
            用户年龄：1
            用户名称：李一
            用户年龄：8
            用户名称：王五
            用户年龄：2
            用户名称：田七
            用户年龄：3
            用户名称：赵八
            用户年龄：4
            用户名称：王二
            用户年龄：5
            用户名称：李四
            用户年龄：6
            用户名称：赵云
            用户年龄：7
            用户名称：黄忠
            用户年龄：8
-- WHILE语句:在执行之前，首先要判断条件表达式的值是否为true，true则执行循环体，否则退出WHILE循环，继续执行循环后面的代码。
declare
  i int := 0;
begin
  while i < 3 loop
    i := i + 1;
    dbms_output.put_line(i);
  end loop;
end;

-- 实例测试
declare
  cursor user is
    select * from user_info;
  user1 user_info%rowtype;
begin
  open user;
  fetch user into user1;
  while(user%found)loop
  dbms_output.put_line('用户名称：' || user1.USER_NAME);
  fetch user into user1;
  end loop;
end;
-- 注意：%found %notfound 用法

--FOR语句是一个可提前设置循环次数的循环控制语句，它有一个循环计数器，通常是一个整型变量，通过这个计数器来控制循环次数
for A IN (reverse) B...C LOOP
   D;
 END LOOP;

--A: 表示一个变量，通常为证书类型，用来作为计数器，默认值是递增的，当循环当中使用reverse关键字时，就会循环递减。
--B: 计数器下线值，当计数器的值小于下限值的时候，终止循环。
--C: 计数器上线值，当计数器的值大于上限值的时候，终止循环。
--D: 循环体。

declare 
  i int := 0;
begin
  for i IN reverse 1..5 LOOP
    dbms_output.put_line(i);
  END LOOP;
end;

-- 配合游标实例测试
declare
  cursor user is
    select * from user_info;
begin
  for user1 in user loop
  dbms_output.put_line('用户名称：'|| user1.USER_NAME);
  dbms_output.put_line('用户ID：'|| user1.USER_ID);
  end loop;
end;

游标实例测试 - 输出结果：
在这里插入图片描述

二师弟&
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle 存储过程的基本语法
东方咖啡屋
06-23 2100
oracle 存储过程的基本语法  1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字(    参数1 IN NUMBER,    参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGINEND 存储过程名字2.SELECT INTO STATEMENT  将select查询的结果存入到变量中，可
Oracle存储过程
猪之仆的博客
07-04 5778
使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%TYPE是非常好的编程风格，因为引用型变量更加灵活。
oracle存储过程详解
qq_31874627的博客
03-22 1497
在Oracle中，创建带参数的存储过程通常涉及到定义输入（IN）、输出（OUT）或输入输出（IN OUT）参数，并在存储过程的主体中使用这些参数。以下是一个简单的Oracle存储过程示例，它接受一个输入参数（一个数字），计算这个数字的平方，并通过一个输出参数返回结果。Oracle SQL Developer提供了一个强大的调试器，允许你逐步执行存储过程代码，查看变量值，设置断点等。这有助于增强代码的可维护性和可读性。游标的使用通常会增加存储过程的复杂性和执行时间，因此应谨慎使用，特别是在处理大量数据时。
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
Jay的个人博客
05-11 2567
含 Oracle PL/SQL基本结构、变量和常量、条件和循环控制、存储过程和函数、游标、触发器、PL/SQL包、动态SQL、异常处理、最佳实践
Oracle 数据库（十四）—— Oracle 存储过程、函数
Jsundoku的博客
04-15 2481
存储过程是一种命名的PL/SQL程序块，它既可以没有参数，也可以有若干个输入、输出参数，甚至可以有多个既作输入又作输出的参数，但它通常没有返回值。存储过程被保存在数据库中，它不可以被SQL语句直接执行或调用，只能通过EXECUT命令执行或在PL/SQL程序块内部被调用。 函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。函数的调用是表达式的一部分，而过程的调用是一条PL/SQL语句。
oracle存储过程语法
02-28
Oracle 存储过程语法详解 Oracle 存储过程是 Oracle 数据库中的一种程序单元，用于实现复杂的业务逻辑和数据处理。存储过程可以提高数据库的安全性、可维护性和性能。 1. 创建存储过程的语法 创建存储过程的基本...
oracle存储过程语法.pdf
10-30
Oracle 存储过程语法详解 Oracle 存储过程是一种编程对象，可以在 Oracle 数据库中执行复杂的逻辑操作。下面是 Oracle 存储过程语法的详细解释： 创建存储过程 存储过程的创建语法如下： ```sql CREATE OR ...
Oracle关于存储过程
weixin_42402597的博客
07-28 558
存储过程 优势：可以在任何地方运行，保存到数据库中，执行的效率比只写sql效率高 可以传参（重点） 1． 创建存储过程和函数。 语法： create or replace procedure 存储过程名字 一般写SP_XXXX //创建一个存储过程 is //声明，代替匿名块的declare begin end 存储过程名字; 例题1打出姓名 create or replace procedure SP_TEST1 is v_empno emp.empno%type; be..
oracle中insert into 用法
热门推荐
weixin_46605842的博客
11-29 1万+
在oracle中，insert语句的使用： 1、insert into 表1(列名1,列名2,……) values(值1,值2,……) 2、insert into 表1(列名1,列名2,……) select 列名1,列名2,…… from表2 3、insert all--无条件添加 into 表1 values(列名1,列名2,……) into 表2values(列名1,列名2,……) select 列名1,列名2,……...
Oracle的存储过程
weixin_41697106的博客
05-19 1万+
一、什么是存储过程 存储过程: 百度百科上是这样解释的，存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。 简单的说就是专门干一件事一段sql语句。 可以由数据库自己去调用，也可以由java程序去调用。 在oracle数据库中存储过程是procedure。 二、为什么要写存储过程 1.效率高 存储过程编译一次后，就会存到
oracle使用存储过程插入文件至数据库操作方法
06-20
存储过程插入文件操作方法
oracle 批量插入数据存储过程
06-25
oracle 批量插入数据存储过程。亲测好用。支持 plsql ,toad,等数据库分析软件。主要包括变量的定义，循环及游标的使用等， 亲测好用
Oracle创建INSERT存储过程并调用
li
12-19 1万+
初学数据库，对存储过程一头雾水，上午学习创建简单的数据表，由于运维需要用到数据库存储，之前生产数据库定时存储过程一直失效，于是想自己写一个存储过程，测试下定时存储任务，后续打算用crontab来定时调用存储过程。下午从网上找了一些存储过程的例子，理解改写后往自己的数据库里面insert数据，一直在疯狂报错，简直到了丧心病狂的地步，瞬间觉得自己好水，哈哈。后来经过不懈努力(浪费时间)后，总算可以用，...
Oracle存储过程基本语法
qiang_general的博客
09-08 782
Oracle存储过程基本语法 存储过程 　　CREATE OR REPLACE PROCEDURE 存储过程名 　　IS 　　BEGIN 　　NULL; 　　END; 其中的is表示的后面将跟随一个pl/sql体，begin标识sql方法的开始。 %ROWTYPE用来声明一个和表中记录一样的record,一个记录声明为具有相同类型的数据库行的作法； 如:t_emp emp%rowtype ; %T...
oracel 存储过程
fivestar2009的博客
01-02 469
今天存储过程运行，遇到个情况，就是在测试机上运行ok，但是在生产机上错误，就是yy  ty应该是0，但是却有数据，后来经分析，需要在存储过程中每次游标，需要初始化yy ty存储过程的赋值：=   yy:=null;create or replace procedure proc_cqjysj(v_ny in varchar2) is       ym date; /*月末*/       yc d
oracle 存储过程的一些基本语法
webjlwang
11-24 502
oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字(    参数1 IN NUMBER,    参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGIN END 存储过程名字2.SELECT INTO STATEMENT  将select查询的结果存...
oracle存储过程之insert的使用，含游标的使用
swqqcs的专栏
01-17 4263
body部分： -- 获取指定计划的指定阶段的指定修改次数的计划类型编号和名称 by shiwei --变更阶段使用 procedure proc_sy_syjs_getAProChange(proID in jwxt.sy_syjs_audit.FProID%type, historyStag
mybatis#号和$区别
最新发布
qq_44031124的博客
06-29 430
然后，当执行SQL时，MyBatis会使用 "PreparedStatement 的 setXXX()方法"来设置参数值，"#{}"：MyBatis会使用预编译的SQL语句，并为每个参数，设置相应的占位符（通常是"?这种方式，可以有效地防止SQL注入攻击，因为，参数值不会被解析为SQL的一部分。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值