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;