Basic PL/SQL programming introduction and practices

Source: Internet
Author: User

Basic PL/SQL programming introduction and practices

Basic PL/SQL programming introduction and practices

1. Start (p1 ~ P2)
2. Background (p3)
3. features and advantages (p4 ~ P5)
4. Instructions for use (p6)
5. syntax structure (p7)
6. Naming reference (p8 ~ P9)
7. Composite Type (p10 ~ P16)
8. Operator (p17)
9. Process Control Statement (p18 ~ P28)
10. Handling exceptions and errors (p29 ~ 32)
11. Functions and stored procedures (p33 ~ P41)
12. End (p42)

1. Background
1. PL/SQL is a program Language called Procedural Language/SQL ). PL/SQL is an extension of SQL statements in Oracle databases. The features of the programming language are added to the use of common SQL statements. Therefore, PL/SQL organizes data operations and query statements into procedural units of PL/SQL code, implement complex functions or computing programming languages through logical judgment, loops, and other operations.
2. PL/SQL type: Database Engine and tool engine (embedded in other languages such as C and JAVA ).
3. PL/SQL includes the programming structure, syntax, and logic mechanism. The tool engine also adds syntaxes that support (such as ORACLE Forms.

3. features and advantages
1. Improve Operation Efficiency
You can process a large amount of data on the server to reduce the data transmission time on the network.
2. Client
You can run the local PL/SQL program on the client, or run the PL/SQL program by sending SQL commands to the server or activating the server.
3. process-based support
SQL statements can be embedded, and various types of conditional branch statements and cyclic statements can be used.
4. Support Modularization
Various processing logics can be separated by using packages, processes, functions, and triggers for convenient management.
5. Support Exception Handling
You can use custom exceptions or built-in exceptions to handle possible exceptions in the Code and improve code robustness.
6. Provide a large number of built-in packages
You can use the Oralce built-in package to process data and business processes more conveniently.
7. Others: better performance, portability and compatibility, maintainability, and ease of use.

4. Instructions for use
1. Allowed statements:
You can use INSERT, UPDATE, DELETE, select into, COMMIT, ROLLBACK, and SAVEPOINT statements to dynamically use DDL (CREATE, ALTER, DROP, and TRUNCATE) in PL/SQL) statement.
2. Running method:
PL/SQL can be used in SQL * PLUS, advanced language, and ORACLE development tools (such as SQL Developer or Procedure Builder.
3. running process:
PL/SQL programs run through an Oracle engine. This engine may be on the Oracle server or the Oracle client. The engine executes procedural statements in PL/SQL, sends the SQL statements to the database server for execution, and then returns the results to the execution end.

5. syntax structure
1. PL/SQL: consists of three parts: Declaration, execution, and exception handling.
2. Structure Description:
-- Declaration part: Declares the constants, types, cursors, local stored procedures, and functions used.
Declare
...
-- Execution part: the specific SQL statement, including the processing process.
Begin
...
-- Exception section: SQL statements for exception handling.
Exception
...
End;

6. Naming reference
1) identifier:
It is case-insensitive, cannot contain minus signs (-), must start with a letter, cannot be a reserved SQL word, cannot exceed 30 characters.
2) Naming reference:
Program variable: v_name v_orderId program constant: c_name c_cityId cursor variable: cursor_name cursor_storeId exception identifier: e_name e_agentId record type: name_record test_city_record binding variable: g_name g_userId error: e_error
3) data types: (5 categories)
1-character type (CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2), 2-digit type (NUMBER, INTEGER, BINARY_FLOAT, BINARY_DOUBLE), 3 time type (DATE, TIMESTAMP, interval year, interval day), 4 large object types (BLOB, CLOB, BFILE, NCLOB) 5. Other types (LONG, raw long raw, ROWID, and UROWID ).

Bfile (moive): stores enlarged binary data objects. Tables only store directories of files. Size <= 4 GB
Blob (photo): The position of the enlarged binary data object, which points to the data block of the binary object. Size <= 4 GB
Clob (book): the location of the enlarged character data object, which points to the data block of the character. Size <= 4 GB
Nclob (ncahr character data): stores the position of the enlarged nchar character data object and points to the data block of the nchar character. Size <= 4 GB
4) variable Declaration
V_flag boolean not null default false;
Identifier [constant] datetype [not null] [: = value | default value | expression]
Identifier: variable name
Datetype: variable type
: = Initial value of the value variable or constant
Default value: default value
Expression is another function variable, text value, etc.
5) Notes
-- Single line comment/* multi-line comment */

7. Introduction to compound types
1. Composite Type: (record type, array type, one-dimensional table type, and two-dimensional table type)
1) record type: the record type is similar to the structure data type in C language. It stores logically related, separated, and basic data type variables as a whole, it must include at least one member of the scalar or record data type, called the pl/SQL record field, which stores different but logically related information. When using a record data type variable, you must first define the record composition and record variables in the Declaration section, and then reference the record variable itself or its members in the execution section.
Type record_name is record (
V1 data_type1 [not null] [: = default_value],
...
Vn data_typen [not null] [: = default_value]);
2) Description: % type: indicates that the data type of the variable is the same as that of the column corresponding to the table.
% Rowtype: indicates that the data type of the variable is the same as that of all columns corresponding to the table.
You do not need to know the Data Type of the column. When the Data Type of the column changes, modify the pl/SQL code.
The assigned variables must correspond to the columns in the select statement.

Declare
Id varchar2 (32); -- id card number
Province varchar2 (10);-province ID
City varchar2 (10); -- city no.
District varchar2 (10); -- Region ID

-- Define the province, city, and region number record object
Type base_info_type is record (
Province base_info.province % type,
City base_info.city % type,
District base_info.district % type );

Sp_record base_info_type;

Begin
Id: = sys_guid ();
-- Query the associated province numbers, city numbers, and Region numbers.
Select province, city, district
Into sp_record
From base_info bi
Where bi. store_id = 'storeid'; − updatetest h ousefohsetfoh. province = sp r ecord. province, foh. city = sp r ecord. city, foh. region = sp r ecord. district, foh. address = 'business path' | lpad (abs (dbms r andom. random), 4, dbms r andom. string ('x', 2) wherefoh. order I d = 'storeid'; − updatetesthousefohsetfoh. province = sprecord. province, foh. city = sprecord. city, foh. region = sprecord. district, foh. address = 'business path' | lpad (abs (dbmsrandom. random), 4, dbmsrandom. string ('x', 2) wherefoh. orderid = '{orderId }';
Commit;
End;

2) array type: a set of records with the same data type.
Type array_name is varray (size) of elementType [not null];
Array_name: array type name size: element size elementType: Data Type
-- The position starts from 1.
Declare
Type city_array is varray (3) of varchar2 (10 );
V_city_array city_array;
Begin
V_city_array: = city_array ('beijing', 'shanghai', 'shenzhen ');
Dbms_output.put_line ('1970 city names = '| v_city_array (3 ));
End;

1. Bind variable: use variable to define
Variable return_cityId number;

SQL> variable returnValue number;
SQL> begin
2 select 3*6 into: returnValue from dual;
3 end;
4/
PL/SQL procedure successfully completed
ReturnValue
---------
18
SQL> print returnValue;
ReturnValue
---------

3) Table type: defines the data type of the record table (or index table. It is similar to the record type, but it is an extension of the record type. It can process multiple rows of records, similar to the two-dimensional array in advanced mode, so that tables in other databases can be imitated in pl/SQL.
Type table is table of elementType [not null]
Index by [binary_integer | pls_integer | varray2]
The keyword index by indicates that a primary key index is created to reference a specific row in the record table variable.
-- Example of using a record table by one-dimensional array
Declare
Type city_table is table of varchar2 (20) index by binary_integer;
V_city_table city_table;
Begin
V_city_table (1): = 'beijing ';
V_city_table (2): = 'shenzhen City ';
Dbms_output.put_line ('1970 city names = '| v_city_table (2 ));
End;

-- Example of using a record table by two-dimensional array
Declare
Type bse_city_table is table of test_city % rowtype index by binary_integer;
V_bse_city_table bse_city_table;
Begin
Select city_id, city_name
Into v_bse_city_table (1). city_id, v_bse_city_table (1). city_name
From test_city bc
Where bc. p_city_id = '020'
And rownum = 1;
Select city_id, city_name
Into v_bse_city_table (2). city_id, v_bse_city_table (2). city_name
From test_city bc
Where bc. p_city_id = '20140901'
And rownum = 1;
Dbms_output.put_line ('region ID in record 1 = '| v_bse_city_table (1). city_id |
'_ Region name =' in record 1 | v_bse_city_table (1). city_name );
Dbms_output.put_line ('region ID in record 1 = '| v_bse_city_table (2). city_id |
'_ Region name =' in record 1 | v_bse_city_table (2). city_name );
End;

8. Operators
1. Relational operators:
=, <> ~ =! = ^ =,>, >=, <, <=
2. General operators:
+,-, *,/,: = (Value assignment number),... (range operator), ||,=> (relationship number)
3. logical operators:
Is null, in, and, or, not, between and
4. Notes:
1) variable assignment: declare and assign values first.
V_storePhone varchar2 (11); -- mobile phone number
V_storePhone: = '000000' | lpad (abs (dbms_random.random), 8, 0 );
2) null + number is null, null | string is a string
3) values of the boolean type can only be set to true, false, and null3.

9. Process Control statements
1) Statement classification: control statements (IF), LOOP statements (LOOP, EXIT), sequential statements (GOTO, NULL)
2) Structure Description
A)
IF <Boolean expression> THEN
PL/SQL statements and SQL statements
End if;
B)
IF <Boolean expression> THEN
PL/SQL statements and SQL statements
ELSE
Other statements
End if;

IF <Boolean expression 1> THEN
PL/SQL statements and SQL statement 1
ELSIF <Boolean expression 2> THEN
Other Statement 1
ELSIF <Boolean expression 3> THEN
Other Statement 2
ELSE
Other Statement 3
End if;

IF statement example
Declare
V_roleId varchar2 (20); -- Role ID
V_result varchar2 (60 );
Begin
For vv in (select distinct su. role_id
From test_ur su
Where su. role_id in ('Project _ sz ',
'Project _ bj ',
'Project _ gz ',
'Project _ Sh') loop
If (vv. role_id = 'Project _ sz ') then
V_result: = vv. role_id | '_ indicates _ Role 1 ';
Dbms_output.put_line (v_result );
Elsif (vv. role_id = 'Project _ Sh') then
V_result: = vv. role_id | '_ indicates _ Role 2 ';
Dbms_output.put_line (v_result );

Elsif (vv. role_id = 'Project _ gz ') then
V_result: = vv. role_id | '_ indicates _ Role 3 ';
Dbms_output.put_line (v_result );
Elsif (vv. role_id = 'Project _ bj ') then
V_result: = vv. role_id | '_ indicates _ Role 4 ';
Dbms_output.put_line (v_result );
Else
V_result: = vv. role_id | '_ indicates _ unknown role ';
Dbms_output.put_line (v_result );
End if;
End loop;
Dbms_output.put_line (to_char (sysdate, 'hh24: mi: ss') | 'processing successfully ');
End;

Loop statement example
Loop
Executed statement
Exit when <Condition Statement>; -- exit the loop when the condition is met.
End loop;
-- Loop example
Declare
V_count number;
V_time number;
Begin
V_count: = 0;
Loop
V_count: = v_count + 1;
Dbms_output.put_line ('subobject' | v_count | 'subobject ');
Exit when (v_count> 3 );
End loop;
End;

While statement example
While <Boolean expression> loop
Executed statement
End loop;
-- While example
Declare
V_count number;
V_time number;
Begin
V_count: = 0;
While (v_count <3) loop
V_count: = v_count + 1;
Dbms_output.put_line ('subobject' | v_count | 'subobject ');
End loop;
End;

For Loop statement example
For loop counter in [reverse] lower limit... upper limit loop
Executed statement
End loop;
Once every cycle, the counter is automatically added with 1, and the reverse keyword is automatically reduced by 1, which must be an integer from small to large. you can exit the loop using exit when.
Declare
V_count number;
Begin
V_count: = 8;
For I in 1 .. v_count loop
Dbms_output.put_line ('sub' | I | 'subobject ');
Exit when (I> 3 );
End loop;
End;

Case when loop syntax
-- Syntax 1
Case conditional expression
When expression result 1 then
Statement 1
...
When expression result n then
Statement n
[Else expression result]
End case;

-- Syntax 2
Case conditional expression
When expression result 1 then
Statement 1
...
When expression result n then
Statement n
[Else statement]
End case;

Case when statement example
Select trunc (tur. created_date, 'dd '),
Count (case
When tur. role_id = 'Project _ Sh' then
1
Else
Null
End) as role 1,
Count (case
When tur. role_id = 'Project _ gz 'then
1
Else
Null
End) as role 2,

Count (case
When tur. role_id = 'Project _ sz 'then
1
Else
Null
End) as role 3,
From test_ur tur
Group by trunc (tur. created_date, 'dd ')
Order by trunc (tur. created_date, 'dd') desc;

5) Go to the specified tag unconditionally
Goto lable
...
<Lable>
6) null statements that do not perform any operations

10. Handling exceptions and errors
1. Oracle provides exception and exception handler to handle errors.
2. exception refers to unexpected events during normal execution. The exception handling of the program block is a predefined or custom error, when a PL/SQL block is run, the entire PL/SQL block is automatically terminated once an exception is thrown and how to handle it is not specified.
3. exception errors are classified into three categories (predefined errors, non-predefined errors, and custom errors)
Pre-defined errors: No need to be defined in the program, which are automatically triggered by Oracle. A total of 24 errors are directly used in exceptions.
Non-predefined error: defined in the program, automatically triggered by Oracle
Custom errors: they must be defined in the program and must be thrown in the program.

1) pre-defined error
Exception
When No_data_found then
Dbms_output.put_line (to_char (sysdate, 'hh24: mi: ss') | 'execute failure ');
2) Non-predefined errors
-- Definition Error
<Exception> exception;
-- Association with standard Oracle errors
Pragma exception_init (<exception situation>, <Exception Code> );
-- Handle errors
Exception
When foundError then
Dbms_output.put_line (to_char (sysdate, 'hh24: mi: ss') | 'execute failure ');

3) custom error
-- Definition Error
<Exception> exception;
-- Raise an error through raise
Raise exception
-- Handle errors
Exception
When raiseError then
Dbms_output.put_line (to_char (sysdate, 'hh24: mi: ss') | 'execute failure ');


4) Modify custom error messages
Dbms_standard.raise_application_error (errorNumber, errorCode, errorsMsg );
ErrorNumber: error number:-20000 ~ -20999
ErrorMsg: error message (<KB)
ErrorFlag: true: add the error to the error list. false replaces the current Error List. The default value is false.
Dbms_standard.raise_application_error (-20001, 'error message ');
Keyword of error record:
Sqlcode: Error Code, for example, 6502
Sqlerrm: error message such as: ORA-06502: PL/SQL: Number or value error: character string buffer too small (<500KB)
Dbms_output.put_line ('error No. _ '| sqlcode |' _ error message _ '| sqlerrm );
When others exception must be placed at the end of the exception Handling Section to handle the default exception. when... There is no limit on the number of exceptions. if an exception is not handled, the program that calls the exception will be detected and the exception will be propagated to the outside. The exception will be processed and resolved or be stopped after the outermost loop is reached, the exception thrown in the declaration part is transferred to the previous part.

11. Functions and stored procedures
1) function:
Create [or replace] function functionName
(Arg1 [{in out}] type1 default value1,
...
Argn [{in out}] typen default valuen)
[Authid definer | current_user] -- permission Control
Return resultType
{Is |}
Variable Declaration
Begin
Statement execution
Return expression
Exception
Exception Handling
End functionName;
In out -- indicates the parameter mode. Input and Output Parameters are supported. If this parameter is not specified, the default value is input. The default value can only be set for input parameters. After a function is called, if the input parameter value is not specified, the default value of the input parameter is used.
Create or replace function funTranslateRole (v_roleId in varchar2,
V_result out varchar2)
Return varchar2
Is
Role_id varchar2 (20); -- Role ID
Begin
Role_id: = v_roleId;
If (role_id = 'Project _ sz ') then
V_result: = role_id | '_ indicates _ Customer Service ';
Else
V_result: = role_id | '_ indicates _ Unknown ';
End if;
Return v_result;
Dbms_output.put_line (to_char (sysdate, 'hh24: mi: ss') | 'processing successfully ');
Exception
When others then
Dbms_output.put_line (to_char (sysdate, 'hh24: mi: ss') | 'processing failed ');
End funTranslateRole;

1) Location Representation
-- Argvalue1, argvalue2,... argvaluen
FunTranslateRole (v_roleId, v_result)

2) Name Representation
-- This method has nothing to do with the order of parameters. v_result and v_roleId must be consistent with the parameter names in the function.
Declare
RoleId varchar2 (20); -- Role ID
Vresult varchar2 (60); -- Role result
Begin
RoleId: = 'Project _ bj ';
Vresult: = funTranslateRole (v_result => vresult, v_roleId => roleId );
Dbms_output.put_line (vresult );
End;

3) combination (name representation + location representation)
If the previous parameter uses name notation, all subsequent parameters must use name notation.
-- Call Method
Declare
V_roleId varchar2 (20); -- Role ID
V_result varchar2 (60); -- Role result
Begin
V_roleId: = 'Project _ bj ';
V_result: = funTranslateRole (v_roleId, v_result );
Dbms_output.put_line (v_result );
End;

2) stored procedure:
Create [or replace] procedure Name
(Arg1 [{in out}] type1 default value1,
...
Argn [{in out}] typen default valuen)
[Authid definer | current_user] -- permission Control
{Is |}
Variable Declaration
Begin
Statement execution
Exception
Exception Handling
End procedureName;
In out-indicates the parameter mode. Input and Output Parameters are supported. If this parameter is not specified, the default value is input. The default value can only be set for input parameters. After a function is called, if the input parameter value is not specified, the default value of the input parameter is used.
Create or replace procedure proTranslateRole (v_roleId in varchar2,
V_result out varchar2)
Is
Role_id varchar2 (20); -- Role ID
Begin
Role_id: = v_roleId;
If (role_id = 'Project _ sz ') then
V_result: = role_id | '_ indicates _ Customer Service ';
Else
V_result: = role_id | '_ indicates _ Unknown ';
End if;
Dbms_output.put_line (to_char (sysdate, 'hh24: mi: ss') | 'processing successfully ');
Exception
When others then
Dbms_output.put_line (to_char (sysdate, 'hh24: mi: ss') | 'processing failed ');
End proTranslateRole;

1) Location Representation
-- Argvalue1, argvalue2,... argvaluen
ProTranslateRole (v_roleId, v_result)
2) Name Representation
-- This method has nothing to do with the order of parameters. v_result and v_roleId must be consistent with the parameter names in the function.
Declare
RoleId varchar2 (20); -- Role ID
Vresult varchar2 (60); -- Role result
Begin
RoleId: = 'Project _ bj ';
Vresult: = proTranslateRole (v_result => vresult, v_roleId => roleId );
Dbms_output.put_line (vresult );
End;
3) combination (name representation + location representation)
If the previous parameter uses name notation, all subsequent parameters must use name notation.
-- Call method 1
Declare
V_roleId varchar2 (20); -- Role ID
V_result varchar2 (60); -- Role result
Begin
V_roleId: = 'Project _ bj ';
V_result: = proTranslateRole (v_roleId, v_result );
Dbms_output.put_line (v_result );
End;

-- Call method 2
Exec [ute] stored procedure name (parameter 1,... parameter n );
-- You can create local functions and procedures in PL/SQL blocks, but you cannot use the create or replace keyword.

1) differences between functions and processes
1. If you want to return multiple values or do not return values, you can use a process. If you want to return only one value, you can use a function.
2. A process is used to execute a series of actions, and a function is used to calculate and return 1 value.
3. You can call a function within an SQL statement to perform complex calculations, but the process cannot.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151359.htm

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.