基础语法 总结

--   第一章节BEGINdbms_output.put_line('abc');END;SELECT * FROM dept;------------------------------------DECLAREa 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;-------------------------------------DECLAREc INT;BEGINSELECT COUNT(*) INTO c FROM emp;dbms_output.put_line('总员工数:' || c);END;SELECT * FROM dept;-----------------------------------------DECLAREdn dept.dname%TYPE; --dn的类型和dept表的dname列的类型一致r dept%ROWTYPE;BEGINdn := '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;-----------记录类型--------------DECLARETYPE dept_rec IS RECORD(dname VARCHAR2(20),loc VARCHAR2(200));dr dept_rec;BEGINdr.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中的集合)---------DECLARETYPE  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;BEGINdbms_output.put_line(ia(1));ia.EXTEND(1); --扩展空间,即添加新元素ia(6) := 100;FOR i IN 1..ia.COUNTLOOPdbms_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);BEGINSELECT AVG(sal) INTO avgSal FROM emp;dbms_output.put_line('平均工资:' || avgSal);IF avgSal > 5000 THENdbms_output.put_line('白领');ELSIF avgSal BETWEEN 2000 AND 5000 THENdbms_output.put_line('蓝领');ELSEdbms_output.put_line('无领');END IF;END;-----------------------CASE结构-----------------如果平均工资>5000,则输出"白领", 如果[2000,5000],是蓝领,<2000是无领DECLARE avgSal NUMBER(10, 2);r VARCHAR2(20);BEGINSELECT 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---------------DECLAREs NUMBER := 0;i INTEGER DEFAULT 1;BEGINLOOPs := s + i;i := i + 1;IF i > 100 THENEXIT;END IF;END LOOP;dbms_output.put_line(s);END;-----------------------for循环2---------------DECLAREs NUMBER := 0;i INTEGER DEFAULT 1;BEGINLOOPs := s + i;i := i + 1;EXIT WHEN i > 100;END LOOP;dbms_output.put_line(s);END;-----------------------for循环3---------------DECLAREs NUMBER := 0;i INTEGER DEFAULT 1;BEGINWHILE i <= 100LOOPs := s + i;i := i + 1;END LOOP;dbms_output.put_line(s);END;-----------------------for循环4---------------DECLAREs NUMBER := 0;BEGINFOR i IN 1..100LOOPs := s + i;END LOOP;dbms_output.put_line(s);END;--------------------for循环访问结果集-----------BEGINFOR v IN (SELECT * FROM dept)LOOPdbms_output.put_line(v.deptno || v.dname || v.loc);END LOOP;END;------------------九九乘法表---------------BEGINFOR r IN 1..9LOOPFOR c IN 1..rLOOPdbms_output.put(c || '*' || r || '=' || (c * r) || ' ');END LOOP;dbms_output.put_line('');END LOOP;END;----------------空语句-----------BEGINIF 3 = 3 THENNULL;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;BEGINdno := &请输入部门编号;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);BEGINOPEN c(10, 30);FETCH c INTO dname_, dloc_;WHILE c%FOUNDLOOPdbms_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);BEGINOPEN c;dbms_output.put_line(c%ROWCOUNT);FETCH c INTO dname_, dloc_;LOOPIF c%NOTFOUND THENEXIT;END IF;dbms_output.put_line(dname_ || ',' || dloc_);FETCH c INTO dname_, dloc_;END LOOP;dbms_output.put_line(c%ROWCOUNT);CLOSE c;END;------------------循环游标----------------------BEGINFOR v IN (SELECT * FROM dept)LOOPdbms_output.put_line(v.deptno || ',' || v.dname || ',' || v.loc);END LOOP;END;------------------------------------------------DECLARECURSOR c(m INT, n INT) IS SELECT * FROM dept WHERE deptno BETWEEN m AND n;BEGINFOR v IN c(10, 30)LOOPdbms_output.put_line(v.deptno || ',' || v.dname || ',' || v.loc);END LOOP;END;-------------------显示游标一次遍历所有行-----DECLARECURSOR c IS SELECT dname, deptno FROM dept;TYPE dnames IS TABLE OF VARCHAR2(20);dns dnames;TYPE deptnos IS TABLE OF NUMBER;des deptnos;BEGINOPEN c;FETCH c BULK COLLECT INTO dns, des;FOR v IN 1..dns.COUNTLOOPdbms_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);BEGINOPEN dc FOR SELECT * FROM dept;FETCH dc INTO a, b, c;WHILE dc%FOUNDLOOPdbms_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;BEGINOPEN dc FOR SELECT * FROM dept;FETCH dc INTO r;WHILE dc%FOUNDLOOPdbms_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;BEGINOPEN dc FOR SELECT dname, loc FROM dept;FETCH dc INTO r;WHILE dc%FOUNDLOOPdbms_output.put_line(r.dname || r.loc);FETCH dc INTO r;END LOOP;CLOSE dc;END;-------------如果平均工资>3000,显示部门名称,否则员工名称---DECLARETYPE c IS REF CURSOR;c_ c;name_ VARCHAR2(20);avg_sal NUMBER(18,2);BEGINSELECT AVG(sal) INTO avg_sal FROM emp;dbms_output.put_line('平均工资:' || avg_sal);IF avg_sal > 6000 THENOPEN c_ FOR SELECT dname FROM dept;ELSEOPEN c_ FOR SELECT ename FROM emp WHERE MOD(empno, 2) = 0;END IF;FETCH c_ INTO name_;WHILE c_%FOUNDLOOPdbms_output.put_line(name_);FETCH c_ INTO name_;END LOOP;CLOSE c_;END;-------------------动态SQL引用游标--------------DECLARETYPE c IS REF CURSOR;c_ c;r dept%ROWTYPE;sql_ VARCHAR2(400);BEGINsql_ := 'select * from dept where deptno > :1 and deptno <:2';OPEN c_ FOR sql_ USING 10, 50;FETCH c_ INTO r;WHILE c_%FOUNDLOOPdbms_output.put_line(r.dname);FETCH c_ INTO r;END LOOP;CLOSE c_;END;-----------------------游标表达式------------DECLARETYPE 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;BEGINOPEN d;FETCH d INTO dname, c_;WHILE d%FOUNDLOOPdbms_output.put_line(dname);FETCH c_ INTO ename;WHILE c_%FOUNDLOOPdbms_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 eLOOP dbms_output.put_line(v.ename || ',' || v.sal);END LOOP;OPEN c;FETCH c INTO r;WHILE c%FOUNDLOOPIF r.job = 'SALESMAN' THENUPDATE 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 eLOOP dbms_output.put_line(v.ename || ',' || v.sal);END LOOP;END;----------------------异常----------------DECLAREdname dept.dname%TYPE;v NUMBER;BEGINv := 5 / 0;SELECT dname INTO dname FROM dept;dbms_output.put_line(dname);EXCEPTIONWHEN ZERO_DIVIDE THENdbms_output.put_line('除数不能为0');WHEN too_many_rows THENdbms_output.put_line('行数太多');WHEN OTHERS THENdbms_output.put_line('未知异常');END;-------------------------非预定异常---------------DECLARE v NUMBER;zero EXCEPTION;PRAGMA EXCEPTION_INIT(zero, -01476);BEGINv := 5 / 0;EXCEPTIONWHEN zero THENdbms_output.put_line('除数为0' || SQLCODE);END;------------------------自定义异常---------------DECLAREc INT;e EXCEPTION;BEGINSELECT COUNT(*) INTO c FROM dept;IF c > 0 THENRAISE e;END IF;EXCEPTION WHEN e THENdbms_output.put_line('错了');END;

--第三章------------------计算一个数字的阶乘------------CREATE OR REPLACE PROCEDURE jc(n INT := 5)ISs INT DEFAULT 1;BEGINFOR v IN 1..nLOOPs := s * v;END LOOP;dbms_output.put_line(s);END;EXECUTE jc(5);CALL jc(5);BEGINjc();END;-------------打印出员工的平均工龄---------SELECT * FROM emp;CREATE OR REPLACE PROCEDURE  avg_ageISaa NUMBER(5, 2);BEGINSELECT 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)ISCURSOR c IS SELECT ename, salFROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = dn);total INTEGER;avg_sal NUMBER(18,2);BEGINFOR v IN cLOOPdbms_output.put_line(v.ename || ',' || v.sal);END LOOP;SELECT COUNT(*), AVG(sal) INTO total, avg_sal FROM empWHERE 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;BEGINprintDept('市场部3');END;-------------判断是否是素数-------------CREATE OR REPLACE PROCEDURE isPrime(n INT, f OUT BOOLEAN)ISflag BOOLEAN := TRUE;BEGINFOR v IN 2..n - 1LOOPIF MOD(n, v) = 0 THENflag := FALSE;EXIT;END IF;END LOOP;f := flag;END;DECLAREflag BOOLEAN;BEGINisPrime(20, flag);dbms_output.put_line(CASE flag WHEN TRUE THEN '是' ELSE '否' END);END;-----------打印出两个数之间的所有素数------CREATE OR REPLACE PROCEDURE printPrime(start_ INT, end_ INT)ISflag BOOLEAN;BEGINFOR v IN start_..end_LOOPisPrime(v, flag);IF flag THENdbms_output.put_line(v);END IF;END LOOP;END;BEGINprintPrime(50, 100);END;SELECT * FROM user_objects WHERE object_type = 'PROCEDURE';SELECT text FROM user_source WHERE NAME='JC';---------------包规范:只定义不实现---------------CREATE OR REPLACE PACKAGE t147ISPROCEDURE 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 t147ISPROCEDURE add_(n INT, m INT, r_ OUT INT)ISBEGINr_ := n + m;END;PROCEDURE sum_4_scope(n INT, m INT, r_ OUT INT)ISs INT := 0;BEGINFOR v IN n..mLOOPs := s + v;END LOOP;r_ := s;END;PROCEDURE get_depts(depts_ OUT c)ISBEGINOPEN depts_ FOR SELECT * FROM dept;END;PROCEDURE print_depts(depts_ IN c)ISr dept%ROWTYPE;BEGINFETCH depts_ INTO r;WHILE depts_%FOUNDLOOPdbms_output.put_line(r.dname);FETCH depts_ INTO r;END LOOP;END;END;DECLAREr_ INT;c_ t147.c;r dept%ROWTYPE;c2_ t147.c;BEGINt147.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_%FOUNDLOOPdbms_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 VARCHAR2ISBEGINRETURN n || ',你好';END;DECLAREv VARCHAR2(20);BEGINv := 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 NUMBERISBEGINRETURN extract(YEAR FROM SYSDATE) - extract(YEAR FROM hiredate);END;SELECT hiredate, calc_year(hiredate) FROM emp;