Oracle 10プログラミングSQL(11)開発サブルーチンに精通

/*
 *     
 */
--          PL/SQL ，         ，              
--PL/SQL         ：     
--    
--    ：      
CREATE OR REPLACE PROCEDURE out_time
IS
BEGIN
 DBMS_OUTPUT.put_line(systimestamp);
END;

begin
 out_time();
end;

-- SQL*PLUS     
--  execute      
set serveroutput on
exec out_time
--  call      
set serveroutput on
call out_time();

--  emp   
alter table emp add primary key(empno);

--    ：  IN  
CREATE OR REPLACE PROCEDURE add_employee
  (eno NUMBER,name varchar2,sal number,job varchar2 default 'CLERK',dno NUMBER)
IS
  e_integrity EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_integrity,-2291);
BEGIN
  insert into emp(empno,ename,sal,job,deptno) values(eno,name,sal,job,dno);
EXCEPTION
  when DUP_VAL_ON_INDEX then
     raise_application_error(-20000,'       ');
  when e_integrity then
     raise_application_error(-20001,'      ');
END;

begin
   add_employee(1111,'MARY',2000,'MANAGER',10);
end;

select * from emp;

--    ：  OUT  
CREATE OR REPLACE PROCEDURE query_employee
(eno NUMBER,name out VARCHAR2,salary out number)
is
begin
     select ename,sal into name,salary from emp where empno=eno;
exception
     when NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20000,'      ');
end;

-- SQL*Plus         
var name varchar2(10)
var salary number
exec query_employee(7788,:name,:salary)
print name salary

select * from emp;

--    ：  IN OUT  
CREATE OR REPLACE PROCEDURE compute
(num1 in out number,num2 in out number)
is
  v1 number;
  v2 number;
begin
  v1:=num1/num2;
  v2:=mod(num1,num2);
  num1:=v1;
  num2:=v2;
end;

-- SQL*Plus         
var n1 number
var n2 number
exec :n1:=100
exec :n2:=30
exec compute(:n1,:n2)
print n1 n2

--          
--  dept   
alter table dept add primary key(deptno);

--                   、              
CREATE OR REPLACE PROCEDURE add_dept
(dno number,dname varchar2 default null,loc varchar2 default null)
is 
begin
   insert into dept values(dno,dname,loc);
exception
   when DUP_VAL_ON_INDEX then
      raise_application_error(-20000,'       ');
end;

--    
exec add_dept(50,'SALES','NEW YORK');
exec add_dept(60);
exec add_dept(70,'ADMIN');

--    
exec add_dept(dname=>'SALES',dno=>51);
exec add_dept(dno=>52);

--    :                    
exec add_dept(53,loc=>'NEW YORK');

select * from dept;

--       
--        ，ORACLE     、                 。
--      ，                ，            ，                 SQL  
--        USER_SOURCE，                   
select text from user_source where name='ADD_DEPT';

--    
drop procedure add_dept;


--    
--          。
--    ：      
--                 
CREATE OR REPLACE FUNCTION get_user
return varchar2
is 
   v_user varchar2(100);
begin
   select username into v_user from user_users;
   return v_user;
end;

--           
var v1 varchar2(100)
exec :v1:=get_user
print v1

-- SQL         
SELECT get_user from dual;

--   DBMS_OUTPUT    
set serveroutput on
exec dbms_output.put_line('       ：'||get_user)


--    ：  IN  
--               ，               
CREATE OR REPLACE FUNCTION get_sal(name in varchar2)
return number
as
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal into v_sal from emp
    where upper(ename)=upper(name);
  return v_sal;
EXCEPTION
  when NO_DATA_FOUND THEN
     raise_application_error(-20000,'      ');
END;

-- SQL*Plus         
var sal NUMBER
exec :sal:=get_sal('scott')
print sal

select * from emp;


--    ：  OUT  
--     ，           ，                ，            ，            
--                        ，      OUT       
CREATE OR REPLACE FUNCTION get_info
(name varchar2,title out varchar2)
return varchar2
as 
   deptname dept.dname%TYPE;
BEGIN
   select a.job,b.dname into title,deptname
   from emp a,dept b
   where a.deptno = b.deptno
   and upper(a.ename)=upper(name);
   return deptname;
EXCEPTION
   when NO_DATA_FOUND THEN
      raise_application_error(-20000,'      ');
END;

--      get_info  ，               。
--  ，       OUT  ，     SQL        ，          OUT         
-- SQL*Plus     get_info     ：
var job varchar2(20)
var dname varchar2(20)
exec :dname:=get_info('scott',:job)
print dname job

--    ：  IN OUT  
--       ，                      ，       ORACLE                     
--                 result  ，        IN OUT     
CREATE OR REPLACE FUNCTION result
(num1 NUMBER,num2 IN OUT NUMBER)
RETURN NUMBER
AS 
   v_result NUMBER(6);
   v_remainder NUMBER;
BEGIN
   v_result:=num1/num2;
   v_remainder:=MOD(num1,num2);
   num2:=v_remainder;
   return v_result;
EXCEPTION
   when ZERO_DIVIDE THEN
      raise_application_error(-20000,'   0');
END;

--       IN OUT  ，     SQL        ，        IN OUT           ，                。
-- SQL*Plus         
var result1 NUMBER
var result2 NUMBER
exec :result2:=30
exec :result1:=result(100,:result2)
print result1 result2


--       
--        ，ORACLE                    。
--        USER_SOURCE,                   
select text from user_source where name='RESULT';

--    
DROP FUNCTION result;


--     
--          
--object_name        ，object_type        ，created          
--status          ，VALID      ， INVALID       （    ）
select object_name,created,status from user_objects
where object_type in('PROCEDURE','FUNCTION');

--        
--      USER_SOURCE           
select text from user_source where name='GET_USER';

--         
--        SHOW ERRORS  
--              USER_ERRORS
CREATE OR REPLACE PROCEDURE raise_salary
(no number,increase number) is 
begin
   update emp set sal=sal+increase where empno=no
end;

--  SHOW ERRORS           
-- SQL*Plus   
show errors procedure raise_salary

--        USER_ERRORS         
select line||'/'||position as "LINE/COL",text error from user_errors where name='RAISE_SALARY';

--      raise_salary
CREATE OR REPLACE PROCEDURE raise_salary
(no number,increase number) is 
begin
   update emp set sal=sal+increase where empno=no;
end;

--        
--       ，      ，             ，             INVALID  。
--             ，              USER_DEPENDENCIES        ，            DEPTREE IDEPTREE             
--  USER_DEPENDENCIES        
--          EMP             ，          USER_DEPENDENCIES   
select name,type from user_dependencies where referenced_name='EMP';

--      DEPTREE IDEPTREE             
--       DEPTREE IDEPTREE       ，      SQL  utldtree.sql           DEPTREE_FILL,      DEPTREE_FILL       
@%oracle_home%\rdbms\admin\utldtree
exec deptree_fill('TABLE','HAIYA1','EMP');
--      deptree_fill  ，          SCOTT.EMP           DEPTREE IDEPTREE 
--                       HAIYA1.EMP    
select nested_level,name,type from deptree;
select * from ideptree;

--       
--             ，              （INVALID）  。
--  ，  EMP       ，           INVALID  
alter table emp add remark varchar2(100);
select object_name,object_type from user_objects where status='INVALID';

select * from emp;

--      INVALID ，            ，            ，    ：
alter procedure add_employee compile;
alter function get_info compile;