当前位置：首页 > news >正文

python: Oracle Stored Procedure query table

news 来源：原创 2025/7/20 12:22:44

oracel sql script

CREATE OR REPLACE PROCEDURE SelectSchool(paramSchoolId IN char,p_cursor OUT SYS_REFCURSOR
) AS
BEGINOPEN p_cursor FORSELECT *FROM SchoolWHERE SchoolId = paramSchoolId;
END SelectSchool;
/-- 查询所有
CREATE OR REPLACE PROCEDURE SelectSchoolAll(p_cursor OUT SYS_REFCURSOR
) AS
BEGINOPEN p_cursor FORSELECT *FROM School;
END SelectSchoolAll;
/

DAL:

    def selectProc(cls)->list:"""存储过程:return:"""data = cls.myms.executeCursor("SelectSchoolAll")return datadef selectIdProc(cls,SchoolId:str)->list:"""存储过程:param SchoolId: 主键ID:return:"""argsvalue= SchoolIdargs = 'paramSchoolId'row = cls.myms.executeParmCursor(args,argsvalue,'SelectSchool')return row

BLL

def selectProc(cls) -> list[SchoolInfo]:"""存储过程查询:return:"""data=[]schools=[]data = cls.dal().selectProc()#print(data)if len(data) > 0:for SchoolId,SchoolName,SchoolTelNo in data:info = SchoolInfo()info.SchoolId = SchoolIdinfo.SchoolName = SchoolNameinfo.SchoolTelNo = SchoolTelNoschools.append(info)return schoolsdef selectIdProc(cls,SchoolId:str) -> list[SchoolInfo]:"""存储过程查询:param SchoolId::return:"""schools = []data = cls.dal().selectIdProc(SchoolId)if len(data) > 0:for SchoolId,SchoolName,SchoolTelNo in data:info = SchoolInfo()info.SchoolId = SchoolIdinfo.SchoolName = SchoolNameinfo.SchoolTelNo = SchoolTelNoschools.append(info)return schools

gui:

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  oracle 11g
# Datetime  : 2024/12/24 16:58
# User      : geovindu
# Product   : PyCharm
# Project   : pyOracleDemo
# File      : main.py
# explain   : 学习
import bll
from bll.school import SchoolBll
from model.school import SchoolInfo
import ttkbootstrap as ttk
from ttkbootstrap.constants import *
from ttkbootstrap.tableview import Tableview
from gui.mainwindow.tabWindow import TabWindowclass MainWidnow(ttk.Window):""""""def __init__(self):""""""super().__init__(themename="cosmo", title="塗聚文學習進行中")# self.Window(themename="cosmo") #superheroself.maxsize = 300# self.geometry('{}x{}'.format(1350, 900))self.first_var = ttk.Variable()self.title = "main"self.themename = 'superhero'self.last_var = ttk.Variable()self.occupation_var = ttk.Variable()self.colors = self.style.colorsself.coldata = [{"text": "编号", "stretch": False},"名称",{"text": "电话", "stretch": False},]bl = bll.SchoolBll()infos =bl.selectProc()  # bl.selectSql()  # sql 语句self.rowdata = []for info in infos:row = []row.append(info.SchoolId)row.append(info.SchoolName)row.append(info.SchoolTelNo)self.rowdata.append(row)self.dt = Tableview(master=self,coldata=self.coldata,rowdata=self.rowdata,paginated=True,pagesize=15,searchable=True,bootstyle=PRIMARY,stripecolor=(self.colors.light, None),)self.dt.pack(fill=BOTH, expand=YES, padx=10, pady=10)# dt.hide_selected_column(cid=0)  #隱藏第一列self.dt.view.bind("<Double-1>", self.rowselected)# dt.view.bind("<<TreeviewSelect>>", rowselected)b1 = ttk.Button(self, text="Open", bootstyle="success")  # ,command=self.openwindowsb1.pack(side=LEFT, padx=5, pady=10)# b1.bind("<Double-1>",openwindows)b1.bind("<Button-1>", self.openwindows)b2 = ttk.Button(self, text="New", bootstyle="info-outline")b2.pack(side=LEFT, padx=5, pady=10)b2.bind("<Button-1>", self.openlint)def rowselected(self, event) -> None:try:iid = self.dt.view.selection()[0]# print(iid)values = self.dt.view.item(iid, 'values')self.first_var.set(values[0])self.last_var.set(values[1])self.occupation_var.set(values[2])print(values[0], values[1], values[2])data = [values[0], values[1], values[2]]subwindow = ChildNewWindow(data)except IndexError as err:passdef openlint(self,event):""":param event::return:"""#self.destroy()print("link")suwindow=TabWindow()self.update()def openwindows(self, event):""""""try:print('open windows')iid = self.dt.view.selection()[0]values = self.dt.view.item(iid, 'values')data = [values[0], values[1], values[2]]subwindow = ChildNewWindow(data)self.update()except IndexError as err:passclass ChildNewWindow(ttk.Window):"""彈出子窗口 ttk.Toplevel"""def __init__(self, data):""":param master:"""super().__init__(title='Child Window')self.geometry('{}x{}'.format(850, 900))self.title = 'Child Window'self.label = ttk.Label(self, text=data[0])self.label.pack()self.labe2 = ttk.Label(self, text=data[1])self.labe2.pack()self.labe3 = ttk.Label(self, text=data[2])self.labe3.pack()if __name__ == '__main__':#print('PyCharm')mainwindow = MainWidnow()mainwindow.mainloop()

输出：

Oracle查询存储过程需要游标，脚本代码写法有点区别，程序也有区别。postgreSQL查询存储过程是函数，程序代码区别只是名称一致就可以了。Sql server和MySQL程序代码调用差不多，脚本代码格式不一样而已。

命名规范和架构规范，找其规律，自写生成器，可以减少自己的工作量，个人初步想法。

python: Oracle Stored Procedure query table

oracel sql script CREATE OR REPLACE PROCEDURE SelectSchool(paramSchoolId IN char,p_cursor OUT SYS_REFCURSOR ) AS BEGINOPEN p_cursor FORSELECT *FROM SchoolWHERE SchoolId paramSchoolId; END SelectSchool; /-- 查询所有 CREATE OR REPLACE PROCEDURE SelectScho…...

编程日记 2025/7/20 12:22:44

相关文章：