今天小编给各位分享存储过程语法的知识，其中也会对sql server存储过程语法进行解释，如果能碰巧解决你现在面临的问题，别忘了关注本站，现在开始吧！
oracle存储过程基本语法
一：存储过程
创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限
删除存储过程，是过程的创建者或者拥有DROP ANY PROCEDURE系统权限
修改存储过程，则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人
执行(或调用)存储过程，是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人
1：语法
1）创建
CREATE[ORREPLACE]PROCEDURE存储过程名[(参数[IN|OUT|INOUT] 数据类型…)]
{AS|IS}
[说明部分]
BEGIN
可执行部分
[EXCEPTION
错误处理部分]
END[过程名];
说明：
OR REPLACE 表示如果存在就覆盖存储过程
参数有三种形式：IN、OUT和IN OUT。则默认为IN。
关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。
2）删除存储过程：
DROP PROCEDURE 存储过程名；
3）修改存储过程：
ALTER PROCEDURE 存储过程名 COMPILE；
4）执行存储过程
EXECUTE 模式名.存储过程名[(参数…)];
或
BEGIN
模式名.存储过程名[(参数…)];
END;
另外：参数可以是变量、常量或表达式
要其它用户执行存储过程须要给其它用户授权
GRANT EXECUTE ON 存储过程名 TO 用户名
2：参数说明
IN 定义一个输入参数变量，用于传递参数给存储过程
OUT 定义一个输出参数变量，用于从存储过程获取数据
IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能
1）参数名 IN 数据类型 DEFAULT 值；
定义一个输入参数变量，用于传递参数给存储过程。
可以是常量、有值变量或表达式
DEFAULT 关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值
在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。
2）参数名 OUT 数据类型；
定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。
在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。
在存储过程中，参数变量只能被赋值而不能将其用于赋值，而且必须给输出变量至少赋值一次。
3）参数名 IN OUT 数据类型 DEFAULT 值；
定义一个输入、输出参数变量，兼有以上两者的功能。
在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。
DEFAULT 关键字为可选项，用来设定参数的默认值。
在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。
补充：如果省略IN、OUT或IN OUT，则默认模式是IN。
调用它时参数个数与位置可以不一致，用以下形式调用：
EXECUTE CHANGE_SALARY(P_RAISE=80,P_EMPNO=7788); //=运算符左侧是参数名，右侧是参数表达式
二：函数
创建函数，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限
删除函数，需要是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人
修改函数，需要是函数的创建者或者拥有ALTER ANY PROCEDURE系统权限的人
执行函数，需要是函数的创建者或拥有EXECUTE ANY PROCEDURE系统权限的人
1：语法
1）创建：
CREATE[ORREPLACE]FUNCTION函数名[(参数[IN] 数据类型…)]
RETURN数据类型
{AS|IS}
[说明部分]
BEGIN
可执行部分
RETURN(表达式)
[EXCEPTION
错误处理部分]
END[函数名];
说明：
参数是可选的，但只能是IN类型(IN关键字可以省略)。
在定义部分的RETURN 数据类型，用来表示函数的数据类型，也就是返回值的类型，不可省略。
在可执行部分的RETURN(表达式)，用来生成函数的返回值，其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句，但只有一个RETURN语句会被执行，一旦执行了RETURN语句，则函数结束并返回调用环境。
2）删除
DROP FUNCTION 函数名；
3）修改
ALTER PROCEDURE 函数名 COMPILE；
4）执行
变量名:=函数名(…)
三：对存储过程 和 函数的查看（可以通过对数据字典【USER_SOURCE】的访问来查询存储过程或函数的有关信息）
1：查询某个存储过程序内容
select TEXT from user_source WHERE NAME=’存储过程名’;
2：查看数据字殿
DESCRIBE USER_SOURCE ; //命令行中
3：查看存储过程的参数
DESCRIBE say_hello; //后面是过程名
4：查看发生编辑错误
SHOW ERRORS ;
5：查询一个存储过程或函数是否是有效状态(即编译成功)
SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME=’过程名’;//注意大小写
VALID表示该存储过程有效(即通过编译)，INVALID表示存储过程无效或需要重新编译。它的状态会改变，这与它依赖外部表（表删除修改等操作）有关系
6：查看存储过程与表的依赖关系
SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME=’SAY_HELLO’;
说明
NAME为实体名，TYPE为实体类型，REFERENCED_OWNER为涉及到的实体拥有者账户，REFERENCED_NAME为涉及到的实体名，REFERENCED_TYPE 为涉及到的实体类型。
问题：
如果一个用户A被授予执行属于用户B的一个存储过程的权限，在用户B的存储过程中，访问到用户C的表，用户B被授予访问用户C的表的权限，但用户A没有被授予访问用户C表的权限，那么用户A调用用户B的存储过程是失败的还是成功的呢？答案是成功的。
在SQL中存储过程的一般语法是什么？
1、 创建语法
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
….
]
as
SQL_statements
2、 创建不带参数存储过程
–创建存储过程
if (exists (select * from sys.objects where name = ‘proc_get_student’))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;
–调用、执行存储过程
exec proc_get_student;
3、 修改存储过程
–修改存储过程
alter proc proc_get_student
as
select * from student;
4、 带参存储过程
–带参存储过程
if (object_id(‘proc_find_stu’, ‘P’) is not null)
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go
exec proc_find_stu 2, 4;
5、 带通配符参数存储过程
–带通配符参数存储过程
if (object_id(‘proc_findStudentByName’, ‘P’) is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = ‘%j%’, @nextName varchar(20) = ‘%’)
as
select * from student where name like @name and name like @nextName;
go
exec proc_findStudentByName;exec proc_findStudentByName ‘%o%’, ‘t%’;
扩展资料：
SQL存储过程优点：
1、重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。
2、减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。
3、安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。
参考资料来源：百度百科—存储过程
Oracle 存储过程中的基本语法有哪些
Oracle存储过程基本语法介绍参考下
Oracle存储过程基本语法 存储过程
1 CREATE OR REPLACE PROCEDURE 存储过程名
2 IS
3 BEGIN
4 NULL;
5 END;
行1:
CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它;
行2:
IS关键词表明后面将跟随一个PL/SQL体。
行3:
BEGIN关键词表明PL/SQL体的开始。
行4:
NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;
行5:
END关键词表明PL/SQL体的结束
存储过程创建语法：
create or replace procedure 存储过程名（param1 in type，param2 out type）
as
变量1 类型（值范围）; –vs_msg VARCHAR2(4000);
变量2 类型（值范围）;
复制代码 代码如下:
Begin
Select count(*) into 变量1 from 表A where列名=param1；
If (判断条件) then
Select 列名 into 变量2 from 表A 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;
注意事项：
1， 存储过程参数不带取值范围，in表示传入，out表示输出
类型可以使用任意Oracle中的合法类型。
2， 变量带取值范围，后面接分号
3， 在判断语句前最好先用count（*）函数判断是否存在该条操作记录
4， 用select 。。。into。。。给变量赋值
5， 在代码中抛异常用 raise+异常名
复制代码 代码如下:
CREATE OR REPLACE PROCEDURE存储过程名
(
–定义参数
is_ym IN CHAR(6) ,
the_count OUT NUMBER,
)
AS
–定义变量
vs_msg VARCHAR2(4000); –错误信息变量
vs_ym_beg CHAR(6); –起始月份
vs_ym_end CHAR(6); –终止月份
vs_ym_sn_beg CHAR(6); –同期起始月份
vs_ym_sn_end CHAR(6); –同期终止月份
–定义游标(简单的说就是一个可以遍历的结果集)
CURSOR cur_1 IS
SELECT 。。。
FROM 。。。
WHERE 。。。
GROUP BY 。。。;
BEGIN
–用输入参数给变量赋初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS
TO_DATE 等很常用的函数。
vs_ym_beg := SUBSTR(is_ym,1,6);
vs_ym_end := SUBSTR(is_ym,7,6);
vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,’yyyymm’), -12),’yyyymm’);
vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,’yyyymm’), -12),’yyyymm’);
–先删除表中特定条件的数据。
DELETE FROM 表名 WHERE ym = is_ym;
–然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount
DBMS_OUTPUT.put_line(‘del上月记录=’||SQL%rowcount||’条’);
INSERT INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt)
SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000
FROM BGD_AREA_CM_M_BASE_T
WHERE ym = vs_ym_beg
AND ym = vs_ym_end
GROUP BY area_code,CMCODE;
DBMS_OUTPUT.put_line(‘ins当月记录=’||SQL%rowcount||’条’);
–遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。
FOR rec IN cur_1 LOOP
UPDATE 表名
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn
WHERE area_code = rec.area_code
AND CMCODE = rec.CMCODE
AND ym = is_ym;
END LOOP;
COMMIT;
–错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。
EXCEPTION
WHEN OTHERS THEN
vs_msg := ‘ERROR IN xxxxxxxxxxx_p(‘||is_ym||’):’||SUBSTR(SQLERRM,1,500);
ROLLBACK;
–把当前错误记录进日志表。
INSERT INTO LOG_INFO(proc_name,error_info,op_date)
VALUES(‘xxxxxxxxxxx_p’,vs_msg,SYSDATE);
COMMIT;
RETURN;
END;
oracle存储过程语法
1 、判断语句:
if 比较式 then begin end; end if;
复制代码 代码如下:
create or replace procedure test(x in number) is
begin
if x 0 then
begin
x := 0 – x;
end;
end if;
if x = 0 then
begin
x: = 1;
end;
end if;
end test;
2 、For 循环
For … in … LOOP
— 执行语句
end LOOP;
(1) 循环遍历游标
复制代码 代码如下:
create or replace procedure test() as
Cursor cursor is select name from student; name varchar(20);
begin
for name in cursor LOOP
begin
dbms_output.putline(name);
end;
end LOOP;
end test;
(2) 循环遍历数组
复制代码 代码如下:
create or replace procedure test(varArray in myPackage.TestArray) as
–( 输入参数varArray 是自定义的数组类型，定义方式见标题6)
i number;
begin
i := 1; — 存储过程数组是起始位置是从1 开始的，与java 、C 、C++ 等语言不同。因为在Oracle 中本是没有数组的概念的，数组其实就是一张
— 表(Table), 每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历
for i in 1..varArray.count LOOP
dbms_output.putline(‘The No.’|| i || ‘record in varArray is:’||varArray(i));
end LOOP;
end test;
3 、While 循环
while 条件语句 LOOP
复制代码 代码如下:
begin
end;
end LOOP;
E.g
create or replace procedure test(i in number) as
begin
while i10 LOOP
begin
i:= i + 1;
end;
end LOOP;
end test;
4 、数组
首先明确一个概念：Oracle 中本是没有数组的概念的，数组其实就是一张表(Table), 每个数组元素就是表中的一个记录。
使用数组时，用户可以使用Oracle 已经定义好的数组类型，或可根据自己的需要定义数组类型。
(1) 使用Oracle 自带的数组类型
x array; — 使用时需要需要进行初始化
e.g:
create or replace procedure test(y out array) is
x array;
begin
x := new array();
y := x;
end test;
(2) 自定义的数组类型 ( 自定义数据类型时，建议通过创建Package 的方式实现，以便于管理)
create or replace package myPackage is
Public type declarations type info is record( name varchar(20), y number);
type TestArray is table of info index by binary_integer;
— 此处声明了一个TestArray 的类型数据，其实其为一张存储Info 数据类型的Table 而已，及TestArray 就是一张表，有两个字段，一个是name ，一个是y 。需要注意的是此处使用了Index by binary_integer 编制该Table 的索引项，也可以不写，直接写成：type TestArray is
table of info ，如果不写的话使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();
end TestArray;
5. 游标的使用 Oracle 中Cursor 是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：
(1)Cursor 型游标( 不能用于参数传递)
复制代码 代码如下:
create or replace procedure test() is
cusor_1 Cursor is select std_name from student where …; –Cursor 的使用方式1 cursor_2 Cursor;
begin
select class_name into cursor_2 from class where …; –Cursor 的使用方式2
可使用For x in cursor LOOP …. end LOOP; 来实现对Cursor 的遍历
end test;
(2)SYS_REFCURSOR 型游标，该游标是Oracle 以预先定义的游标，可作出参数进行传递
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR;
name varhcar(20);
begin
OPEN cursor FOR select name from student where … –SYS_REFCURSOR 只能通过OPEN 方法来打开和赋值
LOOP
fetch cursor into name –SYS_REFCURSOR 只能通过fetch into 来打开和遍历 exit when cursor%NOTFOUND; –SYS_REFCURSOR 中可使用三个状态属性： —%NOTFOUND( 未找到记录信息) %FOUND( 找到记录信息) —%ROWCOUNT( 然后当前游标所指向的行位置)
dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test;
实例
下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：
现假设存在两张表，一张是学生成绩表(studnet) ，字段为：stdId,math,article,language,music,sport,total,average,step
一张是学生课外成绩表(out_school), 字段为:stdId,parctice,comment
通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A ，就在总成绩上加20 分。
复制代码 代码如下:
create or replace procedure autocomputer(step in number) is
rsCursor SYS_REFCURSOR;
commentArray myPackage.myArray;
math number;
article number;
language number;
music number;
sport number;
total number;
average number;
stdId varchar(30);
record myPackage.stdInfo;
i number;
begin
i := 1;
get_comment(commentArray); — 调用名为get_comment() 的存储过程获取学生课外评分信息
OPEN rsCursor for select stdId,math,article,language,music,sport from student t where t.step = step;
LOOP
fetch rsCursor into stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;
total := math + article + language + music + sport;
for i in 1..commentArray.count LOOP
record := commentArray(i);
if stdId = record.stdId then
begin
if record.comment = ‘A’ then
begin
total := total + 20;
go to next; — 使用go to 跳出for 循环
end;
end if;
end;
end if;
end LOOP;
continue average := total / 5;
update student t set t.total=total and t.average = average where t.stdId = stdId;
end LOOP;
end;
end autocomputer;
— 取得学生评论信息的存储过程
create or replace procedure get_comment(commentArray out myPackage.myArray) is
rs SYS_REFCURSOR ；
record myPackage.stdInfo;
stdId varchar(30);
comment varchar(1);
i number;
begin
open rs for select stdId,comment from out_school
i := 1;
LOOP
fetch rs into stdId,comment; exit when rs%NOTFOUND;
record.stdId := stdId;
record.comment := comment;
recommentArray(i) := record;
i:=i + 1;
end LOOP;
end get_comment;
— 定义数组类型myArray
create or replace package myPackage is begin
type stdInfo is record(stdId varchar(30),comment varchar(1));
type myArray is table of stdInfo index by binary_integer;
end myPackage;
关于存储过程语法和sql server存储过程语法的相关介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。
