PL / SQL block basic syntax (ORACLE stored procedures, functions, packages, cursors)

2011-07-11  来源:本站原创  分类:Database  人气:111 

PL / SQL block basic syntax (ORACLE stored procedures, functions, packages, cursors)

1, PL / SQL block

PL / SQL block apply only to Oracle database, using the temporarily stored in the client, rather than stored in the database.

Basic syntax:

declare

Variable declaration, initialization

begin

Business processes, logic code

exception

Exception caught

end;

Variable declaration: <variable name> <type and length> [: = <initial value>]

Example: v_name varchar2 (20): = 'Joe Smith';

Example: See Section 3

2, loop

loop loop syntax:

loop

exit when the expression

end loop;

while loop syntax:

while expression loop

end loop;

for loop syntax:

for <variable> in <variable range (small value .. great value, such as 1 .. 100)> loop

end loop;

can not do for loop variable declaration and initialization.

Example: See Section 3

3, if statement to determine

Basic syntax:

if <expression> then

...

else if <expression> then

...

else

...

end if;

end if;

Example:

declare

v_identity number (4): = 0;

begin

loop

if v_identity = 1 then

dbms_output.put_line ('v_identity = 1');

else if v_identity = 3 then

dbms_output.put_line ('v_identity = 3');

else if v_identity = 6 then

exit;

else

dbms_output.put_line ('v_identity is not 1 or 3');

end if;

end if;

end if; - note the number if the number would end if end marker.

v_identity: = v_identity +1;

end loop;

exception

when others then dbms_output.put_line ('error!');

end;

/

4, branch case

Basic syntax:

case <variable>

when constant then

...

when constant then

...

else

...

end case;

Example:

declare

v_number number (4): = 3;

v_string varchar (20): = 'abc';

begin

case v_number

when 1 then

dbms_output.put_line ('v_number is' | | 1);

when 2 then

dbms_output.put_line ('v_number is' | | 2);

when 3 then

dbms_output.put_line ('v_number is' | | 3);

end case;

case v_string

when 'ab' then

dbms_output.put_line ('v_string is' | | 'ab');

when 'bc' then

dbms_output.put_line ('v_string is' | | 'bc');

else - default match

dbms_output.put_line ('v_string is other value');

end case;

exception

when others then dbms_output.put_line ('error!');

end;

/

5, exception (exception)

Declaration exception syntax: <exception name> exception;

Throw syntax: raise <exception name>;

Catch the exception syntax: when <exception name> then exception handling statements;

Example:

declare

v_input varchar2 (1 ):='& throw '; - Dynamic Input

v_exception_1 exception; - custom exception

v_exception_2 exception;

others exception; - system abnormalities

begin

if v_input = '1 'then

raise v_exception_1; - throws an exception

else if v_input = '2 'then

raise v_exception_2;

else

raise others;

end if;

end if;

exception

- Catch the exception

when v_exception_1 then dbms_output.put_line ('throw exception: v_exception_1');

when v_exception_2 then dbms_output.put_line ('throw exception: v_exception_2');

when others then dbms_output.put_line ('throw exception: others');

end;

/

6, the cursor (cursor)

Declare cursor syntax: cursor <cursor name> is select statement;

Ref cursor declaration syntax: <cursor name> is ref cursor;

Open the cursor syntax: open <cursor name>;

Move the cursor and fetch the data syntax: fetch <cursor name> into <used to store read data variable name>;

Close the cursor syntax: close <cursor name>;

Cursor attributes (properties must close the cursor before the cursor):

% Isopen: to determine whether to open a cursor

% Notfound: data not found

% Found:

% Rowcount: returns the current cursor line number of the scanned data

Cursor Categories: 1, display cursor (custom cursor); 2, the implicit cursor (system cursor); 3, REF cursor

Example:

declare

v_row t_test% rowtype; - t_test table row match the data types of all

cursor v_cur is select * from t_test; - declare cursor

begin

open v_cur; - Open cursor

loop

fetch v_cur into v_row; - the line where the cursor in the data dump v_row

exit when v_cur% notfound; - when the cursor jump to the last row

dbms_output.put_line ('id =' | | v_row.t_id | | 'name =' | | v_row.t_name | | 'msg =' | | v_row.t_msg);

end loop;

close v_cur; - Close cursor

exception

when others then dbms_output.put_line ('throw exception: others');

end;

/

- REF Cursor -

create or replace package upk_select_test

as type uc_test is ref cursor; - declare ref cursor

end upk_select_test;

/

- Call stored procedure ref cursor, the query results back to the way the cursor

create or replace procedure up_select_test_2

(Uc_result out upk_select_test.uc_test)

is

begin

open uc_result for select * from t_test;

end up_select_test_2;

/

7, wildcard type operator

% Type: through a column with a row of data types, such as v_name t_test.t_name% type; wildcard table t_test in t_name.

% Rowtype: with a line through all columns of data types, such as v_row t_test% rowtype; match t_test table row

All data types.

8, the stored procedure (procedure)

Basic syntax:

create procedure <procedure name> (<parameter list, no reference is ignored>)

as | is

Variable declaration, initialization

begin

Business processes, logic code

exception

Unusual capture, fault-tolerant

end <process name>;

Parameters: <parameter name> in | out | in out <parameter type, length shows no> such as: v_name varchar2

in: into the reference

out: a reference

in out: access to reference

Note: as | is expressed as, or is

Call syntax:

1), exec <process name>;

2), execute <process name>;

3), in PL / SQL block in the direct call.

Example:

create or replace procedure up_wap (v_param1 in out varchar2, v_param2 in out varchar2)

is

v_temp varchar2 (20);

begin

dbms_output.put_line ('before the exchange parameters 1:' | | v_param1 | | 'parameters 2:' | | v_param2);

v_temp: = v_param1;

v_param1: = v_param2;

v_param2: = v_temp;

dbms_output.put_line ('After the exchange parameters of 1:' | | v_param1 | | 'parameters 2:' | | v_param2);

exception

when others then dbms_output.put_line ('There is a error when the procedure up_wap executing!');

end up_wap;

/

- Call stored procedure

declare

v_param1 varchar2 (20): = 'param1';

v_param2 varchar2 (20): = 'param2';

begin

up_wap (v_param1 => v_param1, v_param2 => v_param2);

end;

/

9, self-defined functions (function)

Basic syntax:

create function <function name> (<parameter list, no reference is ignored>)

return <return type, no length description>

as | is

Variable declaration, initialization

begin

Business processes, logic code

return <return value>;

exception

Unusual capture, fault-tolerant

end <function name>;

Parameters: in into the reference

Note: Only incoming parameter of type.

In stored procedures and custom functions in the parameters passed (into the reference and the reference) or can not use% type% rowtype match, can not use the null value null, but the stored procedure can return a null value.

Example:

create function uf_select_name_by_id_test (v_id in number)

return varchar2

is

v_name t_test.t_name% type;

begin

select t_name into v_name from t_test where t_id = v_id;

return v_name;

exception

when others then dbms_output.put_line ('error');

end uf_select_name_by_id_test;

/

select uf_select_name_by_id_test (1) name from dual; - select call

declare --pl/sql block calls

v_name varchar2 (20);

begin

v_name: = uf_select_name_by_id_test (1);

dbms_output.put_line ('name =' | | v_name);

end;

/

10 package (package)

Package, can the packaging process (procedure), function (function) and variables.

Note that in the package (package) declared the process (procedure) and function (function) must be achieved in the package body

(Package body) are defined to achieve.

Basic syntax:

create package <package name>

as | is

Variable declaration

Stored procedure declaration

Custom function declaration

end <package name>;

/

create package <package name, consistent with the declaration part>

as | is

Stored procedure code

Custom function code

end <package name>;

/

Example:

- Create package upk_hello

create or replace package upk_hello

is

v_hello_world varchar2 (20): = 'hello world'; - declare variables

procedure up_hello_world (v_name in varchar2); - Statement process

function uf_hello_world (v_name in varchar2) return varchar2; - Statement function

end upk_hello;

/

- Implementation package (upk_hello) declared in the method

create or replace package body upk_hello

is

procedure up_hello_world (v_name in varchar2)

is

v_string varchar2 (100);

begin

v_string: = v_name | | 'say hello world!';

dbms_output.put_line (v_string);

exception

when others then dbms_output.put_line ('error');

end up_hello_world;

function uf_hello_world (v_name in varchar2) return varchar2

is

v_string varchar2 (100);

begin

v_string: = v_name | | 'say hello world!';

return v_string;

exception

when others then dbms_output.put_line ('error');

end uf_hello_world;

end upk_hello;

/

- Packet call

declare

v_msg varchar2 (100);

begin

upk_hello.up_hello_world ('bing');

v_msg: = upk_hello.uf_hello_world ('admin');

dbms_output.put_line (v_msg);

dbms_output.put_line (upk_hello.v_hello_world);

end;

/

相关文章
  • PL / SQL block basic syntax (ORACLE stored procedures, functions, packages, cursors) 2011-07-11

    PL / SQL block basic syntax (ORACLE stored procedures, functions, packages, cursors) 1, PL / SQL block PL / SQL block apply only to Oracle database, using the temporarily stored in the client, rather than stored in the database. Basic syntax: declare

  • Oracle stored procedures and packages 2011-08-11

    First, why use stored procedures? If the application is often required to perform specific operations, these operations can resume on a specific process. The process can be simplified by using the client program development and maintenance, but also

  • basic syntax oracle stored procedure 2010-03-29

    Keywords: oracle 1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELECT INTO STATEMENT To select

  • pl sql developer How to debug stored procedures in the package to create and debug stored procedures 2010-12-22

    Learn to use PL / SQL Developer's debugging functions, for writing complex stored procedures, packages, funtion ... very helpful, so tonight to learn a bit: (1) set breakpoints in the body (want to set a breakpoint in the line begin to click the left

  • Oracle stored procedures using nested cursors to achieve paging 2010-09-13

    Oracle's process to return the result set is more complex; nested cursor to use with (reportedly with a temporary table can not grasp now, but temporary ... ... ... ... hey ... ... Continued.) Here is an example of hi own conclusion when the intervie

  • PL / SQL block structure and purpose 2011-05-16

    PL / SQL block structure and purpose Effect: If you do not use PL / SQL language, oracle can only handle one SQL statement. Each SQL statement causes the client (client) to server (server) calls, which generate significant performance overhead, espec

  • 16.PL/SQL block structure and examples 2010-05-07

    Han Shunping. Fun oralce 24 talk. Plsql programming (1) Fun combat tutorial oracle (DAY) Introduction 1. On the section reviews 2.pl/sql introduction √ 3.pl/sql foundation √ Desired goals 1. Understand the oracle of the pl / sql concepts 2. Master pl

  • PL / SQL DEVELOPER basic Detailed description 2010-03-25

    PL / SQL DEVELOPER basic Detailed description (proposal written after the first stored procedure, the initial hand must-read) Used the oracle of all complaints, in order to stabilize its operation to provide graphical slow sad ah, p4 +128 M to start

  • Management of Oracle 10g stored procedure - query the database view. Stored procedures. Functions 2010-12-08

    Database dictionary view used to display the current user user_objects contains all the objects. It not only lists the user's tables, views, indexes, etc., can also be used to list the user's stored procedures, functions, packages. - 1, query the dat

  • Oracle 10g administration stored procedures - query the database view. Stored procedures. Functions 2010-12-08

    Database dictionary view used to display the current user user_objects all the objects contained. It not only can be used to list the user tables, views, indexes, etc., can also be used to list the user's stored procedures, functions, packages. - 1,

  • Oracle's PL / SQL block 2010-04-21

    1, PL / SQL block: Concept: PL / SQL is a procedural language (Procedural Language) and the Structured Query Language (SQL) a combination of programming language. Categories: a, anonymous: one-time use, not reuse. b, named: include (stored procedures

  • PL / SQL block syntax: 2010-07-21

    PL / SQL block syntax: [DECLARE] - Declaration statements BEGIN - Executable statements [EXCEPTION] - Exception statements END PL / SQL block of each statement must end with a semicolon, SQL statement is more than one line, but the semicolon indicate

  • PL / SQL block structure and use 2011-05-16

    PL / SQL block structure and use Role: If you do not use PL / SQL language, oracle can only handle a SQL statement. Each SQL statement is leading to customer (client) to the server (server) call, thus have a huge performance overhead, especially in n

  • 17.pl/sql classification - procedures, functions, packages, triggers, 2010-05-10

     process used to perform a specific operation procedure, when the build process, not only can specify input parameters (in), you can specify the output parameters (out), through the use of input parameters in the process, the data can be passed to t

  • Skills in Oracle stored procedures 2010-11-11

    During our pl / sql programming dealing with the most is stored procedure. The structure of the stored procedure is very simple, we are here in addition to learning the basic structure of the stored procedure, it will also learn when writing stored p

  • oracle stored procedures in out inout use three parameters model and PROMPT 2010-11-02

    oracle stored procedures in out inout three parameters model oracle process defined in | out | in out3 model parameters, each parameter can choose one is a parameter in the default mode, which is run when the program has a value, the value of the bod

  • Oracle stored procedures to create and call 2010-11-19

    1.Oracle create stored procedures in the following format: create [or replace] procedure <procedure name> <Parameter 1>, [Mode 1], <data type 1>, <Parameter 2>, [Mode 2], <data type 2>, ... is | as begin PL / SQL end; One par

  • (R) Oracle stored procedures 2011-03-25

    Transfer: http://1zebra.iteye.com/blog/427262 1, what is stored procedure. Stored procedure is a database server-side program, it has two types. A similar SELECT query to retrieve the data, the data can be retrieved in the form of a data set returned

  • pl / sql block classification 2011-04-28

    Learning oracle 10g the first day: pl / sql block can be divided into four categories: anonymous block and named blocks, subprograms, and triggers the following were introduced about four pl / sql block Anonymous block: No name pl / sql block, for ex

  • oracle stored procedures and functions 2011-08-11

    -- Subroutine /* Subroutine is a named PL / SQL block , May take an argument called multiple times , Modular Process && Function Process : Perform specific actions Function : Return specific data Definition : CREATE [OR REPLACE] PROCEDURE procedur