oracle 存储过程100例,oracle事务及存储过程实例 | 学步园

--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与end之前

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;