Oracle PL/SQL/Stored Procedure Function/Procedure Parameters

Материал из SQL эксперт
Перейти к: навигация, поиск

create and pass in three parms

   <source lang="sql">

SQL> SQL> create or replace 

 2  procedure three_parms(
 3    p_p1 number,
 4    p_p2 number,
 5    p_p3 number ) as
 6  begin
 7    dbms_output.put_line( "p_p1 = " || p_p1 );
 8    dbms_output.put_line( "p_p2 = " || p_p2 );
 9    dbms_output.put_line( "p_p3 = " || p_p3 );
10  end three_parms;
11  /

Procedure created. SQL> SQL> set serverout on SQL> SQL> exec three_parms( p_p1 => 12, p_p3 => 3, p_p2 => 68 ); p_p1 = 12 p_p2 = 68 p_p3 = 3 PL/SQL procedure successfully completed. SQL> SQL> 


 </source>


create default values

   <source lang="sql">

SQL> SQL> SQL> create or replace 

 2    procedure default_values(
 3    p_parm1 varchar2,
 4    p_parm2 varchar2 default "default 1",
 5    p_parm3 varchar2 default "default 2" ) as
 6  begin
 7    dbms_output.put_line( p_parm1 );
 8    dbms_output.put_line( p_parm2 );
 9    dbms_output.put_line( p_parm3 );
10  end default_values;
11  /

Procedure created. SQL> SQL> exec default_values( "Tom", p_parm3 => "Joel" ); Tom default 1 Joel PL/SQL procedure successfully completed. SQL> SQL> exec default_values( p_parm4 => "Tom" ); BEGIN default_values( p_parm4 => "Tom" ); END; 

     *

ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to "DEFAULT_VALUES" ORA-06550: line 1, column 7: PL/SQL: Statement ignored 


 </source>


Default Parameter Values

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE Instructor ( 

 2     InstructorID INT NOT NULL PRIMARY KEY,
 3     Name        VARCHAR(50) NOT NULL);

Table created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (1,"Victor"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (2,"Bill"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (3,"Mary"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (4,"Jack"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (5,"Peter"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (6,"Tom"); 1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE InsertInstructor (i_ProfID IN INT, 

 2     i_ProfName IN VARCHAR DEFAULT "Prof. A.N. Other")
 3  AS
 4  BEGIN
 5     INSERT INTO Instructor (InstructorID, Name)
 6     VALUES (i_ProfID, i_ProfName);
 7  END;
 8  /

Procedure created. SQL> SQL> SQL> drop table Instructor; Table dropped. 



 </source>


Different ways of calling a procedure with default parameters.

   <source lang="sql">

SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE DefaultTest ( 

 2    p_ParameterA NUMBER DEFAULT 10,
 3    p_ParameterB VARCHAR2 DEFAULT "abcdef",
 4    p_ParameterC DATE DEFAULT SYSDATE) AS
 5  BEGIN
 6    DBMS_OUTPUT.PUT_LINE(
 7      "A: " || p_ParameterA ||
 8      "  B: " || p_ParameterB ||
 9      "  C: " || TO_CHAR(p_ParameterC, "DD-MON-YYYY"));
10  END DefaultTest;
11  /

Procedure created. SQL> show errors No errors. SQL> SQL> BEGIN 

 2    DefaultTest(p_ParameterA => 7, p_ParameterC => "30-DEC-95");
 3  END;
 4  /

END; 

  *

ERROR at line 3: ORA-01843: not a valid month ORA-06512: at line 2

SQL> SQL> BEGIN 

 2    DefaultTest(7);
 3  END;
 4  /

A: 7 B: abcdef C: 19-JUN-2008 PL/SQL procedure successfully completed. SQL> SQL> 


 </source>


First 2 parameters passed by position, the second 2 are passed by name

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE CallMe( 

 2    p_ParameterA VARCHAR2,
 3    p_ParameterB NUMBER,
 4    p_ParameterC BOOLEAN,
 5    p_ParameterD DATE) AS
 6  BEGIN
 7    NULL;
 8  END CallMe;
 9  /

Procedure created. SQL> SQL> SQL> DECLARE 

 2    v_Variable1 VARCHAR2(10);
 3    v_Variable2 NUMBER(7,6);
 4    v_Variable3 BOOLEAN;
 5    v_Variable4 DATE;
 6  BEGIN
 7    CallMe(v_Variable1, v_Variable2,
 8           p_ParameterC => v_Variable3,
 9           p_ParameterD => v_Variable4);
10  END;
11  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> 


 </source>


Legal and illegal formal parameters which are constrained by length.

   <source lang="sql">

SQL> SQL> SQL> SQL> SET echo on SQL> SQL> CREATE OR REPLACE PROCEDURE ParameterLength ( 

 2    p_Parameter1 IN OUT VARCHAR2(10),
 3    p_Parameter2 IN OUT NUMBER(3,1)) AS
 4  BEGIN
 5    p_Parameter1 := "abcdefghijklm";
 6    p_Parameter2 := 12.3;
 7  END ParameterLength;
 8  /

Warning: Procedure created with compilation errors. SQL> SHOW ERRORS Errors for PROCEDURE PARAMETERLENGTH: LINE/COL ERROR

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

2/31 PLS-00103: Encountered the symbol "(" when expecting one of the 

        following:
        := . ) , @ % default character
        The symbol ":=" was substituted for "(" to continue.

3/29 PLS-00103: Encountered the symbol "(" when expecting one of the 

        following:
        := . ) , @ % default character
        The symbol ":=" was substituted for "(" to continue.

SQL> SQL> DECLARE 

 2    v_Variable1 VARCHAR2(40);
 3    v_Variable2 NUMBER(7,3);
 4  BEGIN
 5    ParameterLength(v_Variable1, v_Variable2);
 6  END;
 7  /
 ParameterLength(v_Variable1, v_Variable2);
 *

ERROR at line 5: ORA-06550: line 5, column 3: PLS-00905: object sqle.PARAMETERLENGTH is invalid ORA-06550: line 5, column 3: PL/SQL: Statement ignored

SQL> SQL> 


 </source>


NOCOPY modifier.

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE NoCopyTest ( 

 2    p_InParameter    IN NUMBER,
 3    p_OutParameter   OUT NOCOPY VARCHAR2,
 4    p_InOutParameter IN OUT NOCOPY CHAR) IS
 5  BEGIN
 6    NULL;
 7  END NoCopyTest;
 8  /

Procedure created. SQL> SQL> 


 </source>


Number type parameter

   <source lang="sql">

SQL> SQL> create table numbers( 

 2    n number,
 3    username varchar2(30) )
 4  /

Table created. SQL> SQL> SQL> create or replace 

 2  procedure insert_numbers( p_num number ) authid definer as
 3  begin
 4    insert into numbers values ( p_num, user );
 5  end insert_numbers;
 6  /

Procedure created. SQL> SQL> EXEC insert_numbers(1); PL/SQL procedure successfully completed. SQL> SQL> select * from numbers; 

        N USERNAME
------------------------------ 
        1 SYS

SQL> SQL> drop table numbers; Table dropped. SQL> 


 </source>


ParameterLength using %TYPE for the parameters(Calling ParameterLength illegally (ORA-6502))

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE lecturer ( 

 2    id               NUMBER(5) PRIMARY KEY,
 3    first_name       VARCHAR2(20),
 4    last_name        VARCHAR2(20),
 5    major            VARCHAR2(30),
 6    current_credits  NUMBER(3)
 7    );

Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits) 

 2                VALUES (10002, "Mar", "Wells","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10004, "Man", "Kyte","Economics", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10005, "Pat", "Poll","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10006, "Tim", "Viper","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10007, "Barbara", "Blues","Economics", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10008, "David", "Large","Music", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10010, "Rose", "Bond","Music", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);

1 row created. SQL> SQL> SET echo on SQL> SQL> CREATE OR REPLACE PROCEDURE ParameterLength ( 

 2    p_Parameter1 IN OUT VARCHAR2,
 3    p_Parameter2 IN OUT lecturer.current_credits%TYPE) AS
 4  BEGIN
 5    p_Parameter2 := 12345;
 6  END ParameterLength;
 7  /

Procedure created. SQL> SQL> DECLARE 

 2    v_Variable1 VARCHAR2(1);
 3    v_Variable2 NUMBER;
 4  BEGIN
 5    ParameterLength(v_Variable1, v_Variable2);
 6  END;
 7  /

DECLARE

ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "sqle.PARAMETERLENGTH", line 5 ORA-06512: at line 5

SQL> SQL> drop table lecturer; Table dropped. SQL> 


 </source>


Passing parameter by parameter name

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE CallMe( 

 2    p_ParameterA VARCHAR2,
 3    p_ParameterB NUMBER,
 4    p_ParameterC BOOLEAN,
 5    p_ParameterD DATE) AS
 6  BEGIN
 7    NULL;
 8  END CallMe;
 9  /

Procedure created. SQL> SQL> DECLARE 

 2    v_Variable1 VARCHAR2(10);
 3    v_Variable2 NUMBER(7,6);
 4    v_Variable3 BOOLEAN;
 5    v_Variable4 DATE;
 6  BEGIN
 7    CallMe(p_ParameterA => v_Variable1,
 8           p_ParameterB => v_Variable2,
 9           p_ParameterC => v_Variable3,
10           p_ParameterD => v_Variable4);
11  END;
12  /

PL/SQL procedure successfully completed. SQL> 


 </source>


Pass null to procedure

   <source lang="sql">

SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS 

 2  BEGIN
 3    DBMS_OUTPUT.PUT_LINE("CallProc1 called with " || p1);
 4  END CallProc1;
 5  /

Procedure created. SQL> SQL> SQL> CALL CallProc1("Hello!"); CallProc1 called with Hello! Call completed. SQL> CALL CallProc1(); CallProc1 called with Call completed. 


 </source>


positional and named notation for procedure calls.

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE CallMe( 

 2    p_ParameterA VARCHAR2,
 3    p_ParameterB NUMBER,
 4    p_ParameterC BOOLEAN,
 5    p_ParameterD DATE) AS
 6  BEGIN
 7    NULL;
 8  END CallMe;
 9  /

Procedure created. SQL> SQL> DECLARE 

 2    v_Variable1 VARCHAR2(10);
 3    v_Variable2 NUMBER(7,6);
 4    v_Variable3 BOOLEAN;
 5    v_Variable4 DATE;
 6  BEGIN
 7    CallMe(v_Variable1, v_Variable2, v_Variable3, v_Variable4);
 8  END;
 9  /

PL/SQL procedure successfully completed. SQL> SQL> 


 </source>


Positional vs. named parameter passing.

   <source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE CallMe(pA VARCHAR2,pB NUMBER,pC BOOLEAN,pD DATE) AS 

 2  BEGIN
 3    NULL;
 4  END CallMe;
 5  /

SP2-0804: Procedure created with compilation warnings SQL> SQL> SQL> -- This call uses positional notation SQL> DECLARE 

 2    v1 VARCHAR2(10);
 3    v2 NUMBER(7,6);
 4    v3 BOOLEAN;
 5    v4 DATE;
 6  BEGIN
 7    CallMe(v1, v2, v3, v4);
 8  END;
 9  /

PL/SQL procedure successfully completed. SQL> 



 </source>


Procedure with colunm type as parameter type

   <source lang="sql">

SQL> SQL> CREATE TABLE lecturer ( 

 2    id               NUMBER(5) PRIMARY KEY,
 3    first_name       VARCHAR2(20),
 4    last_name        VARCHAR2(20),
 5    major            VARCHAR2(30),
 6    current_credits  NUMBER(3)
 7    );

Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits) 

 2                VALUES (10002, "Mar", "Wells","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10004, "Man", "Kyte","Economics", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10005, "Pat", "Poll","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10006, "Tim", "Viper","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10007, "Barbara", "Blues","Economics", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10008, "David", "Large","Music", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10010, "Rose", "Bond","Music", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits) 

 2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);

1 row created. SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE printLecturer(p_Major IN lecturer.major%TYPE) AS 

 2    CURSOR c_lecturer IS
 3      SELECT first_name, last_name FROM lecturer WHERE major = p_Major;
 4  BEGIN
 5    FOR v_StudentRec IN c_lecturer LOOP
 6      DBMS_OUTPUT.PUT_LINE(v_StudentRec.first_name || " " ||v_StudentRec.last_name);
 7    END LOOP;
 8  END;
 9  /

Procedure created. SQL> SQL> BEGIN 

 2    printLecturer("Computer Science");
 3  END;
 4  /

Scott Lawson Jone Bliss Sharon Clear PL/SQL procedure successfully completed. SQL> SQL> drop table lecturer; Table dropped. 


 </source>


Procedure with four parameters

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE CallMe( 

 2    p_ParameterA VARCHAR2,
 3    p_ParameterB NUMBER,
 4    p_ParameterC BOOLEAN,
 5    p_ParameterD DATE) AS
 6  BEGIN
 7    NULL;
 8  END CallMe;
 9  /

Procedure created. SQL> SQL> SQL> DECLARE 

 2    v_Variable1 VARCHAR2(10);
 3    v_Variable2 NUMBER(7,6);
 4    v_Variable3 BOOLEAN;
 5    v_Variable4 DATE;
 6  BEGIN
 7    CallMe(p_ParameterB => v_Variable2,
 8           p_ParameterC => v_Variable3,
 9           p_ParameterD => v_Variable4,
10           p_ParameterA => v_Variable1);
11  END;
12  /

PL/SQL procedure successfully completed. SQL> 


 </source>


Store procedure with three parameters

   <source lang="sql">

SQL> -- store procedure with three parameters SQL> SQL> SQL> create or replace 

 2  procedure three_parms(
 3    p_p1 number,
 4    p_p2 number,
 5    p_p3 number ) as
 6  begin
 7    dbms_output.put_line( "p_p1 = " || p_p1 );
 8    dbms_output.put_line( "p_p2 = " || p_p2 );
 9    dbms_output.put_line( "p_p3 = " || p_p3 );
10  end three_parms;
11  /

Procedure created. SQL> SQL> set serverout on SQL> SQL> exec three_parms( p_p1 => 12, p_p3 => 3, p_p2 => 68 ); p_p1 = 12 p_p2 = 68 p_p3 = 3 PL/SQL procedure successfully completed. SQL> 



 </source>


Use column type to control parameter type

   <source lang="sql">

SQL> CREATE TABLE books ( 

 2     isbn      CHAR(10) PRIMARY KEY,
 3     category  VARCHAR2(20),
 4     title     VARCHAR2(100),
 5     num_pages NUMBER,
 6     price     NUMBER,
 7     copyright NUMBER(4),
 8     emp1   NUMBER,
 9     emp2   NUMBER,
10     emp3   NUMBER
11   );

Table created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE ParameterLength (p1 IN OUT VARCHAR2,p2 IN OUT books.copyright%TYPE) AS 

 2   BEGIN
 3     p2 :=345;
 4   END ParameterLength;
 5   /

Procedure created. SQL> SQL> SQL> show errors No errors. SQL> SQL> SQL> DECLARE 

 2     v1 VARCHAR2(1);
 3     v2 NUMBER;
 4   BEGIN
 5     ParameterLength(v1, v2);
 6   END;
 7   /

PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> SQL> drop table books; Table dropped. SQL> SQL> SQL> SQL> 



 </source>


Use concatenation to wrap string passed in

   <source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE format_string( string_in IN OUT VARCHAR2 ) IS 

 2  BEGIN
 3    string_in := "["||string_in||"]";
 4  END;
 5  /

Procedure created. SQL> 



 </source>


User-defined collection type parameter

   <source lang="sql">

SQL> create or replace type rec is object 

 2   ( a number,
 3   b number,
 4   c varchar2(30));
 5  /

SQL> SQL> create or replace type rec_list is table of rec; 

 2  /

Type created. SQL> SQL> create or replace procedure myProcedure(p Rec_list) is 

 2   x number;
 3   begin
 4       null;
 5   end;
 6  /

Procedure created. SQL> SQL> SQL> 



 </source>


Use SYS_REFCURSOR as parameter type

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE orders( order_number NUMBER, 

 2                       create_date  DATE,
 3                       assign_date  DATE,
 4                       close_date   DATE);

SQL> SQL> BEGIN 

 2    FOR counter IN 1..3 LOOP
 3      INSERT INTO orders
 4      VALUES(counter,
 5             SYSDATE,
 6             SYSDATE + 1,
 7             SYSDATE + 2);
 8    END LOOP;
 9  END;
10  /

PL/SQL procedure successfully completed. SQL> SQL> CREATE OR REPLACE TYPE order_date_o AS OBJECT ( order_number NUMBER, 

 2                                                  date_type    VARCHAR2(1),
 3                                                  year         NUMBER,
 4                                                  quarter      NUMBER,
 5                                                  month        NUMBER );
 6  /

SQL> CREATE TYPE order_date_t AS TABLE OF order_date_o; 

 2  /

SQL> SQL> CREATE OR REPLACE FUNCTION date_parse ( p_curs SYS_REFCURSOR ) 

 2                    RETURN order_date_t AS
 3    v_order_rec orders%ROWTYPE;
 4    v_ret_val order_date_t := order_date_t( );
 5
 6  BEGIN
 7    LOOP
 8
 9      FETCH p_curs INTO v_order_rec;
10      EXIT WHEN p_curs%NOTFOUND;
11      v_ret_val.EXTEND(3);
12      v_ret_val(v_ret_val.LAST - 2) := order_date_o(v_order_rec.order_number,"O",
13                                                    TO_CHAR(v_order_rec.create_date,"YYYY"),
14                                                    TO_CHAR(v_order_rec.create_date,"Q"),
15                                                    TO_CHAR(v_order_rec.create_date,"MM"));
16      v_ret_val(v_ret_val.LAST - 1) := order_date_o(v_order_rec.order_number,"A",
17                                                    TO_CHAR(v_order_rec.assign_date,"YYYY"),
18                                                    TO_CHAR(v_order_rec.assign_date,"Q"),
19                                                    TO_CHAR(v_order_rec.assign_date,"MM"));
20      v_ret_val(v_ret_val.LAST) := order_date_o(v_order_rec.order_number,"C",
21                                                TO_CHAR(v_order_rec.close_date,"YYYY"),
22                                                TO_CHAR(v_order_rec.close_date,"Q"),
23                                                TO_CHAR(v_order_rec.close_date,"MM"));
24    END LOOP;
25
26    RETURN(v_ret_val);
27
28  END;
29  /

Function created. SQL> SQL> SELECT * 

 2    FROM TABLE(date_PARSE(CURSOR(SELECT * FROM orders)))
 3  /

ORDER_NUMBER D YEAR QUARTER MONTH

- ---------- ---------- ---------- 
          1 O       2008          2          6
          1 A       2008          2          6
          1 C       2008          2          6
          2 O       2008          2          6
          2 A       2008          2          6
          2 C       2008          2          6
          3 O       2008          2          6
          3 A       2008          2          6
          3 C       2008          2          6
          1 O       2008          2          6
          1 A       2008          2          6
          1 C       2008          2          6
          2 O       2008          2          6
          2 A       2008          2          6
          2 C       2008          2          6
          3 O       2008          2          6
          3 A       2008          2          6
          3 C       2008          2          6

18 rows selected. SQL> SQL> 


 </source>


Wrong way to reference parameters

   <source lang="sql">

SQL> SQL> SQL> create table t( 

 2      n number,
 3      parm varchar2(20)
 4    )
 5    /

Table created. SQL> SQL> create or replace 

 2    procedure insert_into_t(
 3      p_parm1 in number,
 4      p_parm2 in number ) is
 5    begin
 6      insert into t values ( p_parm1, "p_parm1" );
 7      insert into t values ( p_parm2, "p_parm2" );
 8    end insert_into_t;
 9    /

Procedure created. SQL> SQL> begin 

 2     insert_into_t(1,2);
 3  end;
 4  /

PL/SQL procedure successfully completed. SQL> SQL> select * from t; 

        N PARM
-------------------- 
        1 p_parm1
        2 p_parm2

SQL> SQL> drop table t; Table dropped. 


 </source>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Stored_Procedure_Function/Procedure_Parameters&oldid=2095»