--1、创建一个存储过程,计算一个数是否是3和5的倍数
CREATE OR REPLACE PROCEDURE myProcedure(num1 IN NUMBER)IS
BEGIN
IF(MOD(num1,3)=0 AND MOD(num1,5)=0) THEN
dbms_output.put_line('这个数是3和5的倍数');
ELSE
dbms_output.put_line('这个数不是3和5的倍数');
END IF ;
END myProcedure;
--------------------------------------------------
BEGIN
myProcedure(15);
END;
--------------------------------------------------
--------------------------------------------------
-----2、创建一个存储过程,计算一个数是否是3和5的倍数,且输出结果-------
CREATE OR REPLACE PROCEDURE myProcedure2(num1 IN NUMBER,c OUT VARCHAR2 )IS
BEGIN
IF(MOD(num1,3)=0 AND MOD(num1,5)=0) THEN
c:='OK!';
dbms_output.put_line(c);
ELSE
c:='NOT OK !';
dbms_output.put_line(c);
END IF ;
END myProcedure2;
--------------------------------------------------
DECLARE
c2 VARCHAR2(10);
BEGIN
myProcedure2(15,c2);
END;
--------------------------------------------------
--------------------------------------------------
--------3、查询出所有学生的总人数,提取出来------------
CREATE OR REPLACE PROCEDURE myProcedure3(num1 OUT NUMBER)IS
BEGIN
SELECT COUNT(*)INTO num1 FROM Student;
END myProcedure3;
--------------------------------------------------
DECLARE
i1 NUMBER(10);
BEGIN
myProcedure3(i1);
dbms_output.put_line(i1);
END;
--------------------------------------------------
----4、根据放入的参数,制作相应的功能------------------
---如:放入 增加 做增加的功能 放入 修改 做修改的功能--------
SELECT * FROM grade;
CREATE OR REPLACE PROCEDURE myProcedure4(c1 IN VARCHAR2)IS
BEGIN
IF(c1='增加')THEN
INSERT INTO grade VALUES (4,'四年级');
END IF;
IF(c1='修改')THEN
UPDATE grade SET gradeName='四年级' WHERE ID=4;
END IF;
END myProcedure4;
--------------------------------------------------
DECLARE
c1 VARCHAR2(10):='&输入想要的功能';
BEGIN
myProcedure4(c1);
END;
SELECT *FROM grade;
--------------------------------------------------
--------5、创建存储过程--------------------------------
----通过输入的学号找到相应的学生信息,并返回出来-------
CREATE OR REPLACE PROCEDURE myProcedure5(i1 IN NUMBER,stuInfo OUT student%ROWTYPE ) IS
BEGIN
SELECT * INTO stuInfo FROM Student WHERE stuNo=i1;
END;
--------------------------------------------------
DECLARE
stuRow Student%ROWTYPE;
stuId NUMBER(6):='&输入学号';
BEGIN
myProcedure5(stuId,stuRow);
dbms_output.put_line(stuRow.stuNo);
dbms_output.put_line(stuRow.stuName);
dbms_output.put_line(stuRow.address);
dbms_output.put_line(stuRow.sex);
dbms_output.put_line(stuRow.phone);
dbms_output.put_line(stuRow.email);
END;
--------------------------------------------------
---------根据部门编号查询部门信息------------
CREATE OR REPLACE PROCEDURE myProcedure(n1 IN OUT dept.id%TYPE,n2 OUT dept.deptName%TYPE)
AS
BEGIN
SELECT ID,deptName INTO n1,n2 FROM dept WHERE ID=n1;
dbms_output.put_line('你要查询的信息已经查询完成!');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('你要查询的信息不存在!');
WHEN OTHERS THEN
dbms_output.put_line('出现了其他异常错误!');
END myProcedure;
-------------------------------
DECLARE
c1 dept.id%TYPE:='&输入id';
c2 dept.deptName%TYPE;
BEGIN
myProcedure(c1,c2);
dbms_output.put_line('id:'||c1);
dbms_output.put_line('deptName:'||c2);
END;
--------------------------------------------------
----6、通过输入的班级名称,查询出该班级的所有学生-------
CREATE OR REPLACE PROCEDURE myProcedure6(c1 IN OUT VARCHAR2,myCursor OUT Sys_Refcursor)IS
BEGIN
OPEN myCursor FOR SELECT * FROM student WHERE gradeId=(SELECT ID FROM grade WHERE gradeName=c1);
END;
--------------------------------------------------
DECLARE
TYPE stuCursor IS REF CURSOR;
v_stuCursor stuCursor;
stuRow Student%ROWTYPE;
c1 VARCHAR2(20):='&输入班级名称';
BEGIN
myProcedure6(c1,v_stuCursor);
dbms_output.put_line(c1||'学生信息如下:');
LOOP
FETCH v_stuCursor INTO stuRow;
EXIT WHEN v_stuCursor%NOTFOUND;
dbms_output.put_line('-----------------------------');
dbms_output.put_line(stuRow.stuNo);
dbms_output.put_line(stuRow.stuName);
dbms_output.put_line(stuRow.address);
dbms_output.put_line(stuRow.sex);
dbms_output.put_line(stuRow.phone);
dbms_output.put_line(stuRow.email);
END LOOP;
CLOSE v_stuCursor;
END;
--------------------------------------------------
-------------------查询所有部门信息------------
CREATE OR REPLACE PROCEDURE myProvedure2(myCursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN myCursor FOR SELECT * FROM dept;
END;
-----------------
DECLARE
deptInfo dept%ROWTYPE;
TYPE cursor1 IS REF CURSOR;--定义一个动态游标数据类型
cursor2 cursor1;--声明一个该数据类型的游标变量
BEGIN
myProvedure2(cursor2);
LOOP
FETCH cursor2 INTO deptInfo;
EXIT WHEN cursor2%NOTFOUND;
dbms_output.put_line('-----------------------------');
dbms_output.put_line('id:'||deptInfo.id);
dbms_output.put_line('deptNAme'||deptInfo.deptName);
END LOOP;
END;
--------------------------------------------------
---------7、判断输入的数字长度,小于6位数则抛出异常--------
DECLARE
ex_input EXCEPTION;
value1 VARCHAR2(20):='&输入一串数字';
BEGIN
if(LENGTH(value1)<6)THEN
RAISE ex_input;
END IF;
EXCEPTION
WHEN ex_input THEN
dbms_output.put_line('输入的数字小于6啦~~~~~');
END;
----------------------------------------------------------------
-----------------8、定义一个异常类型-----------------------------
--当用户输入的年龄不在18~65之间,报异常(‘你该学习了~~~~~’)-----
DECLARE
ex_input EXCEPTION;
age INT:='&输入你的年龄';
BEGIN
if(age>65 OR age<18)THEN
RAISE ex_input;
END IF;
EXCEPTION
WHEN ex_input THEN
dbms_output.put_line('该学习了~~~~');
END;
----------------------------------------------------------------
----------9、创建一个方法-----------------------------------------
------------根据员工的姓名 查询出员工的年薪-----------------------
CREATE OR REPLACE FUNCTION empSalFind(c1 VARCHAR2)
RETURN scott.emp.sal%TYPE
IS
salYear scott.emp.sal%TYPE;
BEGIN
SELECT (sal+NVL(comm,0))*12 INTO salYear FROM scott.emp WHERE ename=c1;
RETURN salYear;
END;
----------------------------------------------------------------
DECLARE
c1 VARCHAR2(20):='&输入员工姓名';
sal scott.emp.sal%TYPE;
BEGIN
sal:=empSalFind(c1);
dbms_output.put_line(c1||'的年薪是>>'||sal);
END;