欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

首页

自己亲自做的 一些 sql语句块 state statement block function procedure 存储过程 函数 有大用

星期四, 2021-09-09 16:48 — adminshiping1

set serveroutput on;  #执行这个命令才能在plsql中有输出


DECLARE 

 eno emp.empno%TYPE ; 

 en emp.name%TYPE ; 

 mn emp.name%TYPE ; 

 dn varchar2(20) ; 

 

BEGIN 

 -- 输入的信息保存在 eno里 

 eno := &no ; 

 -- 之后根据 eno的值，对数据库进行查询操作 

 SELECT e.name,m.name,d.deptna INTO en,mn,dn FROM emp e,dept d,emp m WHERE 

e.empno=eno   AND e.deptno=d.deptno AND e.org_id=11 AND m.org_id=11 and  ROWNUM=1; 

 DBMS_OUTPUT.put_line('编号为：'||eno||'雇员的姓名为：'||en) ; 

 DBMS_OUTPUT.put_line('编号为：'||eno||'雇员的上级姓名为：'||mn) ; 

 DBMS_OUTPUT.put_line('编号为：'||eno||'雇员所在的部门：'||dn) ; 

 

EXCEPTION 

 WHEN no_data_found THEN 

  DBMS_OUTPUT.put_line('没有此雇员') ; 

END ; 

/ 




DECLARE 

 eno emp.empno%TYPE ; 

 en emp.ename%TYPE ; 

 mn emp.ename%TYPE ; 

 dn dept.dname%TYPE ; 

 dept dept %rowtype ; 

BEGIN 

 -- 输入的信息保存在 eno里 

 eno := &no ; 

 -- 之后根据 eno的值，对数据库进行查询操作 

 SELECT e.ename,m.ename,d.dname INTO en,mn,dn FROM emp e,dept d,emp m WHERE 

e.empno=7369 AND e.mgr=m.empno AND e.deptno=d.deptno ; 

 DBMS_OUTPUT.put_line('编号为：'||eno||'雇员的姓名为：'||en) ; 

 DBMS_OUTPUT.put_line('编号为：'||eno||'雇员的上级姓名为：'||mn) ; 

 DBMS_OUTPUT.put_line('编号为：'||eno||'雇员所在的部门：'||dn) ; 

DBMS_OUTPUT.put_line(dept.deptno) ; 

EXCEPTION 

 WHEN no_data_found THEN 

  DBMS_OUTPUT.put_line('没有此雇员') ; 

END ; 

/ 




declare

sno emp.empno%type;

e1 emp%rowtype;

begin

sno:=&no;

select * into e1 from emp where empno=sno and org_id=11;

dbms_output.put_line(e1.empno||'   '||e1.name);

exception

when no_data_found then

dbms_output.put_line('not find date');

end;

/




下面是自己亲自做的函数的功能

CREATE OR REPLACE FUNCTION myfun(eno in number) RETURN NUMBER 

is 

tot_sen NUMBER ;

BEGIN 

 SELECT SENIORITY*12 INTO tot_sen FROM emp WHERE empno=eno and org_id=11 ;

 RETURN tot_sen ;

END ;

调用函数

SELECT myfun(101) FROM dual ;



函数

create or replace function myfun1(num in integer) 

return number 

is 

tot_sen number;

begin

  dbms_output.put_line('aaaa');

  return 111;

end;

  

执行函数

declare 

    res number;

    num integer :=5;

    begin

    res :=myfun(num);

    dbms_output.put_line(res);

    

    end;

/



















以下是附带的sql语句


--CREATE DATA------------------------------

create table emp(

org_id varchar2(10),

empno varchar2(10),

name varchar2(10),

deptno varchar2(10),

seniority number(3)  --戈

);


create table dept(

deptno varchar2(10),

deptna varchar2(10)

);


create table org(  

 parent_deptno varchar2(10),

 child_deptno varchar2(10)

);


create table dept2(

deptno varchar2(10),

deptna varchar2(10)

);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('11', '101', 'Kevin', 'D01', 15);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('11', '102', 'Wolf', 'D02', 8);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('11', '103', 'Tom', 'D03', 7);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('11', '104', 'Jean', 'D07', 5);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('11', '105', 'Wuno', 'D08', 3);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('11', '106', 'Judy', '', 2);




INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('14', '101', 'Ben', 'D01', 4);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('14', '102', 'Yuki', 'D02', 3);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('14', '103', 'Kelly', 'D03', 2);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('14', '104', 'Sam', 'D07', 8);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('14', '105', 'Ken', 'D08', 4);


INSERT INTO emp(org_id, empno, name, deptno, seniority)

     VALUES ('14', '106', 'Justin', '', 5);




insert into dept(deptno,deptna) values('D01','MIS');


insert into dept(deptno,deptna) values('D02','RD');


insert into dept(deptno,deptna) values('D03','SD');


insert into dept(deptno,deptna) values('D05','PD');


insert into dept(deptno,deptna) values('D06','QA');


insert into dept(deptno,deptna) values('','GM');


insert into org(parent_deptno,child_deptno) values('','SZS');


insert into org(parent_deptno,child_deptno) values('SZS','D01');


insert into org(parent_deptno,child_deptno) values('D01','D011');


insert into org(parent_deptno,child_deptno) values('D01','D012');


insert into org(parent_deptno,child_deptno) values('D012','D0121');


insert into org(parent_deptno,child_deptno) values('D012','D0122');


insert into org(parent_deptno,child_deptno) values('SZS','D02');


insert into dept2(deptno,deptna) values('D01','MIS');


insert into dept2(deptno,deptna) values('D02','RD');


insert into dept2(deptno,deptna) values('D03','SD');


insert into dept2(deptno,deptna) values('D04','PD2');


insert into dept2(deptno,deptna) values('D11','FC');


insert into dept2(deptno,deptna) values('D12','CSS');


insert into dept2(deptno,deptna) values('','PB');




SELECT * FROM EMP;


SELECT * FROM ORG;


SELECT * FROM DEPT;


SELECT * FROM DEPT2;


COMMIT;



------------------------------------------------------------------------------------------------

--QUERY DATA



--┮Τ戈

--6 record


SELECT * FROM emp;


--舱麓琜篶

--7 RECORD

SELECT * FROM ORG;


--侣场戈(DEPT)

--6 RECORD

SELECT * FROM DEPT;


--穝场戈(DEPT2)  

--7 RECORD

SELECT * FROM DEPT2;



--------------------------------------------------------------


--SQL JOIN (16贺)

--(1)cross join

--6X6=36 RECORDS


SELECT *

  FROM emp, dept;


SELECT *

  FROM dept, emp;


--(2)inner join

--3 records


SELECT *

  FROM emp, dept

 WHERE emp.deptno = dept.deptno and emp.org_id='11';


SELECT *

  FROM emp INNER JOIN dept ON emp.deptno = dept.deptno and emp.org_id='11';


SELECT *

  FROM emp INNER JOIN dept USING(deptno) 

 where emp.org_id='11';


--(3)nature join


SELECT *

  FROM emp NATURAL JOIN dept

  where emp.org_id='11';



--(4)left outer join(┮Τ,⊿Τ场)

--6 RECORDS


SELECT *

  FROM emp, dept

 WHERE emp.deptno = dept.deptno(+) and emp.org_id='11';



SELECT *

  FROM emp LEFT OUTER JOIN dept ON emp.deptno = dept.deptno and emp.org_id='11';




--(5)right outer join(┮Τ场,⊿Τ)

--6 record


SELECT *

  FROM emp, dept

 WHERE emp.deptno(+) = dept.deptno and emp.org_id='11';


SELECT *

  FROM emp RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno and emp.org_id='11';


--猔種: 狦ㄢ戈ぃ琌 1癸1 ,1癸 ㄓ掸计ぃ穦单オ┪

--    セ掸计,パproof right join 玡常穦秈︽cross join


--(6)Full outer join

--9掸 REOCRDS


SELECT *

  FROM emp FULL OUTER JOIN dept ON emp.deptno = dept.deptno and emp.org_id='11'


--9掸 REOCRDS


SELECT *

  FROM emp LEFT OUTER JOIN dept ON emp.deptno = dept.deptno and emp.org_id='11'

UNION

SELECT *

  FROM emp RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno and emp.org_id='11'


--12掸 REOCRDS


SELECT *

  FROM emp LEFT OUTER JOIN dept ON emp.deptno = dept.deptno

UNION ALL

SELECT *

  FROM emp RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno;


--(7)left excluding join (Τ⊿Τ场)

--3 record


SELECT *

  FROM emp, dept

 WHERE emp.deptno = dept.deptno(+)

   AND dept.deptno IS NULL;


SELECT *

  FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno

 WHERE dept.deptno IS NULL;


SELECT emp.empno

  FROM emp LEFT OUTER JOIN dept ON emp.deptno = dept.deptno

 WHERE emp.deptno IS NULL;


--(8)right excluding join(Τ场⊿Τ)


SELECT *

  FROM emp, dept

 WHERE emp.deptno(+) = dept.deptno

   AND emp.deptno IS NULL;


SELECT *

  FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno

 WHERE emp.deptno IS NULL;



--(9)outer excluding join (Τ⊿Τ场Τ场⊿Τ)


SELECT *

  FROM emp e, dept d

 WHERE e.deptno = d.deptno(+)

   AND e.org_id='11'

   AND d.deptno IS NULL

UNION

SELECT *

  FROM emp e, dept d

 WHERE e.deptno(+) = d.deptno

   AND e.org_id='11'

   AND e.deptno IS NULL;


SELECT *

  FROM emp FULL OUTER JOIN dept ON emp.deptno = dept.deptno and  AND emp.org_id='11'

 WHERE emp.deptno IS NULL

    OR dept.deptno IS NULL;


--猔種-----------------------------------------------------------------------


SELECT *

  FROM emp, dept

 WHERE emp.deptno = dept.deptno(+)  AND e.org_id='11';


--error (+) ぃノ笲衡じ or ┪ in


SELECT *

  FROM emp, dept

 WHERE emp.deptno = dept.deptno(+)  AND e.org_id='11'

    OR emp.empno = '2001';


-- (璝ㄏノjoin の on , on  join)

--璝ゑ耕,磷苯磞,兵ンon 罽絛瞅硉穦ゑ耕е,τぃアㄤleft join 種)


SELECT *

  FROM emp

       LEFT JOIN dept

           ON (emp.deptno = dept.deptno

           AND emp.org_id='11'

           AND dept.deptno = 'A22');


--暗ず场cross join 筁耾  ,暗场cross join 筁耾 ,竒アㄏノleft join 種


SELECT *

  FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno

 WHERE dept.deptno = 'A22';


------------------------------------

--靡 select 抖 > order by


  SELECT name     t

    FROM emp

ORDER BY t;


--(10)self join


SELECT * FROM org;


--パ┕т

--prior 癘Θ瞷硂掸

--prior x=y 癘Θ瞷硂掸x 穦琌掸y


    SELECT *

      FROM org

CONNECT BY PRIOR parent_deptno = child_deptno            --瞷硂掸克(x)穦琌掸ㄠ(y)

START WITH child_deptno = 'D012';


--パ┕т


    SELECT *

      FROM org

CONNECT BY PRIOR child_deptno = parent_deptno            --瞷硂掸ㄠ(x)穦琌掸克(y)

START WITH parent_deptno = 'D01';


--(11)minus

--盢1奔籔2逞ぃ戈琩тㄓ

--4 RECORDS


SELECT * FROM dept2

MINUS

SELECT * FROM dept;


--3 RECORDS


SELECT * FROM dept

MINUS

SELECT * FROM dept2;


--(12)intersect

--3 RECORDS


SELECT * FROM dept2

INTERSECT

SELECT * FROM dept;


--(13)UNION

--ㄢ┮ΤRECORDS 埃狡

--10 RECORDS


SELECT * FROM dept

UNION

SELECT * FROM dept2;


--(14)UNION ALL

--ㄢ┮ΤRECORDS ぃ埃狡

--13 RECORDS


SELECT * FROM dept

UNION ALL

SELECT * FROM dept2;


--琩高だ闽羛琩高の帽甅琩高,竒盽穦籔闽龄 EXISTS,IN,ANY,ALL

--(15)闽羛琩高(┪闽琩高)

--т侣场ご穝い┮Τ戈

 SELECT *

  FROM dept d

 WHERE EXISTS

           (SELECT 1

              FROM dept2

             WHERE deptno = d.deptno);



--硈钡琩高眔挡狦硄筁闽羛琩高眔. ㄒ


SELECT emp.empno, dept.deptno

  FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno;


--糶Θ妓:


SELECT empno,

       (SELECT deptno

          FROM dept

         WHERE deptno = emp.deptno)

           deptno

  FROM emp;


--(16)碠甅琩高

--т侣场ご穝い┮Τ戈

SELECT *

  FROM dept

 WHERE deptno IN (SELECT deptno FROM dept2);


--т11紅跋14紅跋┮Τ┮Τ戈


SELECT *

  FROM emp

 WHERE org_id = '11'

   AND seniority > ALL (SELECT seniority

                          FROM emp

                         WHERE org_id = '14');


--т11紅跋14紅跋ヴ戈

SELECT *

  FROM emp

 WHERE org_id = '11'

   AND seniority > any (SELECT seniority

                          FROM emp

                         WHERE org_id = '14');


------------------------------------------------------

--NULL 弧----------------------------------------------

--0 RECORD

SELECT * FROM EMP

WHERE DEPTNO = NULL;


--1 RECORD

SELECT * FROM EMP

WHERE DEPTNO IS NULL;


--0 RECORD

SELECT * FROM DEPT

WHERE DEPTNO = NULL;


--1 RECORD

SELECT * FROM DEPT

WHERE DEPTNO =NULL;


--0 RECORD

SELECT * FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO;


--4 RECORDS

SELECT * FROM EMP,DEPT

WHERE NVL(EMP.DEPTNO,'XX')=NVL(DEPT.DEPTNO,'XX');



--弧倒琘跑计'' 籔null ,''=null 

create table kevin_temp

( n1 varchar2(10),

  n2 number);

  

  insert into kevin_temp(n1,n2) values('a',3);

  

  insert into kevin_temp(n1,n2) values('b',5);

  

  

  select * from kevin_temp;

  

  update kevin_temp

     set n2=null

         where n1='a';  

     

   update kevin_temp

     set n2=''

    where n1='b';       


commit;


select * from kevin_temp

where n2='';



select * from kevin_temp

where n2 is null;



存储过程

CREATE OR REPLACE PROCEDURE myprocaa(dno OUT dept.deptno%TYPE)

as

I number;

BEGIN

   SELECT COUNT(*) INTO dno FROM students;

END;



#as 可以改为is

1)第一种方法调用 myprocaa 

declare 

    dno int;

    begin 

    myprocaa(dno);   #在语句中直接执行存储过程 ,不用exec (execute) 或 call

  end;

2)第二种方法

SQL> var dno number;

SQL> var dno number;

 


普通分类: 
oracle
Powered by Drupal

友情链接

校园好文网

 www.shipingzhong.cn  个人技术网_前端_后台    备案号:苏ICP备18010659号-2