Oracle PL/SQL/Stored Procedure Function/Procedure Parameters

Материал из SQL эксперт
Версия от 10:00, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

create and pass in three parms

    
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>



create default values

    
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



Default Parameter Values

    
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.



Different ways of calling a procedure with default parameters.

    
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>



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

    
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>



Legal and illegal formal parameters which are constrained by length.

    
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>



NOCOPY modifier.

    
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>



Number type parameter

    
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>



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

    
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>



Passing parameter by parameter name

    
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>



Pass null to procedure

    
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.



positional and named notation for procedure calls.

    
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>



Positional vs. named parameter passing.

    
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>



Procedure with colunm type as parameter type

    
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.



Procedure with four parameters

    
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>



Store procedure with three parameters

   
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>



Use column type to control parameter type

    
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>



Use concatenation to wrap string passed in

    
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>



User-defined collection type parameter

    
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>



Use SYS_REFCURSOR as parameter type

    
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>



Wrong way to reference parameters

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