2016年06月_happy一个世纪

10月 09月 08月 07月 06月 05月 04月 03月 01月

原创 24PL_SQL之组织机构树状展现

create or replace procedure p(v_empno emp.empno%type,level binary_integer) is cursor c is select * from emp where emp.mgr=v_empno; v_preStr varchar(1024):=''; v_king c%rowtype;begin

2016-06-27 09:52:26 496

原创 23PL_SQL过程之建立触发器

首先创建一张表create table emp_log( uname varchar2(20), action varchar2(10), atime date)然后建立触发器create or replace trigger trig after insert or delete or update on emp for each rowbegin

2016-06-27 09:51:59 326

原创 22PL_SQL过程之建立触发器

这是触发器的一个副作用，用来更新被某个外键参考的主键create or replace trigger triggerm after update on dept for each rowbegin update emp set deptno=:new.deptno where deptno=:old.deptno; //commit;end;注意

2016-06-27 09:51:43 375

原创 21PL_SQL过程之函数

2016-06-27 09:51:20 271

原创 20PL_SQL过程之带参数的存储过程

create or replace procedure proc1(v_a in number,v_b number,v_ret out number,v_temp in out number)is begin if(v_a>v_b) then v_ret:=v_a; else v_ret:=v_b; end if;

2016-06-27 09:51:02 725

原创 19PL_SQL过程之创建存储过程

create or replace procedure proc1is cursor c is select * from emp for update;begin for v_emp in c loop if(v_emp.deptno=10) then update emp set sal=sal+10 where current of c

2016-06-26 21:20:01 430

原创 18PL_SQL过程之可更新的游标

declare cursor c is select * from emp for update; v_temp c%rowtype;begin for v_temp in c loop if(v_temp.sal update emp set sal=sal*2 where current of c; elsif(v_temp.

2016-06-26 21:19:06 391

原创 17PL_SQL过程之带参数的游标

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; --v_temp c%rowtype;begin for v_temp in c(30,'CLERK') loo

2016-06-26 21:18:35 391

原创 16PL_SQL过程之在for和while循环中使用游标

declare cursor c is select * from emp; v_temp c%rowtype;begin open c; fetch c into v_temp; while(c%found) loop dbms_output.put_line(v_temp.ename); fetch c into v_temp;

2016-06-26 20:57:44 730

原创 15PL_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;//使用游标declare cursor c is

2016-06-26 20:28:04 374

原创 14PL_SQL之用表存储错误信息

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创建一个序列d

2016-06-26 20:26:33 338

原创 13PL_SQL过程之错误处理即捕获异常

declare v_temp number(4);begin select empno into v_temp from emp where deptno=10; exception when too_many_rows then dbms_output.put_line('太多记录了'); when others then d

2016-06-26 20:26:03 426

原创 12PL_SQl过程之三种循环

循环do whiledeclare i binary_integer := 1;begin loop dbms_output.put_line(i); i:=i+1; exit when(i>=11); end loop;end;while循环declare j binary_integer := 1;begin

2016-06-26 20:25:09 559

原创 11PL_SQL过程之创建表

begin execute immediate 'create table hello(nnn varchar2(20) default''aaa'')';end;需要加上execute immediate 关键字

2016-06-26 20:24:26 370

原创 10PL_SQL过程之执行if语句

declare v_sal emp.sal%type;begin select sal into v_sal from emp where empno = 7369; if(v_sal dbms_output.put_line('low'); elsif(v_sal=1200) then dbms_output.put_line('middle

2016-06-26 20:19:47 707

原创 09PL_SQL过程之执行insert语句

declare v_deptno dept.deptno%type := 50; v_dname dept.dname%type := 'aaaa'; v_loc dept.loc%type := 'bj';begin insert into dept values(v_deptno,v_dname,v_loc); commit;end;执行更新、

2016-06-26 20:17:19 773

原创 08PL_SQL过程之执行select语句

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;需要注意的是执

2016-06-26 20:16:48 563

原创 07PL_SQL过程之复杂数据类型之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 := 10; v_t

2016-06-26 20:16:15 361

原创 06PL_SQL过程之复杂数据类型Table

declare type type_table_emp is table of emp.empno%type index by binary_integer; v_empnos type_table_emp;begin v_empnos(0) := 7369; v_empnos(1) := 7839; v_empnos(-1) := 9999; dbm

2016-06-26 20:15:35 411

原创 05PL_SQL过程之使用%type声明变量

declare v_empno number(4); v_empno2 emp.empno%type; v_empno3 v_empno2%type;begin dbms_output.put_line('test');end;%type的含义是什么什么的类型可以是某个表的某个字段的类型可以使某个变量的类型

2016-06-26 20:14:52 400

原创 04PL-SQL过程之声明各种简单变量

declare v_temp number(2); 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 va

2016-06-26 20:14:18 406

原创 03PL_SQL过程之捕获异常

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;

2016-06-26 20:13:42 314

原创 02PL_SQL过程之声明变量

declare v_name varchar2(50) := 'My name is yangliqiang!';begin dbms_output.put_line(v_name);end;上面这种方式是声明变量的同时对变量进行初始化declare v_name varchar2(50);begin v_name := 'My name is yang

2016-06-26 20:12:56 334