查看有哪些函数.
select object_name from user_objects where object_type ='FUNCTION'
查看有哪些表.
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
函数案例：
CREATE OR REPLACE FUNCTION GETRESULT
(
a in number,
b in number
)
return number
is
c number(4);
begin
c:=a+b ;
return c;
end ;
select GETRESULT(2,3) from dual;
select *from user_objects;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
1 GETRESULT 75459 FUNCTION 2020/12/19 20:32:40 2020/12/19 20:56:00 2020-12-19:20:56:00 VALID N N N 1
2 ODS_EMP_TMP 75348 75348 TABLE 2020/12/10 21:48:37 2020/12/10 21:48:37 2020-12-10:21:48:37 VALID N N N 1
3 ODS_PRODUCT2 75183 75183 TABLE 2020/11/28 9:47:57 2020/11/28 9:47:57 2020-11-28:09:47:57 VALID N N N 1
4 SYS_C0012043 75072 75072 INDEX 2020/11/27 21:38:56 2020/11/27 21:38:56 2020-11-27:21:38:56 VALID N Y N 4
5 ODS_CUST_INFO 75071 75071 TABLE 2020/11/27 21:38:56 2020/11/27 21:38:56 2020-11-27:21:38:56 VALID N N N 1
6 SYS_C0012039 75064 75229 INDEX 2020/11/27 20:02:05 2020/11/29 23:29:44 2020-11-27:20:02:05 VALID N Y N 4
7 ODS_DEPT 75063 75230 TABLE 2020/11/27 20:02:05 2020/11/29 23:29:44 2020-11-27:20:02:05 VALID N N N 1
8 ODS_EMP 75055 75350 TABLE 2020/11/25 22:12:43 2020/12/13 20:00:21 2020-12-13:20:00:21 VALID N N N 1
9 SYS_C0012037 75056 75349 INDEX 2020/11/25 22:12:43 2020/12/10 21:52:08 2020-11-25:22:12:43 VALID N Y N 4
带输出的函数
create or replace function get_sal
(
v_id in ods_emp.empno%type
)
return number
is
v_salary ods_emp.sal%type :=0;
begin
select sal into v_salary
from ods_emp
where empno =v_id ;
return v_salary;
end get_sal;
方法1
select get_sal(7369) from dual;
方法2
在cmd命令下执行如下代码
set serveroutput on; --这个代码可以写在脚本里具体位置如下：
declare
v_sal ods_emp.sal%type;
begin
v_sal:=get_sal(7902);
dbms_output.put_line('7902的工资是：' || v_sal);
end;
/
C:\app\NanT\product\11.2.0\dbhome_2\sqlplus\admin目录下：
glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle. All Rights Reserved.
--
-- NAME
-- glogin.sql
--
-- DESCRIPTION
-- SQL*Plus global login "site profile" file
--
-- Add any SQL*Plus commands here that are to be executed when a
-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
-- This script is automatically run
--
set serveroutput on;
方法3
PLSQL下调用