如何使用Oracle存储过程的一个简单例子
发布网友
发布时间：2022-04-20 10:45
共2个回答
懂视网
时间：2022-05-01 22:33
--关于游标 if,for 的例子
create or replace procedure peace_if
is
cursor var_c is select * from grade;
begin
for temp in var_c loop
if temp.course_name=‘OS‘ then
dbms_output.put_line(‘Stu_name=‘||temp.stu_name);
else if temp.course_name=‘DB‘ then
dbms_output.put_line(‘DB‘);
else
dbms_output.put_line(‘fengla fengla‘);
end if;
end loop;
end;
--关于游标，for,case得例子
create or replace procedure peace_case1
is
cursor var_c is select * from test_case;
begin
for temp in var_c loop
case temp.vol
when 1 then
dbms_output.put_line(‘haha1‘);
when 2 then
dbms_output.put_line(‘haha2‘);
when 3 then
dbms_output.put_line(‘haha3‘);
when 4 then
dbms_output.put_line(‘haha4‘);
when 5 then
dbms_output.put_line(‘haha5‘);
else
dbms_output.put_line(‘haha‘);
end case;
end loop;
end;
--关于游标 for,case,的例子2
create or replace procedure peace_case2
is
cursor var_c is select * from test_case;
begin
for temp in var_c loop
case
when temp.vol=1 then
dbms_output.put_line(‘haha1‘);
when temp.vol=2 then
dbms_output.put_line(‘haha2‘);
when temp.vol=3 then
dbms_output.put_line(‘haha3‘);
when temp.vol=4 then
dbms_output.put_line(‘haha4‘);
else
dbms_output.put_line(‘haha5‘);
end case;
end loop;
end;
--关于for 循环的例子
--to_char();实现其他数据类型向字符类型转换
create or replace procedure peace_for
is
sum1 number :=0;
temp varchar2(500);
begin
for i in 1..9 loop
temp :=‘‘;
for j in 1..i loop
sum1 :=i*j;
temp:=temp||‘ ‘||to_char(i)||‘*‘||to_char(j)||‘=‘||to_char(sum1)||‘‘;
end loop;
dbms_output.put_line(temp);
end loop;
end;
--关于loop循环
create or replace procedure loop_cur
is
stu_name varchar2(100);
course_name varchar2(100);
cursor var_c is select * from grade;
begin
open var_c;
loop
fetch var_c into stu_name,course_name;
exit when var_c%notfound;
dbms_output.put_line(stu_name||‘ ‘|| course_name);
end loop;
close var_c;
end;
--关于异常处理的例子
create or replace procedure peace_exp(in1 in varchar2)
is
c_n varchar2(100);
begin
select course_name into c_n from grade where stu_name=in1;
dbms_output.put_line(c_n);
exception
when no_data_found
then
dbms_output.put_line(‘try‘);
when TOO_MANY_ROWS
then
dbms_output.put_line(‘more‘);
end;
--异常处理例子2
create or replace procedure peace_insert(c_n in varchar2)
is
error EXCEPTION;
begin
if c_n =‘OK‘ then
insert into grade(course_name) values(c_n);
elsif c_n=‘NG‘ then
insert into grade(course_name) values(c_n);
raise error;
else
dbms_output.put_line(‘c_n‘||‘ ‘||c_n);
end if;
commit;
exception
when error then
rollback;
dbms_output.put_line(‘error‘);
end;
--定义包头
create or replace package peace_pkg
as
function test1(in1 in varchar2)
return number;
procedure test2(in2 in varchar2);
end peace_pkg;
--定义包体
create or replace package body peace_pkg
as
function test1(in1 in varchar2)
return number
as
temp number;
begin
temp:=0;
return temp;
end;
procedure test2(in2 in varchar2)
is
begin
dbms_output.put_line(in2);
end;
end peace_pkg;
oracle_存储过程例子_1
标签：ror cti from roc back char turn ret proc
热心网友
时间：2022-05-01 19:41
---创建表
create table TESTTABLE
(
id1 VARCHAR2(12),
name VARCHAR2(32)
)
select t.id1,t.name from TESTTABLE t
insert into TESTTABLE (ID1, NAME)
values ('1', 'zhangsan');
insert into TESTTABLE (ID1, NAME)
values ('2', 'lisi');
insert into TESTTABLE (ID1, NAME)
values ('3', 'wangwu');
insert into TESTTABLE (ID1, NAME)
values ('4', 'xiaoliu');
insert into TESTTABLE (ID1, NAME)
values ('5', 'laowu');
---创建存储过程
create or replace procere test_count
as
v_total number(1);
begin
select count(*) into v_total from TESTTABLE;
DBMS_OUTPUT.put_line('总人数：'||v_total);
end;
--准备
--线对scott解锁：alter user scott account unlock;
--应为存储过程是在scott用户下。还要给scott赋予密码
---alter user scott identified by tiger;
---去命令下执行
EXECUTE test_count;
----在ql/spl中的sql中执行
begin
-- Call the procere
test_count;
end;
create or replace procere TEST_LIST
AS
---是用游标
CURSOR test_cursor IS select t.id1,t.name from TESTTABLE t;
begin
for Test_record IN test_cursor loop---遍历游标，在打印出来
DBMS_OUTPUT.put_line(Test_record.id1||Test_record.name);
END LOOP;
test_count;--同时执行另外一个存储过程（TEST_LIST中包含存储过程test_count）
end;
-----执行存储过程TEST_LIST
begin
TEST_LIST;
END;
---存储过程的参数
---IN 定义一个输入参数变量，用于传递参数给存储过程
--OUT 定义一个输出参数变量，用于从存储过程获取数据
---IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能
--这三种参数只能说明类型，不需要说明具体长度 比如 varchar2(12)，defaul 可以不写，但是作为一个程序员最好还是写上。
---创建有参数的存储过程
create or replace procere test_param(p_id1 in VARCHAR2 default '0')
as v_name varchar2(32);
begin
select t.name into v_name from TESTTABLE t where t.id1=p_id1;
DBMS_OUTPUT.put_line('name：'||v_name);
end;
----执行存储过程
begin
test_param('1');
end;
default '0'
---创建有参数的存储过程
create or replace procere test_paramout(v_name OUT VARCHAR2 )
as
begin
select name into v_name from TESTTABLE where id1='1';
DBMS_OUTPUT.put_line('name：'||v_name);
end;
----执行存储过程
DECLARE
v_name VARCHAR2(32);
BEGIN
test_paramout(v_name);
DBMS_OUTPUT.PUT_LINE('name：'||v_name);
END;
-------IN OUT
---创建存储过程
create or replace procere test_paramINOUT(p_phonenumber in out varchar2)
as
begin
p_phonenumber:='0571-'||p_phonenumber;
end;
----
DECLARE
p_phonenumber VARCHAR2(32);
BEGIN
p_phonenumber:='26731092';
test_paramINOUT(p_phonenumber);
DBMS_OUTPUT.PUT_LINE('新的电话号码：'||p_phonenumber);
END;
-----sql命令下，查询当前用户的存储过程或函数的源代码，
-----可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下：
SQL> DESCRIBE USER_SOURCE ;
Name Type Nullable Default Comments
---- -------------- -------- -------
-------------------------------------------------------------------------------------------------------------
NAME VARCHAR2(30) Y Name of the object
TYPE VARCHAR2(12) Y Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",
"PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"
LINE NUMBER Y Line number of this line of
source
TEXT VARCHAR2(4000) Y Source text
SQL>
---查询出存储过程的定义语句
select text from user_source WHERE NAME='TEST_COUNT';
----查询存储过程test_paramINOUT的参数
SQL> DESCRIBE test_paramINOUT;
Parameter Type Mode Default?
------------- -------- ------ --------
P_PHONENUMBER VARCHAR2 IN OUT
SQL>
---查看当前的存储过程的状态是否正确，
---VALID为正确，INVALID表示存储过程无效或需要重新编译
SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='TEST_COUNT';
-----如果要检查存储过程或函数的依赖性，可以通过查询数据字典USER_DENPENDENCIES来确定，该表结构如下：
SQL> DESCRIBE USER_DEPENDENCIES;
Name Type Nullable Default Comments
-------------------- ------------- -------- ------- ----------------------------------------------------------
NAME VARCHAR2(30) Name of the object
TYPE VARCHAR2(17) Y Type of the object
REFERENCED_OWNER VARCHAR2(30) Y Owner of referenced object (remote owner if remote object)
REFERENCED_NAME VARCHAR2(64) Y Name of referenced object
REFERENCED_TYPE VARCHAR2(17) Y Type of referenced object
REFERENCED_LINK_NAME VARCHAR2(128) Y Name of dblink if this is a remote object
SCHEMAID NUMBER Y
DEPENDENCY_TYPE VARCHAR2(4) Y
SQL>
---查询存储过程TEST_COUNT的依赖关系
SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='TEST_COUNT';
