dede电影网站,seo网站排名后退,桥东网站建设,dede网站 异步生成oracle的左连接或右连接 以下是解释#xff0c;自己研究下#xff1a; ------------------------------------------------------------------- 数据表的连接有: 1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现 2、外连接: 包括 #xff08;1#xff09;左外… 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),dog126.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), ding126.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),dys456126.com,China ,hebei); v_id:v_id1; 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_pageSize1; 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),abcdefg126.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.id200 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),good126.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
