Oracle-based PL/SQL Programming-1

3. The native installation of Oracle, the default is the boot service, boot time is too slow, shut down, need to open the service:

Oracledbconsoleorcl

Oracleoradb10g_home1isql*plus

Oracleoradb10g_home1tnslistener

ORACLESERVICEORCL This behind the ORCL is the database name, the official term is called the database SID

2. Open PL/SQL software, user name Scott Login Database ORCL password Tiger

3. Open a Test window, as follows

1 --Created on 2018/6/3 by ADMINISTRATOR2 Declare3   --where local variables are declared, including cursors (result set type variables), if not, declare can be removed4   --equivalent to Java's public class a{}5Iinteger;6 begin7   --Executive Section8   --equivalent to JavaPublic static void Main (string[] args)9   --Exception HandlingTen End;

4. Print HelloWorld

The results are as follows:

Dbms_output is the Oracle package,put_line () is the method called

Print on the command line: Set Serveroutput on is required before the print statement is printed, otherwise only execution does not print.

5. Use of Plsql variables:

the variables in Plsql are divided into two types:

(1) Normal data type:char varchar2 date number ( integer and Decimal ) Boolean long

(2) Special variable type (reference variable, record type variable)

Variables are declared as: variable name variable type (variable length) , for example:v_name varchar2

Variable assignment:

(1) declaration using: = Direct assignment:v_name varchar2: = ' Zhangsan ' Write only =, then compare

(2) Statement assignment: Select value into variable name

5.1 Use of common variables

1 --declare a person's information name salary address2 Declare3   --name cannot use name, this is reserved word4V_namevarchar2( -) := 'Zhang San';5   --Salary Decimals: Number (total length (decimal plus integer digits), scale) V_sal number (6,2)--1000.006V_sal Number;7   --Address8V_addrvarchar2( $);9 beginTen   --Direct Assignment OneV_sal:= 15000; A   --Statement Assignment -   Select 'Nanjing Road, Shanghai'  intoV_addr fromDual--The actual work should be from a table -   --Print Variable | | is a concatenation character, equivalent to the Java + theDbms_output.put_line ('Name:' ||V_name|| ', Salary:' ||V_sal|| ', Address:' ||v_addr); - End;

Execution Result:

5.2 Use of reference variables

Reference variable: The type and length of the variable depends on the type and length of the field in the table (the type and length of the variable and the type and length of the field in the reference table), through the table name . Column Name %TYPE Specifies the type and length of the variable, for example v_name Emp.ename%type

the benefit of a reference variable is that you need to know the type of the column in the table using the normal variable definition, whereas using a reference type does not need to consider the type of the column, and using %TYPE is a very programming style because it makes PL/SQL More flexible and more adaptable to updates to database definitions.

1 --inquire about the personal information of employee No. No. 7839 in the EMP table, print name and salary2 Declare3   --a name defines a reference variable4V_ename Emp.ename%TYPE;5   --Salary6V_sal Emp.sal%TYPE;7 8 begin9   --query names and salaries and assign values to variables. Assigning values using the SELECT INTO statementTen   SelectEname, Sal. intoV_ename, V_sal fromEmpwhereEmpno= 7839; OneDbms_output.put_line ('Name:' ||V_ename|| ', Salary:' ||v_sal); A  - End;

Results:

5.3 Record Type variables

A record variable defaults to a row of data in the receiving table, and cannot specify a field, which is equivalent to an object in Java.

Syntax: Variable name   table name %rowtype such as v_emp Emp%rowtype; through the variable name . How to get the value in a variable by the name of the field

1 --record Type Variable example: Use this variable to receive information from a row of SQL in a table2 Declare3   --Record type variable4V_emp EMP%ROWTYPE;5 6 begin7   --query all fields and assign to variables, field names are v_emp. Field names8   Select *  intoV_emp fromEmpwhereEmpno= 7839;9Dbms_output.put_line ('Name:' ||V_emp.ename|| ', Salary:' ||v_emp.sal);Ten  One End;

Results:

6. Process Control:

Conditional branching, Syntax:

BEGIN

IF condition 1 then execute 1;

elsif condition 2 then execution 2;-- Note not ElseIf , it's elsif .

Else Execution 3

End if;

END

Oracle-based PL/SQL Programming-1