Oracle basic tutorial knowledge point summary

1. dba, sysdba, and sysoper are different roles. roles can include system permissions, object permissions, and roles. dba does not include sysdba and sysoper permissions. sysdba,

1. dba, sysdba, and sysoper are different roles. roles can include system permissions, object permissions, and roles. dba does not include sysdba and sysoper permissions. sysdba,

1. Differences between dba, sysdba, and sysoper

A dba is a role. A role can contain system permissions, object permissions, and roles. A dba does not contain sysdba and sysoper permissions;

Sysdba and sysoper are two types of system permissions. If these two types of permissions are relatively large, you are advised to log on to sys and system for management and do not grant them to common users;

2. public of Oracle

Public can be understood as a set of all users;

Grant dba to public; equivalent to all users are DBAs;

--- Plsql programming:

Plsql can be written: process, function, trigger;

Procedures, functions, and triggers are stored in oracle;

Plsql is a very powerful database process language;

The function can be called in java;

Advantages of plsql:

Improve performance. (traditional jdbc database connection, SQL Execution, compilation into database language, return value, close connection, multiple calls, multiple compilations, and write the process only once, on-call ;)

Modular Design Concept (paging process, order process, Transfer Process)

Reduce the amount of network transmission; (a large SQL statement is required, and now a process name is required)

High Security (oracle database protection, fields and everything in the database)

Disadvantages of plsql:

Poor portability (oracle language writing processes cannot be used in DB2)

1. Create a stored procedure

Create or replace procedure p1 is

Begin

Insert into mytable values ('gao', '123 ');

End;

/

2. The Stored Procedure programming reports an error. Check the error:

Show errors;

3. How to execute stored procedures

Exec p1;

------ Writing specifications

1. Single line comment :--

Multi-line comment:/*... */(same in java)

2. Naming rules for the symbols:

Variable: v _ (v_val)

Constant: c-(c_val)

Cursor:-cursor (emp_cursor)

Exception: e _ (e_error)

3. Block Structure

The pl/SQL block consists of three parts: the definition part, the execution part, and the exception part.

Declear/* definition part-define constants, variables, exceptions, and complex data types */

Begin/* execution part-the pl/SQL statements to be executed and SQL statements */must have

Exception/* exception Handling part-handling various running errors */optional

Public static void main (Sting args ){

Int a; // Definition

Try {

A ++; // execution part

} Catch (Exception e) {// Exception Handling Section

}

}

-- Contains only the blocks of the fast execution part.

Set serveroutput on -- open the output service;

Begin

Dbms_output.put_line ('hello, World! ');

End;

/

-- Pl/SQL blocks that contain declarations and execution;

Declare

V_passwd varchar2 (10 );

V_name varchar2 (20 );

Begin

Select passwd, name into v_passwd, v_name from mytable where name = & name; -- the address character indicates that the variable is accepted from the console;

Dbms_output.put_line (v_name | 'password:' | v_passwd );

End;

/

-- Pl/SQL blocks that contain declarations, executions, and exceptions

Declare

V_passwd varchar2 (10 );

V_name varchar2 (20 );

Begin

Select passwd, name into v_passwd, v_name from mytable where name = & name; -- the address character indicates that the variable is accepted from the console;

Dbms_output.put_line (v_name | 'password:' | v_passwd );

Exception

When no_data_found then

Dbms_output.put_line ('the user name entered does not exist! ');

End;

---- Process

A process is used to perform specific operations. It can contain input and output parameters,

-- Eg4:

Create procedure changepasswd (name1 varchar2, passwd1 varchar2) is

Begin

-- Change the password based on the user name

Update mytable set passwd = passwd1 where name = name1;

End;

-- Execute in plsql

Call changepasswd ('wucaiming ', '20140901 ');

-- Call the storage project in java

// Load the driver

Class. forName ("oracle. jdbc. driver. OracleDriver ");

// Get the connection

Connection c = DriverManager. getConnection ("jdbc: oracle: thin: @ 192.168.100.145: 1521: SDZC", "omgap", "omgap ");

// Create CallableStatement

CallableStatement cs = c. prepareCall ("{call changepasswd (?,?)} ");

// Give? Assignment

Cs. setString (1, "wucaiming ");

Cs. setString (2, "123 ");

// Execute

Cs.exe cute ();

/* How to use the process return value? */

Function is used to return specific data.

-- Eg5:

Create or replace function f_returnpasswd (name1 varchar2) return varchar2 is

Pwd varchar2 (30 );

Begin

Select passwd into pwd from mytable where name = name1;

Return pwd;

End;

-- Call in plsql

Var a varchar2 (30 );

Call f_returnPasswd ('wucaiming ') into:;

A package is used to logically combine processes and functions. It consists of a package specification and a package body.

-- Create a package

Create package p_package is

Procedure changepasswd (name1 varchar2, passwd1 varchar2 );

Function f_returnpasswd (name1 varchar2) return varchar2;

End;

-- Implement the package body

Create or replace package body p_package is

Procedure changepasswd (name1 varchar2, passwd1 varchar2) is

Begin

-- Change the password based on the user name

Update mytable set passwd = passwd1 where name = name1;

End;