Oracle中的存储过程简单例子

来源：互联网发布：js validate取消验证编辑：程序博客网时间：2024/05/24 03:58

---创建表
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 procedure 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 procedure
test_count;
end;

create or replace procedure 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 procedure 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 procedure 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 procedure 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';

截止到目前我发现有三种方法可以在存储过程中给变量进行赋值：

1、直接法 :=

如：v_flag := 0;

2、select into

如：假设变量名为v_flag，select count（*） into v_flag from students;

3、execute immediate 变量名（一般是sql的select语句） into 变量名

如：

v_sqlfalg := 'select count(*) from user_tables where table_name='''||v_tablename || '''';

execute immediate v_sqlfalg into v_flag;

其中，v_tablename也是变量

当然2和3实质是一样的。只不过3中的select语句是根据变量生成的。

在存储过程中，是不能直接写select语句的。

INSTR、substr函数

INSTR (源字符串, 目标字符串, 起始位置, 匹配序号) #####注意：起始位置从1开始

　　在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，就是说从字符的开始到字符的结尾就结束。

　　语法如下：
　　instr( string1, string2 ,start_position , )
　　参数分析：
　　string1
　　源字符串，要在此字符串中查找。
　　string2
　　要在string1中查找的字符串.
　　start_position

参数分析：

　　string1:源字符串，要在此字符串中查找。

　　string2:要在string1中查找的字符串.

　start_position:代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。

　　nth_appearance:代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。

注意:如果String2在String1中没有找到，instr函数返回0.

示例：

　　SELECT instr('syranmo','s') FROM dual; -- 返回 1

　SELECT instr('syranmo','ra') FROM dual; -- 返回 3

　SELECT instr('syran mo','a',1,2) FROM dual; -- 返回 0

（根据条件，由于a只出现一次，第四个参数2，就是说第2次出现a的位置，显然第2次是没有再出现了，所以结果返回0。注意空格也算一个字符！）

　SELECT instr('syranmo','an',-1,1) FROM dual; -- 返回 4

　（就算是由右到左数，索引的位置还是要看‘an’的左边第一个字母的位置，所以这里返回4）

　　SELECT instr('abc','d') FROM dual; -- 返回 0

　　注：也可利用此函数来检查String1中是否包含String2，如果返回0表示不包含，否则表示包含。

Oracle中的substr方法

oracle中的substr比较给力,以字符串abcde为例

select substr('abcde',1,3) as str from dual

结果abc

select substr('abcde',3,5) as str from dual

结果cde

select substr('abcde',3,50) as str from dual

不报错,结果cde

从1开始，到几就是第几位，简单易用

NVL函数

NVL函数的格式如下：NVL(expr1,expr2)

含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(id
IN NUMBER,name
OUT varchar2)
AS
BEGIN
SELECT name INTO
name FROM TESTTABLE WHERE id1=id;
EXCEPTION
WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
END;

DECLARE
name varchar2(10);
BEGIN
getSalaryByEmpNo(1,name);
DBMS_OUTPUT.PUT_LINE(name);
END;

阅读全文

0 0