-- 第一章节
BEGIN
dbms_output.put_line('abc');
END;
SELECT * FROM dept;
------------------------------------
DECLARE
a NUMBER := 20;
b NUMBER DEFAULT 30;
c NUMBER;
PI CONSTANT NUMBER := 3.1415926;
BEGIN
--a := &请输入第一个值;
--b := &请输入第二个值;
c := a + b;
--PI := 333;
dbms_output.put_line(c);
END;
-------------------------------------
DECLARE
c INT;
BEGIN
SELECT COUNT(*) INTO c FROM emp;
dbms_output.put_line('总员工数:' || c);
END;
SELECT * FROM dept;
-----------------------------------------
DECLARE
dn dept.dname%TYPE; --dn的类型和dept表的dname列的类型一致
r dept%ROWTYPE;
BEGIN
dn := 'aaaa';
dbms_output.put_line(dn);
SELECT * INTO r FROM dept WHERE deptno = 10;
dbms_output.put_line(r.deptno || r.dname || r.loc);
END;
-----------记录类型--------------
DECLARE
TYPE dept_rec IS RECORD(
dname VARCHAR2(20),
loc VARCHAR2(200)
);
dr dept_rec;
BEGIN
dr.dname := 'xxxxx';
dr.loc := 'xxxxx';
dbms_output.put_line(dr.dname || dr.loc);
SELECT dname, loc INTO dr FROM dept WHERE deptno=20;
dbms_output.put_line(dr.dname || dr.loc);
END;
--------------数组(更像java中的集合)---------
DECLARE
TYPE intArray IS TABLE OF INT; --创建整型数组,索引类型是整数
ia intArray := intArray(11, 21 , 31 ,14 ,15);
TYPE intArray2 IS TABLE OF INT INDEX BY VARCHAR2(20); --索引类型可以自定义
ia2 intArray2;
BEGIN
dbms_output.put_line(ia(1));
ia.EXTEND(1); --扩展空间,即添加新元素
ia(6) := 100;
FOR i IN 1..ia.COUNT
LOOP
dbms_output.put_line(ia(i));
END LOOP;
dbms_output.put_line(ia(ia.FIRST));
ia2('aa') := 22;
--dbms_output.put_line(ia2.FIRST);
END;
-----------------------IF结构---------------
--如果平均工资>5000,则输出"白领", 如果[2000,5000],是蓝领,<2000是无领
DECLARE
avgSal NUMBER(10, 2);
BEGIN
SELECT AVG(sal) INTO avgSal FROM emp;
dbms_output.put_line('平均工资:' || avgSal);
IF avgSal > 5000 THEN
dbms_output.put_line('白领');
ELSIF avgSal BETWEEN 2000 AND 5000 THEN
dbms_output.put_line('蓝领');
ELSE
dbms_output.put_line('无领');
END IF;
END;
-----------------------CASE结构---------------
--如果平均工资>5000,则输出"白领", 如果[2000,5000],是蓝领,<2000是无领
DECLARE
avgSal NUMBER(10, 2);
r VARCHAR2(20);
BEGIN
SELECT AVG(sal) INTO avgSal FROM emp;
dbms_output.put_line('平均工资:' || avgSal);
r := CASE
WHEN avgSal > 5000 THEN '白领'
WHEN avgSal BETWEEN 2000 AND 5000 THEN '蓝领'
WHEN avgSal < 2000 THEN '无领'
ELSE 'xxx'
END;
dbms_output.put_line(r);
END;
SELECT ename, sal, CASE
WHEN sal > 5000 THEN '白领'
WHEN sal BETWEEN 2000 AND 5000 THEN '蓝领'
ELSE '无领'
END CASE FROM emp;
-----------------------for循环1---------------
DECLARE
s NUMBER := 0;
i INTEGER DEFAULT 1;
BEGIN
LOOP
s := s + i;
i := i + 1;
IF i > 100 THEN
EXIT;
END IF;
END LOOP;
dbms_output.put_line(s);
END;
-----------------------for循环2---------------
DECLARE
s NUMBER := 0;
i INTEGER DEFAULT 1;
BEGIN
LOOP
s := s + i;
i := i + 1;
EXIT WHEN i > 100;
END LOOP;
dbms_output.put_line(s);
END;
-----------------------for循环3---------------
DECLARE
s NUMBER := 0;
i INTEGER DEFAULT 1;
BEGIN
WHILE i <= 100
LOOP
s := s + i;
i := i + 1;
END LOOP;
dbms_output.put_line(s);
END;
-----------------------for循环4---------------
DECLARE
s NUMBER := 0;
BEGIN
FOR i IN 1..100
LOOP
s := s + i;
END LOOP;
dbms_output.put_line(s);
END;
--------------------for循环访问结果集-----------
BEGIN
FOR v IN (SELECT * FROM dept)
LOOP
dbms_output.put_line(v.deptno || v.dname || v.loc);
END LOOP;
END;
------------------九九乘法表---------------
BEGIN
FOR r IN 1..9
LOOP
FOR c IN 1..r
LOOP
dbms_output.put(c || '*' || r || '=' || (c * r) || ' ');
END LOOP;
dbms_output.put_line('');
END LOOP;
END;
----------------空语句-----------
BEGIN
IF 3 = 3 THEN
NULL;
END IF;
END;
-------------动态sQL---------------
DECLARE
sql_str VARCHAR2(2000);
TYPE dept_type IS RECORD(
did NUMBER,
dname VARCHAR2(20),
dloc VARCHAR2(200)
);
dt dept_type;
dno NUMBER;
BEGIN
dno := &请输入部门编号;
sql_str := 'select * from dept where deptno = :dno';
EXECUTE IMMEDIATE sql_str INTO dt USING dno;
dbms_output.put_line(dt.did||dt.dname||dt.dloc);
END;
--第二章节
DECLARE
CURSOR c(n INT, m INT) IS SELECT dname, loc FROM dept WHERE deptno BETWEEN n AND m;
dname_ VARCHAR2(20);
dloc_ VARCHAR2(200);
BEGIN
OPEN c(10, 30);
FETCH c INTO dname_, dloc_;
WHILE c%FOUND
LOOP
dbms_output.put_line(dname_ || ',' || dloc_);
FETCH c INTO dname_, dloc_;
END LOOP;
CLOSE c;
END;
DECLARE
CURSOR c IS SELECT dname, loc FROM dept;
dname_ VARCHAR2(20);
dloc_ VARCHAR2(200);
BEGIN
OPEN c;
dbms_output.put_line(c%ROWCOUNT);
FETCH c INTO dname_, dloc_;
LOOP
IF c%NOTFOUND THEN
EXIT;
END IF;
dbms_output.put_line(dname_ || ',' || dloc_);
FETCH c INTO dname_, dloc_;
END LOOP;
dbms_output.put_line(c%ROWCOUNT);
CLOSE c;
END;
------------------循环游标----------------------
BEGIN
FOR v IN (SELECT * FROM dept)
LOOP
dbms_output.put_line(v.deptno || ',' || v.dname || ',' || v.loc);
END LOOP;
END;
------------------------------------------------
DECLARE
CURSOR c(m INT, n INT)
IS SELECT * FROM dept WHERE deptno BETWEEN m AND n;
BEGIN
FOR v IN c(10, 30)
LOOP
dbms_output.put_line(v.deptno || ',' || v.dname || ',' || v.loc);
END LOOP;
END;
-------------------显示游标一次遍历所有行-----
DECLARE
CURSOR c IS SELECT dname, deptno FROM dept;
TYPE dnames IS TABLE OF VARCHAR2(20);
dns dnames;
TYPE deptnos IS TABLE OF NUMBER;
des deptnos;
BEGIN
OPEN c;
FETCH c BULK COLLECT INTO dns, des;
FOR v IN 1..dns.COUNT
LOOP
dbms_output.put_line(dns(v) || ',' || des(v));
END LOOP;
CLOSE c;
END;
----------------------引用游标---------------------
DECLARE
TYPE dept_cur IS REF CURSOR;
dc dept_cur;
a NUMBER;
b VARCHAR2(20);
c VARCHAR2(200);
BEGIN
OPEN dc FOR SELECT * FROM dept;
FETCH dc INTO a, b, c;
WHILE dc%FOUND
LOOP
dbms_output.put_line(a || b || c);
FETCH dc INTO a, b, c;
END LOOP;
CLOSE dc;
END;
--------------------------数据结构化------------
DECLARE
TYPE dept_cur IS REF CURSOR;
dc dept_cur;
r dept%ROWTYPE;
BEGIN
OPEN dc FOR SELECT * FROM dept;
FETCH dc INTO r;
WHILE dc%FOUND
LOOP
dbms_output.put_line(r.deptno || r.dname || r.loc);
FETCH dc INTO r;
END LOOP;
CLOSE dc;
END;
DECLARE
TYPE dept_cur IS REF CURSOR;
dc dept_cur;
TYPE rec IS RECORD(
dname dept.dname%TYPE,
loc dept.loc%TYPE
);
r rec;
BEGIN
OPEN dc FOR SELECT dname, loc FROM dept;
FETCH dc INTO r;
WHILE dc%FOUND
LOOP
dbms_output.put_line(r.dname || r.loc);
FETCH dc INTO r;
END LOOP;
CLOSE dc;
END;
-------------如果平均工资>3000,显示部门名称,否则员工名称---
DECLARE
TYPE c IS REF CURSOR;
c_ c;
name_ VARCHAR2(20);
avg_sal NUMBER(18,2);
BEGIN
SELECT AVG(sal) INTO avg_sal FROM emp;
dbms_output.put_line('平均工资:' || avg_sal);
IF avg_sal > 6000 THEN
OPEN c_ FOR SELECT dname FROM dept;
ELSE
OPEN c_ FOR SELECT ename FROM emp WHERE MOD(empno, 2) = 0;
END IF;
FETCH c_ INTO name_;
WHILE c_%FOUND
LOOP
dbms_output.put_line(name_);
FETCH c_ INTO name_;
END LOOP;
CLOSE c_;
END;
-------------------动态SQL引用游标--------------
DECLARE
TYPE c IS REF CURSOR;
c_ c;
r dept%ROWTYPE;
sql_ VARCHAR2(400);
BEGIN
sql_ := 'select * from dept where deptno > :1 and deptno <:2';
OPEN c_ FOR sql_ USING 10, 50;
FETCH c_ INTO r;
WHILE c_%FOUND
LOOP
dbms_output.put_line(r.dname);
FETCH c_ INTO r;
END LOOP;
CLOSE c_;
END;
-----------------------游标表达式------------
DECLARE
TYPE c IS REF CURSOR;
c_ c;
dname dept.dname%TYPE;
ename emp.ename%TYPE;
CURSOR d IS SELECT dname, CURSOR(SELECT ename FROM emp WHERE emp.deptno = dept.deptno) FROM dept;
BEGIN
OPEN d;
FETCH d INTO dname, c_;
WHILE d%FOUND
LOOP
dbms_output.put_line(dname);
FETCH c_ INTO ename;
WHILE c_%FOUND
LOOP
dbms_output.put_line(' ' || ename);
FETCH c_ INTO ename;
END LOOP;
FETCH d INTO dname, c_;
END LOOP;
CLOSE d;
END;
SELECT * FROM emp;
-----------------通过游标修改和删除数据----------
DECLARE
CURSOR c IS SELECT * FROM emp FOR UPDATE;
r emp%ROWTYPE;
CURSOR e IS SELECT ename, sal FROM emp;
BEGIN
---输出修改之前的薪水
dbms_output.put_line('输出修改之前的薪水');
FOR v IN e
LOOP
dbms_output.put_line(v.ename || ',' || v.sal);
END LOOP;
OPEN c;
FETCH c INTO r;
WHILE c%FOUND
LOOP
IF r.job = 'SALESMAN' THEN
UPDATE emp SET sal = sal + 2000 WHERE CURRENT OF c;
END IF;
FETCH c INTO r;
END LOOP;
CLOSE c;
---输出修改之后的薪水
dbms_output.put_line('输出修改之后的薪水');
FOR v IN e
LOOP
dbms_output.put_line(v.ename || ',' || v.sal);
END LOOP;
END;
----------------------异常----------------
DECLARE
dname dept.dname%TYPE;
v NUMBER;
BEGIN
v := 5 / 0;
SELECT dname INTO dname FROM dept;
dbms_output.put_line(dname);
EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('除数不能为0');
WHEN too_many_rows THEN
dbms_output.put_line('行数太多');
WHEN OTHERS THEN
dbms_output.put_line('未知异常');
END;
-------------------------非预定异常---------------
DECLARE
v NUMBER;
zero EXCEPTION;
PRAGMA EXCEPTION_INIT(zero, -01476);
BEGIN
v := 5 / 0;
EXCEPTION
WHEN zero THEN
dbms_output.put_line('除数为0' || SQLCODE);
END;
------------------------自定义异常---------------
DECLARE
c INT;
e EXCEPTION;
BEGIN
SELECT COUNT(*) INTO c FROM dept;
IF c > 0 THEN
RAISE e;
END IF;
EXCEPTION
WHEN e THEN
dbms_output.put_line('错了');
END;
--第三章
------------------计算一个数字的阶乘------------
CREATE OR REPLACE PROCEDURE jc(n INT := 5)
IS
s INT DEFAULT 1;
BEGIN
FOR v IN 1..n
LOOP
s := s * v;
END LOOP;
dbms_output.put_line(s);
END;
EXECUTE jc(5);
CALL jc(5);
BEGIN
jc();
END;
-------------打印出员工的平均工龄---------
SELECT * FROM emp;
CREATE OR REPLACE PROCEDURE avg_age
IS
aa NUMBER(5, 2);
BEGIN
SELECT
avg(extract(YEAR FROM SYSDATE) -
extract(YEAR FROM hiredate)) INTO aa
FROM emp;
dbms_output.put_line('平均工龄是:' || aa);
END;
----打印出指定部门的所有员工信息,并统计员工人数,平均工资和平均工龄--
CREATE OR REPLACE PROCEDURE printDept(dn VARCHAR2)
IS
CURSOR c IS SELECT ename, sal
FROM emp WHERE deptno =
(SELECT deptno FROM dept WHERE dname = dn);
total INTEGER;
avg_sal NUMBER(18,2);
BEGIN
FOR v IN c
LOOP
dbms_output.put_line(v.ename || ',' || v.sal);
END LOOP;
SELECT COUNT(*), AVG(sal) INTO total, avg_sal
FROM emp
WHERE deptno =
(SELECT deptno FROM dept WHERE dname = dn);
dbms_output.put_line(dn || '的人数:' || total);
dbms_output.put_line('平均工资是:' || avg_sal);
avg_age;
END;
---test--
SELECT * FROM dept;
BEGIN
printDept('市场部3');
END;
-------------判断是否是素数-------------
CREATE OR REPLACE PROCEDURE isPrime(n INT, f OUT BOOLEAN)
IS
flag BOOLEAN := TRUE;
BEGIN
FOR v IN 2..n - 1
LOOP
IF MOD(n, v) = 0 THEN
flag := FALSE;
EXIT;
END IF;
END LOOP;
f := flag;
END;
DECLARE
flag BOOLEAN;
BEGIN
isPrime(20, flag);
dbms_output.put_line(CASE flag WHEN TRUE THEN '是' ELSE '否' END);
END;
-----------打印出两个数之间的所有素数------
CREATE OR REPLACE PROCEDURE printPrime(start_ INT, end_ INT)
IS
flag BOOLEAN;
BEGIN
FOR v IN start_..end_
LOOP
isPrime(v, flag);
IF flag THEN
dbms_output.put_line(v);
END IF;
END LOOP;
END;
BEGIN
printPrime(50, 100);
END;
SELECT * FROM user_objects WHERE object_type = 'PROCEDURE';
SELECT text FROM user_source WHERE NAME='JC';
---------------包规范:只定义不实现---------------
CREATE OR REPLACE PACKAGE t147
IS
PROCEDURE add_(n INT, m INT, r_ OUT INT);
PROCEDURE sum_4_scope(n INT, m INT, r_ OUT INT);
TYPE c IS REF CURSOR;
PROCEDURE get_depts(depts_ OUT c);
PROCEDURE print_depts(depts_ IN c);
END;
---------------包主体:实现包规范中定义的子程序----
CREATE OR REPLACE PACKAGE BODY t147
IS
PROCEDURE add_(n INT, m INT, r_ OUT INT)
IS
BEGIN
r_ := n + m;
END;
PROCEDURE sum_4_scope(n INT, m INT, r_ OUT INT)
IS
s INT := 0;
BEGIN
FOR v IN n..m
LOOP
s := s + v;
END LOOP;
r_ := s;
END;
PROCEDURE get_depts(depts_ OUT c)
IS
BEGIN
OPEN depts_ FOR SELECT * FROM dept;
END;
PROCEDURE print_depts(depts_ IN c)
IS
r dept%ROWTYPE;
BEGIN
FETCH depts_ INTO r;
WHILE depts_%FOUND
LOOP
dbms_output.put_line(r.dname);
FETCH depts_ INTO r;
END LOOP;
END;
END;
DECLARE
r_ INT;
c_ t147.c;
r dept%ROWTYPE;
c2_ t147.c;
BEGIN
t147.add_(10, 20, r_);
dbms_output.put_line(r_);
t147.sum_4_scope(1, 100, r_);
dbms_output.put_line(r_);
t147.get_depts(c_);
FETCH c_ INTO r;
WHILE c_%FOUND
LOOP
dbms_output.put_line(r.dname);
FETCH c_ INTO r;
END LOOP;
CLOSE c_;
OPEN c2_ FOR SELECT * FROM dept;
t147.print_depts(c2_);
CLOSE c2_;
END;
---------------函数--------------
CREATE OR REPLACE FUNCTION hello(n VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN n || ',你好';
END;
DECLARE
v VARCHAR2(20);
BEGIN
v := hello('世界');
dbms_output.put_line(v);
END;
SELECT hello(ename) FROM emp;
SELECT decode(1, 3, '哈哈', 2, '嘿嘿', '呵呵') FROM dual;
----计算员工的工龄-----
CREATE OR REPLACE FUNCTION calc_year(hiredate DATE)
RETURN NUMBER
IS
BEGIN
RETURN extract(YEAR FROM SYSDATE) - extract(YEAR FROM hiredate);
END;
SELECT hiredate, calc_year(hiredate) FROM emp;