PL_SQL

编程技术  /  houtizong 发布于 3年前   78

PL_SQL 学习笔记，包括游标、存储过程、函数、触发器等。

 

/*--变量声明的规则1.    变量名不能够使用保留字，如from、select2.    第一个字符必须是字母3.    变量名最多包含30个字符4.    不要与数据库的表或者列同名5.    每一行只能声明一个变量--常用变量类型1.    binary_integer: 整数，主要用来计数而不是用来表示字段类型2.    number: 数字类型3.    char:  定长字符串4.    varchar2: 变长字符串5.    date:  日期6.    long:  长字符串，最长2GB7.    boolean:布尔类型，可以取值true、false、和null值*/set serveroutput on;begin    dbms_output.put_line('HelloWorld');end;/declare   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_temp  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_count value:' || v_count);end;/--注意：dbms_output.put_line不能直接打印boolean类型。--变量声明，使用%typedeclare   v_empno number(4);   v_empno2  emp.empno%type;   v_empno3  v_empno2%type;begin   dbms_output.put_line('Test');end;/--简单变量赋值declare     v_name varchar2(20);     v_sal number(7,2);     v_sal2 number(7,2);     v_valid boolean :=false;     v_date date;begin      v_name := 'MyName';     v_sal :=23.77;     v_sal2 :=23.77;     v_valid := (v_sal = v_sal2);     v_date := to_date('2010-02-04 11:11:11','YYYY-MM-DD HH24:MI:SS');end;/--Table变量类型,类似java中的数值declare     type type_table_emp_empno is table of emp.empno%type index by binary_integer;     v_empnos  type_table_emp_empno;begin      v_empnos(0) := 6322;     v_empnos(2) := 2314;     v_empnos(-1) := 520;     dbms_output.put_line(v_empnos(-1));end;/    --Record变量类型，类似java中的类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 := '开发部';      v_temp.loc := '1612';           dbms_output.put_line(v_temp.deptno || '  ' || v_temp.dname ||'  ' || v_temp.loc);end;/--用%rowtype声明record变量类型declare      v_temp  dept%rowtype;begin      v_temp.deptno := 50;      v_temp.dname := '开发部';      v_temp.loc := '1612';           dbms_output.put_line(v_temp.deptno || '  ' || v_temp.dname ||'  ' || v_temp.loc);end;/--SQL语句的运用。注意：要有关键字into; SQL返回的记录数有且仅有一条，declare      v_ename  emp.ename%type;      v_sal    emp.sal%type;begin      select ename,sal into v_ename ,v_sal from emp where empno = 7369 ;       dbms_output.put_line(v_ename || '   ' ||v_sal);end;/--create table emp2 as select * from emp;declare      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_count from emp2;      dbms_output.put_line(sql%rowcount || '条记录被影响');      commit;end;/--DDL语句，execute immediate两个''表示' begin     execute immediate 'create table T(nnn varchar2(20) default ''aaa'')';end;--if语句declarev_sal emp.sal%type;beginselect sal into v_sal from emp where empno = 7369;if(v_sal < 1200) thendbms_output.put_line('lower');elsif(v_sal < 2000) thendbms_output.put_line('middle');elsedbms_output.put_line('higher');end if;end;/--循环类似do whiledeclarei binary_integer := 1;beginloopdbms_output.put_line(i);i := i + 1;exit when (i >= 11);end loop;end;/--循环类似whiledeclarej binary_integer := 1;beginwhile j < 11 loopdbms_output.put_line(j);j := j + 1;end loop;end;/--循环类似forbeginfor k in 1..10 loopdbms_output.put_line(k);end loop;for k in reverse 1..10 loopdbms_output.put_line(k);end loop;end;/--异常处理declarev_temp number(4);beginselect empno into v_temp from emp where deptno = 110;exceptionwhen no_data_found thendbms_output.put_line('无记录');when 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);begin        delete from dept where deptno = v_deptno;commit;exceptionwhen others thenrollback;v_errcode := SQLCODE;v_errmsg := SQLERRM;insert into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);commit;end;/--游标declarecursor c isselect * from emp;v_emp c%rowtype;beginopen c;fetch c into v_temp;dbms_output.put_line(v_emp.ename);close c;end;/declarecursor c isselect * from emp;v_emp c%rowtype;beginopen c;loopfetch c into v_emp;exit when (c%notfound);dbms_output.put_line(v_emp.ename);end loop;close c;end;/declarecursor c isselect * from emp;v_emp c%rowtype;beginopen c;fetch c into v_emp;while(c%found) loopdbms_output.put_line(v_emp.ename);fetch c into v_temp;end loop;close c;end;/--游标for循环，自动打开，关闭游标declarecursor c isselect * from emp;beginfor v_emp in c loopdbms_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_emp in c(30,'CLERK') loopdbms_output.put_line(v_emp.ename);end loop;end;/--可更新游标declarecursor c         isselect * from emp2 for update;beginfor v_emp in c loopif(v_emp.sal < 2000) thenupdate emp2 set sal = sal * 2 where current of c;elsif(v_emp.sal = 5000) thendelete from emp2 where current of c;end if;end loop;commit;end;/--存储过程create or replace procedure pis       cursor c isselect * from emp2 for update;beginfor v_emp in c loopif(v_emp.deptno = 10) thenupdate emp2 set sal = sal + 10 where current of c;elsif(v_emp.deptno = 20) thenupdate emp2 set sal = sal + 20 where current of c;elseupdate emp2 set sal = sal + 50 where current of c;end if;end loop;commit;end;/--执行存储过程,方式一exec p;--执行存储过程,方式二beginp;end;/--带参数存储过程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 numberis beginif(v_sal < 2000)thenreturn 0.10;elsif(v_sal < 2750)thenreturn 0.15;elsereturn 0.20;end if;end;/select sal_tax(sal) from emp; --触发器create table emp2_log(uname varchar2(20),action varchar2(10),atime date);create or replace trigger trigafter insert or delete or update on emp2 for each row beginif inserting then insert into emp2_log values (USER,'insert',sysdate);elsif updating then insert 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 = 30;select * from emp2_log;drop trigger trig;create or replace trigger trigafter  update on dept for each row beginupdate emp set deptno = :NEW.deptno where deptno = :OLD.deptno;end;/update dept set deptno = 99 where deptno = 30;select * from emp;rollback;--树状结构的存储与展示create table article (id number primary key,content varchar2(4000),pid number,isleaf number(1), --0代表非叶子，1代表叶子节点alevel number(2));insert into article values (1,'蚂蚁大战大象',0,0,0);insert into article values (2,'大象被打趴下了',1,0,1);insert into article values (3,'蚂蚁也不好过',2,1,2);insert into article values (4,'瞎说',2,0,2);insert into article values (5,'没有瞎说',4,1,3);insert into article values (6,'怎么可能',1,0,1);insert into article values (7,'怎么没可能',6,1,2);insert into article values (8,'可能性是很大的',6,1,2);insert into article values (9,'大象进医院了',2,0,2);insert into article values (10,'护士是蚂蚁',9,1,3);commit;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 loop;for v_article in c loopdbms_output.put_line(v_preStr || v_article.content);if(v_article.isleaf = 0) thenp(v_article.id,v_level + 1);end if;end loop;end;/exec p(0,0);