javaoracleplsql笔记尚学堂马士兵老师.docx

1、javaoracleplsql笔记尚学堂马士兵老师java-oracle,plsql笔记-【尚学堂马士兵老师】PL/SQLdeclare v_name varchar2(20);begin v_name := myname;dbms_output.put_line(v_name);end;捕获异常declare v_num number := 0;begin v_num :=2/v_num; dbms_output.put_line(v_num);exception when others then dbms_output.put_line(error);end;变量声明declare v_t

2、emp number(1); v_count binary_integer := 0; v_sal number(7,2) := 4000.00; v_date date := sysdate; v_pi constant number(3,2) :=3.14; v_valid boolean := false; v_name varchar2(20) not null := Myname;begin dbms_output.put_line(v_temp value: | v_temp);end;-变量声明的规则 /PL_SQL中两个横线可以注释掉一行1、变量声明不能够使用保留字，如from

3、、select等2、第一个字符必须是字母3、变量名最多包含30个字符4、不要与数据库的表或者列同名5、每一行只能声明一个变量-常用变量类型1、binary_integer:整数，主要用来计数而不是用来表示字段类型，数组下标2、number：数字类型3、char：定长字符串4、varchar2：变长字符串5、date：日期6、long：长字符串，最长2G7、boolean：布尔类型，可以取值为true、false和null值，不给初值是null-变量声明，使用%type属性declare v_empno number(4); v_empno2 emp.empno%type; -变量v_empno

4、2的类型参考emp.empno类型 v_empno3 v_empno2%type;begin dbms_output.put_line(Test);end;-Table变量类型(数组)declare type table_emp_empno is table of emp.empno%type index by binary_integer; v_empnos type_table_emp_empno;begin v_empnos(0) :=7369; v_empnos(2) :=7888; v_empnos(-1) :=7323; dbms_output.put_line(v_empnos(

5、-1);end;-Record变量类型(类似于类)declare type type_record_dept is record ( deptno dept.deptno%type, dname dept.dname%type, loc dept.loc%type ); v_temp type_record_dept;begin v_temp.deptno := 50； v_temp.dname := aaa; v_temp.loc := bj; dbms_output.put_line(v_temp.deptno | |v_temp.dname);end;-使用rowtype声明record

6、变量（无论一个表怎么变，不关心，只关心这个表的表名，类似于java中的声明类的实例）declare v_temp dept%rowtype;begin v_temp.deptno := 50; v_temp.dname := aaa; v_temp.loc := bj; dbms_output.put_line(v_temp.deptno | |v_temp.dname);end;-SQL语句的运用-PL/SQL用select语句必须并且只能返回一条记录declare v_ename emp.ename%type; v_sal emp.sal%type;begin select ename,s

7、al into v_ename,v_sal from emp where empno=7369; dbms_output.put_line(v_ename | |v_sal);end;-UPDATEdeclare v_deptno emp2.deptno%type := 50; v_count number;begin update emp2 set sal =sal/2 where deptno = v_deptno; -select deptno into v_deptno from emp2 where empno = 7369; -select count(*) into v_coun

8、t from emp2; -几条记录被影响，就是产生几个值 dbms_output.put_line(sql%rowcount |条记录被影响); -commit必须提交 commit;end;-if语句-取出7369的薪水，如果1200，则输出low,r如果2000则输出middle,否则输出highdeclare v_sal emp.sal%type;begin select sal into v_sal from emp where empno=7369; if (v_sal 1200) then dbms_output.put_line(low); elsif(v_sal =11);

9、end loop;end;declare i binary_integer := 1;begin while i 11 loop dbms_output.put_line(i); i := i+1; end loop;end;declare i binary_integer := 1;begin for i in 1.10 loop dbms_output.put_line(i); end loop;end;-错误处理declare v_temp number(4);begin select empno into v_temp from emp where deptno=10;exceptio

10、n when too_many_rows then dbms_output.put_line(太多记录了); when others then dbms_output.put_line(error);end;-把错误信息保存到一个表中-创建表create table errorlog(int number primary key,errorcode number,errormsg varchar2(1024),errordate date)-创建索引create sequence seq_errorlog_id start with 1 increment by 1;-例子declare v_

11、deptno emp.deptno%type :=10; v_errorcode number; v_errormsg varchar2(1024);begin delete from dept where deptno = v_deptno; commit;exception when others then rollback; v_errorcode := SQLCODE; v_errormsg := SQLERRM; insert into errorlog values (seq_errorlog_id.nextval,v_errorcode,v_errormsg,sysdate);

12、commit;end;-当不显示打印结过的时候执行set serveroutput on;-PL/SQL重点-游标declare cursor c is select * from emp; v_emp c%rowtype;begin open c; fetch c into v_emp; dbms_output.put_line(v_emp.ename); close c;end;-游标初始的时指到第一条记录，然后一条一条的往下走-loopdeclare cursor c is select * from emp; v_emp c%rowtype;begin open c; loop -运行

13、完fetch自动往下走一条 fetch c into v_emp; exit when (c%notfound); dbms_output.put_line(v_emp.ename); end loop; close c;end;-while循环declare cursor c is select * from emp; v_emp c%rowtype;begin open c; fetch c into v_emp; while(c%found) loop dbms_output.put_line(v_emp.ename); fetch c into v_emp; end loop; clo

14、se c;end;-for循环declare cursor c is select * from emp; v_emp c%rowtype;begin for v_temp in c loop dbms_output.put_line(v_emp.ename); end loop;end;-带参数的游标declare cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is select ename,sal from emp where deptno = v_deptno and job = v_job;begin for v_temp

15、in c(30,CLERK) loop dbms_output.put_line(v_temp.ename); end loop;end;-可更新的游标declare cursor c is select * from emp2 for update;begin for v_temp in c loop if (v_temp.sal v_b) then v_ret := -v_a; else v_ret := -v_b; end if; v_temp := v_temp+1;end;-调用过程declare v_a number := 3; v_b number := 4; v_ret num

16、ber; v_temp number :=5;begin p(v_a,v_b,v_ret,v_temp); dbms_output.put_line(v_ret); dbms_output.put_line(v_temp);end;-显示错误show error-函数create or replace function sal_tax (v_sal number) return numberisbegin if(v_sal 2000) then return 0.10; elsif(v_sal 2750) then return 0.15; else return 0.20; end if;e

17、nd;-触发器（概念牢牢掌握）create table emp2_log(uname varchar2(20),action varchar2(10),atime date);-after/beforecreate or replace trigger trig -for each row after insert or delete or update on emp2 for each rowbegin if inserting then insert into emp2_log values(USER,insert,sysdate); elsif updating then insert

18、into emp2_log values(USER,update,sysdate); elsif deleting then insert into emp2_log values(USER,delete,sysdate); end if;end;update emp2 set sal = sal*2 where deptno=10;-更新有参考列create or replace trigger trig after update on dept for each rowbegin update emp set deptno = :NEW.deptno where deptno = :OLD

19、.deptno;end;-递归-数状结构的存储与展示create table article( id number primary key, cont varchar2(4000), pid number, isleaf number(1),-0 代表非叶子节点，1代表叶子节点 alevel number(2);insert into article values(1,蚂蚁战大象,1,0,0);insert into article values(2,蚂蚁战大象2,1,0,1);insert into article values(3,蚂蚁战大象3,2,1,2);commit;create o

20、r replace procedure p(v_pid article.pid%type,v_level binary_integer) is cursor c is select * from article where pid=v_pid; v_preStr varchar2(1024) := ;begin for i in 1.v_level loop v_preStr := v_preStr |*; end loop; for v_article in c loop dbms_output.put_line(v_preStr | v_article.cont); if (v_artic

21、le.isleaf = 0) then p(v_article.pid,v_level+1); end if; end loop;end;第一课：客户端1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。2. 从开始程序运行:sqlplus，是图形版的sqlplus.3. http:/localhost:5560/isqlplusToad：管理， PlSql Developer:第二课：更改用户1. sqlplus sys/bjsxt as sysdba2. alter user scott account unlock;(解锁)第三课：table s

22、tructure1. 描述某一张表：desc 表名2. select * from 表名第四课：select 语句：1.计算数据可以用空表：比如：.select 2*3 from dual2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 annual sal from emp;区别，加双引号保持原大小写。不加全变大写。3. select ename | abcd 如果连接字符串中含有单引号，用两个单引号代替一个单引号。第五课：distinctselect deptno from emp;select distinct

23、deptno from emp;select distinct deptno from emp;select distinct deptno ,job from emp去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。第六课：Whereselect * from emp where deptno =10;select * from emp where deptno 10;不等于10 select * from emp where ename =bike;select ename,sal from emp where sal between 800 and 150

24、0 (=800 and 800order by sal desc;select lower(ename) from emp;select ename from empwhere lower(ename) like _a%;等同于select ename from emp where ename like _a% or ename like _A%;select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.select chr(65) from dual 结果为:Aselect ascii(a) from dual 结果为:65select round(23.652,1) from dual; 结果为: 23.7select round(23.652,-1) from dual; 20select to_char(sal,$99_999_999) from emp;select to_char(sal,L99_999_999) from emp;人民币符号,L:代表本地符号这个需要掌握牢:select birthdate from emp;显示为:BIRTHDATE-17-12月-80-改为:select to_char(birthdate,YYYY-MM-DD HH:MI:SS) from emp;