Databases (storage, views)

Stored procedures:

Stored procedures (stored procedure) are sometimes referred to as sproc. Stored procedures are stored in a database rather than in a separate file, with input parameters, output parameters, and return values.

In a database, the process of creating stored procedures is the same as creating other objects, except for the AS keyword that it uses . The basic syntax for a stored procedure is as follows:

CREATE procduer| PROC <sproc name>

[<parameter Name>[schema.] <data Type>[varying][=<default value>][out [put]][,

[<parameter Name>[schema.] <data Type>[varying][=<default value>][out [put]][,

...]]

[With

recompile| encryption| [EXECUTE as {caller| Self| owner|< ' user name ';}]

[For REPLICATION]

As

<code>| EXTERNAL NAME <assembly name>.<assembly class>

Example stored procedure one:

Create proc   first asSelect* from newsGo

To execute a stored procedure method:

--The following statements can all execute statementsExecuteNewsexecNews--define a variable to receive the return valueDeclare @fanhuizhi intExecute @fanhuizh=NewsSelect @fanhuizh--Modifying stored ProceduresAlter procCangcang asSelectCode as'numbering', Mname as'name', Mprice as'Price', Gname as'supplier', (Mshu+Cshu) as'Total Goods'  fromMenJoinCang onMen.mcode=Cang.codeJoinGong onCang.cg=Gong.gcodeGo

Use a stored procedure to find examples of information in three tables:

Create proc SECOF  as begin Select *  from Gong Select *  from Cang Select *  from  Men End Go Execute SECOF

Enter student number to see the total score of each student's class

1. An overview view of the view is actually a query SQL statement that displays related data in one or more tables or other views. The view uses the results of a query as a table, so the view can be thought of as a stored query or a virtual table. Views originate from tables, and all changes to view data are eventually reflected in the base table of the view, which must obey the integrity constraints of the base table and also trigger triggers defined on the base table. (Oracle supports explicitly defining triggers and defining some logical constraints on the view )

2. The view's storage differs from the table, and the view does not require allocation of storage space, nor does the view contain actual data. A view simply defines a query, and the data in the view is fetched from the base table, which is generated dynamically when the view is referenced. Because views are based on other objects in the database, a view requires only the space in the data dictionary to hold its definition, without additional storage space.

3. The role of the view

Users can display data from a base table in different ways, and the view is powerful in that it organizes the data in the base table according to the needs of different users. Common uses for views are as follows:

(1) provides additional security controls for tables by setting the columns and rows of data that the user can access by using the view

(2) Hide data complexity: A connection (join) can be used in a view to form a new dataset with related columns in multiple tables. This view hides the fact that the data originates from multiple tables for the user.  

(3) simplifying the user's SQL statement: Users can query information from multiple tables without knowing how the tables are connected by using views.  

(4) display the data in the base table at different angles:: The column name of the view can be arbitrarily changed without affecting the base table of this view

(5) make the application unaffected by changes in the base table definition: A 3 column in a base table that contains 4 data columns is queried in the definition of a view. When new columns are added to the base table, applications that use this view will not be affected because the definition of the view is not affected.  

(6) Saving complex queries:A query may perform complex calculations on table data. After the user saves the query as a view, it is only possible to query this view each time a similar calculation is made.

(7) Logical data Independence:: Views enable applications and database tables to be somewhat independent. If there is no view, the application must be built on the table. With a view, the program can be built on top of the view so that the program is separated from the database table by the view.

 UseChaoshiGoSelect*  fromCangkuSelect *  fromgongying--write the above two statements as a statement displaySelectCangku.ccode,cname,cprice,cshu,cgcode,gongying.gname,gtel fromCangku, gongyingwhereCangku.cgcode=Gongying.gcode--Join onSelectCangku.ccode,cname,cprice,cshu,cgcode,gongying.gname,gtel fromCangkuJoinGongying onCangku.cgcode=Gongying.gcode--View Creation (statement to create a view)Create ViewCangkugongying--syntax for creating views as--as go in the middle put a query statement I needSelectCangku.ccode,cname,cprice,cshu,cgcode,gongying.gname,gtel fromCangkuJoinGongying onCangku.cgcode=Gongying.gcodeGo--Call ViewSelect * fromCangkugongying--A view is a virtual table, so you can--View Creation (mouse action create VIEW)Select *  fromXinSelectCname,cshu fromXin

Database (storage, view)