資料庫開發---常用物件-函式

資料庫開發---常用物件-函式，儲存過程，遊標，觸發器，包

----函式

1>函式和存數過程的區別：

    儲存過程                                            函式

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

    不能被作為表示式呼叫                                只能作為表示式被呼叫

    宣告頭部關鍵字為procedure                        宣告頭部關鍵字為function

    宣告頭部不包含return關鍵字來描述返回型別         頭部必須包含return關鍵字,且PL/SQL塊中至少包含一個有效的return語句

    可以透過out,in out返回零個或多個值               透過return語句返回一個與頭部宣告中型別一致的值,也可使用in,in out返回值

    SQL語句中不可呼叫儲存過程                        SQL語句可以呼叫函式

    多用於資料庫中完成特定的操作,如刪除,更新,插入等DML操作     多用於特定的資料如選擇等

---建立語句

create or replace function test_func_1 return varchar2 is

begin

  dbms_output.put_line('hello world!');

  return 1;

end;

---函式的執行

create or replace function get_user return varchar2 is

  v_user varchar2(10);

begin

  select username into v_user from user_users;

  return v_user;

end;

/

1、使用變數接收。

SQL> var current_user varchar2(10)

SQL> exec :current_user:=get_user

2、使用SQL

 select get_user from dual;

3、使用exec呼叫

execute

exec

SQL> set serveroutput on

4、call

call dbms_output.put_line('當前登入的使用者是： '||get_user);

5、塊中呼叫(匿名塊中或函式儲存過程中都可以)

begin

  dbms_output.put_line('當前登入的使用者是： ' || get_user);

end;

/ declare v_user varchar2(10);

begin

  v_user := get_user;

end;

/

select get_user from dual;

2>常用的查詢函式相關定義的語句：

select owner,object_name,object_type from dba_objects where object_name='GET_INFO';

select text from user_source where name='TEST_FUNC_1';--查出函式定義資訊

1.得到一個表或索引的ddl語句

SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;

select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;

2.得到一個使用者下的所有表，索引，儲存過程的ddl

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)

FROM USER_OBJECTS u

where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');

3.得到所有表空間的ddl語句

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)

FROM DBA_TABLESPACES TS;

4.得到所有建立使用者的ddl

SELECT DBMS_METADATA.GET_DDL('USER',U.username)

FROM DBA_USERS U;

3>函式中傳入傳出引數的用法

create or replace function get_sal(name IN varchar2) -----IN 即是傳入的變數

 return number as

  v_sal emp.sal%type;

begin

  select sal into v_sal from emp where ename = NAME;

  return v_sal;

end;

SELECT   GET_SAL('SCOTT') FROM DUAL;

create or replace function get_sal(name out varchar2) -----IN 即是傳入的變數,不能作為賦值目標

 return number as

  v_sal emp.sal%type;

begin

  select sal into v_sal from emp where ename = name;

  name := 'SCOTT';

  return v_sal;

end;

    create or replace function get_info(name in varchar2,

                                        ejob out varchar2) return varchar2 as

      v_dname dept.dname%type;

    begin

      select e.job, d.dname

        into ejob, v_dname

        from emp e, dept d

       where e.deptno = d.deptno

         and e.ename = name;

      return v_dname;

    end;

    /

declare

  ----v_job用來接收需要傳出的值，而in引數則不能接受傳入引數！ v_dname接收的是傳入的的值

  v_job   varchar2(20) := 'abc';

  v_dname varchar2(20);

begin

  dbms_output.put_line(v_job);

  v_dname := get_info('JAMES', v_job);

  dbms_output.put_line('部門:' || v_dname || ' 職位:' || v_job);

end;

/

----傳值的方式

v_dname:=get_info(name =>'JAMES',ejob => v_job);

v_dname:=get_info('JAMES',ejob => v_job);

4>函式的語法結構

create or replace function func_name  return type  is/as

begin

            ; 

  end;