如何获取使用％ROWTYPE参数的PLSQL过程的真实签名？
例如 :
clear screen;
prompt > Table creation to support %ROWTYPE
create table samples (
id number,
code varchar2(15),
lib varchar2(200) );
prompt > Package witch use %ROWTYPE
create or replace package use_samples as
procedure getSample(input_sample samples%ROWTYPE);
end use_samples;
/
prompt > Package BODY witch use %ROWTYPE
create or replace package body use_samples as
procedure getSample(input_sample IN samples%ROWTYPE) is
ex samples%ROWTYPE;
begin
select * into ex from samples where samples.code = input_sample.code;
end getSample;
end use_samples;
/
prompt > Proc arguments by ALL_ARGUMENTS
set pagesize 50000
set linesize 2000
set verify off
CLEAR COLUMNS;
COLUMN object_name HEADING "PROC" FORMAT A30 JUSTIFY LEFT;
COLUMN argument_name HEADING "ARGUMENT_NAME" FORMAT A30 JUSTIFY LEFT;
select object_name, argument_name, in_out, data_level, position, data_type
from all_arguments
where owner = USER
and package_name = 'USE_SAMPLES'
and object_name = 'GETSAMPLE';
prompt >> Argument 'INPUT_SAMPLE' is shown as 'PL/SQL RECORD' without any link to 'samples%ROWTYPE'
prompt > PLSQL types declared
select *
from DBA_PLSQL_TYPES
where owner = USER
and package_name = 'USE_SAMPLES';
prompt >> There is no declared type because we use directly a %ROWTYPE argument
prompt > Clean up
drop package use_samples;
drop table samples;
给出:
> Table creation to support %ROWTYPE
Table SAMPLES created.
> Package witch use %ROWTYPE
Package USE_SAMPLES compiled
> Package BODY witch use %ROWTYPE
Package body USE_SAMPLES compiled
> Proc arguments by ALL_ARGUMENTS
columns cleared
PROC ARGUMENT_NAME IN_OUT DATA_LEVEL POSITION DATA_TYPE
------------------------------ ------------------------------ --------- ---------- ---------- ------------------------------
GETSAMPLE INPUT_SAMPLE IN 0 1 PL/SQL RECORD
GETSAMPLE ID IN 1 1 NUMBER
GETSAMPLE CODE IN 1 2 VARCHAR2
GETSAMPLE LIB IN 1 3 VARCHAR2
>> Argument 'INPUT_SAMPLE' is shown as 'PL/SQL RECORD' without any link to 'samples%ROWTYPE'
> PLSQL types declared
no rows selected
>> There is no declared type because we use directly a %ROWTYPE argument
> Clean up
Package USE_SAMPLES dropped.
Table SAMPLES dropped.
因此，对于ALL_ARGUMENTS，“INPUT_SAMPLE”显示为“PL/SQL RECORD”，而没有指向“samples％ROWTYPE”的任何链接。并且在DBA_PLSQL_TYPES中没有这种类型的痕迹。
如何以这种形式获取此过程的声明类型？
GETSAMPLE INPUT_SAMPLE IN SAMPLES%ROWTYPE
最佳答案
我可以建议走另一条路:
1)使用表中需要的所有属性创建对象类型
2)创建您的TYPE的对象表
3)将参数传递给您的TYPE程序
4)您可以从all_arguments获取类型名称%rowtype和%type是在编译过程中动态解析的伪类型。那么在这种情况下，您希望在data_dictionary信息中看到什么？没有与sample％rowtype相关的命名记录/类型/对象。
关于oracle - 如何使用％ROWTYPE获取真实签名，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36622298/