首先
SQL> set serveroutput on;
基本语法
create or replace procedure [名称](变量名 in/out 类型) is
[定义变量]
begin
[语句]
end；
/
循环语句实例：打印99乘法表
create or replace procedure chengfabiao is
begin
for i in 1 .. 9 loop
for j in 1 .. 9 loop
dbms_output.put(' '||i||' x '||j||' = '||i*j); ----put不会换行
end loop;
dbms_output.put_line(''); ----put_line可以换行
end loop;
end;
/
控制语句/游标实例：输出工资水平
create or replace procedure gongzishuiping is
cursor cur_emp is ----游标必须和循环语句联合使用
select * from emp;
begin
for x in cur_emp loop ----或者case when…then end case；
if x.sal between 700 and 1400 then
dbms_output.put_line(x.empno||' '||x.ename||' '||x.sal||' '||'laji');
elsif x.sal between 1401 and 2000 then
dbms_output.put_line(x.empno||' '||x.ename||' '||x.sal||' '||'yiban');
elsif x.sal between 2001 and 4000 then
dbms_output.put_line(x.empno||' '||x.ename||' '||x.sal||' '||'bucuo');
end if;
end loop;
end;
/
动态游标
DECLARE
teacher_id NUMBER(5);
teacher_name VARCHAR2(50);
teacher_title VARCHAR2(50);
teacher_sex char(1); ---定义变量存放teachers表中的内容
CURSOR teacher_cur IS
SELECT TID,TNAME,TITLE,SEX
FROM TEACHERS
WHERE TID<117; ---选择号码小于117的数据
BEGIN
OPEN teacher_cur; ---打开游标
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;
---将！第一行！数据存入变量中，游标后移。在循环外相当于启动循环
LOOP
EXIT WHEN NOT teacher_cur%FOUND; ---如果游标到尾则结束
IF teacher_sex='M' THEN
INSERT INTO MALE_TEACHERS(TID,TNAME,TITLE)
VALUES(teacher_id,teacher_name,teacher_tile);
ELSE
INSERT INTO FRMALE_TEACHERS(TID,TNAME,TITLE)
VALUES(teacher_id,teacher_name,teacher_title);
END IF; ---条件控制实现男女分离
FETCH teacher_cur INTO teacher_id,teacher_name,teacher_title,teacher_sex;
---游标移动到下一行，在循环里
END LOOP;
CLOSE teacher_cur; ---关闭游标
END;
/*按照sal=2000分类，显示员工编号，姓名，工资，部门编号。自己想的实例*/
DECLARE
v_ename VARCHAR2(10);
v_empno NUMBER(4);
v_deptno NUMBER(2);
v_sal NUMBER(7,2);
CURSOR cur_emp IS
SELECT empno,ename,sal,deptno FROM emp;
BEGIN
OPEN cur_emp;
FETCH cur_emp INTO v_empno,v_ename,v_sal,v_deptno;
LOOP
EXIT WHEN NOT cur_emp%FOUND;
IF v_sal >= 2000 THEN
INSERT INTO
niubi(IEMPNO,IENAME,ISAL,IDEPTNO)
VALUES(v_empno,v_ename,v_sal,v_deptno);
ELSE
INSERT INTO
laji(IEMPNO,IENAME,ISAL,IDEPTNO)
VALUES(v_empno,v_ename,v_sal,v_deptno);
END IF;
FETCH cur_emp INTO v_empno,v_ename,v_sal,v_deptno;
END LOOP;
CLOSE cur_emp;
END;
函数调用实例：工资等级分类统计
CREATE OR REPLACE FUNCTION count_num --创建函数
(daxiao in VARCHAR2) --指定 输入变量 变量类型和某个变量一致
return NUMBER --指定返回值的类型
AS
out_num NUMBER;
BEGIN
IF daxiao='>' THEN
SELECT count(*) INTO out_num
FROM emp
WHERE sal>=2000;
ELSE
SELECT count(*) INTO out_num
FROM emp
WHERE sal<2000;
END IF;
RETURN(out_num);
END count_num;
/
DECLARE --存储过程调用函数count_num
da_num NUMBER;
xiao_num NUMBER;
BEGIN
da_num:=count_num('>');
dbms_output.put_line(da_num);
xiao_num:=count_num('<');
dbms_output.put_line(xiao_num);
END;
/
触发器实例：对emp进行增删改时记录操作类型和实践
CREATE OR REPLACE TRIGGER tr_log
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR each row --对每行
DECLARE
info VARCHAR2(10);
BEGIN
IF inserting THEN
info:='INSERT';
ELSIF updating THEN
info:='UPDATE';
ELSE
info:='DELETE';
END IF;
INSERT INTO SQL_INFO VALUES(info,SYSDATE);
END tr_log;
