PLSQL學習-【7儲存過程、函式】

函式只能被呼叫不能單獨執行。

先建立登陸人員的表。

23:34:31 SQL> create table login_tab(name varchar2(20) , login_date date);
Table created.

 SQL> create or replace procedure p_login

 is 

  begin

   insert into login_tab values(USER ,sysdate);

   commit;

  end;

 / 



SQL> exec p_login;

帶引數的儲存過程：

in 代表 傳入的值
out 代表 返回的值
inout 代表傳入的值處理後還是返給這個引數

01:15:53 SQL> create or replace procedure p_delete(p_empno in number)
01:16:36   2 is 
01:16:37   3 begin
01:16:44   4 delete from emp where empno = p_empno;
01:17:06   5   commit;
01:17:10   6 if sql%notfound then
01:17:26   7      dbms_output.put_line(\'not found\');
01:17:46   8 end if;
01:17:49   9 end;
01:17:55  10 /
Procedure created.

SQL> select * from user_objects;
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYP CREATED    LAST_DDL_TIME TIMESTAMP          STATUS  TEMPORARY GENERATED SECONDARY
-------------- ------------------------------ ---------- -------------- ---------- ----------- ------------- -------------------
PK_DEPT     51150     51150          INDEX      2005/10/22  2005/10/22 22 2005-10-22:22:23:19 VALID   N         N         N
DEPT        51149     51149          TABLE      2005/10/22  2005/10/22 22 2005-10-22:22:23:19 VALID   N         N         N
EMP         51151     51151          TABLE      2005/10/22  2005/10/22 22 2005-10-22:22:23:20 VALID   N         N         N
PK_EMP      51152     51152          INDEX      2005/10/22  2005/10/22 22 2005-10-22:22:23:20 VALID   N         N         N
BONUS       51153     51153          TABLE      2005/10/22  2005/10/22 22 2005-10-22:22:23:20 VALID   N         N         N
SALGRADE    51154     51154          TABLE      2005/10/22  2005/10/22 22 2005-10-22:22:23:20 VALID   N         N         N
LOGIN_TAB   55969     55969          TABLE      2014/11/23  2014/11/23 23 2014-11-23:23:35:20 VALID   N         N         N
P_LOGIN     55970                    PROCEDURE  2014/11/23  2014/11/23 23 2014-11-23:23:37:11 VALID   N         N         N
P_DELETE    55971                    PROCEDURE  2014/11/24  2014/11/24 1: 2014-11-24:01:17:57 VALID   N         N         N
P_1         55946                    PROCEDURE  2014/11/15  2014/11/15 10 2014-11-15:10:24:53 VALID   N         N         N
P_2         55947                    PROCEDURE  2014/11/15  2014/11/15 10 2014-11-15:10:35:58 VALID   N         N         N
P_3         55948                    PROCEDURE  2014/11/15  2014/11/15 10 2014-11-15:10:41:03 VALID   N         N         N
EMP1        55950     55950 TABLE               2014/11/15  2014/11/15 13 2014-11-15:13:12:05 VALID   N         N         N
TEST        55956     55956 TABLE               2014/11/21  2014/11/21 14 2014-11-21:14:45:32 VALID   N         N         N
QUERY_DEPT  55957                    PROCEDURE  2014/11/21  2014/11/21 17 2014-11-21:17:55:26 VALID   N         N         N
15 rows selected

SQL> select * from user_source;
NAME    TYPE    LINE  TEXT
------------------------------ ------------ ---------- -------------------------------------------------------------------------
P_LOGIN                        PROCEDURE             1 procedure p_login
P_LOGIN                        PROCEDURE             2 is
P_LOGIN                        PROCEDURE             3 begin
P_LOGIN                        PROCEDURE             4 insert into login_tab values(USER ,sysdate);
P_LOGIN                        PROCEDURE             5  commit;
P_LOGIN                        PROCEDURE             6 end;

刪除儲存過程：

SQL> drop procedure p_1;
Procedure dropped

例：刪除測試資料庫中無效的表。

01:50:06 SQL> create or replace procedure p_drop 
01:50:52   2 is
01:50:53   3      cursor cu_tab is
01:51:03   4      select * from tab where tname like 'TEST%' or (tname like 'EMP%' and length(tname)>3);
01:52:46   5 begin
01:52:50   6 for i in cu_tab loop
01:53:38   7      v_sql:='drop table :1';
01:54:59   8 execute immediate v_sql using i.tname;
01:55:22   9 end loop;
01:55:32  10 end;

這樣不行。

這個才可以。

create or replace procedure p_drop 
 is
    cursor cu_tab is
      select * from tab where tname like 'TEST%' or (tname like 'EMP%' and length(tname) > 3); 
    v_sql varchar2(100);
begin
 for i in cu_tab loop
   dbms_output.put_line(i.tname);
    v_sql:='drop table '|| i.tname;
   execute immediate v_sql ;
  end loop;
end;
/

函式：
寫一個函式，計算各部門的平均 工資，再寫一個呼叫函式的匿名塊。

create or replace function f_avg(p_deptno number)
  return number
  is
  v_avg number;
begin
   select trunc(avg(sal),) into v_avg from emp1 where deptno = p_deptno;
   return v_avg;
end;

declare 
  v_num number;
begin
  v_num:=f_avg(10);
  dbms_output.put_line(v_num);
end;

函式也可以透過user_source檢視。