1、创建带参数的存储过程,首先创建表,执行如下sql:create table FUT_CCGC_DCS_T (c1 int, c2 char(10), c3 varchar(20));
2、创建带参数的存储过程,执行如下SQL命令:
create or replace procedure FUT_CCGC_DCS(i in int)
as j int;total int;
begin
for j in 1 ..i loop
insert into FUT_CCGC_DCS_T values(j,3,'aaaaa'); end loop;
end;
3、调用存储过程,执行如下SQL命令:FUT_CCGC_DCS (10);
4、查询,验证结果,执行如下SQL命令:select count(*) from FUT_CCGC_DCS_T;
5、创建不带参数的存储过程,首先创建结果记录表,执行如下SQL命令:Create table results (type varchar(20),num int);
6、创建对象,执行如下SQL命令:create table FUT_CCGC_BDCS_T (c1 int, c2 char(10), c3 varchar(20));
7、插入测试数据,执行如下sql:
insert into FUT_CCGC_BDCS_T values(1,'a','aaaaa');
insert into FUT_CCGC_BDCS_T values(2,'b','bbbbb');
insert into FUT_CCGC_BDCS_T values(3,'c','ccccc');
8、创建不带参数的存储过程,执行如下SQL命令:
create or replace procedure FUT_CCGC_BDCS is total int;
begin
insert into results select 'FUT_CCGC_BDCS',count(*)from FUT_CCGC_BDCS_T;
end;
9、测试调用存储过程,执行如下sql:call FUT_CCGC_BDCS;
10、验证结果,执行如下sql:Select num from results where type='FUT_CCGC_BDCS';
11、游标定义、打开、拨动、关闭,首先创建表并插入数据:create table FUT_CCGC_YB(id int,name varchar(10),title varchar(20));
insert into FUT_CCGC_YB values(1,'yang','红楼梦');
insert into FUT_CCGC_YB values(2,'zhang','老人与海');
insert into FUT_CCGC_YB values(3,'wang','正能量');
insert into FUT_CCGC_YB values(4,'chen','提升自我');
commit;
12、创建存储过程,包含游标的定义、打开、拨动、关闭操作,执行如下sql:
CREATE OR REPLACE PROCEDURE PROC_FUT_CCGC_YB AS
STR VARCHAR;
CURSOR C1 IS SELECT TITLE FROM FUT_CCGC_YB;
BEGIN
OPEN C1; --赋值后再打开
FETCH NEXT C1 INTO STR;
PRINT STR;
CLOSE C1;
END;
13、调用存储过程,执行如下sql:call PROC_FUT_CCGC_YB;
14、存储过程支持动态SQL执行语句,创建存储过程,包含动态SQL:
CREATE OR REPLACE PROCEDURE PROC_FUT_CCGC_DTSQL(id IN INT, name IN varchar(10)) AS
DECLARE
str_sql varchar := 'SELECT title from FUT_CCGC_YB WHERE
id = ? AND name = ?';
BEGIN
EXECUTE IMMEDIATE str_sql USING id,name;
EXCEPTION
WHEN OTHERS THEN PRINT 'error';
END;
15、调用存储过程:CALL PROC_FUT_CCGC_DTSQL(2,'zhang');
16、删除存储过程,执行如下sql:drop procedure FUT_CCGC_BDCS;