oracle存储过程和函数示例

LILIJJJJ 2012-11-13 08:22:22

--1、创建表
CREATE TABLE STUDENT
(
SNO NUMBER(4) PRIMARY KEY,
SNAME VARCHAR2(20),
SAGE NUMBER(4),
SSEX VARCHAR(2)
)
--2、创建序列
CREATE SEQUENCE MY_NUM
START WITH 1
INCREMENT BY 1
MAXVALUE 30 NOCYCLE;
--3、设置约束，默认值等操作
--sage设置check约束
ALTER TABLE STUDENT MODIFY
(
SAGE CHECK(SAGE>18)
);
--SSEX设置check约束
ALTER TABLE STUDENT MODIFY
(
SSEX CHECK(SSEX='男' or ssex='女')
);
--sage设置默认值
ALTER TABLE STUDENT MODIFY
(
SAGE DEFAULT(20)
);
--4、插入数据
INSERT INTO STUDENT VALUES(MY_NUM.NEXTVAL,'张三',default,'男');
INSERT INTO STUDENT VALUES(MY_NUM.NEXTVAL,'李斯',21,'女');
INSERT INTO STUDENT VALUES(MY_NUM.NEXTVAL,'天启',default,'男');
--附加：查看当前序列的号数
SELECT MY_NUM.CURRVAL FROM DUAL;
--5、查询表
SELECT * FROM STUDENT
--6、使用事务
BEGIN
INSERT INTO STUDENT VALUES(MY_NUM.NEXTVAL,'王林',23,'男');
UPDATE STUDENT SET SSEX='男' WHERE SNAME='天启';
COMMIT;
DBMS_OUTPUT.put_line('语句执行成功');
EXCEPTION --出异常执行该语句
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.put_line('语句执行失败');
END;
--7、创建存储过程
--例题1：
----不带参数的存储过程
CREATE OR REPLACE PROCEDURE STUDENT_PRO
AS
BEGIN
DBMS_OUTPUT.put_line('创建存储过程'); ----此处必须加分号
END;
--调用存储过程
BEGIN
STUDENT_PRO;----此处必须加分号
END;
--删除存储过程
drop procedure STUDENT_PRO;
--例题2：
---默认为in输入参数，OUT为输出参数,TESTOUT中带两个参数，一个输入参数，一个输出参数
CREATE OR REPLACE PROCEDURE TESTOUT
(VALUE1 NUMBER,VALUE2 OUT NUMBER)
AS
BEGIN
VALUE2:=VALUE1+100;
END;
--调用存储过程
DECLARE
RESULT NUMBER;
BEGIN
TESTOUT(10,RESULT);
DBMS_OUTPUT.put_line(RESULT);
END;
--例题3：
SELECT * FROM SCOTT.DEPT;
----in out参数
CREATE OR REPLACE PROCEDURE TEST_INOUT
(
DNO SCOTT.DEPT.DEPTNO%TYPE,
NAMES OUT SCOTT.DEPT.DNAME%TYPE
)
AS
BEGIN
--根据dno查询names---（DNAME INTO NAMES）表示将查询到得DNAME赋值给NAMES
SELECT DNAME INTO NAMES FROM SCOTT.DEPT WHERE DEPTNO=DNO;
END;

--调用存储过程
--因为有out输出参数，所以调用时必须声明变量
DECLARE
NAMES VARCHAR(20);
BEGIN
TEST_INOUT(10,NAMES);
DBMS_OUTPUT.put_line(NAMES);
END;
--8、函数
----格式,特别注意每句代码的位置
CREATE OR REPLACE FUNCTION GET_NAME
(
DNO SCOTT.DEPT.DEPTNO%TYPE
)
RETURN VARCHAR2---返回数据类型
AS
--声明变量保存dname，并返回
NAMES VARCHAR2(20);
BEGIN
--根据DNO查询DNAME
SELECT DNAME INTO NAMES FROM SCOTT.DEPT WHERE DEPTNO=DNO;
RETURN NAMES;--返回该数据类型的值
END;

--调用函数
BEGIN
DBMS_OUTPUT.put_line('部门名称：'||GET_NAME(20));---字符串与变量连接使用||
END;

--删除函数
DROP FUNCTION GET_NAME;

...全文