使用Oracle命令查询Function,Table . .etc

最新推荐文章于 2025-12-29 20:49:07 发布

原创最新推荐文章于 2025-12-29 20:49:07 发布 · 4.7k 阅读

本内容遵循CC 4.0 BY-SA版权协议

博客主要围绕数据库操作展开，介绍了查看函数和表的SQL语句，如查看函数用'select object_name from user_objects where object_type ='FUNCTION'' ，查看表用'SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'' 。还给出函数调用案例，以及带输出函数的三种调用方法。

查看有哪些函数.

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下调用