Variable declaration in PL/SQL programming

Source: Internet
Author: User
Create the first program helloworldbegindbms_output.put_line (

Create the first program hello world begin dbms_output.put_line (

Variable declaration in PL/SQL programming

Create the first program hello world

Begin

Dbms_output.put_line ('Hello World ');

End;

/

Set serveroutput on; (command used to output statements to the dos window)

Begin

Dbms_output.put_line ('Hello World ');

End;

/

You can see Hello world in the dos window;

(Declaration of simple variables)

Declare variables:

Declare

V_name varchar2 (20); (variable name before, type after)

Begin

V_name: = 'myname'; (assign a value to the variable)

Dbms_output.put_line (v_name );

End;

/

(Exception Handling)

Declare

V_num number: = 0;

Begin

V_num: = 2/v_num;

Dbms_output.put_line (v_num );

Exception

When others then

Dbms_output.put_line ('error ');

End;

Rules for variable declaration:

1 variable names cannot use reserved words: (from, select)

2. The first character must be a letter.

3. Variable names can contain up to 30 characters

4. Do not name the table or column in the database

5 each row can only declare one variable

Common type variables:

Binary_integer: integer, mainly used to count rather than represent the field type (improving efficiency)

Number

Char: a fixed-length string;

Date: date;

Long: A long string with a maximum length of 2 GB;

Constant exercises;

Declare

V_temp number (1 );

V_count binary_integer: = 0;

V_sal number (4000.00): =;

V_date date: = sysdate;

V_pi constant number (3.14): =;

V_valid boolean: = false;

V_name varchar2 (20) not null: = 'myname ';

Begin

Dbms_output.put_line ('v _ temp value' | v_temp); (| connector)

End;

/

Use '--' to comment out a paragraph /**/

Dbms_output.put_line () cannot print the boolean variable

Use the % type attribute:

Declare (variable declaration type varies according to the table type)

V_empno number (4 );

V_empno2emp.empno % type;

V_empno3v_empno2 % type;

Begin

Dbms_output.put_line ('test ');

End;

Compound variable declaration:

First:

Table variable type (equivalent to java array)

Declare

Typetype_table_emp_empnois table of emp. empno % type index by binary_integer;

V_empnostype_table_emp_empno;

Begin

V_empnos (0): = 7369;

Vanderbilt empnos (2): = 7839;

V_empnos (-1): = 9999; -- (the subscript can be smaller than 0) (comment)

Dbms_output.put_line (v_empnos (-1 ));

End;

Record variable type: (equivalent to java class)

Declare

Typetype_record_dept isrecord

(

Deptno dept. deptno % type,

Dname dept. dname % type,

Loc dept. loc % type

);

V_temp type_record_dept;

Begin

V_temp.deptno: = 50; (equivalent to referencing to access member variables)

V_temp.dname: = 'aaa ';

V_temp.loc: = 'bj ';

Dbms_output.put_line (v_temp.deptno | ''| v_temp.dname );

End;

Use % rowtype to declare the record variable;

Declare

V_tempdept % rowtype;

Begin

V_temp.deptno: = 50;

V_temp.dname: = 'aaa ';

V_temp.loc: = 'bj ';

Dbms_output.put_line (v_temp.deptno | ''| v_temp.dname );

End;

Application of SQL (select) Statements in PlSQL:

Declare

V_ename emp. ename % type;

V_sal emp. sal % type;

In the select in -- plsql statement, the select statement must contain into, and the return record must be guaranteed.

Dbms_output.put_line (v_ename | ''| v_sal );

End;

Declare

V_emp emp % rowtype;

Begin

Select * into v_emp from emp where empno = 7369;

Dbms_output.put_line (v_emp.empno );

End;

Application of other SQL statements in plsql:

Declare

V_deptno dept. deptno % type: = 50;

V_dname dept. dname % type: = 'aaa ';

V_loc dept. loc % type: = 'bj ';

Begin -- insert a value;

Insert into dept2 values (v_deptno, v_dname, v_loc );

Commit;

End;

Declare

V_deptnoemp2.deptno % type: = 10;

V_count number;

Begin

Update emp2 set sal = sal/2 where deptno = v_deptno;

-- Select deptno into v_deptno from emp2 where empno = 7369;

-- Select count (*) into v_count from emp2;

-- SQL indicates the statement that has just been executed. (After this statement is executed, how many records are affected)

Dbms_output.put_line (SQL % rowcount | 'Affected records ');

Commit;

End;

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.