Oracle PLSQL语句实例

/** * plsql:某个项目对效率要求比较高的时候用，一般不用，大多数项目写的是夸数据库平台的，用不上。 * pssql大多数能做的东西，java都能替代它。在某些特殊的地方要求用plsql的时候你才会用。 *  * 变量声明的规则： * 1、变量名不能使用保留字，如from、select等 * 2、第一个字符必须是字母 * 3、变量名最多包含30个字符 * 4、不要与数据库的表或者列同名 * 5、每一行只能声明一个变量 *  * 常用变量类型 * 1、 binary_integer:整数，主要用于计数而不是用来表示字段类型 * （Oracle本身非常注重效率，他的很多设计都是为了效率） * 2、number：数字类型 * 3、char：定长字符串 * 4、varchar2:变长字符串 * 5、date：日期 * 6、long：长字符创，最长2GB * 7、boolean：布尔类型，可以取为true、false和null值 *  */set serveroutput on;--默认是关闭的begindbms_output.put_line('HelloWorld');end;--输出HelloWorlddeclarev_name varchar2(20);beginv_name:='mynaem';dbms_output.put_line(v_name);end;declarev_num number :=0;beginv_num :=2/v_num;dbms_output.put_line(v_num);exception--处理异常when others thendbms_output.put_line('error');end;declare v_tem 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; --sql里面也有常量v_valid boolean:=false; --不能打印布尔类型的值v_name varchar2(20) not null:='MyName'; --定义变量还能有限制begindbms_output.put_line('v_temp value:' || v_temp);end;declarev_empno number(4);v_empno2 emp.empno%type;--该变量的类型跟随另一变量的类型v_empno3 v_empno2%type;begindbms_output.put_line('Test');end --Table变量类型declare/** * type表示我定义了一种类型,名称叫tyep_table_emp_empno * is table说明这是一个数组，这个数组里面装着emp.empno%type类型的数据 * index by说明他的下表是由binary_ingeger来索引 */type tyep_table_emp_empno is table of emp.empno%type index by binary_ingeger;v_empnos type_table_emp_empno;beginv_empnos(0):=7369;v_empnos(2):=7839;v_empnos(-1):=9999;--在Oracle里面，这个下标值可以取负值dbms_output.put_line(v_empnos(-1));end --Record变量类型declaretype type_record_dept is record(deptno dept.deptno%type,dname dept.dname%type,loc dept.loc%type);v_temp type_record_dept;beginv_temp.deptno:=50;v_temp.dname:='aaaa';v_temp.loc:='bj';dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);end--使用%rowtype声明record变量declarev_temp dept%rowtype;--更具dept表的字段来beginv_temp.deptno:=50;v_temp.dname:='aaaa';v_temp.loc:='bj';dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);end declarev_deptno emp2.deptno%type:=10;v_count number;beginupdate emp2 set sal=sal/2 where deptno=v_deptno;--update、insert、delete可以直接用select deptno into v_deptno from emp2 where empno=7369;--select语句必须和into一起使用且保证有且只有一条记录select count(*) into v_count from emp2;dbms_output.put_line(sql%rowcount||'条记录被影响');--rowcount代表刚刚执行完的这条sql语句到底影响了多少条记录--sql代表的是刚刚执行完的那条sql语句commit;--用完之后要commitend /** * plsql中的判断 */begin--create table语句在plsql里面不能单独执行execute immediate 'create table T(nnn varchar2(20) default ''aaa'')';end declarev_sal emp.sal%type;beginselect sal into v_sal from emp where empno=7369;if(v_sal<1200) thendbms_output.put_line('low');elsif(v_sal<2000) then --小心elsif中间没有edbms_output.put_line('middle');elsedbms_output.put_line('high');end if;end /** * plsql 中的循环 */declarei binary_integer:=1;beginloop --plsql里面的循环以loop开头，以end loop结尾dbms_output.put_line(i);i:=i+1;exit when(i>=11);end loop;end declarej binary_integer:=1;beginwhile j<11 loopdbms_output.put_line(j);j:=j+1;end loop;end beginfor k in 1..10 loop --相当于java里面增强的for循环dbms_output.put_line(k);end loop;for k in reverse 1..10 loopdbms_output.put_line(k);end loop;end /** * plsql里面的错误处理 * too_many_rows, no_data_found * 这个东西了解下就够了 */declarev_temp number(4);beginselect empno into v_temp from emp where deptno=10;exceptionwhen too_many_rows thendbms_output.put_line('太多条记录了');when others thendbms_output.put_line('error');end /** * 创建记录错误的日志表 */create table errorlog(id number primary key,errcode number,errmsg varchar2(1024),errdate date);create sequence seq_errorlog_id start with 1 increment by 1;declarev_deptno dept.deptno%type :=10;v_errcode number;v_errmsg varchar2(1024);begindelete from dept where deptno=v_deptno;commit;exceptionwhen others thenrollback;--捕获到异常后首先要回滚v_errcode:=SQLCODE;--SQLCODE代表出错的代码，Oracle里面出错代码都是负数v_errmsg:=SQLERRM;--SQLERRM代表出错信息insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);commit;end /** * 游标 */declare--loop循环cursor c is select * from emp;v_emp c%rowtype; --将v_emp定义为游标c的类型beginopen c;loopfetch c into v_emp;exit when (c%notfound)--如果找不着记录就返回dbms_output.put_line(v_emp.ename);end loop;close c;end declare--while循环cursor c is select * from emp;v_emp c%rowtype; --将v_emp定义为游标c的类型beginopen c;fetch c into v_emp;while(c%found) loopdbms_output.put_line(v_emp.ename);fetch c into v_emp;end loop;close c;end declare--for循环:cursor 不需要打开或者关闭，所以for循环是我们平时用得最多的循环cursor c is select * from emp;beginfor v_emp in c loop;dbms_output.put_line(v_emp.ename);end loop;end--带参数的游标declarecursor c(v_deptno emp.deptno%type, v_job emp.job%type)isselect ename,sal from emp where deptno=v_deptno and job=v_job;beginfor v_temp in c(30,'CLERK') loopdbms_output.put_line(v_temp.ename);end loop;end--大多数的游标是用来读取的，也有部分游标是用来更新的（只需了解）declarecursor cisselect * from emp2 for update;beginfor v_temp in c loopif(v_temp.sal<2000)thenupdate emp2 set sal=sal*2 where current of c;elseif(v_temp.sal=5000)thendelete from emp2 where current of c;end if;end loop;commit;end/** * 创建存储过程(存储过程创建了不代表她已经执行了) * Oracle的存储过程，即便我们有语法错误，依然会被创建。执行的时候会报错。 */create or replace procedure piscursor c isselect * from emp2 for update;beginfor v_temp in c loopif(v_temp.sal<2000)thenupdate emp2 set sal=sal*2 where current of c;elseif(v_temp.sal=5000)thendelete from emp2 where current of c;end if;end loop;commit;endexec p;--执行存储过程/** * 带参数的存储过程 *  in是传入参数 * out是传出参数（存储过程是没有返回值的） *  如果没有写，默认是in *  in out 即传入又传出 */create or replace procedure p(v_a in number, v_b number, v_ret out number, v_temp in out number)isbeginif(v_a>v_b)thenv_ret:=v_a;elsev_ret:=v_b;end if;v_temp:=v_temp+1;end --调用过程declarev_a number:=3;v_b number:=4;v_ret number;v_temp number:=5;beginp(v_a,v_b,v_ret,v_temp);dbms_output.put_line(v_ret);dbms_output.put_line(v_temp);end /** * 创建函数 */create or replace function sal_tax(v_sal number)return numberisbeginif(v_sal < 2000) thenreturn 0.10;elseif(v_sal<2750) thenreturn 0.15;elsereturn 0.20;end ifend --调用函数select lower(ename),sal_tax(sal)from emp;/** * 创建触发器 */create tabel emp2_log(uname varchar2(20),action varchar2(10),atime date);create or replace trigger trig/** * 触发器不能直接执行，必须依附在某张表上 * 可以有after也可以有before insert * 如果没有for each row则整个操作会被触发一次 * 如果用for each row，如果一个操作更新了六行则被触发六次 */after insert or delete or update on emp2 for each rowbeginif inserting theninsert into emp2_log values(USER,'insert',sysdate);elseif updateing theninsert into emp2_log values(USER,'update',sysdate);elseif deleting theninsert into emp2_log values(USER,'delete',sysdate);end if;end /** * 触发器的一个副作用（不重要，了解即可。万不得已的情况下用） * 从下面例子可以看出当我们update一个语句的时候 * 是先触发触发器 */update dept set deptno=99 where deptno=10;--这样做会报错，违反了完整约束条件--建立下面触发器以后就可以了（因为对参考dept表的emp表里面的值也做了修改）create or replace trigger trigafter update on deptfor each rowbeginupdate emp set deptno=:NEW.deptno where deptno=:OLD.deptno;end /** * 树状结构的存储与展示 */create table article(id number primary key,cont varchar2(4000),pid number,isleaf number(1), --0代表非叶子节点，1代表叶子节点alevel number(2));--查询上表中某条记录的孩子节点create or replace procedure p(v_pid article.pid%type,v_level binary_integer) iscursor c is select * from article where pid=v_pid;v_preStr varchar2(1024):='';beginfor i in 1..v_level loopv_preStr:=v_preStr||'***';end loopfor v_article in c loopdbms_output.put_line(v_preStr||v_article.cont);if(v_article.isleaf=0) thenp(v_article.id,v_level+1);end if;end loop;end