в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:00:59Z

1 версия

Новая страница

==Demonstrates default parameters.==



<source lang="sql">

SQL> CREATE TABLE book (
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> CREATE OR REPLACE PROCEDURE AddNewBook(
2 p_ISBN IN book.ISBN%TYPE,
3 p_Category IN book.category%TYPE := "Oracle Server",
4 p_Title IN book.title%TYPE,
5 p_NumPages IN book.num_pages%TYPE,
6 p_Price IN book.price%TYPE,
7 p_Copyright IN book.copyright%TYPE DEFAULT TO_NUMBER(TO_CHAR(SYSDATE, "YYYY")),
8 p_emp1 IN book.emp1%TYPE,
9 p_emp2 IN book.emp2%TYPE := NULL,
10 p_emp3 IN book.emp3%TYPE := NULL) AS
11
12 BEGIN
13 INSERT INTO book (isbn, category, title, num_pages, price,copyright, emp1, emp2, emp3)
14 VALUES (p_ISBN, p_Category, p_Title, p_NumPages, p_Price,p_Copyright, p_emp1, p_emp2, p_emp3);
15 END AddNewBook;
16 /
Procedure created.
SQL>
SQL> BEGIN
2 AddNewBook("0", "Database", "A Book",5, 34.99, 2004, 1);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table book;
Table dropped.

</source>

==Parameter with default NULL value==



<source lang="sql">

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> CALL CallProc1("Hello!");
CallProc1 called with Hello!
Call completed.
SQL> CALL CallProc1();
CallProc1 called with
Call completed.
SQL>
SQL>

</source>

==Parameter with default value==



<source lang="sql">

SQL> create table myTable(
2 myKey primary key,
3 myValue
4 )as
5 select object_name, max( owner||"_"||object_id )from all_objects group by object_name
6 /
Table created.
SQL>
SQL> create table myTableCopy as select * from myTable where 1=0;
Table created.
SQL>
SQL> create or replace procedure row_fetch_row_select( p_arraysize in number default 100 )
2 as
3 l_myKey myTableCopy.myKey%type;
4 l_myValue myTableCopy.myValue%type;
5 cursor c is select object_name from all_objects;
6 begin
7 open c;
8 loop
9 fetch c into l_myKey;
10 exit when c%notfound;
11 begin
12 select myValue into l_myValue from myTable where myKey = l_myKey;
13 exception
14 when no_data_found then l_myValue := null;
15 end;
16 insert into myTableCopy ( myKey, myValue )values( l_myKey, l_myValue );
17 end loop;
18 close c;
19 end;
20 /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table myTableCopy;
Table dropped.
SQL>

</source>

==Procedure with default parameter value==



<source lang="sql">

SQL>
SQL>
SQL> create or replace
2 procedure default_values(
3 p_parm1 varchar2,
4 p_parm2 varchar2 default "AAA",
5 p_parm3 varchar2 default "BBB" ) 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> set serverout on
SQL>
SQL> exec default_values( "Tom", p_parm3 => "Joe" );
Tom
AAA
Joe
PL/SQL procedure successfully completed.
SQL>

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Stored Procedure Function/Default Parameter Value - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия