Oracle PL/SQL entry syntax points

Source: Internet
Author: User
Tags dname

Pl_sql: With branching and looping, process oriented
Anonymous BLOCK:
Declare (optional, where various variables and cursors are declared)
Begin (necessary, start from here)
Exception (executed after crawling to an exception)
End
[SQL] View plaincopy
Set serveroutput on; (default is OFF)
--The simplest PL/SQL statement block
Begin
Dbms_output.put_line (' helloworld! ');
End
--the simplest block of statements
Declare
V_name varchar2 (20);
Begin
V_name: = ' myname ';
Dbms_output.put_line (V_name);
End
--The composition of the statement block
Declare
V_name number: = 0;
Begin
V_name: = 2/v_num;
Dbms_output.put_line (V_name);
exception
When others then
Dbms_output.put_line (' error ');
End
--Variable declaration
1. Variable name can not use reserved words, such as from,select, etc.
2. The first character must be a letter
3. Variable name contains up to 30 characters
4. Do not have the same name as a database table or column
5. Only one variable can be declared per line
--Common variable types
1. Binary_integer: integer, used primarily to count rather than to denote field type, high efficiency
2. Number: Numeric type
3. Char: fixed-length string
4. VARCHAR2: Variable length string
5. Date: Dates
6. Long: length string, maximum 2GB
7. Boolean: Boolean type, can take value ture, false and null value, must give initial value, cannot print
--Variable declaration

[SQL] View plaincopy
Declare
V_temp number (1);
V_count Binary_integer: = 0;
V_sal number (7,2): = 4000.00;
V_date Date: = Sysdate;
V_PI constant Number (3,2): = 3.14;
V_valid Boolean: = false;
V_name varchar2 () NOT null: = ' MyName ';
Begin
Dbms_output.put_line (' v_temp value: ' | | v_count);
End
--variable declaration, using the%type property

[SQL] View plaincopy
/* Comment Multiple lines */--Comment one line
Declare
V_empno number (4);
V_empno2 emp.empno%type;--declaring a variable type with a field type in the table
V_EMPNO3 v_empno2%type;--declaring variable type with variable Type property
Begin
Dbms_output.put_line (' Test ');
End
--Simple variable assignment

[SQL] View plaincopy
Declare
V_name varchar2 (20);
V_sal number (7,2);
V_sal2 number (7,2);
V_valid Boolean: = false;
V_date date;
Begin
V_name: = ' MyName ';
V_sal: = 23.77;
V_sal2: = 23.77;
V_valid: = (v_sal = v_sal2);--Determine whether equal use =
V_date: = To_date (' 1999-08-12 12:23:30 ', ' yyyy-mm-dd HH24:MI:SS ');
End
--table variable type, which represents an array

[SQL] View plaincopy
Declare
Type Type_table_emp_empno is table of Emp.empno%type index by binary_integer;--first declares a type
V_empnos type_table_emp_empno;--then declare the variable with this type
Begin
V_empnos (0): = 7369;
V_empnos (2): = 7839;
V_empnos (-1): = 9999;
Dbms_output.put_line (V_empnos ( -1));---1 access is not available, 0,2 can access the
End
--record type, representing a class

[SQL] View plaincopy
Declare
type type_record_dept is record
(
Deptno dept.deptno%type,
Dname Dept. Dname%tpye,
Loc Dept.loc%type
);
V_temp type_record_dept;
Begin
V_temp.deptno: = 50;
V_temp.dname: = ' aaaa ';
V_temp.loc: = ' BJ ';
Dbms_output.put_line (V_temp.deptno | | "| | V_temp.dname);
End;
--Declare the record variable with%rowtype
[SQL] View plaincopy
declare
v_temp dept%rowtype;
Begin
V_temp.deptno: = 50;
V_temp.dname: = ' aaaa ';
V_temp.loc: = ' BJ ';
Dbms_output.put_line (V_temp.deptno | | "| | V_temp.dname);
End;
--pl/sql Executes a SELECT statement, with into, you must return and return only one record
--sql statement application
[SQL] View plaincopy
Declare
V_ename Emp.ename%type;
V_sal Emp.sal%type;
Begin
Select Ename,sal to V_ename,v_sal from emp where empno = 7369;
Dbms_output.put_line (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.ename);
End

Declare
V_deptno Dept.deptno%type: = 50;
V_dname dept.dname%type: = ' aaaa ';
V_loc dept.loc%type: = ' BJ ';
Begin
INSERT into DEPT2 values (V_DEPTNO,V_DNAME,V_LOC);
commit;--, don't forget!!!
End

SELECT * from Dept2;

Declare
V_deptno emp2.deptno%type: = 10;
V_count number;
Begin
--update emp2 Set sal = SAL/2 where deptno = V_deptno;
--select Deptno to V_deptno from emp2 where empno = 7369;
Select COUNT (*) into V_count from EMP2;
Dbms_output.put_line (Sql%rowcount | | ' records are affected ');//sql, the keyword that represents the SQL program that you just executed
end;
--pl/sql executes the DDL statement and requires execute immediate
[SQL] view plaincopy
Begin
Execute immediate ' CREATE TABLE T (nnn VARCHAR2 (() Default ' aaaa ') ';
End;
--if Statement
[SQL] View plaincopy
--remove 7369 of the salary, if <1200, output ' low ' if <2000 output ' middle ', otherwise output ' high '

Declare
V_sal Emp.sal%type;
Begin
Select Sal into V_sal from emp where empno = 7369;
if (v_sal<1200) then
Dbms_output.put_line (' low ');
elsif (v_sal<2000) then--elsif no E
Dbms_output.put_line (' Midddle ');
Else
Dbms_output.put_line (' High ');
End If;
End
--Cycle
[SQL] View plaincopy
Declare
I binary_integer: = 1;
Begin
Loop
Dbms_output.put_line (i);
I: = i + 1;
Exit when (I >= 11);
End Loop;
End

Declare
J Binary_integer: = 1;
Begin
While J < loop
Dbms_output.put_line (j);
J: = j + 1;
End Loop;
End

Begin
For k in 1..10 Loop
Dbms_output.put_line (k);
End Loop;

For k in reverse 1..10 loop
Dbms_output.put_line (k);
End Loop;
End
--Error handling
[SQL] View plaincopy
Declare
V_temp number (4);
Begin
Select Empno to V_temp from emp where deptno = 10;
exception
When Too_many_rows Then
Dbms_output.putline (' Too many Records ');
When others then
Dbms_output.put_line (' error ');
End

Declare
V_temp number (4);
Begin
Select Empno to V_temp from emp where empno = 2222;
exception
When No_data_found Then
Dbms_output.put_line (' no data ');
End

--sql Program cross-database platform is better, PL/SQL efficiency is high
--DBA, error log
[SQL] View plaincopy
CREATE TABLE Errorlog
(
ID number primary Key,
Errcode number,
ErrMsg VARCHAR2 (1024),
Errdate Date
);

Create sequence seq_errorlog_id start with 1 increment by 1;

Declare
V_deptno Dept.deptno%type: = 10;
V_errcode number;
V_errmsg VARCHAR2 (1024);
Begin
Delete from dept where deptno = V_deptno;
Commit
exception
When others then
Rollback
V_errcode: = SQLCODE;
V_errmsg: = SQLERRM;
INSERT into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
Commit
End
cursors cursor, pointer on result set
[SQL] View plaincopy
Declare
Cursor C is
SELECT * from emp;--just declares the cursor, not yet fetching the data
V_emp C%rowtype
Begin
Open C;
Fetch C into v_emp;
Dbms_output.put_line (V_emp.ename);
Close C;
End

Declare
Cursor C is
SELECT * from EMP;
V_emp C%rowtype;
Beigin
Open C;
Loop
Fetch C into v_emp;
Exit when (C%notfound);
Dbms_output.put_line (V_emp.ename);
End Loop;
Close C;
End
--Cursors with parameters
[SQL] View plaincopy
Declare
Cursor C (v_deptno emp.deptno%type,v_job emp.job%type)
Is
Select Ename,sal from emp where deptno = v_deptno and job = V_job;
Begin
For v_temp in C (+, ' clerk ') loop
Dbms_output.put_line (V_temp.ename);
End Loop;
End
--Updatable Cursors
[SQL] View plaincopy
Declare
Cursor C
Is
SELECT * from EMP2 for update;
Begin
For v_temp in C loop
if (V_temp.sal <) Then
Update EMP2 Set sal = sal*2 where CURRENT of C;
else if (v_temp.sal =) Then
Delete from EMP2 where current of C;
End If;
End Loop;
Commit
End
--Create a stored procedure, a PL/SQL block with a name
[SQL] View plaincopy
Grant CREATE procedure to Shijin;
Create or Replace procedure P
Is
Cursor C is
SELECT * from EMP2 for update;
Begin
For v_emp in C loop
if (V_emp.deptno = ten) Then
Update EMP2 Set sal = Sal + ten where current of C;
else if (V_emp.deptno =) Then
Update EMP2 Set sal = sal + where current of C;
Else
Update EMP2 Set sal = sal + where current of C;
End If;
End Loop;
Commit
End Loop;

Begin
P
End
Or
exec p;
--Stored procedure with parameters, default is in
[SQL] View plaincopy
Create or Replace procedure P
(V_a in Number,v_b Number,v_ret out number,v_temp in out number)
Is

Begin
if (V_a > V_b) Then
V_ret: = v_a;
Else
V_ret: = V_b;
End If;
V_temp: = v_temp + 1;
End

Declare
V_a Number: = 3;
V_b Number: = 4;
V_ret number;
V_temp Number: = 5;
Begin
P (v_a,v_b,v_ret,v_temp);
Dbms_output.put_line (V_ret);
Dbms_output.put_line (v_temp);
End
--function
[SQL] View plaincopy
Create or Replace function Sal_tax
(v_sal number)
return number
Is
Begin
if (V_sal <) Then
return 0.10;
elsif (V_sal < 2750) Then
return 0.15;
Else
return 0.20;
End If;
End

Select Sal_tax (SAL) from EMP;
--Trigger!!! Grasp the concept firmly
[SQL] View plaincopy
CREATE TABLE Emp2_log
(
uname varchar2 (20),
Action VARCHAR2 (10),
Atime Date
);
Grant create Triger to Shijin;
Create or Replace trigger trig
After insert or delete or update on EMP2--Here you can add a for each row
Begin
If inserting then
INSERT into Emp2_log values (USER, ' Insert ', sysdate);
elsif updating Then
INSERT into Emp2_log values (USER, ' Update ', sysdate);
elsif deleting Then
INSERT into Emp2_log values (USER, ' delete ', sysdate);
End If;
End

Update EMP2 Set sal = sal*2 where deptno = 30;

Drop trigger trig;

Create or Replace trigger trig
After update on dept
For each row
Begin
Update emp Set deptno =: New.deptno where deptno =: old.deptno;
End
--Storage and display of tree-like structure data
[SQL] View plaincopy
drop table article;

CREATE TABLE Article
(
ID number primary KEY,
Cont varchar2 (4000),
PID number,
IsLeaf number (1),--0 Represents a non-leaf node, 1 represents a leaf node
Alevel number (2)
)

Create or Replace procedure P (v_pid article.pid%type,v_level binary_integer) is
cursor C was select * from article where pid = V_pid;
V_prestr VARCHAR2 (1024): = ';
Begin
For i in 1..v_level loop
V_prestr: = V_prestr | | ‘****‘;
End Loop;
for v_article in C loop
Dbms_output.put_line (v_prestr | | v_article.cont);
if (v_article.isleaf = 0) then
P (v_articel.id,v_level + 1);
End If;
End Loop;
End;
INSERT into article values (1, ' Ant vs Elephant ', 0,0,0);
INSERT into article values (2, ' elephants are beaten down ', 1,0,1);
INSERT into article values (3, ' ants are not good ', 2,1,2);
INSERT into article values (4, ' nonsense ', 2,0,2);
INSERT into article values (5, ' no nonsense ', 4,1,3);
INSERT into article values (6, ' How possible ', 1,0,1);
INSERT into article values (7, ' How not possible ', 6,1,2);
INSERT into article values (8, ' very likely ', 6,1,2);
INSERT into article values (9, ' Elephant in hospital ', 2,0,2);
INSERT into article values (10, ' Nurse is Ant ', 9,1,3);

Set serverout on;
Begin
P (0,0);
End
Show error;

Oracle PL/SQL entry syntax points

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.