PLSQL 笔记

oracle的左连接或右连接 以下是解释，自己研究下： ------------------------------------------------------------------- 数据表的连接有: 1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现 2、外连接: 包括 （1）左外连接(左边的表不加限制) （2）右外连接(右边的表不加限制) （3）全外连接(左右两表都不加限制) 3、自连接(连接发生在一张基表内) select a.studentno, a.studentname, b.classname from students a, classes b where a.classid(+) = b.classid; STUDENTNO STUDENTNAM CLASSNAME ---------- ---------- ------------------------------ 1 周虎 一年级一班 2 周林 一年级二班 一年级三班 以上语句是右连接： 即"(+)"所在位置的另一侧为连接的方向，右连接说明等号右侧的所有 记录均会被显示，无论其在左侧是否得到匹配。也就是说上例中，无 论会不会出现某个班级没有一个学生的情况，这个班级的名字都会在 查询结构中出现。
反之： select a.studentno, a.studentname, b.classname from students a, classes b where a.classid = b.classid(+);
STUDENTNO STUDENTNAM CLASSNAME ---------- ---------- ------------------------------ 1 周虎 一年级一班 2 周林 一年级二班 3 钟林达
则是左连接，无论这个学生有没有一个能在一个班级中得到匹配的部门号， 这个学生的记录都会被显示。
select a.studentno, a.studentname, b.classname from students a, classes b where a.classid = b.classid;
这个则是通常用到的内连接,显示两表都符合条件的记录
总之,
左连接显示左边全部的和右边与左边相同的 右连接显示右边全部的和左边与右边相同的 内连接是只显示满足条件的!

 

 

create or replace procedure insert_procedure is

begin  

insert into employee(id,username,password,birthday,email,address) values(5,'dog','dog123',to_date('2010-10-15 12:15:01','YYYY-MM-DD hh:mi:ss'),'dog@126.com','China,Henan');

end;

/ 

create or replace procedure delete_procedure is

begin  

delete from employee where id = 5;

end;

/

create or replace procedure delete_procedure(p_id number) is

begin  

delete from employee where id = p_id;

end;

/

begin  

dbms_output.put_line('Hello, world');

end;

create or replace procedure name_procedure(v_id number) is  

v_name employee.username%type;

begin  

select username into v_name from employee where id = v_id;  

dbms_output.put_line('username is ' || v_name);

end; /

create or replace procedure name_exception_procedure(v_id number) is  

v_name employee.username%type;

begin  

select username into v_name from employee where id = v_id;  

dbms_output.put_line('username is ' || v_name);

exception  

when no_data_found then    

dbms_output.put_line('根据ID找不到相关记录');

end;

/

create or replace procedure name_many_procedure(v_id number) is  

v_username employee.username%type;

begin  

select username into v_username from employee where id = v_id;

exception  

when too_many_rows then    

dbms_output.put_line('返回记录数过多');

end;

/

create or replace procedure update_procedure(v_id in number,v_username in varchar2,v_password in varchar2) is

begin  

update employee set employee.password = v_password,employee.username = v_username where employee.id = v_id;

end;

/

create or replace function password_function(v_username in varchar2)

return varchar2 is v_password employee.password%type;

begin  

select employee.password into v_password from employee where employee.username = v_username; return v_password;

Exception   when no_data_found then    

dbms_output.put_line('用户名不存在 !');    

return null;

end;

/

create or replace package sys_package is   procedure update_procedure(v_username in varchar2);  

function select_function(v_username in varchar2) return varchar2;

end;

/

create or replace package body sys_package is   procedure update_procedure(v_username in varchar2) is    

begin      

update employee set employee.username = v_username where employee.id = 1;    

end;    

function select_function(v_username in varchar2)

return varchar2 is     v_password employee.password%type;    

begin      

select employee.password into v_password from employee where employee.username = v_username;      

return v_password;    

end;

end; /

declare  

type employee_record_type is     record(v_username employee.username%type, v_password employee.password%type, v_email employee.email%type);   employee_record employee_record_type;

begin  

select employee.username, employee.password, employee.email into employee_record from employee where employee.id = 1;  

dbms_output.put_line('username :' || employee_record.v_username || 'password :' || employee_record.v_password || 'email :' || employee_record.v_email);

end;

/

declare   type employee_cursor_type is ref cursor;

employee_cursor employee_cursor_type;  

v_username employee.username%type;  

v_password employee.password%type;

begin  

open employee_cursor for     select employee.username, employee.password from employee where employee.id = 5;  

loop    

fetch employee_cursor into v_username,v_password;  

exit when employee_cursor%notfound;    

dbms_output.put_line('username :' || v_username || 'password :' || v_password);  

end loop;  

close employee_cursor;

end;

/

create or replace procedure if_employee_procedure(v_id number) is v_username employee.username%type;

begin   select employee.username into v_username from employee where employee.id = v_id;  

if length(v_username) < 11 then    

update employee set employee.username = rpad(v_username, 11, 0) where employee.id = v_id;  

end if;

end;

/

create or replace procedure if_else_employee_procedure(v_id in number) is v_username employee.username%type;

begin  

select employee.username into v_username from employee where employee.id = v_id;  

if length(v_username) < 10 then    

update employee set employee.username = rpad(v_username, 18, 0) where id = v_id;  

else    

update employee set employee.username = rpad(v_username, 15, 0) where id = v_id;  

end if;

end;

/

create or replace procedure loop_employee_procedure is

v_id number:=6;

begin  

loop    

insert into employee values(v_id, 'ding', 'ding', to_date('2012-10-06 10:15:27','yyyy-mm-dd hh:mi:ss'), 'ding@126.com', 'China');  

exit when v_id = 15;  

v_id:=v_id + 1;  

end loop;

end;

/

create or replace procedure while_procedure is

v_id number := 16;

begin   while v_id < 30 loop    

insert into employee values(v_id, 'dys','dys456',to_date('1988-10-15 15:24:18','yyyy-mm-dd hh24:mi:ss'),'dys456@126.com','China ,hebei');     v_id:=v_id+1;  

end loop;

end;

/

 

create or replace procedure return_procedure(v_id in number,v_username out varchar2) is begin

select employee.username into v_username from employee where employee.id = v_id;

end;

/

create or replace package myPackage is  

type v_employee_cursor is ref cursor;

end;

/

create or replace procedure ref_cursor_procedure(v_id in number,v_out_result out myPackage.v_employee_cursor) is

begin  

open v_out_result for     select * from employee where employee.id = v_id;

end;

/

create or replace procedure fenYeProcedure(v_in_table in varchar2,v_in_pageSize in number,v_in_pageNow in number,v_out_result out myPackage.v_employee_cursor,v_out_totalRows out number,v_out_pageCount out number) is

v_sql varchar2(2000);

v_start_row number;

v_end_row number;

begin  

v_start_row:=v_in_pageSize*(v_in_pageNow-1)+1;  

v_end_row:=v_in_pageSize*v_in_pageNow;  

v_sql:='select t2.* from (select t.*, rownum rn from (select * from ' || v_in_table || ') t where rownum <= ' || v_end_row || ') t2 where t2.rn >= '|| v_start_row;   open v_out_result for v_sql;  

select count(*) into v_out_totalRows from emp;  

if mod(v_out_totalRows,v_in_pageSize)=0 then

v_out_pageCount:=v_out_totalRows/v_in_pageSize;  

else    

v_out_pageCount:=v_out_totalRows/v_in_pageSize+1;  

end if;

end;

/

 

create or replace view employee_view as select * from employee; /

 

create or replace view employee_view as select * from employee with read only; /

 

create or replace trigger insert_trigger

after insert on scott.employee

begin  

dbms_output.put_line('添加了一条记录');

end;

/

insert into employee values(110,'abcdefg','abcdefg123',to_date('2011-12-14 12:45:36','yyyy-mm-dd hh:mi:ss'),'abcdefg@126.com','China,toString');

create or replace trigger update_trigger

after update on scott.employee

for each row begin  

dbms_output.put_line('更改了一条数据');

end;

/

create or replace trigger before_brigger

before delete on scott.employee begin  

if to_char(sysdate,'day') in ('星期日','星期六','星期四') then    

raise_application_error('-20001','星期天不能删除员工信息!');  

end if;

end;

/

create or replace trigger all_trigger

before insert or update or delete on scott.employee begin  

case     when inserting then      

dbms_output.put_line('请不要添加');      

raise_application_error(-20002,'不能添加数据');    

when updating then      

dbms_output.put_line('请不要修改');      

raise_application_error(-20003,'不能修改数据');   

when deleting then     

dbms_output.put_line('请不要删除');     

raise_application_error(-20004,'不能删除数据');  

end case;

end;

/

create or replace trigger new_old_trigger

before update on scott.employee for each row

begin   if :new.id < :old.id then    

dbms_output.put_line('ID不能变小');    

raise_application_error(-20005,'ID不能小于原来');   

else    

dbms_output.put_line('原来ID是 : ' || :old.id || '新的ID是 : ' || :new.id);  

end if;

end;

/

create or replace trigger backup_trigger

before delete on scott.employee for each row

begin  

insert into employee_backup values (:old.id,:old.username);

end;

/

create or replace trigger limit_trigger

before update on scott.employee for each row begin  

if (:new.id<:old.id or :new.id>:old.id*2) then    

dbms_output.put_line('ID范围不对!');    

raise_application_error(-20006,'ID范围不合理');  

end if;   

end;

/

create or replace trigger limit_age_trigger

before insert on scott.employee for each row begin  

if :new.id>200 then    

dbms_output.put_line('ID过大');    

raise_application_error(-20007,'ID值太大了');  

end if;

end;

/

create or replace trigger limit_age_trigger

before insert on scott.employee for each row

begin  

if add_months(:new.birthday,18*12)>sysdate then    

dbms_output.put_line('年龄太小了');    

raise_application_error(-20007,'对不起年龄太小了');  

end if;

end;

/

insert into employee values(120,'good','good123',to_date('1999-12-13 12:15:49','yyyy-mm-dd hh:mi:ss'),'good@126.com','China,Hubei');

 

create or replace trigger generate_birthday_trigger

before insert on scott.test for each row declare  

v_length int;  

v_date varchar2(10);

begin   v_length := length(:new.IDCard);  

if v_length = 18 then    

v_date := substr(:new.IDCard,7,8);  

elsif v_length = 15 then    

v_date := '19' || substr(:new.IDCard,7,6);  

else    

:new.birthday := null;  

end if;  

:new.birthday := to_date(v_date,'yyyy/mm/dd');

end;

/

 

create or replace procedure delete_all_procedure(v_ids in long) is v_sql long; begin   v_sql:='delete from users t where t.id in (' || v_ids || ')';   execute immediate v_sql; end;

转载于:https://www.cnblogs.com/dingyingsi/archive/2013/02/26/2933491.html